mydac430d7_4.30.1.17 problem with tables named 'status'

sean · Post by **sean** » Fri 14 Jul 2006 08:57

Hi,

The server is mysql-max-5.0.18-linux-i686.

When I execute the following query from a TMyQuery, i get the error. "#42S02 Table Inventory.STATUS does not exist.

SELECT s.vlan, s.status, s.name, s.description, s.mac, comment, ChangeDate,
ChangeUser,LastSeen, building, office, port, switch, history, LastVlan,
v.value, stat.value
FROM systems s, vlan v, status stat
WHERE s.vlan=v.id AND s.status=stat.id AND s.status2

If I execute it from the mysql command line it works fine.
I was previously running mysql 4 and upgrade the server to mysql5 and the latest mydac, and then this problem started appearing.

Has mydac a problem with tables named "status"? I cannot easily change the table name..
I also tried to quote the table name, and setQuoteName=True, but to no avail.

Thanks,

Sean

Antaeus · Post by **Antaeus** » Fri 14 Jul 2006 10:01

We can't reproduce this problem if single table with STATUS name is used in SELECT statement. Please supply us with following information:
- Script to create tables that are used in your SELECT statement;
- Exact version of Delphi, C++ Builder or Kylix
- Exact version of MyDAC. You can see it in About sheet of TMyConnection Editor

sean · Post by **sean** » Fri 14 Jul 2006 16:02

The exact version was in the subject of the previous message.
Delphi is 7.

Tables:
CREATE TABLE `systems` (
`name` varchar(30) NOT NULL default 'xx',
`description` varchar(100) NOT NULL default '',
`vlan` int(11) NOT NULL default '13',
`mac` varchar(30) NOT NULL default '',
`comment` varchar(100) default '',
`ChangeDate` varchar(100) default '',
`ChangeUser` varchar(100) default '',
`status` tinyint(4) NOT NULL default '1',
`LastSeen` datetime default NULL,
`building` varchar(15) default 'Ber-Omu93',
`office` varchar(8) default NULL,
`port` varchar(20) default NULL,
`switch` varchar(20) default NULL,
`history` text,
`LastVlan` varchar(15) default NULL,
`os1` varchar(30) default NULL,
`os2` varchar(30) default NULL,
`class` int(11) default '0',
`os` int(11) default '0',
`r_ip` varchar(20) default NULL,
`r_timestamp` datetime default NULL,
`r_ping_timestamp` datetime default NULL,
`AuthLast` datetime default NULL,
`AuthPort` varchar(20) default NULL,
`AuthSw` varchar(20) default NULL,
`AuthVlan` int(11) default NULL,
`inventar` varchar(20) default NULL,
`os3` varchar(30) default NULL,
PRIMARY KEY (`mac`)
) ENGINE=MyISAM

CREATE TABLE `status` (
`id` int(10) unsigned NOT NULL default '0',
`value` varchar(30) NOT NULL default ''
) ENGINE=MyISAM

CREATE TABLE `vlan` (
`id` int(11) NOT NULL default '0',
`value` varchar(30) NOT NULL default '',
`vlan_description` varchar(100) default NULL,
`vlan_group` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

Regards,

Sean

eduardosic · Post by **eduardosic** » Fri 14 Jul 2006 23:11

>>> The exact version was in the subject of the previous message.

the last version is 4.30.1.15 not 4.30.1.17

with last version and mysql 5.0.22 works fine.

sean · Post by **sean** » Sun 16 Jul 2006 06:05

That really is the newest version I downloaded last week, and just verified the about box.

It *is* linked to the the name of a table being "status". If I create an identicat table called "vstatus", and query that, there is no problem. This means that the problem is not wioth the system tables, or the naming of a field "status".

So if its the table, I wonder why?
I also access the status table via MyTAble object, as opposed to a query. That is also fine.

I need to keep the table (for now), for compatibility reasons, can you suggest a work around or fix?

Thanks,

Sean

Antaeus · Post by **Antaeus** » Mon 17 Jul 2006 07:12

We have tested this issue with MySQL Server 5.0.18 for Windows but no problems were found. Possibly the name of the table that is on the server and name of the table that you use in your query are in different cases. Please check this. If it won't help to solve the problem, please send us (evgeniyD*crlab*com) a complete small sample to demonstrate it.

sean · Post by **sean** » Mon 17 Jul 2006 19:38

The query is done on a lowercase table, and the file name is as follows:

-rw-rw---- 1 mysql mysql 80 Nov 26 2004 /mysql/inventory/status.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 24 2005 /mysql/inventory/status.MYI
-rw-rw---- 1 mysql mysql 8588 Oct 8 2004 /mysql/inventory/status.frm

When I copy these files to a new name "vstatus" and queryvtsta fom MyDAC its OK:
-rw-r----- 1 mysql mysql 80 Jul 14 10:14 /mysql/inventory/vstatus.MYD
-rw-r----- 1 mysql mysql 1024 Jul 14 10:14 /mysql/inventory/vstatus.MYI
-rw-r----- 1 mysql mysql 8588 Jul 14 10:15 /mysql/inventory/vstatus.frm

I have to admit I'm perplexed, especially since you cannot reproduce.. I'll think a bit more...

Antaeus · Post by **Antaeus** » Tue 18 Jul 2006 07:26

Can you reproduce this problem on the same MySQL Server for Windows?

vtur · Post by **vtur** » Mon 20 Nov 2006 12:44

I have the same problem with the table name"status".
Mysql server version: 5.0.22-standard
Mydac 4.40.0.20 for Borland C++Builder 6

Error message is:# 42502Table xxxxxx.STATUS does not exist.
Real name of table is "status".
Problem persists only under Linux (case sensitive), under Windows MySQL server it works.

Do you have any solutions?

Antaeus · Post by **Antaeus** » Mon 20 Nov 2006 12:59

Please provide us with exact steps to reproduce this problem including all required SQL scripts (script to create table, script that causes this error, etc).

sean · Post by **sean** » Mon 20 Nov 2006 13:20

I renamed my table to 'vstatus'

Antaeus · Post by **Antaeus** » Wed 22 Nov 2006 11:13

Thank you for your cooperation. We have reproduced this behaviour. This is issue of MyDAC. Try to quote the `status` table name manually to avoid converting it to the upper case.

Devart Forums