Windows mySQL - two "auto_increments"

[ChaOs]

5 hours ago, ChaOs said:

Software-Info:

* Windows 8.1 Professional Edition

* Apache/2.4.23 (Win32); OpenSSL/1.0.2h; PHP Version 7.0.9; PHP-Extension: mysqli

* phpMyAdmin 4.5.1; 10.1.16-MariaDB; DB-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $

 

[n3g4n]

Ooops, sorry I missed that info on your first post.

 

Try this to see if this works better:

 

INSERT INTO `___home_impressum_wordcascade_wordcascade` (
	`id`,
	`headline`,
	`word`,
	`unlocked`,
	`order`,
	`timestamp`
) 
SELECT 
  '20180826235955' ,
  'aa',
  'bb', 
   0, 
   MAX(`order`)+ 1,
  '2018-08-26 23:59:55'
FROM`___home_impressum_wordcascade_wordcascade` `tmp`

 

[ChaOs]

6 hours ago, n3g4n said:

Ooops, sorry I missed that info on your first post.

 

Try this to see if this works better:

 

INSERT INTO `___home_impressum_wordcascade_wordcascade` (
	`id`,
	`headline`,
	`word`,
	`unlocked`,
	`order`,
	`timestamp`
) 
SELECT 
  '20180826235955' ,
  'aa',
  'bb', 
   0, 
   MAX(`order`)+ 1,
  '2018-08-26 23:59:55'
FROM`___home_impressum_wordcascade_wordcascade` `tmp`

 

Sry, too did not get the msg before I fell to sleep .... it was 5:30 AM here and I thought the day was long enough...

 

This doesn't work anyway .... the Syntax-Highlighter is driving me crazy so freakn hard.

But why SELECT before '20180826235955' ?

 

 

 

[Afro.]

So from what I gathered by quickly reading through it cos i'm at work 😛 your id will always be unique due to DATETIME but your  "order" must be unique too and i'm guessing you use PHP on the Server Side so just use

$order = date('U');

and insert that as order. 

Or in straight in mysql
 

INSERT INTO `table` (id, order) VALUES (1, UNIX_TIMESTAMP ( NOW () ) )

 

[n3g4n]

3 hours ago, ChaOs said:

Sry, too did not get the msg before I fell to sleep .... it was 5:30 AM here and I thought the day was long enough...

 

This doesn't work anyway .... the Syntax-Highlighter is driving me crazy so freakn hard.

But why SELECT before '20180826235955' ?

 

 

 

No problem at all. 

 

You can give a default value to a SELECT.  So if you SELECT '20180826235955',  you force the first value of the SELECT query result to be '20180826235955'.

 

There must be something wrong with your phpMyAdmin settings since it's working perfectly in mine.  Can you try running this query in your PHP code to see if it works?

Edited August 27, 2018 by n3g4n

[ChaOs]

6 hours ago, n3g4n said:

No problem at all. 

 

You can give a default value to a SELECT.  So if you SELECT '20180826235955',  you force the first value of the SELECT query result to be '20180826235955'.

 

There must be something wrong with your phpMyAdmin settings since it's working perfectly in mine.  Can you try running this query in your PHP code to see if it works?

Yes this is what I am going to try. And also testing the "lock"-Version ... gonna tell if something changed this way

 

Thx for it first btw

 

[n3g4n]

Just now, ChaOs said:

Yes this is what I am going to try. And also testing the "lock"-Version ... gonna tell if something changed this way

 

Thx for it first btw

 

You're welcome!

[ChaOs]

On 8/27/2018 at 2:16 PM, Afro. said:

So from what I gathered by quickly reading through it cos i'm at work 😛 your id will always be unique due to DATETIME but your  "order" must be unique too and i'm guessing you use PHP on the Server Side so just use

$order = date('U');

and insert that as order. 

Or in straight in mysql
 

INSERT INTO `table` (id, order) VALUES (1, UNIX_TIMESTAMP ( NOW () ) )

@Afro.

Yeah, thx ... but the fields are:

 

--------------------------------------------------------------------------------------
| id*            | headline | word         | unlocked | order* | timestamp           |
--------------------------------------------------------------------------------------
| 20180826235958 | Hello    | How are you? | 0        | 1      | 2018-08-26 23:59:58 |
| 20180826235959 | Hi       | I'm fine     | 0        | 2      | 2018-08-26 23:59:59 |

were order is 1,2,3,4,5,6, .... and no date, so you can stack the order.

and id is datetime as numeric string.

 

@n3g4n

I have a thought about that the second entry did not work ... because primary & int(255) ... so you get a collision because the biggest possible int (2147483648) was taken two times (the datetime-string is bigger than int255). Maybe that was the reason last time ... but the freakin' syntax parser everytime underlines that ... i'll try your method out again later, because first I tried the lock-version as I got the int problem this time and made the id simply double.

 

@Chuckun

As far you're saying that you're familar with that lock methods and as you said the lock only blocks until the request is done and the unlocking was given ... unfortunately I think the lock method doesn't work at all. It seems to lock nothing.

I can request and request and request and nothing prevents me from that.

 

For this case I wrote a little test-system (zip file) with clear outputs to imagine what is happening....

the 'insert_test_lock_method.php' calls the insert and the 'sql_unittest.php' is a frameset with 10 frames to call the .php-file 10 times with one click within 1-2 seconds ... a little ugly but useful to test this case anyway.

 

    $tbl = '___home_impressum_wordcascade_wordcascade';
        
    $config = array(
        'sql' => array(
            'server' => 'localhost',
            'user' => 'root',
            'password' => '',
            'database' => 'webgen_tkr'
        )
    );

 

those lines should be changed to own db settings in insert_test_lock_method.php

 

@n3g4n

maybe you could look at those way, too ...

 

thank you very much

 

 

 

test.zip

Edited August 30, 2018 by ChaOs
wrong file

[n3g4n]

12 minutes ago, ChaOs said:

I have a thought about that the second entry did not work ... because primary & int(255) ... so you get a collision because the biggest possible int (2147483648) was taken two times (the datetime-string is bigger than int255). Maybe that was the reason last time ... but the freakin' syntax parser everytime underlines that ... i'll try your method out again later, because first I tried the lock-version as I got the int problem this time and made the id simply double.

In the table I created for testing the query I gave you, I had to change the id INT to BIGINT and it worked.  You could try with another id value, of course.

[ChaOs]

8 hours ago, n3g4n said:

In the table I created for testing the query I gave you, I had to change the id INT to BIGINT and it worked.  You could try with another id value, of course.

Yeah. But until I got that the id tried to be similar in this case I had to try the lock-version, because the syntax parser in my PHPmyAdmin, as u saw, everytime cried. So it was nearly impossible to fix up other errors which I didn't see by myself.

Now I go on testing the code by your select max version.

 

Stupid is also that the LOCK-Version seems not to be working. 

But this was the only way that came in my mind to check if it's working.

And by the way ... the lock is the version with the most work, true, because you jump between PHP and mySQL,

but it was also the most hopeful ... if that had worked you could turn around those way every time because it's flexible enough, you know?

 

[Afro.]

Chaos create a repository on github or bitbucket and link me so I can help you

[ChaOs]

14 hours ago, Afro. said:

Chaos create a repository on github or bitbucket and link me so I can help you

https://bitbucket.org/isocity/mysql-transaction-model/src/master/

 

 

[Afro.]

10 hours ago, ChaOs said:

https://bitbucket.org/isocity/mysql-transaction-model/src/master/

 

 

Alright i'll look into it after work