Issue 130 in jwpl: cannot create index revisionIdx for revisions table

[jw...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 130 by elisa.fe...@gmail.com: cannot create index revisionIdx for
revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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

Attachments:
mysqlImportWarnings.txt 7.7 KB

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[jw...@googlecode.com]

Comment #1 on issue 130 by elisa.fe...@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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.

[jw...@googlecode.com]

Comment #2 on issue 130 by torsten....@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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?

[jw...@googlecode.com]

Comment #3 on issue 130 by elisa.fe...@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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.

[Torsten Zesch]

Sorry. I don't have any good ideas what could be causing this.

Others?

-Torsten

> --
> Sie erhalten diese Nachricht, weil Sie Mitglied der Google Groups-Gruppe
> "jwpl-dev" sind.
> Wenn Sie sich von dieser Gruppe abmelden und keine E-Mails mehr von dieser
> Gruppe erhalten möchten, senden Sie eine E-Mail an
> jwpl-dev+u...@googlegroups.com.
> Weitere Optionen: https://groups.google.com/d/optout

[Oliver Ferschke]

You have only used myisamchk, right?
What about mysqlrepair or mysqlcheck?

Try

mysqlrepair -A --auto-repair
mysqlrepair -A -o


or

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases



-Oliver

[jw...@googlecode.com]

Comment #4 on issue 130 by elisa.fe...@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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!

[jw...@googlecode.com]

Comment #5 on issue 130 by elisa.fe...@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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.

[jw...@googlecode.com]

Comment #6 on issue 130 by elisa.fe...@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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?

[jw...@googlecode.com]

Comment #7 on issue 130 by elisa.fe...@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

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

[jw...@googlecode.com]

Updates:
Status: Done

Comment #8 on issue 130 by torsten....@gmail.com: cannot create index

revisionIdx for revisions table
https://code.google.com/p/jwpl/issues/detail?id=130

(No comment was entered for this change.)