mORMot Open Source

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

server does not creates tables

Hello,

pls show me my mistake.

I start my server on full empty mysql database.
And I am awaiting it will create all model tables for me.

  aProps := TSQLDBUniDACConnectionProperties.Create('MySQL', 'medplatform', '***', '***');
  aProps.SpecificOptions.Values['Server'] := '****';
  aProps.SpecificOptions.Values['Port'] := '3306';
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);

    MapFields(aModel);

    try
      DeleteFile('data1.db3');
      aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
      try
        aRestServer.CreateMissingTables; // create tables or fields if missing

Log:

20150604 11441159  +    TSQLDatabase(026E8D98).001606A8 SynSQLite3.TSQLDatabase.DBOpen (3743) 
20150604 11441159  +    	TSQLDatabase(026E8D98).0015F5F2 SynSQLite3.TSQLDatabase.Execute (3325) 
20150604 11441159 SQL   		PRAGMA cache_size=10000
20150604 11441159  -    	00.000.511
20150604 11441159  -    00.001.421
20150604 11441159  +    TSQLDatabase(026E8D98).0015FAE8 SynSQLite3.TSQLDatabase.Execute (3442) 
20150604 11441159 SQL   	SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
20150604 11441159  -    00.000.612
20150604 11441159 debug TSQLDatabase(026E8D98) TableNames=[]
20150604 11441159  +    TSQLDatabase(026E8D98).0015F5F2 SynSQLite3.TSQLDatabase.Execute (3325) 
20150604 11441159 SQL   	BEGIN TRANSACTION;
20150604 11441159  -    00.000.196
20150604 11441159  +    TSQLDatabase(026E8D98).0015F5F2 SynSQLite3.TSQLDatabase.Execute (3325) 
20150604 11441159 SQL   	CREATE VIRTUAL TABLE AuthUser USING External(LogonName TEXT COLLATE SYSTEMNOCASE, DisplayName TEXT COLLATE SYSTEMNOCASE, PasswordHashHexa TEXT COLLATE SYSTEMNOCASE, GroupRights INTEGER, Data BLOB);
20150604 11441159  +    	TSQLDBUniDACStatement(026CDE30).003131E1 SynDBDataset.TSQLDBDatasetStatementAbstract.Prepare (448) 
20150604 11441159  +    		TSQLDBUniDACConnection(02E7D678).Connect to ProviderName=MySQL Database=medplatform on Server=91.213.21.104
20150604 11441205 DB    			Connected to MySQL (5.5.42)
20150604 11441205  -    		00.134.042
20150604 11441205  -    	00.134.597
20150604 11441205  +    	TSQLDBUniDACStatement(026CDE30).00313364 SynDBDataset.TSQLDBDatasetStatementAbstract.ExecutePrepared (464) 
20150604 11441205 SQL   		TSQLDBUniDACStatement(026CDE30) SELECT NOW()
20150604 11441207  -    	00.037.259
20150604 11441543 EXC   	EMySqlException ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0031D29A MySqlNetUni.TMySqlNet.ReadError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 
20150604 11441626 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441627 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441627 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441628 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441628 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441629 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 
20150604 11441629 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441631 info  	TSQLRestStorageExternal.Destroy -> {"TSQLRestStorageExternal(0260B8C0)":{}}
20150604 11441631 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005994 System.@HandleAnyException 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError 003410DF MyClassesUni.TMySQLCommand.Check 0034564A MyClassesUni.TMySQLCommand.Execute 002F09AB CRAccess.TCRRecordSet.ExecCommand 002F2658 CRAccess.TCRRecordSet.ExecFetch 003471B8 MyClassesUni.TMySQLRecordSet.InternalOpen 0021F717 MemData.TData.Open 0035087D MyClassesUni.TMySQLMetaData.GetIndex 003509B7 MyClassesUni.TMySQLMetaData.GetIndexes 002F6B93 CRAccess.TCRMetaData.GetMetaData 002D5EDF DBAccess.TDAMetaData.InternalOpen 0029A5C3 DB.TDataSet.SetActive 0029A40A DB.TDataSet.Open 001E1679 SynDB.TSQLDBConnectionProperties.GetIndexesAndSetFieldsColumnIndexed (4570) 003153C5 SynDBUniDAC.TSQLDBUniDACConnectionProperties.GetFields (292) 001EA3C6 mORMotDB.TSQLRestStorageExternal.Create (605) 
20150604 11441632 EXC   	EMyError ("\r\n#42S02Table 'medplatform.authuser' doesn't exist") at 0033F4B0 MyClassesUni.TMySQLConnection.MySQLError  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 
20150604 11441649 EXC   	ESQLite3Exception ("Error SQLITE_ERROR (1) - \"\r\n#42S02Table 'medplatform.authuser' doesn't existM\"") at 00162686 SynSQLite3.sqlite3_check (4530)  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 
20150604 11441650  -    04.857.691
20150604 11441650  +    TSQLDatabase(026E8D98).0015F5F2 SynSQLite3.TSQLDatabase.Execute (3325) 
20150604 11441650 SQL   	ROLLBACK TRANSACTION;
20150604 11441650  -    00.001.557
20150604 11441650 EXC   ESQLite3Exception ("Error SQLITE_ERROR (1) - \"\r\n#42S02Table 'medplatform.authuser' doesn't existM\"") at 00162686 SynSQLite3.sqlite3_check (4530)  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005E42 System.@ExceptionHandler 00162686 SynSQLite3.sqlite3_check (4530) 001617C3 SynSQLite3.TSQLRequest.Execute (4086) 0015F62B SynSQLite3.TSQLDatabase.Execute (3329) 00165292 mORMotSQLite3.TSQLRestServerDB.CreateMissingTables (842) 
20150604 11441651 EXCOS EAccessViolation (C0000005) at 00046BAC Classes.TThread.Terminate  stack trace API 000EBA48 SynCommons.SynRtlUnwind (42799) 00005E42 System.@ExceptionHandler 

Thanks a lot for advance.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: server does not creates tables

Are you using the latest 1.18 nightly version of the source?

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: server does not creates tables

I say sorry for delay.

Today I downloaded and applied night build.

I have very simple code:

  FLocalModel := LocalModel;

  aProps := TSQLDBUniDACConnectionProperties.Create(TSQLDBUniDACConnectionProperties.URI(dMySQL,'***:***'), 'mednet', '***', '***');
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';

  VirtualTableExternalRegisterAll(FLocalModel, aProps, [regMapAutoKeywordFields]);

  FLocalRestServer := TSQLRestServerDB.Create(FLocalModel, SQLITE_MEMORY_DATABASE_NAME, true); // authentication=true
  FLocalRestServer.CreateMissingTables;

and I have an exception on last row of the snippet:

First chance exception at $7673B727. Exception class EMySqlException with message
#42S02Table 'mednet.transfer' doesn't exist'.

What is correct way to create automatically tables in mysql?

Thanks

Last edited by alexdmatveev (2015-06-16 11:28:46)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: server does not creates tables

I do not have MySQL at hand here, so perhaps you may try to find out what is wrong on your side.

Also try another library: perhaps there is an issue with your version of UniDAC.
SynDBZeos has been reported as working well.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: server does not creates tables

Yes, it works fine with Zeos.

Thanks a lot.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: server does not creates tables

Currently in our project it is very critically to exchange ZeosLib with Unidac.
Could you check model creation problem with unidac that I described above?

What should I do for it? Create a "feature request" ticket?

Thanks a lot.

erick

Member

Registered: 2015-09-09

Posts: 155

Re: server does not creates tables

I have a similar problem when using the following code to mysql/mariadb (both fail)

  aModel := TSQLModel.Create([TSQLNotesDB, TSQLStudentsDB], ROOT_NAME);
  try
    pseudoname := 'engugrad';
    MySQL := TOLEDBODBCSQLConnectionProperties.Create(pseudoname, pseudoname,
      '', 'blah', 'blah');
    VirtualTableExternalRegisterAll(aModel, MySQL);

    try
        // create the main mORMot server
        aServer := TSQLRestServerDB.Create(aModel, ':memory:', True);
        try
          // create tables or fields if missing
          aServer.CreateMissingTables;

     
First, CreateMissingTables spits out an exception saying NotesDB table does not exist,
which is true, I'm wanting CreateMissingTables to create it.

The next exception complains from the ODBC driver that CLOB used in trying to create
the table generates an error when I try to use RawUTF8 fields..

If I add index 32 to the RawUTF8, it complains about adding NVARCHAR(32) as being a syntax error.

Problem is, the customer does not want to add Zeos drivers, can SQL over ODBC be tuned to work?

Erick

erick

Member

Registered: 2015-09-09

Posts: 155

Re: server does not creates tables

I see firedac can support mysql, but I only have Delphi professional version, and we want database stored on a separate server, so firedac is out.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: server does not creates tables

You are using OleDB here over ODBC.
Please try directly with SynODBC.

I guess that the ORM is not able to recognize the underlying MySQL database from your OleDB connection string.
If it tries to create NVARCHAR() fields, the DBMS field is not dMySQL.
It should try to create varchar(%) character set UTF8 columns (see DB_FIELDS[] constant in SynDB.pas).

BTW, which version of MySQL are you using?

erick

Member

Registered: 2015-09-09

Posts: 155

Re: server does not creates tables

That works like a charm, thanks.

I'm using both 5.5 ancient, and MariaDB 10.x latest.

Both worked.