View Issue Details

IDProjectCategoryView StatusLast Update
0014519FPCDatabasepublic2015-07-22 12:07
Reporterdidi Assigned ToJoost van der Sluis  
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Fixed in Version3.0.0 
Summary0014519: MySQL command "Check Table" generates error "Commands out of sync" Error
DescriptionI use the FCL-DB (SQLDB) package. It is the one that installs by default with Lazarus/FPC. I use TMySQL50Connection, TSQLTransaction and TSQLQuery to commit quries to the MySQL Server. Everything wotks fine EXCEPT the following:

when I try to issue a "Check Table" command two consecuitive times, using the TSQLQuery's ExecSQL method, then I get an error the SECOND time.
The error says: Commands out of sync; you can't run this command now

so the following

SQLQuery1.SQL.Text:= 'Check Table T1 EXTENDED';
SQLQuery1.ExecSQL;
SQLQuery1.SQL.Text:= 'Check Table T2 EXTENDED';
SQLQuery1.ExecSQL;

Generates the error on the second call to ExecSQL;








OS: WinXP
FPC: 2.2.2
Laz: 0.9.26
MySQL: 5.4
Additional InformationFor some weird reason however, if i put a "select" infront of it all, and it works fine !!

SQLQuery1.SQL.Text:= 'select * from T1';
SQLQuery1.ExecSQL;
SQLQuery1.SQL.Text:= 'Check Table T1 EXTENDED';
SQLQuery1.ExecSQL;
SQLQuery1.SQL.Text:= 'Check Table T2 EXTENDED';
SQLQuery1.ExecSQL;

works withput error
TagsDatabase, dataset, TSQLQuery
Fixed in Revision 20917
FPCOldBugId0
FPCTarget
Attached Files

Relationships

related to 0016236 closedJoost van der Sluis TMySQL50Connection does not support CALL statements, which return result sets 

Activities

Paul Ishenin

2009-09-06 10:57

developer   ~0030475

How is lazarus involved here if you are working with fpc component?

didi

2009-09-06 21:18

reporter   ~0030494

Sorry if I posted under wrong category. I can see that you have changed the category to FPC. Thank you. Please tell me how you did it ? So I know next time :-)

Felipe Monteiro de Carvalho

2010-07-21 09:55

developer   ~0039541

Please provide a compilable example with database

LacaK

2010-07-22 08:40

developer   ~0039586

Last edited: 2010-07-22 08:44

Problem is in fact, that "CHECK TABLE" returns resultset, which is not processed (using mysql_store_result()), when ExecSQL is used.
(http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html)

There are IMHO 2 steps:

1. describe "CHECK" as stSelect and then use Open instead of ExecSQL
(add to function TConnectionName.StrToStatementType:
+ if s = 'check' then exit(stSelect);
) But MySQL provides a lot of "administration commands", which returns result sets: ANALYZE, CHECK, REPAIR, OPTIMIZE, ...)

or/and (more generic and IMHO better solution):

2. always call mysql_store_result(FMySQL) in procedure TConnectionName.Execute (not only if FNeedData=true)
This is not a problem, because in case of statements that do not return result sets mysql_store_result() returns null.
"You don't have to call mysql_store_result() or mysql_use_result() for other statements, but it does not do any harm or cause any notable performance degradation if you call mysql_store_result() in all cases." see http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html)

See also: http://bugs.freepascal.org/view.php?id=16236

LacaK

2012-04-18 08:18

developer   ~0058698

Patch maps MySQL administration statements (which return resultset) to statement type stSelect to allow use of Open and get data.
Patch also clean-ups usage of MySQLError procedure.

2012-04-18 08:19

 

mysqlconn.inc.diff (2,923 bytes)   
--- mysqlconn.inc.ori	Mon Mar 19 07:05:36 2012
+++ mysqlconn.inc	Wed Apr 18 10:04:40 2012
@@ -230,7 +230,7 @@ Resourcestring
   SErrVersionMismatch = '%s can not work with the installed MySQL client version: Expected (%s), got (%s).';
   SErrSettingParameter = 'Error setting parameter "%s"';
 
-Procedure MySQlError(R : PMySQL;Msg: String;Comp : TComponent);
+Procedure MySQLError(R : PMySQL;Msg: String;Comp : TComponent);
 
 Var
   MySQLMsg : String;
@@ -263,10 +263,13 @@ end;
 function TConnectionName.StrToStatementType(s : string) : TStatementType;
 
 begin
-  S:=Lowercase(s);
-  if s = 'show' then exit(stSelect);
-  if s = 'call' then exit(stExecProcedure);
-  result := inherited StrToStatementType(s);
+  s:=Lowercase(s);
+  if (s='analyze') or (s='check') or (s='checksum') or (s='optimize') or (s='repair') or (s='show') then
+    exit(stSelect)
+  else if s='call' then
+    exit(stExecProcedure)
+  else
+    Result := inherited StrToStatementType(s);
 end;
 
 
@@ -313,13 +316,13 @@ begin
       else
         Opt := pchar(OptStr);
       if mysql_options(HMySQL,AMysql_Option,Opt) <> 0 then
-          MySQlError(HMySQL,Format(SErrSettingParameter,[params.Names[i]]),Self);
+          MySQLError(HMySQL,Format(SErrSettingParameter,[params.Names[i]]),Self);
       end;
     end;
 
   HMySQL:=mysql_real_connect(HMySQL,PChar(H),PChar(U),Pchar(P),Nil,APort,Nil,CLIENT_MULTI_RESULTS); //CLIENT_MULTI_RESULTS is required by CALL SQL statement(executes stored procedure), that produces result sets
   If (HMySQL=Nil) then
-    MySQlError(Nil,SErrServerConnectFailed,Self);
+    MySQLError(Nil,SErrServerConnectFailed,Self);
 
   // MySQL _Server_ version 4.1 and later
   // major_version*10000 + minor_version *100 + sub_version
@@ -328,7 +331,7 @@ begin
     // Only available for mysql 5.0.7 and later...
     // if (mysql_set_character_set(HMySQL, PChar(CharSet)) <> 0) then
     if mysql_query(FMySQL,PChar('SET CHARACTER SET ''' + EscapeString(CharSet) +''''))<>0 then
-      MySQLError(HMySQL,Format(SErrExecuting,[StrPas(mysql_error(HMySQL))]),Self);
+      MySQLError(HMySQL,SErrExecuting,Self);
     end;
 end;
 
@@ -406,7 +409,7 @@ begin
     ConnectMySQL(AMySQL,pchar(H),pchar(U),pchar(P));
     try
       if mysql_query(AMySQL,pchar(query))<>0 then
-        MySQLError(AMySQL,Format(SErrExecuting,[StrPas(mysql_error(AMySQL))]),Self);
+        MySQLError(AMySQL,SErrExecuting,Self);
     finally
       mysql_close(AMySQL);
     end;
@@ -554,7 +557,7 @@ begin
       C.FStatement := stringsreplace(C.FStatement,ParamNames,ParamValues,[rfReplaceAll]);
       end;
     if mysql_query(FMySQL,Pchar(C.FStatement))<>0 then
-      MySQLError(FMYSQL,Format(SErrExecuting,[StrPas(mysql_error(FMySQL))]),Self)
+      MySQLError(FMYSQL,SErrExecuting,Self)
     else
       begin
       C.RowsAffected := mysql_affected_rows(FMYSQL);
mysqlconn.inc.diff (2,923 bytes)   

Marco van de Voort

2012-04-18 09:07

manager   ~0058699

What is the reason you changed opinion from (2) to (1)?

LacaK

2012-04-18 10:28

developer   ~0058707

Hm good question ;-)
Option (2) will only cause that error will not be raised (returned data will be silently consumed), but no data will be filled in client dataset (it is because ExecSQL does not expect, that any data will be returned).

So if we want also rows in dataset we must use Open (instead of ExecSQL).
And when we use Open sqlDB checks (parses) if provided SQL.Text contains any sql statement which can (is able) return data. In this phase is used StrToStatementType which determines kind of sql statement. If it is not StSelect or stExecProcedure error is immediately raised (SErrNoSelectStatement).
(it is relatively weak point (because we need know before executing all possible statements that can return data), but it is how is it implemented)

Marco van de Voort

2012-04-18 10:45

manager   ~0058708

Thank you for the explanation and the patch.

Committed.

Issue History

Date Modified Username Field Change
2009-09-05 21:47 didi New Issue
2009-09-05 21:47 didi Widgetset => Win32/Win64
2009-09-05 21:50 didi Tag Attached: Database
2009-09-05 21:50 didi Tag Attached: dataset
2009-09-05 21:51 didi Tag Attached: TSQLQuery
2009-09-06 10:57 Paul Ishenin Note Added: 0030475
2009-09-06 10:57 Paul Ishenin Project Packages => FPC
2009-09-06 18:50 Jonas Maebe FPCOldBugId => 0
2009-09-06 18:50 Jonas Maebe Category Database => Database Components
2009-09-06 18:50 Jonas Maebe Product Version 0.9.26 =>
2009-09-06 18:51 Jonas Maebe Status new => assigned
2009-09-06 18:51 Jonas Maebe Assigned To => Joost van der Sluis
2009-09-06 21:18 didi Note Added: 0030494
2010-07-21 09:55 Felipe Monteiro de Carvalho Note Added: 0039541
2010-07-21 09:55 Felipe Monteiro de Carvalho Status assigned => feedback
2010-07-22 08:40 LacaK Note Added: 0039586
2010-07-22 08:44 LacaK Note Edited: 0039586
2012-03-17 17:20 Marco van de Voort Relationship added related to 0016236
2012-04-18 08:18 LacaK Note Added: 0058698
2012-04-18 08:19 LacaK File Added: mysqlconn.inc.diff
2012-04-18 09:07 Marco van de Voort Note Added: 0058699
2012-04-18 10:28 LacaK Note Added: 0058707
2012-04-18 10:45 Marco van de Voort Fixed in Revision => 20917
2012-04-18 10:45 Marco van de Voort Status feedback => resolved
2012-04-18 10:45 Marco van de Voort Fixed in Version => 2.7.1
2012-04-18 10:45 Marco van de Voort Resolution open => fixed
2012-04-18 10:45 Marco van de Voort Note Added: 0058708