ERROR 1616 (HY000) at line 97: DELAYED option not supported for table 'address'

ERROR 1616 (HY000) at line 97: DELAYED option not supported for table ‘address’

I get this error message when I try to import into MySQL. I am using the latest version of SQLBackUpAndFtp. It used to work with previous versions, but not any longer.

Obviously the SQL being generated contains DELAYED INSERT statements that are not supported by MySQL any longer due to deprecation! As far as I understand they have been ignored by the last couple of MySQL versions but produce an error in more recent versions. Here is a link: https://dba.stackexchange.com/questions/160168/mysql-insert-delayed-deprecated-what-now

I have a preliminary solution for this: I edited the generated .sql file and deleted all DELAYED statements from it. Then it worked. Obviously the DELAYED statement is not only deprecated it seems to be completely useless, too.

Can you guys fix this bug please.

Hi Roland,

Please give us some time to check and fix this issue.

Sorry for the inconvenience.

Hi Roland,

Thank you for your logs, we have checked them.

Delayed Insert instruction was added to mysqldump in some older MySQL Server versions. We haven’t added this instruction as it isn’t added in the latest versions of MySQL Server.

If you have any further questions, please let us know.

Hi Alexander,

I do not understand your reply to be honest! Does this mean you will not fix this bug?? How am I supposed to fix it myself? If I use MySQLWorkbench and do the export of the data from the server with it I have no issue to load the database locally. Using your tool it does not work for the reason mentioned above. Hence: There must be something MySQLWorkbench is doing right with the export and something that your tool does wrong.

Just checked the .sql file that MySQLWorkbench generates: It contains normal INSERT statements without the DELAYED option!

According to this it is an option to turn DELAYED inserts on or off. So I guess you would need to set the options properly and it should work:
https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Hi Roland,

SQLBackupAndFTP does not specify the –delayed-insert instruction at all, it should be specified by mysqldump utility. Perhaps in some versions of mysqldump, –delayed-insert instruction is enabled by default.

Could you please specify what version of MySQL Server do you use? Also if you specify a path to your bin folder at the advanced connection settings, please tell us what version of mysqldump do you use?

Sorry for the inconvenience.

Hi, I use phpMyAdmin to connect to the remote server and I don’t specify “Path to MySQL bin folder”.

This is the comment at the top of the dump:
– phpMyAdmin SQL Dump
– version 4.8.4
https://www.phpmyadmin.net/

– Host: localhost:3306
– Generation Time: Mar 13, 2019 at 07:00 PM
– Server version: 5.7.25-0 ubuntu 0.16.04.2
– PHP Version: 7.1.14

Hi Roland_Finke,

Thanks for the details. We thought you use MySQL Server (TCP/IP) connection. Please give us some time to check this issue for phpMyAdmin.

Sorry for the inconvenience.

Hi Roland_Finke,

We have checked this issue with different versions of phpMyAdmin (including 4.8.4) and weren’t able to reproduce the issue. Could you please send us a screenshot of your “phpMyAdmin backup options” window?

Sorry for the inconvenience.

Here you are:

image.png

I tried various configurations; it never made any difference. I always keep getting the warning:

“Backup verify: No INSERT INTO or REPLACE INTO statement in dump file.”

That’s because the statements are all like this: INSERT DELAYED INTO

Hi Roland_Finke,

Thanks for the details, but your screenshot isn’t displayed. Could you please try to download it again?

Sorry for the inconvenience.

Please use Chrome. Then you can see it. With Firefox it is actually not displayed. Can’t log into my account in Firefox because I haven’t memorized my credentials. If I upload it in Chrome again it probably won’t be visible for you in Firefox either. And Chrome is a lot faster than Firefox too.

We use Chrome and still cannot see the screenshot. If it possible, please download your screenshot again.

Sorry for the inconvenience.

Thank you for the screenshot, we have checked your settings but weren’t able to reproduce the issue. It will help us a lot if you provide us with a test access to your phpMyAdmin. To keep the credentials in secrete please send them to support@sqlbackupandftp.com

Sorry for the inconvenience.

Sorry, don’t have a test access for this. So be it as it may … I know that I can use the backup simply be replacing DELAYED with nothing in the .sql file. That must be good enough for the time being …

Thank you for your reply. Please feel free to contact us on any issues or concerns.