MySQL Bugs: #44477: It seems like MYSQLDUMP doesnt dump all data

Bug #44477	It seems like MYSQLDUMP doesnt dump all data
Submitted:	25 Apr 2009 15:17	Modified:	5 May 2009 20:34
Reporter:	Martin T	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	5.0.45, insert into, mysqldump

Description:
Hi,

I have the following setup:

* MySQL server 4.0.45 nt community
* Win2003 web server
* a MySQL DB MyISAM tables charset latin1 and danish (it have several tables and data) and 1 single innoDB table
* DOS-prompt
* MySQL ODBC connector driver 5.1

Problem:
If I try to run:

mysqldump -uroot -pmypsword --opt  --triggers --routines --databases mydb > c:\backup\mySQLdumpfile.sql

it runs ok, and VERY fast! Below 15 seconds.
If I then check inside the SQL file afterwards, it looks like it havent dumped alle data, and also in some of the tables there are no "INSERT INTO" statements at all!? (even though it has data originally)

What can this be?
And how to correct the problem?

How to repeat:
look up

PS: Im running the mysqldump command from a normal DOS prompt.

Thank you for the problem report. Please, try to use mysqldump from the newer version, 5.0.77 at least, and inform about the results.

I have now tried for several hours to install an upgrade - ITS SIMPLY IMPOSSIBLE!!

I have tried 5.0.77 and 5.1.34. Both says the cannot start the MySQL service during the setup wizard!

I have tried everything, uninstalled earlier versions, both using Add/Remove Programs and also MySQL Instance Wizard, also stopped webites, also removed services from service-list in Windows, also restarted machine, also removed ALL MySQL keys in regedit.

WHATS GOING ON?

I have tried to find a log-file, dont know exactly which, but I found one .err file in the MySQL Server 5.0\Data folder. Here is some data from it:

--- start ---
090427  1:14:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Page directory corruption: supremum not pointed to
090427  1:14:03  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; asc                                                                                                                                                                                                                                                                                                                                                                                                 ;InnoDB: End of page dump
090427  1:14:04  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page directory corruption: supremum not pointed to
090427  1:14:04  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; asc                                                                                                                                                                                                                                                                                                                                                                                                 ;InnoDB: End of page dump
090427  1:14:04  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
090427  1:14:04 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=0
read_buffer_size=65536
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 32000 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=00000000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
006CF686    mysqld-nt.exe!page_cur_search_with_match()[page0cur.c:307]
006774A7    mysqld-nt.exe!btr_cur_search_to_nth_level()[btr0cur.c:500]
006AFB3E    mysqld-nt.exe!btr_pcur_open_on_user_rec()[btr0pcur.c:549]
006A2355    mysqld-nt.exe!dict_load_indexes()[dict0load.c:604]
006A3164    mysqld-nt.exe!dict_load_sys_table()[dict0load.c:1023]
006B87E0    mysqld-nt.exe!dict_boot()[dict0boot.c:378]
006657A9    mysqld-nt.exe!innobase_start_or_create_for_mysql()[srv0start.c:1462]
004447A2    mysqld-nt.exe!innobase_init()[ha_innodb.cc:1428]
0044B23D    mysqld-nt.exe!ha_init()[handler.cc:481]
004B801E    mysqld-nt.exe!init_server_components()[mysqld.cc:3424]
004BBE45    mysqld-nt.exe!win_main()[mysqld.cc:3800]
004BC20B    mysqld-nt.exe!mysql_service()[mysqld.cc:3960]
006DF00F    mysqld-nt.exe!_threadstart()[thread.c:196]
77E6482F    kernel32.dll!GetModuleHandleA()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!
090427  1:15:36 [ERROR] Default storage engine (InnoDB) is not available
090427  1:15:36 [ERROR] Aborting

090427  1:15:36 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!
090427  1:23:10 [ERROR] Default storage engine (InnoDB) is not available
090427  1:23:10 [ERROR] Aborting

090427  1:23:10 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!
090427 15:29:41 [ERROR] Default storage engine (InnoDB) is not available
090427 15:29:41 [ERROR] Aborting

090427 15:29:41 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete

--- end ---

Could You use that for something?

PS: All my tables are MyISAM except 1 that is InnoDB.
When I install MySQL server, I of course therefore dont choose InnoDB as my primary db engine.

Thank you for the feedback.

Error shown looks like misconfiguration. Please check your data directory and configuration file in case if Config Wizard changed it.

See also bug #15770 and bug #18191 about very same wizard problem.

It still doesnt work!
Its impossible to upgrade!

I have now tried to change the size in the config file and also tried to delete all logfiles. Nothing helps! The wizard keeps on saying Could not start the service.

If I, after running the install wizard, then go to system services, I can see that It HAVE installed the service, but its not started!

If I try to start it within Windows there, it pops up saying:

Services
Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.

In the System Evnt Log it says:

Default storage engine (InnoDB) is not available
Aborting

Thank you for the feedback.

> In the System Evnt Log it says:
>
> Default storage engine (InnoDB) is not available
> Aborting

Please open configuration file, find InnoDB-related options and either correct paths, or comment default-storage-engine option in case if you are not going to use InnoDB.

Its SO stupid!

Because every time I change something in the my.ini file, it just gets removed again to default when I run the Server Config Wizard!

It happens if I change the log file size from 50 to 10m
and it happens when I comment out the default db engine innodb.

What should I do?
Its such a standard thing to do and everybody have to do it (install the MySQL server), so why is such simple task not tested better and removed all these beginner errors.. I really cannot understand it...

It still says cannot start the service (of course)

Thank you for the feedback.

There is verified bug #42820 about this problem with Config Wizard. Currently you can just skip configuration: you have everything configured already.

Alternatively to check original problem you can search error log in case if it contains some information or even upload compressed database directory to our FTP server as described in "Files" section. But I still recommend to upgrade, because many bugs were fixed since version 5.0.45 and there is huge chance problem solved already.

AHH!!

Why is nobody writing anywhere on the internet that THE SOLUTION IS:

* NOT to use the Wizard at all! It "destroys" the my.ini file so it doesnt work!
* ONLY chance the my.ini manually in an editor and put a # in front of the default innodb engine line
* after that ONLY start the MySQL service MANUALLY in the Windows Services list!

THEN it finally works! ;o)

I have tried SO many solutions on the internet foras, but nowhere is the above KEY information.. *s*

OK, now I will try if the dump works..

I just wonder how to chance the user & password on the db now, when its not allowed to use the config wizard..?

FINALLY! ;o))))

It works!

The MYSQLDUMP.EXE now seems to return all the INSERT INTO statements needed.

This is done by version: 5.0.77

Thanx! :)

PS: If using EditPlus as the editor, the data looks odd, and it seems like there is no INSERT INTO lines for big tables, and all the data is written onto itself as many letters uppon each other later down in the file.

However.. Using default Notepad shows the sql dump alle fine with the INSERT INTO's correctly :)

Thank you for the feedback.

Report closed as "Can't repeat".

The InnoDB redo log file size problem is Bug#13494.
The mysqldump formatting issue seems to be an MS-DOS or Windows problem, excepting CR+LF for line termination instead of plain LF.