|
我在尝试执行具有一个更新语句的存储过程时遇到以下错误.
下面是错误发生后的INNODB STATUS OUTPUT：
=====================================
090828 12:54:36 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1796,signal count 1794
Mutex spin waits 0,rounds 3036,OS waits 85
RW-shared spins 3538,OS waits 1653; RW-excl spins 56,OS waits 55
------------
TRANSACTIONS
------------
Trx id counter 0 1289747
Purge done for trx's n:o < 0 1289739 undo n:o < 0 0
History list length 85
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 1289744,not started,process no 2986,OS thread id 140445151164752
MySQL thread id 462,query id 64252 Terminator 192.168.1.1 mysqluser
---TRANSACTION 0 0,OS thread id 140445155424592
MySQL thread id 522,query id 64273 192.168.1.2 sysuser
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 1289710,OS thread id 140445132179792
MySQL thread id 490,query id 63941 Fembot 192.168.1.5 mysqluser
---TRANSACTION 0 1289743,OS thread id 140445141580112
MySQL thread id 496,query id 64247 192.168.1.2 sysuser
---TRANSACTION 0 1289639,OS thread id 140445132978512
MySQL thread id 488,query id 63512 Fembot 192.168.1.5 mysqluser
---TRANSACTION 0 1289742,OS thread id 140445134043472
MySQL thread id 461,query id 64242 Terminator 192.168.1.1 mysqluser
---TRANSACTION 0 1289340,OS thread id 140445131114832
MySQL thread id 404,query id 59793 192.168.1.4 mysqluser
---TRANSACTION 0 1289678,OS thread id 140445139450192
MySQL thread id 179,query id 63763 192.168.1.4 mysqluser
---TRANSACTION 0 1289672,OS thread id 140445143976272
MySQL thread id 150,query id 63745 192.168.1.4 mysqluser
---TRANSACTION 0 1289651,OS thread id 140445138385232
MySQL thread id 183,query id 63563 192.168.1.4 mysqluser
---TRANSACTION 0 1289706,OS thread id 140445149833552
MySQL thread id 173,query id 63929 192.168.1.4 mysqluser
---TRANSACTION 0 1289676,OS thread id 140445149034832
MySQL thread id 171,query id 63757 192.168.1.4 mysqluser
---TRANSACTION 0 1289708,OS thread id 140445138917712
MySQL thread id 181,query id 63935 192.168.1.4 mysqluser
---TRANSACTION 0 1289732,OS thread id 140445145307472
MySQL thread id 169,query id 64159 192.168.1.4 mysqluser
---TRANSACTION 0 1289488,OS thread id 140445143710032
MySQL thread id 152,query id 62259 192.168.1.4 mysqluser
---TRANSACTION 0 1289492,OS thread id 140445139982672
MySQL thread id 177,query id 62276 192.168.1.4 mysqluser
---TRANSACTION 0 1287321,OS thread id 140445137586512
MySQL thread id 186,query id 11487 192.168.1.4 mysqluser
---TRANSACTION 0 1287320,OS thread id 140445137852752
MySQL thread id 185,query id 11475 192.168.1.4 mysqluser
---TRANSACTION 0 1289653,OS thread id 140445142911312
MySQL thread id 167,query id 63576 192.168.1.4 mysqluser
---TRANSACTION 0 1289696,OS thread id 140445144508752
MySQL thread id 147,query id 63900 192.168.1.4 mysqluser
---TRANSACTION 0 1287290,OS thread id 140445140248912
MySQL thread id 176,query id 11326 192.168.1.4 mysqluser
---TRANSACTION 0 1287289,OS thread id 140445149301072
MySQL thread id 175,query id 11314 192.168.1.4 mysqluser
---TRANSACTION 0 1289643,OS thread id 140445150632272
MySQL thread id 145,query id 63536 192.168.1.4 mysqluser
---TRANSACTION 0 1287255,OS thread id 140445140515152
MySQL thread id 156,query id 11079 192.168.1.4 mysqluser
---TRANSACTION 0 1289694,OS thread id 140445145839952
MySQL thread id 65,query id 63894 192.168.1.3 mysqluser
---TRANSACTION 0 1286467,OS thread id 140445146106192
MySQL thread id 64,query id 3652 192.168.1.3 mysqluser
---TRANSACTION 0 1286466,OS thread id 140445146372432
MySQL thread id 63,query id 3640 192.168.1.3 mysqluser
---TRANSACTION 0 1289731,OS thread id 140445157288272
MySQL thread id 20,query id 64155 Fembot 192.168.1.5 mysqluser
---TRANSACTION 0 1289746,ACTIVE 0 sec,OS thread id 140445151697232 starting index read
mysql tables in use 1,locked 1
LOCK WAIT 4 lock struct(s),heap size 1216,6 row lock(s)
MySQL thread id 464,query id 64270 Terminator 192.168.1.1 mysqluser Updating
Update test
Set email = 1
Where testid = NAME_CONST('InputtestID',5)
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2952 n bits 176 index `PRIMARY` of table `DB`.`test` trx id 0 1289746 lock_mode X locks rec but not gap waiting
Record lock,heap no 6 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 8; hex 0000000000000005; asc ;; 1: len 6; hex 00000013ae09; asc ;; 2: len 7; hex 000000066629ae; asc f) ;; 3: len 27; hex 796f67696e692e6b756c6b61726e69407961686f6f2e636f2e696e; asc yog.123@yahoo.co.in;; 4: len 6; hex 6950686f6e65; asc iPhone;; 5: len 28; hex 4e644a4b676d5661637068666b4868476c4761525a502b385a44303d; asc NdJKgmVacphfkHhGlGaRZP+8ZD0=;; 6: len 28; hex 577a786b6f4956532f735a374b4559732b4b6b794c39307a4a35513d; asc WzxkoIVS/sZ7KEYs+KkyL90zJ5Q=;; 7: len 1; hex 81; asc ;; 8: len 1; hex 80; asc ;; 9: len 8; hex 80001245b60a4c53; asc E LS;; 10: SQL NULL; 11: len 30; hex 626635336237613736306633353462653665383834666666303936626233; asc bf53b7a760f354be6e884fff096bb3;...(truncated);
------------------
---TRANSACTION 0 1289745,OS thread id 140445136972112
10 lock struct(s),heap size 3024,6 row lock(s)
MySQL thread id 463,query id 64255 Terminator 192.168.1.1 mysqluser
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0,aio writes: 0,ibuf aio reads: 0,log i/o's: 0,sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
20795 OS file reads,2556 OS file writes,1210 OS fsyncs
3.12 reads/s,37221 avg bytes/read,0.18 writes/s,0.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1,free list len 0,seg size 2,0 inserts,0 merged recs,0 merges
Hash table size 17393,node heap has 1 buffer(s)
0.00 hash searches/s,0.61 non-hash searches/s
---
LOG
---
Log sequence number 0 287498463
Log flushed up to 0 287498463
Last checkpoint at 0 287498463
0 pending log writes,0 pending chkp writes
913 log i/o's done,0.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 31312994; in additional pool allocated 1048576
Dictionary memory allocated 8764592
Buffer pool size 512
Free buffers 0
Database pages 511
Modified db pages 0
Pending reads 0
Pending writes: LRU 0,flush list 0,single page 0
Pages read 45969,created 76,written 1658
7.09 reads/s,0.00 creates/s,0.09 writes/s
Buffer pool hit rate 977 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB,0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2986,id 140445169375568,state: sleeping
Number of rows inserted 2367,updated 662,deleted 2330,read 30234862
0.00 inserts/s,0.03 updates/s,0.00 deletes/s,1828.97 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
这也是slow_query_logs：
# Query_time: 51.156942 Lock_time: 0.000008 Rows_sent: 0 Rows_examined: 5
SET timestamp=1251444327;
CALL SP_test(5,'test@test.com');
这是过程的更新语句：
Update test
set testflag = 1
where testid = 5;
请告诉我
最佳答案
我确实遇到了同样的问题.
这是与更改lower_case_table_names属性有关的问题.
您是否更改过上述财产？
如果在0、1、2之间切换,则数据库文件和索引将被破坏.
解决此问题的最佳方法：
>删除架构
>设置lower_case_table_names满足您的要求
>重新创建架构
（编辑：我爱故事小小网_铜陵站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
浙公网安备 33038102330570号