innobase_query_caching_of_table_permitted error?
I just upgraded MySQL from 4.1.14 to 5.0.16 and I'm getting the following error over and over again in my server log. 051213 15:55:21051213 15:55:21 [ERROR] The calling thread is holding the adaptive search, latch though calling innobase_query_caching_of_table_permitted. I haven't been able to narrow it down to a single query yet, I was hoping someone had already seen this. I'm running FreeBSD 5.4. Thanks. Mike
Re: Correct way to use innodb_file_per_table?
Heikki, the best way would be to symlink whole database directories under the datadir. Then also an ALTER TABLE keeps the new .ibd file on the drive you intended it to be on. If you symlink a single .ibd file, then an ALTER will create the new .ibd file as not symlinked. As an example, let us say you have three databases: 'database1', 'database2', and 'test'. You may shut down mysqld, copy all the contents of /datadir/database2 to drive 2, and then symlink the directory /datadir/database2 to drive 2. Hmm, ok. I have just one decent size database though. I want to split the tables in that database between disks. I haven't turned on innodb_file_per_table yet I'm trying to plan it out first, so I don't know the file layout yet. If my data directory is /var/db/mysql and my InnoDB data file is /var/db/mysql/ibdata1 then the table files will be created under /var/db/mysql/MyDatabase/MyTable1.ibd, /var/db/mysql/MyDatabase/MyTable2.ibd, etc it sounds like. Is there a way to split those table files? So I could have something like /data/disk1/MyTable1.ibd, /data/disk2/MyTable2.ibd? Thanks for your help. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Correct way to use innodb_file_per_table?
I've added a second drive to my database machine and want to split my database tables between the two drives to balance the load and improve performance. I know I'll need to drop and restore the db to get MySQL to create the tables in their own files. What's the correct way to use innodb_file_per_table? If my datadir is /var/db/mysql , should I let MySQL create all the tables in /var/db/mysql then move the files and create symlinks in /var/db/mysql? I see a note in the InnoDB docs that says you can't move the *.ibd files around, what does that refer to? System is FreeBSD 5.3, MySQL 4.10, current ibdata file is about 25 gb. Thanks for any pointers. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize table and replication failure
I issued a optimize table statement on my master which failed with a lock wait timeout message due to some competing queries running at the same time. Now the statement has been replicated to my slaves, and it completes successfully. However it's killing the slave input thread with the following message in my error log: 050208 15:10:38 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Lock wait timeout exceeded; try restarting transaction' (1205), Error on slave: 'no error' (0). Default database: 'MyDB'. Query: 'optimize table My_Table', Error_code: 0 050208 15:10:38 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'db-bin.000340' position 73609938 What's the correct way to work around this? I don't mind if the optimize table statement is skipped on the slaves. I just want replication to continue. MySQL versions 4.1.7 on the master, 4.1.9 on the slave. Thanks. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary tables, replication, and innodb tables
I'm working on a new series of queries that share a temporary table. I've tried using both create temporary table select and create temp table then insert into t ... select from y. Both seem to create a lock that (if the select portion runs long enough) causes a deadlock with the replication thread on that box (it's a slave). When the select portion runs more than innodb_lock_wait_timeout seconds the slave replication thread dies with the errors: 041119 16:54:06 [ERROR] Slave: Error 'Lock wait timeout exceeded; try restarting transaction' on query. ., Error_code: 1205 041119 16:54:06 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'db-bin.81' position 65976472 Am I missing something here or is the only way to avoid potential problems with the slave replication thread is to increase innodb_lock_wait_timeout to a large enough value that it will be longer than any potential select for the temporary table? All innodb tables, MySQL 4.1.7 for both master and slaves. Thanks. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
