John,
this SQL statement:
INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT (
CUID
) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID
FROM `TV_LOG_ALL`
group by 2,3;
sets locks on all the records it scans in TV_LOG_ALL. If you have a small
buffer pool, then the InnoDB lock table may indeed grow so big that it does
not fit in the buffer pool. The default size of the buffer pool is just 8
MB.
Fix: modify my.cnf and increase innodb_buffer_pool_size. But do not make it
bigger than about 70 % of your computer's RAM.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
----- Original Message -----
From: "Danny Stolle" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, January 02, 2006 9:31 PM
Subject: Re: Table Lock issue on insert
Hi John,
I have looked around a bit and you might be interested in this part:
InnoDB: Do not intentionally crash mysqld if the buffer pool is
exhausted by the lock table; return error 1206 instead ...
check this link: http://dev.mysql.com/doc/refman/4.1/en/news-4-1-8.html
it is quite interesting and i am trying to get this error on my mysql
databases by changing the bufferpool and inserting a lot of data;
Best regards,
Danny
Brittingham, John wrote:
They are InnoDB and max_write_lock_count=4294967295.
The same thing happens when I create a copy of the table.
-----Original Message-----
From: Danny Stolle [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 31, 2005 2:59 AM
To: Brittingham, John; mysql@lists.mysql.com
Subject: Re: Table Lock issue on insert
Hi John,
What kind of engine are you using on your table? MyIsam or InnoDB or are
you using merged tables?
If you query your system variables what is your max_write_lock_count?
If you create a copy of the table: mysql>create table cp1 like
USERS_PER_HOUR; and you try the insert again; is the error gone?
Danny
Brittingham, John wrote:
I am having trouble with table lock.
The query is as follows:
INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT (
CUID
) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID
FROM `TV_LOG_ALL`
group by 2,3;
I keep getting this error: #1206 - The total number of locks exceeds
the
lock table size
How do I fix this?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
