[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL query

Posted on 2014-08-17
3
Medium Priority
?
575 Views
Last Modified: 2014-08-26
Hi All,

I am not able to create Table Partition on MySQL DB because of below error. Please advise how to fix below error.

Error: "SQL Error (1054): Unknown column 'xUPD_A50000' in 'partition function' "

Table structure and Partition script:
CREATE TABLE `Temp_T` (
	`ID` VARCHAR(20) NOT NULL,
	`DATE_WORKED` VARCHAR(20) NOT NULL,
	`ACTUAL_DATE_ENTERED` VARCHAR(20) NULL DEFAULT NULL,
	PRIMARY KEY (`ID`)
)
PARTITION BY RANGE(ID) (
        PARTITION p0 VALUES LESS THAN (xUPD_A50000), 
        PARTITION p1 VALUES LESS THAN (xUPD_A100000), 
        PARTITION p2 VALUES LESS THAN (xUPD_A150000), 
       PARTITION p3 VALUES LESS THAN (xUPD_B50000), 
        PARTITION p4 VALUES LESS THAN (xUPD_B100000), 
        PARTITION p5 VALUES LESS THAN (xUPD_B150000), 
        PARTITION p6 VALUES LESS THAN (MAXVALUE)
);

Sample data:
ID                                     DATE_WORKED                               ACTUAL_DATE_ENTERED
===============================================================
xUPD_A50000               2013-01-01                                            2013-06-01
xUPD_A51000                2013-01-01                                           2014-09-06
xUPD_B80000               2003-07-03                                             2011-05-06
xUPD_B150000             2004-07-01                                              2002-03-01

Open in new window

0
Comment
Question by:sqldba2013
3 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 750 total points
ID: 40267019
You need to enclose the string literal values in apostrophe:
CREATE TABLE `Temp_T` (
	`ID` VARCHAR(20) NOT NULL,
	`DATE_WORKED` VARCHAR(20) NOT NULL,
	`ACTUAL_DATE_ENTERED` VARCHAR(20) NULL DEFAULT NULL,
	PRIMARY KEY (`ID`)
)
PARTITION BY RANGE(ID) (
        PARTITION p0 VALUES LESS THAN ('xUPD_A50000'), 
        PARTITION p1 VALUES LESS THAN ('xUPD_A100000'), 
        PARTITION p2 VALUES LESS THAN ('xUPD_A150000'), 
       PARTITION p3 VALUES LESS THAN ('xUPD_B50000'), 
        PARTITION p4 VALUES LESS THAN ('xUPD_B100000'), 
        PARTITION p5 VALUES LESS THAN ('xUPD_B150000'), 
        PARTITION p6 VALUES LESS THAN ('MAXVALUE')
);

Open in new window

0
 
LVL 3

Accepted Solution

by:
stevejacob68 earned 750 total points
ID: 40267372
Hi,

Always add string in apostrophe. If user does not define string in apostrophe, then code could result in error. Same problem is occurred in your database.
0
 

Author Closing Comment

by:sqldba2013
ID: 40285338
Thanks for your suggestions and I have created Table Partition on my environment as per my requirement.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question