MySQL Bugs: #85627: ERROR 1054 (42S22) at line 1: Unknown column 'knr' in 'from clause'

Bug #85627	ERROR 1054 (42S22) at line 1: Unknown column 'knr' in 'from clause'
Submitted:	26 Mar 2017 9:57	Modified:	27 Apr 2017 8:04
Reporter:	Kun Qiu	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	Ubuntu (14.04)
Assigned to:		CPU Architecture:	Any
Tags:	Concurrency bug, flush tables with read lock, Rare event, UPDATE INNER JOIN

Description:
Hi, this error message, "ERROR 1054 (42S22) at line 1: Unknown column 'knr' in 'from clause'", manifested when I was trying to do some research on Bug NO.38691. For #38691, the bug can be reproduced by:

 table t1 and table t2: 

>  knr int(11) unsigned default NULL
>  em varchar(255) default NULL
>  UNIQUE KEY knr
>  KEY em

and the update statement

>  UPDATE t2 INNER JOIN t1 USING(knr) SET knr = NULL WHERE t1.em <> t2.em

and concurrently execute flush statement

> FLUSH TABLES WITH READ LOCK; UNLOCK TABLES

This bug is not easily to be reproduced on my computer, therefore I tried to increase concurrent number, say 50, of the "FLUSH..." statements to increase the probability of reproduce that bug. The bug #38691 can be much easier to be reproduced at this circumstances, however, the ERROR 1054 message showed up sometimes, but not always, when I running the bug reproduction scripts of bug #38691.  

This bug could be the duplication of #35242 or #56572. If it is, please let me know. 

How to repeat:
Three scripts are needed. 
You can create another to execute the three scripts like this:
> 1. execute script1.sh first
> 2. execute script2.sh and 50 of script3.sh concurrently. (It's better for script2.sh to run for a few seconds ahead start running 50 of script3.sh)

script1.sh

#!/bin/bash
export mysql=/home/quentin/workspace/mysql/mysql_5.0.67/bin/mysql
##
## create table t1 and t2 (identical definition):
##

interrupt(){
        exit
}
trap interrupt SIGINT SIGTERM
$mysql -uroot -p000 -B test <<'EOF'
drop table if exists t1;
drop table if exists t2;

CREATE TABLE t1 (
  `knr` int(11) unsigned default NULL,
  `em` varchar(255) default NULL,
  UNIQUE KEY `knr` (`knr`),
  KEY `em` (`em`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

CREATE TABLE t2 like t1;
EOF

##
## fill table with data:
##
max=100
i=0
while [ $i -lt $max ] ; do
  i=$(($i + 1))
  echo "insert into t1 values ($i, 'em$i');"
  echo "insert into t2 values ($i, 'em$i');"
done | $mysql -uroot -p000 -B test 

script2.sh
#!/bin/bash
for i in `seq 50000` ; do
  $mysql -uroot -p000 test -e "UPDATE t2 INNER JOIN t1 USING(knr) SET knr = NULL WHERE t1.em <> t2.em"
  sleep 1
  echo -n "$i "
done

script3.sh
#!/bin/bash

for i in `seq 50000` ; do
  $mysql -B --user=root -p000 test -e "FLUSH TABLES WITH READ LOCK; UNLOCK TABLES"
  sleep 1
done

The error message will show up after you try several times. In my computer, the bug #38691 will always show up first and lead the mysql server crash and you will not observe this ERROR 1054. So, you have to try several times. I can also upload the scripts I used to reproduce this error. Thank you.  

Suggested fix:
Sorry I don't have any suggestions.

this file will control all other three files to reproduce the error

Attachment: new_run.sh (application/octet-stream, text), 500 bytes.

this is the script1.sh file, suppose to be executed firstly

Attachment: bug38691_pre.sh (application/octet-stream, text), 720 bytes.

this is the script2.sh file I used

Attachment: bug38691_script1.sh (application/octet-stream, text), 378 bytes.

this is the script3.sh file I used.

Attachment: bug38691_script2.sh (application/octet-stream, text), 256 bytes.

Hello Kun Qiu,

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".