Edit report at https://bugs.php.net/bug.php?id=61588&edit=1
ID: 61588
User updated by: cdburgess at gmail dot com
Reported by: cdburgess at gmail dot com
Summary: PDOStatement::getColumnMeta returns original table
name from view
-Status: Feedback
+Status: Assigned
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:
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 ;
*/
Previous Comments:
------------------------------------------------------------------------
[2012-04-02 09:17:33] [email protected]
I can't reproduce this. Please provide a reproduce script including table and
view definition and information about the MySQL server version you are using.
------------------------------------------------------------------------
[2012-04-01 03:58:58] cdburgess at gmail dot com
Description:
------------
The data returned from PDOStatement::getColumnMeta is inconsistent when run on
a
MySQL view. It works fine against tables, but in some cases when run against a
View in MySQL, it returns the original table name from the table that was used
to
create the view.
Expected result:
----------------
I would expect the Table name being returned to be the name of the View, not
the
original table the view is created from.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=61588&edit=1
