Problem with MySQL and CreateMissingTables

itSDS · 2014-10-02 12:45:20

Hi AB,

today i tried to optimize my Service which connects to a MySQL - Server with the following Statement

  FModel := CreateModel;
  FConnection := TSQLDBUniDACConnectionProperties.Create(TSQLDBUniDACConnectionProperties.URI(dMySQL,host + ':' + port), databasename, username, password);
  VirtualTableExternalRegisterAll(FModel, fConnection);
  FSQLDB := TSQLDatabase.Create(SQLITE_MEMORY_DATABASE_NAME, '');
  FDatabase := TSQLRestServerDB.Create(FModel, FSQLDB, true);
  FDatabase.CreateMissingTables(0);

because it is a service which every 10 min works with the database, i disconnect it after i dont use it.
To increase Perfomance i wanted to call CreateMissingTables only at Servicestart and removed the above line.
In that way:

  FModel := CreateModel;
  FConnection := TSQLDBUniDACConnectionProperties.Create(TSQLDBUniDACConnectionProperties.URI(dMySQL,host + ':' + port), databasename, username, password);
  VirtualTableExternalRegisterAll(FModel, fConnection);
  FSQLDB := TSQLDatabase.Create(SQLITE_MEMORY_DATABASE_NAME, '');
  FDatabase := TSQLRestServerDB.Create(FModel, FSQLDB, true);
  if IsServiceStarted then
    FDatabase.CreateMissingTables(0);

And here is the Problem:
Now the Connection does not connect to MySQL any more !!!
Allthough FConnection points to a MySQL Database.
Now i think in CreateMissingTable is some code which connects the SQLite Engine with the MySQL one ?!

Last edited by itSDS (2014-10-02 12:46:44)

ab · 2014-10-03 07:30:17

The mORMot orm will auto reconnect optionally.
You should better use this feature.

EgonHugeist · 2014-10-04 12:57:33

Note i did some MySQL reconnection refactoring on Zeos7.2:

Add

'MYSQL_OPT_CONNECT_TIMEOUT=x'
'MYSQL_OPT_RECONNECT=TRUE'

To the Connection/ZURL properties and the clientdll is doing the job. Don't know what happens if a connection is broken -> still missing a valid testcase ):

ab · 2014-10-04 13:15:59

Yes we are in favor of using zeos/zdbc with mORMot instead of UniDac.
Performance and integration are much better.

itSDS · 2014-10-08 11:59:01

I Implemented my Database as Global - Variable now and checked the Reconnect with mysql stop/start - works fine.
@Zeos I don't use Zeos cause i use UniDAC in all of my Projekts and know it well. In normal Projekts i don't thinks it's slow.

Do you know the Reason why UniDAC is slower as ZEOS ? It uses MySQL-Direct Interface to connect to MySQL.

ab · 2014-10-08 16:37:26

On reading, UniDAC would use a TDataSet for reading the results, which is a real bottleneck.
On writing, UniDAC is less optimized than the latest version of Zeos.

See http://blog.synopse.info/post/2014/03/0 … PostgreSQL

INSERTION SPEED:

ODBC MySQL	3160	38309	10856	47630
ZEOS MySQL	3426	34037	12217	40186
FireDAC MySQL	3078	43053	10955	45781
UniDAC MySQL	3119	27772	11246	33288

READING SPEED:

ODBC MySQL	10143	65538	82447
ZEOS MySQL	2052	171803	245772
FireDAC MySQL	3636	75081	105028
UniDAC MySQL	4798	99940	146968

Those numbers may be even better with the latest version of ZDBC/Zeos (Egon did make a lot of optimization work during the last months).

mORMot Open Source

#1 2014-10-02 12:45:20

Problem with MySQL and CreateMissingTables

#2 2014-10-03 07:30:17

Re: Problem with MySQL and CreateMissingTables

#3 2014-10-04 12:57:33

Re: Problem with MySQL and CreateMissingTables

#4 2014-10-04 13:15:59

Re: Problem with MySQL and CreateMissingTables

#5 2014-10-08 11:59:01

Re: Problem with MySQL and CreateMissingTables

#6 2014-10-08 16:37:26

Re: Problem with MySQL and CreateMissingTables

Board footer