I've had the same issue. The problem, as I saw it, was that I inserted a
negatvie value into the ID field. For some reason, it "rolls" to the
highest possible positive numeric value if a negative number is used in the
ID field.
I made the field unsigned to address this issue in mySQL, but, to fix it
entirely, I had to drop the table entirely and re-create before it would let
me fix the problem. Simply deleting the negative ID record didn't fix the
problem.
>From: Gábor Lénárt <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: [EMAIL PROTECTED]
>Subject: auto_increment problem with mysql 3.23.32
>Date: Wed, 14 Feb 2001 12:21:50 +0100
>
>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 ids>0)
>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.
>
>
>--
> --[ Gábor Lénárt ]---[ 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
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
---------------------------------------------------------------------
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
