[Maria-discuss] Why MyISAM as default engine for system tables ?
Hello, A few weeks ago, we got the following fatal error when starting mariadb: [Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot. Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ? Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically. Julien
Am 06.06.2018 um 12:38 schrieb Julien Muchembled:
A few weeks ago, we got the following fatal error when starting mariadb:
[Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting
Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot.
Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?
Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically.
which version are you suing? AFAIK in the meantime InnoDB is the default but keep in mind we have machines where innodb is even not built at all and the tablespace stuff has terrible sideeffects - backup/recovery of MyISAM databasees - shutdown the service, rsync the folder and you are done
"why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?" Probably because of the time required to implement and test this. To be honest you were probably just very unlucky. Generally the MySQL db receives few writes so the chances of corruption are very small. I can honestly say I've never had this issue in over 10 years of working with MySQL/MariaDB. As I recall the documentation does warn you not to change the mysql table to another other engine. Has anyone else tried? Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Julien Muchembled Sent: 06 June 2018 12:38 To: Maria Discuss <maria-discuss@lists.launchpad.net> Cc: Robin Sébastien <seb@nexedi.com> Subject: [Maria-discuss] Why MyISAM as default engine for system tables ? Hello, A few weeks ago, we got the following fatal error when starting mariadb: [Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot. Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ? Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically. Julien _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Last time i had a innodb problem i changed all innodb to one file per table 2018-06-06 9:11 GMT-03:00 <Rhys.Campbell@swisscom.com>:
"why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?"
Probably because of the time required to implement and test this. To be honest you were probably just very unlucky. Generally the MySQL db receives few writes so the chances of corruption are very small. I can honestly say I've never had this issue in over 10 years of working with MySQL/MariaDB.
As I recall the documentation does warn you not to change the mysql table to another other engine. Has anyone else tried?
Rhys
-----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell= swisscom.com@lists.launchpad.net] On Behalf Of Julien Muchembled Sent: 06 June 2018 12:38 To: Maria Discuss <maria-discuss@lists.launchpad.net> Cc: Robin Sébastien <seb@nexedi.com> Subject: [Maria-discuss] Why MyISAM as default engine for system tables ?
Hello,
A few weeks ago, we got the following fatal error when starting mariadb:
[Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting
Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot.
Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?
Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically.
Julien
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
This doesn't touch the mysql db. They would still be myisam. You have to explicitly do an ALTER to innodb I think. From: Roberto Spadim [mailto:roberto@spadim.com.br] Sent: 06 June 2018 16:21 To: Campbell Rhys, INI-ONE-SCC-MVS-IMQ <Rhys.Campbell@swisscom.com> Cc: jm@nexedi.com; Maria Discuss <maria-discuss@lists.launchpad.net>; seb@nexedi.com Subject: Re: [Maria-discuss] Why MyISAM as default engine for system tables ? Last time i had a innodb problem i changed all innodb to one file per table 2018-06-06 9:11 GMT-03:00 <Rhys.Campbell@swisscom.com<mailto:Rhys.Campbell@swisscom.com>>: "why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?" Probably because of the time required to implement and test this. To be honest you were probably just very unlucky. Generally the MySQL db receives few writes so the chances of corruption are very small. I can honestly say I've never had this issue in over 10 years of working with MySQL/MariaDB. As I recall the documentation does warn you not to change the mysql table to another other engine. Has anyone else tried? Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell<mailto:maria-discuss-bounces%2Brhys.campbell>=swisscom.com@lists.launchpad.net<mailto:swisscom.com@lists.launchpad.net>] On Behalf Of Julien Muchembled Sent: 06 June 2018 12:38 To: Maria Discuss <maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net>> Cc: Robin Sébastien <seb@nexedi.com<mailto:seb@nexedi.com>> Subject: [Maria-discuss] Why MyISAM as default engine for system tables ? Hello, A few weeks ago, we got the following fatal error when starting mariadb: [Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot. Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ? Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically. Julien _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
2018-06-06 17:21 GMT+03:00 Roberto Spadim <roberto@spadim.com.br>:
Last time i had a innodb problem i changed all innodb to one file per table
Unfortunately, there still is the single point of failure for InnoDB: the system tablespace. It contains the InnoDB data dictionary and the state of the transaction system. If it becomes corrupted, you can be really out of luck. You can try to import your .ibd files to an empty server instance and hope for the best. I hope that some day we will be able to eliminate it in MariaDB, and have a transactional, crash-safe data dictionary implemented above the storage engine layer. At the minimum, create/drop/rename operations of .frm files would be logged like transactions. I have written down some plans regarding this: https://jira.mariadb.org/browse/MDEV-11633 -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
Am 06.06.2018 um 16:21 schrieb Roberto Spadim:
Last time i had a innodb problem i changed all innodb to one file per table
which don't help anything given the shared tablespace, that below is from a crash in 2009 and all that "helpful" pages don't work at all after you have deleted the #file weeks later since it had a old timestamp and so looked as a orphaned tempfile the only thing you gain with per-table is that you can shrink/optimize the tables instead never reclaim space after 20 GB was deleted and that is why i don't want the system-tables as innodb at all, they typically don't crash because few to no write access and even if the restore is a simple rsync from one of the rsnapshot-backups 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: The error means the system cannot find the path specified. 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: The error means the system cannot find the path specified. 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: Could not find a valid tablespace file for `dbmail/#sql2-704-271`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2018-06-06 17:45:27 140034181385856 [Warning] InnoDB: Ignoring tablespace for `dbmail`.`#sql2-704-271` because it could not be opened. 2018-06-06 17:45:27 140034181385856 [Note] InnoDB: 128 out of 128 rollback segments are active. 2018-06-06 17:45:27 140034181385856 [Note] InnoDB: Creating shared tablespace for temporary tables 2018-06-06 17:45:27 140034181385856 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2018-06-06 17:45:27 140034181385856 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2018-06-06 17:45:27 140034181385856 [Note] InnoDB: Waiting for purge to start 2018-06-06 17:45:27 140034181385856 [Note] InnoDB: 5.7.22 started; log sequence number 2250847037053 2018-06-06 17:45:27 140033147254528 [Note] InnoDB: Loading buffer pool(s) from /Volumes/dune/mysql_replication/ib_buffer_pool 2018-06-06 17:45:27 140033147254528 [Note] InnoDB: Cannot open '/Volumes/dune/mysql_replication/ib_buffer_pool' for reading: No such file or directory 2018-06-06 17:45:27 140034181385856 [Note] Server socket created on IP: '0.0.0.0'. 2018-06-06 17:45:27 140034181385856 [Note] Reading of all Master_info entries succeded
2018-06-06 18:48 GMT+03:00 Reindl Harald <h.reindl@thelounge.net>:
2018-06-06 17:45:27 140034181385856 [ERROR] InnoDB: Could not find a valid tablespace file for `dbmail/#sql2-704-271`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
MariaDB 10.3 would drop #sql tables inside InnoDB at startup, so you should not need to do that manually. That was fixed in MDEV-14585. I felt that to do this safely, we also need MDEV-14717 (making RENAME TABLE crash-safe inside InnoDB), which required an undo log format change. That one was not possible in a GA version, so I did both in 10.3. I don't think that having system tablespaces in InnoDB would be a good option. Some users might run with MyRocks instead of InnoDB, for example. The Aria engine should always be there, because it can be used for temporary storage during query execution. -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation
Note that several system tables in "mysql" schema cannot have InnoDB as storage engine due to explicit prohibition in the InnoDB code (see https://github.com/MariaDB/server/blob/10.4/storage/innobase/row/row0mysql.c...) and due to lots of other code throughout the codebase assuming that the system tables are not transactional. MySQL has fixed that only in 8.0. I'd say the question of why system tables don't have Aria storage engine is still valid though. On Wed, Jun 6, 2018 at 5:11 AM <Rhys.Campbell@swisscom.com> wrote:
"why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?"
Probably because of the time required to implement and test this. To be honest you were probably just very unlucky. Generally the MySQL db receives few writes so the chances of corruption are very small. I can honestly say I've never had this issue in over 10 years of working with MySQL/MariaDB.
As I recall the documentation does warn you not to change the mysql table to another other engine. Has anyone else tried?
Rhys
-----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Julien Muchembled Sent: 06 June 2018 12:38 To: Maria Discuss <maria-discuss@lists.launchpad.net> Cc: Robin Sébastien <seb@nexedi.com> Subject: [Maria-discuss] Why MyISAM as default engine for system tables ?
Hello,
A few weeks ago, we got the following fatal error when starting mariadb:
[Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting
Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot.
Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ?
Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically.
Julien
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (6)
-
Julien Muchembled -
Marko Mäkelä -
Pavel Ivanov -
Reindl Harald -
Rhys.Campbell＠swisscom.com -
Roberto Spadim