| Bug #14944 | MySQL Integer not summing correctly | ||
|---|---|---|---|
| Submitted: | 15 Nov 2005 11:18 | Modified: | 15 Nov 2005 15:58 |
| Reporter: | Graham Philips | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.12 | OS: | Windows (Win XP / Server 2K3) |
| Assigned to: | CPU Architecture: | Any | |
[15 Nov 2005 11:18]
Graham Philips
[15 Nov 2005 12:14]
MySQL Verification Team
Could you please provide the sample script with table structure definition, some rows insert and the offended query. Also are you using the DSN option change BIGINT Columns to Int otherwise do a test with. Thanks in advance.
[15 Nov 2005 13:18]
Graham Philips
Here is the appropriate MySQL backup dump to generate the required data:
----------------------------------------------------------
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version 5.0.15-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
--
-- Create schema power_db
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ power_db;
USE power_db;
--
-- Table structure for table `power_db`.`pw_order_dtl`
--
DROP TABLE IF EXISTS `pw_order_dtl`;
CREATE TABLE `pw_order_dtl` (
`order_dtl_id` int(11) NOT NULL auto_increment,
`order_id` int(11) NOT NULL default '0',
`product_id` int(11) NOT NULL default '0',
`basic_price` float NOT NULL default '0',
`vat_price` float NOT NULL default '0',
`transaction` int(11) NOT NULL default '0',
`size` float(11,1) NOT NULL default '1.0',
`quantity` int(11) NOT NULL default '0',
PRIMARY KEY (`order_dtl_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `power_db`.`pw_order_dtl`
--
/*!40000 ALTER TABLE `pw_order_dtl` DISABLE KEYS */;
INSERT INTO `pw_order_dtl` (`order_dtl_id`,`order_id`,`product_id`,`basic_price`,`vat_price`,`transaction`,`size`,`quantity`) VALUES
(1,452,1,0,0,2,1.0,3),
(2,452,3,0,0,2,1.0,1),
(3,452,7,0,0,1,1.0,1),
(4,452,5,0,0,1,1.0,1);
/*!40000 ALTER TABLE `pw_order_dtl` ENABLE KEYS */;
--
-- Table structure for table `power_db`.`pw_product`
--
DROP TABLE IF EXISTS `pw_product`;
CREATE TABLE `pw_product` (
`product_id` int(11) NOT NULL auto_increment,
`type` varchar(100) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`url` varchar(100) NOT NULL default '',
`buy_price` float NOT NULL default '0',
`purchaseable` tinyint(4) NOT NULL default '0',
`hireable` tinyint(4) NOT NULL default '0',
`hire_price` float NOT NULL default '0',
`hire_length` int(11) NOT NULL default '0',
`stock` float NOT NULL default '0',
`stock_wait` int(11) NOT NULL default '0',
`active` tinyint(1) NOT NULL default '0',
`vat` int(11) NOT NULL default '0',
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `power_db`.`pw_product`
--
/*!40000 ALTER TABLE `pw_product` DISABLE KEYS */;
INSERT INTO `pw_product` (`product_id`,`type`,`description`,`url`,`buy_price`,`purchaseable`,`hireable`,`hire_price`,`hire_length`,`stock`,`stock_wait`,`active`,`vat`) VALUES
(1,'1','EMFields PRO','/sales/meters/pro.asp',295,1,1,29.79,7,8,14,1,2),
(3,'1','COM Monitor','/sales/meters/com.asp',149,1,1,21.28,7,0,14,1,2),
(5,'2','Buying an EMF-safe property','/sales/pubs/emfsafe.asp',15,1,0,0,0,13,14,1,1),
(7,'2','EMF and Microwave protection','/sales/pubs/emfprotect.asp',15,1,0,0,0,25,14,1,1);
/*!40000 ALTER TABLE `pw_product` ENABLE KEYS */;
--
-- Table structure for table `power_db`.`pw_product_type`
--
DROP TABLE IF EXISTS `pw_product_type`;
CREATE TABLE `pw_product_type` (
`type_id` int(11) NOT NULL auto_increment,
`type_description` varchar(100) NOT NULL default '',
PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `power_db`.`pw_product_type`
--
/*!40000 ALTER TABLE `pw_product_type` DISABLE KEYS */;
INSERT INTO `pw_product_type` (`type_id`,`type_description`) VALUES
(1,'Meter'),
(2,'Publication');
/*!40000 ALTER TABLE `pw_product_type` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
------------------------------------------------------
The offending query is the one mentioned in the original post, though the problem is not the query itself, but the running of it on a webserver. The connection variable is:
------------------------------------------------------
dbCon = "Driver={mySQL ODBC 3.51 Driver};" & _
"Server={server};" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database={database};" & _
"Uid={username}" & _
"Pwd={password}"
--------------------------------------------
Is there anything else you need?
[15 Nov 2005 14:09]
MySQL Verification Team
I did a small test using the test ODBC tool called odbcte32 and I got the expected resulted. Currently I don't have ways for to run asp pages on my computer, so can you to start the server with --log start option and show us which is the query send to the server, also you can make my life easy if you provide once the complete script test code which I will run when I fix my Windows. Full Connect(Default) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN 'bugdb5'. SQLExecDirect: In: hstmt = 0x003B1B50, szSqlStr = "select ptype.type_id, od.product_id, sum(od.quantity) ...", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "type_id", "product_id", "quantity", "size" 1, 1, 4, 2.0 2, 7, 2, 2.0 2 rows fetched f Thanks in advance.
[15 Nov 2005 14:23]
Graham Philips
Not trying to sound ignorant, but my experience at configuring and debugging MySQL is very limited and I could not found an adequate explanation using Google - How do I start the server with the parament --log? - Graham
[15 Nov 2005 14:47]
MySQL Verification Team
If you are starting the server as service and have an my.ini file you just
need to add a line with the word log. Like I show below:
c:\mysql\bin>type c:\my.ini
[mysqld]
basedir=c:/mysql
datadir=c:/mysql/data
log
When the server is started it creates a file called computer_name.log,
how you can see below:
c:\mysql\bin>dir c:\mysql\data\*.log
O volume na unidade C não tem nome.
O número de série do volume é 0465-CD10
Pasta de c:\mysql\data
15/11/2005 12:39 125 light.log
1 arquivo(s) 125 bytes
0 pasta(s) 13.564.436.480 bytes disponíveis
So after shutdown the server you can see the queries log how
you can see below:
c:\mysql\bin>type c:\mysql\data\light.log
mysqld-nt, Version: 5.0.15-nt-log. started with:
TCP Port: 0, Named Pipe: (null)
Time Id Command Argument
051115 12:40:31 1 Connect root@localhost on power_db
051115 12:41:20 1 Query select ptype.type_id, od.product_id, sum(od.quantity) as quantity, sum(od.size)
as size
from pw_order_dtl od, pw_product prod, pw_product_type ptype
where od.product_id = prod.product_id
and prod.type = ptype.type_id
and od.order_id = 452
group by ptype.type_id
order by ptype.type_id asc
051115 12:41:26 1 Quit
051115 12:41:43 2 Connect root@localhost on
2 Shutdown
If you start the server as standalone so:
mysqld-nt --standalone --console --log
[15 Nov 2005 15:39]
Graham Philips
I'm afraid I will have to apologise here. My upgrade to MyODBC 3.51.12 was today (to see if it fixed the bug), and I had not thought to reset my machine to see if that was the issue. Having reset the machine the problems have entirely gone away and the script is functioning as expected - Just need to get the webhost to update now ... Thanks for your time.
[15 Nov 2005 15:58]
MySQL Verification Team
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/
