Just reading the header you put in the question shows something interesting. In fact, the question shows three things:
MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binariesServer version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.- You wanted to load the mysqldump file into
Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1
What a jumble of versions to do this with.
If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.
mysqldump --no-data --all-databases ... > MySQLSchema.sql
This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,
mysqldump --no-create-info --all-databases ... > MySQLData.sql
Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql
BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.
Give it a Try !!!
Interesting problem and I think that I've figured it out.
For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as database/schema names.
Congratulations - it looks like you've spotted a MySQL bug.
Something like this (underscore following number and e) works.
MariaDB [(none)]> create schema 1_e34343_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
which appears to confirm my conclusion.
This does also - i.e. 1ex works, but 1e1 fails.
MariaDB [(none)]> create schema 1ex;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create schema 1e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e1' at line 1
MariaDB [(none)]>
=== Experiments leading me to my conclusion ============
MariaDB [(none)]> create schema 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create schema 5a370227_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> drop schema 5a370227_db;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> create database 5a370227_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> drop database 5a370227_db;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> create database 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100' at line 1
MariaDB [(none)]> create database 100_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 1000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 100000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 1000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 100000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 1000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 100000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 1000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 100000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 1000000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 100000000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 10000000000000000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database 1e0000000000000000000000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000000000000000000000000000_db' at line 1
MariaDB [(none)]> create database 1e000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e000000000000000_db' at line 1
MariaDB [(none)]> create database 1e0000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000_db' at line 1
MariaDB [(none)]> create database 1e4_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_db' at line 1
MariaDB [(none)]> create database 1e4_zx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_zx' at line 1
MariaDB [(none)]>
MariaDB [(none)]> create database 1a4_zx;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
Best Answer
There is old Bug Report about MySQL Workbench connecting to older databases.
Others have come across this
When you google the subject of "mysql bad handshake", you will see two basic issues:
The second post I referred to actually recommends "dummying down" and using an older product (MySQL Administrator for you Old School DBAs like myself).
Main thing to do is to make sure your drivers match the version of Workbench you are using. BTW Don't feel bad about using MySQL 5.1. There are still a lot of die-hards out there that are using (I hope you sitting down), MySQL 3.23.58.
WHAT TO DO
Use Older Versions of MySQL Workbench, Navicat, SquirrelSQL.
Hey, that worked in the second post (Click Here for Those Tools)
Please remember that MySQL 5.1 made allowances for MySQL 4.1 password authentication. Newer ODBC/JDBC drivers may not understand MySQL 5.1's way of authenticating and other network-related things, colloquially called "BAD HANDSHAKE".