Re: Bizarre InnoDB Error Message ( trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED)
Jason,
I am Cc:ing the MySQL General mailing list, so that others who bump into
this bug can find this discussion.
Jason J. W. Williams wrote:
Mr. Tuuri,
We have a high degree of UPDATE/INSERT concurrency along with high
SELECTs. It causes a deadlock about once every 24 hours. In this case
a deadlock was associated with this event.
ha_innodb.cc in 5.0:
int
convert_error_code_to_mysql(
/**/
/* out: MySQL error code */
int error, /* in: InnoDB error code */
THD*thd)/* in: user thread handle or NULL */
{
if (error == DB_SUCCESS) {
return(0);
} else if (error == (int) DB_DUPLICATE_KEY) {
return(HA_ERR_FOUND_DUPP_KEY);
} else if (error == (int) DB_RECORD_NOT_FOUND) {
return(HA_ERR_NO_ACTIVE_RECORD);
} else if (error == (int) DB_ERROR) {
return(-1); /* unspecified error */
} else if (error == (int) DB_DEADLOCK) {
/* Since we rolled back the whole transaction, we must
tell it also to MySQL so that MySQL knows to empty the
cached binlog for this transaction */
if (thd) {
ha_rollback(thd);
}
return(HA_ERR_LOCK_DEADLOCK);
...
/*
Frees a possible InnoDB trx object associated with the current THD. */
static
int
innobase_close_connection(
/*==*/
/* out: 0 or error number */
THD*thd)/* in: handle to the MySQL thread of the user
whose resources should be free'd */
{
trx_t* trx;
trx = (trx_t*)thd-ha_data[innobase_hton.slot];
ut_a(trx);
if (trx-active_trans == 0
trx-conc_state != TRX_NOT_STARTED) {
sql_print_error(trx-active_trans == 0, but trx-conc_state !=
TRX_NOT_STARTED);
}
if (trx-conc_state != TRX_NOT_STARTED
global_system_variables.log_warnings)
sql_print_warning(MySQL is closing a connection that has an
active
InnoDB transaction. %lu row modifications
will
roll back.,
(ulong)trx-undo_no.low);
innobase_rollback_trx(trx);
trx_free_for_mysql(trx);
return(0);
}
Hmm... I need to check that the auto-increment code in ha_innodb.cc sets
trx-active_trans correctly. I guess you have an auto-inc column in your
table?
The deadlock output from
SHOW INNODB STATUS was so long, that it was truncated the SHOW
INNODB STATUS information somewhere in the middle of the deadlocked
rows output. The current transactions setting was completely missing
due to the truncation. I don't have access to the my.cnf from where I
am now, but I will send it on Monday once I get access. Lastly, there
were no errors printed to the .err log prior to the errors I sent.
Thank you so much for writing back. I do truly appreciate it! It is
very relieving to know it is not dangerous.
Best Regards,
Jason
Regards,
Heikki
On 12/30/06, Heikki Tuuri [EMAIL PROTECTED] wrote:
Jason,
Jason J. W. Williams wrote:
Hello Mr. Tuuri,
I'm sorry to bother you directly about this. I have had very little
luck finding anything on this in the forums or on Google and was
hoping you could help me understand a strange error message I received
from InnoDB (5.0.27). Any help would be very much appreciated. Thank
you in advance!
Best Regards,
Jason
---ERROR MESSAGE---
061228 19:02:55 [ERROR] trx-active_trans == 0, but trx-conc_state !=
TRX_NOT_STARTED
061228 19:02:55 [Warning] MySQL is closing a connection that has an
active InnoDB transaction. 0 row modifications will roll back.
the error itself does not sound dangerous.
But do you have an idea how you got this?
What is your my.cnf like?
Are there any other warnings or errors printed to the .err log prior to
this?
Best regards,
Heikki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trouble starting mysqld daemon
Hi! I'm a rookie, so bear with me... Keep getting: Starting mysqld daemon with databases from /opt/mysql/mysql/data Stopping server from pid file /opt/mysql/mysql/data/unknown.pid 061228 17:16:04 mysqld ended After this command bin/safe_mysqld --user=mysql Very open to suggestions Thanks _ Find sales, coupons, and free shipping, all in one place! MSN Shopping Sales Deals http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trouble starting mysqld daemon
Check for .err text log files .. they are probably in /opt/mysql/mysql/data/ called servername.err servername is the hostname of your box. On Thu, 28 Dec 2006, Jeff Jones wrote: Hi! I'm a rookie, so bear with me... Keep getting: Starting mysqld daemon with databases from /opt/mysql/mysql/data Stopping server from pid file /opt/mysql/mysql/data/unknown.pid 061228 17:16:04 mysqld ended After this command bin/safe_mysqld --user=mysql Very open to suggestions Thanks _ Find sales, coupons, and free shipping, all in one place! ?MSN Shopping Sales Deals http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639 -- 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]
Re: trouble starting mysqld daemon
Thanks! I think I'm in business. I changed some privileges, examined the logs (which helped a lot), and initialized the database again using mysql_install_db --user=mysql --datadir=/opt/mysql/mysql/data now onto PHP take care Jeff From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Jeff Jones [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: trouble starting mysqld daemon Date: Sun, 31 Dec 2006 14:23:53 -0500 (EST) Check for .err text log files .. they are probably in /opt/mysql/mysql/data/ called servername.err servername is the hostname of your box. On Thu, 28 Dec 2006, Jeff Jones wrote: Hi! I'm a rookie, so bear with me... Keep getting: Starting mysqld daemon with databases from /opt/mysql/mysql/data Stopping server from pid file /opt/mysql/mysql/data/unknown.pid 061228 17:16:04 mysqld ended After this command bin/safe_mysqld --user=mysql Very open to suggestions Thanks _ Find sales, coupons, and free shipping, all in one place! MSN Shopping Sales Deals http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639 -- 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] _ Type your favorite song. Get a customized station. Try MSN Radio powered by Pandora. http://radio.msn.com/?icid=T002MSN03A07001 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax
- Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 1:29 AM Subject: SQL syntax Hi, I'm getting the following error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Someone may have replied to you by now so I apologize if this is old news but I think the problem _precedes_ enabled = '1' Take a look at the SQL just BEFORE that where you wrote: countryid = , That is surely not valid SQL in any dialect of SQL I have ever seen. You need an appropriate value before the comma, such as: countryid = 7, or countryid = '7', If you don't want to assign a value to countryid, just omit the entire 'countryid =,' altogether. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
