Carlos,
ok, this makes sense.
InnoDB: 2 transaction(s) which must be rolled back or cleaned up InnoDB: in total 2 row operations to undo InnoDB: Trx id counter is 0 44866816 041126 13:59:13 InnoDB: Error: page 0 log sequence number 1 3814617350 InnoDB: is in the future! Current system log sequence number 0 43634. InnoDB: Your database may be corrupt. InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 44866371, 1 rows to undo041126 13:59:13 InnoDB: Fatal error: cannot allocate 4294964152 bytes of InnoDB: memory with malloc!
The tablespace is corrupt, therefore the rollback crashes. Try to set:
innodb_force_recovery=4
in your my.cnf or my.ini.
Then, if you have the .frm files for the tables (I hope your customer did not delete them, too), there is a good chance that you can dump all the rows in your tables, or almost all.
http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
If you do not have the .frm files, you can try creating the tables as empty in another MySQL installation, and copy the .frm files from there to this corrupt database.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
----- Alkuperäinen viesti ----- Lähettäjä: "Carlos Augusto" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Lähetetty: Friday, November 26, 2004 6:11 PM
Aihe: Re: Innodb Corruption with error logs now.
Well look what i did: I changed innodb_log_file_size as you asked me for 5M and then i tryed to start mysqld again then i it doesnt run. And the output at err.log was this one:
I will remember that ibdata1 is my old corrputed data. And iblogile1 is a new iblog because the iblogfile corresponding to the old ibdata1 was deleted by someone here. -----------------begin result InnoDB: Error: auto-extending data file c:\MySQL\server\data\ibdata1 is of a different size InnoDB: 305792 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 384000 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041126 13:58:12 [ERROR] Can't init databases 041126 13:58:12 [ERROR] Aborting 041126 13:58:46 [Note] mysqld: Shutdown complete --------------------end result
So i saw that this problem was in my.ini(at c:\windows) and i changed to this:innodb_data_file_path = ibdata1:3000M:autoextend then i tryied to run again the daemon and i get the following results ---------result--------------
041126 13:59:13 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...
041126 13:59:13 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
041126 13:59:13 InnoDB: Error: page 7 log sequence number 1 2489338706
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 2 log sequence number 1 2489343508
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 11 log sequence number 1 2489345137
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 5 log sequence number 1 2476307008
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 6 log sequence number 1 2759486042
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 45 log sequence number 1 2489342324
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 1943 log sequence number 1 40742737
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 98320 log sequence number 1 3749111878
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 98305 log sequence number 1 2489339022
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 3587 log sequence number 0 486631928
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 3619 log sequence number 1 83786313
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 49198 log sequence number 1 3818468379
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
041126 13:59:13 InnoDB: Error: page 49153 log sequence number 1 2476605300
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
InnoDB: 2 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 2 row operations to undo
InnoDB: Trx id counter is 0 44866816
041126 13:59:13 InnoDB: Error: page 0 log sequence number 1 3814617350
InnoDB: is in the future! Current system log sequence number 0 43634.
InnoDB: Your database may be corrupt.
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 44866371, 1 rows to undo041126
13:59:13 InnoDB: Fatal error: cannot allocate 4294964152 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 69489132 bytes. Operating system errno: 8
InnoDB: Cannot continue operation!
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: We now intentionally generate a seg fault so that
InnoDB: on Linux we get a stack trace.
041126 13:59:13 [ERROR] mysqld: Got signal 11. Aborting!
041126 13:59:13 [ERROR] Aborting
041126 13:59:13 [Note] mysqld: Shutdown complete
---------result--------------
Ill paste again my config for helping you try to help me solve this problem.
-------my.ini-------- # CLIENT SECTION [client] port=3306 # SERVER SECTION # [mysqld] innodb_force_recovery = 1 skip-networking port=3306 basedir=C:/MySQL/server/ datadir=C:/MySQL/server/Data/ default-character-set=latin1 default-storage-engine=INNODB max_connections=100 query_cache_size=100M table_cache=256 tmp_table_size=103M thread_cache=8 #*** MyISAM Specific options myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=205M key_buffer_size=157M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K #*** INNODB Specific options *** innodb_data_home_dir = c:/MySQL/server/data/ innodb_data_file_path = ibdata1:3000M:autoextend innodb_log_group_home_dir = c:/MySQL/server/data/ innodb_log_arch_dir = c:/MySQL/server/data/ innodb_buffer_pool_size = 32M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 ------my.ini--------- I put my.ini at c:\windows\ that is my windir.
Thanks for attention
On Thu, 25 Nov 2004 22:37:44 +0200, Heikki Tuuri <[EMAIL PROTECTED]> wrote:Carlos,
mysqld is not reading your my.ini.
Look: you specified innodb_log_file_size as 61M, but InnoDB only created the
default 5M logfiles that it creates if the user does not specify any log
file size!
I tested crashing mysqld, and deleting the ib_logfiles. I got the output pasted below.
http://dev.mysql.com/doc/mysql/en/InnoDB_configuration.html
"
Where to specify options on Windows? The rules for option files on Windows
are as follows:
Only one of `my.cnf' or `my.ini' should be created.
The `my.cnf' file should be placed in the root directory of the `C:' drive.
The `my.ini' file should be placed in the WINDIR directory; for example,
`C:\WINDOWS' or `C:\WINNT'. You can use the SET command at the command
prompt in a console window to print the value of WINDIR:
C:\> SET WINDIR
windir=C:\WINNT
If your PC uses a boot loader where the `C:' drive is not the boot drive, your only option is to use the `my.ini' file. "
Regards,
Heikki
Version: '4.1.8-log' socket: '/home/heikki/bugsocket' port: 3307 Source
distr
ibution
Killed
<I deleted ib_logfiles here>
[EMAIL PROTECTED]:~/mysql-4.1/sql> ./mysqld 041125 22:02:24 InnoDB: Log file /home/heikki/data/ib_logfile0 did not exist: n ew to be created InnoDB: Setting log file /home/heikki/data/ib_logfile0 size to 56 MB InnoDB: Database physically writes the file full: wait... 041125 22:02:27 InnoDB: Log file /home/heikki/data/ib_logfile1 did not exist: n ew to be created InnoDB: Setting log file /home/heikki/data/ib_logfile1 size to 56 MB InnoDB: Database physically writes the file full: wait... 041125 22:02:33 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... 041125 22:02:33 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 49164. InnoDB: Doing recovery: scanned up to log sequence number 0 49164 041125 22:02:33 InnoDB: Error: page 7 log sequence number 0 46889119 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 1 log sequence number 0 46911058 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 2 log sequence number 0 46910935 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 11 log sequence number 0 37487932 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 5 log sequence number 0 46890338 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 6 log sequence number 0 36678493 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 45 log sequence number 0 46922676 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 54 log sequence number 0 36678493 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 102 row operations to undo InnoDB: Trx id counter is 0 2304 041125 22:02:33 InnoDB: Error: page 0 log sequence number 0 46910935 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 1834, 102 rows to undo041125 22:02:33 InnoDB : Error: page 9 log sequence number 0 37488609 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 8 log sequence number 0 36663829 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 10 log sequence number 0 37488587 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 12 log sequence number 0 37487927 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 55 log sequence number 0 46899852 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 56 log sequence number 0 46857610 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 57 log sequence number 0 46834312 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 58 log sequence number 0 46910935 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 59 log sequence number 0 44227717 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 60 log sequence number 0 43496540 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 61 log sequence number 0 44617317 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 62 log sequence number 0 46875148 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 63 log sequence number 0 42385768 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 513 log sequence number 0 4800527 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt. 041125 22:02:33 InnoDB: Error: page 512 log sequence number 0 4665622 InnoDB: is in the future! Current system log sequence number 0 49164. InnoDB: Your database may be corrupt.
InnoDB: Rolling back of trx id 0 1834 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Last MySQL binlog file position 0 23024529, file name
./binlog.000002
041125 22:02:33 InnoDB: Flushing modified pages from the buffer pool...
041125 22:02:34 InnoDB: Started; log sequence number 0 49164
041125 22:02:34 [Warning] mysql.user table is not updated to new password
format
; Disabling new password usage until mysql_fix_privilege_tables is run
./mysqld: ready for connections.
Version: '4.1.8-log' socket: '/home/heikki/bugsocket' port: 3307 Source
distr
ibution
----- Original Message ----- From: "Carlos Augusto" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, November 25, 2004 8:59 PM Subject: Re: Innodb Corruption with error logs now.
> ib_logfile is different from ibdata1.=20 > Just because i get the database without Ib_logfiles... > > I=B4m sorry, i=B4m sending now my.cnf and what is in /data directory
>
> Dir:
> 11/25/2004 04:51 PM <DIR> .
> 11/25/2004 04:51 PM <DIR> ..
> 11/15/2004 11:31 AM 5,010,096,128 ibdata1
> 11/25/2004 04:51 PM 5,242,880 ib_logfile0
> 11/25/2004 04:50 PM 5,242,880 ib_logfile1
> 11/25/2004 04:43 PM 3,542 mibhweb01.err
> 11/25/2004 03:17 PM <DIR> mysql
> 11/25/2004 04:34 PM 95 mysql.err
> 11/25/2004 03:17 PM <DIR> test
> 5 File(s) 5,020,585,525 bytes
>
> ---my.ini----
>
> [client]
>
> port=3D3306
>
>
>
> innodb_force_recovery =3D 2
> skip-networking
>
>
> port=3D3306
>
> basedir=3DC:/MySQL/server/
>
> datadir=3DC:/MySQL/server/Data/
>
>
> default-character-set=3Dlatin1
>
> default-storage-engine=3DINNODB
>
>
> max_connections=3D100
>
>
> query_cache_size=3D100M
>
> # section [mysqld_safe]
> table_cache=3D256
>
> tmp_table_size=3D103M
>
>
> thread_cache=3D8
>
> #*** MyISAM Specific options
>
>
> myisam_max_sort_file_size=3D100G
>
> myisam_max_extra_sort_file_size=3D100G
>
> myisam_sort_buffer_size=3D205M
>
> key_buffer_size=3D157M
>
> read_buffer_size=3D64K
> read_rnd_buffer_size=3D256K
>
> sort_buffer_size=3D256K
>
>
> #*** INNODB Specific options ***
>
> #skip-innodb
>
> innodb_additional_mem_pool_size=3D7M
>
> innodb_flush_log_at_trx_commit=3D1
>
> innodb_log_buffer_size=3D4M
>
> innodb_buffer_pool_size=3D305M
>
> innodb_log_file_size=3D61M
>
> innodb_thread_concurrency=3D10
> ---end my.ini---
>
>
> On Thu, 25 Nov 2004 20:15:43 +0200, Heikki Tuuri
> <[EMAIL PROTECTED]> wrote:
>> Carlos,
>>=20
>> when filing bug reports for any software, not just MySQL, you should
>> prov=
> ide
>> the full information. You should not try to pick the relevant material
>> yourself.
>>=20
>> What do you have in your my.cnf file? What does the '/data' directory
>> contain?
>>=20
>> The errors below look like you would have at least two ibdata files, >> with
>> conflicting lsn stamps in them. InnoDB refuses to init the log files
>> beca=
> use
>> the data files do not match.
>>=20
>> Regards,
>>=20
>> Heikki
>>=20
>> ----- Alkuper=E4inen viesti -----
>> L=E4hett=E4j=E4: "Carlos Augusto" <[EMAIL PROTECTED]>
>> Vastaanottaja: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
>> L=E4hetetty: Thursday, November 25, 2004 8:01 PM
>> Aihe: Innodb Corruption with error logs now.
>>=20
>>=20
>>=20
>>=20
>> Well, let me begin again..
>>=20
>> I=B4m new in a corporation and my jobs is deal with mysql.
>>=20
>> We had, running mysqld 4.1.7 and using Innodb for a database of
>> 4.7GB~. But some time inserting data into it, the server crashed.
>>=20
>> So i get the server do analyze it.
>>=20
>> When i get to the server the mysqld(config-file,errorfile)was ready
>> removed and only ibdata1 file was in hd.
>> I installed mysqld now and I tryed to put ibdata1 into /data files.
>> When i did this i started the daemon and i get some errors( Ill paste
>> all errors at the end of the post) . Then i tryed to add into my.ini
>> the following parameter:
>> innodb_force_recovery =3D1. And i get new errors. I tryed change the
>> value for 2, and 3 respectively.
>> The only thing i=B4ve changed at my.ini was this one above.
>>=20
>> Now i=B4m pasting the FULL content of errorlog.
>>=20
>> I aprecciate your help and thanks for attention,
>>=20
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3DBegin Error >> log=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D
>>=20
>> ----> I pasted the ibdata1 at /data directory
>> 041125 15:22:18 InnoDB: Log file .\ib_logfile0 did not exist: new to >> be
>> created
>> InnoDB: Setting log file .\ib_logfile0 size to 5 MB
>> InnoDB: Database physically writes the file full: wait...
>> 041125 15:22:18 InnoDB: Log file .\ib_logfile1 did not exist: new to >> be
>> created
>> InnoDB: Setting log file .\ib_logfile1 size to 5 MB
>> InnoDB: Database physically writes the file full: wait...
>> InnoDB: Cannot initialize created log files because
>> InnoDB: data files are corrupt, or new data files were
>> InnoDB: created when the database was started previous
>> InnoDB: time but the database was not shut down
>> InnoDB: normally after that.
>> 041125 15:22:18 [ERROR] Can't init databases
>> 041125 15:22:18 [ERROR] Aborting
>> 041125 15:22:18 [Note] mysqld: Shutdown complete
>>=20
>> ----> the part bellow i tryed to use innodb_force_recovery =3D 1
>> InnoDB: No valid checkpoint found.
>> InnoDB: If this error appears when you are creating an InnoDB >> database,
>> InnoDB: the problem may be that during an earlier attempt you managed
>> InnoDB: to create the InnoDB data files, but log file creation failed.
>> InnoDB: If that is the case, please refer to
>> InnoDB: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html
>> 041125 15:27:14 [ERROR] Can't init databases
>> 041125 15:27:14 [ERROR] Aborting
>>=20
>> 041125 15:27:14 [Note] mysqld: Shutdown complete
>>=20
>> ----> the part bellow i tryed to use innodb_force_recovery =3D 2
>> InnoDB: No valid checkpoint found.
>> InnoDB: If this error appears when you are creating an InnoDB >> database,
>> InnoDB: the problem may be that during an earlier attempt you managed
>> InnoDB: to create the InnoDB data files, but log file creation failed.
>> InnoDB: If that is the case, please refer to
>> InnoDB: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html
>> 041125 15:28:54 [ERROR] Can't init databases
>> 041125 15:28:54 [ERROR] Aborting
>>=20
>> 041125 15:28:54 [Note] mysqld: Shutdown complete
>>=20
>> ----> the part bellow i tryed to use innodb_force_recovery =3D 3
>>=20
>> InnoDB: No valid checkpoint found.
>> InnoDB: If this error appears when you are creating an InnoDB >> database,
>> InnoDB: the problem may be that during an earlier attempt you managed
>> InnoDB: to create the InnoDB data files, but log file creation failed.
>> InnoDB: If that is the case, please refer to
>> InnoDB: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html
>> 041125 15:41:04 [ERROR] Can't init databases
>> 041125 15:41:04 [ERROR] Aborting
>>=20
>> 041125 15:41:04 [Note] mysqld: Shutdown complete
>>=20
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3DEnd Error
>> log=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D
>>=20
>> Thanks
>> Carlos
>>=20
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/[EMAIL PROTECTED]
> om
>>=20
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
