Hi all,
I have recently upgraded my MySQL server to the latest version, 3.23.38 and,
whether this is related or just a coincidence, a lot of heavily-used tables
(mainly INSERT queries and a few SELECT queries via a containing MERGE
table) in one of my databases have had their indexes corrupted.
>From what I can figure out, according to the indexes, MySQL is thinking the
tables all have less rows than they actually contain, so the auto-increment
values aren't working properly and I get error messages telling me records
can't be inserted because of duplicate keys!
When I do a CHECK TABLE on a table suffering from errors, I get results like
the following (edited for brevity):
> <X> clients is using or hasn't closed the table properly
> Size of datafile is: 33197896 Should be: 33197612
> Found 116894 keys of 116893
> Corrupt
where '<X>' is generally a number between 0 and 3. And when I do REPAIR
TABLE, I get the following (again, edited) output:
> Number of rows changed from 116893 to 116894
> OK
Can anyone offer any possible explanations for what has caused this
corruption?
I dearly hope it is not because of the upgrade to 3.23.38, as I need to use
this version to correct a bug with MERGE tables! I attach the output from a
CHECK TABLE command I ran on all the tables in this database that might be
affected. I've also repaired all the tables I've spotted are corrupt so far,
but I fear this may be a recurring problem.
Any help or explanations are very gratefully accepted.
Regards,
------------------------------------------------
Basil Hussain ([EMAIL PROTECTED])
corrupt_banner_indexes.txt
+----------------------+-------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text
| |
+----------------------+-------+----------+---------------------------------------------------------+
| banners.eventlog_11 | check | status | OK
| |
| banners.eventlog_13 | check | status | OK
| |
| banners.eventlog_14 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_14 | check | status | OK
| |
| banners.eventlog_15 | check | status | OK
| |
| banners.eventlog_16 | check | status | OK
| |
| banners.eventlog_17 | check | status | OK
| |
| banners.eventlog_18 | check | status | OK
| |
| banners.eventlog_19 | check | status | OK
| |
| banners.eventlog_20 | check | status | OK
| |
| banners.eventlog_21 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_21 | check | status | OK
| |
| banners.eventlog_22 | check | status | OK
| |
| banners.eventlog_23 | check | status | OK
| |
| banners.eventlog_24 | check | status | OK
| |
| banners.eventlog_25 | check | status | OK
| |
| banners.eventlog_26 | check | status | OK
| |
| banners.eventlog_27 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_27 | check | status | OK
| |
| banners.eventlog_28 | check | status | OK
| |
| banners.eventlog_29 | check | status | OK
| |
| banners.eventlog_30 | check | status | OK
| |
| banners.eventlog_31 | check | status | OK
| |
| banners.eventlog_32 | check | status | OK
| |
| banners.eventlog_33 | check | status | OK
| |
| banners.eventlog_34 | check | status | OK
| |
| banners.eventlog_35 | check | status | OK
| |
| banners.eventlog_36 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_36 | check | status | OK
| |
| banners.eventlog_37 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_37 | check | status | OK
| |
| banners.eventlog_38 | check | status | OK
| |
| banners.eventlog_39 | check | status | OK
| |
| banners.eventlog_40 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_40 | check | error | Record-count is not ok; is 111753 Should
|be: 111754 |
| banners.eventlog_40 | check | warning | Found 1 deleted blocks Should be: 0
| |
| banners.eventlog_40 | check | error | Corrupt
| |
| banners.eventlog_41 | check | status | OK
| |
| banners.eventlog_43 | check | status | OK
| |
| banners.eventlog_44 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_44 | check | status | OK
| |
| banners.eventlog_45 | check | status | OK
| |
| banners.eventlog_46 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_46 | check | status | OK
| |
| banners.eventlog_47 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_47 | check | status | OK
| |
| banners.eventlog_48 | check | status | OK
| |
| banners.eventlog_49 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_49 | check | status | OK
| |
| banners.eventlog_50 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_50 | check | status | OK
| |
| banners.eventlog_51 | check | status | OK
| |
| banners.eventlog_52 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_52 | check | status | OK
| |
| banners.eventlog_53 | check | status | OK
| |
| banners.eventlog_54 | check | status | OK
| |
| banners.eventlog_55 | check | warning | 0 clients is using or hasn't closed the
|table properly |
| banners.eventlog_55 | check | status | OK
| |
| banners.eventlog_56 | check | status | OK
| |
| banners.eventlog_57 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_57 | check | status | OK
| |
| banners.eventlog_58 | check | warning | 3 clients is using or hasn't closed the
|table properly |
| banners.eventlog_58 | check | warning | Size of datafile is: 32580764 Should
|be: 32580196 |
| banners.eventlog_58 | check | error | Found 114721 keys of 114719
| |
| banners.eventlog_58 | check | error | Corrupt
| |
| banners.eventlog_59 | check | warning | 3 clients is using or hasn't closed the
|table properly |
| banners.eventlog_59 | check | warning | Size of datafile is: 31348772 Should
|be: 31348204 |
| banners.eventlog_59 | check | error | Found 110383 keys of 110381
| |
| banners.eventlog_59 | check | error | Corrupt
| |
| banners.eventlog_60 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_60 | check | status | OK
| |
| banners.eventlog_61 | check | status | OK
| |
| banners.eventlog_62 | check | status | OK
| |
| banners.eventlog_63 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_63 | check | status | OK
| |
| banners.eventlog_64 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_64 | check | status | OK
| |
| banners.eventlog_65 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_65 | check | status | OK
| |
| banners.eventlog_66 | check | status | OK
| |
| banners.eventlog_67 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_67 | check | status | OK
| |
| banners.eventlog_68 | check | status | OK
| |
| banners.eventlog_69 | check | status | OK
| |
| banners.eventlog_70 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_70 | check | error | Record-count is not ok; is 91668 Should
|be: 91669 |
| banners.eventlog_70 | check | warning | Found 1 deleted blocks Should be: 0
| |
| banners.eventlog_70 | check | error | Corrupt
| |
| banners.eventlog_71 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_71 | check | status | OK
| |
| banners.eventlog_72 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_72 | check | status | OK
| |
| banners.eventlog_73 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_73 | check | error | Record-count is not ok; is 88983 Should
|be: 88984 |
| banners.eventlog_73 | check | warning | Found 1 deleted blocks Should be: 0
| |
| banners.eventlog_73 | check | error | Corrupt
| |
| banners.eventlog_74 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_74 | check | error | Record-count is not ok; is 82526 Should
|be: 82527 |
| banners.eventlog_74 | check | warning | Found 1 deleted blocks Should be: 0
| |
| banners.eventlog_74 | check | error | Corrupt
| |
| banners.eventlog_75 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_75 | check | status | OK
| |
| banners.eventlog_76 | check | status | OK
| |
| banners.eventlog_77 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_77 | check | error | Record-count is not ok; is 42051 Should
|be: 42052 |
| banners.eventlog_77 | check | warning | Found 1 deleted blocks Should be: 0
| |
| banners.eventlog_77 | check | error | Corrupt
| |
| banners.eventlog_78 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_78 | check | status | OK
| |
| banners.eventlog_79 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_79 | check | status | OK
| |
| banners.eventlog_80 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_80 | check | status | OK
| |
| banners.eventlog_81 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_81 | check | status | OK
| |
| banners.eventlog_82 | check | warning | 3 clients is using or hasn't closed the
|table properly |
| banners.eventlog_82 | check | status | OK
| |
| banners.eventlog_83 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_83 | check | status | OK
| |
| banners.eventlog_84 | check | warning | 0 clients is using or hasn't closed the
|table properly |
| banners.eventlog_84 | check | status | OK
| |
| banners.eventlog_85 | check | warning | 3 clients is using or hasn't closed the
|table properly |
| banners.eventlog_85 | check | status | OK
| |
| banners.eventlog_86 | check | warning | 3 clients is using or hasn't closed the
|table properly |
| banners.eventlog_86 | check | warning | Size of datafile is: 16967864 Should
|be: 16967012 |
| banners.eventlog_86 | check | error | Found 59746 keys of 59743
| |
| banners.eventlog_86 | check | error | Corrupt
| |
| banners.eventlog_87 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_87 | check | status | OK
| |
| banners.eventlog_88 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_88 | check | status | OK
| |
| banners.eventlog_89 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_89 | check | status | OK
| |
| banners.eventlog_90 | check | warning | 0 clients is using or hasn't closed the
|table properly |
| banners.eventlog_90 | check | status | OK
| |
| banners.eventlog_91 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_91 | check | status | OK
| |
| banners.eventlog_92 | check | warning | 1 clients is using or hasn't closed the
|table properly |
| banners.eventlog_92 | check | status | OK
| |
| banners.eventlog_93 | check | status | OK
| |
| banners.eventlog_94 | check | status | OK
| |
| banners.eventlog_95 | check | status | OK
| |
| banners.eventlog_96 | check | status | OK
| |
| banners.eventlog_97 | check | status | OK
| |
| banners.eventlog_98 | check | status | OK
| |
| banners.eventlog_99 | check | status | OK
| |
| banners.eventlog_100 | check | status | OK
| |
| banners.eventlog_101 | check | status | OK
| |
| banners.eventlog_102 | check | status | OK
| |
| banners.eventlog_103 | check | status | OK
| |
| banners.eventlog_104 | check | status | OK
| |
| banners.eventlog_105 | check | status | OK
| |
| banners.eventlog_106 | check | status | OK
| |
| banners.eventlog_107 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_107 | check | status | OK
| |
| banners.eventlog_108 | check | status | OK
| |
| banners.eventlog_109 | check | status | OK
| |
| banners.eventlog_110 | check | status | OK
| |
| banners.eventlog_111 | check | status | OK
| |
| banners.eventlog_112 | check | status | OK
| |
| banners.eventlog_113 | check | status | OK
| |
| banners.eventlog_114 | check | status | OK
| |
| banners.eventlog_115 | check | status | OK
| |
| banners.eventlog_116 | check | warning | 2 clients is using or hasn't closed the
|table properly |
| banners.eventlog_116 | check | warning | Size of datafile is: 664844 Should
|be: 663708 |
| banners.eventlog_116 | check | error | Found 2341 keys of 2337
| |
| banners.eventlog_116 | check | error | Corrupt
| |
| banners.eventlog_117 | check | status | OK
| |
| banners.eventlog_118 | check | status | OK
| |
+----------------------+-------+----------+---------------------------------------------------------+
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
