MySQL Alter Column causes Error 1205 and error 2046

Question

I'm using MySQL Workbench and trying to alter a column name. The column is part of many foreign keys.

I'm thinking that's what's causing the error. I'm trying to rename strCustomerCode to strCustomerCodeNEW

Code:

ALTER TABLE `Check4It_MainDB`.`tbl_000_010_MAIN_REPORT_INFO` 
CHANGE COLUMN `strCustomerCode` `strCustomerCodeNEW` VARCHAR(255) 
    CHARACTER SET 'utf8' NOT NULL ;

Here is the error:

ERROR 1025: Error on rename of './Check4It_MainDB/#sql-b6a_4a5' to './Check4It_MainDB/tbl_000_010_MAIN_REPORT_INFO' (errno: 150)

SQL Statement:

ALTER TABLE `Check4It_MainDB`.`tbl_000_010_MAIN_REPORT_INFO` 
CHANGE COLUMN `strCustomerCode` `strCustomerCodeNEW` VARCHAR(255) 
    CHARACTER SET 'utf8' NOT NULL

ERROR: Error when running failback script. Details follow
ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `tbl_000_010_MAIN_REPORT_INFO` (
    `strCustomerCode` varchar(255) CHARACTER SET utf8 NOT NULL'
    `strPasscodeAdmin` varchar(255) CHARACTER SET utf8 NOT NULL,
    `strPasscodeClient` varchar(255) CHARACTER SET utf8 NOT NULL,
    `idMainReport_ID` int(11) NOT NULL AUTO_INCREMENT,
    `lngProcedure_ID` int(11) DEFAULT NULL,
    `strHardDriveID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    `lngInspector_01_ID` int(11) DEFAULT NULL,
    `lngInspector_02_ID` int(11) DEFAULT NULL,
    `lngShift_ID` int(11) DEFAULT NULL,
    `lngUnit_ID` int(11) DEFAULT NULL,
    `dtmReportCreated` datetime DEFAULT NULL,
    `dtmReportSubmitted` datetime DEFAULT NULL,
    `bolCompleted` bit(1) DEFAULT b'0',
    `memReportComments` longtext CHARACTER SET utf8,
    `strTextMessage` varchar(160) CHARACTER SET utf8 DEFAULT NULL,
    `bolTextMessageOn` bit(1) DEFAULT b'0',
    `bolTextSent` bit(1) DEFAULT b'0',
    `bolReportEmailSent` bit(1) DEFAULT b'0',
    `bolCompletionEmailSent` bit(1) DEFAULT b'0',
    `bolReportPostedToCloud` bit(1) DEFAULT b'0',
    `strComputerName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    `strUserLogin` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    `strUserVersionNUmber` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    `bolImportedReport` bit(1) DEFAULT NULL,
    `bolTestReport` bit(1) DEFAULT b'0',
    `strOSVersion` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    `strWindowsVersion` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`idMainReport_ID`, `strCustomerCode`, `strPasscodeAdmin`, `strPasscodeClient`),

KEY `idMainTest_ID1` (`lngProcedure_ID`),
KEY `idProcedure_ID` (`idMainReport_ID`),
KEY `lngMemebrInfoID` (`lngInspector_01_ID`),
KEY `lngShift_ID` (`lngShift_ID`),
KEY `lngUnit_ID` (`lngUnit_ID`),
KEY `strHardDriveID` (`strHardDriveID`),
KEY `strPasscodeClient` (`strPasscodeClient`),
KEY `tbl_000_010_MAIN_REPORT_INFOstrPasscodeAdmin` (`strPasscodeAdmin`),
KEY `tblREF_InspectorInfotbl_000_010_MAIN_REPORT_INFO` (`lngInspector_01_ID`,`strCustomerCode`),
KEY `strPasscodeAdmin` (`strPasscodeAdmin`,`strCustomerCode`,`lngProcedure_ID`),
KEY `idMainReport_ID` (`idMainReport_ID`,`strPasscodeAdmin`,`strCustomerCode`,`strPasscodeClient`),
KEY `strCustomerCode` (`strCustomerCode`,`lngInspector_02_ID`),
KEY `lngShift_ID_2` (`lngShift_ID`,`strCustomerCode`),
KEY `strCustomerCode_2` (`strCustomerCode`,`lngUnit_ID`),

CONSTRAINT `FK_WithShiftID` FOREIGN KEY (`lngShift_ID`) REFERENCES `tblREF_ShiftID` (`idShift_ID`) 
    ON DELETE CASCADE ON UPDATE CASCADE) 
ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=latin1

So I'm going to assume that the out of place `'` on the second line of your `create` statement is a typo. — gunr2171, Sep 19 '13 at 17:05

score 0 · Answer 1 · answered Sep 19 '13 at 17:12

0

Your error says

ERROR 1046: No database selected

This is because no database was selected. How is the sql server supposed to know what database to look for the table Check4It_MainDB?

To do this in the workbench, right-click a database and choose Set As Default Schema. This will now make any script that does not explicitly give a database to use your indicated database.

The better way is to add this to the top of all your scripts:

use MyDatabaseName;

Any code executed after that will use that database, unless again explicitly defined.

answered Sep 19 '13 at 17:12

gunr2171

16,104
25
61
88

Well, I was able to run the ALTER on other tables that had no foreign keys. Only those with foreign keys had a problem. I already had the default database set and used a new SQL Tab (Upper left menu icon) in MySQL. Maybe the issue was due to the way the FK was trying to be altered with the new column name and not having the explicit DB name. The way I fixed it was do do a dump of the whole DB (it's not very large), did a global replace in the script of the column name and reloaded it. That fixed it. – John Skolits Sep 19 '13 at 18:38

MySQL Alter Column causes Error 1205 and error 2046

1 Answers1