developing a transaction safe interface with MySQL as database
Hei All, I am new here (my name is Gábor Lénárt and I am from Hungary), and I am also quite new to develop more complex applications using RDBMS, MySQL in our case. I hope it's the right place to ask general questions too. I have experience to create simple applications without transaction handling or so, and also with administrating MySQL server at least. Now I have to develop a SOAP interface which is about querying data from MySQL database, and also to modify. The interface may be used by multiple clients in parallel, and it's also must be a secure solution to do that, so of course using transactions are must for here. However I am not so well experienced in this area. What can I do to be sure, that starting transaction guarantees that I see a consistent snapshot of my database (even if other requests modify it meanwhile) with mixed SELECT/UPDATE/INSERT with also be able to read back my changes done within my transaction. MySQL documentation is a bit mystical for me, since it refers for Oracle, which I have no experience at all. I guessed this will work: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET AUTOCOMMIT = 0; START TRANSACTION WITH CONSISTENT SNAPSHOT; [... my queries with lots of SELECT/INSERT/UPDATE statements ...] COMMIT; (or ROLLBACK if there was some error during the processing) Is it a good solution for me then? I am not so sure about isolation levels and so. The other problem with this is error handling,. What kind of error codes can I expect to signal there is some kind of conflict between parallel transactions? Since the SQL error handler must be prepared to handle other kind of SQL problems (which shouldn't occur, but who can be sure there is no bug in the application!), and the error codes caused by the conflicting transactions, dead locks, and so on. Or is it enough to check the error code after issuing COMMIT, so all errors there are about only these, and I can be sure that errors at other SQL statements are caused by bugs in my application? The second problem of mine maybe even more complex (well, for me at least). If there is a SOAP request to modify the database, basically it describes multiple modifications in the database through multiple steps. But I also have constraints and rules to check, but it cannot be done in SQL level. It's because these checks are quite complex ones, not possible to explain as simple rules like 'UNIQUE' fields and so on, and also, the constraints and other relation rules of my interface can be broken during the transaction steps, just they must be checked at the end, before the COMMIT. I've already implemented this, however I have a worrisome thought. Since my transaction sees a consistent snapshot of the DB which was the state at the time of issuing START TRANSACTION I will not see if other threads of my interface got another SOAP requests resulting breaking some constraints I want to check within the transaction. Ok maybe my English knowledge is a bit terrible to explain myself right, also maybe my problems are quiter beginner types, but I would be grateful if someone can help me with the answers. Thanks a lot in advance, -- Gábor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
auto_increment problem with mysql 3.23.32
Hi,
It's me again ;-) Now I have got a very serious problem. I've just downloaded
3.23.32 and compile then run it without any major trouble. BUT my headache
is about this:
mysql insert into cikk values (NULL,'Beta','','','','','N','','',NULL,0,0,0,0);
mysql select * from cikk where cim='Beta';
++--++++-+++---++--+--+-+---+
| id | cim | cimurl | cimkep | szoveg | hszoveg | fontos | forras | forrasurl |
|feldatum | felvitte | moddatum | modositotta | tipus |
++--++++-+++---++--+--+-+---+
| -1 | Beta |||| | N || |
|20010214120417 |0 |0 | 0 | 0 |
++--++++-+++---++--+--+-+---+
I've tried use 0 or NULL as value for 'id' when doing the insert query.
The structure of table 'cikk' is:
CREATE TABLE cikk (
id bigint(20) NOT NULL auto_increment,
cim varchar(200),
cimurl varchar(100),
cimkep mediumblob,
szoveg text,
hszoveg text,
fontos enum('Y','N') DEFAULT 'Y' NOT NULL,
forras varchar(200),
forrasurl varchar(100),
feldatum timestamp(14),
felvitte bigint(20),
moddatum bigint(20),
modositotta bigint(20),
tipus bigint(20) DEFAULT '0' NOT NULL,
KEY id_index (id),
KEY tipus_index (tipus),
KEY feldatum_index (feldatum),
KEY fontos_index (fontos)
);
Any values inserted the table become id -1 ... ;-(
However if I try declare id as unsigned I got an awfull big number as 'id',
and I can't even delete it with 'delete from cikk where id=.;', mysql
monitor simply returns that 0 row is affected, and the data is still exists
in the table.
It's very serious for me since this is a commercial site.
I had to upgrade to new mysql version, since old one became unusable one
day (a very long query was blocking mysql forever with all of idle CPU
time used. if I tried to strace that mysqld process with strace -p ...,
it created a null length file and the process exists. However at home
with mysql 3.23.31 it was fine, and with 3.23.32 it's okey too, but now
I have got this auto_increment problem).
Note, that I USED id -1 in this table for a special purpose, but now I
deleted ALL ids smaller than 1 then I tried to insert but it does not
work. It didn't work if I tried to dump the database (only with ids0)
drop table, then recreate table and data from the saved dump).
By the way, the query which had got problems with the older mysql version
on our server was:
SELECT i.id as id,i.fullservice as fullservice,i.www as www,i.ceg as
ceg,v.varos as varos,i.irszam as irszam,i.tel as tel,i.fax as fax,i.email as
email,i.tevleir as tevleir,t0.terulet as t0,t1.terulet as t1,t2.terulet as
t2,t3.terulet as t3,t4.terulet as t4,t5.terulet as t5 FROM ceginfo as
i,cegvaros as v,cegterulet as t0,cegterulet as t1,cegterulet as
t2,cegterulet as t3,cegterulet as t4,cegterulet as t5 WHERE i.varos=v.id AND
i.terulet0=t0.id AND i.terulet1=t1.id AND i.terulet2=t2.id AND
i.terulet3=t3.id AND i.terulet4=t4.id and i.terulet5=t5.id AND ervenyes='Y'
AND i.ceg LIKE '%balogh%' ORDER BY ceg LIMIT 0,10;
But now it's not problem. My only question is how can I avoid that auto_increment
problem.
PLEASE, help me.
And please cc the mail for me too, I'm not on the list.
Thanx a lot in advance, Gabor.
--
--[ Gbor Lnrt ]---[ Vivendi Telecom Hungary ]-[ [EMAIL PROTECTED] ]--
U have 8 bit comp or chip of them and it's unused or to be sold? Call me!
---[ +36 30 2270823 ]-- LGB -[ Linux/UNIX/8bit 4ever ]-
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment problem with mysql 3.23.32
On Wed, Feb 14, 2001 at 02:38:11PM +0200, Sinisa Milivojevic wrote: Hi! I have just tested your case on my Linux and it worked just fine. What OS is that and are you using our binaries. If not, please do. OK, I will (but it takes some time to download). Till that I have got a more accurate bug report on this topic. I created a test table containing only an 'auto_increment' field named 'id'. If I insert values with '0' as id in the insert query, everything works fine (incremented values). AND NOW THE TRICK: I inserted -1 to the table. And after THIS, all of my insert queries even with id 0 in the query result in -1 ! But this is not the end. If I delete all ids smaller than 1, then I try to insert some data with id 0, I've got -1 AGAIN! So it seems that the table store the last insert id value, and if it's -1, it stucks. Older MySQL versions were fine. It's hard because I've got many LARGE tables with complex applications that use the technique to have auto_increment value, BUT mark some fields with negative numbers as IDs for special purposes not listed normally (where id0). The really annoying bug, that even after I delete all of my negative id'ed records, the next insert will result in -1 too ;-( So I have to delete all negative ids, dump the table, then recreate and reload just for kick last insert id value for a table out from that bad state? With my databases it will be very hard work for the hw, not counting other problems. What's your opponion on this? Thanx a lot. - Gabor -- --[ Gbor Lnrt ]---[ Vivendi Telecom Hungary ]-[ [EMAIL PROTECTED] ]-- U have 8 bit comp or chip of them and it's unused or to be sold? Call me! ---[ +36 30 2270823 ]-- LGB -[ Linux/UNIX/8bit 4ever ]- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto_increment problem with mysql 3.23.32
On Wed, Feb 14, 2001 at 04:21:31PM +0200, Sinisa Milivojevic wrote: Hi! The above is actually documented behaviour. I've just realized that it's true (checked). But it was supported by older mysql versions and now I'm getting stuck with the old tables (MyISAM). Entering negative values and negative values in general, are actually not suported in auto_increment columns. OK, it's right and clean for me now ;-) But what can I do if I DONE this before? For example one of the development group members at our firm try this. Of course I can remove all of the records inserted with negative ids but even after this the table remains in a state where I can't work correctly. Is there a chance to kick out the insert id mysql internal variable for this table ? It's not an easy task to recreate the table with dumping and restoring it :( BTW, sorry for my tons of mails :( - Gabor -- --[ Gbor Lnrt ]---[ Vivendi Telecom Hungary ]-[ [EMAIL PROTECTED] ]-- U have 8 bit comp or chip of them and it's unused or to be sold? Call me! ---[ +36 30 2270823 ]-- LGB -[ Linux/UNIX/8bit 4ever ]- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: simple SQL question
On Thu, Jan 25, 2001 at 12:52:11PM -0600, Gerald L. Clark wrote: I would suggest not having 2 cities in your firm record, and making fname,city your key. select * from firms order by fname,city would give you. A+B company Dallas A+B company London New systems Ltd New York New systems Ltd Paris Nice, but it has got some problems. If I correctly understand you, you suggest me to double records which have got multiple city entries. The problem is that firms table has got many fields even binary ones to hold picture data so it would be expensive to double them. And my other problem: indexing character types are more slower than just bigint values. By the way is there any ANSI SQL solution for it? I mean only in mySQL can't be implemented simply or this is a general SQL problem? PS: Maybe I should stop CC'ing this thread to the list, shouldn't I. - Gabor Lenart - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
