| Bug #1306 | Table corruption on insert 'client is using or hasn't closed the table properly | ||
|---|---|---|---|
| Submitted: | 16 Sep 2003 10:42 | Modified: | 17 Sep 2003 5:22 |
| Reporter: | Ben Lewis | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.0.15 | OS: | Windows (Win95/Win98 AND Linux) |
| Assigned to: | CPU Architecture: | Any | |
[16 Sep 2003 10:43]
Ben Lewis
This is the PHP script. the db.inc.phtml is a standard db connection
Attachment: chooseShipTo.phtml (application/octet-stream, text), 4.50 KiB.
[16 Sep 2003 10:44]
Ben Lewis
Tables and bin log
Attachment: debugme.zip (application/x-zip-compressed, text), 1.92 KiB.
[17 Sep 2003 5:22]
Alexander Keremidarski
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.
As described in manual myisamchk must be never used when mysqld is also running.
Note that even myisamchk message contains reference to this issue:
MYISAM~1.EXE: warning: 1 clients is using or hasn't closed the table properly
A "client using" it in this case usually means mysqld itself whcih is running.
Please confirm if you have ran myisamchk after shutting down mysqld. If you had both running at the same time this is not a bug, but expected behaviour.
On the contrast mysqlcheck can be used with running mysqld as it sends CHECK or REPAIR statements to mysqld.
Just for the record:
mysql> INSERT INTO shipTo (billToID,company,address1,address2,city,state,zip,mainPhone)
VALUES ('10380','Z Test 3','test','test','test','test','','');
mysql> check table shipTo;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| bugs.shipTo | check | status | OK |
+-------------+-------+----------+----------+
[17 Sep 2003 11:42]
Ben Lewis
It seems to have been in the PHP code, although for the life of me I cannot find out where. I edited it to update it to new PHP standards and everything works fine now. Sorry for the bother.
[17 Sep 2003 11:44]
Ben Lewis
PS. I had indeed stopped mysqld before running myisamchk. So, I'm not sure what the deal is.
Description: Intermittent table corruption when inserting seemingly normal values into a table via PHP script. I.E. INSERT INTO shipTo (billToID,company,address1,address2,city,state,zip,mainPhone) VALUES ('10380','Z Test 3','test','test','test','test','','') Immediately after this insertion, I redirect the browser to view the list of records with billToID=10380, or whatever the billToID might be. When redirected, the new entry does not show up. Two insertions was all it took. The above, and another. ran: C:\mysql\bin>myisamchk -s c:\share\mysql\data\WorkFlowTrack\*.MYI MYISAM~1.EXE: MyISAM file c:\share\mysql\data\WorkFlowTrack\shipTo.MYI MYISAM~1.EXE: warning: 1 clients is using or hasn't closed the table properly MyISAM-table 'c:\share\mysql\data\WorkFlowTrack\shipTo.MYI' is usable but should be fixed went through the MySQL documentation on "building a test case," Even after running mysqlbinlog c:\share\mysql\data\DH_1-bin.069 | mysql on the restored table, I re-ran myisamchk -s c:\share\mysql\data\WorkFlowTrack\*.MYI and got the same error as above. Driving me mad!! I can do similar inserts on different table structures with similar redirects, and have no problems. It's this one that is killing me. And maybe what's even more madening is that on two different pages with the exact same SQL, one page returns the results correctly, the other does not. How to repeat: Create table: CREATE TABLE shipTo ( shipToID bigint(20) NOT NULL auto_increment, billToID bigint(20) NOT NULL default '0', company varchar(25) NOT NULL default '', address1 varchar(25) NOT NULL default '', address2 varchar(25) NOT NULL default '', city varchar(10) NOT NULL default '', state char(2) NOT NULL default '', zip varchar(9) NOT NULL default '', fax varchar(10) NOT NULL default '', mainPhone varchar(10) NOT NULL default '', shipToNumber tinyint(4) NOT NULL default '0', contacts enum('yes','no') NOT NULL default 'no', lastUpdated datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (shipToID), UNIQUE KEY shipToID (shipToID) ) TYPE=MyISAM create a script to take form data and insert into table with a redirect to immediately view records with billToID equal to that just inserted.