Foreign Key Issue
Hi,
I have the script below to create 5 tables. Three of them create fine but
two return an error of 150 which I understand to be a foreign key issue,
however I can't see anything wrong with the foreign key statements.
Could someone possibly have a look and see if they can identify the issue
please? I think I have been looking at it so long now 'I can't see the
wood for the trees'.
The two tables which fail are `cube_security` and `cube_measures`. Im
running MySQL 5.1.32 on a Windows XP test machine.
SCRIPT--
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `cubedoc` DEFAULT CHARACTER SET latin1 COLLATE
latin1_general_ci ;
SHOW WARNINGS;
USE `cubedoc`;
-- -
-- Table `cubedoc`.`cubes`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
`idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cube_name` CHAR(30) NOT NULL ,
`cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
`Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcubes`) )
ENGINE = InnoDB
COMMENT = 'General Cube Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_dimensions`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
`idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`dimension_name` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
PRIMARY KEY (`idcube_dimensions`) ,
INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
CONSTRAINT `dimensions_cubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Dimension Data ';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_security`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
`idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`dimension` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
`restricted_user_group` CHAR(30) NOT NULL ,
`restriction_details` TEXT NOT NULL ,
PRIMARY KEY (`idcube_security`) ,
INDEX `security_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `security_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Internal Cube Security';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_measures`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
`idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`measure_name` CHAR(30) NOT NULL ,
`measure_source_calculation` VARCHAR(80) NOT NULL ,
PRIMARY KEY (`idcube_measures`) ,
INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `measures_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Measure Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_changelog`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
`idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`change_date` DATE NOT NULL ,
`version` DECIMAL(4,2) NOT NULL ,
`status` ENUM('Development','User Acceptance','Live','Retired') NOT NULL
DEFAULT 'Development' ,
`change_call_work_request_no` INT NOT NULL ,
`change_detail` TEXT NOT NULL ,
`actioned_by` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcube_changelog`) ,
INDEX `changelog_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `changelog_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Changes log';
SHOW WARNINGS;
SET sql_mo...@old_sql_mode;
SET foreign_key_chec...@old_foreign_key_checks;
SET unique_chec...@old_unique_checks;
END SCRIPT--
TIA
John
--
SOLVED: Re: Foreign Key Issue
Sorry all, I was being a dummy! Missed the unsigned attribute off the
foreign key columns on the problem tables.
Regards
Hi,
I have the script below to create 5 tables. Three of them create fine but
two return an error of 150 which I understand to be a foreign key issue,
however I can't see anything wrong with the foreign key statements.
Could someone possibly have a look and see if they can identify the issue
please? I think I have been looking at it so long now 'I can't see the
wood for the trees'.
The two tables which fail are `cube_security` and `cube_measures`. Im
running MySQL 5.1.32 on a Windows XP test machine.
SCRIPT--
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `cubedoc` DEFAULT CHARACTER SET latin1 COLLATE
latin1_general_ci ;
SHOW WARNINGS;
USE `cubedoc`;
-- -
-- Table `cubedoc`.`cubes`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
`idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cube_name` CHAR(30) NOT NULL ,
`cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
`Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcubes`) )
ENGINE = InnoDB
COMMENT = 'General Cube Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_dimensions`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
`idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`dimension_name` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
PRIMARY KEY (`idcube_dimensions`) ,
INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
CONSTRAINT `dimensions_cubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Dimension Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_security`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
`idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`dimension` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
`restricted_user_group` CHAR(30) NOT NULL ,
`restriction_details` TEXT NOT NULL ,
PRIMARY KEY (`idcube_security`) ,
INDEX `security_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `security_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Internal Cube Security';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_measures`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
`idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`measure_name` CHAR(30) NOT NULL ,
`measure_source_calculation` VARCHAR(80) NOT NULL ,
PRIMARY KEY (`idcube_measures`) ,
INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `measures_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Measure Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_changelog`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
`idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`change_date` DATE NOT NULL ,
`version` DECIMAL(4,2) NOT NULL ,
`status` ENUM('Development','User Acceptance','Live','Retired') NOT NULL
DEFAULT 'Development' ,
`change_call_work_request_no` INT NOT NULL ,
`change_detail` TEXT NOT NULL ,
`actioned_by` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcube_changelog`) ,
INDEX `changelog_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `changelog_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
Re: SOLVED: Re: Foreign Key Issue
I had the same problem and was going crazy,
the ket/foreign key fields must be exactly the same.
I 'forward' engineered the database with MySQL Workbench and was almost
posting a bug!
Cheers
Claudio
2009/3/26 John Daisley john.dais...@mypostoffice.co.uk
Sorry all, I was being a dummy! Missed the unsigned attribute off the
foreign key columns on the problem tables.
Regards
Hi,
I have the script below to create 5 tables. Three of them create fine but
two return an error of 150 which I understand to be a foreign key issue,
however I can't see anything wrong with the foreign key statements.
Could someone possibly have a look and see if they can identify the issue
please? I think I have been looking at it so long now 'I can't see the
wood for the trees'.
The two tables which fail are `cube_security` and `cube_measures`. Im
running MySQL 5.1.32 on a Windows XP test machine.
SCRIPT--
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `cubedoc` DEFAULT CHARACTER SET latin1
COLLATE
latin1_general_ci ;
SHOW WARNINGS;
USE `cubedoc`;
-- -
-- Table `cubedoc`.`cubes`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
`idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cube_name` CHAR(30) NOT NULL ,
`cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
`Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcubes`) )
ENGINE = InnoDB
COMMENT = 'General Cube Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_dimensions`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
`idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`dimension_name` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
PRIMARY KEY (`idcube_dimensions`) ,
INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
CONSTRAINT `dimensions_cubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Dimension Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_security`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
`idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`dimension` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
`restricted_user_group` CHAR(30) NOT NULL ,
`restriction_details` TEXT NOT NULL ,
PRIMARY KEY (`idcube_security`) ,
INDEX `security_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `security_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Internal Cube Security';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_measures`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
`idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`measure_name` CHAR(30) NOT NULL ,
`measure_source_calculation` VARCHAR(80) NOT NULL ,
PRIMARY KEY (`idcube_measures`) ,
INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `measures_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Measure Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_changelog`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
`idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`change_date` DATE NOT NULL ,
`version` DECIMAL(4,2) NOT NULL ,
`status` ENUM('Development','User Acceptance','Live','Retired') NOT
NULL
DEFAULT 'Development' ,
A problem relative ibdata1
Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? Thanks for your future suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A problem relative ibdata1
You can't! it is part of the datafiles of InnoDB storage engine! and It contains important data like the information schema. So I would rather ask you, is it abnormally growing or is it just your feeling? You should be more precise to have good answers, for instance how much megabytes per minute is it growing? How many inserts do you have on the tables per second/minute/hour? Ciao! Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? Thanks for your future suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
where is the stored functions STORED?
hi, It seems that the stored functions isn't stored in a seperated file like trigger does. Where are them? If I copy the database directory from a server to another, can I get the same functions on the both servers? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A problem relative ibdata1
Hi Riccardo, I don't think you should need any other storage engine if you need foreign keys. InnoDB is by far the most used, standard, transactional, foreign key-supporting MySQL storage engine. No other valid option comes to my mind now. But please share your needs and let's see if I am wrong. Ciao Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Well, I think it's abnormal because after a few days I can have a ibdata1 of 7-8 GB!!! I don't think it's normal because, if ibdata1 continue is growth... I must be purchase a datacente :P Now I try to retrive a charts of queries... however do you know if there is an engine that support/manage foreign key? I use innodb for this reason.. but the size of ibdata1 is a problem :'( Thanks for ur reply Il giorno 26/mar/09, alle ore 12:30, Claudio Nanni ha scritto: You can't! it is part of the datafiles of InnoDB storage engine! and It contains important data like the information schema. So I would rather ask you, is it abnormally growing or is it just your feeling? You should be more precise to have good answers, for instance how much megabytes per minute is it growing? How many inserts do you have on the tables per second/minute/hour? Ciao! Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? Thanks for your future suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Fwd: A problem relative ibdata1
forgot the list, sorry :) Walter Heck -- Forwarded message -- From: Walter Heck - OlinData.com li...@olindata.com Date: Thu, Mar 26, 2009 at 2:09 PM Subject: Re: A problem relative ibdata1 To: Cc: Riccardo Michele Filippone ethern...@gmail.com Riccardo, if you're ibdata1 file is growing fast, it means you are inserting lots of data into it. It doesn't magically grow out for no reason :) We can't look into your application, so you will have to monitor it yourself. Try enabling the general query log for 1 minute or so and see what kind of queries are running. You will most likely find something you are not expecting. What kind of application are you running MySQL under? If it is a website, is it custombuilt or an off-the-shelf product? Could it be some kind of logging procedure that is going banana's? We need more info to be able to answer your question :) good luck! Walter Heck On Thu, Mar 26, 2009 at 1:29 PM, Claudio Nanni claudio.na...@gmail.com wrote: Hi Riccardo, I don't think you should need any other storage engine if you need foreign keys. InnoDB is by far the most used, standard, transactional, foreign key-supporting MySQL storage engine. No other valid option comes to my mind now. But please share your needs and let's see if I am wrong. Ciao Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Well, I think it's abnormal because after a few days I can have a ibdata1 of 7-8 GB!!! I don't think it's normal because, if ibdata1 continue is growth... I must be purchase a datacente :P Now I try to retrive a charts of queries... however do you know if there is an engine that support/manage foreign key? I use innodb for this reason.. but the size of ibdata1 is a problem :'( Thanks for ur reply Il giorno 26/mar/09, alle ore 12:30, Claudio Nanni ha scritto: You can't! it is part of the datafiles of InnoDB storage engine! and It contains important data like the information schema. So I would rather ask you, is it abnormally growing or is it just your feeling? You should be more precise to have good answers, for instance how much megabytes per minute is it growing? How many inserts do you have on the tables per second/minute/hour? Ciao! Claudio 2009/3/26 Riccardo Michele Filippone ethern...@gmail.com Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? Thanks for your future suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: A problem relative ibdata1
In the last episode (Mar 26), Riccardo Michele Filippone said: Hello! First of all sorry for my terrible english. Well I've a big problem with ibdata1, this damn file increase its size constantly. I tryed to use innodb_file_per_table... but: - MySQL create a lot of ibd files for all DB (correct) - ibdata1 is however created and increase its size... how can I prevent the creation of this file (ibdata1)? The main cause of ibdata1 growth when using innodb_file_per_table is large transactions. InnoDB rollback data is stored in ibdata1. To minimize the size of ibdata1, try to commit frequently when doing table loads or other operations that might change a lot of data in a single transaction. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: where is the stored functions STORED?
In the last episode (Mar 26), Cui Shijun said: It seems that the stored functions isn't stored in a seperated file like trigger does. Where are them? If I copy the database directory from a server to another, can I get the same functions on the both servers? They're stored in the mysql.proc table. If you're only copying one database, it might be easier to use mysqldump --no-create-info --no-data --routines mydb , which will give you the commands to recreate the stored procedures for the mydb database. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: where is the stored functions STORED?
The table mysql.proc always contains the hard code of stored procedures and functions. Triggers in 5.0 are stored in a .TRG file Make sure, if you used mysqldump, to include --triggers as a dump option Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Cui Shijun [mailto:rancp...@gmail.com] Sent: Thursday, March 26, 2009 7:44 AM To: mysql@lists.mysql.com Subject: where is the stored functions STORED? hi, It seems that the stored functions isn't stored in a seperated file like trigger does. Where are them? If I copy the database directory from a server to another, can I get the same functions on the both servers? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] Alpha release v05.08 of the BLOB Streaming Engine
Hi, Alpha version 5.08 of the BLOB streaming engine for MySQL has been released. The BLOB Streaming Engine is a MySQL storage engine which enables the storage and streaming of BLOB data directly in and out of a MySQL database. You can download the source code from http://www.blobstreaming.org/download. For more details see: http://www.blobstreaming.org/documentation Some of the new features in 5.08: * It is now possible to store user defined metadata with the BLOB. * It is now possible to assign an alias to the BLOB and then use the alias to fetch the BLOB back from the engine. For a full list of changes please have a look at my BLOG posting http://bpbdev.blogspot.com/ If you have any questions or comments feel free send them directly to me. Barry - Barry Leslie SNAP Innovation Softwareentwicklung GmbH Senior Software Engineer Tel: (001) 250 595 4228 Fax: (001) 250 595 4233 Email: barry.les...@primebase.com Web: www.PrimeBase.com SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg, Max-Brauer-Allee 50, Germany Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul McCullagh - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication Issues
We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop
Re: Replication Issues
Most likely something is wrong in your AMSCD2-relay-bin.index file. Check this out: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 Walter Heck On Thu, Mar 26, 2009 at 10:02 PM, Dirk Bremer dirk.bre...@nisc.coop wrote: Could not find first log file name in binary log index file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication Issues
Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication Issues
On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication Issues
I notice in the startup messages there is an underscore in the file names, but there are dashes in the dir list you provided. ??? On 3/26/09, Dirk Bremer dirk.bre...@nisc.coop wrote: On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
