MySQL Bugs: #13691: Upgrading from 4.1.x to 5.0.x Decimal ColumnType error

Bug #13691	Upgrading from 4.1.x to 5.0.x Decimal ColumnType error
Submitted:	2 Oct 2005 16:56	Modified:	7 Oct 2005 10:04
Reporter:	Richard de Courtney	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.0.13	OS:	Microsoft Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
After an upgrade from 4.1.14 to 5.0.13, a table with the column type of DECIMAL produced spurious errors when trying to do a simple SELECT * FROM {tablename}. The error received on a Windows Server (using ASP) was:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

After conducting much research, there were no definitive answers. I recreated the table, repopulated the table, and only after removing the DECIMAL columns did the error disappear. I changed the columntype to FLOAT and this stopped the error from re-occuring.

Whilst I'm (only just) aware of the change in the DECIMAL storage type from string to binary. Shouldn't any upgrade of the database conduct these changes as part of the upgrade/installation routine?

Could the error be captured in a more definitive way by the ODBC call, rather than the spurious error message above?

How to repeat:
Upgrade database from 4.0.x to 5.0.3+ and leave tables with the columntype of DECIMAL unchanged.

Suggested fix:
During the installation, tables are changed automatically (via installation or script).

Thank you for a problem report.

The problem may be either in upgrade procedure itself or according to changes in DECIMAL data handling (see http://dev.mysql.com/doc/mysql/en/precision-math-decimal-changes.html for details).

Personally I'd recommend to dump data in 4.1.14 and restore them in 5.0.13, but the logic of your application should also be checked. 

To check what is the real problem with your table, and is it ODBC-related or uprgade-procedure related issue, please, send the results of SHOW CREATE TABLE for the table causing problems with this simple select.

I can now reproduce the error, when I change the column type to DECIMAL 10,3 in Navicat, a subsequent SELECT query (nothing special with the select) searches the table, will come back with no results.

The table is:

CREATE TABLE `store` (
  `productcode` int(11) NOT NULL default '0',
  `categorycode` int(11) default NULL,
  `category` varchar(50) default NULL,
  `subcategory` varchar(50) default NULL,
  `title` varchar(50) default NULL,
  `description` mediumtext,
  `units` int(11) default NULL,
  `setupcost` decimal(10,3) default NULL,
  `monthlycost` decimal(10,3) default NULL,
  `minimum` int(11) default NULL,
  `deliverydate` varchar(10) default NULL,
  `itemquantity` int(11) default NULL,
  `service` varchar(255) default NULL,
  `validformonths` smallint(6) default '0',
  PRIMARY KEY  (`productcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

If I change the SETUPCOST and MONTHLYCOST columns to FLOAT(10,3) it works fine.

Could you please provide any ASP test case of the bug?

CREATE TABLE `mysqltest` (
  `id` int(11) NOT NULL auto_increment,
  `columna` int(11) NOT NULL,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into `mysqltest` values('1','10','Test'),
 ('2','20','test 2'),
 ('3','30','test 3');

Page: http://enterprise.wirelessmedia.com/dev/mysql/test.asp

	'--// Open MySQL Database
	Set objConn = Server.CreateObject("ADODB.Connection")
	Set RS = Server.CreateObject("ADODB.RecordSet")
	objConn.ConnectionString="Driver={MySQL ODBC 3.51 Driver};Server=localhost;Option=3;Database=XXXXXX;Uid=XXXXXX;Pwd=;Port=3307"
	objConn.open
	
	'--// Select the SUM of columntype integer
		set RS = objConn.execute("SELECT SUM(columnA) AS testcount FROM mysqltest")
		response.write(RS("testcount"))

	'--// Close MySQL Database
	set RS = nothing
		objConn.Close
	Set objConn = nothing

The page produces the error:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

[Microsoft][ODBC Driver Manager] Program type out of range

/dev/mysql/test.asp, line 0

I think this is related to another Bug #13730 that I've also logged.

Everything works as expected in mysql command line client:

mysql> use test;
Database changed
mysql> CREATE TABLE `mysqltest` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `columna` int(11) NOT NULL,
    ->   `text` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)

mysql>
mysql> insert into `mysqltest` values('1','10','Test'),
    ->  ('2','20','test 2'),
    ->  ('3','30','test 3');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(columnA) AS testcount FROM mysqltest;
+-----------+
| testcount |
+-----------+
|        60 |
+-----------+
1 row in set (0.09 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)

That is why I changed category to ODBC. 

But, please, note, that there is no DECIMAL column in your test case, and original report was about problems with DECIMAL... Do you have any additional information on what is the real problem you have? If the test case you provided is the problem, then we have to change the Synopsis too. Or, you may report it as a separate bug, but then we need something repeatable about DECIMAL here.

There're many related bugreports.
See e.g. BUG#11795 and BUG#11035

Sorry, the above maybe related but heres the error concerning the Decimal formatting of a column:

CREATE TABLE `mysqltest2` (
  `id` int(11) NOT NULL auto_increment,
  `columna` decimal(5,2) NOT NULL,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into `mysqltest2` values('1','10.25','Test'), ('2','20.5','test 2'), ('3','30.75','test 3');

Script: http://enterprise.wirelessmedia.com/dev/mysql/decimal.asp
(a simple select * from mysqltest2)

Error: [Microsoft][ODBC Driver Manager] Program type out of range

There seems to be extremely serious problems between 5.0.13 and ODBC 3.51.11