Hello,
First, Yes I can create repeatable test case, I still have live
server with the same problem. and another Dual opteron server, which i
created the same database there as a test to make sure there are no
hardware faliures casuing this probles, but i still had the same problem
there too. I can give full access to any of MySQL team to check this
problem. I had aleaddy 2 DBA's fail to Identify the problem in there.
About the kind of index. any index except primary creates this problem.
I have tried with index on column only , and on multiple, one index
instead of the the multiple i had on the table ( shown below ) . but
still same problem.
The indexes i have there :
mysql> show index from keywordlog;
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| keywordlog | 1 | time_date_index | 1 | time_date
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | time_date_index | 2 | username
| A | NULL | NULL | NULL | YES | BTREE
| |
| keywordlog | 1 | time_date_index | 3 | keyword
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | keyword_index | 1 | keyword
| A | NULL | 10 | NULL | | BTREE
| |
| keywordlog | 1 | keyword_index | 2 | time_date
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | ip_index | 1 | ip
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | ip_index | 2 | username
| A | NULL | NULL | NULL | YES | BTREE
| |
| keywordlog | 1 | username | 1 | username
| A | NULL | NULL | NULL | YES | BTREE
| |
| keywordlog | 1 | username | 2 | bid
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | username | 3 | time_date
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | bid | 1 | bid
| A | NULL | NULL | NULL | | BTREE
| |
| keywordlog | 1 | bid | 2 | time_date
| A | NULL | NULL | NULL | | BTREE
| |
+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.00 sec)
Here is the result of show processlist on the second server where i
created the test case:
mysql> show processlist;
+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
Info |
+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| 2 | root | localhost | donds87_smartppc | Query | 263 | update |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 3 | root | localhost | donds87_smartppc | Query | 0 | NULL |
show processlist |
| 4 | root | localhost | donds87_smartppc | Query | 191 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 5 | root | localhost | donds87_smartppc | Query | 184 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 6 | root | localhost | donds87_smartppc | Query | 182 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 7 | root | localhost | donds87_smartppc | Query | 180 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 8 | root | localhost | donds87_smartppc | Query | 180 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 9 | root | localhost | donds87_smartppc | Query | 179 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 10 | root | localhost | donds87_smartppc | Query | 178 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 11 | root | localhost | donds87_smartppc | Query | 177 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 12 | root | localhost | donds87_smartppc | Query | 176 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
| 13 | root | localhost | donds87_smartppc | Query | 175 | Locked |
INSERT INTO keywordlog (keyword, username, ip, time_date, country,
refer_url, request_url, xmlstatus |
+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
As you see the first INSERT stays in UPDATE status like this forever.
causing the rest to be LOCKED
Here is the information about this table.
mysql> show table status like 'keywordlog';
+---------------------+---------------------+----------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length
| Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
| keywordlog | MyISAM | Fixed | 518 | 267 | 138306
| 1146756268031 | 77824 | 0 | NULL |
2003-12-27 19:21:28 | 2003-12-28 02:40:00 | 2003-12-27 21:40:36
| | |
+------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
HTH,
Derek J
On Mon, 2003-12-29 at 11:00, Sergei Golubchik wrote:
> Hi!
>
> On Dec 28, Derek J wrote:
> > Hello Every Body ,
> > I have been facing a strange problem, that i assume its a mysql bug of
> > some sort.
> >
> > In my datadabase there are 2 tables, if i tried to add any index to them
> > ,any INSERT INTO request status will be - in show processlist- update
> > and will freeze like this forever , and consequently all inserts ot this
> > table stays in the que, untill mysql stops responding.
> >
> > If i drop the INDEX on those tables INSERTS works fine, once i create
> > ANY index except PRIMARY index, INSERTS fails.
> >
> > ALL kind of tricks, like myisamchk, drop table and creating a new one,
> > even moving it to a new machine seems to cause the same problem.
> >
> > Running Mysqld 4.0.16 / 4.0.17 ( tried both) on AMD opteron 2 gig ram
> > using MySQL Binaries on REDHAT AS3.
> >
> > one of The table structures:
> >
> > CREATE TABLE `keywordlog` (
> > `username` char(40) default NULL,
> > `country` enum('Unknown','Unknown','AD','Andorra','AE',**removed rest
> > of enumerate due to restricton on email size**,'ZW','Zimbabwe') NOT NULL
> > default 'Unknown',
> > `ip` char(15) NOT NULL default '',
> > `time_date` datetime NOT NULL default '0000-00-00 00:00:00',
> > `keyword` char(50) NOT NULL default '',
> > `refer_url` char(70) default NULL,
> > `request_url` char(70) default NULL,
> > `xmlstatus` enum('HTML','XML') NOT NULL default 'HTML',
> > `bid` char(6) NOT NULL default '',
> > `toolbar` int(1) default '0'
> > ) TYPE=MyISAM;
>
> What columns are you trying to index ? What is the index definition ?
> What does SHOW PROCESSLIST show when there are "freezed" inserts ?
> How big is your table ?
> Can you create a repeatable test case ?
>
> Regards,
> Sergei
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
