Strange errors "Duplicate entry '送'"...

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Post Reply
User avatar
mikovchain
Registered User
Posts: 45
Joined: Mon Oct 16, 2006 5:16 am

Strange errors "Duplicate entry '送'"...

Post by mikovchain »

I met a strange error when posting in my test forum, error description:

Code: Select all

SQL ERROR [ mysql4 ]

Duplicate entry '送' for key 2 [1062]

SQL

INSERT INTO phpbb3dev_search_wordlist (word_text) VALUES ('送')

BACKTRACE

FILE: includes/db/mysql.php
LINE: 132
CALL: dbal_mysql->sql_error()

FILE: includes/db/dbal.php
LINE: 374
CALL: dbal_mysql->sql_query()

FILE: includes/search/fulltext_native.php
LINE: 1100
CALL: dbal_mysql->sql_multi_insert()

FILE: includes/functions_posting.php
LINE: 1891
CALL: fulltext_native->index()

FILE: posting.php
LINE: 938
CALL: submit_post()
I have tried my best to track the error, finally I found the word "老" and "送" conflicts in my database. I query the table search_wordlist with sql:

Code: Select all

select * from phpbb3dev_search_wordlist where word_text='送'
It got a strange result which you can see in picture below:
Untitled-4.gif
(8.7 KiB) Downloaded 1139 times
But there is no record with word_text's value equal '送'. I don't know how mysql would return such a result.

My server environment:
PHP 4.4.4
Mysql 4.0.26
Apache Release 10337100
Apache API Version 19990320

I also tested the forum on another server ( PHP 4.3.11, MySQL 4.1.20, Apache Release 10331100) and no such error occured.

I am confused by this error. Can anyone give some help?
Last edited by mikovchain on Sun Feb 25, 2007 5:51 pm, edited 1 time in total.
PHPBB CHINA For Chinese Users

User avatar
naderman
Consultant
Posts: 1727
Joined: Sun Jan 11, 2004 2:11 am
Location: Berlin, Germany
Contact:

Re: Strange errors "Duplicate entry '送'"...

Post by naderman »

What is the collation of that column? Should be shown in phpmyadmin. Is it really utf8_bin?

Edit: Sorry I didn't notice that you are using MySQL 4.0, this would probably work with 4.1, will have to investigate a bit.

User avatar
mikovchain
Registered User
Posts: 45
Joined: Mon Oct 16, 2006 5:16 am

Re: Strange errors "Duplicate entry '送'"...

Post by mikovchain »

Do you mean the link "Show MySQL system variables" on the first page of phpMyAdmin?
I didn't find the collation information.
It shows:
Variable Session value / Global value
back log 50
basedir /
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set latin1
character sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent insert ON
connect timeout 3
convert character set
datadir /var/lib/mysql/
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
flush OFF
flush time 0
ft boolean syntax + -><()~*:""&|
ft max word len 254
ft max word len for sort 20
ft min word len 4
ft stopword file (built-in)
have bdb NO
have crypt YES
have innodb YES
have isam YES
have openssl NO
have query cache YES
have raid NO
have symlink YES
init file
innodb additional mem pool size 1,048,576
innodb autoextend increment 8
innodb buffer pool size 8,388,608
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb fast shutdown ON
innodb file io threads 4
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb log arch dir ./
innodb log archive OFF
innodb log buffer size 1,048,576
innodb log file size 5,242,880
innodb log files in group 2
innodb log group home dir ./
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb table locks ON
innodb thread concurrency 8
interactive timeout 20
join buffer size 1,044,480
key buffer size 16,777,216
language /usr/share/mysql/english/
large files support ON
license GPL
local infile ON
locked in memory OFF
log OFF
log bin OFF
log error
log slave updates OFF
log slow queries ON
log update OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 16,776,192
max binlog cache size 4,294,967,295
max binlog size 1,073,741,824
max connect errors 10
max connections 128
max delayed threads 20
max heap table size 16,777,216
max insert delayed threads 20
max join size 18,446,744,073,709,552,000
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max tmp tables 32
max user connections 0
max write lock count 4,294,967,295
myisam max extra sort file size 268,435,456
myisam max sort file size 9,223,372,036,854,776,000
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 67,108,864
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
open files limit 2,186
pid file /var/lib/mysql/server5.meyu.net.pid
port 3,306
protocol version 10
query alloc block size 8,192
query cache limit 1,048,576
query cache size 33,554,432
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 2,048
read buffer size 2,093,056
read only OFF
read rnd buffer size 262,144
rpl recovery rank 0
server id 1
skip external locking ON
skip networking OFF
skip show database OFF
slave net timeout 3,600
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer size 2,097,144
sql mode 0
table cache 1,024
table type MYISAM
thread cache size 128
thread stack 196,608
timezone CST
tmp table size 33,554,432
tmpdir /tmp/
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
version 4.0.26-standard-log
version comment Official MySQL RPM
version compile os unknown-linux-gnu
wait timeout 1


As for the column, I think there is no collation information because MySQL's verison is below 4.1, so the table will be created like:

Code: Select all

# Table: 'phpbb_search_wordlist'
CREATE TABLE phpbb_search_wordlist (
	word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
	word_text text NOT NULL,
	word_common tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	PRIMARY KEY (word_id),
	UNIQUE wrd_txt (word_text(255))
);
instead of

Code: Select all

# Table: 'phpbb_search_wordlist'
CREATE TABLE phpbb_search_wordlist (
	word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
	word_text varchar(255) DEFAULT '' NOT NULL,
	word_common tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	PRIMARY KEY (word_id),
	UNIQUE wrd_txt (word_text)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

User avatar
jojobarjo32
Registered User
Posts: 164
Joined: Wed Jun 22, 2005 7:38 pm
Location: France

Re: Strange errors "Duplicate entry '送'"...

Post by jojobarjo32 »

The collation is normally written on the table page :
ScreenShot001.png
ScreenShot001.png (5.77 KiB) Viewed 9323 times

User avatar
mikovchain
Registered User
Posts: 45
Joined: Mon Oct 16, 2006 5:16 am

Re: Strange errors "Duplicate entry '送'"...

Post by mikovchain »

Yes I know, but seems there is no such setting in mysql-4.0.26.
Untitled-2.gif
(32.66 KiB) Downloaded 1137 times
And, I am sorry I have made a mistake in the former post. The mysql version of another server is not 4.0.26, it is 4.1.20.
in the phpinfo() the figure 4.0.26 only shows the version of "mysql Client API".

Will this errro occur in all mysql 4.0.26?
PHPBB CHINA For Chinese Users

User avatar
mikovchain
Registered User
Posts: 45
Joined: Mon Oct 16, 2006 5:16 am

Re: Strange errors "Duplicate entry '送'"...

Post by mikovchain »

I currently modified the function sql_multi_insert($table, &$sql_ary) to avoid the problem...
Using "INSERT IGNORE INTO", so no duplicate warning will be thrown out

But the search function will meet trouble when searching any keys that contain "老“...

User avatar
naderman
Consultant
Posts: 1727
Joined: Sun Jan 11, 2004 2:11 am
Location: Berlin, Germany
Contact:

Re: Strange errors "Duplicate entry '送'"...

Post by naderman »

It's correct that you have no collation. We've identified the problem, which is MySQL's handling of UNIQUE keys in latin1-mode. We're looking into it and will try to come up with a solution. using INSERT INTO IGNORE is not a really good idea, as this will corrupt the contents of the wordmatch table as you said yourself.

User avatar
DavidMJ
Registered User
Posts: 932
Joined: Thu Jun 16, 2005 1:14 am
Location: Great Neck, NY

Re: Strange errors "Duplicate entry '送'"...

Post by DavidMJ »

Should be fixed in CVS
Freedom from fear

User avatar
mikovchain
Registered User
Posts: 45
Joined: Mon Oct 16, 2006 5:16 am

Re: Strange errors "Duplicate entry '送'"...

Post by mikovchain »

To naderman & DavidMJ:

Thank you so much for your kindly help!

Post Reply