PHP :: Bug #61588 :: PDOStatement::getColumnMeta returns original table name from view

Bug #61588

PDOStatement::getColumnMeta returns original table name from view

Submitted:

2012-04-01 03:58 UTC

Modified:

2012-04-30 15:02 UTC

Votes:	6
Avg. Score:	4.8 ± 0.4
Reproduced:	3 of 3 (100.0%)
Same Version:	3 (100.0%)
Same OS:	3 (100.0%)

From:

cdburgess at gmail dot com

Assigned:

mysql (profile)

Status:

Not a bug

Package:

PDO related

PHP Version:

5.3.10

OS:

Mac OSX

Private report:

CVE-ID:

None

View Developer Edit

[2012-04-01 03:58 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports

[2012-04-02 07:04 UTC] aharvey@php.net

-Assigned To: +Assigned To: mysql

[2012-04-02 09:17 UTC] johannes@php.net

-Status: Assigned +Status: Feedback

[2012-04-02 09:17 UTC] johannes@php.net

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-03 05:57 UTC] 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 ;

*/

[2012-04-03 05:57 UTC] cdburgess at gmail dot com

-Status: Feedback +Status: Assigned

[2012-04-30 15:02 UTC] uw@php.net

-Status: Assigned +Status: Not a bug

[2012-04-30 15:02 UTC] 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`, `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.

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-09-12 21:18 UTC] 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.

[2014-09-10 16:15 UTC] cdburgess at gmail dot com

Here is the existing MySQL bug for this issue: http://bugs.mysql.com/bug.php?id=66794

	php.net \| support \| documentation \| report a bug \| advanced search \| search howto \| statistics \| random bug \| login
go to bug id or search bugs for


Copyright © 2001-2024 The PHP Group All rights reserved.	Last updated: Thu Dec 19 02:01:28 2024 UTC