To Whom It May Concern:
I have reported the following symptoms in UNIX using 'mysqlbug' character
mode utility. I am including the text of that problem statement with this
email. Additionally, I am attaching several sample DDL input files, and
the results of SHOW COLUMNS FROM <table name> output.
Voila, the mysqlbug problem text:
************************************************************************
*******************************************************
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: buff
To: [EMAIL PROTECTED]
Subject: DDL CREATE TABLE data format CHAR(x) compiles as VARCHAR(x)
Description:
My DDL files for CREATE TABLE statements contain no VARCHAR(x) data
formats. However, when compiling all DDL for a given database. For some
reason invisible to me, some of the columns whose data format is CHAR(x)
end up being created as VARCHAR(x). The set of columns that mutate in
this fashion is consistent, i.e., the same set of columns always get this
error. However, the rime or reason for this set seems to be happenstance.
That is: For a given dB name with a parallel set of DDL the same columns
mutate to VARCHAR. In other words: I use DDL set A with dB name 1, DDL
set B with dB name 2, etc., etc.
NOTE: pair A1, B2, etc get consistent within pair, but consistently
different between pair sets of VARCHAR columns.
The string /VARCHAR/ cannot be found via grep in any of my DDL scripts. I
can email or FTP a DDL script.
How-To-Repeat:
I will email or FTP a set of DDL scripts
Fix:
NONE
Submitter-Id: [EMAIL PROTECTED]
Originator: Buff
Organization: cynchrony.com hosted by interland.com
MySQL A.B's customer is Interland
MySQL support: interland.com says -- none
Synopsis: CHAR(X) mutates in DDL compile varchar(x)
Severity: critical
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.22.32 (Source distribution)
Environment: FreeBSD cynchrony.com 4.1.1-STABLE FreeBSD 4.1.1-STABLE #0:
Tue Aug 21 22:35:59i GMT 2001
root@osbuilder:/usr/obj/usr/src/sys/SERVER i386
MySql Version:
+-----------+
| version() |
+-----------+
| 3.22.32 |
+-----------+
************************************************************************
*************************************************
end of mysqlbug problem statement text
In preparing data for this email I noticed that it seems to happen for
columns added after a certain period of time. Originally, the
Question_Asked_Quantitative table did not have the Question_Subscale_Name
column. Generation number 2 DDL version 09 of the trimergence_main
database did not have the column, whereas generation number 3 DDL version
13 of the trimergence_main database added that and other columns.
The current version of mysql is listed in the mysqlbug problem statement
text, above
I do not know which version mysql was when generation 2 of the database was
created.
attachments follow -->
generation 09 show cols ===========================
Field Type Null Key Default Extra
Questionnaire_ID int(11) PRI 0
Question_Number int(11) PRI 0
Question_Subscale_Name varchar(32) PRI
Question_Text text NULL
Generation 09 DDL =================================
use trimergence_main2;
CREATE TABLE Customer_Authentication (
Customer_ID CHAR(30) NOT NULL,
Customer_Name CHAR(128) NOT NULL,
Company_SignUp_date DATE NOT NULL,
Company_Expire_Date DATE NOT NULL,
Customer_Validation_flag CHAR(1) NOT NULL,
Contact_Person CHAR(128) NOT NULL,
Contact_email CHAR(128) NOT NULL,
Customer_address_street CHAR(128),
Customer_address_city CHAR(64),
Customer_address_state_cd CHAR(2),
Contact_area_code CHAR(3) NOT NULL,
Contact_phone CHAR(8) NOT NULL,
Customer_address_zipcode CHAR(10),
Customer_URL_name CHAR(128),
PRIMARY KEY (Customer_ID)
);
CREATE UNIQUE INDEX XPKCustomer_Authentication ON Customer_Authentication
(
Customer_ID ASC
);
CREATE TABLE Question_Asked_Qualitative (
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Asked_Text TEXT NOT NULL,
Question_Answer_Text TEXT NOT NULL,
PRIMARY KEY (Questionnaire_ID, Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Asked_Qualitative ON
Question_Asked_Qualitative
(
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Question_Asked_Quantitative (
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Text TEXT NOT NULL,
PRIMARY KEY (Questionnaire_ID, Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Asked_Quantitative ON
Question_Asked_Quantitative
(
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Question_Qualitative_Answer (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Question_number INTEGER NOT NULL,
Question_Answer_Text TEXT NOT NULL,
Question_Answered_Flag CHAR(1) NOT NULL,
Question_Answer_Merged_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID,
Question_number)
);
CREATE UNIQUE INDEX XPKQuestion_Qualitative_Answer ON
Question_Qualitative_Answer
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC,
Question_number ASC
);
CREATE TABLE Question_Quantitative_Answer (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Answer_Score TINYINT NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID,
Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Quantitative_Answer ON
Question_Quantitative_Answer
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Questionnaire_Form (
Questionnaire_ID INTEGER NOT NULL,
Questionnaire_Name_Brief CHAR(16) NOT NULL,
Questionnaire_Name_Long CHAR(128) NOT NULL,
Question_Count SMALLINT NOT NULL,
Question_Fee_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Questionnaire_ID)
);
CREATE UNIQUE INDEX XPKQuestionnaire_Form ON Questionnaire_Form
(
Questionnaire_ID ASC
);
CREATE TABLE Questionnaire_Status (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Questionnaire_Status SMALLINT NOT NULL,
Quantitative_Sum SMALLINT NOT NULL,
Email_Sent_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID)
);
CREATE UNIQUE INDEX XPKQuestionnaire_Status ON Questionnaire_Status
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC
);
CREATE TABLE User_Authentication (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Password CHAR(16) NOT NULL,
User_Access_Level_Code TINYINT NOT NULL,
User_SignUp_Date DATE NOT NULL,
Password_Reminder CHAR(64) NOT NULL,
User_Expire_Date DATE NOT NULL,
Login_User_Name CHAR(60) NOT NULL,
User_Validated_Flag CHAR(1) NOT NULL,
Email_Address CHAR(128) NOT NULL,
Phone_area_code CHAR(3) NOT NULL,
Phone_number CHAR(8) NOT NULL,
Questionnaire_Count SMALLINT NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID)
);
CREATE UNIQUE INDEX XPKUser_Authentication ON User_Authentication
(
Customer_ID ASC,
Login_ID ASC
);
====================================================
generation 13 show cols
use trimergence_main2;
CREATE TABLE Customer_Authentication (
Customer_ID CHAR(30) NOT NULL,
Customer_Name CHAR(128) NOT NULL,
Company_SignUp_date DATE NOT NULL,
Company_Expire_Date DATE NOT NULL,
Customer_Validation_flag CHAR(1) NOT NULL,
Contact_Person CHAR(128) NOT NULL,
Contact_email CHAR(128) NOT NULL,
Customer_address_street CHAR(128),
Customer_address_city CHAR(64),
Customer_address_state_cd CHAR(2),
Contact_area_code CHAR(3) NOT NULL,
Contact_phone CHAR(8) NOT NULL,
Customer_address_zipcode CHAR(10),
Customer_URL_name CHAR(128),
PRIMARY KEY (Customer_ID)
);
CREATE UNIQUE INDEX XPKCustomer_Authentication ON Customer_Authentication
(
Customer_ID ASC
);
CREATE TABLE Question_Asked_Qualitative (
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Asked_Text TEXT NOT NULL,
Question_Answer_Text TEXT NOT NULL,
PRIMARY KEY (Questionnaire_ID, Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Asked_Qualitative ON
Question_Asked_Qualitative
(
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Question_Asked_Quantitative (
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Text TEXT NOT NULL,
PRIMARY KEY (Questionnaire_ID, Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Asked_Quantitative ON
Question_Asked_Quantitative
(
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Question_Qualitative_Answer (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Question_number INTEGER NOT NULL,
Question_Answer_Text TEXT NOT NULL,
Question_Answered_Flag CHAR(1) NOT NULL,
Question_Answer_Merged_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID,
Question_number)
);
CREATE UNIQUE INDEX XPKQuestion_Qualitative_Answer ON
Question_Qualitative_Answer
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC,
Question_number ASC
);
CREATE TABLE Question_Quantitative_Answer (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Answer_Score TINYINT NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID,
Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Quantitative_Answer ON
Question_Quantitative_Answer
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Questionnaire_Form (
Questionnaire_ID INTEGER NOT NULL,
Questionnaire_Name_Brief CHAR(16) NOT NULL,
Questionnaire_Name_Long CHAR(128) NOT NULL,
Question_Count SMALLINT NOT NULL,
Question_Fee_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Questionnaire_ID)
);
CREATE UNIQUE INDEX XPKQuestionnaire_Form ON Questionnaire_Form
(
Questionnaire_ID ASC
);
CREATE TABLE Questionnaire_Status (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Questionnaire_Status SMALLINT NOT NULL,
Quantitative_Sum SMALLINT NOT NULL,
Email_Sent_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID)
);
CREATE UNIQUE INDEX XPKQuestionnaire_Status ON Questionnaire_Status
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC
);
CREATE TABLE User_Authentication (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Password CHAR(16) NOT NULL,
User_Access_Level_Code TINYINT NOT NULL,
User_SignUp_Date DATE NOT NULL,
Password_Reminder CHAR(64) NOT NULL,
User_Expire_Date DATE NOT NULL,
Login_User_Name CHAR(60) NOT NULL,
User_Validated_Flag CHAR(1) NOT NULL,
Email_Address CHAR(128) NOT NULL,
Phone_area_code CHAR(3) NOT NULL,
Phone_number CHAR(8) NOT NULL,
Questionnaire_Count SMALLINT NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID)
);
CREATE UNIQUE INDEX XPKUser_Authentication ON User_Authentication
(
Customer_ID ASC,
Login_ID ASC
);
================================================
generation 13 DDL
USE trimergence_main4;
CREATE TABLE Customer_Authentication (
Customer_ID CHAR(30) NOT NULL,
Customer_Name CHAR(128) NOT NULL,
Customer_SignUp_date DATE NOT NULL,
Customer_Expire_Date DATE NOT NULL,
Customer_Validation_flag CHAR(1) NOT NULL,
Customer_Contact_Name CHAR(128) NOT NULL,
Customer_Contact_Email CHAR(128) NOT NULL,
Customer_address_street CHAR(128),
Customer_address_city CHAR(64),
Customer_Address_State_Code CHAR(2),
Customer_Address_Zipcode CHAR(10),
Customer_Area_Code CHAR(3) NOT NULL,
Customer_Phone_Number CHAR(8) NOT NULL,
Customer_FAX_Number CHAR(12),
Customer_Country_Code CHAR(3),
Customer_URL_Name CHAR(128),
PRIMARY KEY (Customer_ID)
);
CREATE UNIQUE INDEX XPKCustomer_Authentication ON Customer_Authentication
(
Customer_ID ASC
);
CREATE TABLE Question_Asked_Qualitative (
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Asked_Text TEXT NOT NULL,
Question_Feedback_Text TEXT NOT NULL,
PRIMARY KEY (Questionnaire_ID, Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Asked_Qualitative ON
Question_Asked_Qualitative
(
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Question_Asked_Quantitative (
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Subscale_Name CHAR(32) NOT NULL,
Question_Text TEXT NOT NULL,
PRIMARY KEY (Questionnaire_ID, Question_Number,
Question_Subscale_Name)
);
CREATE UNIQUE INDEX XPKQuestion_Asked_Quantitative ON
Question_Asked_Quantitative
(
Questionnaire_ID ASC,
Question_Number ASC,
Question_Subscale_Name ASC
);
CREATE TABLE Question_Qualitative_Answer (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Answer_Text TEXT NOT NULL,
Question_Answered_Flag CHAR(1) NOT NULL,
Question_Answer_Merged_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID,
Question_Number)
);
CREATE UNIQUE INDEX XPKQuestion_Qualitative_Answer ON
Question_Qualitative_Answer
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC,
Question_Number ASC
);
CREATE TABLE Question_Quantitative_Answer (
Customer_ID CHAR(30) NOT NULL,
Question_Subscale_Grouping_Number SMALLINT NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Question_Number INTEGER NOT NULL,
Question_Subscale_Name CHAR(32) NOT NULL,
Answer_Score TINYINT NOT NULL,
PRIMARY KEY (Customer_ID, Question_Subscale_Grouping_Number,
Login_ID, Questionnaire_ID, Question_Number,
Question_Subscale_Name)
);
CREATE UNIQUE INDEX XPKQuestion_Quantitative_Answer ON
Question_Quantitative_Answer
(
Customer_ID ASC,
Question_Subscale_Grouping_Number ASC,
Login_ID ASC,
Questionnaire_ID ASC,
Question_Number ASC,
Question_Subscale_Name ASC
);
CREATE TABLE Question_Quantitative_Subscale (
Question_Subscale_Grouping_Number SMALLINT NOT NULL,
Question_Subscale_Name CHAR(32) NOT NULL,
Range_Number SMALLINT NOT NULL,
Range_Value_Low_Number SMALLINT NOT NULL,
Range_Value_High_Number SMALLINT NOT NULL,
Question_Subscale_Description TEXT NOT NULL,
PRIMARY KEY (Question_Subscale_Grouping_Number,
Question_Subscale_Name)
);
CREATE UNIQUE INDEX XPKQuestion_Quantitative_Subscale ON
Question_Quantitative_Subscale
(
Question_Subscale_Grouping_Number ASC,
Question_Subscale_Name ASC
);
CREATE TABLE Questionnaire_Form (
Questionnaire_ID INTEGER NOT NULL,
Questionnaire_Name_Brief CHAR(16) NOT NULL,
Questionnaire_Name_Long CHAR(128) NOT NULL,
Question_Count SMALLINT NOT NULL,
Question_Fee_Flag CHAR(1) NOT NULL,
Question_Mandatory_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Questionnaire_ID)
);
CREATE UNIQUE INDEX XPKQuestionnaire_Form ON Questionnaire_Form
(
Questionnaire_ID ASC
);
CREATE TABLE Questionnaire_Status (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Questionnaire_ID INTEGER NOT NULL,
Questionnaire_Status SMALLINT NOT NULL,
Quantitative_Sum SMALLINT NOT NULL,
Email_Sent_Flag CHAR(1) NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID, Questionnaire_ID)
);
CREATE UNIQUE INDEX XPKQuestionnaire_Status ON Questionnaire_Status
(
Customer_ID ASC,
Login_ID ASC,
Questionnaire_ID ASC
);
CREATE TABLE User_Authentication (
Customer_ID CHAR(30) NOT NULL,
Login_ID CHAR(30) NOT NULL,
Password CHAR(16) NOT NULL,
User_Access_Level_Code TINYINT NOT NULL,
User_SignUp_Date DATE NOT NULL,
Password_Reminder CHAR(64) NOT NULL,
User_Expire_Date DATE NOT NULL,
User_Full_Name CHAR(60) NOT NULL,
User_Validated_Flag CHAR(1) NOT NULL,
Email CHAR(128) NOT NULL,
Phone_Area_Code CHAR(3) NOT NULL,
Phone_Number CHAR(8) NOT NULL,
Questionnaire_Count SMALLINT NOT NULL,
PRIMARY KEY (Customer_ID, Login_ID)
);
CREATE UNIQUE INDEX XPKUser_Authentication ON User_Authentication
(
Customer_ID ASC,
Login_ID ASC
);
CREATE TABLE Validation_Codes_Country (
Country_Code CHAR(3) NOT NULL,
Country_Name CHAR(128) NOT NULL,
PRIMARY KEY (Country_Code)
);
CREATE UNIQUE INDEX XPKValidation_Codes_Country ON Validation_Codes_Country
(
Country_Code ASC
);
CREATE TABLE Validation_Codes_State (
State_Code CHAR(2) NOT NULL,
State_Name CHAR(128) NOT NULL,
PRIMARY KEY (State_Code)
);
CREATE UNIQUE INDEX XPKValidation_Codes_State ON Validation_Codes_State
(
State_Code ASC
);
Buff Smith, Project Manager
Trimergence Web Site Project
Cynchron.Com a Customer of Interland.Com the hosting ISP.
310 280-3593
http://www.trimergence.com/
(AKA) [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
