Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC MySQL: Table doesn't exist



Permlink Replies: 20 - Last Post: Oct 21, 2015 2:53 AM Last Post By: Andrea Kaiser
Andrea Kaiser

Posts: 63
Registered: 11/17/15
FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2015 5:06 AM
1. I drop a TFDPhysMySQLDriverLink on a form:
Name: FDPhysMySQLDriverLink1
DriverId: MySQL_DriverID
VendorLib: C:\Temp\Test\MySQL_Release\libmysqld.dll

2. I drop a TFDConnection on the form:
Name: FDConnection1
DriverName: FDPhysMySQLDriverLink1

3. I use the connection editor of FDConnection1, fill in the fields:
Database: Test
User_Name, Server, Port etc.
I click the button "Test" - Connection OK!

4. I drop a TFDQuery on the form:
Name: FDQuery1
Connection: FDConnection1

5. I use the Query editor of FdQuery1 and enter as an SQL statement: "select * from address"

6. When I execute it, the following message appears: [FireDAC][Phys][MySQL] Table 'test.address' doesn't exist. But, of course, this table DOES exist.

What am I doing wrong?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2015 6:33 PM   in response to: Andrea Kaiser in response to: Andrea Kaiser
6. When I execute it, the following message appears: [FireDAC][Phys][MySQL] Table 'test.address' doesn't exist. But, of course, this table DOES exist.

How you have verified that 'address' table exists in 'test' database ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 12:29 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
6. When I execute it, the following message appears: [FireDAC][Phys][MySQL] Table 'test.address' doesn't exist. But, of course, this table DOES exist.

How you have verified that 'address' table exists in 'test' database ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Yes, 'address' exists in the 'test' database.
I tried a lot of other things to connect MySQL to FireDAC, I consulted tutorials - nothing seems to work.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 1:09 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
Yes, 'address' exists in the 'test' database.

How you have verified that 'address' table exists in 'test' database ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 1:34 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Yes, 'address' exists in the 'test' database.

How you have verified that 'address' table exists in 'test' database ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect

I can manage and check it with Delphi 7 and SqlDirect. This table is definitely there.
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 6:03 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
To get to the bottom of this, I decided to fill out everything in the code and then try to create a db and a table. Just to make sure.

I put the components on the form, but don't edit any settings. I do this in a procedure.

When I hit the button that activates this procedure, the program just closes without any messages:

    with FDPhysMySQLDriverLink1 do begin
      DriverID  := 'MySQL';
      VendorLib := 'C:\Temp\Test_FireDAC\MySQL_Release\libmysqld.dll';
    end;
 
    with FDConnection1 do begin
      DriverName  := 'MySQL';
      LoginPrompt := false;
      Params.Database := 'Test';
      Params.DriverID := 'MySQL';
      Params.UserName := 'root';
      Connected := true;                        <----- Here the program crashes
    end;
 
    with FDQuery1 do begin
      Connection := FDConnection1;
      SQL.Add('select * from address');
    end;
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 6:24 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
VendorLib := 'C:\Temp\Test_FireDAC\MySQL_Release\libmysqld.dll';
...
Connected := true; <----- Here the program crashes

You are using MySQL Embedded. It requires additional configuration:
http://docwiki.embarcadero.com/RADStudio/Seattle/en/Connect_to_MySQL_Server_%28FireDAC%29#MySQL_Embedded_Server

To verify the table existence you can do:
* run mysql.exe
* type:
use test;
select * from address;


Does it work ?

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 7:13 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
First of all, thanks for your time, Dmitry, it's much appreciated.
I'm evaluating Delphi 10 Seattle Architect and since FireDAC seems to be the new hot thing,
I try to establish any SQL-Connections with those components.

Thanks for the link, I added embedded arguments.
I changed the VendorLib and the basedir to the original installation of MySQL (4.0.17).
Not sure about the '--skip-innodb', but this is surely not the problem.

    with FDPhysMySQLDriverLink1 do begin
      DriverID  := 'MySQL';
      EmbeddedArgs.Add('--basedir=<C:\MySQL>');
      EmbeddedArgs.Add('--datadir=<C:\Server\MySQL>');
      EmbeddedArgs.Add('--skip-innodb');
      VendorLib := 'C:\MySQL\Embedded\DLL\release\libmysqld.dll';
    end;
 
    with FDConnection1 do begin
      DriverName  := 'MySQL';
      LoginPrompt := false;
      Params.Database := 'Test';
      Params.DriverID := 'MySQL';
      Params.UserName := 'root';
      Connected := true;              <---- still crashes here
    end;


use test;
select * from address;


Yes, it works - I receive all records from this table.
quinn wildman

Posts: 856
Registered: 12/2/99
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 9:28 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
Try making a connection on Data Explorer. Is the table listed under
tables? If so, drag and drop the database and table from Data Explorer
onto a form. Then look at TFDQuery that gets created and see how it is
different.

Andrea Kaiser wrote:
1. I drop a TFDPhysMySQLDriverLink on a form:
Name: FDPhysMySQLDriverLink1
DriverId: MySQL_DriverID
VendorLib: C:\Temp\Test\MySQL_Release\libmysqld.dll

2. I drop a TFDConnection on the form:
Name: FDConnection1
DriverName: FDPhysMySQLDriverLink1

3. I use the connection editor of FDConnection1, fill in the fields:
Database: Test
User_Name, Server, Port etc.
I click the button "Test" - Connection OK!

4. I drop a TFDQuery on the form:
Name: FDQuery1
Connection: FDConnection1

5. I use the Query editor of FdQuery1 and enter as an SQL statement: "select * from address"

6. When I execute it, the following message appears: [FireDAC][Phys][MySQL] Table 'test.address' doesn't exist. But, of course, this table DOES exist.

What am I doing wrong?
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 11:07 AM   in response to: quinn wildman in response to: quinn wildman
quinn wildman wrote:
Try making a connection on Data Explorer. Is the table listed under
tables? If so, drag and drop the database and table from Data Explorer
onto a form. Then look at TFDQuery that gets created and see how it is
different.

With the data explorer the problem with the vendor lib occurs again.

Has anybody a sample how I connect in a simple way FireDAC with MySQL? I prefer code instead of properties - so it's easier to understand.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2015 2:43 PM   in response to: Andrea Kaiser in response to: Andrea Kaiser
Has anybody a sample how I connect in a simple way FireDAC with MySQL? I prefer code instead of properties - so it's easier to understand.

Do you really need MySQL Embedded ? If not then:
* specify libmysql.dll instead of libmysqld.dll
* remove EmbeddedArgs
* try to connect to your MySQL server.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 1:05 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Do you really need MySQL Embedded ? If not then:
* specify libmysql.dll instead of libmysqld.dll
* remove EmbeddedArgs
* try to connect to your MySQL server.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

I changed the code:
    with FDPhysMySQLDriverLink1 do begin
      DriverID  := 'MySQL';
      VendorLib := 'C:\MySQL\bin\libmySQL.dll';
    end;
 
    with FDConnection1 do begin
      DriverName  := 'MySQL';
      LoginPrompt := false;
      Params.Database := 'Test';
      Params.DriverID := 'MySQL';
      Params.UserName := 'root';
      Connected := true;
    end;
 
    with FDQuery1 do begin
      Connection := FDConnection1;
      SQL.Add('select * from address');
      Active := true;                <----- Crash
    end;


and I get an access violation, when the program activates the FDQuery1 component:

Access violation at 151EDC04 in 'FireDACCommon230.bpl'. read of address 00000035


Should I use another component than FireDAC to connect to MySQL? What would be the best component for that in Delphi 10 Seattle?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 2:08 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
Please post your test application to attachments forum and I will review it.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 2:51 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Please post your test application to attachments forum and I will review it.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Did it: https://forums.embarcadero.com/thread.jspa?threadID=118317&stqc=true
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 3:33 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
1) It is strange that you are getting AV in FireDACCommon230.bpl, but your
project does not have "Link with runtime packages" = True. Do you really get
described AV when you run your application ?
2) Could you please post to attachments also your libmysql.dll.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 5:02 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
1) It is strange that you are getting AV in FireDACCommon230.bpl, but your
project does not have "Link with runtime packages" = True. Do you really get
described AV when you run your application ?
2) Could you please post to attachments also your libmysql.dll.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

1) Yes, I really get this AV with the exact project I attached.

2) Done in the attachment forum: https://forums.embarcadero.com/thread.jspa?messageID=712702#712702
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC MySQL: Table doesn't exist [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 5:39 AM   in response to: Andrea Kaiser in response to: Andrea Kaiser
1) Yes, I really get this AV with the exact project I attached.

Strange ...


This is a bug in libmysql.dll v 4.0.17, which returns uninitialized pointer
in one place. There are two basic options:
* upgrade libmysql.dll to 5.0.xxx series. Not higher, because probably
it will be incompatible with 4.0.17 server.
* upgrade both client and server to current MySQL versions.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
L
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 6:28 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
This is a bug in libmysql.dll v 4.0.17, which returns uninitialized pointer
in one place. There are two basic options:
* upgrade libmysql.dll to 5.0.xxx series. Not higher, because probably
it will be incompatible with 4.0.17 server.
* upgrade both client and server to current MySQL versions.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

This bug never occurred in Delphi 7 with SqlDirect oder ADO components.

I have a problem with upgrading: a lot of my customers use MySQL 4 and I can't force them to upgrade.
Isn't there another solution? Like using embedded MySQL in FireDAC?
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: L
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 11:00 PM   in response to: Andrea Kaiser in response to: Andrea Kaiser
I installed MySQL 5.5 and it worked.

Thanks a lot for your help, Dmitry!

Now I' going to test how I can get information about the SQL data definition (tables, fields etc) of any database via FireDAC.
Can you point me in the right direction?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: L
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 20, 2015 11:45 PM   in response to: Andrea Kaiser in response to: Andrea Kaiser
This bug never occurred in Delphi 7 with SqlDirect oder ADO components.

Because they use less DBMS API information than FireDAC.

Isn't there another solution?

You can change FireDAC sources to workaround this issue.

Now I' going to test how I can get information about the SQL data definition (tables, fields etc) of any database via FireDAC.
Can you point me in the right direction?

http://docwiki.embarcadero.com/RADStudio/Seattle/en/Working_with_Metadata_%28FireDAC%29

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Andrea Kaiser

Posts: 63
Registered: 11/17/15
Re: L
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 21, 2015 2:53 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
This bug never occurred in Delphi 7 with SqlDirect oder ADO components.

Because they use less DBMS API information than FireDAC.

Isn't there another solution?

You can change FireDAC sources to workaround this issue.

Now I' going to test how I can get information about the SQL data definition (tables, fields etc) of any database via FireDAC.
Can you point me in the right direction?

http://docwiki.embarcadero.com/RADStudio/Seattle/en/Working_with_Metadata_%28FireDAC%29

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Thank you very much.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02