Bug #15387 | Table '.' doesn't exist | ||
---|---|---|---|
Submitted: | 1 Dec 2005 15:37 | Modified: | 20 Jan 2006 17:57 |
Reporter: | Mark Neri | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.13 | OS: | Linux (Linux 2.6) |
Assigned to: | CPU Architecture: | Any |
[1 Dec 2005 15:37]
Mark Neri
[1 Dec 2005 16:38]
Nikolay Shestakov
I have this problem for 5.0.15 and 5.0.16 on linux (debian from packages) and windows (xp and 2003 server), but when i call SP. if reconect to server this problem is free, but can repeat. sometimes server is crash on this statement (SP), but after restart is work. this SP like: create procedure rm(CHAR ide) begin declare lm date; select `LastMod` into lm from `t1` where `t1`.`id`= ide; if isnull(lm) = 0 then delete from `t2` where `t2`.`id` = ide and `t2`.`LastMod` < lm; delete from `t3` where `t3`.`id` = ide and `t3`.`LastMod` < lm; ... end if; end; but t2 and t3 have triggers on delete which update t2_1 and t3_1 accordingly like creat trigger t2_update after update on t2 for each row begin insert into t2_1 (`f1`, `f2`, ...) values (OLD.f1, OLD.f2,...) on duplicate key update `t2_1`.`f2` = `t2_1`.`f2` + VALUES(`f2`), ... end; where f1 is primary key.
[1 Dec 2005 16:40]
Nikolay Shestakov
in previous post is error - trigger on delete
[2 Dec 2005 8:18]
Valeriy Kravchuk
Mark: Please, inform, what version of jdbc drivers do you use and what version of MySQL server you are connecting to. Nikolay: Please, send all the SQL statements to repeat the problem you described.
[7 Dec 2005 13:42]
Mark Neri
MySQL 5.0.13-rc-Debian_1 mysql-connector-java-3.1.11 The insert statement is as follows: insert into trades (id,valid,ts,order_time,fill_time,deal_date,value_date,instrument_id,quantity,rate,broker_id,broker_trade_id,broker_trade_time,trader,account,cpty_id,order_type,execution_mode,origin_time,user_req_id,alt_broker_trade_id) values (null,1,null,1132608956526,1132608956587,'2005-11-21','2005-11-23','EUR/USD','2000000.0000000000000000','1.17290000000000','HotSpot','3179224','16:35:56','RHAC','CSHARB026','HotSpot','IOC','API',1132608956525,'bertBlyleven:system3:24337|23954','3179223'); I don't think it matters, but id is an auto increment integer. ts defaults to the current time. Also, this problem is quite rare. Less than once in 10,000 tries of similar inserts. There is a trigger that deletes rows when the valid column becomes 0. There is another trigger that moves rows to another table when they are deleted. I don't expect that either one is triggering in this case.
[8 Dec 2005 18:23]
Valeriy Kravchuk
Mark, Thank you for the additional information. Please, send the results of SHOW CREATE TABLE trades (the table you are inserting into). Can you try to upgrade to 5.0.15 at least? I'll have to check the behaviour you described on the last versdion, in any case.
[14 Dec 2005 14:28]
Mark Neri
Results of SHOW CREATE TABLE trades: CREATE TABLE `trades` (\n `id` int(10) unsigned NOT NULL auto_increment,\n `valid` tinyint(4) default NULL,\n `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n `order_time` bigint(20) default NULL,\n `fill_time` bigint(20) default NULL,\n `deal_date` date default NULL,\n `value_date` date default NULL,\n `instrument_id` varchar(16) default NULL,\n `quantity` decimal(16,3) default NULL,\n `rate` decimal(14,8) default NULL,\n `broker_id` varchar(16) default NULL,\n `broker_trade_id` varchar(24) default NULL,\n `broker_trade_time` time default NULL,\n `trader` varchar(8) default NULL,\n `account` varchar(10) default NULL,\n `cpty_id` varchar(32) default NULL,\n `order_type` varchar(16) default NULL,\n `execution_mode` varchar(10) default NULL,\n `origin_time` bigint(20) default NULL,\n `user_req_id` varchar(32) default NULL,\n `alt_broker_trade_id` varchar(24) default NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `trade_broker_id_index` (`broker_trade_id`),\n KEY `trade_ts_index` (`ts`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1
[14 Dec 2005 14:34]
Mark Neri
Upgrades to 5.016. The problem may have changed, but is at least similar, except that breaking the connection and reconnecting doesn't seem to fix it. Upgraded mysql drivers to 3.1.12. No luck. Tried innodb. No luck. Adding a clear batch call between insert batches may have helped, but we're not sure. Here are some packets that were sent/received: 0000 00 14 c2 54 e6 3e 00 14 c2 54 a8 66 08 00 45 00 ...T.>.. .T.f..E. 0010 01 24 58 5b 40 00 40 06 cb 58 0a 01 01 0f 0a 01 .$X[@.@. .X...... 0020 01 10 82 60 0c ea 2b 5b 96 2b 86 ea 71 43 80 18 ...`..+[ .+..qC.. 0030 19 80 37 48 00 00 01 01 08 0a 54 46 64 42 59 43 ..7H.... ..TFdBYC 0040 06 22 ec 00 00 00 17 09 00 00 00 00 01 00 00 00 ."...... ........ 0050 00 00 00 01 08 00 08 00 0a 00 0a 00 fd 00 fd 00 ........ ........ 0060 fd 00 fd 00 fd 00 0b 00 fd 00 fd 00 fd 00 fd 00 ........ ........ 0070 fd 00 08 00 fd 00 fd 00 f6 c8 de 27 08 01 00 00 ........ ...'.... 0080 8a cb de 27 08 01 00 00 07 d5 07 0c 0e 00 00 00 ...'.... ........ 0090 07 d5 07 0c 10 00 00 00 07 45 55 52 2f 4a 50 59 ........ .EUR/JPY 00a0 18 2d 31 30 30 30 30 30 2e 30 30 30 30 30 30 30 .-100000 .0000000 00b0 30 30 30 30 30 30 30 30 30 12 31 34 32 2e 35 33 00000000 0.142.53 00c0 30 30 30 30 30 30 30 30 30 30 30 30 08 43 75 72 00000000 0000.Cur 00d0 72 65 6e 65 78 0f 41 32 30 30 35 33 34 38 30 31 renex.A2 00534801 00e0 39 4d 58 30 30 08 00 00 00 00 00 00 04 26 04 52 9MX00... .....&.R 00f0 4b 4f 43 09 43 53 48 41 52 42 30 32 36 04 42 6f KOC.CSHA RB026.Bo 0100 66 41 03 49 4f 43 03 41 50 49 00 00 00 00 00 00 fA.IOC.A PI...... 0110 00 00 1a 74 72 61 64 65 42 6f 78 3a 73 79 73 74 ...trade Box:syst 0120 65 6d 31 3a 36 39 32 35 7c 32 30 32 38 04 6e 75 em1:6925 |2028.nu 0130 6c 6c ll 0000 00 14 c2 54 a8 66 00 14 c2 54 e6 3e 08 00 45 08 ...T.f.. .T.>..E. 0010 00 7c ad 8e 40 00 40 06 76 c5 0a 01 01 10 0a 01 .|..@.@. v....... 0020 01 0f 0c ea 82 60 86 ea 71 43 2b 5b 97 1b 80 18 .....`.. qC+[.... 0030 09 d8 16 8f 00 00 01 01 08 0a 59 43 06 23 54 46 ........ ..YC.#TF 0040 64 42 44 00 00 01 ff 7a 04 23 34 32 53 30 32 54 dBD....z .#42S02T 0050 61 62 6c 65 20 27 65 6c 65 74 65 64 5f 63 61 73 able 'el eted_cas 0060 68 4e 6f 54 72 61 64 65 73 2e 66 72 6d 2e 68 4e hNoTrade s.frm.hN 0070 6f 54 72 61 64 65 73 2e 66 72 6d 27 20 64 6f 65 oTrades. frm' doe 0080 73 6e 27 74 20 65 78 69 73 74 sn't exi st And here is the insert that we did 2005-12-14@00:04:38.669:insert:com.mysql.jdbc.ServerPreparedStatement[9] - insert into trades (id,valid,ts,order_time,fill_time,deal_date,value_date,instrument_id,quantity,rate,broker_id,broker_trade_id,broker_trade_time,trader,account,cpty_id,order_type,execution_mode,origin_time,user_req_id,alt_broker_trade_id) values (null,1,null,1134540278006,1134540278666,'2005-12-14','2005-12-16','EUR/JPY','-100000.0000000000000000','142.53000000000000','Currenex','A2005348019MX00','00:04:38','RKOC','CSHARB026','BofA','IOC','API',0,'tradeBox:system1:6925|2028','null'); In addition, there is a trigger on this table that deletes a record when it a certain field takes on a certain value. This should not be triggering, ever. The is also a trigger that adds into another table when a record is deleted. I don't think this is happening, but it might be.
[14 Dec 2005 14:42]
Mark Neri
One of the error messages that came back on this insert was as follows: Table 'd.rod_rank, push_rank=old.push_rank, cash_exchange=old.cash_exch., push_rank=old.push_rank, cash_exchange=old.cash_exchange, futu' doesn't exist (seen 6 times) This is the text of a trigger on another table in the database. We do not expect that this table was being modified at the time. We are quite sure that it was not being deleted in a way that would set of this trigger.
[16 Dec 2005 16:44]
Robert Zeh
We have a trigger on the table as well: CREATE TRIGGER `trades_delete` BEFORE DELETE ON `trades` FOR EACH ROW INSERT INTO deleted_trades SET id=old.id, order_time=old.order_time, fill_time=old.fill_time, deal_date=old.deal_date, value_date=old.value_date, instrument_id=old.instrument_id, quantity=old.quantity, rate=old.rate, broker_id=old.broker_id, broker_trade_id=old.broker_trade_id, broker_trade_time=old.broker_trade_time, trader=old.trader, account=old.account, cpty_id=old.cpty_id, order_type=old.order_type, execution_mode=old.execution_mode, origin_time=old.origin_time, user_req_id=old.user_req_id, alt_broker_trade_id=old.alt_broker_trade_id
[20 Dec 2005 17:57]
Valeriy Kravchuk
Please, send the results of SHOW CREATE TABLE for your deleted_trades table also. Without trigger your insert works OK for me: mysql> insert into trades -> (id,valid,ts,order_time,fill_time,deal_date,value_date,instrument_id,quantity, -> rate,broker_id,broker_trade_id,broker_trade_time,trader,account,cpty_id,order_type -> ,execution_mode,origin_time,user_req_id,alt_broker_trade_id) -> values -> (null,1,null,1132608956526,1132608956587,'2005-11-21','2005-11-23','EUR/USD', -> ''2000000.0000000000000000','1.17290000000000','HotSpot','3179224','16:35:56','RHAC' -> ,'CSHARB026','HotSpot','IOC','API',1132608956525,'bertBlyleven:system3:24337|23954','3179223'); Query OK, 1 row affected (0,02 sec) So, I have to repeat with trigger. Have you tried to upgrade, by the way? Please, try 5.0.17 that is officially released already.
[27 Dec 2005 18:11]
Robert Zeh
Valeriy, I'm not sure you understand the problem. The failure doesn't happen on every insert --- it happens once in a great while, perhaps one insert out of 5,000, where the 5,000 inserts could be spread out over a week. After digging through the source code, it feels like mysql is ending up with a bad pointer. But that's just a guess. And, after looking over the documentation, it looks like our triggers are violating the following constraint by inserting records into the deleted_trades: The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK. I'm assuming that an insert into another table is an implicit start transaction and commit. Please let me know if I'm wrong. Anyway, we've removed the triggers and backed out to MySQL 4.1.15-Debian.
[27 Dec 2005 19:36]
Dmitry Lenev
Hi, Mark, Nikolay and Robert! I suspect that you are observing effects of the bug #13399 "Crash when executing PS/SP which should activate trigger which is now dropped" (note that this bug can be repeatable when one uses FLUSH TABLES instead of DROP TRIGGER statement) or may be you are affected by the bug #12329 "Bogus error msg when executing PS with stored procedure after SP was re-created". Both these bugs were fixed in MySQL 5.0.17. So can you give a try to this version (or even to the upcoming 5.0.18) and inform us if upgrade solved your problem? And thank you for your interest in MySQL and your patience!!!
[21 Jan 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".