RE: Adding language support to tables
You want to say either "Germany" or "Deutschland", depending on a "language_code" somewhere? Remove the strings you have in those tables now; add about 4 new tables, each one paralleling the existing tables, but more rows and these columns: * id (the PK of the existing table) * language code (ENUM or TINYINT UNSIGNED indicating the language) * spelling (utf8 version for the language in question) There may be better ways to do your task, but see how this feels. Sketch out the CREATE TABLEs, INSERTs and SELECTs. (Adding N columns for N languages is a maintenance and coding nightmare. Tomorrow, you will need N+1 languages.) I would jettison the id in: `country_id` INT NOT NULL , `country_code` CHAR(2) NOT NULL , and use country_code as the PRIMARY KEY, and make it ASCII, not UTF8. That would turn the 4-byte id into a 2-byte string. I gather you are using an new-enough NDB so that FOREIGN KEYs are implemented? > -Original Message- > From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, May 01, 2013 5:53 AM > To: [MySQL] > Subject: Adding language support to tables > > Hi, > > I've the following database structure of 4 tables for geographical > information > > > > CREATE TABLE IF NOT EXISTS `mydb`.`country` ( > > `country_id` INT NOT NULL , > > `country_code` CHAR(2) NOT NULL , > > `name` VARCHAR(255) NOT NULL , > > PRIMARY KEY (`country_id`) , > > UNIQUE INDEX `country_code_UNIQUE` (`country_code` ASC) ) > > ENGINE = ndbcluster > > DEFAULT CHARACTER SET = utf8 > > COLLATE = utf8_unicode_ci; > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`region` ( > > `region_id` INT NOT NULL , > > `name` VARCHAR(255) NOT NULL , > > `country_code` CHAR(2) NOT NULL , > > PRIMARY KEY (`region_id`) , > > INDEX `FK_country_code` (`country_code` ASC) , > > CONSTRAINT `FK_country_code` > > FOREIGN KEY (`country_code` ) > > REFERENCES `mydb`.`country` (`country_code` ) > > ON DELETE NO ACTION > > ON UPDATE NO ACTION) > > ENGINE = ndbcluster > > DEFAULT CHARACTER SET = utf8 > > COLLATE = utf8_unicode_ci; > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`city` ( > > `city_id` INT NOT NULL , > > `region_id` INT NOT NULL , > > `name` VARCHAR(255) NOT NULL , > > `latitude` DOUBLE NOT NULL , > > `longitude` DOUBLE NOT NULL , > > PRIMARY KEY (`city_id`) , > > INDEX `FK_region_id` (`region_id` ASC) , > > CONSTRAINT `FK_region_id` > > FOREIGN KEY (`region_id` ) > > REFERENCES `mydb`.`region` (`region_id` ) > > ON DELETE NO ACTION > > ON UPDATE NO ACTION) > > ENGINE = ndbcluster > > DEFAULT CHARACTER SET = utf8 > > COLLATE = utf8_unicode_ci; > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`district` ( > > `district_id` INT NOT NULL , > > `city_id` INT NOT NULL , > > `name` VARCHAR(255) NOT NULL , > > `latitude` DOUBLE NOT NULL , > > `longitude` DOUBLE NOT NULL , > > PRIMARY KEY (`district_id`) , > > INDEX `FK_city_id` (`city_id` ASC) , > > CONSTRAINT `FK_city_id` > > FOREIGN KEY (`city_id` ) > > REFERENCES `mydb`.`city` (`city_id` ) > > ON DELETE NO ACTION > > ON UPDATE NO ACTION) > > ENGINE = ndbcluster > > DEFAULT CHARACTER SET = utf8 > > COLLATE = utf8_unicode_ci; > > > > > Basically I'm wanting to add language support for each table to > translate the name field in each instance. All other information will > remain the same. > > Therefore is my best approach to add some sort of look-up table with > the translation...? > > Thanks > Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Adding language support to tables
"You want to say either "Germany" or "Deutschland", depending on a "language_code" somewhere?" *Yes, this is the exact issue I'm on on about. * I like your idea. Does anyone else have any thoughts before I try this way ? On Wed, May 1, 2013 at 4:19 PM, Rick James wrote: > You want to say either "Germany" or "Deutschland", depending on a > "language_code" somewhere? > > Remove the strings you have in those tables now; add about 4 new tables, > each one paralleling the existing tables, but more rows and these columns: > * id (the PK of the existing table) > * language code (ENUM or TINYINT UNSIGNED indicating the language) > * spelling (utf8 version for the language in question) > > There may be better ways to do your task, but see how this feels. Sketch > out the CREATE TABLEs, INSERTs and SELECTs. > > (Adding N columns for N languages is a maintenance and coding nightmare. > Tomorrow, you will need N+1 languages.) > > I would jettison the id in: > `country_id` INT NOT NULL , > `country_code` CHAR(2) NOT NULL , > and use country_code as the PRIMARY KEY, and make it ASCII, not UTF8. > That would turn the 4-byte id into a 2-byte string. > > I gather you are using an new-enough NDB so that FOREIGN KEYs are > implemented? > > > -Original Message- > > From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] > > Sent: Wednesday, May 01, 2013 5:53 AM > > To: [MySQL] > > Subject: Adding language support to tables > > > > Hi, > > > > I've the following database structure of 4 tables for geographical > > information > > > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`country` ( > > > > `country_id` INT NOT NULL , > > > > `country_code` CHAR(2) NOT NULL , > > > > `name` VARCHAR(255) NOT NULL , > > > > PRIMARY KEY (`country_id`) , > > > > UNIQUE INDEX `country_code_UNIQUE` (`country_code` ASC) ) > > > > ENGINE = ndbcluster > > > > DEFAULT CHARACTER SET = utf8 > > > > COLLATE = utf8_unicode_ci; > > > > > > > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`region` ( > > > > `region_id` INT NOT NULL , > > > > `name` VARCHAR(255) NOT NULL , > > > > `country_code` CHAR(2) NOT NULL , > > > > PRIMARY KEY (`region_id`) , > > > > INDEX `FK_country_code` (`country_code` ASC) , > > > > CONSTRAINT `FK_country_code` > > > > FOREIGN KEY (`country_code` ) > > > > REFERENCES `mydb`.`country` (`country_code` ) > > > > ON DELETE NO ACTION > > > > ON UPDATE NO ACTION) > > > > ENGINE = ndbcluster > > > > DEFAULT CHARACTER SET = utf8 > > > > COLLATE = utf8_unicode_ci; > > > > > > > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`city` ( > > > > `city_id` INT NOT NULL , > > > > `region_id` INT NOT NULL , > > > > `name` VARCHAR(255) NOT NULL , > > > > `latitude` DOUBLE NOT NULL , > > > > `longitude` DOUBLE NOT NULL , > > > > PRIMARY KEY (`city_id`) , > > > > INDEX `FK_region_id` (`region_id` ASC) , > > > > CONSTRAINT `FK_region_id` > > > > FOREIGN KEY (`region_id` ) > > > > REFERENCES `mydb`.`region` (`region_id` ) > > > > ON DELETE NO ACTION > > > > ON UPDATE NO ACTION) > > > > ENGINE = ndbcluster > > > > DEFAULT CHARACTER SET = utf8 > > > > COLLATE = utf8_unicode_ci; > > > > > > > > > > > > CREATE TABLE IF NOT EXISTS `mydb`.`district` ( > > > > `district_id` INT NOT NULL , > > > > `city_id` INT NOT NULL , > > > > `name` VARCHAR(255) NOT NULL , > > > > `latitude` DOUBLE NOT NULL , > > > > `longitude` DOUBLE NOT NULL , > > > > PRIMARY KEY (`district_id`) , > > > > INDEX `FK_city_id` (`city_id` ASC) , > > > > CONSTRAINT `FK_city_id` > > > > FOREIGN KEY (`city_id` ) > > > > REFERENCES `mydb`.`city` (`city_id` ) > > > > ON DELETE NO ACTION > > > > ON UPDATE NO ACTION) > > > > ENGINE = ndbcluster > > > > DEFAULT CHARACTER SET = utf8 > > > > COLLATE = utf8_unicode_ci; > > > > > > > > > > Basically I'm wanting to add language support for each table to > > translate the name field in each instance. All other information will > > remain the same. > > > > Therefore is my best approach to add some sort of look-up table with > > the translation...? > > > > Thanks > > Neil >
RE: Chain Replication QUestion
> 1) Enable log-bin on master2 (slave that will be converted to a master) That does not 'convert' it -- it makes it both a Master and a Slave (a "Relay"). The CHANGE MASTER is probably correct, but it is difficult to find the right spot. A simple way is to 1. Stop all writes everywhere. 2. Wait for replication to catchup everywhere. 3. FLUSH LOGS everywhere. 4. Now CHANGE MASTER on the Slave to the start (POS=0 or 4) of the freshly created binlog in the machine that is the Slave's new Master. 5. Start writes. > -Original Message- > From: Richard Reina [mailto:gatorre...@gmail.com] > Sent: Wednesday, May 01, 2013 6:00 AM > To: Manuel Arostegui > Cc: mysql@lists.mysql.com > Subject: Re: Chain Replication QUestion > > Hello Manuel, > > Thank you for your reply. Could I do the following?: > > 1) Enable log-bin on master2 (slave that will be converted to a master) > 2) Enable log-slave-updates on master2 > 3) Execute CHANGE MASTER to on another existing slave so that it gets > it's updates from master2 instead of master1. > > Thanks for the help thus far. > > > 2013/4/30, Manuel Arostegui : > > 2013/4/30 Richard Reina > > > >> I have a few slaves set up on my local network that get updates from > >> my main mysql database master. I was hoping to turn one into a > master > >> while keeping it a slave so that I can set up a chain. Does anyone > >> know where I can find a "how to" or other documentation for this > >> specific task? > >> > >> > > It is quite easy: > > > > Enable log-slave-updates in the slave you want to be a master. > > Do a mysqldump -e --master-data=2 and put that mysqldump in the > future > > slaves. Take a look at the first lines of the mysqldump where you'll > > find the position and logfile those slaves need to start the > replication from. > > You can also use xtrabackup if you like. > > > > Manuel. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Chain Replication QUestion
If you're able to use MySQL 5.6 and enable GTIDs then it gets a whole lot simpler as you don't need to worry about finding the correct positions in the binary logs. Take a look at http://www.mysql.com/why-mysql/white-papers/mysql-replication-high-availability/ and http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ Andrew. > -Original Message- > From: Rick James [mailto:rja...@yahoo-inc.com] > Sent: 01 May 2013 16:29 > To: Richard Reina; Manuel Arostegui > Cc: mysql@lists.mysql.com > Subject: RE: Chain Replication QUestion > > > 1) Enable log-bin on master2 (slave that will be converted to a > master) > That does not 'convert' it -- it makes it both a Master and a Slave (a > "Relay"). > > The CHANGE MASTER is probably correct, but it is difficult to find the > right spot. > A simple way is to > 1. Stop all writes everywhere. > 2. Wait for replication to catchup everywhere. > 3. FLUSH LOGS everywhere. > 4. Now CHANGE MASTER on the Slave to the start (POS=0 or 4) of the > freshly created binlog in the machine that is the Slave's new Master. > 5. Start writes. > > > > -Original Message- > > From: Richard Reina [mailto:gatorre...@gmail.com] > > Sent: Wednesday, May 01, 2013 6:00 AM > > To: Manuel Arostegui > > Cc: mysql@lists.mysql.com > > Subject: Re: Chain Replication QUestion > > > > Hello Manuel, > > > > Thank you for your reply. Could I do the following?: > > > > 1) Enable log-bin on master2 (slave that will be converted to a > master) > > 2) Enable log-slave-updates on master2 > > 3) Execute CHANGE MASTER to on another existing slave so that it gets > > it's updates from master2 instead of master1. > > > > Thanks for the help thus far. > > > > > > 2013/4/30, Manuel Arostegui : > > > 2013/4/30 Richard Reina > > > > > >> I have a few slaves set up on my local network that get updates > from > > >> my main mysql database master. I was hoping to turn one into a > > master > > >> while keeping it a slave so that I can set up a chain. Does > anyone > > >> know where I can find a "how to" or other documentation for this > > >> specific task? > > >> > > >> > > > It is quite easy: > > > > > > Enable log-slave-updates in the slave you want to be a master. > > > Do a mysqldump -e --master-data=2 and put that mysqldump in the > > future > > > slaves. Take a look at the first lines of the mysqldump where > you'll > > > find the position and logfile those slaves need to start the > > replication from. > > > You can also use xtrabackup if you like. > > > > > > Manuel. > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Chain Replication QUestion
Hello Manuel, Thank you for your reply. Could I do the following?: 1) Enable log-bin on master2 (slave that will be converted to a master) 2) Enable log-slave-updates on master2 3) Execute CHANGE MASTER to on another existing slave so that it gets it's updates from master2 instead of master1. Thanks for the help thus far. 2013/4/30, Manuel Arostegui : > 2013/4/30 Richard Reina > >> I have a few slaves set up on my local network that get updates from >> my main mysql database master. I was hoping to turn one into a master >> while keeping it a slave so that I can set up a chain. Does anyone >> know where I can find a "how to" or other documentation for this >> specific task? >> >> > It is quite easy: > > Enable log-slave-updates in the slave you want to be a master. > Do a mysqldump -e --master-data=2 and put that mysqldump in the future > slaves. Take a look at the first lines of the mysqldump where you'll find > the position and logfile those slaves need to start the replication from. > You can also use xtrabackup if you like. > > Manuel. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Adding language support to tables
Hi, I've the following database structure of 4 tables for geographical information CREATE TABLE IF NOT EXISTS `mydb`.`country` ( `country_id` INT NOT NULL , `country_code` CHAR(2) NOT NULL , `name` VARCHAR(255) NOT NULL , PRIMARY KEY (`country_id`) , UNIQUE INDEX `country_code_UNIQUE` (`country_code` ASC) ) ENGINE = ndbcluster DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `mydb`.`region` ( `region_id` INT NOT NULL , `name` VARCHAR(255) NOT NULL , `country_code` CHAR(2) NOT NULL , PRIMARY KEY (`region_id`) , INDEX `FK_country_code` (`country_code` ASC) , CONSTRAINT `FK_country_code` FOREIGN KEY (`country_code` ) REFERENCES `mydb`.`country` (`country_code` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = ndbcluster DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `mydb`.`city` ( `city_id` INT NOT NULL , `region_id` INT NOT NULL , `name` VARCHAR(255) NOT NULL , `latitude` DOUBLE NOT NULL , `longitude` DOUBLE NOT NULL , PRIMARY KEY (`city_id`) , INDEX `FK_region_id` (`region_id` ASC) , CONSTRAINT `FK_region_id` FOREIGN KEY (`region_id` ) REFERENCES `mydb`.`region` (`region_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = ndbcluster DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `mydb`.`district` ( `district_id` INT NOT NULL , `city_id` INT NOT NULL , `name` VARCHAR(255) NOT NULL , `latitude` DOUBLE NOT NULL , `longitude` DOUBLE NOT NULL , PRIMARY KEY (`district_id`) , INDEX `FK_city_id` (`city_id` ASC) , CONSTRAINT `FK_city_id` FOREIGN KEY (`city_id` ) REFERENCES `mydb`.`city` (`city_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = ndbcluster DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci; Basically I'm wanting to add language support for each table to translate the name field in each instance. All other information will remain the same. Therefore is my best approach to add some sort of look-up table with the translation...? Thanks Neil
