Strange MyISAM C API issue with multiple connections

I am unsure if this is a bug with MySQL, something on our servers or a
mistake/misunderstanding on our part, but: if we have two connections open,
insert some data using INSERT .. SELECT via the first connection, and then do
a SELECT .. GROUP BY ... query against that data with the second query, we
sometimes get a result that appears to be calculated before the inserts are
'processed'.

I have adapted our issue to a test case which, at least on our servers,
demonstrates the problem. It sometimes requires a few attempts to see the
apparent miscalculation. (Correct results outputting "1:2" and "2:1", with
apparently incorrect results outputting only "1:2".)

If the second connection is not opened until after the INSERTs, the problem
does not seem to occur. If the INSERTs use INSERT .. VALUES () rather than
INSERT .. SELECT, the problem does not seem to occur. It also does not seem
to occur when using InnoDB rather than MyISAM.

If the query cache is enabled, the second connection (when it has apparently
miscalculated the results) will continue to 'calculate' the 'wrong' results
until the connection is closed/re-opened.

Tested on MySQL 4.0.9 and 4.0.13.

-- The tables
use test;
create table t0 (
id int,
dummyid int,
dummy text
) type=myisam;

create table t1 (
id int not null auto_increment primary key,
dummyid int,
dummy text
) type=myisam;

-- Dummy data
insert into t0 values ( 1, 1, 'Dummy One - One' );
insert into t0 values ( 2, 1, 'Dummy One - Two' );
insert into t0 values ( 3, 2, 'Dummy Two - One' );

/****************
dummyclient.c
****************/
#include
#include
#include

int main()
{
MYSQL my1, my2;

mysql_init( &my1 );
mysql_real_connect( &my1,"localhost","root","","test",0,NULL,0);

mysql_init( &my2 );
mysql_real_connect( &my2,"localhost","root","","test",0,NULL,0);

mysql_real_query(&my1, "TRUNCATE t1", 11);

/* Load data */
mysql_real_query( &my1, "INSERT INTO t1 ( dummyid, dummy ) SELECT dummyid,
dummy FROM t0 WHERE id = 1", 78);
mysql_real_query( &my1, "INSERT INTO t1 ( dummyid, dummy ) SELECT dummyid,
dummy FROM t0 WHERE id = 2", 78);
mysql_real_query( &my1, "INSERT INTO t1 ( dummyid, dummy ) SELECT dummyid,
dummy FROM t0 WHERE id = 3", 78);

/* Queries results with second connection */
mysql_real_query( &my2, "SELECT dummyid, COUNT(*) numdums FROM t1 GROUP BY
dummyid", 57 );

MYSQL_RES* res = mysql_use_result( &my2 );
MYSQL_ROW row;
while ( row = mysql_fetch_row( res ) ) {
printf("%s : %s\n", row[0], row[1]);
}
mysql_free_result( res );
}


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Strange MyISAM C API issue with multiple connections

Hello Dean,

Dean Ellis wrote:
> I am unsure if this is a bug with MySQL, something on our servers or a
> mistake/misunderstanding on our part, but: if we have two connections open,
> insert some data using INSERT .. SELECT via the first connection, and then do
> a SELECT .. GROUP BY ... query against that data with the second query, we
> sometimes get a result that appears to be calculated before the inserts are
> 'processed'.
>
> I have adapted our issue to a test case which, at least on our servers,
> demonstrates the problem. It sometimes requires a few attempts to see the
> apparent miscalculation. (Correct results outputting "1:2" and "2:1", with
> apparently incorrect results outputting only "1:2".)

With your test complied I get correct result no matter if Qcache is turned on or off.

$ ./dummyclient
1 : 2
2 : 1
$ ./dummyclient
1 : 4
2 : 2
$ ./dummyclient
1 : 6
2 : 3

All the time Qcache_hits remainss constant as it should as Inserts invalidate
Select queries cached. Qcache_inserts increases accordingly.

> If the second connection is not opened until after the INSERTs, the problem
> does not seem to occur. If the INSERTs use INSERT .. VALUES () rather than
> INSERT .. SELECT, the problem does not seem to occur. It also does not seem
> to occur when using InnoDB rather than MyISAM.

This is very strange as it suggest whatever your problem is it is not Query Cache
related. Query cache works with all table types.

> If the query cache is enabled, the second connection (when it has apparently
> miscalculated the results) will continue to 'calculate' the 'wrong' results
> until the connection is closed/re-opened.

Which I was unable to repeat ...

This is really very strange so if you can find some more details please let us know.

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




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Strange MyISAM C API issue with multiple connections

> With your test complied I get correct result no matter if Qcache is turned
> on or off.

It takes sometimes 10 or 20 attempts to notice the problem here (usually less
than that, but not always).

> $ ./dummyclient
> 1 : 2
> 2 : 1
> $ ./dummyclient
> 1 : 4
> 2 : 2
> $ ./dummyclient
> 1 : 6
> 2 : 3

The sample I provided truncates the table between executions, so those are odd
results.

> All the time Qcache_hits remainss constant as it should as Inserts
> invalidate Select queries cached. Qcache_inserts increases accordingly.

I only mentioned the Query Cache detail because, in our test case, once the
second connection retrieves the 'wrong' results, if the cache is enabled it
will continue to retrieve the 'wrong' results until the connection is
closed/reopened. ie: it is retrieving a value that is inconsistent with the
data in the table and different from the result one would get if one opened
another client and ran the same query.

> Which I was unable to repeat ...

The only thing I could suggest is repeating it 10-20 times to see if it
happens. It is inconsistent... Sometimes it shows up immediately, and
sometimes it takes a few attempts.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Strange MyISAM C API issue with multiple connections

Hi,

Dean Ellis wrote:
>>With your test complied I get correct result no matter if Qcache is turned
>>on or off.
>
>
> It takes sometimes 10 or 20 attempts to notice the problem here (usually less
> than that, but not always).
>
>
>>$ ./dummyclient
>>1 : 2
>>2 : 1
>>$ ./dummyclient
>>1 : 4
>>2 : 2
>>$ ./dummyclient
>>1 : 6
>>2 : 3
>
>
> The sample I provided truncates the table between executions, so those are odd
> results.

You are right. After fixing that TRUNCATE query result is always:

1 : 2
2 : 1




> The only thing I could suggest is repeating it 10-20 times to see if it
> happens. It is inconsistent... Sometimes it shows up immediately, and
> sometimes it takes a few attempts.

I ran it into loops with different delays and still it works correct :(


--
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




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Strange MyISAM C API issue with multiple connections

On Tuesday 25 March 2003 08:41 pm, Alexander Keremidarski wrote:
> > The only thing I could suggest is repeating it 10-20 times to see if it
> > happens. It is inconsistent... Sometimes it shows up immediately, and
> > sometimes it takes a few attempts.
> I ran it into loops with different delays and still it works correct :(

Well, before I give up and assume it's something really weird with our
servers, I have to ask if you looped the execution of the client application,
or if you looped the result query within the same client.

ie: if the query is 'good' inside our client, it stays good within that
instance of the client. It stays 'bad' if the query cache is enabled,
otherwise it corrects itself the next time the query is executed.

I just run the client app a couple dozen times and, invariably, the test code
I gave will result in only seeing the "1:2" and not the "2:1". It's always
the last row that I insert that it doesn't seem to see.

I'll try it on some other systems and see what happens. We tested it on
totally different hardware, with totally different Linux distributions and
kernels, tried it with MySQL AB binaries and custom-compiled versions, with
the same results.

Weird.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Strange MyISAM C API issue with multiple connections

Hi,

Dean Ellis wrote:
> On Tuesday 25 March 2003 08:41 pm, Alexander Keremidarski wrote:


>>I ran it into loops with different delays and still it works correct :(
>
>
> Well, before I give up and assume it's something really weird with our
> servers, I have to ask if you looped the execution of the client application,
> or if you looped the result query within the same client.

# while true; do ./dummyclient ; sleep 1; done

With different number of 'sleep' seconds and even tried to run it as above from
several consoles - up to 5.

The result always is:

1 : 2
2 : 1
1 : 2
2 : 1
....




> I'll try it on some other systems and see what happens. We tested it on
> totally different hardware, with totally different Linux distributions and
> kernels, tried it with MySQL AB binaries and custom-compiled versions, with
> the same results.

Please make sure you can repeat it with our binary distributions and also send me
more details about your kernel, gcc used, glibc and ./configure line you used to
build. I assume it was built from source as you refer to 4.0.13



> Weird.

.... to say the least.

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




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org