msql-4.2 bug with a second non uniq index and a where clause

[sgrf]

Hello!

Please have a look at this strange behaviour using a second, non unique, index with a where clause. All required information is appended in an example.
As soon as index sgNode_Status is removed from the example table sgNode data are available again and vanish after recreating the index. Queries without a where clause are not affected.

Kind Regards
Stefan

root@q920:/tmp# cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS"

root@q920:/tmp# date
Mo 22. Apr 15:29:43 CEST 2019

root@q920:/tmp# uname -a
Linux q920 4.15.0-47-generic #50-Ubuntu SMP Wed Mar 13 10:40:37 UTC 2019 i686 i686 i686 GNU/Linux

root@q920:/tmp# /opt/msql4/bin/msqld &
[1] 20527

Mini SQL Version 4.2
Copyright (c) 1993-94 David J. Hughes
Copyright (c) 1995-2018 Hughes Technologies Pty Ltd.
All rights reserved.

Loading configuration from '/opt/msql4/etc/msql.conf'.
Server running as user 'daemon'.
Server mode is Read/Write.
Server process reconfigured to accept 254 connections.
Server table cache holds 16 entries.

Warning : No ACL file. Using global read/write access.

root@q920:/tmp# /opt/msql4/bin/msqladmin version

Version Details :-

msqladmin version 4.2
mSQL server version 4.2
mSQL protocol version 24
mSQL connection Localhost via UNIX socket
Target platform Linux-4.15.0-47-generic-i686

Configuration Details :-

Default config file /opt/msql4/msql.conf
TCP socket 1114
UNIX socket /opt/msql4/msql.sock
mSQL user daemon
Admin user root
Install directory /opt/msql4
PID file location /opt/msql4/msqld.pid
Memory Sync Timer 30
Hostname Lookup True
Backend Processes 3


root@q920:/tmp# /opt/msql4/bin/msqladmin stats

Server Statistics
-----------------

Mini SQL Version 4.2
Copyright (c) 1993-94 David J. Hughes
Copyright (c) 1995-2018 Hughes Technologies Pty Ltd.
All rights reserved.

Config file : /opt/msql4/etc/msql.conf
Max connections : 254
Cur connections : 1
Backend processes: 0
Running as user : daemon
Server uptime : 0 days, 0 hours, 0 mins, 3 secs
Connection count : 1

Connection table :
Sock Username Hostname Database Connect Idle Queries
+-----+------------+-----------------+------------+---------+------+--------+
| 6 | root | UNIX Sock | No DB | 432205H 2 | 0 | 0 |
+-----+------------+-----------------+------------+---------+------+--------+



root@q920:/tmp# /opt/msql4/bin/relshow


+---------------------------+
| Databases |
+---------------------------+
+---------------------------+


root@q920:/tmp# /opt/msql4/bin/msqladmin create Test
Database "Test" created.

root@q920:/tmp# /opt/msql4/bin/msql Test

Welcome to the miniSQL monitor. Type \h for help.


mSQL > CREATE TABLE sgNode (
Node CHAR(15) NOT NULL,
Status CHAR(10) NOT NULL
) \g
-> -> ->
Query OK. 1 row(s) modified or retrieved.

mSQL > CREATE UNIQUE INDEX sgNode_Node ON sgNode (
Node
) \g
-> ->
Query OK. 1 row(s) modified or retrieved.


# PROBLEMATIC SECOND INDEX

mSQL > CREATE INDEX sgNode_Status ON sgNode (Status)\g

Query OK. 1 row(s) modified or retrieved.


mSQL > INSERT INTO sgNode VALUES ('1.2.3.4','snmp')\g

Query OK. 1 row(s) modified or retrieved.

# EVERYTHING IS THERE

mSQL > SELECT * from sgNode\g

Query OK. 1 row(s) modified or retrieved.

+-----------------+------------+
| Node | Status |
+-----------------+------------+
| 1.2.3.4 | snmp |
+-----------------+------------+

# BUT NOT IF USING WHERE

mSQL > SELECT * FROM sgNode WHERE Status='snmp'\g

Query OK. 0 row(s) modified or retrieved.

+-----------------+------------+
| Node | Status |
+-----------------+------------+
+-----------------+------------+

# NOW REMOVE INDEX AND BOTH QUERIES ARE FINE

mSQL > DROP INDEX sgNode_Status FROM sgNode\g

Query OK. 1 row(s) modified or retrieved.


mSQL > SELECT * from sgNode\g

Query OK. 1 row(s) modified or retrieved.

+-----------------+------------+
| Node | Status |
+-----------------+------------+
| 1.2.3.4 | snmp |
+-----------------+------------+



mSQL > SELECT * FROM sgNode WHERE Status='snmp'\g

Query OK. 1 row(s) modified or retrieved.

+-----------------+------------+
| Node | Status |
+-----------------+------------+
| 1.2.3.4 | snmp |
+-----------------+------------+

#NOW RECREATE THE PROBLEM

mSQL > CREATE INDEX sgNode_Status ON sgNode (Status)\g

Query OK. 1 row(s) modified or retrieved.


mSQL > SELECT * from sgNode\g

Query OK. 1 row(s) modified or retrieved.

+-----------------+------------+
| Node | Status |
+-----------------+------------+
| 1.2.3.4 | snmp |
+-----------------+------------+

# AND THE DATA VANISHES AGAIN

mSQL > SELECT * FROM sgNode WHERE Status='snmp'\g

Query OK. 0 row(s) modified or retrieved.

+-----------------+------------+
| Node | Status |
+-----------------+------------+
+-----------------+------------+


mSQL > \q


Bye!

root@q920:/tmp#

[david]

Hi sgrf

Thanks for the detailed bug report! That's interesting, as we've never seen issues with multiple indices before (and we use them heavily in our own internal products etc). Let me have a look into that and follow-up here when I have an understanding of what's going on.


Thanks

David
...

[sgrf]

Hi David,

i still have to use msql-1.0.16 and msql-2.0.11, although both can not be compiled with modern gcc anymore.

Therefore i've tested mql-4.2.

My automated tests complained not in all conditons with several indizes.

Today tested msql-3.11, seems to be fine too.

[david]

Hi

We can't reproduce the problem on our primary dev / test platforms (Centos). We'll spin up an Ubuntu 18LTS VM to match your environment and repeat the tests to try to reproduce the bug. I'll update here further when we've done that.


Thanks

David
...

[sgrf]

Uups, that reminded me to a bug found in a linked library much later years ago. But msql4 doesn't use strange libraries:

root@q920:# ldd /opt/msql4/bin/msqld
linux-gate.so.1 (0xb7f10000)
libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xb7c6a000)
/lib/ld-linux.so.2 (0xb7f12000)

Just for the case, I've removed also optimization -O from src/site.mm, but got the same result.

Tested a more agressive optimization "-mtune=generic -O3 -fomit-frame-pointer ", but got the same result.

Even used an old msql from msql-3.11 with same protocol version, but got the same result.

Please note I use a 32bit OS, as most of my production servers are quite old meanwhile.

root@q920:# gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/lib/gcc/i686-linux-gnu/7/lto-wrapper
Target: i686-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Ubuntu 7.3.0-27ubuntu1~18.04' --with-bugurl=file:///usr/share/doc/gcc-7/README.Bugs --enable-languages=c,ada,c++,go,brig,d,fortran,objc,obj-c++ --prefix=/usr --with-gcc-major-version-only --program-suffix=-7 --program-prefix=i686-linux-gnu- --enable-shared --enable-linker-build-id --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --libdir=/usr/lib --enable-nls --with-sysroot=/ --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --with-default-libstdcxx-abi=new --enable-gnu-unique-object --disable-vtable-verify --enable-libmpx --enable-plugin --enable-default-pie --with-system-zlib --with-target-system-zlib --enable-objc-gc=auto --enable-targets=all --enable-multiarch --disable-werror --with-arch-32=i686 --with-multilib-list=m32,m64,mx32 --enable-multilib --with-tune=generic --enable-checking=release --build=i686-linux-gnu --host=i686-linux-gnu --target=i686-linux-gnu
Thread model: posix
gcc version 7.3.0 (Ubuntu 7.3.0-27ubuntu1~18.04)

[sgrf]

Also tried gcc 5.3, same result.
As I can't get gdb to work with your sources - my fault, did not use it for a long time, i,'ve used your debug mode.
I made the same query without the index (<) and with the second index (>) and made a diff.

Without index it is using IDX 1, but with Index it is using SEQ???
It's getting late, let's continue tomorrow

< [msqld 8904] Cache entry 0 = sgNode:Test, age = 1 (OK)
< [msqld 8904] Found cache entry at 0
---
> [msqld 8904] Cache entry 0 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 1 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 2 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 3 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 4 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 5 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 6 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 7 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 8 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 9 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 10 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 11 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 12 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 13 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 14 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] Cache entry 15 = NULL:NULL, age = 0 (NULL Def!!!)
> [msqld 8904] --> readTableDef()
> [msqld 8904] <-- readTableDef()
> [msqld 8904] --> loadIndices()
> [msqld 8904] <-- loadIndices()
> [msqld 8904] Loading cache entry 0 (Test:sgNode)
> [msqld 8904] --> tableInitTable()
> [msqld 8904] <-- tableInitTable()
39c60
< [msqld 8904] setupCandidate() : Using IDX 1 for sgNode
---
> [msqld 8904] setupCandidate() : Using SEQ for sgNode
42,44c63,75
< [msqld 8904] getCandidate() : using IDX 'sgNode_Status' on sgNode
< [msqld 8904] getCandidate() : IDX key on sgNode = 'snmp','1886219891'
< [msqld 8904] getCandidate() : IDX on sgNode => NO_POS
---
> [msqld 8904] getCandidate() : SEQ on sgNode => 0
> [msqld 8904] tableReadRow() : 0 of sgNode - Active
> [msqld 8904] --> compareMatchRow()
> [msqld 8904] <-- compareMatchRow()
> [msqld 8904] --> tableExtractValues()
> [msqld 8904] --> parseFillValue()
> [msqld 8904] <-- parseFillValue()
> [msqld 8904] --> parseFillValue()
> [msqld 8904] <-- parseFillValue()
> [msqld 8904] <-- tableExtractValues()
> [msqld 8904] --> formatPacket()
> [msqld 8904] <-- formatPacket()
> [msqld 8904] getCandidate() : SEQ on sgNode => NO_POS

[david]

Hi

I have not been able to reproduce this issue on an Ubuntu test platform running 64-bit Ubuntu 18.04.2 LTS. I'll contact you directly to see how we can move forward with debugging this. I'll update this forum thread once that's been done.


Thanks

David
...

[david]

Just closing this off. The problem has been identified and resolved. The issue potentially impacts 32-bit systems. The fix will be in the next release. Many thanks to the OP for his help in resolving this issue.


David
...