| Bug #68450 | InnoDB stopword tables cannot be set if the tables use UTF8 | ||
|---|---|---|---|
| Submitted: | 21 Feb 2013 7:50 | Modified: | 15 Mar 2013 17:40 |
| Reporter: | Ernie Souhrada | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2013 8:54]
Valeriy Kravchuk
This is easy to verify:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE innodb_ft_bug (
-> value VARCHAR(18) NOT NULL DEFAULT ''
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (4.20 sec)
mysql> SET GLOBAL innodb_ft_server_stopword_table='test/innodb_ft_bug';
ERROR 1231 (42000): Variable 'innodb_ft_server_stopword_table' can't be set to t
he value of 'test/innodb_ft_bug'
mysql> SET GLOBAL innodb_ft_user_stopword_table='test/innodb_ft_bug';
ERROR 1231 (42000): Variable 'innodb_ft_user_stopword_table' can't be set to the
value of 'test/innodb_ft_bug'
mysql> show create table information_schema.innodb_ft_default_stopword\G
*************************** 1. row ***************************
Table: INNODB_FT_DEFAULT_STOPWORD
Create Table: CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` (
`value` varchar(18) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.47 sec)
[21 Feb 2013 10:09]
MySQL Verification Team
Hello Ernie, Thank you for the report. Verified as described. Regards, Umesh
[21 Feb 2013 18:50]
MySQL Verification Team
It should be a latin1 column, not utf8.
[21 Feb 2013 18:52]
MySQL Verification Team
Ernie, Umesh,
Why this test does not work for either of you.
This test works like charm for the two of us:
-----------------------------------------------------------------------
mysql> use test1;
Database changed
mysql> create table if not exists abc( value varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> set global innodb_ft_user_stopword_table ="test1/abc";
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert into abc values ("they");
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> insert into abc values ("database");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> set session innodb_ft_user_stopword_table ="test1/abc";
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> select @@innodb_ft_user_stopword_table;
+---------------------------------+
| @@innodb_ft_user_stopword_table |
+---------------------------------+
| test1/abc |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE if not exists articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB
;
Query OK, 0 rows affected (0.26 sec)
mysql>
mysql> INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('How To Use MySQL Well','After you went through a they...') ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('Database' IN NATURAL LANGUAGE MODE);
Empty set (0.10 sec)
mysql>
mysql> # Following are for innodb_ft_server_stopword_table
mysql> drop table if exists articles;
Query OK, 0 rows affected (0.50 sec)
mysql>
mysql> set global innodb_ft_server_stopword_table="test1/abc";
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE if not exists articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.26 sec)
mysql>
mysql> INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('they How To Use MySQL Well','After you went through a ...');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('Database' IN NATURAL LANGUAGE MODE);
Empty set (0.03 sec)
mysql>
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('they' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)
mysql> drop table if exists articles;
Query OK, 0 rows affected (0.21 sec)
mysql> drop table if exists abc;
Query OK, 0 rows affected (0.11 sec)
mysql> show global variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.2.10 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.10 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
-----------------------------------------------------------------------
Could be character set. Each of us has latin1 as default, except utf8 as system charset.
Can you check out the above script if it work for you and we can see what is a problem !!
[21 Feb 2013 18:58]
Ernie Souhrada
It's apparently a character set issue. If I specify that my stopword file is latin1, then it does work, but if I specify utf8, or if utf8 gets chosen by default for whatever reason, it produces the error messages shown in the original bug report. Other character sets may behave properly (or not).
[27 Feb 2013 15:12]
Jimmy Yang
yes, it is a stopword charset issue, and it is being fixed.
[15 Mar 2013 17:40]
Bugs System
Added changelog entry to 5.6.11, 5.7.1. Attempting to replace the default InnoDB FTS stopword list by creating an InnoDB table with the same structure as "INFORMATION_SCHEMA.innodb_ft_default_stopword" would result in an error. "SHOW CREATE TABLE" revealed that the new InnoDB table was created with "CHARSET=utf8". The InnoDB FTS stopword table validity check only supported latin1. This fix extends the validity check for all supported character sets.
Description: According to the documentation, with InnoDB full-text search, it's possible to replace the default stopword list with your own, either per-table or server-wide by creating an InnoDB table with the same structure as INFORMATION_SCHEMA.innodb_ft_default_stopword - i.e., with a single column called "value" which is also a VARCHAR. That feature does not work; when attempting to do it, I get the following error message: mysql> SET GLOBAL innodb_ft_server_stopword_table='test/innodb_myisam_stopword'; ERROR 1231 (42000): Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'test/innodb_myisam_stopword' mysql> SET GLOBAL innodb_ft_user_stopword_table='test/innodb_myisam_stopword'; ERROR 1231 (42000): Variable 'innodb_ft_user_stopword_table' can't be set to the value of 'test/innodb_myisam_stopword' and in my error logs, I have this: InnoDB: invalid column type for stopword table test/innodb_myisam_stopword. Its first column must be of varchar type InnoDB: invalid column type for stopword table test/innodb_myisam_stopword. Its first column must be of varchar type Which makes no sense, given the following: mysql> show create table test.innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_myisam_stopword` ( `value` varchar(18) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show create table information_schema.innodb_ft_default_stopword\G *************************** 1. row *************************** Table: INNODB_FT_DEFAULT_STOPWORD Create Table: CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` ( `value` varchar(18) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 How to repeat: USE test; CREATE TABLE innodb_ft_bug ( value VARCHAR(18) NOT NULL DEFAULT '' ) ENGINE=INNODB DEFAULT CHARSET=utf8; SET GLOBAL innodb_ft_server_stopword_table='test/innodb_ft_bug'; or SET GLOBAL innodb_ft_user_stopword_table='test/innodb_ft_bug';