Help with a crash
Running 5.1.34 on XP
Box has been working for form some time, however last night it
asserted out. I've attached the log output, below. I've taken the
query, that is listed, and run it without any trouble -- its only
issue being that it could use some rewriting.
I'd greatly appreciate any help in determining what went wrong, and
hopefully how to avoid it in future.
thanks!
Martin
Version: '5.1.34-community-log' socket: '' port: 3306 MySQL
Community Server (GPL)
InnoDB: Error: MySQL is trying to perform a consistent read
InnoDB: but the read view is not assigned!
TRANSACTION 0 465024216, ACTIVE 0 sec, OS thread id 6108 starting
index read, thread declared inside InnoDB 0
mysql tables in use 8, locked 4
MySQL thread id 590766, query id 42766839 dt-capitalmkts3.cppib.ca
192.168.3.160 PriceWriter Sending data
SELECT orders.orderno, a.alias, orders.size, currency_list.code,
MIN(orders_timestamps.time)
FROM orders
INNER JOIN assetlibrary.asset_alias_list a
ON a.assetid = orders.assetid
AND a.sourceid = (SELECT sourceid FROM assetlibrary.data_source_list
WHERE name = 'SEDOL')
INNER JOIN trading.currency_list
ON currency_list.currencyid = orders.currencyid
INNER JOIN orders_timestamps
ON orders_timestamps.orderno = orders.orderno
AND orders_timestamps.timestampid IN (SELECT timestampid FROM
timestamp_list WHERE name IN ('Order Start','Email Date'))
WHERE NOT EXISTS(SELECT * FROM ord
100121 19:11:10 InnoDB: Assertion failure in thread 6108 in file
.\row\row0sel.c line 3647
InnoDB: Failing assertion: 0
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/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 896 stopped in file .\handler\ha_innodb.cc line 4509
100121 19:11:10 - mysqld got exception 0xc005 ;
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=134217728
read_buffer_size=131072
max_used_connections=57
max_threads=100
threads_connected=21
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 170103 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0xcab8018
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...
006BC818mysqld.exe!row_search_for_mysql()[row0sel.c:3647]
006A9AF0mysqld.exe!ha_innobase::index_read()[ha_innodb.cc:4412]
0043AB09mysqld.exe!handler::index_read_map()[handler.h:1390]
0057AD5Emysqld.exe!join_read_always_key()[sql_select.cc:11661]
0057A7C5mysqld.exe!sub_select()[sql_select.cc:11109]
0057431Dmysqld.exe!evaluate_join_record()[sql_select.cc:11224]
0057A7DEmysqld.exe!sub_select()[sql_select.cc:5]
0057431Dmysqld.exe!evaluate_join_record()[sql_select.cc:11224]
0057A7DEmysqld.exe!sub_select()[sql_select.cc:5]
0058E7F0mysqld.exe!do_select()[sql_select.cc:10865]
0058EE1Bmysqld.exe!JOIN::exec()[sql_select.cc:1799]
0058FFA3mysqld.exe!mysql_select()[sql_select.cc:2380]
005903DBmysqld.exe!handle_select()[sql_select.cc:268]
005539B4mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4982]
00554AF6mysqld.exe!mysql_execute_command()[sql_parse.cc:2204]
00636614mysqld.exe!init_available_charsets()[charset.c:447]
00636988mysqld.exe!get_charset_by_csname()[charset.c:580]
005E85AAmysqld.exe!MYSQLparse()[sql_yacc.cc:15681]
00551F6Cmysqld.exe!parse_sql()[sql_parse.cc:7770]
0055A0B6mysqld.exe!mysql_parse()[sql_parse.cc:5867]
0055AC23mysqld.exe!dispatch_command()[sql_parse.cc:1218]
0055BA27mysqld.exe!do_command()[sql_parse.cc:861]
005DEC81mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
00644D1Bmysqld.exe!pthread_start()[my_winthread.c:85]
0072A1A3mysqld.exe!_callthreadstart()[thread.c:293]
FC8FE900
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0CA15FF8=SELECT orders.orderno, a.alias, orders.size,
currency_list.code, MIN(orders_timestamps.time)
FROM orders
INNER JOIN assetlibrary.asset_alias_list a
ON a.assetid = orders.assetid
AND a.sourceid = (SELECT sourceid FROM assetlibrary.data_source_list
WHERE name = 'SEDOL')
INNER JOIN trading.currency_list
ON currency_list.currencyid = orders.currencyid
INNER JOIN orders_timestamps
ON orders_timestamps.orderno = orders
Re: Fwd: Help with Timestamp invalid value error
I had already tried that, actually. Produces the same error. I should have mentioned that as well, sorry! The version is 5.1.34 thanks for the help, btw! Martin On Fri, Sep 4, 2009 at 11:14 AM, John Daisley wrote: > Is your table innodb? If so i think the 'STRICT_TRANS_TABLES' sql_mode 'may' > be causing the problem. > > Try inserting the value as > > '2008-03-09 02:56:34.737' > > Do you get the same error? > > What mysql version is your server? > > > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > > -Original Message- > From: Proemial > Sent: Friday, September 04, 2009 3:39 PM > To: mysql@lists.mysql.com > Subject: Fwd: Help with Timestamp invalid value error > > Currently set to: > NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > > On Fri, Sep 4, 2009 at 10:24 AM, John > Daisley wrote: >> What is your sql_mode set to? >> >> I tried inserting that value into a timestamp column on our test server and >> it works fine. >> >> Regards >> >> John Daisley >> Mobile +44(0)7812 451238 >> Email j...@butterflysystems.co.uk >> >> Certified MySQL 5 Database Administrator (CMDBA) >> Certified MySQL 5 Developer >> Cognos BI Developer >> >> --- >> Sent from HP IPAQ mobile device. >> >> >> > > > [The entire original message is not included] > -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: Help with Timestamp invalid value error
Currently set to: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION On Fri, Sep 4, 2009 at 10:24 AM, John Daisley wrote: > What is your sql_mode set to? > > I tried inserting that value into a timestamp column on our test server and > it works fine. > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > > -Original Message- > From: Proemial > Sent: Friday, September 04, 2009 2:27 PM > To: mysql@lists.mysql.com > Subject: Help with Timestamp invalid value error > > I run a process which loads a series of timestamped data into a table. > I use the TIMESTAMP column. > > I have a single value with a timestamp of '2008-03-9 2:56:34.737' > which fails on insert with 'incorrect datetime'. Days before, and > after work. Hours later in the day work. I thought it might be > related to the timezone, but have none of the TZ tables populated. > > Can someone tell me why this date in particular fails? > > The query that fails: > INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID, > topic_list, product_list) > VALUES ( > '2008-03-9 2:56:34.737', > '', > '', > '') > > Table: > newsID int(10) unsigned PRI auto_increment > timeStamp timestamp CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP > TRStoryID varchar(128) > topic_list varchar(512) > product_list varchar(512) > > > > [The entire original message is not included] > -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with Timestamp invalid value error
I run a process which loads a series of timestamped data into a table. I use the TIMESTAMP column. I have a single value with a timestamp of '2008-03-9 2:56:34.737' which fails on insert with 'incorrect datetime'. Days before, and after work. Hours later in the day work. I thought it might be related to the timezone, but have none of the TZ tables populated. Can someone tell me why this date in particular fails? The query that fails: INSERT INTO secondarydata.trs_newslist (`timestamp`, TRStoryID, topic_list, product_list) VALUES ( '2008-03-9 2:56:34.737', '', '', '') Table: newsID int(10) unsignedPRI auto_increment timeStamp timestamp CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP TRStoryID varchar(128) topic_list varchar(512) product_listvarchar(512) Thank you! Martin -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems with auto_increment updating when (i think) it shouldn't
Hmm, that makes sense. I should have thought of that. Thanks!
On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withers wrote:
> It will also update the auto_increment column when you ROLLBACK a failed
> insert:
>
> mysql> USE test;
> Database changed
> mysql> SELECT * FROM t1\G
> Empty set (0.00 sec)
> mysql> DROP TABLE t1;
> Query OK, 0 rows affected (0.06 sec)
> mysql>
> mysql> CREATE TABLE t1(
> -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
> -> c1 VARCHAR(255),
> -> PRIMARY KEY(id)
> -> ) ENGINE=InnoDB;
> Query OK, 0 rows affected (0.13 sec)
> mysql> START TRANSACTION;
> Query OK, 0 rows affected (0.00 sec)
> mysql> INSERT INTO t1(c1) VALUES('TEST1');
> Query OK, 1 row affected (0.00 sec)
> mysql> INSERT INTO t1(c1) VALUES('TEST2');
> Query OK, 1 row affected (0.00 sec)
> mysql> ROLLBACK;
> Query OK, 0 rows affected (0.02 sec)
> mysql> SHOW CREATE TABLE t1\G
> *** 1. row ***
> Table: t1
> Create Table: CREATE TABLE `t1` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `c1` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
> 1 row in set (0.00 sec)
> mysql> SELECT * FROM t1\G
> Empty set (0.00 sec)
> mysql> INSERT INTO t1(c1) VALUES('TEST3');
> Query OK, 1 row affected (0.03 sec)
> mysql> SELECT * FROM t1\G
> *** 1. row ***
> id: 3
> c1: TEST3
> 1 row in set (0.00 sec)
> mysql>
>
> I believe this is how it has to work. In the event that I start a
> transaction, then another transaction starts, mine fails, the other
> completes and commit's, it has to get ID #3 and not ID #1. At the time the
> transaction was taking place, ID #1 and #2 were in use.
>
> Essentially, your SQL statement is a single transaction with AUTO_COMMIT set
> to '1'.
>
>
>
> On Fri, Aug 7, 2009 at 8:55 AM, Proemial wrote:
>>
>> Hey folks. I'm getting some weird behaviour out of Auto_increment.
>> If I enter a attempt to INSERT a row into a table with a UNIQUE index,
>> where the insert would violate uniqueness of existing data, I'm seeing
>> the auto_increment increase even though the insert fails.
>>
>> The server in question is 5.1.34 running as master. Slave is also 5.1.34.
>>
>> First noticed through a script operating over ODBC, but replicated by
>> hand through the query browser.
>>
>> I couldn't see anything in the ref manual stating this as standard
>> behaviour -- but I easily could have missed something there. Can
>> someone point me in the right direction?
>>
>> Thank you!
>> Martin
>>
>> Using Mysql 5.1.34
>> TEST CASE:
>>
>> CREATE TABLE `test`.`test_table` (
>> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>> `name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
>> UNIQUE KEY `index_2` (`name`)
>> )
>> ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
>>
>> insert some values
>>
>>
>> 1, 'test'
>> 2, 'test2'
>> 3, 'test3'
>>
>>
>> SHOW TABLE STATUS
>> Name test_table
>> Engine InnoDB
>> Version 10
>> Row_format Compact
>> Rows 3
>> Avg_row_length 5461
>> Data_length 16384
>> Max_data_length 0
>> Index_length 16384
>> Data_free 0
>> Auto_increment 4
>> Create_time 2009-08-07 09:33:04
>> Update_time
>> Check_time
>> Collation latin1_swedish_ci
>> Checksum
>> Create_options
>> Comment
>>
>> ---
>> INSERT INTO test.test_table (name) VALUES ('test')
>>
>> SHOW TABLE STATUS
>> Name test_table
>> ...
>> Auto_increment 5
>>
>> ---
>> INSERT IGNORE test.test_table (name) VALUES ('test')
>>
>> SHOW TABLE STATUS
>> Name test_table
>> ...
>> Auto_increment 6
>>
>>
>>
>> --
>> ---
>> This is a signature.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
>>
>
>
>
> --
> -
> Johnny Withers
> 601.209.4985
> joh...@pixelated.net
>
--
---
This is a signature.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problems with auto_increment updating when (i think) it shouldn't
Hey folks. I'm getting some weird behaviour out of Auto_increment.
If I enter a attempt to INSERT a row into a table with a UNIQUE index,
where the insert would violate uniqueness of existing data, I'm seeing
the auto_increment increase even though the insert fails.
The server in question is 5.1.34 running as master. Slave is also 5.1.34.
First noticed through a script operating over ODBC, but replicated by
hand through the query browser.
I couldn't see anything in the ref manual stating this as standard
behaviour -- but I easily could have missed something there. Can
someone point me in the right direction?
Thank you!
Martin
Using Mysql 5.1.34
TEST CASE:
CREATE TABLE `test`.`test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL, PRIMARY KEY (`id`),
UNIQUE KEY `index_2` (`name`)
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
insert some values
1, 'test'
2, 'test2'
3, 'test3'
SHOW TABLE STATUS
Nametest_table
Engine InnoDB
Version 10
Row_format Compact
Rows3
Avg_row_length 5461
Data_length 16384
Max_data_length 0
Index_length16384
Data_free 0
Auto_increment 4
Create_time 2009-08-07 09:33:04
Update_time
Check_time
Collation latin1_swedish_ci
Checksum
Create_options
Comment
---
INSERT INTO test.test_table (name) VALUES ('test')
SHOW TABLE STATUS
Nametest_table
...
Auto_increment 5
---
INSERT IGNORE test.test_table (name) VALUES ('test')
SHOW TABLE STATUS
Nametest_table
...
Auto_increment 6
--
---
This is a signature.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication problems: slave fails to update
I'm trying to resolve a frustrating replication problem with my databases. The master contains a number of schema, only using Innodb tables. Updates happen regularly, usually using bulk inserts of the form INSERT ... ON DUPLICATE UPDATE. Data is mostly numbers. The missing queries contain no non-deterministic functions, no BLOB/TEXT fields, no triggers or procedures, and no variables. The queries are generated by various C programs, connecting through Connector/ODBC. typical table (explain results): 'nodeID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'calculationID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'columnID', 'tinyint(3) unsigned', 'NO', 'PRI', '1', '' 'value', 'double', 'NO', '', '', '' 'lastUpdate', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'on update CURRENT_TIMESTAMP typical bulk insert: INSERT INTO risk.risk_node_tree (nodeID, calculationID, columnID, value, lastUpdate) VALUES (1,2,1,1000,null),... ON DUPLICATE KEY UPDATE value=VALUES(value) Replication is active, and I am able to test it by doing single point modifications through the query browser. However, the bulk inserts seem to vanish: The Master updates, but the slave does not. There are no errors in the log file. SHOW SLAVE STATUS states no problems. I have attempted changing binlog_format, and have received the same results on all three settings. The Master has no settings to ignore any particular schema. Using the query browser, and running the same exact query with the same user, results in the query properly replicating. Both instances are 5.1.34. This is causing me to have to resynchronize the databases every night, which is getting to be something of a chore. Does anyone have any idea what might be happening, or could suggest an avenue of investigation? Any help would be greatly appreciated. Martin -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
