Okay and what do you store it that table?

|
MasterZiv 2019-12-24 10:11:34
TheQuotidian 2019-12-24 10:07:33
http://www.sqlines.com/download

I wouldn’t trust this…

MasterZiv 2019-12-24 10:12:12
TheQuotidian 2019-12-24 10:07:33
http://www.sqlines.com/download

Especially for converting a single simple stored program

TheQuotidian 2019-12-24 10:12:32
Ok
Syedjafer 2019-12-24 10:52:54
@TheQuotidian tanks
Syedjafer 2019-12-24 10:53:20
Any other solutions available
Syedjafer 2019-12-24 10:53:33
Like preprocessing
TheQuotidian 2019-12-24 11:23:36
PL SQL needs to be re written. I’m curious why is the need though considering both to be from the Oracle stable.
MasterZiv 2019-12-24 11:24:10
TheQuotidian 2019-12-24 11:23:36
PL SQL needs to be re written. I’m curious why is the need though considering both to be from the Oracle stable.

Do you mean to re-design the language?

TheQuotidian 2019-12-24 11:24:27
Re syntact
MasterZiv 2019-12-24 11:25:24
TheQuotidian 2019-12-24 11:23:36
PL SQL needs to be re written. I’m curious why is the need though considering both to be from the Oracle stable.

this “both” — what are they?

TheQuotidian 2019-12-24 11:30:05
Mysql and Oracle
Deadpool 2019-12-25 12:58:27
I was. Working on workbench
Deadpool 2019-12-25 12:59:08
I accidentally ended up hitting on truncate option in Mumbai region
Deadpool 2019-12-25 12:59:28
Would it delete complete data from server too
TheQuotidian 2019-12-25 12:59:46
You can ask AWS to restore the last image
MasterZiv 2019-12-25 13:00:01
Deadpool 2019-12-25 12:59:28
Would it delete complete data from server too

What command did you send ?

Deadpool 2019-12-25 13:02:18
I exported and imported data from back up like events
Deadpool 2019-12-25 13:02:48
Then I thought I was in local host and hit on truncate option
TheQuotidian 2019-12-25 13:12:18
The only way is backups or if you have binary logs that contain all the data.otherwise cannot restore.
piterden 2019-12-25 18:19:36
Becoming a senior fullstack developer in 13 minutes and 37 seconds.
piterden 2019-12-25 22:26:23
Focus on data
madno 2019-12-27 09:30:41
How to make updates faster in a table
madno 2019-12-27 09:31:03
It takes a lot of time to update a single row.
madno 2019-12-27 09:31:06
Any help??
piterden 2019-12-27 09:31:26
Show details
madno 2019-12-27 09:32:23
Ok
madno 2019-12-27 09:33:49
CREATE TABLE t_discover_agent_master_scan (
id int(11) NOT NULL AUTO_INCREMENT,
scan_id varchar(100) DEFAULT NULL,
name text NOT NULL,
status text,
parameters longtext,
archive bit(1) DEFAULT NULL,
is_completed bit(1) DEFAULT NULL,
is_aborted bit(1) DEFAULT NULL,
start_time bigint(20) DEFAULT NULL,
end_time bigint(20) DEFAULT NULL,
total_time bigint(20) DEFAULT NULL,
scan_logs longtext,
ipaddress_to_credential_id_map longtext,
run_by int(11) DEFAULT NULL,
group_probe_id int(11) DEFAULT NULL,
client_id int(11) DEFAULT NULL,
credential_id int(11) DEFAULT NULL,
location_id int(11) DEFAULT NULL,
unique_id varchar(100) DEFAULT NULL,
scheduled_scan_id int(11) DEFAULT NULL,
is_email_sent bit(1) NOT NULL,
da_ip_address varchar(100) DEFAULT NULL,
exclude_ip_address longtext,
ports longtext,
certificate_directory text,
registry_path text,
software_url text,
install_arguments text,
audit_id int(11) DEFAULT NULL,
blueprint_mssql text,
is_create_disc_asset bit(1) DEFAULT NULL,
is_export_data bit(1) DEFAULT NULL,
mssql_data_mapping_parameters text,
mssql_query text,
is_quick_scan bit(1) DEFAULT b’0′,
is_host_name_scan bit(1) DEFAULT b’0′,
PRIMARY KEY (`id`),
KEY FK51B9EBD5134638C9 (`audit_id`),
KEY FK51B9EBD5C4C3C220 (`run_by`),
KEY FK51B9EBD564392E85 (`group_probe_id`),
KEY FK51B9EBD5A67A35A0 (`client_id`),
KEY FK51B9EBD57F0A9464 (`location_id`),
KEY FK51B9EBD574ABAEA4 (`credential_id`),
CONSTRAINT FK51B9EBD5134638C9 FOREIGN KEY (`audit_id`) REFERENCES t_cmdb_audits (`id`),
CONSTRAINT FK51B9EBD564392E85 FOREIGN KEY (`group_probe_id`) REFERENCES t_discover_agent_group_probe (`id`),
CONSTRAINT FK51B9EBD574ABAEA4 FOREIGN KEY (`credential_id`) REFERENCES t_discover_agent_credential (`id`),
CONSTRAINT FK51B9EBD57F0A9464 FOREIGN KEY (`location_id`) REFERENCES t_locations (`id`),
CONSTRAINT FK51B9EBD5A67A35A0 FOREIGN KEY (`client_id`) REFERENCES t_discover_agent_client (`id`),
CONSTRAINT FK51B9EBD5C4C3C220 FOREIGN KEY (`run_by`) REFERENCES t_core_users (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=753 DEFAULT CHARSET=latin1
madno 2019-12-27 09:34:44
update statement is
piterden 2019-12-27 09:37:34
What the fuck is it?
madno 2019-12-27 09:38:05
sorry, its quite a long query
piterden 2019-12-27 09:38:32
I don’t see the start of it
madno 2019-12-27 09:38:57
Ill send a file of it
piterden 2019-12-27 09:41:44
madno 2019-12-27 09:30:41
How to make updates faster in a table

To make it faster you should make it smaller

madno 2019-12-27 09:42:43
piterden 2019-12-27 09:41:44
To make it faster you should make it smaller

Ok

madno 2019-12-27 09:43:22
Because of this i am getting primary key lock??
piterden 2019-12-27 09:44:53
parameters
scan_logs

What is storing in these fields?

madno 2019-12-27 09:46:50
In Parameters up addressess
madno 2019-12-27 09:47:27
In scanlogs we store the some logs about the ip’s
piterden 2019-12-27 09:48:10
Okay and what do you store it that table? Essentially…
madno 2019-12-27 09:49:53
We store the customer queries about the machines they want to know the details about
madno 2019-12-27 09:50:15
Like ip, windows or Linux, timestamps
MasterZiv 2019-12-27 09:54:00
madno 2019-12-27 09:30:41
How to make updates faster in a table

Use PK for updates, use other keys and indices, and that is all

madno 2019-12-27 09:54:57
We use PK for updates as u can see in the query
MasterZiv 2019-12-27 09:56:06
madno 2019-12-27 09:54:57
We use PK for updates as u can see in the query

Fine!

piterden 2019-12-27 09:56:55
It seems you have broken normal forms there
madno 2019-12-27 09:58:45
Yeah that is biggest issue we are facing..we have this monolithic table to store everything ..
MasterZiv 2019-12-27 09:59:03
madno 2019-12-27 09:54:57
We use PK for updates as u can see in the query

Ok and what is wrong with the update? What is disturbing you?

MasterZiv 2019-12-27 09:59:42
madno 2019-12-27 09:58:45
Yeah that is biggest issue we are facing..we have this monolithic table to store everything ..

This is bad…
I’ll take a look in details

madno 2019-12-27 10:02:24
MasterZiv 2019-12-27 09:59:03
Ok and what is wrong with the update? What is disturbing you?

Two things i want to know
1. Any work around for the lock-mode X locks rec but not gap waiting (what is the solution for this)
2. How to make the MySQL write optimised

piterden 2019-12-27 10:08:54
madno 2019-12-27 10:02:24
Two things i want to know
1. Any work around for the lock-mode X locks rec but not gap waiting (what is the solution for this)
2. How to make the MySQL write optimised

IMHO you should refactor your database to have a number of smaller tables. It would be better for you to hire a professional

|