Next MySQL GA release
Hi, does anyone know when the GA release on a 5.5 or 5.6 trunk is planned? I noticed that the versions switched from 5.5.4-beta to 5.5.5-m3 in only a few weeks. But it happened so often the after milestone was followed by another beta release instead of the next GA release, that I have no clue what will happen after 5.5.5-m3. So in short: Is there any chance that a 5.5 or 5.6 GA release will be ready until end of September, or should I setup a new server with 5.1.x still? Kind regards Marten Lehmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump: Got error: 1030
Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB table space errors
How can I remove a table space from the internal data dictionary? The documentation doesn't help at all. Even after a fresh installation and importing all dumps of the databases from scratch, I get these errors: 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... Cannot determine thread, fp=0x453d60e0, backtrace may not be correct. Stack range sanity check OK, backtrace follows: (nil) New value of fp=0x1be63920 failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x2aaae00bcd40 is invalid pointer thd->thread_id=9694 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. Number of processes running now: 0 081103 19:47:13 mysqld restarted InnoDB: Log scan progressed past the checkpoint lsn 0 2529773664 081103 19:47: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... InnoDB: Doing recovery: scanned up to log sequence number 0 2529774316 081103 19:47:22 InnoDB: Error: table 'db20255/tbl_film_categories' InnoDB: in InnoDB data dictionary has tablespace id 2365, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html InnoDB: for how to resolve the issue. 081103 19:47:22 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 081103 19:47:23 InnoDB: Started; log sequence number 0 2529774316 081103 19:47:24 [Note] /vrmd/mysql/mysql-5.0.67/libexec/mysqld: ready for connections. Version: '5.0.67-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql crashing every now and then
Hello, our mysql 5.0.67 on a AMD Opteron 2218 with 2 GB RAM is crashing from time to time. This what is in the log: 081028 6:37:30 - mysqld got signal 11 ; 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=402653184 read_buffer_size=2097152 max_used_connections=31 max_connections=1 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 41353216 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x1fa7feb0 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... Cannot determine thread, fp=0x4510b0e0, backtrace may not be correct. Stack range sanity check OK, backtrace follows: (nil) New value of fp=0x1fa7feb0 failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x201766f0 = SELECT DISTINCT session_id, DATE_FORMAT(access_time,'%Y-%m-%d %H:%i') as fmtDate, substring( access_time, 1, 0 ) AS dd, COUNT(access_time) as cnt FROM curry_stattraq WHERE session_id <> 0 GROUP BY session_id ORDER BY dd DESC LIMIT 0, 1000 thd->thread_id=682597 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. Number of processes running now: 0 081028 06:37:31 mysqld restarted InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 081028 6:37:31 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... 081028 6:37:50 InnoDB: Started; log sequence number 0 1081908061 081028 6:37:51 [Note] /vrmd/mysql/mysql-5.0.67/libexec/mysqld: ready for connections. Version: '5.0.67-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution Although the log doesn't state the database name, I found out the specific database. I'm a bit astounded that anything might be wrong with the tables, since we did a rebuild (mysqldump and import) of all tables. To be sure, I started a repair table anyway: mysql> repair table curry_stattraq; +++--+--+ | Table | Op | Msg_type | Msg_text | +++--+--+ | db18841.curry_stattraq | repair | status | OK | +++--+--+ 1 row in set (10.06 sec) Now I was trying to do the sql query: mysql> SELECT DISTINCT session_id, DATE_FORMAT(access_time,'%Y-%m-%d %H:%i') as fmtDate, substring( access_time, 1, 0 ) AS dd, COUNT(access_time) as cnt FROM curry_stattraq WHERE session_id <> 0 GROUP BY session_id ORDER BY dd DESC LIMIT 0, 1000; ERROR 2013 (HY000): Lost connection to MySQL server during query What is mysql doing here? Why does it crash? The table seems to be fine and there is enough RAM and CPU left to process the query. Any ideas? Kind regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL und dual cores
Hello, we are using MySQL 4.1 and 5 on AMD dual core processors, but I can only see one mysqld process on each machine. Since a process is always tied to a certain processor, mysqld doesn't seem to make use of the second core. As far as I know multiple threads of one process would be visible as different processes using the ps command. Is mysqld really not using more than one processor core? Or if it does, then how can I verify it? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange files in database directory
Hello, Marten, in my experience, these are most often temporary files leftover from an incomplete operation. They might be from a long-running query that was canceled, or from a table repair operation that errored out or was interrupted. In my experience it is safe to delete them, provided you take care to not delete one that is in use (i.e. mysql is actively writing to it). The modification dates should let you watch for that. thanks. Mysql 5 seems a bit buggy then, because I have never seen this behavior in mysql 4.1. And from the logs I couldn't see that mysql crashed or restartet. In fact, our mysql 5 server is mostly idling around (in contrary to our mysql 4.1 servers). Kind regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange files in database directory
Hello, I was wondering why the partition is almost full, since the databases aren't very big. Then I noticed these files in the database directory: -rw-rw 1 mysql mysql4310560 Dec 24 10:13 #sql_10d4_0.MYD -rw-rw 1 mysql mysql 1024 Dec 24 10:13 #sql_10d4_0.MYI -rw-rw 1 mysql mysql 17182880 Jan 10 01:28 #sql_10e0_0.MYD -rw-rw 1 mysql mysql 1024 Jan 10 01:28 #sql_10e0_0.MYI -rw-rw 1 mysql mysql 20853120 Mar 17 10:55 #sql_10e6_0.MYD -rw-rw 1 mysql mysql 1024 Mar 17 10:55 #sql_10e6_0.MYI -rw-rw 1 mysql mysql 19048320 Feb 20 07:35 #sql_10ef_0.MYD -rw-rw 1 mysql mysql 1024 Feb 20 07:35 #sql_10ef_0.MYI -rw-rw 1 mysql mysql 25702240 Jan 10 03:23 #sql_10fa_0.MYD -rw-rw 1 mysql mysql 1024 Jan 10 03:23 #sql_10fa_0.MYI -rw-rw 1 mysql mysql8159360 Mar 17 11:17 #sql__0.MYD -rw-rw 1 mysql mysql 1024 Mar 17 11:17 #sql__0.MYI -rw-rw 1 mysql mysql7724800 Mar 8 18:11 #sql_1119_0.MYD -rw-rw 1 mysql mysql 1024 Mar 8 18:11 #sql_1119_0.MYI -rw-rw 1 mysql mysql 17927360 Feb 6 04:40 #sql_1125_0.MYD -rw-rw 1 mysql mysql 1024 Feb 6 04:40 #sql_1125_0.MYI -rw-rw 1 mysql mysql 11600160 Mar 3 12:59 #sql_1126_0.MYD -rw-rw 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_0.MYI -rw-rw 1 mysql mysql 0 Mar 3 12:59 #sql_1126_1.MYD -rw-rw 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_1.MYI -rw-rw 1 mysql mysql 25076800 Dec 24 10:28 #sql_1129_0.MYD -rw-rw 1 mysql mysql 1024 Dec 24 10:28 #sql_1129_0.MYI -rw-rw 1 mysql mysql 27626080 Mar 8 18:19 #sql_1135_0.MYD -rw-rw 1 mysql mysql 1024 Mar 8 18:19 #sql_1135_0.MYI There a really dozends of it, currently about 5000 files using 45 GB (!) of the partition. I thought these might be files from temporary tables, but then I restartet mysql so it should remove them, but they didn't disappear. What are these files from and how can I get rid of it? Can I simply delete them? And how can I be sure that they don't appear again? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how disable engine=memory
Hello, when we create user accounts on our mysql server, then we strictly disallow "CREATE TEMPORARY TABLE". This said, I was a bit shocked that mysql doesn't really care and allows this command without a problem: create table x engine = memory; What is the use to deny users to create a temporary table (which is kept in memory only), when each user is free to consume all memory using the memory-engine for a table? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: German collation for UTF8 missing
Hello, If I recall that correctly, utf8_swedish_ci is the collation to use for european/western european languages. Those Swedish people think they can stand for whole Europe... ;) Not tested my reply, though. and doesn't work either. This is the sorting result with utf8_swedish_ci: ++---+ | id | name | ++---+ | 1 | abc | | 3 | opq | | 8 | taal | | 6 | taest | | 7 | toast | | 5 | täst | | 2 | äbc | | 4 | öpq | ++---+ But instead it should be: ++---+ | id | name | ++---+ | 1 | abc | | 2 | äbc | | 4 | öpq | | 3 | opq | | 8 | taal | | 6 | taest | | 7 | toast | | 5 | täst | ++---+ Java has been designed for and is working with UTF8 for more than 10 years now. But Mysql still doesn't seem to have any clue about UTF8. All UTF8 extensions just look like dirty hacks to stuff UTF8 into it ordinary one-byte-per-character tables without a concept. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: German collation for UTF8 missing
Hello, default-character-set=latin1_d e that's not UTF8. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
German collation for UTF8 missing
Hello, I want to store my data with UTF8, thus I'm using the utf8 charset for my tables. But which collcation shall I use? I cannot find anything appropriate. I cannot use utf8_unicode_ci or utf8_general_ci, because this seems to treat "Ä" and "A" equally. So I couldn't store the words "ÄBC" and "ABC" in the same index. On the other hand I cannot use utf8_bin, because it doesn't sort correctly (as people in Germany would expect it, Ä = Ae). So, whats left? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb mysql crash
Hello, today I got this in my logs and mysql stopped working. InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 071107 8:46:26 InnoDB: Flushing modified pages from the buffer pool... 071107 8:46:26 InnoDB: Started; log sequence number 1 1008136481 /var/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution InnoDB: Error: page n:o stored in the page read in is 538976288, should be 1644! 071107 8:46:28 InnoDB: Error: page 538976288 log sequence number 538976288 538976288 InnoDB: is in the future! Current system log sequence number 1 1008136645. InnoDB: Your database may be corrupt. InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 1644. InnoDB: You may have to recover from a backup. 071107 8:46:28 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020 071107 8:46:29 InnoDB: Page checksum 138226020, prior-to-4.0.14-form checksum 3161919040 InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum 538976288 InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 538976288 InnoDB: Page number (if stored to page already) 538976288, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 538976288 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 1644. So great, that mysql doesn't even tell me at which table or database to look. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. Number of processes running now: 2 mysqld process hanging, pid 19729 - killed mysqld process hanging, pid 19729 - killed 071107 08:46:29 mysqld restarted 071107 8:46:29 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 071107 8:46:29 [ERROR] Do you already have another mysqld server running on port: 3306 ? 071107 8:46:29 [ERROR] Aborting 071107 8:46:29 [Note] /var/mysql/mysql-4.1.18/libexec/mysqld: Shutdown complete 071107 08:46:29 mysqld ended I cannot count any more how often I had problems with innodb. I regret ever compiled it in, but now that a lot of users are using it, I need to recover from the problem. But there is one major problem: All error messages and recovery documentation of innodb sound, as if I would deal with just a few tables. But I have hundrets of databases with thousands of tables! So there is no fast dump and insert of tables. Any ideas (besides from dumping everything and importing at then)? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
utf8 problem in index
Hello, I have a table like this: CREATE TABLE `places` ( `name` varchar(256) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | Then I want to insert two values: pjöngjang.com and pjongjang.com But on the second record I get this message: DBD::mysql::st execute failed: Duplicate entry 'pjongjang' for key 1 Whats wrong with it? The character set and collation set is defined as utf8, so mysql should see a difference between "ö" and "o". Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump with single rows per dataset
Hello, thanks. I just wonder why this isn't actually documented in the --help output of mysqldump. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump with single rows per dataset
Hello, I couldn't find an option within mysql 5.0.x to get a dump of all rows of a table with one line per row/record. I always get insert statements with all rows at once. Has that option been removed since 4.1.x? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
detecting the table type by sql?
Hello, how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a given table name with sql? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb madness
Hello, mysql was such a reliable and unbreakable database until innodb showed up. All the time I had problems with mysql it was related to innodb. Today again: InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... 070206 09:29:19 mysqld ended Is there any use of innodb_file_per_table? As it doesn't remove the requirement of central innodb-transaction logs and data isn't stored in there, it was speedup the start where mysql seems to look for .ibd files. And mysql fails for same reason at the start, but doesn't tell much. When I'm setting innodb_force_recovery, then I get logs like 070206 9:31:42 InnoDB: Error: page 63 log sequence number 0 4317619 InnoDB: is in the future! Current system log sequence number 0 8204. InnoDB: Your database may be corrupt. 070206 9:31:42 InnoDB: Error: page 192 log sequence number 0 14323525 InnoDB: is in the future! Current system log sequence number 0 8204. InnoDB: Your database may be corrupt. 070206 9:31:42 InnoDB: Started; log sequence number 0 8204 /libexec/mysqld: ready for connections. But it doesn't actually fix the innodb-files. Once I run mysql with forced recovery, I can start it again normally. But mostly the server crashes some hours later because one big mysql process using 100% of the cpu shows up at some point and keeps running all the time. Is there any "repair innodb" command like there is a "repair table <...>" for MyIsam tables? Otherwise I think I have to recreated all. How can I check which tables are using innodb with sql? How can walk through the tables with "show databases" and "show tables". Thanks. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question on create_tmp_table_priv
Hello, > If a person forgets to drop temp tables and stays within a connection for hours or even days, memory resources can be overloaded a lot of temp tables. Furthermore, loading temp tables with a GB of data is bad practice but is possible under such circumstances. that is the sort of thing that I expected and was afraid of. Also, I think the mysql documentation doesn't really point out that temporary tables are always kept in memory. Couldn't mysql create them in /tmp/. instead? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question on create_tmp_table_priv
Hello, if there are not any security impacts, why does the Create_tmp_table-privilege exist separately to the create_table-privilege? Or if it has security impacts (maybe automatically granting other rights), which ones? The mysql documentation doesn't tell much about this. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
security impacts by Create_tmp_table_priv?
Hello, is it safe to give users in s shared hosting environment the Create_tmp_table_priv privilege? Are any other privileges granted by this option? Why does mysql store the temporary tables? What happens if the connection dies before mysql has deleted the temporary table? Does you user see it when he logs in to the database next time in that case? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Well, ok, we need to stress this more in the manual. A few users have misunderstood that ibdata files would no longer be needed if one uses innodb_file_per_table. ib_logfiles are always needed. How else can InnoDB recover after a crash. but how can I repair my existing ib-files so that the error on startup doesn't appear any more? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Hello Heikki, can you email the complete .err log from the server to [EMAIL PROTECTED] the complete log is about 50 mb, since a lot of errors occur. I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is >= 153 MB in a data file! We had a crash of the filesystem on one server so we moved all databases to a new server by copying the files. I didn't copy the ib-files because I didn't know that they are indispensable since I created all innodb-tables with innodb_file_per_table. When I restored some databases that didn't work correct, I simply removed the files from the databases-directory, which also wasn't a good idea as I know now, at least regarding innodb-table (most tables are thankfully in myisam-format). This server is hosting a few hundrets of databases (we are doing shared hosting), so it's not so easy to recover the ib-files and keep all databases running. Are you using some exotic file system? No, ext3. The output looks like ib_logfiles and data files from different servers would be mixed. Thats not exactly the way it happened, but it may have the same result. Since the dictionary only has problems with some certain databases, we simply created a new databases and restored the db-dump into it. So the databases are mostly working, but the errors on startup appear anyhow. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb errors on startup
Hello, I'm getting this in my errorlog: 060328 18:43:45 mysqld ended 060328 18:43:46 mysqld started 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_websites.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_wsepgmerchant.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Started; log sequence number 0 18831221 /vrmd/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution How do I get rid of these messages / the errors? The page for troubleshooting didn't help. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2013 (HY000): Lost connection to MySQL server during query
Hello, again, I'm having problems with InnoDB tables. A certain table cannot be dropped. If I'm issueing the drop table statement, the connection is lost and I get the following in the logfile: 060327 14:38:11 InnoDB: error: space object of table db15670/mw_pagelinks, InnoDB: space id 12 did not exist in memory. Retrying an open. InnoDB: Error: trying to add tablespace 12 of name './db15670/mw_pagelinks.ibd' InnoDB: to the tablespace memory cache, but tablespace InnoDB: 12 of name './db15720/admin.ibd' already exists in the tablespace InnoDB: memory cache! 060327 14:38:11 InnoDB: Error: page 3 log sequence number 0 153218641 InnoDB: is in the future! Current system log sequence number 0 14322402. InnoDB: Your database may be corrupt. 060327 14:38:11InnoDB: Assertion failure in thread 196621 in file fsp0fsp.c line 3202 InnoDB: Failing assertion: xdes_get_bit(descr, XDES_FREE_BIT, buf_frame_get_page_no(header) % FSP_EXTENT_SIZE, mtr) == FALSE 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/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; 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=402653184 read_buffer_size=2093056 max_used_connections=12 max_connections=1000 threads_connected=5 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 290904 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8a000c18 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... Cannot determine thread, fp=0xbe1fbe08, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80a43b4 0x82bf71c 0x820a5fd 0x81b64f5 0x8169f26 0x81a4bc7 0x81a4224 0x81a36c2 0x817f332 0x817e57d 0x819376b 0x8125411 0x811ba68 0x812abe0 0x812a500 0x80b76b2 0x80bbb72 0x80b54bd 0x80b5102 0x80b48f9 0x82bb001 0x82ed89a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8bb38e0 = drop table mw_pagelinks thd->thread_id=220 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060327 14:38:12 mysqld restarted 060327 14:38:12 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. The whole mysql-server crashed just because something is wrong in one innodb table. The force-recovery-hint didn't help at all, because it just made it possible do drop the table, but nothing was fixed and I couldn't create the table back then. Why does this happen? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
Hello, This is spelled out pretty clearly in the manual: InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate. well, thats what I found before. But it doesn't explain why InnoDB does need a logfile even when all transactions are committed or rolled back and mysql is shut down. It does explain, that I can use innodb_file_per_table which gives the impression that with that option a shared ibdata isn't required. Or maybe it is required during runtime, but not for a backup. So with InnoDB it is not possible to shutdown the database server, backup the files and maybe use them on another server if the other server already has an ibdata and ib_logfile? Do I always have to do a mysqldump then? That takes much longer. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb in 4.1.18
Hi, I had a lot of trouble today because the InnoDB integration in MySQL is lousy. I read the manual and worked with innodb_per_file_table. So when I shutdown mysql I should be able to delete ib_logfile0, ib_logfile1 and ibdata1, because all table-data should be stored in the .idb and .frm files. But this obviously is not the case! MySQL always reports errors that it can't find the table files, although they are in the directory of the database. And additionally: If I'm removing the files from a databases and try to create a table that existed before (but now doesn't exist because the files are removed), I can't create it any more. What is the function of ib_logfile and ibdata? I expected them to store transaction data only, but the seem to store more. But documentation is very sparse on that. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: purge log fails
Hello, http://dev.mysql.com/doc/mysql/en/crashing.html I decided to create a bug report as this list is rather for resolving common problems than discussing about the mysql development. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: purge log fails
MySQL could die during your query. What is in error log? Oh my god, it's really dieing. I haven't looked in the error log before, because I though, it's just this connection that got lost. This is the error-log output: mysqld got signal 11; 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=402653184 read_buffer_size=2093056 max_used_connections=24 max_connections=1000 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 290904 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xa3500490 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... Cannot determine thread, fp=0xbe5fe748, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80b2589 0x82cb328 0x808f73b 0x808ce7e 0x80db3d5 0x80ca97b 0x80c45e1 0x80c4226 0x80c3a1d 0x82c6c21 0x82f916a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xa68e960 = purge master logs before (select adddate(current_timestamp(), interval -4 day)) thd->thread_id=106601 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 050428 21:44:17 mysqld restarted 050428 21:44:17 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... 050428 21:44:18 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 1226476. InnoDB: Doing recovery: scanned up to log sequence number 0 1226476 InnoDB: Last MySQL binlog file position 0 79, file name ./vm23-bin.000102 050428 21:44:18 InnoDB: Flushing modified pages from the buffer pool... 050428 21:44:18 InnoDB: Started; log sequence number 0 1226476 /usr/mysql/mysql-4.1.11/libexec/mysqld: ready for connections. Version: '4.1.11-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
purge log fails
Hello, the current 4.1.11 implementation of PURGE LOGS doesn't seem to work any more. I created a script running once each day doing the following statement: purge master logs before (select adddate(current_timestamp(), interval -4 day)) That way, all logs prior to today - 4 days will become deleted. This worked fine with 4.1.9. But since I upgraded from 4.1.9 to 4.1.11 I alway get this error: DBD::mysql::db do failed: Lost connection to MySQL server during query at /vrmd/admin/cron/apps/purge_logs/purge_logs.pl line 15. This also happens when I'm doing the query manually logged in at the mysql-prompt. For testing, I simplified to statement e.g. to purge master logs before '2005-04-24'; Now I didn't lost the connection, but on the other hand, the purging wasn't done. All logfiles remained at their position. To clear a bit of space, I was running "reset master". But so all logfiles have been removed, but I want to keep the logfiles for at least the last 4 days to be able to track statements in case of errors. Any idea what is going wrong? As it worked in 4.1.9, I guess there's a problem in the mysql-server? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.20 -> 4.1.9 Can't open file: 'Autor.ibd'
Hello, I hope that you have not destroyed the ibdata file. The tables are there. Did you edit the my.cnf in the upgrade? What does mysqld print to the .err log? when we changed from 4.0.20 to 4.1.9 we moved to a new server and copied only the database directories, but no error-logs or ibdata file. However, I have a backup of the ibdata file. Is it possible to extract individual tables of this file manually? Regards Marten Lehmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.20 -> 4.1.9 Can't open file: 'Autor.ibd'
Hello, some weeks ago we switched from mysql 4.0.20 to 4.18 and later to 4.1.9. A customer now told us, that he can't access his old InnoDB-tables. He gets #1016 - Can't open file: 'Autor.ibd' (errno: 1) if he tries to access the table "Autor". This happens to other InnoDB-tables created with mysql 4.0.20, too. The only file left is the Autor.frm. Have innodb-tables been stored in a central file at 4.0.20 maybe and now individual table-files with 4.1.x? How can this happen? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp migration from 4.0.x to 4.1.x?
Hello, a server of us with dozends of databases from our customers is running mysql 4.0.x and some users are using timestamp-columns in their tables. If we would switch to 4.1.x immediately, there would be no time for our customers to check their scripts relying on the older timestamp format with a maximum of 14 characters. With 4.1.x, the timestamp format is always 19 characters long. How can I give our customers a chance on changing their scripts and timestamp-definitions step by step? The --new-option in mysql 4.0.20 would just cause the newer 19-char format to become affective, but as I can't set this on a per-user-basis, this doesn't help me so far. Is there any way of using older timestamp(14) definitions in mysql 4.1.x? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
charset latin1_de removed in 4.1.4?
Hello, in 4.0.20 I was happy that German Umlauts are sorted correctly (ä -> ae, ö -> oe, ü -> ue). With 4.1.4 the configuration stops: checking "character sets"... configure: error: Charset 'latin1_de' not available. (Available are: binary ascii armscii8 ascii big5 cp1250 cp1251 cp1256 cp1257 cp850 cp852 cp866 dec8 euckr gb2312 gbk geostd8 greek hebrew hp8 keybcs2 koi8r koi8u latin1 latin2 latin5 latin7 macce macroman sjis swe7 tis620 ucs2 ujis utf8). See the Installation chapter in the Reference Manual. Is latin1 in 4.1.4 able to sort correctly? What happens to clients that are using latin1_de as default-character set, but the server is only able to handle latin1? Will they receive errors? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4a-gamma in production?
Hello, Is gamme the last step until the 4.1.4 will be released? Yes. Or will rc's follow before? No. that's nice to hear. So when will the final version be released? 2 days? 2 weeks? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: root-password important?
Hi, I would never allow anyone except myself to connect to my MySQL server as the root MySQL user. I don't want to allow either. That's why I'm asking. From outside, noone can connect as root directly. And as noone has access to localhost through ssh or whatever, noone except me can login as root. My question was just: Is it possible somehow for a remote-client, that connects from outside to the mysql-server, to re-connect as root once logged in? Is there any any connect or open function that could be used from a mysql-client that's connected to a server? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
root-password important?
Hello, imagine the following setup: - a mysql-server - a client on a different host Privileges on the mysql-server are setup in a manner, that users can connect from every host, but not from localhost (and they don't even have access to localhost). root on the other hand is allowed to connect from localhost only, but without giving him a password. Is this a security problem? Is there any way to trick the mysql-server, so that a remote-client can claim to be a localhost-client and thus can connect as mysql-user root? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query & bind-address
Hello, Tell us the OS, the MySQL server and client version and the build information (custom or binary downloaded from MySQL.com). Redhat Enterprise Linux 3, self-build mysql 4.0.20 (server and client). This *might* be a problem of resolver in glibc. I don't see a reason why it should be a resolver problem? The error messages says "lost connection _during_ query", this means that the connection is already established, so obviously the resolver already did it's job to find out the ip-address of the mysql-server. And anyway: I used bind-address before and it worked. I just don't understand why it doesn't work again now. Mysql itself is starting correctly and the server log doesn't show anything. But the clients are losing the connection. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.4a-gamma in production?
Hello, 4.1 offers some interesting new features like more secure passwords and subqueries. However, this release is still labeled "a-gamma"; on the other hand it shall be used for future development. What's that "a" for (usually for alpha) if it's also stated "gamma"? Is gamme the last step until the 4.1.4 will be released? Or will rc's follow before? Is it possible to use 4.1.4a-gamma in production or is it too buggy yet? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query & bind-address
Hello, I'm experiencing a strange thing: When bind is running without "bind-address" everything works fine. But when I'm doing a "bind-address = ..." the clients get "Lost connection to MySQL server during query". I don't understand this, because ot worked once before and the ip-address the clients are connection to is the same as the one mentioned at "bind-address=...". Do you have any ideas for that? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--skip-name-resolve doesn't work
Hello, today one of our caching nameservers failed. Although a second nameserver was listed in /etc/resolf.conf and mysqld was started with --skip-name-resolv, mysql struggled and took very long to accept new connections. It's obvious, that mysql is doing reverse-dns lookups against what configured him to do. All entries in the Host table contain % instead of ip-addresses. But that shouldn't be a reason. With % I mean: Any ip-address. Mysql seems to think: Any hostname, so resolv the hostname from the ip-address first. How can I completely prevent mysql from looking up hosts? I definetely can't define every ip-address because it really can be _any_. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
defaults for net_read_timeout and net_write_timeout?
Hello, what are the defaults for the following options? net_read_timeout net_write_timeout Regards Marten Lehmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character sets on client or server?
Hello, do I need the character sets on the client side or just on the server? Client and server are different servers, so I want to reduce the client-package to a minimum: /usr/bin/mysql /usr/bin/mysqldump /usr/bin/mysqlshow /usr/lib/mysql/libmysqlclient* I don't need man-pages et cetera. So, if a client wants to use a differen locale, does he need the character sets to be stored locally, or will they be sent from the server, so that he doesn't have to store them locally? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to bind mysql to a certain ip-address?
Hello, I was reading through the documentation, but I couldn't find anything on how to bind mysql to a certain ip-address. The only thing I found was how to bind mysql to a different port: mysqld_safe --port=port_number But I don't want mysql to listen on all interfaces. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to disallow MEMORY-tables?
Hello, how can I prevent users from creating databases with the type MEMORY (also known as HEAP)? Regards Marten Lehmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-connect is working very slow (2)
Hello, regarding --skip-name-resolve: I read, that values in the Host-column of the mysql-authorization must be localhost or ip-addresses to work. Since everyone can connect from everywhere, I used 'localhost' and '%'. Is % treated the same? Is the ip-address-rule obligatory or just a should-have? Is --skip-name-resolve treated as if wouldn't be there if the values in Host aren't of type ip-address? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-connect is working very slow
Hello, this weekend, one of our caching nameservers failed. Since then, all mysql-connects were running very slow. I removed the failing nameserver in /etc/resolv.conf. Later, I was setting up another caching nameserver instead and inserted it in /etc/resolv.conf. I rebooted the mysql-machine. Nothing helped so far. What does mysql take so long to connect? I guess it's some sort of lookup, but I don't know what in detail. Since there's no failing nameserver anymore, I don't understand, why it is still taking so long. Is it performing ident lookups also? How can I check which nameserver mysql is using? I also tried --skip-name-resolve in the mysqld-startup, but this also didn't help. But connecting from localhost is fast as always. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
