mysql importing issue

[Sandor K]

Hi guys,

I am having problem while importing the my backup.sql to the new server.
My sql file is 2.2GB.

Error message:

ERROR 1064 (42000) at line 611: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' smtp; 550-5.1.1 The email account that you tried to reach does\r\n    not exis' at line 1

Old Server Sql version:

+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| innodb_version          | 5.5.41           |
| protocol_version        | 10               |
| slave_type_conversions  |                  |
| version                 | 5.5.41-0+wheezy1 |
| version_comment         | (Debian)         |
| version_compile_machine | x86_64           |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+

New Servcer Sql version:

+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| innodb_version          | 5.5.44           |
| protocol_version        | 10               |
| slave_type_conversions  |                  |
| version                 | 5.5.44-0+deb7u1  |
| version_comment         | (Debian)         |
| version_compile_machine | x86_64           |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+

The versions are slightly different but I don't think that is the problem.

Any suggestions?

Thanks!

 

[twisted1919]

@Sandor K - Seems something has changed through the versions.
Can you paste here the entire line that causes the problem, from the sql file i mean?

 

[Sandor K]

Funny thing that I just exported only that table where the problem was and then imported without problem. It took really long time as it has about 5.5m rows.
For some reason i am not able to import all tables together.

 

[twisted1919]

@Sandor K - This might be because of the configuration, amount of information you have to import and server resources.
I remember once i imported a huge amount of data into a small VPS and the import finished in few hours, but the number of records increased for a few more hours after the import finished, very weird behavior.

 

[Sandor K]

this is a dedicated server with

Intel XEON D-1540 6 cores 2.6 GHz and 32gb ram. during the import not much movement in CPU and memory usage.
Maybe there is a default memory limit in mysql?

 

[twisted1919]

@Sandor K - It might as well be, mysql configuration is hard to get right.
I am always generating the mysql configuration using https://tools.percona.com/
However, the above tool is best when you have just installed mysql.
In your case, you can generate a new configuration based on your server specs and then use that to compare with your own configuration and change one directive at a time, followed by a mysql server restart to make sure you don't change directives that can kill the server.