Edit report at https://bugs.php.net/bug.php?id=61588&edit=1
ID: 61588
Comment by: jprodonovich at ttttire dot com
Reported by: cdburgess at gmail dot com
Summary: PDOStatement::getColumnMeta returns original table
name from view
Status: Not a bug
Type: Bug
Package: PDO related
Operating System: Mac OSX
PHP Version: 5.3.10
Assigned To: mysql
Block user comment: N
Private report: N
New Comment:
I have the exact same problem described here on a slightly older version of
MySQL.
Has there been any kind of resolution from MySQL on this issue or any type of
workaround that can be used?
I also followed the same steps and verified that the mysqli command-line client
produced the incorrect name for the Table when selecting from a view.
Previous Comments:
------------------------------------------------------------------------
[2012-04-30 15:02:00] [email protected]
Sorry, but your problem does not imply a bug in PHP itself. For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system is not the
appropriate forum for asking support questions. Due to the volume
of reports we can not explain in detail here why your report is not
a bug. The support channels will be able to provide an explanation
for you.
Thank you for your interest in PHP.
Thank you for your report. You have hit an issue but this is not a PHP bug.
Please, note the fine line between a PHP related bug and an issue with the
MySQL database.
Please, log in to MySQL using the MySQL command line client. Set the option
--column-type-info for the command line client. This will make the MySQL prompt
print the metadata reported by MySQL.
Upon execution of:
mysql> SELECT `MyInstall`.`id`, `MyInstall`.`user_id`, `MyInstall`.`script_id`,
`MyInstall`.`path`, `MyInstall`.`url`, `MyInstall`.`created`,
`MyInstall`.`version`, `MyInstall`.`admin_url`, `MyInstall`.`name`,
`MyInstall`.`icon` FROM `my_installs` AS `MyInstall` where user_id =
"dc038c9e-7b4b-11e1-8397-60195b7d6275" ORDER BY `url` ASC;
I see MySQL report the following meta data for the 3rd column (offset 2 in PDO):
Field 3: `script_id`
Catalog: `def`
Database: `testpdo`
Table: `MyInstall`
Org_table: `scripts`
Type: STRING
Collation: utf8_general_ci (33)
Length: 108
Max_length: 36
Decimals: 0
Flags: NO_DEFAULT_VALUE
Adding a second condition to the WHERE clause does in fact change meta data as
you report it.
mysql> SELECT `MyInstall`.`id`, `MyInstall`.`user_id`, `MyInstall`.`script_id`,
`MyInstall`.`path`, `MyInstall`.`url`, `MyInstall`.`created`,
`MyInstall`.`version`, `MyInstall`.`admin_url`, `MyInstall`.`name`,
`MyInstall`.`icon` FROM `my_installs` AS `MyInstall` where user_id =
"dc038c9e-7b4b-11e1-8397-60195b7d6275" and script_id =
"057de1e0-7b48-11e1-8397-60195b7d6275" ORDER BY `url` ASC;
Note the difference:
Field 3: `script_id`
Catalog: `def`
Database: `testpdo`
Table: `Script`
Org_table: `scripts`
Type: STRING
Collation: utf8_general_ci (33)
Length: 108
Max_length: 36
Decimals: 0
Flags: NOT_NULL PRI_KEY NO_DEFAULT_VALUE PART_KEY
However, as this issue can be reproduced on the MySQL prompt one can be sure
that there is no bug inside PHP. PHP does give you what MySQL reports.
The correctness of the MySQL value itself should be checked by MySQL, not PHP.
Please report a bug at mysql.com.
------------------------------------------------------------------------
[2012-04-03 05:57:11] cdburgess at gmail dot com
PHP v5.3.10
MySQL v5.5.22
Apache v2.2.21
Here is a script that contains all of the information you need to reproduce.
The
commented parts at the bottom contain all of the schema / data information.
Just
create your database, setup the PDO access, and run the script. It will provide
the queries, descriptions, and getColumnMeta results to show you what I am
seeing.
Thanks!
---------- SCRIPT BELOW HERE ----------
<?php
$connection = new PDO(
'mysql:host=localhost;dbname=testpdo',
'root',
'password'
);
$query = "select * from my_installs WHERE user_id = 'dcc87a2c-7b4b-11e1-8397-
60195b7d6275' and script_id = '057de1e0-7b48-11e1-8397-60195b7d6275' LIMIT 1";
echo $query . '<br>' . "\n";
echo 'In this query, you will see the table is reported as expected.
(my_installs)';
$result = $connection->query($query);
var_dump($result->getColumnMeta(2));
$query = "SELECT `MyInstall`.`id`, `MyInstall`.`user_id`,
`MyInstall`.`script_id`, `MyInstall`.`path`, `MyInstall`.`url`,
`MyInstall`.`created`, `MyInstall`.`version`, `MyInstall`.`admin_url`,
`MyInstall`.`name`, `MyInstall`.`icon` FROM `my_installs` AS `MyInstall` WHERE
`user_id` = 'dcc87a2c-7b4b-11e1-8397-60195b7d6275' ORDER BY `url` ASC";
echo $query . '<br>' . "\n";
echo 'With the Alias format of the query and using only the user_id in the
where
clause, the table Alias is reported.';
$result = $connection->query($query);
var_dump($result->getColumnMeta(2));
$query = "SELECT `MyInstall`.`id`, `MyInstall`.`user_id`,
`MyInstall`.`script_id`, `MyInstall`.`path`, `MyInstall`.`url`,
`MyInstall`.`created`, `MyInstall`.`version`, `MyInstall`.`admin_url`,
`MyInstall`.`name`, `MyInstall`.`icon` FROM `my_installs` AS `MyInstall` WHERE
`user_id` = 'dcc87a2c-7b4b-11e1-8397-60195b7d6275' AND `script_id` = '057de1e0-
7b48-11e1-8397-60195b7d6275' ORDER BY `url` ASC";
echo $query . '<br>' . "\n";
echo 'When adding the second where clause (using script_id), the Script table
alias is reported, but MyInstall or my_installs is expected.';
$result = $connection->query($query);
var_dump($result->getColumnMeta(2));
/*
-- phpMyAdmin SQL Dump
-- version 3.4.10.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 03, 2012 at 07:46 AM
-- Server version: 5.5.22
-- PHP Version: 5.3.10
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `testpdo`
--
DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `BuildNewInstallStatPartition`()
BEGIN
DECLARE maxpart_date date;
SELECT SUBSTR(MAX(PARTITION_DESCRIPTION) , 2, 19) + INTERVAL 1 MONTH
INTO maxpart_date
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'install_stats';
SET @sql := CONCAT('ALTER TABLE install_stats ADD PARTITION (PARTITION p_'
, YEAR(maxpart_date), MONTH(maxpart_date)
, ' values less than('''
, CAST(maxpart_date as DATETIME)
, '''))');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- --------------------------------------------------------
--
-- Stand-in structure for view `my_installs`
--
CREATE TABLE IF NOT EXISTS `my_installs` (
`id` char(36)
,`user_id` char(36)
,`script_id` char(36)
,`script_version_id` char(36)
,`script_version_package_id` char(36)
,`name` varchar(35)
,`version` varchar(25)
,`path` varchar(255)
,`url` varchar(255)
,`admin_url` varchar(128)
,`icon` varchar(128)
,`created` datetime
);
-- --------------------------------------------------------
--
-- Table structure for table `scripts`
--
CREATE TABLE IF NOT EXISTS `scripts` (
`id` char(36) NOT NULL,
`category_id` char(36) NOT NULL,
`name` varchar(35) NOT NULL,
`icon` varchar(128) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `scripts`
--
INSERT INTO `scripts` (`id`, `category_id`, `name`, `icon`, `created`) VALUES
('057de1e0-7b48-11e1-8397-60195b7d6275',
'05aff694-7b48-11e1-8397-60195b7d6275',
'WordPress', 'icon_WordPress.gif', '0000-00-00 00:00:00');
-- --------------------------------------------------------
--
-- Table structure for table `script_installs`
--
CREATE TABLE IF NOT EXISTS `script_installs` (
`id` char(36) NOT NULL,
`user_id` char(36) NOT NULL,
`script_version_package_id` char(36) NOT NULL,
`path` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `script_version_package_id` (`script_version_package_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `script_installs`
--
INSERT INTO `script_installs` (`id`, `user_id`, `script_version_package_id`,
`path`, `url`, `created`) VALUES
('a6d1342a-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c14429c-7b48-11e1-8397-60195b7d6275', 'blog1', 'blog1.example.com', '2009-06-
15 12:43:30'),
('a6d134ac-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c16aa6e-7b48-11e1-8397-60195b7d6275', 'blog2', 'blog2.example.com', '2009-06-
15 12:15:10'),
('a880554e-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c16aa6e-7b48-11e1-8397-60195b7d6275', 'blog3', 'blog3.example.com', '2010-06-
28 22:27:48'),
('ad7aac3e-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c14429c-7b48-11e1-8397-60195b7d6275', 'blog4', 'blog4.example.com', '2010-06-
29 18:19:46'),
('ae92abf8-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c16aa6e-7b48-11e1-8397-60195b7d6275', 'blog5', 'blog5.example.com', '2010-10-
04 19:12:28'),
('c71a2368-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c144120-7b48-11e1-8397-60195b7d6275', 'blog6', 'blog6.example.com', '2011-11-
07 22:26:38'),
('c71c1c36-7b4d-11e1-8397-60195b7d6275',
'dc038c9e-7b4b-11e1-8397-60195b7d6275',
'0c144120-7b48-11e1-8397-60195b7d6275', 'blog7', 'blog7.example.com', '2011-11-
08 09:26:58');
-- --------------------------------------------------------
--
-- Table structure for table `script_versions`
--
CREATE TABLE IF NOT EXISTS `script_versions` (
`id` char(36) CHARACTER SET utf8 NOT NULL,
`script_id` char(36) CHARACTER SET utf8 NOT NULL,
`version` varchar(25) CHARACTER SET utf8 NOT NULL,
`admin_url` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `script_id` (`script_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `script_versions`
--
INSERT INTO `script_versions` (`id`, `script_id`, `version`, `admin_url`,
`created`) VALUES
('0c02e286-7b48-11e1-8397-60195b7d6275',
'057de1e0-7b48-11e1-8397-60195b7d6275',
'3.2.1', 'wp-admin', '2011-12-01 10:15:52'),
('0c050624-7b48-11e1-8397-60195b7d6275',
'057de1e0-7b48-11e1-8397-60195b7d6275',
'3.3.1', 'wp-admin', '2012-02-21 15:12:01');
-- --------------------------------------------------------
--
-- Table structure for table `script_version_packages`
--
CREATE TABLE IF NOT EXISTS `script_version_packages` (
`id` char(36) NOT NULL,
`script_version_id` char(36) NOT NULL,
`process` enum('install','upgrade','import') CHARACTER SET utf8 NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `script_version_id` (`script_version_id`,`process`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `script_version_packages`
--
INSERT INTO `script_version_packages` (`id`, `script_version_id`, `process`,
`created`) VALUES
('0c144120-7b48-11e1-8397-60195b7d6275',
'0c02e286-7b48-11e1-8397-60195b7d6275',
'install', '2011-12-01 10:15:52'),
('0c14429c-7b48-11e1-8397-60195b7d6275',
'0c02e286-7b48-11e1-8397-60195b7d6275',
'upgrade', '2011-12-01 10:15:53'),
('0c16aa6e-7b48-11e1-8397-60195b7d6275',
'0c050624-7b48-11e1-8397-60195b7d6275',
'upgrade', '2012-01-03 18:05:35');
-- --------------------------------------------------------
--
-- Structure for view `my_installs`
--
DROP TABLE IF EXISTS `my_installs`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`my_installs` AS select `ScriptInstall`.`id` AS `id`,`ScriptInstall`.`user_id`
AS `user_id`,`Script`.`id` AS `script_id`,`ScriptVersion`.`id` AS
`script_version_id`,`ScriptVersionPackage`.`id` AS
`script_version_package_id`,`Script`.`name` AS `name`,`ScriptVersion`.`version`
AS `version`,`ScriptInstall`.`path` AS `path`,`ScriptInstall`.`url` AS
`url`,`ScriptVersion`.`admin_url` AS `admin_url`,`Script`.`icon` AS
`icon`,`ScriptInstall`.`created` AS `created` from (((`script_installs`
`ScriptInstall` left join `script_version_packages` `ScriptVersionPackage`
on((convert(`ScriptVersionPackage`.`id` using utf8) =
`ScriptInstall`.`script_version_package_id`))) left join `script_versions`
`ScriptVersion` on((`ScriptVersion`.`id` =
convert(`ScriptVersionPackage`.`script_version_id` using utf8)))) left join
`scripts` `Script` on((`Script`.`id` = `ScriptVersion`.`script_id`)));
DELIMITER $$
--
-- Events
--
CREATE DEFINER=`root`@`localhost` EVENT `e_create_order_stats_partition` ON
SCHEDULE EVERY 1 MONTH STARTS '2011-03-15 03:30:00' ON COMPLETION NOT PRESERVE
ENABLE DO CALL BuildNewInstallStatPartition$$
DELIMITER ;
*/
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=61588
--
Edit this bug report at https://bugs.php.net/bug.php?id=61588&edit=1
