Zeoslib Portal

This is the official forum of ZeosLib providing support to all ZeosLib users.
https://zeoslib.sourceforge.io/

MySQL identifier case questions

https://zeoslib.sourceforge.io/viewtopic.php?t=131179

Page 1 of 2

MySQL identifier case questions

Posted: 07.01.2021, 19:34
by leus
Hello,

I've been having problems with MySQL with mixed case tables and columns.

First, the following code in src/dbc/ZDbcMetadata.pas, TZAbstractDatabaseMetadata.NormalizePatternCase seems weird to me, because it is converting mixed case identifiers (like a table name) to lowercase.

Code: Select all

   icMixed: if not FDatabaseInfo.StoresLowerCaseIdentifiers then
                  if FDatabaseInfo.StoresUpperCaseIdentifiers then
                    Result := UpperCase(Pattern)
                  else
                    Result := LowerCase(Pattern);

The following code in TZDefaultIdentifierConvertor.GetIdentifierCase also seems wrong as it converts to lowercase when StoresUpperCaseIdentifiers is true (I filed a ticket yesterday):

Code: Select all

if Metadata.GetDatabaseInfo.StoresUpperCaseIdentifiers and (Result <> icUpper) then
      S := UpperCase(Value)
    else if not Metadata.GetDatabaseInfo.StoresUpperCaseIdentifiers and (Result <> icLower) then
      s := LowerCase(Value)
    else S := Value;

And also, the following strikes me also as wrong, since MySQL table names are case sensitive under many OSes, which makes MySQL de facto a mixed case identifier database. In my local copy I changed all the offending code

Looking at src/dbc/ZDbcMySqlMetadata.pas, I see

Code: Select all

function StoresLowerCaseIdentifiers: Boolean; override;
function StoresMixedCaseIdentifiers: Boolean; override;
//    function StoresUpperCaseQuotedIdentifiers: Boolean; override; -> Not implemented
...
function TZMySQLDatabaseInfo.StoresLowerCaseIdentifiers: Boolean;
begin
  Result := True; //https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html
end;
...
function TZMySQLDatabaseInfo.StoresMixedCaseIdentifiers: Boolean;
begin
  Result := False; //https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html
end;
I found out these issues while trying to run mORMot CreateMissingTables() in a MySQL server stored in Amazon. I have attached a little patch with my changes, but of course my testing has been very limited (tested on Windows / localhost and Amazon, which is Linux).

Re: MySQL identifier case questions

Posted: 08.01.2021, 10:29
by marsupilami
Hello leus,

thank you for contributing. I applied your patch to trunk in revision 7204. Currently I am waiting for the test suites to finish their runs.

Note: I saw you are using git for doing version control. You might want to base your work on the unofficial repository here. It gets updated on a nightly basis.

Best regards,

Jan

Re: MySQL identifier case questions

Posted: 08.01.2021, 11:48
by marsupilami
Hello leus,

your changes make a test on SQLite fail: TestCompTicket386 in test\bugreport\ZTestBugCompSQLite.pas fails now. Do you have a chance to look into this?
Some hints on running the test suites can be found on this wiki page. If you have questions regarding this, I will be happy to answer them.

Best regards,

Jan

Re: MySQL identifier case questions

Posted: 08.01.2021, 14:00
by MJFShark
Note that handling mixed case on MySQL is part of my previous pull request fixes. I've also made a matrix of the various "Stores" properties for each protocol and it looks like SQLLite sets both StoresLowerCaseIdentifiers and StoresUpperCaseIdentifiers to true (I don't use SQLLite but it seems like those settings should always be mutually exclusive.)

-Mark
P.S.: Related:
Firebird sets "SupportsMixedCaseIdentifiers" to true, I believe it should be false (though that particular setting isn't used as far as I can tell.)

Re: MySQL identifier case questions

Posted: 08.01.2021, 20:14
by leus
Thank you!

I'll definitely take a look and try to fix these issues.

Re: MySQL identifier case questions

Posted: 08.01.2021, 21:17
by leus
marsupilami wrote: ↑08.01.2021, 11:48 Hello leus,

your changes make a test on SQLite fail: TestCompTicket386 in test\bugreport\ZTestBugCompSQLite.pas fails now. Do you have a chance to look into this?
Some hints on running the test suites can be found on this wiki page. If you have questions regarding this, I will be happy to answer them.

Best regards,

Jan
I'll be happy to help, I already forked your repository. However, I cannot for the life of me make the test suite to run, I get "Requested database driver was not found" (I copied the dlls to the directory where the exe is, for SQLite3 I gave a full path, etc.) Is there documentation on the configuration file for the tests?

Re: MySQL identifier case questions

Posted: 08.01.2021, 22:06
by leus
marsupilami wrote: ↑08.01.2021, 11:48 Hello leus,

your changes make a test on SQLite fail: TestCompTicket386 in test\bugreport\ZTestBugCompSQLite.pas fails now. Do you have a chance to look into this?
Some hints on running the test suites can be found on this wiki page. If you have questions regarding this, I will be happy to answer them.

Best regards,

Jan
I cloned your repository and after some hair pulling I managed to run the tests for sqlite (the example template says "protocol: sqlite-3" and I changed to "sqlite", hope that's OK).

Here are the results from the test using my fork without any changes:

Code: Select all

C:\delphi\zeoslib\packages\DelphiXE10.3\build>ZTestAll.exe -b
Config File Name: C:\delphi\zeoslib\database\test.properties
Executing C:\delphi\zeoslib\database\drop_sqlite.sql
Executing C:\delphi\zeoslib\database\create_sqlite.sql
Executing C:\delphi\zeoslib\database\populate_any.sql

DUnit / Testing
............................................................................................................................................................................................................................................................F.............................................................................................................................................................F..F.......................................................................................
Time: 0:00:03.286

FAILURES!!!
Test Results:
Run:           397
Failures:        3
Errors:          0
There were 3 failures:
  1) TestCompTicket386: ETestFailure
     at
      "sqlite3/sqlite: the fieldcount of table table_ticket_386, expected: <3> but was: <0>"
  2) TestDbcBCDValues: ETestFailure
     at
      "sqlite3/sqlite: sqlite: BCD compare mismatch, for column "curr18_4", Expected: 12345678901234.5678 got: 12345678901234,6, expected: <0> but was: <1>"
  3) TestInsertFailAndCorrectCachedUpdates: ETestFailure
     at
      "sqlite3/sqlite: the constraint should forbit inserting the row, expected: <False> but was: <True>"
I still have doubts on how to actually run the test suite, but I cannot send private messages yet...

Re: MySQL identifier case questions

Posted: 08.01.2021, 22:31
by leus
Following my previous post, mysql test results from the same code, no changes:

Code: Select all

C:\delphi\zeoslib\packages\DelphiXE10.3\build>ZTestAll.exe -b
Config File Name: C:\delphi\zeoslib\database\test.properties
Executing C:\delphi\zeoslib\database\drop_mysql.sql
Executing C:\delphi\zeoslib\database\drop_mysql_bugreport.sql
Executing C:\delphi\zeoslib\database\create_mysql.sql
Executing C:\delphi\zeoslib\database\populate_any.sql
Executing C:\delphi\zeoslib\database\populate_mysql.sql
Executing C:\delphi\zeoslib\database\create_mysql_bugreport.sql

DUnit / Testing
..........................................................................................................................................................................................................................................................................................................E.......................................................................................................................E.E..............................................................F...........................................F..............................................................
Time: 0:00:09.289

FAILURES!!!
Test Results:
Run:           484
Failures:        2
Errors:          3
There were 3 errors:
  1) TestTicket304: EZSQLException
     at
      "SQL Error: Table 'zeos.tableticket304' doesn't exist
Code: 1146 SQL: delete from TableTicket304
"
  2) Test_abtest: EZSQLConnectionLost
     at
      "SQL Error: Lost connection to MySQL server during query
Code: 2013 SQL: CALL ABTEST(?,?,?,?,?)"
  3) Test_TEST_All_TYPES: EZSQLConnectionLost
     at
      "SQL Error: Lost connection to MySQL server during query
Code: 2013 SQL: CALL TEST_All_TYPES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

There were 2 failures:
  1) TestInsertFailAndCorrectCachedUpdates: ETestFailure
     at
      "mysql5/mysql: the constraint should forbit inserting the row, expected: <False> but was: <True>"
  2) TestSelectTwoQueriesGetMoreResults: ETestFailure
     at
      "mysql5/mysql: Final update count, expected: <0> but was: <3>"

Re: MySQL identifier case questions

Posted: 09.01.2021, 15:50
by marsupilami
Hello leus,

you should be able to send and receive private messages now. Regarding MySQLs tableticket304: I just added a patch to trunk to have it there too. I wonder why this change never made it there...

Regarding the other errors on MySQL: They don't fail on the test system, I will have to look into this at another time.

Regarding the fails on sqlite:
TestInsertFailAndCorrectCachedUpdates fails on the test system too.
TestDbcBCDValues doesn't fail. I assume that there is a problem with the test rather than with Zeos itself there.

Best regards,

Jan

Re: MySQL identifier case questions

Posted: 10.01.2021, 20:48
by leus
marsupilami wrote: ↑09.01.2021, 15:50 Hello leus,

you should be able to send and receive private messages now. Regarding MySQLs tableticket304: I just added a patch to trunk to have it there too. I wonder why this change never made it there...

Regarding the other errors on MySQL: They don't fail on the test system, I will have to look into this at another time.

Regarding the fails on sqlite:
TestInsertFailAndCorrectCachedUpdates fails on the test system too.
TestDbcBCDValues doesn't fail. I assume that there is a problem with the test rather than with Zeos itself there.

Best regards,

Jan
Okay. I still see errors on my side. The MySQL errors are probably because of my local version (I'm using MySQL 8 with the appropriate libmysql.dll).

Code: Select all

C:\delphi\zeoslib\packages\DelphiXE10.3\build>ZTestAll.exe -b
Config File Name: C:\delphi\zeoslib\database\test.properties
Executing C:\delphi\zeoslib\database\drop_mysql.sql
Executing C:\delphi\zeoslib\database\drop_mysql_bugreport.sql
Executing C:\delphi\zeoslib\database\create_mysql.sql
Executing C:\delphi\zeoslib\database\populate_any.sql
Executing C:\delphi\zeoslib\database\populate_mysql.sql
Executing C:\delphi\zeoslib\database\create_mysql_bugreport.sql

DUnit / Testing
..........................................................................................................................................................................................................................................................................................................E......................................................................................................................................................................................F...........................................F..............................................................
Time: 0:00:16.383

FAILURES!!!
Test Results:
Run:           484
Failures:        2
Errors:          1
There was 1 error:
  1) TestTicket304: EZSQLException
     at
      "SQL Error: Table 'zeos.tableticket304' doesn't exist
Code: 1146 SQL: delete from TableTicket304
"

There were 2 failures:
  1) TestInsertFailAndCorrectCachedUpdates: ETestFailure
     at
      "mysql5/mysql: the constraint should forbit inserting the row, expected: <False> but was: <True>"
  2) TestSelectTwoQueriesGetMoreResults: ETestFailure
     at
      "mysql5/mysql: Final update count, expected: <0> but was: <3>"

C:\delphi\zeoslib\packages\DelphiXE10.3\build>
The TestTicket304 test expects a table that's not present (I assume it should be in create_mysql_bugreport.sql but it is not).

The TestInsertFailAndCorrectCachedUpdates test I don't understand well the intention of that test is. Is it trying to check some constraint? (because the table doesn't have any in the c_dep_id column):

Code: Select all

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.0.21 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             11.1.0.6116
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for zeos
CREATE DATABASE IF NOT EXISTS `zeos` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `zeos`;

-- Dumping structure for table zeos.cargo
CREATE TABLE IF NOT EXISTS `cargo` (
  `c_id` bigint NOT NULL AUTO_INCREMENT,
  `c_dep_id` smallint DEFAULT NULL,
  `c_name` char(10) DEFAULT NULL,
  `c_seal` tinyint(1) DEFAULT NULL,
  `c_date_came` datetime DEFAULT NULL,
  `c_date_out` datetime DEFAULT NULL,
  `c_weight` float DEFAULT NULL,
  `c_width` int DEFAULT NULL,
  `c_height` int DEFAULT NULL,
  `c_cost` float(12,4) DEFAULT NULL,
  `c_attributes` blob,
  PRIMARY KEY (`c_id`),
  KEY `cargo_FK` (`c_dep_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32771 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table zeos.cargo: ~7 rows (approximately)
/*!40000 ALTER TABLE `cargo` DISABLE KEYS */;
REPLACE INTO `cargo` (`c_id`, `c_dep_id`, `c_name`, `c_seal`, `c_date_came`, `c_date_out`, `c_weight`, `c_width`, `c_height`, `c_cost`, `c_attributes`) VALUES
	(1, 2, 'Grain', 1, '2002-12-20 02:00:00', '2002-12-20 02:00:00', 5000, NULL, NULL, 1769.4301, NULL),
	(2, 1, 'Paper', 2, '2002-12-19 14:00:00', '2002-12-23 00:00:00', 1000, 10, 10, 986.4700, NULL),
	(3, 1, 'Wool', 0, '2002-12-20 18:00:00', NULL, 400, 7, 4, 643.1100, NULL),
	(4, 2, 'Suagr', 1, '2002-12-21 10:20:00', '2002-12-26 00:00:00', 2034, NULL, NULL, 1964.8700, NULL);
/*!40000 ALTER TABLE `cargo` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


The TestSelectTwoQueriesGetMoreResults I haven't checked yet, but I understand after the last GetMoreResults() call, GetUpdateCount() should return 0. Will do some debugging tomorrow.

Re: MySQL identifier case questions

Posted: 11.01.2021, 12:26
by marsupilami
Hello Leus,

just to be clear: The test suites don't run without errors currently. We are working in that. Most probably the additional errors you see occur because the tests aren't portable enough. It is not simple for me to run Zeos Tests in different locales....

Maybe it makes sense to just focus on TestCompTicket386?

Best regards,

Jan

Re: MySQL identifier case questions

Posted: 11.01.2021, 15:21
by leus
marsupilami wrote: ↑11.01.2021, 12:26 Maybe it makes sense to just focus on TestCompTicket386?
Oh. Yes, sure.

Re: MySQL identifier case questions

Posted: 12.01.2021, 00:49
by leus
Regarding TestCompTicket386, it seems that the database.properties template file is missing the creation and drop of ticket report tables:

Code: Select all

; SQL scripts to create database objects
sqlite3.create.scripts=create_sqlite.sql,populate_any.sql

; SQL scripts to drop database objects
sqlite3.drop.scripts=drop_sqlite.sql
It seems that all is needed to do is add the creation and drop scripts:

Code: Select all

; SQL scripts to create database objects
sqlite3.create.scripts=create_sqlite.sql,create_sqlite3_bugreport.sql,populate_any.sql

; SQL scripts to drop database objects
sqlite3.drop.scripts=drop_sqlite.sql,drop_sqlite3_bugreport.sql
And here's the execution:

Code: Select all

C:\delphi\zeoslib\packages\DelphiXE10.3\build>ZTestAll.exe -b
Config File Name: C:\delphi\zeoslib\database\test.properties
Executing C:\delphi\zeoslib\database\drop_sqlite.sql
Executing C:\delphi\zeoslib\database\drop_sqlite3_bugreport.sql
Executing C:\delphi\zeoslib\database\create_sqlite.sql
Executing C:\delphi\zeoslib\database\create_sqlite3_bugreport.sql
Executing C:\delphi\zeoslib\database\populate_any.sql

DUnit / Testing
.........................................................................................................................................................................................................................................................................................................................................................................................................................F..F.......................................................................................
Time: 0:00:03.344

FAILURES!!!
Test Results:
Run:           397
Failures:        2
Errors:          0
There were 2 failures:
  1) TestDbcBCDValues: ETestFailure
     at
      "sqlite3/sqlite: sqlite: BCD compare mismatch, for column "curr18_4", Expected: 12345678901234.5678 got: 12345678901234,6, expected: <0> but was: <1>"
  2) TestInsertFailAndCorrectCachedUpdates: ETestFailure
     at
      "sqlite3/sqlite: the constraint should forbit inserting the row, expected: <False> but was: <True>"




C:\delphi\zeoslib\packages\DelphiXE10.3\build>

Re: MySQL identifier case questions

Posted: 12.01.2021, 10:12
by miab3
I have for Zeos8 svn7215 yet:

TestCompTicket386: ETestFailure
at $009B08B5
sqlite3/sqlite: the fieldcount of table table_ticket_386 and Field_1, expected: <1> but was: <0>

Michał

Re: MySQL identifier case questions

Posted: 12.01.2021, 14:24
by leus
miab3 wrote: ↑12.01.2021, 10:12 I have for Zeos8 svn7215 yet:

TestCompTicket386: ETestFailure
at $009B08B5
sqlite3/sqlite: the fieldcount of table table_ticket_386 and Field_1, expected: <1> but was: <0>

Michał
Beginner question: I'm using git, which branch should I use for testing?
All times are UTC+01:00
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited