Devart Forums

I have an application that uses a query with "SHOW FIELDS FROM xxxxx" to get the field information of tables for diferente operation. However, on some cases, TMyQuery gives a runtime error for no apparent reason. The table does exists and if I execute the same query with a diferent tool (SqlYog, MySql-Front, MySql Client) on the databese, it executes correcty so the problem is not the query.

Specifically the query is:

SHOW FIELDS FROM acumuni

The server version is MySql 4.0.24 under linux
The MyDac version is 4.40.0.19 under Delphi 7.

The "acumuni" table is a MRG_MYISAM table.

I have also used this command on other MRG_MYISAM tables without a problem. The problem seems to be this specific table. I have Checked and repaired all tables that make up "acumuni" so table corruption is not the problem.

Any help is greatly apreciated. Please advise me if there is any other information you need to help you.

Regards
Jose Ostos

Please provide us with script to create table that causes the problem and script to fill this table with data if it is necessary.

The problem is very strange and dificult to reproduce for you. If I recreate the tables with a script, the error does not occur, only when they have large amounts of data.

The table "acumuni" is a merge of tables "acumuni1997" --> "acumuni2006", one for each year. The total record count of all tables is 20,480,972 records, which is why it is split in the first place. If I truncate all tables, no error occurs. If I delete some records of some of the tables, the error does not occur.

This would lead to believe that a table is corrupt. However, I have checked all the tables extensively with MyIsamchk and no error is reported.

The strange thing is that under the same conditions ONLY MYDAC gives me an error. I execute the "SHOW FIELDS FROM acumuni" in MySql and it works, from MySql-Front and it works, from SQLYog and it works. So MyDac MUST be doing something diferent that under certain unknown condition, it gives the error.

Doing more tests, I discovered that it only gives an error when the TMyConnection component used has the propertyr Options.Direct = True. If it is false, and it uses "LibMySql.dll", no error occurs so I assume that it must have something to do with the "Direct" driver you use.

I cannot reproduce a small dataset for you to check this with but I can offer to upload it to an FTP where you can download it from, the zipped file weighs about 320Mb. I am now uploadong the file. and it may take a while to finish uploading.

You can download it from:

ftp://ostosturner.com/

User: corelab
password: support

I hope this helps you solve the issue. Feel free to ask whatever is necessary to solve this.

Regards
Jose Ostos

Try to perform some other tests:
1) create a new table with the same structure and type (e.g. "acumuni_1"), fill it with data from "acumuni" and execute SHOW COLUMNS on the new table. Possibly mysqldump.exe utility of MySQL server or TMyDump component of MyDAC will be helpful
2) change type of the table to MyISAM or InnoDB and execute SHOW COLUMNS
3) perform TRUNCATE TABLE command before execution of SHOW COLUMNS
Inform us about results of all these tests and exact error message you get.

The table "acumuni" is a MERGE tabla and has no actual data. I have tried TRUNCATE TABLE on some of the component tables and the rror does not occur. Doing the SHOW FIELDS on any of the component tables (e.g. acumuni1999) does not give an error.

The point is:

1) The error occurs only under certain circumstances.
2) MySql reports no errors with the tables.
3) The error ONLY occurs with the "Direct" option and not when LibMySql.dll is used. I think that this point is a KEY to the situacion.

The best way to pinpoint the exact problem is for you to trace the command with the data that is known to reproduce the problem and analyze where and why it produces the error. This data is available to you.

I have already worked around the specific problem. I think that changing the data to make it work will not solve the real problem from its root. As long as the real issue is resolved, it may appear later on in a diferent way. The point is that I cannot trust the components if unexpected things happen under certain "unknown" conditions. We have the oportunity to find this strange and peculiar bug in the "Direct" driver and it should be taken to really fix it from its root.

I am aware that the data file is HUGE, but you can download it even if it takes a long time (all night if necessary) if that is what is necessary to identify the problem.

It is in everybody's best interest to fix the tools that we all use and depend on.

Once you have the data, I am sure that finding the problem will run smoothly.

Regards
Jose Ostos

We agree with you. If the problem is in our Direct mode it must be solved by fixing MyDAC. And we want you to help us. Size of test data you have is realy greate and it is necessary to decrese its size.
> I have tried TRUNCATE TABLE on some of the component tables and the rror does not occur
Try to determine which table from union causes the problem. Or possibly the problem is caused by total numder of records.

Beleive me, I have tried to reduce the size, but when I reduce it the error disappears and I have not been able to determine any one component to be at fault. I know your position about the size because with my customers I am also in you same shoes.

I usually make an extreme effort to give support (you or others) VERY SMALL samples both in code and data because I KNOW that the smaller it is, the problem is more isolated and easier to find. I do my best to pinpoint the problem so that it can easily and quickly be resolved, it is in my own best interest.

So believe me, I CANNOT make it smaller because then the situation does not happen. So PLEASE download the data, you can leave it downloading at night while you sleep or doing something else, it should not take more than a few hours. If you have problems with the FTP, advise me and I'll look for another way send it to you.

Regards
Jose Ostos

Is this problem reproducible on MySQL Server for Windows?

Try to make an application that does following things:
- connects to server,
- creates tables,
- fills these tables with random data (TMyLoader component can be used),
- executes the query that causes the problem.

Also supply us with exact version of MySQL client. You can see it in Info sheet of TMyConnection Editor.

The problem occurs both in linux server 4.0.24 and in windows server 4.0.26. The production DB is Linux and the development DB is on windows. In fact, the tables I put on the FTP are from the windows server.

As I have repeated before, creating the tables or changing them or whatever will not reproduce the problem. The problem occurs specifically with the data files I put on the FTP at your disposal.

I don't see how the Client version will help if the problem occurs when the client is NOT used (Direct=True). If it is ued (Direct=False) the error does not occur.

The other tools I have checked with (SqlYog, MySql-Front, MySql Client) all use LibMySql (Client Library) and none give the error which is a strong indication that the problem is MOST LIKEKY to be in the DIRECT driver (No MySql Client).

Why can't you just DOWNLOAD the data from the FTP?

We have spent more time arguing about this than it would have taken you to download the files. Do I have to BEG YOU to download the files? Just download the data, check it, and see for yourself.!!!! PLEASE !!!!.

Regards
Jose Ostos

Please supply us with libmysql.dll version you use. And check if the problem can be reproduced using client library of another version (first of all 4.1).
Send us your registration information to following e-mail address: evgeniyD*crlab*com.
Is it possible to grant us access to your MySQL Server via internet?

I already sent the registration information you requested.

The application is used exclusively in-house database and there is no posibility of making available to the internet. We don't even have a static IP.

As I Mentioned before, the application DOES NOT use libmysql.dll client since it connects using the "Direct=True" option. If I use the libmysql.dll (version that comes with 4.0.26) the error does not occur. I will however upload it to the FTP.

Have you even downloaded the data files and tested them?

Regards
Jose Ostos

We have answered you by e-mail.