Bug in MySQL 4.0.10 - ALTER TABLE ADD INDEX AFTER

Hi!
I've found a weird bug in MySQL 4.0.10 when adding indexes to a table...

I'm running Red Hat Linux 8.0
MySQL 4.0.10-gamma. Binary RPM version.
The machine is a dual P2, 400MHz
768 Mb RAM

How-to-repeat:
DROP TABLE IF EXISTS loginFailTable2;
CREATE TABLE loginFailTable2 (
usrName char(40) NOT NULL default '',
usrPass char(40) NOT NULL default '',
lastUserName char(40) default '',
trackerCookie char(30) default '',
failIP char(20) default '0',
failTime datetime NOT NULL default '0000-00-00 00:00:00',
KEY usrNameIndex (usrName(5))
) TYPE=MyISAM;

ALTER TABLE loginFailTable2 ADD FULLTEXT(lastUserName);
ALTER TABLE loginFailTable2 ADD INDEX failIPIndex (failIP) AFTER
usrNameIndex;

- this is all ok, BUT if i shut down mysqld and start it up again between
the two ALTER TABLE-statements, I get:
ERROR 2013: Lost connection to MySQL server during query on the second one.
It seems that everything is working fine as long as I have not restarted
mysqld, but once I do that, all ALTER TABLE-statements which adds an INDEX
(any kind) with "AFTER" specified will crash the server!

I ran inte the bug when trying to upgrade my production database from
3.23.55 to 4.0.10 (I really want those FULLTEXT-features).
I believe I have hit other bugs as well - I have a large table with some
indexes, and after adding three FULLTEXT-indexes, I can no longer run
"myisamchk -R 1 table.MYI" on it! I gives me error 126 immediately, and I
have to repair the table. I have not been able to pinpoint the problem, but
I'll keep trying. I did not help to do a full mysqldump and read it back
in...
I could try to send you the table - it's a bit over 200Mb gzipped. But it
contains some very sensitive user data, so I'd really like to find out a
test-case to send instead...

Hope this can be fixed soon!
Regards,
Tobias Lind



------------------------------------------------------------ ---------
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 bugs-thread13810@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in MySQL 4.0.10 - ALTER TABLE ADD INDEX AFTER

Tobias Lind writes:
> Hi!
> I've found a weird bug in MySQL 4.0.10 when adding indexes to a table...
>
> I'm running Red Hat Linux 8.0
> MySQL 4.0.10-gamma. Binary RPM version.
> The machine is a dual P2, 400MHz
> 768 Mb RAM
>
> How-to-repeat:
> DROP TABLE IF EXISTS loginFailTable2;
> CREATE TABLE loginFailTable2 (
> usrName char(40) NOT NULL default '',
> usrPass char(40) NOT NULL default '',
> lastUserName char(40) default '',
> trackerCookie char(30) default '',
> failIP char(20) default '0',
> failTime datetime NOT NULL default '0000-00-00 00:00:00',
> KEY usrNameIndex (usrName(5))
> ) TYPE=MyISAM;
>
> ALTER TABLE loginFailTable2 ADD FULLTEXT(lastUserName);
> ALTER TABLE loginFailTable2 ADD INDEX failIPIndex (failIP) AFTER
> usrNameIndex;
>
> - this is all ok, BUT if i shut down mysqld and start it up again between
> the two ALTER TABLE-statements, I get:
> ERROR 2013: Lost connection to MySQL server during query on the second one.
> It seems that everything is working fine as long as I have not restarted
> mysqld, but once I do that, all ALTER TABLE-statements which adds an INDEX
> (any kind) with "AFTER" specified will crash the server!
>
> Hope this can be fixed soon!
> Regards,
> Tobias Lind
>

Hi!

Thank you for your bug report.

You should get a syntax error on the above command and not a crash.

This is a patch:

===== sql/sql_yacc.yy 1.200 vs edited =====
*** /tmp/sql_yacc.yy-1.200-20332 Thu Feb 6 16:55:56 2003
--- edited/sql/sql_yacc.yy Fri Feb 21 21:16:30 2003
***************
*** 1288,1295 ****

opt_place:
/* empty */ {}
! | AFTER_SYM ident { store_position_for_column($2.str); }
! | FIRST_SYM { store_position_for_column(first_keyword); };

opt_to:
/* empty */ {}
--- 1288,1314 ----

opt_place:
/* empty */ {}
! | AFTER_SYM ident
! {
! LEX *lex=Lex;
! if (!lex->last_field)
! {
! send_error(&lex->thd->net, ER_SYNTAX_ERROR);
! YYABORT;
! }
! store_position_for_column($2.str);
! }
! | FIRST_SYM
! {
! LEX *lex=Lex;
! if (!lex->last_field)
! {
! send_error(&lex->thd->net, ER_SYNTAX_ERROR);
! YYABORT;
! }
! store_position_for_column(first_keyword);
! }
! ;

opt_to:
/* empty */ {}
===== sql/sql_parse.cc 1.295 vs edited =====
*** /tmp/sql_parse.cc-1.295-20334 Wed Feb 19 16:08:27 2003
--- edited/sql/sql_parse.cc Fri Feb 21 21:10:32 2003
***************
*** 2739,2744 ****
--- 2739,2745 ----
thd->lex.select_lex.table_list.next= (byte**) &thd->lex.select_lex.table_list.first;
thd->lex.select_lex.next=0;
thd->lex.olap=0;
+ thd->lex.last_field=0;
thd->lex.select->olap= UNSPECIFIED_OLAP_TYPE;
thd->fatal_error=0; // Safety
thd->last_insert_id_used=thd->query_start_used=thd->insert_i d_used=0;


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13818@lists.mysql.com
To unsubscribe, e-mail

Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index

Hi!
Ok, I finally managed to create a small test case that shows a bug with
myisamchk -R on a table with fulltext-index and certain content in the
table.

I found the bug in MySQL 4.0.10, binary rpm-version.
I also tested on MySQL 3.23.55 (binary rpm), and the bug is also present in
that version.

I'm running Red Hat Linux 8.0
MySQL 4.0.10-gamma. Binary RPM version.
The machine is a dual P2, 400MHz
768 Mb RAM

How-to-repeat:
DROP TABLE IF EXISTS usrProfileTable2;
CREATE TABLE usrProfileTable2 (
usrName varchar(40) NOT NULL default '',
music text,
PRIMARY KEY (usrName),
FULLTEXT KEY music (music)
) TYPE=MyISAM;

INSERT INTO usrProfileTable2 VALUES ('amrisen','jag gillar pop musik.');
INSERT INTO usrProfileTable2 VALUES ('amro','markolio');
INSERT INTO usrProfileTable2 VALUES ('Amy-Ery90','MP3/kPOP/kRB');
INSERT INTO usrProfileTable2 VALUES ('Amtil','E.M.M.A');

Shut down mysqld
Running:
myisamchk -R 1 /var/lib/mysql/kamrat/usrProfileTable2.MYI

....will give me:
- Sorting records for MyISAM-table
'/var/lib/mysql/kamrat/usrProfileTable2.MYI'
Data records: 4 Deleted: 0
myisamchk: error: 126 when updating key-pointers
MyISAM-table '/var/lib/mysql/kamrat/usrProfileTable2.MYI' is not fixed
because of errors

And the table is corrupt...

Regards,
Tobias Lind



------------------------------------------------------------ ---------
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 bugs-thread13820@lists.mysql.com
To unsubscribe, e-mail

RE: Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index

> How-to-repeat:

I repeated that and it failed for me as well.

I think the bug is in myisamchk, not mysqld.

Mysql has no problem using this command from the Mysql command prompt:

ALTER TABLE usrProfileTable2 ORDER BY usrName;
Query OK, 4 rows affected (0.25 sec)
Records: 4 Duplicates: 0 Warnings: 0

Using the ALTER TABLE syntax has the added benefit of not requiring you
shut down mysqld.

-steve-



------------------------------------------------------------ ---------
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 bugs-thread13821@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index

Tobias,

Tobias Lind wrote:
> Hi!
> Ok, I finally managed to create a small test case that shows a bug with
> myisamchk -R on a table with fulltext-index and certain content in the
> table.

Thanks for your Bug Report
It is BUG and it will be fixed, but I would ask you why do you want to use this at
all.

What are you trying to achieve with myisamchk -R on Full-Text Index?
I can't find even way to define what expected result should be ....

You see if you have usual non-fultext Index then you have Index_value -> Row pairs
stored there -R makes sense as "Order Rows table in order of appearance in Index"

There is reason to do it in case you expect to speed up queries with ORDER BY
Indexed_columns.

But full-text index stores completely different information. According to what
you expect rows to be sorted by myisamchk -R ?

And what this will give you? You can't ORDER BY by Full-text index content.


I believe -R makes no sence with Full-text at all and will be forbidden.

Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
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 bugs-thread13822@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index

Hí!
Well, I'm actually not sorting by the full-text-indexed column - I was
sorting by the primary index (the fulltext-index in the example-code was
no. 2, and I'm, was running myisamchk -R 1)
The reason for using myisamchk -R is exactly wat you noted: to make SELECTs
faster with ORDER BY column 1.
I've been using that in my application for a long time, but haven't been
using any fulltext-indexes. Now I started experimenting with
fulltext-indexing, and found out that the sorting (on primary key) failed
when I added a fulltext-index on a certain column...

But as Steven Roussey noted, "ALTER TABLE usrProfileTable2 ORDER BY usrName"
will
work, so I can use that instead for now... :)

Best regards,
Tobias Lind


----- Original Message -----
From: "Alexander Keremidarski"
To: "Tobias Lind"
Cc: ;
Sent: Saturday, February 22, 2003 10:26 AM
Subject: Re: Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index


> Tobias,
>
> Tobias Lind wrote:
> > Hi!
> > Ok, I finally managed to create a small test case that shows a bug with
> > myisamchk -R on a table with fulltext-index and certain content in the
> > table.
>
> Thanks for your Bug Report
> It is BUG and it will be fixed, but I would ask you why do you want to use
this at
> all.
>
> What are you trying to achieve with myisamchk -R on Full-Text Index?
> I can't find even way to define what expected result should be ....
>
> You see if you have usual non-fultext Index then you have Index_value ->
Row pairs
> stored there -R makes sense as "Order Rows table in order of appearance in
Index"
>
> There is reason to do it in case you expect to speed up queries with ORDER
BY
> Indexed_columns.
>
> But full-text index stores completely different information. According to
what
> you expect rows to be sorted by myisamchk -R ?
>
> And what this will give you? You can't ORDER BY by Full-text index
content.
>
>
> I believe -R makes no sence with Full-text at all and will be forbidden.
>
> Best regards
>
> --
> MySQL Conference and Expo 2003
http://www.mysql.com/events/uc2003/index.html
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski

> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com
>
>
>
>
> ------------------------------------------------------------ ---------
> 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 bugs-thread13822@lists.mysql.com
> To unsubscribe, e-mail
>
>



------------------------------------------------------------ ---------
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 bugs-thread13823@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index

Hi,

Tobias Lind wrote:
> Hí!
> Well, I'm actually not sorting by the full-text-indexed column - I was
> sorting by the primary index (the fulltext-index in the example-code was
> no. 2, and I'm, was running myisamchk -R 1)

I just found that you are sorting on PK :(

So what you found is that myisamchk -R obviously does not work well on table with
Full-text.


Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
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 bugs-thread13824@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in MySQL 4.0.10 - myisamchk with FULLTEXT-index

Hi!

On Feb 22, Tobias Lind wrote:
> Hi!
> Ok, I finally managed to create a small test case that shows a bug with
> myisamchk -R on a table with fulltext-index and certain content in the
> table.

Fixed, thanks.

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
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 bugs-thread13825@lists.mysql.com
To unsubscribe, e-mail