MariaDB query Error

[Aurelian_N]

Hello guys,

So i’m trying to Transfer a MariaDB Database into a SQLiteDatabase

So far all ok except with the latest MariaDB Databases it seems that data that comes from the server is messed up and columns don’t respect the order or data is lost on the process .

Anyone encountered same issue ?

I first scan the MariaDB and scan all the tables and get their type .
Then i create the structure for each table in a local SQLite DB
Then scan each table again and get the data and put it in SQLite DB

Remember it does work on older versions of MariaDB

so while scanning the Columns with

For i As Integer = 0 To row.ColumnCount -1

I reach to a column which is Date Format I get the Column format with

Select Case row.ColumnAt(i).Type

and in my case is 9 so i treat it as Date

Now , looking on the Table structure on SQLiteDB and on the MariaDB server they are identical on names , properties and Order so that is not an issue.

Once the code breaks because of a Nil Value in the data i discover that in the Row.ColumnAt(i) in my case i have a Nil Value and not a Date value, but if i look on the Database for that specific row i get the needed date as “1992-03-29 00:00:00”

Now, Question to @XOJO, does the RecordSet follows the Structure record or not ? As apparently here it does not, but again, on older versions of MariaDB it does .

So i guess along with all the other issues i assume that the Driver needs a full update as i get messed up data lately and loosing customer data is not an option here.

I forgot to mention

XOJO 2023R1.1
MacOS Ventura 13.4.1(c)
Debian 11
MariaDB 10.8.8

Thanks.

[Emile_Schwarz]

Just in case, MariaDB current version is 11.0.2 (june 2023).

[Aurelian_N]

Hello Emile, That’s not the point .

The issue seems to be somewhere else

It seems that while i = 9 the code goes to Case 8, 10 which should not happen.

And looking into the Table Structure that field is a Date Value so worst case should be 8, or 10, i have no such column as Time structure based on the code.

Anyone got issues like that ?

[Rick_Araujo]

Have you tried dump MariaDB, fix the SQL for SQLite compatibility, and run it for SQLite?

Like:

mysqldump -u userName -p databaseName > database.sql

Then edit database.sql as you wish, fix differences and then create a database.sqlite and run the sql as:

sqlite3 ".open --new database.sqlite" ".read database.sql" .quit

[Ian_Kennedy]

That 9 is in case for a text block, not a datetime block? Which is not what your description claims.

@Aurelian_N

and in my case is 9 so i treat it as Date

[Aurelian_N]

That’s my point as it is in Text so i supposed to treat it as Text and not as Time format , but still, based on the debugging it comes as 9 which is Time Format while the actual data is “1992-03-29” so it should be treated mostly as Date and not as Time and 100% of the data in that column is same as it is the Birth Date and there are no nils

[Aurelian_N]

Now, i set the Type as string out of curiosity and i get this :

So as you see cData is “1992-03-29 00:00:00” but on Type is 9 which comes as Time Type and on DateTimeValue if i add it this way it crashes with incorrect type.

That is the funny part

[AlbertoD]

What time zone are you?
Does 1992-03-29 00:00:00 exists or is the day the daylight saving time kicks in?

There is a problem that Xojo can’t handle non-existing dates. In the USA the DST changes at 2am so there is no problem but in other countries the DST changes at 0hrs, so if in your Time Zone the clock changes at 0hrs 1992-03-29 then that date is not valid and can be the problem that you are seeing.

[Greg_O]

Um… isn’t the “time” type just the time component? Looking at their docs, time is just hh:MM:SS.ssssss which does not seem to indicate that your data type assumption is correct.

Specifically:

In MariaDB 10.1.2 a new temporal format was introduced from MySQL 5.6 that alters how the TIME , DATETIME and TIMESTAMP columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the mysql56_temporal_format system variable.

That would explain why a change in version caused a behavior change in Xojo as this is now the default.

[Aurelian_N]

Exactly that is what i thought , while in my MariaDB database the date is just “1992-03-29” nothing else, as the app was pre DateTime part and data was stored as Date and not as TimeStamp. the curios part is that it sees it as Time, even if i set the case If 9 then set it as Text and still it goes to 8, 10

[Aurelian_N]

Hi Alberto, as stated in the other post my date in DB is “1992-03-29” nothing else, so the 00:00:00 part is added by XOJO it seems to complete the DateTime format .

[Greg_O]

@Aurelian_N

pre DateTime part

Do you mean Xojo DateTime or the MariaDB DateTime column type?

[Aurelian_N]

I’m not sure i get it .

That is the Date , and that is the Column Type as DATE

[Rick_Araujo]

I had the same kind of pseudo-problem with mysql. I had queries that in the past returned yyyy-mm-dd and from some uncertain point they returned yyyy-mm-dd 00:00:00, but my routines were ready and discarding the time. I had no issues.

[AlbertoD]

Read Greg’s post above, I guess that is the problem.

You can use datetime from string for example. Or remove the time if that is better.

[Aurelian_N]

Does not work, i get same error for this Date. If you see in the snapshot i have

cDate = DateTime.FromString(cData) with same error . and based on my tests it seems that XOJO is adding that in order to comply with the format , which in some cases is Altering the data and it is not ok .

and now it comes the weird part , i put some code to strip the 00 and i get a clean date which of course it fails.

So i put a try/catch and i get this as error :

Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD

Last time i checked 1992-03-29 existed in the calendar so is this a funy Joke from XOJO ? or a bug ?

[Rick_Araujo]

I thing you forgot to remove one space.

[AlbertoD]

So I question again, what time zone are you in?
If the Daylight hour change is at that time?

Try modifying the time part to 01:00:00

[Aurelian_N]

The Trim supposed to do that , normally.

[Aurelian_N]

Hi Alberto,

Again, this is not related, as said XOJO adds that and they should not, as you see in my below snapshot all the data that is added BY XOJO from MariaDB which does not have those 00:00:00 and no issues there. the issue seems to be that specific date , i delete it from the database and re-added it manually and same issue .

Other dates are working and it seems that this date does not