semi-OT : mySQL oddities

[jbv via use-livecode]

Hi list,
I'm not 100% sure yet, but I think I'm experiencing some problems with
mySQL 5.6.41 and LC server, the most annoying one being random duplicate
entries in some tables, especially the biggest ones, for instance 71 Mb
and nearly 140000 entries.
I have searched the web for more info and have found a few posts on
stackoverflow and some other blogs mentioning problems with autoincrement,
but the symptoms are slightly different...
I was wondering if anyone had experienced similar issues ?

Thanks in advance.
Best,
jbv

[Bob Sneidar via use-livecode]

I use mySQL 5.6 running on a Windows server. I do not have this issue. The only way LC will add a record is with the insert command. I suspect there is someplace in your code where you expect to update, and it's inserting instead.

I wrap my database commands in a series of commands and functions, all of which put the current user name (I have a login system in place) and the datetime the update was made. Something like that might help you determine when the updates were made.

Bob S

Post by jbv via use-livecode
Hi list,
I'm not 100% sure yet, but I think I'm experiencing some problems with
mySQL 5.6.41 and LC server, the most annoying one being random duplicate
entries in some tables, especially the biggest ones, for instance 71 Mb
and nearly 140000 entries.
I have searched the web for more info and have found a few posts on
stackoverflow and some other blogs mentioning problems with autoincrement,
but the symptoms are slightly different...
I was wondering if anyone had experienced similar issues ?
Thanks in advance.
Best,
jbv

[Bob Sneidar via use-livecode]

Oh BTW I use autoincrement on all my tables for the primary key.

Bob S

Post by Bob Sneidar via use-livecode
I use mySQL 5.6 running on a Windows server. I do not have this issue. The only way LC will add a record is with the insert command. I suspect there is someplace in your code where you expect to update, and it's inserting instead.
I wrap my database commands in a series of commands and functions, all of which put the current user name (I have a login system in place) and the datetime the update was made. Something like that might help you determine when the updates were made.
Bob S

Post by jbv via use-livecode
Hi list,
I'm not 100% sure yet, but I think I'm experiencing some problems with
mySQL 5.6.41 and LC server, the most annoying one being random duplicate
entries in some tables, especially the biggest ones, for instance 71 Mb
and nearly 140000 entries.
I have searched the web for more info and have found a few posts on
stackoverflow and some other blogs mentioning problems with autoincrement,
but the symptoms are slightly different...
I was wondering if anyone had experienced similar issues ?
Thanks in advance.
Best,
jbv

[jbv via use-livecode]

Hi list,
This is a quick follow-up to my post from a couple of months ago.
After weeks of tweeking and testing, I am now 99.99% sure there's
an issue with auto-increment in the latest versions of mySQL, at
least with large tables with several indexes and complex scripts
that insert and update several rows in one pass.

Long story short, I finally removed auto-increment on a primary
key (integer) and added the following lines to my script (pseudo code) :
select MAX(id) from myTable limit 1
put it into tMaxID
add 1 to tMaxID

I've been watching & checking the table content for several weeks,
and random duplicates / corruption of rows have totally disappeared.
I hope I found the solution, but will keep checking.
I also thing I will bypass auto-increment in future projects.

All the best,
jbv

Post by jbv via use-livecode
Hi list,
I'm not 100% sure yet, but I think I'm experiencing some problems with
mySQL 5.6.41 and LC server, the most annoying one being random duplicate
entries in some tables, especially the biggest ones, for instance 71 Mb
and nearly 140000 entries. I have searched the web for more info and have
found a few posts on stackoverflow and some other blogs mentioning
problems with autoincrement, but the symptoms are slightly different... I
was wondering if anyone had experienced similar issues ?
Thanks in advance.
Best,
jbv
_______________________________________________
http://lists.runrev.com/mailman/listinfo/use-livecode

[Bob Sneidar via use-livecode]

That is very curious. I am running mySQL 5.6 not sure the minor version. I use autoinc in all my tables without difficulty, but I also use sqlYoga, so I do not craft the actual queries.

Bob S

Post by jbv via use-livecode
I've been watching & checking the table content for several weeks,
and random duplicates / corruption of rows have totally disappeared.
I hope I found the solution, but will keep checking.
I also thing I will bypass auto-increment in future projects.
All the best,
jbv