MySQL problem | SOLVED

Just starting out? Need help? Post your questions and find answers here.
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MySQL problem

Post by Marc56us »

Hi,

This is a small snipet, as thanos' test, ODBC works for me too and not the direct connection.
(Change 'Use' line and 'OpenDatabase' line to test the two version)

Code: Select all

UseMySQLDatabase()
; UseODBCDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
    Protected Result
    
    Result = DatabaseUpdate(Database, Query$)
    Debug "Query : " + Query$ 
    Debug "Rows  : " + AffectedDatabaseRows(Database)
    
    If Result = 0
        Debug "ERROR : >>> " + DatabaseError() + " <<<"
        End
    EndIf
    Debug ""
    
    ProcedureReturn Result
EndProcedure

; Direct: 'Commands out of sync; you can't run this command now'
hDatabase = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=PB_Test", "test", "test")

; ODBC (MariaDB ODBC 3.1)
; https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.11/
; hDatabase = OpenDatabase(#PB_Any, "PB_test", "test", "test")

If (hDatabase)
    Debug "Connected to MySQL"+ #CRLF$

    CheckDatabaseUpdate(hDatabase, "SET autocommit = OFF;")
    CheckDatabaseUpdate(hDatabase, "BEGIN;")

    ;     ForEach Values2Insert()
    ;         CheckDatabaseUpdate(hDatabase, Values2Insert())
    ;         i+1
    ;     Next
    
    CheckDatabaseUpdate(hDatabase, "COMMIT;")
    CloseDatabase(hDatabase)
EndIf
ODBC

Code: Select all

Connected to MySQL

Query : SET autocommit = OFF;
Rows  : 0

Query : BEGIN;
Rows  : 0

Query : COMMIT;
Rows  : 0
Direct

Code: Select all

Connected to MySQL

Query : SET autocommit = OFF;
Rows  : 0

Query : BEGIN;
Rows  : 0
ERROR : >>> Commands out of sync; you can't run this command now <<<
ODBC driver installed and connected to the database with default options.

I tried to use AffectedDatabaseRows(Database) to try to clear the buffer, but it has no effect.
According to several opinions it is the DLL that has a problem.
I'll keep on looking (even if I don't use MySQL anymore, but PostgreSQL)

8)
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post by thanos »

I also checked with different system configurations (XP Home, Pb 5.71, several version of libmariadb and mysqllib) with the same bad results
Regards,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post by thanos »

Is it necessary to open a thread at bugs section?
Regards,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MySQL problem

Post by Marc56us »

thanos wrote: Mon Apr 19, 2021 10:55 am Is it necessary to open a thread at bugs section?
Regards,
According to what we can read on the Internet, this problem comes from the DLL, so PB team can't do anything.

Use ODBC or if you have not yet started developing the application, change the DBMS (i.e PostfreSQL is a bit more difficult to configure, but much more consistent and respects the SQL standards and now very well documented)
:wink:
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post by thanos »

Thanks a lot Marc56us!
I am always using PostgreSQL (when I left Firebird) as my DBMS which I have some experience with them. But in this particular project I have to use MySQL.
According to what we can read on the Internet, this problem comes from the DLL, so PB team can't do anything.
It sounds strange. With using the same dll I wrote a small program with Harbour and the job was done.
So, we have to wait for a new version of MariaDB and hope to fix the problem?
Regards,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: MySQL problem

Post by wayne-c »

I also have this problem, always get these "Commands out of sync; you can't run this command now" errors. Mostly when trying to lock tables, but also with other commands. Via ODBC the exact same PureBasic code and same MariaDB database works without any errors.

But with the UseMySQLDatabase("libmariadb.dll") not.

It's very frustrating to have to install an ODBC driver to access a local MariaDB database :cry:
As you walk on by, Will you call my name? Or will you walk away?
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: MySQL problem

Post by infratec »

You can try the former solution to access MySQL:

https://www.purebasic.fr/english/viewto ... 12&t=56390

If the fault is still there, than it is a dll problem, else a PB problem.
User avatar
the.weavster
Addict
Addict
Posts: 1537
Joined: Thu Jul 03, 2003 6:53 pm
Location: England

Re: MySQL problem

Post by the.weavster »

thanos wrote: Sat Apr 10, 2021 5:37 pm The INSERT statements isn't something special.
Here are the first five rows:

Code: Select all

INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (1, '2020-04-30', '06:10:15', '20200430_061015_025579062-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1100-000-021-3', '', '', 'ΜΕ|ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227015, '', '', 1, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300610150255790621303', '2020-04-30, 06:10:15.25579062');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (2, '2020-04-30', '06:21:19', '20200430_062119_026243812-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227679, '', '', 2, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300621190262438123782', '2020-04-30, 06:21:19.26243812');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (3, '2020-04-30', '06:25:36', '20200430_062536_026500796-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227936, '', '', 3, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300625360265007963007', '2020-04-30, 06:25:36.26500796');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (4, '2020-04-30', '06:26:56', '20200430_062656_026580312-00001-1', 1.0, 1.9, 0.0, 1.9, 0.0, 0.0, '00001-1', '1300-000-105-9', '', '', 'ΓΛΥΚΟΣ|', '', '', '00008-1', '01', 1588228016, '', '', 4, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300626560265803128479', '2020-04-30, 06:26:56.26580312');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (5, '2020-04-30', '06:31:48', '20200430_063148_026872718-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-168-3', '', '', '', '', '', '00008-1', '01', 1588228308, '', '', 5, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300631480268727183471', '2020-04-30, 06:31:48.26872718');
I saw this on stackoverflow and would be interested to know if this makes a difference to the problem you're encountering:

"Commands out of sync; you can't run this command now"

"This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application."
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: MySQL problem

Post by wayne-c »

infratec wrote: Tue Apr 20, 2021 4:22 pm You can try the former solution to access MySQL:

https://www.purebasic.fr/english/viewto ... 12&t=56390

If the fault is still there, than it is a dll problem, else a PB problem.
The first short test was running fine, without any errors - does this mean there's a bug in PureBasic's MySQL Library?
As you walk on by, Will you call my name? Or will you walk away?
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post by thanos »

the.weavster wrote: Tue Apr 20, 2021 5:46 pm
thanos wrote: Sat Apr 10, 2021 5:37 pm The INSERT statements isn't something special.
Here are the first five rows:

Code: Select all

INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (1, '2020-04-30', '06:10:15', '20200430_061015_025579062-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1100-000-021-3', '', '', 'ΜΕ|ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227015, '', '', 1, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300610150255790621303', '2020-04-30, 06:10:15.25579062');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (2, '2020-04-30', '06:21:19', '20200430_062119_026243812-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227679, '', '', 2, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300621190262438123782', '2020-04-30, 06:21:19.26243812');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (3, '2020-04-30', '06:25:36', '20200430_062536_026500796-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227936, '', '', 3, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300625360265007963007', '2020-04-30, 06:25:36.26500796');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (4, '2020-04-30', '06:26:56', '20200430_062656_026580312-00001-1', 1.0, 1.9, 0.0, 1.9, 0.0, 0.0, '00001-1', '1300-000-105-9', '', '', 'ΓΛΥΚΟΣ|', '', '', '00008-1', '01', 1588228016, '', '', 4, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300626560265803128479', '2020-04-30, 06:26:56.26580312');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (5, '2020-04-30', '06:31:48', '20200430_063148_026872718-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-168-3', '', '', '', '', '', '00008-1', '01', 1588228308, '', '', 5, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300631480268727183471', '2020-04-30, 06:31:48.26872718');
I saw this on stackoverflow and would be interested to know if this makes a difference to the problem you're encountering:

"Commands out of sync; you can't run this command now"

"This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application."
I strip the semicolon (;) from every statement and the records inserted correctly.
Thanks a lot!
I could not imagine such a thing!
I started the statements with:

Code: Select all

SET autocommit=0
BEGIN
...
...
...
COMMIT
Does MySQL understands the $LF as the end of statement instead of semicolon?
Another quirk of MySQL?
Regards,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: MySQL problem

Post by wayne-c »

the.weavster wrote: Tue Apr 20, 2021 5:46 pm
I saw this on stackoverflow and would be interested to know if this makes a difference to the problem you're encountering:

"Commands out of sync; you can't run this command now"

"This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application."
Thank you for this hint. I am aware of this, and no, I do not have any semicolons at the end of my statements.
As you walk on by, Will you call my name? Or will you walk away?
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MySQL problem

Post by Marc56us »

Just tested, works, but this is the start transaction still not work. (whatever keyword BEGIN, START TRANSACTION...)
Create table and insert works fine.

Test: MariaDB 10.5.9 x64 - Windows 10 20H2 - Pb 5.73 LTS x64

Code: Select all

UseMySQLDatabase() 

#DB_Name = "test"
#DB_User = "test"
#DB_Pass = "test"

If OpenDatabase(0, "host=localhost port=3306 dbname=" + #Db_Name, #DB_User, #DB_Pass)
    Debug "Connected to MySQL"
Else
    Debug "Connection failed: " + DatabaseError()
EndIf

Procedure Query(Query$)
    Debug #CRLF$ + "Query : " + Query$
    If DatabaseUpdate(0, Query$)
        Debug "Rows  : " + AffectedDatabaseRows(0)
    Else
        Debug "Rows  : " + AffectedDatabaseRows(0)
        Debug "Error : " + DatabaseError()
        FinishDatabaseQuery(0)
    EndIf
EndProcedure

Query("CREATE TABLE IF NOT EXISTS PB_Test (Col1 TEXT)")

Query("SET autocommit = 0")

Query("BEGIN") ; Query("START TRANSACTION")

Query("INSERT INTO PB_test (col1) VALUES ('AAA')") 
Query("INSERT INTO PB_test (col1) VALUES ('BBB')") 
Query("INSERT INTO PB_test (col1) VALUES ('CCC')") 

Query("COMMIT")

FinishDatabaseQuery(0)

If IsDatabase(0) : Debug #CRLF$ + "Close Database": CloseDatabase(0) : EndIf

End

Code: Select all

Connected to MySQL

Query : CREATE TABLE IF NOT EXISTS PB_Test (Col1 TEXT)
Rows  : 0

Query : SET autocommit = 0
Rows  : 0

Query : BEGIN
Rows  : 0
Error : Commands out of sync; you can't run this command now

Query : INSERT INTO PB_test (col1) VALUES ('AAA')
Rows  : 1

Query : INSERT INTO PB_test (col1) VALUES ('BBB')
Rows  : 1

Query : INSERT INTO PB_test (col1) VALUES ('CCC')
Rows  : 1

Query : COMMIT
Rows  : 0

Close Database
:|
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: MySQL problem

Post by captain_skank »

I've tried this in the past and never got transactions to work at all.

Are you using ODBC and windows ?? does turning on 'execution of 'allow mulitple statements' in ODBC Data Sources help ?
wayne-c
Enthusiast
Enthusiast
Posts: 335
Joined: Tue Jun 08, 2004 10:29 am
Location: Zurich, Switzerland

Re: MySQL problem

Post by wayne-c »

I think we should not mix up things here. There are apparently two problems discussed in this thread:
  • UseMySQLDatabase() seems not to work correctly in general (via ODBC or direct access to the dll works, see above)
and
  • Some SQL's executed do not work (Transactions)
Maybe the two are related, maybe not.

I think we should first ensure UseMySQLDatabase() gets the bug fix, then see for the other problems.
As you walk on by, Will you call my name? Or will you walk away?
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post by thanos »

@Marc56us
It worked for me also with a transaction (started with BEGIN).
I inserted 165.000 rows.
I had some errors with some rows but it worked.
Regards,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
Post Reply