MySQL Bugs: #38219: Database Related Issue

Bug #38219	Database Related Issue
Submitted:	18 Jul 2008 6:24	Modified:	4 Aug 2008 13:54
Reporter:	Jayant Patil	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S1 (Critical)
Version:	4.1.22	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement, BIGINT, Database Related Issue, datatype, duplicate keys

Description:
Hello,

We are currently running the website. Our service provider has provided
phpmyadmin as an database interface. From the few days we are finding
difficulty to add any of data to the database tables.

example:
table name: admaster
column name: id
datatype: BIGINT(20)

now the above said table have total 16139 records.

Now whenever i had to add the new data, it shows the duplicate key id.

For temporary purpose, I change the length of datatype to 21 and then back to 20 for the column 'userid'.

The problem get solved for some time. when the 10 to 15 records are added,
the problem arises again and hence we lost the most of data in between.

for your kind information:
Server version: 4.1.22-standard
MySQL client version: 4.1.22
phpMyAdmin - 2.11.0
Language:English

Now please anyone help me to sort out this problem.

I hope from your reference / technical view, I can put the support ticket
to our server administrator who is also finding helpless in the same
issue.

Please revert back asap.

Warm Regards,
Jayant Patil

How to repeat:
CREATE TABLE `admaster` (
  `id` bigint(20) NOT NULL auto_increment,
  `renewed` char(1) NOT NULL default 'N',
  `adtype` varchar(10) default 'B',
  `vtype` bigint(20) default '0',
  `typeid` bigint(20) default '0',
  `make` bigint(20) default '0',
  `modelid` bigint(20) default '0',
  `versionid` varchar(255) default NULL,
  `other_make` varchar(100) default NULL,
  `other_model` varchar(100) default NULL,
  `other_version` varchar(100) default NULL,
  `style` int(11) default '0',
  `color` varchar(255) default NULL,
  `month` varchar(10) default NULL,
  `year` varchar(255) default NULL,
  `fueltype` varchar(255) default NULL,
  `bodycolor` varchar(255) default NULL,
  `interiorcolor` varchar(255) default NULL,
  `noofdoors` varchar(255) default NULL,
  `feature` varchar(255) default NULL,
  `mileage` varchar(11) default '0',
  `askingprice` varchar(11) default '0',
  `description` text,
  `fname` varchar(255) default NULL,
  `lname` varchar(255) default NULL,
  `add1` varchar(255) default NULL,
  `add2` varchar(255) default NULL,
  `state` varchar(255) default NULL,
  `country` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `other_city` varchar(100) NOT NULL default '0',
  `phone` varchar(255) default NULL,
  `mobile` varchar(255) default NULL,
  `fax` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `zip` varchar(255) default NULL,
  `img1` varchar(255) default NULL,
  `img2` varchar(255) default NULL,
  `img3` varchar(255) default NULL,
  `img4` varchar(255) default NULL,
  `uid` varchar(255) default NULL,
  `reg_no` varchar(15) default NULL,
  `reg_place` varchar(100) default NULL,
  `owner_no` varchar(10) default NULL,
  `have_insure` varchar(20) default NULL,
  `insure_date` date default NULL,
  `featurevalue` varchar(255) NOT NULL default '',
  `tyresprice` int(4) NOT NULL default '0',
  `reg_date` varchar(10) NOT NULL default '',
  `insexp_date` varchar(10) NOT NULL default '',
  `fitexp_date` varchar(10) NOT NULL default '',
  `permit` varchar(30) NOT NULL default '',
  `hypothecation` char(3) NOT NULL default '',
  `hypothetname` varchar(100) NOT NULL default '',
  `bodytytpe` varchar(4) NOT NULL default '',
  `builtat` varchar(100) NOT NULL default '',
  `cabin` varchar(10) NOT NULL default '',
  `featuredllist` char(1) NOT NULL default 'N',
  `posted` datetime default NULL,
  `updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `block_status` char(1) default 'N',
  `publish` char(1) default 'N',
  `expireddate` varchar(255) default NULL,
  `blockmail` varchar(255) default 'N',
  `transmission` varchar(255) default NULL,
  `drivetype` varchar(255) default NULL,
  `approve` char(1) default 'N',
  `alertmail` char(1) default 'N',
  `newlistingmail` char(1) default 'N',
  `freeorpaid` varchar(255) default 'Free',
  `usingmembership` char(1) default 'N',
  `usermembershipid` varchar(255) default NULL,
  `totalview` int(8) default '0',
  `emailsent` bigint(20) NOT NULL default '0',
  `renewalmail` char(1) default 'N',
  `appr_date` date NOT NULL default '0000-00-00',
  `appr_stat` char(1) NOT NULL default 'N',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16139 ;

Insert into admaster set vtype='2', make='43', modelid='714', versionid='137', other_make='', other_model='', other_version='', month='4', year='2002', fueltype='1', bodycolor='1', feature='Y', mileage='17000', askingprice='35000', description='', fname='Manpreet Singh', lname='Hundal', add1='Vrindavan Garden, Sahibabad, Ghaziabad, UP', add2='', city='21475', other_city='', phone='', mobile='9222222222', fax='', email='jayant@autonagar.com', zip='', uid='4632', reg_no='00000', reg_place='Ghaziabad', owner_no='5', have_insure='Not Specified', tyresprice='', reg_date='7-4-2002', insexp_date='--', fitexp_date='--', permit='', hypothecation='Yes', hypothetname='', bodytytpe='Half', builtat='', cabin='', posted=now(), updated=now(), approve='N'

Suggested fix:
For temporary purpose, I change the length of datatype to 21 and then back to 20 for the column 'userid'.

But this is not the right procedure, but surely temporary one.

Many thanks for reporting a bug. Unfortunately MySQL 4.1.22 is a really old version. Please upgrade first to MySQL 5.0.51b.

Also your problem not looks like a bug. Bug system is not the appropriate forum for asking help on using MySQL products.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

If you have problems with newer version and our support couldn't help you too then please make a short test that we can use for reproduce your problem.

Thank you for your interest in MySQL.

Do really the change of version will help the solution. I am not really confirm about it, as our server administrator has reasoning the other things.

See what our administrator says:
"We have checked the server.
Mysql is working fine on the server.
Nothing on the server end can cause such an issue.
You will need to check the code once.
Or it can only happen if you are logging in
from multiple locations and working on mysql."

Is this possible or the very inappropriate answer?

I really appreciate your point of view.

I would request our server administrator to upgrade the same.

Thanks.

Please, send the results of:

show table status like 'admaster';

Thanks for your reply.

----------------------------------------------------------------------
The request query: SHOW TABLE STATUS LIKE 'admaster';
The result as per:

Name:		 		admaster 
Engine:		 		InnoDB 
Version:		 	9
Row_format:	 		Dynamic 
Rows:				10933
Avg_row_length: 		720
Data_length:			7880704
Max_data_length: 		NULL 
Index_length:	 		0
Data_free:	 		0
Auto_increment: 		16285
Create_time:	 		2008-07-19 10:54:20
Update_time:	 		NULL 
Check_time:	 		NULL 
Collation:		 	latin1_swedish_ci 
Checksum:			NULL 
Create_options: 
Comment	InnoDB free:            49152 kB
--------------------------------------------------------------------

Kindly look for the same and confirm.

Thank you for the feedback.

You said "For temporary purpose, I change the length of datatype to 21 and then back to 20 for the column 'userid'." But there is no column named 'userid' in the CREATE TABLE you provided. Please either provide accurate output of SHOW CREATE TABLE or indicate correct name of the problem column.

Sorry for the inconvenience. I had some mis-communication.

Please look this.
Now, what i had changed into the code part for temporary purpose.
The unfortunate query:
----------------------------------------------------------------
ALTER TABLE `admaster` CHANGE `id` `id` BIGINT( 22 ) NOT NULL AUTO_INCREMENT

ALTER TABLE `admaster` CHANGE `id` `id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT.
-----------------------------------------------------------------

The above are two queries look similar but length of datatype is different.
I run these queries, when there is Insert error in database and the problem is solved and the data which i am losing can be retrieved.

Jayant Patil

Unfortunately, I can't repeat this ... neither with MySQL 4.1.22 nor with MySQL 5.0.