Pages: 1

#1 2014-10-16 13:45:02

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

We tried to install a service at a customer wich we testet and programmed on Windows MySQL 5.6

CreateMissingTables works as expected.

No we installed it at a customer and he has MySQL 5.5 on Linux

CreateMissingTables makes Errors:

1. it creates the Table in the Database
2. After Restart it can not find the former created Tables nor the fields in it.

here is some Log Output from the Linux Server

20141015 10585206  +    TSQLDatabase(0288CD58).00AD13EA 
20141015 10585206 SQL   	CREATE VIRTUAL TABLE kVASYAuftraegeRecord USING External(kVASy_AuftragID TEXT COLLATE SYSTEMNOCASE, TODO TEXT COLLATE SYSTEMNOCASE, Status TEXT COLLATE SYSTEMNOCASE, TSYSAufgabenID INTEGER, CreationDate TEXT COLLATE ISO8601, LastChangeDate TEXT COLLATE ISO8601);
20141015 10585206  +    	TSQLDBUniDACStatement(03235F60).00AC9EF1 
20141015 10585206  +    		TSQLDBUniDACConnection(0288CD00).Connect to ProviderName=MySQL Database=manthey_turnus_schwerte on Server=192.168.101.231
20141015 10585206 DB    			Connected to MySQL (5.5.35-0ubuntu0.12.04.2)
20141015 10585206  -    		00.001.373
20141015 10585206  -    	00.001.464
20141015 10585206  +    	TSQLDBUniDACStatement(03235F60).00ACA045 
20141015 10585206 SQL   		TSQLDBUniDACStatement(03235F60) SELECT NOW()
20141015 10585206  -    	00.000.455
20141015 10585206  +    	TSQLDBUniDACStatement(032360E0).00AC9EF1 
20141015 10585206  -    	00.000.123
20141015 10585206  +    	TSQLDBUniDACStatement(032360E0).00ACA045 
20141015 10585206 SQL   		TSQLDBUniDACStatement(032360E0) CREATE TABLE kVASYAuftraegeRecord (ID  INT NOT NULL,kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE,TODO varchar(50) character set UTF8,Status varchar(50) character set UTF8,TSYSAufgabenID bigint,CreationDate datetime,LastChangeDate datetime, PRIMARY KEY(ID))
20141015 10585206 EXC   		EMySqlException ("\r\n#42S01Table 'kVASYAuftraegeRecord' already exists") at 008103D2  stack trace 00831ADF 00832212 0083225F 00687DAF 0083345A 00833526 0065543D 007F8888 006557D6 00655930 0040A2F7 0040A322 00ACD17C 00ACA1FA 00ABDAB8 00ABDACD 00ADA43C 00AD6903 00AE6067 00AE6294 
20141015 10585206 EXC   		EMyError ("\r\n#42S01Table 'kVASYAuftraegeRecord' already exists") at 0082B6A8  stack trace API 00535998 0040B0D0 771CB46B 77180133 0082B6A8 0082D2AF 00832212 00687DAF 0065543D 006557D6 00ACD17C 00ADA43C 00AD6903 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 
20141015 10585206 EXC   		EMyError ("\r\n#42S01Table 'kVASYAuftraegeRecord' already exists") at 0082B6A8  stack trace API 00535998 0040B0D0 771CB46B 77180133 0082B6A8 0082D2AF 00832212 00687DAF 0065543D 006557D6 00ACD17C 00ADA43C 00AD6903 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 
20141015 10585206 EXC   		EMyError ("\r\n#42S01Table 'kVASYAuftraegeRecord' already exists") at 0082B6A8  stack trace API 00535998 0040B0D0 771CB46B 77180133 0082B6A8 0082D2AF 00832212 00687DAF 0065543D 006557D6 00ACD17C 00ADA43C 00AD6903 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 
20141015 10585207 EXC   		EUniError ("\r\n#42S01Table 'kVASYAuftraegeRecord' already exists") at 007F0FAC  stack trace 00655930 0040A2F7 0040A322 00ACD17C 00ACA1FA 00ABDAB8 00ABDACD 00ADA43C 00AD6903 00AE6067 00AE6294 005174C6 008A5AB0 00AE6338 00406FAE 00AE0EF7 00406FAE 0040BD78 00ADFED6 00AE0F3D 
20141015 10585207 EXC   		EUniError ("\r\n#42S01Table 'kVASYAuftraegeRecord' already exists") at 007F0FAC  stack trace 00AD6903 00AE6067 00AE6294 005174C6 008A5AB0 00AE6338 00406FAE 00AE0EF7 00406FAE 0040BD78 00ADFED6 00AE0F3D 00B2B8BE 00B2BC5B 00B07125 00B2E763 00B0D794 00406FAE 00AE0EF7 00AE41D7 
20141015 10585207  -    	00.008.261
20141015 10585207  +    	TSQLDBUniDACStatement(032360E0).00AC9EF1 
20141015 10585207  -    	00.000.069
20141015 10585207  +    	TSQLDBUniDACStatement(032360E0).00ACA045 
20141015 10585207 SQL   		TSQLDBUniDACStatement(032360E0) ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE
20141015 10585207 EXC   		EMySqlException ("\r\n#42S21Duplicate column name 'kVASy_AuftragID'") at 008103D2  stack trace 00831ADF 00832212 0083225F 00687DAF 0083345A 00833526 0065543D 007F8888 006557D6 00655930 0040A2F7 0040A322 00ACD17C 00ACA1FA 00ABDAB8 00ABDACD 00ADA43C 00AD6A70 00AE6067 00AE6294 
20141015 10585207 EXC   		EMyError ("\r\n#42S21Duplicate column name 'kVASy_AuftragID'") at 0082B6A8  stack trace API 00535998 0040B0D0 771CB46B 77180133 0082B6A8 0082D2AF 00832212 00687DAF 0065543D 006557D6 00ACD17C 00ADA43C 00AD6A70 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 
20141015 10585207 EXC   		EMyError ("\r\n#42S21Duplicate column name 'kVASy_AuftragID'") at 0082B6A8  stack trace API 00535998 0040B0D0 771CB46B 77180133 0082B6A8 0082D2AF 00832212 00687DAF 0065543D 006557D6 00ACD17C 00ADA43C 00AD6A70 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 
20141015 10585207 EXC   		EMyError ("\r\n#42S21Duplicate column name 'kVASy_AuftragID'") at 0082B6A8  stack trace API 00535998 0040B0D0 771CB46B 77180133 0082B6A8 0082D2AF 00832212 00687DAF 0065543D 006557D6 00ACD17C 00ADA43C 00AD6A70 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 
20141015 10585207 EXC   		EUniError ("\r\n#42S21Duplicate column name 'kVASy_AuftragID'") at 007F0FAC  stack trace 00655930 0040A2F7 0040A322 00ACD17C 00ACA1FA 00ABDAB8 00ABDACD 00ADA43C 00AD6A70 00AE6067 00AE6294 008A5AB0 00AE6338 00406FAE 00AE0EF7 00406FAE 0040BD78 00ADFED6 00AE0F3D 00B2B8BE 
20141015 10585207 EXC   		EUniError ("\r\n#42S21Duplicate column name 'kVASy_AuftragID'") at 007F0FAC  stack trace 00AD6A70 00AE6067 00AE6294 008A5AB0 00AE6338 00406FAE 00AE0EF7 00406FAE 0040BD78 00ADFED6 00AE0F3D 00B2B8BE 00B2BC5B 00B07125 00B2E763 00B0D794 00406FAE 00AE0EF7 00AE41D7 00AE498B 
20141015 10585207  -    	00.008.558
20141015 10585207 EXC   	EORMException ("TSQLRestStorageExternal.Create: TSQLkVASYAuftraegeRecord: unable to create external missing field kVASYAuftraegeRecord.kVASy_AuftragID - SQL=\"ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE\"") at 00AD6AFA  stack trace API 00535998 0040B0D0 771CB46B 77180133 00AD6AFA 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 00AD456A 00AD369D 00AD1423 00ADD135 00B51300 00B50F17 00B52479 00B54E5A 
20141015 10585207 info  	TSQLRestStorageExternal.Destroy -> {"TSQLRestStorageExternal(02815EC0)":{}}
20141015 10585207 EXC   	EORMException ("TSQLRestStorageExternal.Create: TSQLkVASYAuftraegeRecord: unable to create external missing field kVASYAuftraegeRecord.kVASy_AuftragID - SQL=\"ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE\"") at 00AD6AFA  stack trace API 00535998 0040B0D0 771CB46B 77180133 00AD6AFA 008A5AB0 00ADFED6 00B2B8BE 00B2BC5B 00B07125 00B004B9 00B005DF 00AD456A 00AD369D 00AD1423 00ADD135 00B51300 00B50F17 00B52479 00B54E5A 
20141015 10585207 EXC   	EORMException ("TSQLRestStorageExternal.Create: TSQLkVASYAuftraegeRecord: unable to create external missing field kVASYAuftraegeRecord.kVASy_AuftragID - SQL=\"ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE\"") at 00AD6AFA  stack trace 00AE0F3D 00B2B8BE 00B2BC5B 00B07125 00B2E763 00B0D794 00406FAE 00AE0EF7 00AE41D7 00AE498B 00AE0F69 00AE4FBE 00AFD090 00AFC9D6 00B0DA3B 00B0E589 00B09FFD 00AFD020 00AFCA58 00AE0F69 
20141015 10585208 EXC   	ESQLite3Exception ("Error SQLITE_ERROR (1) - \"TSQLRestStorageExternal.Create: TSQLkVASYAuftraegeRecord: unable to create external missing field kVASYAuftraegeRecord.kVASy_AuftragID - SQL=\"ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE\"QUE\"\"") at 00AD4A6A  stack trace 00B51300 00B51270 00B50F17 74E148A1 74E148A1 74E148A1 74E148A1 0052119A 00B52479 00B529AB 00B54E5A 00B5541B 0094BB7D 0094B7BC 0094BBF5 0095F6DB 0095F705 00956073 009560BB 004CE564 
20141015 10585208  -    00.027.617
20141015 10585208  +    TSQLDatabase(0288CD58).00AD13EA 
20141015 10585208 SQL   	ROLLBACK TRANSACTION;
20141015 10585208  -    00.000.023
20141015 10585208 EXC   ESQLite3Exception ("Error SQLITE_ERROR (1) - \"TSQLRestStorageExternal.Create: TSQLkVASYAuftraegeRecord: unable to create external missing field kVASYAuftraegeRecord.kVASy_AuftragID - SQL=\"ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE\"QUE\"\"") at 00AD4A6A  stack trace API 00535998 0040B0D0 771CB46B 77180133 00AD4A6A 00AD369D 00AD1423 00ADD135 00B51300 00B50F17 00B52479 00B54E5A 00B5541B 0094B7BC 00956073 0040BC82 758C338A 771A9F72 771A9F45 
20141015 10585208  +    TSQLRestServerDB(027FC900).Shutdown
20141015 10585208 info  	CurrentRequestCount=0
20141015 10585209  -    00.010.712
20141015 10585209 info  TSQLRestServerDB.Destroy -> {"TSQLRestServerDB(027FC900)":{"NoAJAXJSON":false,"HandleAuthentication":true,"StaticVirtualTableDirect":true}}
20141015 10585209  +    00AD23A0 
20141015 10585209  -    00.000.015
20141015 10585209 EXC   ESQLite3Exception ("Error SQLITE_ERROR (1) - \"TSQLRestStorageExternal.Create: TSQLkVASYAuftraegeRecord: unable to create external missing field kVASYAuftraegeRecord.kVASy_AuftragID - SQL=\"ALTER TABLE kVASYAuftraegeRecord ADD kVASy_AuftragID varchar(30) character set UTF8 NOT NULL UNIQUE\"QUE\"\"") at 00AD4A6A  stack trace 0094BB7D 0094B7BC 0094BBF5 0095F6DB 0095F705 00956073 009560BB 004CE564 0040BC82 758C338A 771A9F72 771A9F45

our UniDAC Version is 5.5.12

Rad Studio 12.1 Santorini

Offline

#2 2014-10-16 16:36:15

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

What does UniDAC return for the table schema?
There seems to be a problem with UniDac reading MetaData from the DB.
It is perhaps not a problem of SynDB/mORMot, but on the UniDac side.

Offline

#3 2014-10-17 09:45:58

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

May be you are right,

first it want's to create a table which obviously exists. Then it want's to create a field which exists ...
But my Problem is connected to the other i postet.

What i saw in the source of mORMot, debugging CreateMissingTables is that the statement which selects the existing tables is written as SQLite Query.

'SELECT name FROM sqlite_master WHERE type=''table'' AND name NOT LIKE ''sqlite_%'';'

Should SQLite Engine transfer this statement to a UniDAC GetTablenames Statement ? Or a MySQL Query to get Tablenames ?

Last edited by itSDS (2014-10-17 09:48:58)

Rad Studio 12.1 Santorini

Offline

#4 2014-10-17 10:05:42

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

Hi i digged a little in CreateMissingTables and SynDBUniDac

the call of

  fDB.GetTableNames(TableNamesAtCreation);

in CreateMissingTables does not result in

TSQLDBUniDACConnectionProperties.GetTableNames

is this a bug ?

Rad Studio 12.1 Santorini

Offline

#5 2014-10-17 16:09:34

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

The SQLite statement you discovered is at virtual table level only.

Then there is another CREATE TABLE, directly to the external DB, at mORMotDB.pas level.
This is the one which is not retrieve the table names.

Do you have some simple code to reproduce the problem?

Offline

#6 2014-10-18 08:44:14

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

ok - now i understand.
But back to my Error - Log

At 

TSQLRestStorageExternal.Create(aClass: TSQLRecordClass; aServer: TSQLRestServer);
...
 fProperties.GetFields(fTableName,fFieldsExternal);
...

the GetFields seems to get no fields (REASON unknown atm)
the code goes into Create Table, were ExecuteDirect results in nil (cause of Create Table Exception)

      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then begin
        fProperties.GetFields(fTableName,fFieldsExternal); // fields from DB after create
        if fFieldsExternal=nil then
          raise EORMException.CreateUTF8('%.Create: external table creation % failed:'+
            ' GetFields() returned nil - SQL="%"',[self,StoredClass,fTableName,SQL]);
      end;

no EORMException is raised. As Result Create Function tries no to create all Missing fields wich results in the next Exceptions.

I think we have to points now:

1. Why does Ubuntu MySQL 5.5 not return the Fieldlist of the Table ?
2. Why do you try to create Fields if Create Tables fails smile

I'll have a look at GetFields.

Rad Studio 12.1 Santorini

Offline

#7 2014-10-19 07:18:05

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,661
Website

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

fProperties.GetFields() calls TSQLDBUniDACConnectionProperties.GetFields.

It uses UniDAC metadata functions to retrieve the table information.
It should return the fields as expected, especially if it does under Windows.
Perhaps with a MySQL under Linux, it expects TABLE_SCHEMA to be set.
Or perhaps there is a bug in UniDAC.
Could you ask in the UniDAC forums? Sounds very tied to UniDAC implementation, not in mORMot.

Offline

#8 2014-10-19 21:48:54

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 516

Re: Problem with UniDAC MySQL Windows 5.6 compared with Linux 5.5

Hi Ab, as i wrote here: http://synopse.info/forum/viewtopic.php?id=2065
i made this little Modification where i set SCOPE to LOCAL. May Be this does an error on the Linux MySQL - i'll try this out ans set Schema to the used Schema.

In all cases it is wrong not to set the Schema - Parameter in GetFields of UniDAC. If it is not set all Tables of the complete Database will be searched for fields.

Rad Studio 12.1 Santorini

Offline

Pages: 1

Board footer

Powered by FluxBB