help needed restoring crashed mysql
Hello. I have the following problem. I was importing a large database to mysql using mysqldump. Unfortunately this filled up the whole disk, and mysqldump exited with an error that the table it is currently writing to is full. Checking df -h it shows that the disk usage is at 100%. I decided to drop the database I was importing, but rechecking df -h shows that it is still at 100%. I then looked for the cause of this and found a very large file under /var/lib/mysql. I think it is ibdata1. I deleted it and rechecked df -h, but it is stll at 100%. I then stopped mysql and finally the output of df -h was correct. However, when I try to restart mysql it outputs the following errors in the error log; it is in the attached file. Thanks for anyone that could help. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 29 12:51:23 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: infimum not pointed to 29 12:51:23 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex (LOTS OF ZEROES...) ;InnoDB: End of page dump 29 12:56:28 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 may be a freshly allocated page 29 12:56:28InnoDB: Error: trying to access a stray pointer 0x33987ff8 InnoDB: buf pool start is at 0xb3978000, end at 0xb4178000 InnoDB: Probable reason is database corruption or memory InnoDB: corruption. If this happens in an InnoDB database recovery, see InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: how to force recovery. 29 12:56:28 InnoDB: Assertion failure in thread 3067451088 in file ../../../storage/innobase/include/buf0buf.ic line 264 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 29 12:56:28 - mysqld got signal 6 ; 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=16777216 read_buffer_size=131072 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345919 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 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... stack_bottom = (nil) thread_stack 0x3 /usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb763ecbd] /usr/sbin/mysqld(handle_segfault+0x494) [0xb730a854] [0xb7085400] /lib/tls/i686/nosegneg/libc.so.6(abort+0x182) [0xb6d88d42] /usr/sbin/mysqld(page_cur_search_with_match+0x9e3) [0xb755cdb3] /usr/sbin/mysqld(btr_cur_search_to_nth_level+0x5ae) [0xb74f2c8e] /usr/sbin/mysqld(btr_pcur_open_on_user_rec+0x73) [0xb75be6c3] /usr/sbin/mysqld(+0x46fc55) [0xb7512c55] /usr/sbin/mysqld(dict_load_sys_table+0x75) [0xb75195c5] /usr/sbin/mysqld(dict_boot+0xd27) [0xb75bf927] /usr/sbin/mysqld(innobase_start_or_create_for_mysql+0x1299) [0xb7595459] /usr/sbin/mysqld(+0x43e7e6) [0xb74e17e6] /usr/sbin/mysqld(ha_initialize_handlerton(st_plugin_int*)+0x3f) [0xb741611f] /usr/sbin/mysqld(+0x411f2a) [0xb74b4f2a] /usr/sbin/mysqld(plugin_init(int*, char**, int)+0x8a7) [0xb74b8827] /usr/sbin/mysqld(+0x2698d2) [0xb730c8d2] /usr/sbin/mysqld(main+0x1fa) [0xb730ff9a] /lib/tls/i686/nosegneg/libc.so.6(__libc_start_main+0xe6) [0xb6d71bd6] /usr/sbin/mysqld(+0x182d81) [0xb7225d81] 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
Am 29.11.2011 14:08, schrieb Luis Pugoy: Hello. I have the following problem. I was importing a large database to mysql using mysqldump. Unfortunately this filled up the whole disk, and mysqldump exited with an error that the table it is currently writing to is full. Checking df -h it shows that the disk usage is at 100%. I decided to drop the database I was importing, but rechecking df -h shows that it is still at 100%. I then looked for the cause of this and found a very large file under /var/lib/mysql. I think it is ibdata1. ibdata1 does NEVER get smaller, this is normal and a hughe problem in your case, only if you are using innodb_file_per_table which is NOT default would retire the space after drop tables why is this dumb innodb_file_per_table=0 default since MOST PEOPLE have only troubles with it because they can not free space with optimize table with no real benefits? signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
On 29.11.2011, at 5:21, Reindl Harald wrote: ibdata1 does NEVER get smaller, this is normal and a hughe problem in your case, only if you are using innodb_file_per_table which is NOT default would retire the space after drop tables why is this dumb innodb_file_per_table=0 default since MOST PEOPLE have only troubles with it because they can not free space with optimize table with no real benefits? Hi... The logic behind this is probably that without innodb_file_per_table=1 and with several large ibdata files, the space IS freed up when one does optimize table or drop table. The space is freed up inside the database files and can be reused. If we think about it, the database product should only resolve problems of the database space management, not of the OS space management. Then, the user essentially asked InnoDB to keep allocating arbitrary amount of space as needed, ignoring that the OS disk is actually of the limited size. To be correct about it, the user should have stated that the ibdata file should have a firm limit and not autoextend beyond that. This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. Making innodb tablespaces default... well, it still would not liberate the users from thinking whether they want to run with them enabled or not. For example, if I have 1 tables of 100 bytes each, I probably do not want tablespaces. If I have 1% of tables consuming 99% of the space, I would also not want the tablespaces. As for the OP's problem, unless he changed his mind about the need to import, the same amount of space would anyway be consumed. The solution would probably be to find some bigger O Sdisk and copy that ibdata file there. Right? Cheers Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. That's where MySQL(read InnoDB) got stuck actually, it never introduced a powerful datafiles management system, and that is where Oracle excels (as far as being almost a O.S.) with multiple level of abstractions, just think of ASM. It is actually the part of Oracle I like most as well as the really oraclish way to get stats out of it! The 'problem' with MySQL is that it is so easy to start with it that people do not realize that is also a real RDBMS. -- Claudio Making innodb tablespaces default... well, it still would not liberate the users from thinking whether they want to run with them enabled or not. For example, if I have 1 tables of 100 bytes each, I probably do not want tablespaces. If I have 1% of tables consuming 99% of the space, I would also not want the tablespaces. As for the OP's problem, unless he changed his mind about the need to import, the same amount of space would anyway be consumed. The solution would probably be to find some bigger O Sdisk and copy that ibdata file there. Right? Cheers Karen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: help needed restoring crashed mysql
Am 29.11.2011 20:25, schrieb Karen Abgarian: On 29.11.2011, at 5:21, Reindl Harald wrote: why is this dumb innodb_file_per_table=0 default since MOST PEOPLE have only troubles with it because they can not free space with optimize table with no real benefits? The logic behind this is probably that without innodb_file_per_table=1 and with several large ibdata files, the space IS freed up when one does optimize table or drop table. The space is freed up inside the database files and can be reused. well, and if you have this day 2 TB mysql-data and a year later get rid of 1 TB of it they allocated space can be REUSED for innodb but never for any other application If we think about it, the database product should only resolve problems of the database space management, not of the OS space management. the database producht with default settings is the part starting the troubles of os-space-managment and this is idiotic, no other words for this! MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database the user essentially asked InnoDB to keep allocating arbitrary amount of space as needed, ignoring that the OS disk is actually of the limited size. To be correct about it, the user should have stated that the ibdata file should have a firm limit and not autoextend beyond that. yes this case is a user-problem but the cases where ibdata1 is growing becasue ONCE bigger data was stored and never release the allocated space is a design-problem signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
Hi... there is stuff inline there. The logic behind this is probably that without innodb_file_per_table=1 and with several large ibdata files, the space IS freed up when one does optimize table or drop table. The space is freed up inside the database files and can be reused. well, and if you have this day 2 TB mysql-data and a year later get rid of 1 TB of it they allocated space can be REUSED for innodb but never for any other application I did not say it is the right thing to not have an option to shrink the database or do file management. I tried to explain the logic that is probably put into this product. Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. The databases usually either grow, or can grow, or are destroyed. In that regard the product with this feature lacking probably still covers the needs of most. By comparison, Oracle did not provide ability to drop the datafiles until, eh, version 8, I believe, and it was not made easy until version 10. If we think about it, the database product should only resolve problems of the database space management, not of the OS space management. the database producht with default settings is the part starting the troubles of os-space-managment and this is idiotic, no other words for this! I would say inconvenient. As I explained above, the OS space allocation problems that way could be considered a corner case and thus be considered unimportant by MySQL development. Considering the problem of reclaiming 1 terabyte out of 2-terabyte database, one could resolve it with creating a brand new instance followed by export/import of data. It is not that there is no solution, it is inconvenient to use. MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset. but the cases where ibdata1 is growing becasue ONCE bigger data was stored and never release the allocated space is a design-problem Not exactly. A design problem is to build a server in such a way as that adding a feature to remove datafiles would be impossible (without major rebuild). I think this one can be added. I didn't bother to check, but I would be surprised if there isn't already an enhancement request for this
Re: help needed restoring crashed mysql
On Nov 29, 2011, at 11:50 AM, Claudio Nanni wrote: This is not to say that MySQL could not have more of the file management features. For example, the ability to add or remove datafiles on the fly and the ability to detach tablespaces as collections of tables. That's where MySQL(read InnoDB) got stuck actually, it never introduced a powerful datafiles management system, and that is where Oracle excels (as far as being almost a O.S.) with multiple level of abstractions, just think of ASM. It is actually the part of Oracle I like most as well as the really oraclish way to get stats out of it! The 'problem' with MySQL is that it is so easy to start with it that people do not realize that is also a real RDBMS. -- Yes, Oracle has features of the known kind. But, if we consider the wishlist for InnoDB, then on top there would be an ability to add files without stopping database (that is without innodb tablespaces), followed by the ability to control which files are part of the database (information schema of that). It looks like at some point they have decided that the file per table thing will cover all needs. Well, it doesn't. I personally do not like dealing with 50G files should individual table grow to this size. It will be much better managed with a larger collection of smaller files, without innodb tablespaces. It is after I have those I would want the features like removing files or compacting the database.
Re: help needed restoring crashed mysql
Am 30.11.2011 01:11, schrieb Karen Abgarian: MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset if you are working many years with mysql and myisam you normally do not expect this - and no my work depends never really on luck and that is why it look in ALL datadirs of all software i am using and missed the table files known from my isam most peopole DO NOT care about this and not expect that allocated space will not be freed nor makes it any sense to have a whole database-server to dump/import because you get rid of big databases Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. well, so install http://www.dbmail.org/ with replication and offsite-backups of the slave, get rid of your biggest mail-user and think about how useful it is to waste all this space as before multiple times in the backup-storages it did never happen to me - but i heard so many people start whining because the mysql-defaults and these are most pepole which do not have our knowledge to handle this before and in this case also not able to handle dump/import in a production environment signature.asc Description: OpenPGP digital signature
Re: help needed restoring crashed mysql
Hi... and some more stuff inline. Well, I would not base my database design on luck and playing. There should be good awareness of what the features do and what would be the plan to deal with file allocations should the database grow, shrink or somerset if you are working many years with mysql and myisam you normally do not expect this - and no my work depends never really on luck and that is why it look in ALL datadirs of all software i am using and missed the table files known from my isam most peopole DO NOT care about this and not expect that allocated space will not be freed nor makes it any sense to have a whole database-server to dump/import because you get rid of big databases I am not saying there is anything wrong with your experience. I understand that people having worked with MyISAM might have a different mindset. But they at some point did make a decision to use InnoDB. I think this is the time when I would ask myself a question: where is it that my data will be stored. The concept is not difficult to explain. Most people do not expect a gas tank to shrink once the gas is consumed...right? Another piece of logic is that it is not really typical for the databases to lose 50% of its volume. well, so install http://www.dbmail.org/ with replication and offsite-backups of the slave, get rid of your biggest mail-user and think about how useful it is to waste all this space as before multiple times in the backup-storages I didn't have a pleasure to use dbmail. I presume it does something with mail users. Thinking logically, if I got rid of my biggest mail user, I might eventually get another user, even bigger one, which would consume the same space vacated by the deceased user. So why would I want to give up the space then? it did never happen to me - but i heard so many people start whining because the mysql-defaults and these are most pepole which do not have our knowledge to handle this before and in this case also not able to handle dump/import in a production environment If the people do not have the knowledge to do exports/imports, the brand new and cool file management feature will not help them either. Essentially, the instructions how to use that feature are written in the same book as the instructions how to do export/imports. If we consider it as given that people would never read, it is a dead end. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
