semi-OT : mySQL oddities

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

semi-OT : mySQL oddities

Pi Digital 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


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: semi-OT : mySQL oddities

Pi Digital 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


> On Oct 9, 2018, at 14:13 , jbv via use-livecode <[hidden email]> wrote:
>
> 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


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: semi-OT : mySQL oddities

Pi Digital via use-livecode
Oh BTW I use autoincrement on all my tables for the primary key.

Bob S


> On Oct 9, 2018, at 14:50 , Bob Sneidar <[hidden email]> wrote:
>
> 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
>
>
>> On Oct 9, 2018, at 14:13 , jbv via use-livecode <[hidden email]> wrote:
>>
>> 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
>


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: semi-OT : mySQL oddities

Pi Digital via use-livecode
In reply to this post by Pi Digital 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

On Tue, October 9, 2018 10:13 pm, jbv via use-livecode wrote:

> 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
>
>
> _______________________________________________
> use-livecode mailing list [hidden email] Please visit this
> url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>



_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: semi-OT : mySQL oddities

Pi Digital 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


> On Nov 28, 2018, at 24:58 , jbv via use-livecode <[hidden email]> wrote:
>
> 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


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode