MySQL Bugs: #46260: Importing dump file created with 5.1 to 5.4 hangs, with no errors

Bug #46260	Importing dump file created with 5.1 to 5.4 hangs, with no errors
Submitted:	17 Jul 2009 10:34	Modified:	17 Aug 2009 12:02
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.4	OS:	Linux (Ubuntu)
Assigned to:		CPU Architecture:	Any

Description:
As mentioned in how to repeat section.

How to repeat:
1) Created data and dump file with 5.1.

2) Started 5.4 server with mtr. commandline as below

nidhi@nidhi-laptop:~/mysql-builds/mysql-azalea/mysql-test$ ./mysql-test-run.pl --master_port=9306 --slave_port=9308 --vardir=/home/nidhi/mysql-sources/updown_14jul/var/master --mysqld=--innodb_data_file_path=ibdata1:50M:autoextend --mysqld=--max_allowed_packet=256M --user=root --skip-ndb --mysqld=--innodb --mysqld=--default_character_set=utf8 --mysqld=--concurrent_insert=0 --mysqld=--innodb_log_file_size=67108864 --mysqld=--innodb_log_files_in_group=2 --testcase-timeout=10 --suite-timeout=20 --force --start-and-exit alias

Starts server as:

nidhi    10870     1  7 15:43 pts/1    00:00:00 /home/nidhi/mysql-builds/mysql-azalea/libexec/mysqld --no-defaults --basedir=/home/nidhi/mysql-builds/mysql-azalea --character-sets-dir=/home/nidhi/mysql-builds/mysql-azalea/share/mysql/charsets --secure-file-priv=/home/nidhi/mysql-sources/updown_14jul/var/master --log-bin-trust-function-creators --default-character-set=latin1 --tmpdir=/home/nidhi/mysql-sources/updown_14jul/var/master/tmp --connect-timeout=60 --pid-file=/home/nidhi/mysql-sources/updown_14jul/var/master/run/master.pid --port=9306 --socket=/home/nidhi/mysql-sources/updown_14jul/var/master/tmp/master.sock --datadir=/home/nidhi/mysql-sources/updown_14jul/var/master/master-data --log-output=table,file --log=/home/nidhi/mysql-sources/updown_14jul/var/master/log/master.log --log-slow-queries=/home/nidhi/mysql-sources/updown_14jul/var/master/log/master-slow.log --server-id=1 --loose-innodb_data_file_path=ibdata1:10M:autoextend --local-infile --loose-skip-innodb --loose-skip-ndbcluster --key_buffer_size=1M --sort_buffer=256K --max_heap_table_size=1M --ssl-ca=/home/nidhi/mysql-builds/mysql-azalea/mysql-test/std_data/cacert.pem --ssl-cert=/home/nidhi/mysql-builds/mysql-azalea/mysql-test/std_data/server-cert.pem --ssl-key=/home/nidhi/mysql-builds/mysql-azalea/mysql-test/std_data/server-key.pem --innodb_data_file_path=ibdata1:50M:autoextend --max_allowed_packet=256M --innodb --default_character_set=utf8 --concurrent_insert=0 --innodb_log_file_size=67108864 --innodb_log_files_in_group=2 --skip-log-bin --core-file --open-files-limit=1024

3) Try loading the dump (attached dump file).

nidhi@nidhi-laptop:~/mysql-builds/mysql-azalea/mysql-test$ /home/nidhi/mysql-builds/mysql-azalea/bin/mysql --socket=/home/nidhi/mysql-sources/updown_14jul/var/master/tmp/master.sock --user=root --password= --max_allowed_packet=256M --default-character-set=utf8 < /home/nidhi/mysql-sources/extra_60/mysql-test/qa-suite/updown/scenarios/51minor/r/dump.tmp

---hangs 

connecting through client mysql, mysqladmin hangs after that.

nidhi@nidhi-laptop:~/mysql-sources/extra_60/mysql-test/qa-suite/updown/scenarios/51minor/r$ /home/nidhi/mysql-builds/mysql-azalea/bin/mysql --socket=/home/nidhi/mysql-sources/updown_14jul/var/master/tmp/master.sock --user=root

--hangs

File uploaded to ftp://ftp.mysql.com/pub/mysql/upload/ as 
dump.tmp-bug46260.gz

Sorry, but I was not able to repeat this with recent mysql-azalea from bzr. I had started server as (quote from fc -l):

989      rm var/ib_logfile*
...
995      cd /home2/openxs/dbs/azalea/
996      bin/mysqladmin -uroot shutdown
997      bin/mysqld_safe --max_allowed_packet=256M --default-character-set=utf8 --innodb_log_file_size=64M &

and then:

openxs@suse:/home2/openxs/dbs/azalea> bin/mysql -uroot < /home2/openxs/dump.tmp-bug46260
openxs@suse:/home2/openxs/dbs/azalea> echo $?
0
openxs@suse:/home2/openxs/dbs/azalea> tail var/suse.err
091022 22:16:51 [Note] /home2/openxs/dbs/azalea/libexec/mysqld: Shutdown complete

091022 22:16:51 mysqld_safe mysqld from pid file /home2/openxs/dbs/azalea/var/suse.pid ended
091022 22:17:36 mysqld_safe Starting mysqld daemon with databases from /home2/openxs/dbs/azalea/var
091022 22:17:36 [Note] Plugin 'FEDERATED' is disabled.
091022 22:17:36 [Note] Plugin 'ndbcluster' is disabled.
091022 22:17:36  InnoDB: Started; log sequence number 0 70354956
091022 22:17:36 [Note] Event Scheduler: Loaded 7 events
091022 22:17:36 [Note] /home2/openxs/dbs/azalea/libexec/mysqld: ready for connections.
Version: '5.4.4-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

As far as I can see, data were loaded successfully and fast enough:

mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| downgrade_drop_me_db |
| ev_db1               |
| mysql                |
| privdb               |
| procdb               |
| test                 |
| trigdb               |
| trigdb2              |
| typesdb              |
| upgrade_drop_me_db   |
| viewdb               |
| viewtempdb           |
+----------------------+
13 rows in set (0.02 sec)

Please, check if this is actually related to mtr or other options used.

I couldn't repeat too in Windows:

Database changed
ERROR 1298 (HY000) at line 4777 in file: 'c:\tmp\dump.tmp': Unknown or incorrect time zone: 'NULL'
ERROR 1231 (42000) at line 4779 in file: 'c:\tmp\dump.tmp': Variable 'sql_mode' can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 4780 in file: 'c:\tmp\dump.tmp': Variable 'foreign_key_checks' can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 4781 in file: 'c:\tmp\dump.tmp': Variable 'unique_checks' can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 4782 in file: 'c:\tmp\dump.tmp': Variable 'character_set_client' can't be set to the value of 'NULL'
Query OK, 0 rows affected (0.00 sec)

ERROR 1231 (42000) at line 4784 in file: 'c:\tmp\dump.tmp': Variable 'collation_connection' can't be set to the value of 'NULL'
ERROR 1231 (42000) at line 4785 in file: 'c:\tmp\dump.tmp': Variable 'sql_notes' can't be set to the value of 'NULL'
mysql> show variables like "%version%";
+-------------------------+---------------------------+
| Variable_name           | Value                     |
+-------------------------+---------------------------+
| protocol_version        | 10                        |
| version                 | 5.4.4-alpha-Win X64-debug |
| version_comment         | Source distribution       |
| version_compile_machine | unknown                   |
| version_compile_os      | Win64                     |
+-------------------------+---------------------------+
5 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".