Colin, Where can I read about the impact of having Primary Keys as VARCHAR? This entails an 8 times overhead in Sybase, Oracle, and Microsoft for changing PK values. It must rebuild the index versus and "update-in-place."
Buff Smith, Maestro THE BLUES BUFFET BAND 310 280-3593 http://BluesBuffetBand.com (AKA) [EMAIL PROTECTED] -----Original Message----- From: Colin Faber [SMTP:[EMAIL PROTECTED]] Sent: Saturday, May 18, 2002 3:46 PM To: [EMAIL PROTECTED] Cc: '[EMAIL PROTECTED]' Subject: Re: apparent bug Hi, this is a feature. see http://www.mysqldeveloper.com/manual/index.pcgi?section=Silent_column_ch anges ;-) Buff Smith wrote: > > 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 -- Colin Faber (303) 736-5160 fpsn.net, Inc. --------------------------------------------------------------------- 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
