Description:
Author NOTE: Common occurence if migrating from Oracle data-warehouses to MySQL data warehouses where a row-schema is used that appears to be outrageous, and has many COLUMNS.
Fault tested and found on following platforms. Appears to be MYSQL CLI-CLIENT
application related. Systems Tested include:
LinuxIntel/Win32/Linux390 Mysql servers 3.2x.yy and higher. (not specific to server) Linux390/LinuxPPC/Linux68k/OpenBSD (Compiled from source tarball) 4.x and higher packages.
Using 'mysql --user=myuser --password=mypass <MY_SQL_LDR_SCR.sql'
the Error:
ERROR 1064 at line 14: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server ver
sion for the right syntax to use near 'USR_DHCMA_ADJUST_CODE CHAR(2) DEFAULT '',
How to repeat:
file: MY_SQL_LDR_SCR.sql ***
-- This snapshot is a derivation of Prior Work
-- All rights reserved. All changes are reflective of providing
-- Technical assistance. Not for commercial implementation.
--
-- MySQL dump/load 9.07
--
-- Host: localhost Database: MYTSTDB
---------------------------------------------------------
-- Server version 4.00.00
CREATE DATABASE MYTSTDB;
USE MYTSTDB;
--
-- Table structure for table 'TBL_TST'
--
CREATE TABLE TBL_FST
(
USR_EMP_ID CHAR(9) DEFAULT '',
USR_PYRLL_UNIT CHAR(9) DEFAULT '',
USR_NAME CHAR(30) DEFAULT '',
USR_CHANGED CHAR(1) DEFAULT '',
USR_PYRLL_UNIT_2 CHAR(9) DEFAULT '',
USR_LAST_NAME CHAR(30) DEFAULT '',
USR_FIRST_NAME CHAR(15) DEFAULT '',
USR_MIDDLE_INITIAL CHAR(15) DEFAULT '',
USR_PAYROLL_CALC_SEQ CHAR(1) DEFAULT '',
USR_SOCIAL_INSURANCE_NO CHAR(9) DEFAULT '',
USR_CHECK_NAME CHAR(30) DEFAULT '',
USR_ADDRESS_1 CHAR(30) DEFAULT '',
USR_ADDRESS_2 CHAR(30) DEFAULT '',
USR_ADDRESS_3 CHAR(30) DEFAULT '',
USR_CITY CHAR(17) DEFAULT '',
USR_PROV CHAR(2) DEFAULT '',
USR_POSTCODE CHAR(10) DEFAULT '',
USR_HOME_AREA_CODE CHAR(3) DEFAULT '',
USR_HOME_PHONE CHAR(8) DEFAULT '',
USR_OFFICE_AREA_CODE CHAR(3) DEFAULT '',
USR_OFFICE_PHONE CHAR(8) DEFAULT '',
USR_OFFICE_EXTENSION CHAR(4) DEFAULT '',
USR_FORMER_NAME CHAR(30) DEFAULT '',
USR_EMER_NAME CHAR(30) DEFAULT '',
USR_EMER_ADDRESS_1 CHAR(30) DEFAULT '',
USR_EMER_ADDRESS_2 CHAR(30) DEFAULT '',
USR_EMER_ADDRESS_3 CHAR(30) DEFAULT '',
USR_EMER_CITY CHAR(17) DEFAULT '',
USR_EMER_PROV CHAR(2) DEFAULT '',
USR_EMER_POSTCODE CHAR(10) DEFAULT '',
USR_EMER_1_AREA_CODE CHAR(3) DEFAULT '',
USR_EMER_1_PHONE CHAR(8) DEFAULT '',
USR_EMER_1_EXTENSION CHAR(4) DEFAULT '',
USR_EMER_2_AREA_CODE CHAR(3) DEFAULT '',
USR_EMER_2_PHONE CHAR(8) DEFAULT '',
USR_EMER_2_EXTENSION CHAR(4) DEFAULT '',
USR_SEX CHAR(1) DEFAULT '',
USR_FULL_PART_RETIRED CHAR(2) DEFAULT '',
USR_BENEFIT_STATUS CHAR(2) DEFAULT '',
USR_SALARIED_HOURLY CHAR(1) DEFAULT '',
USR_PAYROLL_GROUP CHAR(4) DEFAULT '',
USR_EMPLOYEE_STATUS CHAR(2) DEFAULT 'AC',
USR_BENEFIT_CLASS CHAR(5) DEFAULT '',
USR_BENEFIT_DATE CHAR(8) DEFAULT '',
USR_HIRE_DATE CHAR(8) DEFAULT '',
USR_REHIRE_DATE CHAR(8) DEFAULT '',
USR_ADJUSTED_HIRE_DATE CHAR(8) DEFAULT '',
USR_BIRTH_DATE CHAR(8) DEFAULT '',
USR_ETHNIC_CODE CHAR(2) DEFAULT '',
USR_OFFICER CHAR(1) DEFAULT '',
USR_TERMINATION_DATE CHAR(8) DEFAULT '',
USR_TERMINATION_CODE CHAR(5) DEFAULT '',
USR_TERMINATION_REASON CHAR(30) DEFAULT '',
USR_POSITION_CODE CHAR(6) DEFAULT '',
USR_ORGANIZATION CHAR(9) DEFAULT '',
USR_TITLE CHAR(30) DEFAULT '',
USR_WORK_COMP CHAR(4) DEFAULT '',
USR_UNION CHAR(2) DEFAULT '',
USR_CITIZENSHIP CHAR(2) DEFAULT '',
USR_MILITARY CHAR(4) DEFAULT '',
USR_PAYROLL_CYCLE CHAR(1) DEFAULT '',
USR_SHIFT CHAR(1) DEFAULT '',
USR_HAS_COST_CENTER CHAR(1) DEFAULT 'N',
USR_VBT_FLAG CHAR(1) DEFAULT '',
USR_VBT_DEDUCTION_FLAG CHAR(1) DEFAULT '',
USR_VBT_TRANSIT_ROUTING_NO CHAR(9) DEFAULT '',
USR_VBT_ACCOUNT_NO CHAR(17) DEFAULT '',
USR_MINIMUM_CHECK NUMERIC(11,2) DEFAULT 0,
USR_VBT_PRENOTE_DATE CHAR(8) DEFAULT '',
USR_LAST_PAID_DATE CHAR(8) DEFAULT '',
USR_ANNUAL_PAY NUMERIC(11,2) DEFAULT 0,
USR_REGULAR_PAY_CODE CHAR(8) DEFAULT '',
USR_SALARY_RATE NUMERIC(11,4) DEFAULT 0,
USR_SALARY_RATE_CHANGE_DATE CHAR(8) DEFAULT '',
USR_SALARY_RATE_CHANGE_AMOUNT NUMERIC(11,4) DEFAULT 0,
USR_SALARY_RATE_CHANGE_CODE CHAR(5) DEFAULT '',
USR_MARRIED_SINGLE CHAR(1) DEFAULT '',
USR_RETURN_DATE CHAR(8) DEFAULT '',
USR_TRANSFER_DATE CHAR(8) DEFAULT '',
USR_PRINT_R9_FLAG CHAR(1) DEFAULT '',
USR_PENSION_PLAN CHAR(1) DEFAULT '',
USR_TAX_GROUP CHAR(4) DEFAULT '',
USR_VETERAN_CODE CHAR(6) DEFAULT '',
USR_HANDICAP_CODE CHAR(6) DEFAULT '',
USR_CCRA_TYPE CHAR(6) DEFAULT '',
USR_CCRA_EXPIRATION_DATE CHAR(8) DEFAULT '',
USR_CCRA_ID CHAR(12) DEFAULT '',
USR_CLEARANCE_TYPE CHAR(6) DEFAULT '',
USR_CLEARANCE_NUMBER CHAR(12) DEFAULT '',
USR_DRIVERS_LICENSE_TYPE CHAR(6) DEFAULT '',
USR_DRIVERS_LICENSE_NO CHAR(12) DEFAULT '',
USR_DRIVERS_LICENSE_PROV CHAR(2) DEFAULT '',
USR_BOND_TYPE CHAR(6) DEFAULT '',
USR_BOND_NUMBER CHAR(12) DEFAULT '',
USR_SENIORITY_DATE CHAR(8) DEFAULT '',
USR_SERVICE_YEARS NUMERIC(2) DEFAULT 0,
USR_EDUCATION_YEARS NUMERIC(2) DEFAULT 0,
USR_LOCATION CHAR(6) DEFAULT '',
USR_REMINDER_CODE_1 CHAR(5) DEFAULT '',
USR_REMINDER_DATE_1 CHAR(8) DEFAULT '',
USR_REMINDER_CODE_2 CHAR(5) DEFAULT '',
USR_REMINDER_DATE_2 CHAR(8) DEFAULT '',
USR_REMINDER_CODE_3 CHAR(5) DEFAULT '',
USR_REMINDER_DATE_3 CHAR(8) DEFAULT '',
USR_REMINDER_CODE_4 CHAR(5) DEFAULT '',
USR_REMINDER_DATE_4 CHAR(8) DEFAULT '',
USR_REMINDER_CODE_5 CHAR(5) DEFAULT '',
USR_REMINDER_DATE_5 CHAR(8) DEFAULT '',
USR_REMINDER_CODE_6 CHAR(5) DEFAULT '',
USR_REMINDER_DATE_6 CHAR(8) DEFAULT '',
USR_RRMC_CODE CHAR(4) DEFAULT '',
USR_DATE_LAST_CHANGE CHAR(8) DEFAULT '',
USR_TIME_LAST_CHANGED CHAR(6) DEFAULT '',
USR_LOCKED CHAR(8) DEFAULT 'N',
USR_LAST_STOP_DATE CHAR(8) DEFAULT '',
USR_TTF_CHANGED CHAR(1) DEFAULT 'N',
USR_GGT_CHANGED CHAR(1) DEFAULT 'N',
USR_SERVICE_MONTHS NUMERIC(4) DEFAULT 0,
USR_GRADE CHAR(4) DEFAULT '',
USR_VARIABLE_1 NUMERIC(11,2) DEFAULT 0,
USR_VARIABLE_2 NUMERIC(11,2) DEFAULT 0,
USR_VARIABLE_3 CHAR(6) DEFAULT '',
USR_VARIABLE_4 CHAR(6) DEFAULT '',
USR_VARIABLE_5 CHAR(8) DEFAULT '',
USR_VARIABLE_6 CHAR(8) DEFAULT '',
USR_VARIABLE_7 CHAR(8) DEFAULT '',
USR_VARIABLE_8 CHAR(8) DEFAULT '',
USR_STANDARD_HOURS NUMERIC(7,4) DEFAULT 0,
USR_SCHEDULE CHAR(10) DEFAULT '',
USR_COUNTRY CHAR(3) DEFAULT '',
USR_PENSION CHAR(6) DEFAULT '',
USR_HIGHLY_COMPENSATED CHAR(1) DEFAULT 'N',
USR_USER_STATUS CHAR(6) DEFAULT '',
USR_USER_DEFINED_AREA_1 CHAR(80) DEFAULT '',
USR_USER_DEFINED_AREA_2 CHAR(80) DEFAULT '',
USR_MNGR_ID CHAR(9) DEFAULT '',
USR_SALARY_RATE_FREQ CHAR(1) DEFAULT '',
USR_NEXT_REVIEW_DATE CHAR(8) DEFAULT '',
USR_LAST_STATUS_CODE CHAR(5) DEFAULT '',
USR_LAST_STATUS_DATE CHAR(8) DEFAULT '',
USR_LAST_STATUS_REASON CHAR(30) DEFAULT '',
USR_CCRA_TYPE_2 CHAR(6) DEFAULT '',
USR_CCRA_EXPIRATION_2 CHAR(8) DEFAULT '',
USR_CCRA_ID_2 CHAR(12) DEFAULT '',
USR_ORGANIZATION_CHANGE_CODE CHAR(5) DEFAULT '',
USR_ORGANIZATION_CHANGE_DATE CHAR(8) DEFAULT '',
USR_POSITION_CHANGE_CODE CHAR(5) DEFAULT '',
USR_POSITION_CHANGE_DATE CHAR(8) DEFAULT '',
USR_CHANGE_DATE CHAR(8) DEFAULT '',
USR_SPOUSE_LAST_NAME CHAR(30) DEFAULT '',
USR_SPOUSE_FIRST_NAME CHAR(15) DEFAULT '',
USR_SPOUSE_MIDDLE_NAME CHAR(15) DEFAULT '',
USR_SPOUSE_NAME CHAR(30) DEFAULT '',
USR_EMER_RELATIONSHIP CHAR(14) DEFAULT '',
USR_CONSIDER_FOR_REHIRE CHAR(1) DEFAULT 'Y',
USR_HRIS_EXPANSION_AREA CHAR(50) DEFAULT '',
USR_PROCESS CHAR(1) DEFAULT '',
USR_HR_ORG_UNIT CHAR(40) DEFAULT '',
USR_PIN_NUMBER CHAR(4) DEFAULT '',
USR_DHCMA_ADJUST_CODE CHAR(2) DEFAULT '',
USR_DHCMA_ADJUST_REASON CHAR(30) DEFAULT '',
USR_MAIL_ADDR_1 CHAR(30) DEFAULT '',
USR_MAIL_ADDR_2 CHAR(30) DEFAULT '',
USR_MAIL_ADDR_3 CHAR(30) DEFAULT '',
USR_MAIL_CITY CHAR(17) DEFAULT '',
USR_MAIL_PROV CHAR(2) DEFAULT '',
USR_MAIL_POSTAL CHAR(10) DEFAULT '',
USR_MAIL_COUNTRY CHAR(3) DEFAULT '',
USR_VIEW_LANG_PREF CHAR(3) DEFAULT '',
USR_DOC_LANG_PREF CHAR(3) DEFAULT '',
USR_FAX_AREA_CODE CHAR(3) DEFAULT '',
USR_FAX_PHONE CHAR(8) DEFAULT '',
USR_CELL_AREA_CODE CHAR(3) DEFAULT '',
USR_CELL_PHONE CHAR(8) DEFAULT '',
USR_PAGER_AREA_CODE CHAR(3) DEFAULT '',
USR_PAGER_PHONE CHAR(8) DEFAULT '',
USR_OTHER_AREA_CODE CHAR(3) DEFAULT '',
USR_OTHER_PHONE CHAR(8) DEFAULT '',
USR_EMAIL_ADDRESS CHAR(40) DEFAULT '',
USR_HOME_COUNTY CHAR(16) DEFAULT '',
USR_TRUE_MARITAL_STATUS CHAR(1) DEFAULT '',
USR_NICKNAME CHAR(16) DEFAULT '',
USR_PASSWORD CHAR(14) DEFAULT '',
USR_NAME_TITLE CHAR(6) DEFAULT '',
USR_SPOUSE_BIRTH_DATE CHAR(8) DEFAULT '',
USR_NUM_DEPENDENTS NUMERIC(2) DEFAULT 0
USR_DHCMA_ADJUST_CODE CHAR(2) DEFAULT '',
USR_DHCMA_ADJUST_REASON CHAR(30) DEFAULT '',
USR_DOC_LOCALE_PREF CHAR(5) DEFAULT '',
USR_EMER_COUNTRY CHAR(3) DEFAULT '',
USR_SMOKER CHAR(2) DEFAULT '',
USR_SPOUSE_SMOKER CHAR(2) DEFAULT '',
USR_OFFICE_ADDRESS_1 CHAR(30) DEFAULT '',
USR_OFFICE_ADDRESS_2 CHAR(30) DEFAULT '',
USR_OFFICE_ADDRESS_3 CHAR(30) DEFAULT '',
USR_OFFICE_ADDRESS_4 CHAR(30) DEFAULT '',
USR_OFFICE_CITY CHAR(30) DEFAULT '',
USR_OFFICE_PROV_PROV CHAR(2) DEFAULT '',
USR_OFFICE_POSTAL CHAR(10) DEFAULT '',
USR_OFFICE_COUNTRY CHAR(3) DEFAULT '',
USR_OFFICE_USE_LOC CHAR(1) DEFAULT 'Y',
USR_OTHER_EXTENSION CHAR(4) DEFAULT '',
USR_PAGER_EXTENSION CHAR(4) DEFAULT '',
USR_EMER_LANGUAGE CHAR(3) DEFAULT '',
USR_SAME_ADDRESSES CHAR(1) DEFAULT 'Y',
USR_NAME_SUFFIX CHAR(3) DEFAULT '',
USR_EMER_EMAIL_ADDRESS CHAR(64) DEFAULT '',
USR_PREFERRED_GIVEN_NAME CHAR(15) DEFAULT '',
USR_LEGAL_NAME CHAR(50) DEFAULT '',
USR_EMER_SAME_ADDRESSES CHAR(1) DEFAULT 'Y',
USR_DHCMA_NORMAL_RET_DATE CHAR(8) DEFAULT '',
USR_DHCMA_3070_RET_DATE CHAR(8) DEFAULT '',
USR_DHCMA_33AFC_RET_DATE CHAR(8) DEFAULT '',
USR_DHCMA_YEARS_CAAT_SERV CHAR(3) DEFAULT '',
USR_DHCMA_RRDT_SERV_DATE CHAR(8) DEFAULT '',
);
file: MY_SQL_LDR_SCR.sql ***
Use 'mysql --user=myuser --password=mypass <MY_SQL_LDR_SCR.sql'
And the 1064 Error should appear at the respective line where the Operating System and compile build specific point is reached.
Suggested fix:
NOTE: The Syntax for all of the above was tested rigorously for correct-ness, this is NOT a SYNTAX problem, but a SCRIPT feeding problem into the MYSQL-CLIENT used in the COMMAND-LINE and buffer related issues from first approximations.
This appears to be related to the "Quantity" of statements for COLUMN descriptions for the CREATE TABLE statement in the .SQL script file.
Each platform errors CONSISTENTLY at the same quantity point.
WORKAROUND:
The ALTER TABLE syntax is not limited for use to add the COLUMNS needed that the CLIENT .SQL script cannot load.
In fact the .SQL file quantity of statements used in the command-line script is the limiting factor, and is not server related. And also leads to a similar error if the "ALTER TABLE" scripts are not distributed accordingly.
When using 'mysql --user=myuser --password=mypass <MY_SQL_LDR_SCR.sql' type of inline script statements, limit the statements in the .SQL file to ~15 less than what the error stops at: In the above example I would use a .SQL script with only 170 Lines of SQL statements.
file: MY_SQL_LDR_SCR.sql ***
USE MYTSTDB;
CREATE TABLE MYHUGE_COLUMN_SETS
(
MY_TABLE_NAME_000 NUMERIC(2) DEFAULT 0
);
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_001 NUMERIC(2) DEFAULT 0;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_002 NUMERIC(2) DEFAULT 0;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_003 NUMERIC(2) DEFAULT 0;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_004 NUMERIC(2) DEFAULT 0;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_005 NUMERIC(2) DEFAULT 0;
.
.
.
.
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_170 NUMERIC(2) DEFAULT 0;
file: MY_SQL_LDR_SCR.sql ***
file: MY_SQL_LDR_SCR2.sql ***
USE MYTSTDB;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_171 NUMERIC(2) DEFAULT 0;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_172 NUMERIC(2) DEFAULT 0;
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_173 NUMERIC(2) DEFAULT 0;
.
.
.
.
.
ALTER TABLE MYHUGE_COLUMN_SETS ADD MY_TABLE_NAME_215 NUMERIC(2) DEFAULT 0;
file: MY_SQL_LDR_SCR2.sql ***
Then use a script or batch file with:
mysql --user=myuser --password=mypass <MY_SQL_LDR_SCR.sql
mysql --user=myuser --password=mypass <MY_SQL_LDR_SCR2.sql
In the batch to create the tables.
