debian-start with large myisam table
Help!
A big myisam table of mine, res_creators_separate,
chichek:~# ls -l /var/lib/mysql/acis/res_creators_separate.MYD
-rw-rw---- 1 mysql mysql 2385125580 2009-10-16 13:41 /var/lib/mysql/acis/res_creators_separate.MYD
crashes mysql, in some subtle way. It hapens when I run the normal
debian start, i.e. including the lines
echo "Checking for corrupt, not cleanly closed and upgrade needing tables."
(
upgrade_system_tables_if_necessary;
check_root_accounts;
check_for_crashed_tables;
) >&2 &
from /etc/mysql/debian-start. If I comment out these lines,
everything is just fine.
I need a way to do what these commands do, on the command line, with
the server down, with some verbose output.
I have been fighting with this for almost three weeks now. I am at
my wit's end. Here come the gory details of my story.
The problem is limited to mySQL 5.1. I have 5.1.37-2 on squeeze. I
have two test systems, they both have this issue. The production
version runs 5.0, with similar tables, it is fine.
Let us make sure that the table is clean
chichek:/var/lib/mysql/acis# myisamchk -r res_creators_separate
- recovering (with sort) MyISAM-table 'res_creators_separate'
Data records: 522410
- Fixing index 1
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table 'res_creators_separate' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
ok add a flag
chichek:/var/lib/mysql/acis# myisamchk --sort_buffer_size=200M -r res_creators_separate
- recovering (with sort) MyISAM-table 'res_creators_separate'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
Data records: 65401756
New let us start mysql
chichek:~# /etc/init.d/mysql start
/var/log/daemon.log says
Oct 15 15:13:32 chichek mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Oct 15 15:13:32 chichek mysqld: 091015 15:13:32 [Note] Plugin 'FEDERATED' is disabled.
Oct 15 15:13:33 chichek mysqld: 091015 15:13:33 InnoDB: Started; log sequence number 0 44233
Oct 15 15:13:33 chichek mysqld: 091015 15:13:33 [Note] Event Scheduler: Loaded 0 events
Oct 15 15:13:33 chichek mysqld: 091015 15:13:33 [Note] /usr/sbin/mysqld: ready for connections.
Oct 15 15:13:33 chichek mysqld: Version: '5.1.37-2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)
Oct 15 15:13:34 chichek /etc/mysql/debian-start[23061]: Upgrading MySQL tables if necessary.
Looks all fine. Go to bed, let this work for a few hours.
Next morning, when I launch the application that feeds the database,
it gets stuck. I think it can not access res_creators_separate.
There is no error reported by daemon.log. But when I shut down mysql,
chichek:~# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
/var/log/daemon.log says
Oct 16 11:44:45 chichek mysqld: 091016 11:44:45 [Note] /usr/sbin/mysqld: Normal shutdown
Oct 16 11:44:45 chichek mysqld:
Oct 16 11:44:45 chichek mysqld: 091016 11:44:45 [Note] Event Scheduler: Purging the queue. 0 events
Oct 16 11:44:47 chichek mysqld: 091016 11:44:47 [Warning] /usr/sbin/mysqld: Forcing close of thread 5 user: 'debian-sys-maint'
Oct 16 11:44:47 chichek mysqld:
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Looking for 'mysql' as: /usr/bin/mysql
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--socket=/var/run/mysqld/mysqld.sock'
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--socket=/var/run/mysqld/mysqld.sock'
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: /usr/bin/mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'REPAIR TABLE ... '
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: FATAL ERROR: Upgrade failed
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.apu_queue OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.cit_doc_similarity OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.cit_old_sug OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.cit_sug OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.citation_events OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.citations OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.citations_deleted OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.events OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.ft_urls OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.ft_urls_choices OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.institutions OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.names OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.objects OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.records OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.relations OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.res_creators_bulk OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.res_creators_separate
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: error : Table upgrade required. Please do "REPAIR TABLE `res_creators_separate`" or dump/reload to fix it!
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.resources
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: error : Table upgrade required. Please do "REPAIR TABLE `resources`" or dump/reload to fix it!
It found that res_creators_separate was faulty, and tried to repair
it? When I shutdown, it reports it is not finished?
/var/log/daemon.log furthermore says
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.rp_suggestions OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.session_history OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.sid_id_to_handle OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.sid_last_numbers OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.sysprof OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.threads OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: acis.users OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.columns_priv OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.db OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.event OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.func OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.general_log
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Error : You can't use locks with log tables.
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: status : OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.help_category OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.help_keyword OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.help_relation OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.help_topic OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.host OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.ndb_binlog_index OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.plugin OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.proc OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.procs_priv OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.servers OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.slow_log
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Error : You can't use locks with log tables.
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: status : OK
I think this error is harmless. /var/log/daemon.log continues
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.tables_priv OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.time_zone OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.time_zone_leap_second OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.time_zone_name OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.time_zone_transition OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.time_zone_transition_type OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: mysql.user OK
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]:
Oct 16 11:44:47 chichek /etc/mysql/debian-start[23064]: Repairing tables
Oct 16 11:44:47 chichek /etc/mysql/debian-start[8445]: Checking for insecure root accounts.
Oct 16 11:45:33 chichek mysqld: 091016 11:45:33 InnoDB: Starting shutdown...
Oct 16 11:45:46 chichek mysqld: 091016 11:45:46 InnoDB: Shutdown completed; log sequence number 0 44233
Oct 16 11:45:46 chichek mysqld: 091016 11:45:46 [Warning] Forcing shutdown of 1 plugins
Oct 16 11:45:46 chichek mysqld: 091016 11:45:46 [Note] /usr/sbin/mysqld: Shutdown complete
Oct 16 11:45:46 chichek mysqld:
Oct 16 11:45:46 chichek mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Let us start the server again
chichek:~# /etc/init.d/mysql start
/var/log/daemon.log says
Oct 16 11:50:43 chichek mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Oct 16 11:50:43 chichek mysqld: 091016 11:50:43 [Note] Plugin 'FEDERATED' is disabled.
Oct 16 11:50:44 chichek mysqld: 091016 11:50:44 InnoDB: Started; log sequence number 0 44233
Oct 16 11:50:44 chichek mysqld: 091016 11:50:44 [Note] Event Scheduler: Loaded 0 events
Oct 16 11:50:44 chichek mysqld: 091016 11:50:44 [Note] /usr/sbin/mysqld: ready for connections.
Oct 16 11:50:44 chichek mysqld: Version: '5.1.37-2' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)
Oct 16 11:50:45 chichek /etc/mysql/debian-start[8674]: Upgrading MySQL tables if necessary.
same picture, no reporting of problems. Now let's stop it
chichek:~# /etc/init.d/mysql stop
Stopping MySQL database server: mysqldERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
OOPS! It appears that the second attempt crashes the server.
Let's try again
chichek:~# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
Nothing is reported by /var/log/daemon.log
I can now go back to the start, fix my broken table. If I leave out
the debian start lines, the server works just like a charm.
To no avail I have tried to fiddle with the mysql
configurations. This is my current version.
| #
| # The MySQL database server configuration file.
| #
| # You can copy this to one of:
| # - "/etc/mysql/my.cnf" to set global options,
| # - "~/.my.cnf" to set user-specific options.
| #
| # One can use all long options that the program supports.
| # Run program with --help to get a list of available options and with
| # --print-defaults to see which it would actually understand and use.
| #
| # For explanations see
| # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
|
| # This will be passed to all mysql clients
| # It has been reported that passwords should be enclosed with ticks/quotes
| # escpecially if they contain "#" chars...
| # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
| [client]
| port = 3306
| socket = /var/run/mysqld/mysqld.sock
|
| # Here is entries for some specific programs
| # The following values assume you have at least 32M ram
|
| # This was formally known as [safe_mysqld]. Both versions are currently parsed.
| [mysqld_safe]
| socket = /var/run/mysqld/mysqld.sock
| nice = 0
|
| [mysqld]
| #
| # * Basic Settings
| #
| user = mysql
| pid-file = /var/run/mysqld/mysqld.pid
| socket = /var/run/mysqld/mysqld.sock
| port = 3306
| basedir = /usr
| datadir = /var/lib/mysql
| tmpdir = /tmp
| language = /usr/share/mysql/english
| skip-external-locking
| #
| # Instead of skip-networking the default is now to listen only on
| # localhost which is more compatible and is not less secure.
| bind-address = 127.0.0.1
| #
| # * Fine Tuning
| #
| key_buffer = 16M
| max_allowed_packet = 16M
| thread_stack = 128K
| thread_cache_size = 8
| # This replaces the startup script and checks MyISAM tables if needed
| # the first time they are touched
| myisam-recover = BACKUP
| #max_connections = 100
| #table_cache = 64
| #thread_concurrency = 10
| #
| # * Query Cache Configuration
| #
| query_cache_limit = 1M
| query_cache_size = 16M
| #
| # * Logging and Replication
| #
| # Both location gets rotated by the cronjob.
| # Be aware that this log type is a performance killer.
| # As of 5.1 you can enable the log at runtime!
| #log_type = FILE
| #general_log = /var/log/mysql/mysql.log
| #
| # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
| #
| # Here you can see queries with especially long duration
| #log_slow_queries = /var/log/mysql/mysql-slow.log
| #long_query_time = 2
| #log-queries-not-using-indexes
| #
| # The following can be used as easy to replay backup logs or for replication.
| # note: if you are setting up a replication slave, see README.Debian about
| # other settings you may need to change.
| #server-id = 1
| #log_bin = /var/log/mysql/mysql-bin.log
| expire_logs_days = 10
| max_binlog_size = 100M
| #binlog_do_db = include_database_name
| #binlog_ignore_db = include_database_name
| #
| # * InnoDB
| #
| # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
| # Read the manual for more InnoDB related options. There are many!
| #
| # * Security Features
| #
| # Read the manual, too, if you want chroot!
| # chroot = /var/lib/mysql/
| #
| # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
| #
| # ssl-ca=/etc/mysql/cacert.pem
| # ssl-cert=/etc/mysql/server-cert.pem
| # ssl-key=/etc/mysql/server-key.pem
|
I made some changes here
| ### ToK: from http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html
| # You can write your other MySQL server options here
| # ...
| ##innodb_data_home_dir =
| #
| # Data files must be able to hold your data and indexes
| ##innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend
| #
| # Set buffer pool size to 50-80% of your computer's memory,
| # but make sure on Linux x86 total memory usage is < 2GB
| innodb_buffer_pool_size=800M
| innodb_additional_mem_pool_size=20M
| ##innodb_log_group_home_dir = /dr3/iblogs
| #
| # Set the log file size to about 25% of the buffer pool size
| innodb_log_file_size=200M
| innodb_log_buffer_size=8M
| #
| innodb_flush_log_at_trx_commit=1
| innodb_lock_wait_timeout=50
| #
| # Uncomment the next line if you want to use it
| ##innodb_thread_concurrency=5
| ###
|
then again the defaults
| [mysqldump]
| quick
| quote-names
| max_allowed_packet = 16M
|
| [mysql]
| #no-auto-rehash # faster start of mysql but no tab completition
|
| [isamchk]
| key_buffer = 16M
|
| #
| # * IMPORTANT: Additional settings that can override those from this file!
| # The files must end with '.cnf', otherwise they'll be ignored.
| #
| !includedir /etc/mysql/conf.d/
and some more additions
|
| #
| # added by ToK
| #
| sort_buffer_size = 200M
| key_buffer = 160M
| max_allowed_packet = 160M
Thank you for making it until here.
Cheers,
Thomas Krichel http://openlib.org/home/krichel
RePEc:per:1965-06-05:thomas_krichel
skype: thomaskrichel
Reply to: