cannot create index revisionIdx for revisions table

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Follow steps documented here to create revisions table: 
https://code.google.com/p/jwpl/wiki/InstructionsCSVImport
2. Successfully import first csv file (I've imported only a portion of the 
wikipedia dump)
Although import was successful, there were several warnings (see attached file).
3. Attempt to create index revisionIdx with the following command:
CREATE UNIQUE INDEX revisionIdx ON revisions(RevisionID);
4. mysql fails with the following error:
ERROR 1194 (HY00): Table 'revisions' is marked as crashed and should be repaired

What is the expected output? What do you see instead?
The expected output is that the index should get created successfully.

What version of the product are you using? On what operating system?
Revision Machine: 0.10.0
MySQL: 5.6.21 MySQL Community Server (GPL)
OS: Mac OS X

Please provide any additional information below.
I thought this was an issue with mySQL, but checks on the table all return OK, 
and I even repaired the table, but with no luck. 
Here is the output of myisamchk -dvv:
MyISAM file:         /usr/local/mysql/data/wiki_20140502/revisions
Record format:       Packed
Character set:       utf8_general_ci (33)
File-version:        1
Creation time:       2014-10-30 11:08:54
Recover time:        2014-10-30 11:20:48
Status:              checked,analyzed
Auto increment key:              1  Last value:               6926824
Data records:              6926824  Deleted blocks:                 0
Datafile parts:            6926824  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:        6989377620  Keyfile length:          71196672
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                   69

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     4   unique  unsigned long                  1      2931712       1024

Field Start Length Nullpos Nullbit Type
1     1     1                                                         
2     2     4                      no zeros                           
3     6     4                      no zeros                           
4     10    4                      no zeros                           
5     14    4                      no zeros                           
6     18    4                      no zeros                           
7     22    8                      no zeros                           
8     30    11                     blob                               
9     41    11     1       1       blob                               
10    52    1                      no zeros                           
11    53    10                     blob                               
12    63    4      1       2       no zeros                           
13    67    1                      no zeros               

Original issue reported on code.google.com by elisa.fe...@gmail.com on 30 Oct 2014 at 4:47

Attachments:

• mysqlImportWarnings.txt

[GoogleCodeExporter]

I forgot to mention I can consistently recreate this issue. I can drop the 
revisions table, recreate, re-populate and hit the error again when attempting 
to create the index.

Original comment by elisa.fe...@gmail.com on 30 Oct 2014 at 5:26

[GoogleCodeExporter]

I am not sure why the revision table should be crashed.
(One frequent reason is a full HD)
Did you try to repair the table?

Original comment by torsten....@gmail.com on 30 Oct 2014 at 9:04

[GoogleCodeExporter]

Yes- I tried both with the REPAIR sql command and the myisamchk -r command. I 
verified the hard drive is not full. Strangely, the CHECK and myisamchk -e 
commands show that the the table is Ok, so I'm perplexed why the table is 
marked as crashed.
Here are the REPAIR commands I've issued along with the output:
sudo /usr/local/mysql/bin/myisamchk -r 
/usr/local/mysql/data/wiki_20140502/revisions
- recovering (with sort) MyISAM-table 
'/usr/local/mysql/data/wiki_20140502/revisions'
Data records: 6926824
- Fixing index 1

mysql> REPAIR Table revisions;
+-------------------------+--------+----------+----------+
| Table                   | Op     | Msg_type | Msg_text |
+-------------------------+--------+----------+----------+
| wiki_20140502.revisions | repair | status   | OK       |
+-------------------------+--------+----------+----------+
1 row in set (1 min 26.25 sec)

But creating the index still fails:
mysql> CREATE UNIQUE INDEX revisionIdx ON revisions(RevisionID);
ERROR 1194 (HY000): Table 'revisions' is marked as crashed and should be 
repaired

Even though the check table is OK:
mysql> CHECK table revisions extended;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| wiki_20140502.revisions | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (27.11 sec)

$ sudo /usr/local/mysql/bin/myisamchk -e 
/usr/local/mysql/data/wiki_20140502/revisions
Checking MyISAM file: /usr/local/mysql/data/wiki_20140502/revisions
Data records: 6926824   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check records and index references

I can display the columns in the table:mysql> show columns from revisions;
+-------------------------+------------------+------+-----+---------+-----------
-----+
| Field                   | Type             | Null | Key | Default | Extra     
     |
+-------------------------+------------------+------+-----+---------+-----------
-----+
| PrimaryKey              | int(10) unsigned | NO   | PRI | NULL    | 
auto_increment |
| FullRevisionID          | int(10) unsigned | NO   |     | NULL    |           
     |
| RevisionCounter         | int(10) unsigned | NO   |     | NULL    |           
     |
| RevisionID              | int(10) unsigned | NO   |     | NULL    |           
     |
| ArticleID               | int(10) unsigned | NO   |     | NULL    |           
     |
| Timestamp               | bigint(20)       | NO   |     | NULL    |           
     |
| Revision                | mediumtext       | NO   |     | NULL    |           
     |
| Comment                 | mediumtext       | YES  |     | NULL    |           
     |
| Minor                   | tinyint(4)       | NO   |     | NULL    |           
     |
| ContributorName         | text             | NO   |     | NULL    |           
     |
| ContributorId           | int(10) unsigned | YES  |     | NULL    |           
     |
| ContributorIsRegistered | tinyint(4)       | NO   |     | NULL    |           
     |
+-------------------------+------------------+------+-----+---------+-----------
-----+
12 rows in set (0.00 sec)

And I can see the primary key index:
mysql> show index from revisions;
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | 
Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
| revisions |          0 | PRIMARY  |            1 | PrimaryKey  | A         |  
   6926824 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

Sorry this might seem like a mysql issue but I just can't figure out why the 
revisions table is having trouble when creating the index. Note that I can 
continue to add more wikipedia revisions with the "load data" command without 
any issues. It's only creating the index that gives me problems.

Original comment by elisa.fe...@gmail.com on 31 Oct 2014 at 2:19

[GoogleCodeExporter]

Update: I tried importing a single wikipedia file, and was able to successfully 
create the index. I will next try importing two wikipedia files, to see if that 
caused the problem. Feel free to close the issue in the meantime. Thanks for 
the support!

Original comment by elisa.fe...@gmail.com on 3 Nov 2014 at 3:28

[GoogleCodeExporter]

Creating a csv file out of two wikipedia files, and then importing this one csv 
file resulted in the table being marked as crashed. Will investigate if 
creating two csv files (one for each wikipedia file), yields different results.

Original comment by elisa.fe...@gmail.com on 3 Nov 2014 at 2:06

[GoogleCodeExporter]

Creating two csv files (one for each of the two wikipedia files) also resulted 
in the table being marked as crashed. Note that I imported only the first csv 
file into the revisions table, and was able to successfully create the index. I 
then dropped and recreated the revisions table and successfully imported the 
second csv file and created the index. The issue happens when I want to create 
an index for more than one wikipedia file. Any ideas what is going on?

Original comment by elisa.fe...@gmail.com on 3 Nov 2014 at 5:56

[GoogleCodeExporter]

After enabling tracing in mySQL, the problem went away. Please close/cancel 
this issue. Thanks!

Original comment by elisa.fe...@gmail.com on 4 Dec 2014 at 9:34

[GoogleCodeExporter]

Original comment by torsten....@gmail.com on 4 Dec 2014 at 10:15

• Changed state: Done