Edit report at https://bugs.php.net/bug.php?id=61588&edit=1
ID: 61588
Comment by: cdburgess at gmail 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:
Here is the existing MySQL bug for this issue: http://bugs.mysql.com/bug.php?id=66794
Previous Comments:
------------------------------------------------------------------------
[2012-09-12 21:18:29] jprodonovich at ttttire dot com
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.
------------------------------------------------------------------------
[2012-04-30 15:02:00] uw@php.net
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,
mysql> MyInstall.user_id, MyInstall.script_id,
mysql> MyInstall.path, MyInstall.url,
mysql> MyInstall.created, MyInstall.version,
mysql> MyInstall.admin_url, MyInstall.name,
mysql> MyInstall.icon FROM my_installs AS MyInstall
mysql> 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,
mysql> MyInstall.user_id, MyInstall.script_id,
mysql> MyInstall.path, MyInstall.url,
mysql> MyInstall.created, MyInstall.version,
mysql> MyInstall.admin_url, MyInstall.name,
mysql> MyInstall.icon FROM my_installs AS MyInstall
mysql> where user_id = "dc038c9e-7b4b-11e1-8397-60195b7d6275" and script_id =
mysql> "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