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: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 5.0.13 | OS: | Windows (Windows 2003) |
Assigned to: | CPU Architecture: | Any |
[2 Oct 2005 16:56]
Richard de Courtney
[3 Oct 2005 7:39]
Valeriy Kravchuk
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.
[3 Oct 2005 16:07]
Richard de Courtney
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.
[4 Oct 2005 7:32]
Vasily Kishkin
Could you please provide any ASP test case of the bug?
[4 Oct 2005 19:43]
Richard de Courtney
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
[4 Oct 2005 19:44]
Richard de Courtney
I think this is related to another Bug #13730 that I've also logged.
[5 Oct 2005 9:12]
Valeriy Kravchuk
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.
[7 Oct 2005 10:04]
Sergei Golubchik
There're many related bugreports. See e.g. BUG#11795 and BUG#11035
[7 Oct 2005 14:56]
Richard de Courtney
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