Sja Hangs/idle/freezes While Backing Up Mysql Db Tables

[Saleem]

Hi,

I am using SQLyog Trial 8.54. I have scheduled a backup using the Schedule Backups power tool to backup all the MySQL databases including system database in a MySQL server. The job starts, and backs up few tables and then it just stays on “Exporing table

…” for a very long time. The MySQL Version on the server is:

[root@localhost ~]# mysql –version

mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

It mostly stays there on the table called “bugs” and the table is used by bugzilla.

Any help regarding this is greatly appreciated.

Thanks,

Saleem.

[peterlaursen]

1) Could you try exporting this particular table alone (with SQLyog or another tool).  How big is a .zip of such dump? If you can post the .zip to our ticket system we could try in our environment. There may be server-side settings involved and it is not sure that it is reproducible in our environment, though. But we need to try it. The output from “SHOW GLOBAKL VARIABLES” and/or the my.cnf may also be useful.

2) Also you could try to import such dump to another server and check if problem is the same.  

3) Also we will need the jobfile to see your settings for the job.  You can obscure details about the server, password etc.

4) What is 'very long time'? How long did you wait? 

5) How do you connect? direct, SSH-tunnel, HTTP-tunnel?

I suspect (but it is a guess only, of course) that this table may have a very long TEXT/BLOB column with a description-field or similar and some buffer setting (server-side or client-side) is not optimal.  

[Saleem]

1. Tried exporting this particular table with SQLYog and MySQL Administrator, it works fine and takes 2 minutes to complete. Not only on this database, few other tables on another db is also freezing like this. Without zipping the .sql file size of this db is 91.7MB.

I have attached the show global variable command's output and the my.cnf. Unfortunately I am waiting for authorisation from the higher level to upload the .sql dump as .zip file.

2. I have another server with same configuration [including OS and mysql version] and import works, export with sja gives problem as described.

3. Zipped the backup.xml and attached the backup.zip [as the forum does not allow attaching .xml files] with the credentials and other important info with the word “censored”

4. 3 days 🙂 I just let it to complete on it's own and as of now it is still backing up that table. I have attached the screenshot with the db names scrambled.

5. Direct MySQL connection. The server and the machines all are connected on a Gigabit switch with managed L3 switches.

Please do let me know if I can help any further.

Regards..

'peterlaursen' wrote on '19:

1) Could you try exporting this particular table alone (with SQLyog or another tool).  How big is a .zip of such dump? If you can post the .zip to our ticket system we could try in our environment. There may be server-side settings involved and it is not sure that it is reproducible in our environment, though. But we need to try it. The output from “SHOW GLOBAKL VARIABLES” and/or the my.cnf may also be useful.

2) Also you could try to import such dump to another server and check if problem is the same.  

3) Also we will need the jobfile to see your settings for the job.  You can obscure details about the server, password etc.

4) What is 'very long time'? How long did you wait? 

5) How do you connect? direct, SSH-tunnel, HTTP-tunnel?

I suspect (but it is a guess only, of course) that this table may have a very long TEXT/BLOB column with a description-field or similar and some buffer setting (server-side or client-side) is not optimal.  

[Mahesh]

Are you able to backup successfully particular table using “Backup table as SQL dump..” ?

Select a table from Object browser — > Right Click –> Export –> “Backup table as SQL dump”

[Saleem]

Yes Mahesh,

It works like a charm and completed the table export in not less than 2 minutes, where as the sja backup is still running for almost 3 and half days now.

Regards..

'Mahesh' wrote on '19:

Are you able to backup successfully particular table using “Backup table as SQL dump..” ?

Select a table from Object browser — > Right Click –> Export –> “Backup table as SQL dump”

[Mahesh]

This looks weird, can you attach a .SQL file created using “Backup as SQLdump” option (zip and attach).

Also You can create a support ticket from here for privacy of data:

http://webyog.com/support/

Thanks for the information.

[Saleem]

Hi Mahesh,

I am really sorry, but I didn't get the clearance to upload the .sql file.

But the mysql database is just filled with multiple instances of bugzilla's databases used by the testing team, development team, etc for development of various applications developed by the organization.

It normally gets stuck at the table “bugs” in the bugzilla's database. May be this will help you to re-produce the problem at your end.

Regards…

'Mahesh' wrote on '19:

This looks weird, can you attach a .SQL file created using “Backup as SQLdump” option (zip and attach).

Also You can create a support ticket from here for privacy of data:

http://webyog.com/support/

Thanks for the information.

[tammax]

'Saleem' wrote on '19:

Hi,

I am using SQLyog Trial 8.54. I have scheduled a backup using the Schedule Backups power tool to backup all the MySQL databases including system database in a MySQL server. The job starts, and backs up few tables and then it just stays on “Exporing table

…” for a very long time. The MySQL Version on the server is:

[root@localhost ~]# mysql –version

mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

It mostly stays there on the table called “bugs” and the table is used by bugzilla.

Any help regarding this is greatly appreciated.

Thanks,

Saleem.

[peterlaursen]

@tammax.  You are only quoting Saleem and do not add any information of your own.  What was the message?  Do you have same problem? 

[nithin]

Saleem,

We need to know which query is slow on server.

So please recreate the situation and follow the steps explained below, when it is in waiting state for “Exporting ….”

– Open another SQLyog and connect to same server

– Open Tools->Show->Processlist. It lists all active processes of the server. Click on 'Refresh' button in dialog, and you can find which process is for the Scheduled-backup.

To identify the process, please check the columns 'Command'(which would be “Query”) and 'State' (which would be “Sending data”, or “Writing”) of the table displayed. Please see the attached screen-shot.

– Once you identified the row (process) that refer to the Scheduled-backup thread, copy the 'Info' column (that contains the query executing) and send to us.

– You can also provide the other column status also by sending screen-shot of 'process list' dialog and mark the particular row.

For privacy you could create ticket using below link

http://webyog.com/support/

Regards

[Saleem]

Hi nithin,

Please find the attached screenshots taken at same time. This time sja was frozen while taking a wordpress blog's db backup and stuck at wp_redirection_modules table and the command for that process in the process list shows sleep!! 🙁

See the attached screenshots. I have masked the IPs and DB names. The server and the desktop taking backup are within the LAN.

I think we are making progress here, so the backup process is not taking backup, but sleeping. How to wake it up?

Regards..

'nithin' wrote on '23:

Saleem,

We need to know which query is slow on server.

So please recreate the situation and follow the steps explained below, when it is in waiting state for “Exporting ….”

– Open another SQLyog and connect to same server

– Open Tools->Show->Processlist. It lists all active processes of the server. Click on 'Refresh' button in dialog, and you can find which process is for the Scheduled-backup.

To identify the process, please check the columns 'Command'(which would be “Query”) and 'State' (which would be “Sending data”, or “Writing”) of the table displayed. Please see the attached screen-shot.

– Once you identified the row (process) that refer to the Scheduled-backup thread, copy the 'Info' column (that contains the query executing) and send to us.

– You can also provide the other column status also by sending screen-shot of 'process list' dialog and mark the particular row.

For privacy you could create ticket using below link

http://webyog.com/support/

Regards

[nithin]

Saleem,

Looks like these processes are not related to scheduled-backup. These are three sessions created by SQLyog while connecting to MySQL.

Can you try these,

– Instead of using command prompt, use the scheduled-backup wizard itself and do export for these cases: i)the specific database ii)all databases.

– Do you have an option to import the SQL dump to a local server? If yes, then import and do the export and tell if slowness still persists.(One reason we expect is since you export from live server and many are accessing the tables at same time and some lock on table may have happened).

[Mahesh]

Also, This issue may happen if table(s) are getting locked somehow.

Please change the all tables type to INNODB because MYISAM table(s) uses table level locking.

Try scheduled backup after changing table(s) type to INNOdb.

[Saleem]

Hi Guys,

Changing the table type is not an option in the production environment.

I had the 7.02 version from my friend, uninstalled the existing version and installed the old version. Configured everything and everything is working fine. sja takes the backup of those dbs in 1 minute.

Conclusion: Old version works, new version does not.

Any details?

[peterlaursen]

Are you sure that you are using exactly the same settings with the two versions?  For instance I think we have added the option to  'backup in a single transaction' in between. 

[Mahesh]

Hi Saleem,

I think it will be better idea to have GoTo meeting session.

Please create a ticket from here :

http://webyog.com/support/

Also please attach a job file(s) created with both 7.x and 8.x, Connection type(Direct,HTTP,SSH) and mention which timezone you are in and suitable time to have online meeting session in ticket itself ?

[lcarsonmcphersonr]

hope i am in the right spot

having trouble dumping a DB and putting it back

[Author]

Posts