| Bug #46542 | mysql.events OK, but information_schema.event corrupted | ||
|---|---|---|---|
| Submitted: | 4 Aug 2009 12:23 | Modified: | 4 Aug 2009 13:15 |
| Reporter: | Michael Bladowski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S1 (Critical) |
| Version: | 5.1.37-log | OS: | Linux (debian 5.0) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | corrupted, Event, events | ||
[4 Aug 2009 12:53]
Michael Bladowski
here is my dump of mysql.event:
-- MySQL dump 10.13 Distrib 5.1.37, for pc-linux-gnu (i686)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 5.1.37-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `event`
--
DROP TABLE IF EXISTS `event`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `event` (
`db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`name` char(64) NOT NULL DEFAULT '',
`body` longblob NOT NULL,
`definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`execute_at` datetime DEFAULT NULL,
`interval_value` int(11) DEFAULT NULL,
`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_executed` datetime DEFAULT NULL,
`starts` datetime DEFAULT NULL,
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
`comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`originator` int(10) unsigned NOT NULL,
`time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`body_utf8` longblob
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2009-08-04 14:52:52
[4 Aug 2009 13:03]
Valeriy Kravchuk
Your even table does not have primary key: PRIMARY KEY (`db`,`name`) while it should... Is it a fresh installation of 5.1.37 or you had upgraded from some older version?
[4 Aug 2009 13:15]
Michael Bladowski
sorry sorry sorry.... i had an old table structure of my event table ;-((( that was the problem... ;(
Description: 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=8388608 read_buffer_size=262144 max_used_connections=1 max_threads=151 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 125121 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x8b4d610 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 = 0xb4a4e3c8 thread_stack 0x30000 /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x21)[0x84dbe61] /usr/local/mysql/bin/mysqld(handle_segfault+0x381)[0x8202cf1] [0xb7f9c420] /usr/local/mysql/bin/mysqld(_ZN19Event_db_repository12create_eventEP3THDP16Event_parse_datac+0xbe)[0x83745be] /usr/local/mysql/bin/mysqld(_ZN6Events12create_eventEP3THDP16Event_parse_datab+0x121)[0x8375c71] /usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x5ec2)[0x8218772] /usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x340)[0x821a280] /usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x11d8)[0x821b468] /usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0xe0)[0x821bce0] /usr/local/mysql/bin/mysqld(handle_one_connection+0x253)[0x820c613] /lib/libpthread.so.0[0xb7f7ef50] /lib/libc.so.6(clone+0x5e)[0xb7e905ce] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8b96e70 = CREATE EVENT `event01` ON SCHEDULE AT '2009-08-04 13:24:04' + INTERVAL 1 HOUR DO BEGIN select now(); END thd->thread_id=2 thd->killed=NOT_KILLED 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. 090804 14:17:39 mysqld_safe Number of processes running now: 0 090804 14:17:39 mysqld_safe mysqld restarted 090804 14:17:39 [Warning] The syntax '--log' is deprecated and will be removed in MySQL 7.0. Please use '--general_log'/'--general_log_file' instead. 090804 14:17:39 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090804 14:17:39 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: Last MySQL binlog file position 0 8307, file name ./mysql-bin.000074 090804 14:17:39 InnoDB: Started; log sequence number 0 605732050 090804 14:17:39 [Note] Recovering after a crash using mysql-bin 090804 14:17:39 [Note] Starting crash recovery... 090804 14:17:39 [Note] Crash recovery finished. 090804 14:17:39 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information in file: '/tmp/#sql_1703_0.frm' 090804 14:17:39 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information in file: '/tmp/#sql_1826_0.frm' 090804 14:17:39 [Note] Event Scheduler: Loaded 5 events 090804 14:17:39 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.1.37-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL) How to repeat: select * from information_schema.events -> ERROR 1548 (HY000): Cannot load from mysql.event. The table is probably corrupted or fatal error with this statement: DELIMITER | CREATE EVENT `event01` ON SCHEDULE AT '2009-08-04 13:24:04' + INTERVAL 1 HOUR DO BEGIN select now(); END| DELIMITER ; mysql> repair table mysql.event extended; +-------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+--------+----------+----------+ | mysql.event | repair | status | OK | +-------------+--------+----------+----------+ 1 row in set (0.01 sec) mysql.event is okay