How to update mySQL correctly?
lazarus / 16-Databases

----------------------------------------------------------------------
Ocye · 2012-03-07 10:07

I manage user data within a mySql table. That works as expected except
for newly appended entries. These entries are not updated - in
contrast to those I converted some time ago from an ini file. I tried
to change upda]"]>Blockedde to upWhereChanged and changes are updated.
But with this setting multiple entries were created and at last the
complete table destroyed.

Please have a look on my code snippet. I'm interessted in all critics.
The code runs multithreaded on a Sheeva plug device without cisual
components.

Code: [Select]uses ...  Mysql50conn, sqldb, db,...

  MySQL := TMySQL50Connection.Create(nil);
  Trans := TSQLTransaction.Create(nil);
  with MySQL do
  begin
    HostName := '127.0.0.1';
    UserName := _user;
    Password := _passwd;
    DatabaseName := _database;
    Connected:=true;
    Transaction := Trans;
  end;

  Query := TSQLQuery.Create(nil);
  Query.DataBase := MySQL;
  Query.Upda]"]>Blockedde := upWhereChanged; //upWhereAll
  Query.SQL.Text := 'SET CHARACTER SET `utf8`';
  Query.ExecSQL;

  Query.Active:=false;
  Query.SQL.Text := 'SELECT * FROM Users WHERE Name="Test"';
  Query.Active:=true;
  Query.Edit;
  writeln('1:', Query.FieldValues['GamesPlayed']); //1
 
Query.FieldValues['GamesPlayed']:=Query.FieldValues['GamesPlayed']+1;
  writeln('2:', Query.FieldValues['GamesPlayed']); //2
  Query.Post;
  Query.ApplyUpdates;
  Trans.Commit;

  Query.Active:=false;
  Query.SQL.Text := 'SELECT * FROM Users WHERE Name="Test"';
  Query.Active:=true;
  writeln('3:', Query.FieldValues['GamesPlayed']); //2, if
Upda]"]>Blockedde is upWhereChanged, 1 in case of upWhereAll

  Query.Close;

  Query.Free;
  Trans.Free;
  MySQL.Free;
A new entry is created as follows:
Code: [Select]Info.PlayerName:=aRecvMsg.Sender;
  Query.Active:=false;
  Query.SQL.Text := 'SELECT * FROM Users WHERE
Name="'+Info.PlayerName+'"';
  Query.Active:=true;
  if Query.RecordCount=0 then
  begin
     Query.Cancel;
     Query.Append;
     Query.FieldValues['Name'] := Info.PlayerName;
     Query.Post;
     Query.ApplyUpdates; //to apply update
  end;
  Query.Active:=false;
  Query.SQL.Text := 'SELECT * FROM Users WHERE
Name="'+Info.PlayerName+'"';
  Query.Active:=true;

----------------------------------------------------------------------
ludob · 2012-03-07 16:37

upWhereAll uses all fields to create the update query. So if your
table has fields Name,GamesPlayed,x,y then it will create a query like
Code: [Select]update Users set ... where Name=a and GamesPlayed=b and
x=c and y=d.
upWhereChanged only uses only the fields changed and key fields if
defined. It looks like you have no key fields defined so this becomes 
Code: [Select]update Users set GamesPlayed =x where GamesPlayed=y.
This will potentially change a lot of records.
upWhereAll looks for an exact match on all fields which is sometimes
impossible because of data conversion (rounding errors for example) . 
I don't what the files are in Users but you only set 'Name' when
creating a new record. This could potentially create unexpected
(null?) values in the record which could make subsequent WHERE clauses
go wrong.
I suggest you define a primary key in your table and use
upWhereKeyOnly.

----------------------------------------------------------------------
Ocye · 2012-03-07 18:27

Quote from: ludob on March 07, 2012, 05:37:51 pm...I don't what the
files are in Users but you only set 'Name' when creating a new record.
This could potentially create unexpected (null?) values in the record
which could make subsequent WHERE clauses go wrong....I change other
values later, similar to gamesplayed and, of course, this fails as
well. Sounds convincing to me that null fields are responsible for the
problem. I thought it would be filled with any value...
Thanks a lot for your help!

----------------------------------------------------------------------
Ocye · 2012-03-12 22:24

I still have problems with sql updates. After some traffic I get the
error
QuoteAn error occured while applying the updates in a record: : No
update query specified and failed to generate one. (No fields for
inclusion in where statement found)and the query loses connection to
the sql server ("Lost connection to MySQL server during query").

I'm updating somewhat different from the proposed way in SqlDBHowto. I
do Code: [Select]Query.Edit;
Query.FieldValues['...']:=...;Query.Post;Query.ApplyUpdates; instead
of Code: [Select]UPDATE TBLNAMES SET NAME='Edited name' WHERE ID=1;
I'm a little bit confused since my code works well - just for some
hours. Any help is appreciated.

----------------------------------------------------------------------
ludob · 2012-03-13 07:56

Quote("Lost connection to MySQL server during query")There is a wait-
timeout on MySQL that closes connections if there is no activity for a
certain period. You can change the time-out or configure the
connection to reconnect automatically.
Use the following connection parameters to change these settings:
Code: [Select]  FConnection.Params.add('mysql_init_command=set global
wait_timeout = 2');
  FConnection.Params.add('mysql_opt_reconnect=1');
Info on the different param options can be found here
http://dev.mysql.com/doc/refman/5.5/en/mysql-options.html

----------------------------------------------------------------------
Ocye · 2012-03-13 08:15

Quote from: ludob on March 13, 2012, 08:56:15 amThere is a wait-
timeout on MySQL that closes connections if there is no activity for a
certain period.That's not the reason but the consequence. All of my
querys fail after the mentioned error (the db itself is running and
myphpadmin can connect).

----------------------------------------------------------------------
ludob · 2012-03-13 09:15

QuoteAll of my querys fail after the mentioned error If all queries
fail then probably your db connection is gone. Read this
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Quote(the db itself is running and myphpadmin can connect).Did I say
the server died? The connection to the server is gone. If you restart
your app, it'll connect again and function correctly for a while.
myphpadmin creates also a new connection.

Don't get me wrong. Perhaps there is also something else wrong in your
program but the "Lost connection to MySQL server during query" message
is a strong indication that you lost your connection to the MySQL
server. This message comes from the libmysql client and not from sqldb
code. And since your code is running fine for a few hours, I would
eliminate first any such external errors such as disconnections before
suspecting your code.  Your small edit, post, applyupdates snippet
doesn't show any problems.

----------------------------------------------------------------------
Ocye · 2012-03-13 10:07

I assume an error concerning my query since the connection is lost
directly after
QuoteAn error occured while applying the updates in a record: : No
update query specified and failed to generate one. (No fields for
inclusion in where statement found)

Your reference says
Quote* You can also get these errors if you send a query to the server
that is incorrect or too large.
* You can also encounter this error with applications that fork child
processes, all of which try to use the same connection to the MySQL
server. This can be avoided by using a separate connection for each
child process.

What happens if two threads are executing queries simultaneous (which
might be a difference to my tests)?

PS: TMySQL50Connection, TSQLTransaction, and TSQLQuery are global
variables accessed by some threads. Does each thread needs it's own
query?

----------------------------------------------------------------------
ludob · 2012-03-13 14:00

QuoteWhat happens if two threads are executing queries simultaneous
(which might be a difference to my tests)?

PS: TMySQL50Connection, TSQLTransaction, and TSQLQuery are global
variables accessed by some threads. Does each thread needs it's own
query?
A normal libmysql isn't thread safe. You need a special thread safe
build for that. In any case, sharing a  TMySQL50Connection and
TSQLTransaction among threads is always wrong. What is a transaction
in a multi-threaded environment? You don't know what happens between
the start transaction and a commit/rollback. 
Make sure every thread has his own connection and transaction. You
don't have to create a connection per thread but use a connection
pool, as long as no connection is used by more than one thread at a
time.