pt-upgrade v. 2.0.3 fails to execute SELECT query when it contains duplicate labels

Reported by Jaime Sicam on 2012-02-28
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Status tracked in 2.2
2.1
Undecided
Unassigned
2.2
Undecided
Unassigned

Bug Description

Below, is the a test case of running: SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1

./pt-upgrade -h localhost D=employees --query="SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1"

# Query 1: ID 0x02A224E9A90784F3 at byte 0 _______________________________
# Found 0 differences in 1 samples:
# checksums 0
# column counts 0
# column types 0
# query times 0
# row counts 0
# warning counts 0
# warning levels 0
# warnings 0
# localhost:3306
# Errors 1
# Warnings 1
# Query_time
# sum 0
# min 0
# max 0
# avg 0
# pct_95 0
# stddev 0
# median 0
# row_count
# sum 0
# min 0
# max 0
# avg 0
# pct_95 0
# stddev 0
# median 0
use `employees`;
SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1
/* 02A224E9A90784F3-1 */ SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1

Jaime Sicam (jssicam) wrote :
Jaime Sicam (jssicam) wrote :

My apologies. The description is not complete. This is the output of pt-upgrade:

use `employees`;
SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1
/* 02A224E9A90784F3-1 */ SELECT first_name as 'Name', last_name as 'Name' FROM employees.employees LIMIT 1

# Errors
# Query ID Host Error
# ================== ============== ==========================================
# 02A224E9A90784F3-1 localhost:3306 Failed to execute query: DBD::mysql::db do failed: Duplicate column name 'Name' at ./pt-upgrade line 8945.

# Statistics
# CompareResults_after_execute_skipped 1
# CompareResults_execute_error 1
# events 1

tags: added: pt-upgrade
Baron Schwartz (baron-xaprb) wrote :

What's happening here is that pt-upgrade is trying to create a temporary table with the SELECT:

CREATE TEMPORARY TABLE ... AS SELECT ...

MySQL will allow you to write a SELECT statement with duplicate columns, but not create a table with duplicates. The best way to handle this is to fix the original statement, because it is likely to cause all kinds of problems, now or in the future. For example, imagine if MySQL gets more strict in the future? Or, what if there is some code that fetches each row and puts it into a hash table or dictionary, and then accesses the 'Name' column?

Baron Schwartz (baron-xaprb) wrote :

This is one of those thigns that we should trap and print out a nice error message with a knowledgebase link. I'll add a KB tag.

tags: added: kb-link
Changed in percona-toolkit:
status: New → Triaged
importance: Undecided → Wishlist
Daniel Nichter (daniel-nichter) wrote :

pt-upgrade was rewritten for 2.2 and I've tested it and this bug no longer affects 2.2. We're not going to fix 2.1.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers