SQL Bobbins

[ECA]

cock.

So I have this table , call it number, and it's gone and hit 99999, and every item after that has got number 100000. when it's meant to be a simple +1 to the last number.

the field is set to varchar(32), so it shouldnt be a problem.

What the tittins is going on? Any ideas.

 

[ECA]

Looking at the code, it just grabs the current value, adds 1, and updates the field - nothing crazy looking going on. :/

 

[Moriath]

Odd. Hard to say without seeing the code. Is it a display bug?

 

[ECA]

Don't think so, had a look at the code - and it just grabs that value, adds 1, and writes it back.

I'm fucking baffled, like scouse talking to a brexit voter.

 

[Moriath]

Lol. Well if theres no limit set. Just a 32 spaced varchar it should do what it says on the tin.

You tried manually updating the field to 100001 ?

 

[ECA]

@Moriath

Been looking at the code and thinking about it - it's reading the latest order number as 99999, adding one, and then writing as 100000.

So it seems like the issue is the code reading the newest number right?

the code there is:

$orderQuery = ("Select * From tablename Where Number > 0 Order By Number Desc Limit 1");


If I run that query manually it returns the result of 99999, despite there being several 100000 ( duplicate value ).

I havn't tried manually updating one to like 1000041 or some shit, you reckon that's worth a go?

 

[ECA]

ok so, that query returns 99999, whats stupid is each row has a datecrated in epoch, so if I change the query to sort descending by epoch I get 100000 ( ie correct ).

Any downside in changing it to pull the latest id by that instead of the order number?

 

[ECA]

Or can I just change it from varchar to int without fucking the data? It's only ever an integer.

 

[Moriath]

Or can I just change it from varchar to int without fucking the data? It's only ever an integer.

That depends on what else your doing with it

 

[Moriath]

@Moriath

Been looking at the code and thinking about it - it's reading the latest order number as 99999, adding one, and then writing as 100000.

So it seems like the issue is the code reading the newest number right?

the code there is:

$orderQuery = ("Select * From tablename Where Number > 0 Order By Number Desc Limit 1");


If I run that query manually it returns the result of 99999, despite there being several 100000 ( duplicate value ).

I havn't tried manually updating one to like 1000041 or some shit, you reckon that's worth a go?

But its weird why it did it when it got to 99999 and not before. You would have thought if there was an error simply taking the number and adding one to it, it would have happened before.

So its taking 100000 and minusing 1 and addi 1 again. Or is the variable not being cleared so its always 99999

 

[Moriath]

ok so, that query returns 99999, whats stupid is each row has a datecrated in epoch, so if I change the query to sort descending by epoch I get 100000 ( ie correct ).

Any downside in changing it to pull the latest id by that instead of the order number?

Taking the last number would be fine. Or the highest. Seems a bit screwy to always do a sort order to just add one to the highest number.

 

[ECA]

Taking the last number would be fine. Or the highest. Seems a bit screwy to always do a sort order to just add one to the highest number.

it's using ORDER BY with a desc 1, so it just returns the highest result. The problem is that should be 100000, but it's returning 99999. Something to do with order by and varchar doing something wierd?

If I select * from creationtime and order by number desc 1, I get the correct result of 100000.
It's something weird going on with varchar and order by where it doesn't think 100000 is greater than 99999.... or something even screwier than that.

 

[Moriath]

Yup. Odd feature you found there. Doing the find a slightly different way shouldnt make any odds afaik

 

[caLLous]

It sounds like it's trying to sort it alphabetically because varchar isn't explicitly a number type but if it was a sorting problem it would be like 1 10 100 2 20 200 3 30 etc, ie, it would've tripped up waaaaay before getting to 100000. If you're not ever going to do mathy stuff with the value (which includes sorting numerically) then a string variable is fine but otherwise always store it as a number type. Try casting to an int in the order statement and you might get away without having to change from varchar (it'll probably be noticeably slower than natively sorting integers though with 100000+ records).

The same thing happens in bash with the sort command unless you use the -g switch which forces sorting by numerical not alphabetical value.

 

[Moriath]

If the sort was like that as you say would have fecked up ages ago... Or perhaps it did with 99999 numbers no one noticed lol

 

[TdC]

Varchar isn't a number. Also, why not do a select max()?

 

[ECA]

Varchar isn't a number. Also, why not do a select max()?

I didn't design the db or write the code.

 

[TdC]

Can you change the code?

 

[TdC]

Also, if you change the schema, you may have to dump/insert the data again. Don't use int if you foresee being able to go over whatever MAXINT is on your system.

 

[TdC]

I became intrigued by your problem but I can't replicate it on a database I happened to have handy (postgresql). If I make a table with a serial PK and an int column and loop an insert to 150K it works just peachy. Sorry chumlar

 

[ECA]

It's mysql running on centos.
Also I'd assume you need the field set to varchar(32) to try to replicate.

 

[TdC]

well, that's amusing

portal=> create table test_table(
ID SERIAL PRIMARY KEY NOT NULL,
MYNUM VARCHAR(32) NOT NULL
);
CREATE TABLE
portal=> INSERT INTO test_table (mynum)
SELECT x.mynum
FROM generate_series(1,15000001) AS x(mynum);
INSERT 0 15000001
portal=>  select max(mynum) from test_table;
   max
---------
 9999999
(1 row)

and

portal=> drop table test_table;
DROP TABLE
portal=> create table test_table(
ID SERIAL PRIMARY KEY NOT NULL,
MYNUM INT NOT NULL
);
CREATE TABLE
portal=> INSERT INTO test_table (mynum)
SELECT x.mynum
FROM generate_series(1,15000001) AS x(mynum);
INSERT 0 15000001
portal=>  select max(mynum) from test_table;
   max
----------
 15000001
(1 row)

 

[ECA]

You got higher than me with varchar though
Fucking weird shit eh.

 

[TdC]

for a moment I was thinking it was the fact that you're violating the number of bits in the varchar def but your number isn't big enough.

anyway,

ALTER TABLE tablename MODIFY columnname INTEGER;

might work.

 

[ECA]

Yup, my plans to duplicate the db, run that on the duplicate, fingers crossed it works and I can do it on live.

 

[TdC]

remember to modify the rows where you have duplicate numbers to be what they are supposed to be before you flip your test into prod.

 

[Moriath]

Yup, my plans to duplicate the db, run that on the duplicate, fingers crossed it works and I can do it on live.

Good luck

 

[caLLous]

To save having to manually rename the +100000 ones, could you sort the whole table by epoch time then step through it with a newly created "number" field of type integer, and then rename the new number field to Number?

 

[ECA]

To save having to manually rename the +100000 ones, could you sort the whole table by epoch time then step through it with a newly created "number" field of type integer, and then rename the new number field to Number?

Great idea but unfortunately there's a lot of stuff that doesn't get assigned a number so I can't do that.

 

[TdC]

What what what? Who the hell designed your database?