Innodb problem 1

[sipps]

Hi,

I am creating tables with foreign keys and the Innodb function. The tables get created fine, but when I do a show table status; all of them are still MyISAM.

The database schema is as follows:

CREATE TABLE booking (
Booking_ID int(11) NOT NULL auto_increment,
Customer_ID int(11) NOT NULL default '0',
Vehicle_Group_ID int(11) NOT NULL default '0',
Pick_Up_Location int(11) NOT NULL default '0',
Pick_Up_Date date NOT NULL default '0000-00-00',
Pick_Up_Time time NOT NULL default '00:00:00',
Pick_Up_Mileage int(6) default NULL,
Drop_Off_Location int(11) NOT NULL default '0',
Drop_Off_Date date NOT NULL default '0000-00-00',
Drop_Off_Time time NOT NULL default '00:00:00',
Drop_Off_Mileage int(6) default NULL,
Booking_Date date default NULL,
Credit_Card_Number int(16) NOT NULL default '0',
Expiry_Date varchar(5) NOT NULL default '',
Total_Amount float default NULL,
Vehicle_Registration varchar(7) NOT NULL default '',
PRIMARY KEY (Booking_ID),
KEY Customer_ID (Customer_ID),
KEY Vehicle_Group_ID (Vehicle_Group_ID),
KEY Pick_Up_Location (Pick_Up_Location),
KEY Drop_Off_Location (Drop_Off_Location),
KEY Vehicle_Registration (Vehicle_Registration),
FOREIGN KEY (`Customer_ID`) REFERENCES `customer` (`Customer_ID`),
FOREIGN KEY (`Vehicle_Group_ID`) REFERENCES `vehicle_group` (`Vehicle_Group_ID`),
FOREIGN KEY (`Vehicle_Registration`) REFERENCES `vehicle` (`Vehicle_Registration`),
FOREIGN KEY (`Pick_Up_Location`) REFERENCES `location` (`Location_ID`),
FOREIGN KEY (`Drop_Off_Location`) REFERENCES `location` (`Location_ID`)
) TYPE=InnoDB;

CREATE TABLE customer (
Customer_ID int(11) NOT NULL auto_increment,
Customer_Category_ID int(11) NOT NULL default '0',
Customer_Title varchar(5) default NULL,
Customer_Fname varchar(10) NOT NULL default '',
Customer_Lname varchar(12) NOT NULL default '',
Customer_Address varchar(80) default NULL,
Customer_Town varchar(20) default NULL,
Customer_County varchar(20) default NULL,
Customer_Postcode varchar(10) default NULL,
Customer_Telephone_Number varchar(15) default NULL,
Customer_Email varchar(35) NOT NULL default '',
Customer_DOB date NOT NULL default '0000-00-00',
Customer_Driving_License varchar(15) NOT NULL default '',
PRIMARY KEY (Customer_ID),
KEY Customer_Category_ID (Customer_Category_ID),
FOREIGN KEY (`Customer_Category_ID`) REFERENCES `customer_category` (`Customer_Category_ID`)
) TYPE=InnoDB;

CREATE TABLE location (
Location_ID int(11) NOT NULL auto_increment,
Location_Name varchar(35) NOT NULL default '',
Location_Address varchar(80) default NULL,
Location_Town varchar(20) default NULL,
Location_County varchar(25) default NULL,
Location_Postcode varchar(10) default NULL,
Location_Telephone_Number varchar(15) default NULL,
Location_Email varchar(35) NOT NULL default '',
PRIMARY KEY (Location_ID)
) TYPE=InnoDB;

etc, all the other tables have the TYPE=InnoDB statement.
I have also read that I can change the default MyISAM to InnoDB by writing a line in the my.cnf file. A problem is that I cannot find a My.cnf on my machine!

I have these tables created fine on one machine, and the InnoDB works fine, but I need them on another, and this is where the problem is.

Please can someone help!

Thanks

 

[piti]

maybe your mysql is not compiled with the innodb table type support or you haven't specified the innodb startup options in the my.cnf file
my.cnf file does not need to be created during the install process, just create it in the /etc dir and insert there what you need
hope that helps

 

[sipps]

I am using Windows, and I am quite new to MySQL, so you'll have to be a bit more precise! Sorry!
I have no etc directory in MySQL, and when I search for my.cnf I get nothing, and this is on both machines. The first machine works great, and Innodb is there and I cannot delete a record when it is referenced by something else so that's great, problem is the other machine is MyISAM and I can't get Innodb in there.

Where should the my.cnf be in windows, and what should be the contents of this file, and how do I know that MySQL is using this file?

Thanks!

 

[piti]

well, i supposed you are runing an *ix system as you were writing about my.cnf, win build of mysql use my.ini file
as i do not use win builds i assume the proper location of my.ini is in the directory you installed mysql into
innodb support is included in the mysql-max binary as it's written in the mysql documentation

http://www.mysql.com/doc/en/InnoDB_overview.html

so you have to run the max binary instead of the one you are running now

and for the purpose of my.cnf (in your case my.ini) read

http://www.mysql.com/doc/en/Option_files.html

 

[sipps]

Thanks piti,

I found the my.ini file, changed it but it still is not working, I am trying to find out why by looking at the differences between the working and non-working machines. I am not sure if I am running the mysql-max binary, but the mysqld shows mysqld-nt, so I guess I'm not. But it's weird how one machine has innodb working, and they both have the same my.ini file. I didn't think that I needed to specify default-table-type=innodb if I put the Type=Innodb on the end of the create table statement.

Cheers

 

[squidster]

Hi I'm fairly new to this as well so I know what you're going through. Hopefully some of this might help.

You definately must be running mysqld-max-nt.exe (assuming win2k +). Mysql will still allow you to create tables specified as Innodb even if you are running a version that is not compatable such as mysqld-nt, it just forgets the foreign keys and makes the table myisam.

mysqld-max-nt.exe should be located in mysql\bin, if you start up via a .bat you need to check this by editing it to see which version of mysql its trying to run, it should have a line something like this:-

cd C:\mysql\bin
start mysqld-max-nt.exe --standalone

Your my.cnf or .ini (looks like windows can use either, mines a .cnf and I'm on win2k) usually located in c:\, must have something like the following: -

innodb_data_file_path = ibdata1:100M
innodb_data_home_dir =
innodb_log_group_home_dir = c:/mysql/iblogs
innodb_log_arch_dir = c:/mysql/iblogs
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

if any of these lines have # in front they have been comented out so you need to delete the #.

Good luck!

 

[sipps]

Thanks squidster!

Is up and running now!

Cheers