Re: How do I use and JOIN the mysql.time_zone% tables?
Part of your answer is the offset column, which seems to be relative to the abbreviation used. This implies, to me, that each particular abbreviation has it's own way of specifying the "starting point" of the time. Added is the DST flag, which (probably) tells you that your app needs to keep daylight savings time in mind. I can't tell you why there are so many, or which one to pick, but there you go. Just, you know, pick one, learn it's rules and stick to it. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
How do I use and JOIN the mysql.time_zone% tables?
I'm trying to figure out how to join the mysql.time_zone% tables and make sense of this. YES, I know how to "use" them with SET time_zone = timezone; and all that. http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html That is NOT what I need them for (yet). I have a list of airports and those airports have cities and countries. I need to correlate those cities (which may or may not have an exact match in the time_zone_name table, so for each airport/city, I will need to hunt down the right "offset" via some page like this: http://www.timeanddate.com/worldclock/search.html THEN store the Time_zone_id in my city table (or whatever the unique combination is that I'd need and at this point I'm very confused as to what that is). The problem is I can find no real documentation what all these 5 tables are each for (some are obvious, but so cryptic it's hard to digest them) Moreover, I don't understand the results I'm getting... SELECT * FROM time_zone_name WHERE `Name` LIKE '%brussels%'; Name Time_zone_id - Europe/Brussels 412 posix/Europe/Brussels 993 right/Europe/Brussels 1574 First, WTF are there THREE Brussels?? SELECT * FROM time_zone_transition_type WHERE Time_zone_id IN (412, 993, 1574); Time_zone_id Transition_type_id Offset Is_DST Abbreviation -- -- -- 412 0 0 0 WET 412 13600 0 CET 412 27200 1 CEST 412 33600 0 CET 412 47200 1 CEST 412 53600 1 WEST 412 6 0 0 WET 412 7 0 0 WET 412 87200 1 CEST 412 93600 0 CET 993 0 0 0 WET 993 13600 0 CET 993 27200 1 CEST 993 33600 0 CET 993 47200 1 CEST 993 53600 1 WEST 993 6 0 0 WET 993 7 0 0 WET 993 87200 1 CEST 993 93600 0 CET 1574 0 0 0 WET 1574 13600 0 CET 1574 27200 1 CEST 1574 33600 0 CET 1574 47200 1 CEST 1574 53600 1 WEST 1574 6 0 0 WET 1574 7 0 0 WET 1574 87200 1 CEST 1574 93600 0 CET Now WTF are there TEN rows PER? This page: http://www.timeanddate.com/worldclock/city.html?n=48 says Brussels, Belgium is "CEST" so why are there 10 time_zone_transition_types SELECT * FROM time_zone_name JOIN time_zone_transition_type ON time_zone_name.Time_zone_id = time_zone_transition_type.Time_zone_id -- AND time_zone_transition.Transition_type_id = -- time_zone_transition_type.Transition_type_id WHERE `Name` LIKE '%brussels%'; Name Time_zone_id Transition_type_id Offset Is_DST Abbreviation - -- -- -- Europe/Brussels 412 0 0 0 WET Europe/Brussels 412 13600 0 CET Europe/Brussels 412 27200 1 CEST Europe/Brussels 412 33600 0 CET Europe/Brussels 412 47200 1 CEST Europe/Brussels 412 53600 1 WEST Europe/Brussels 412 6 0 0 WET Europe/Brussels 412 7 0 0 WET Europe/Brussels 412 87200 1 CEST Europe/Brussels 412 93600 0 CET posix/Europe/Brussels 993 0 0 0 WET posix/Europe/Brussels 993 13600 0 CET pos
Are Ubuntu 64-bit /var/lib/mysql/ibdata (etc) compatible with 32-bit ??
We have a PSE05 "Master" and PSE06 "Slave" (PRODUCTION servers) both are Ubuntu 32-bit. We have a third slave PSE07 which is Ubuntu 64-bit. This is our 'live backup' so to speak. We take mysqld down daily on there and tarball the /var/lib/mysql and /var/log/mysql as snapshots (since mysqldump would take a week literally to re-import). Our data is about 100GB and nearly 1 Billion records and growing by several hundred thousand per day. We had some replication hose-up where someone accidentally wrote to the PSE06 slave. This wasn't caught right away and so it cascaded and queued up about 130 rows to be written. Obviously going through this whole business: mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Would take WAY too long and painful. So we thought we'd shut down PSE06, scp the .tgz file from PSE07, dump it in, set the pointers on the slave and let replication catch up. Not so much. So is this even possible. Are the ibdata files binary compatible between "bit" versions (or even different OS's for that matter) We are getting errors: 101013 23:56:22 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.^M r...@pse06:~# find /home/mysql/ | grep relay /home/mysql/mysqld-relay-bin.01 /home/mysql/relay-log.info /home/mysql/mysqld-relay-bin.05 /home/mysql/binlog/mysqld-relay-bin.004055 /home/mysql/binlog/mysqld-relay-bin.004287 /home/mysql/binlog/mysqld-relay-bin.005693 /home/mysql/binlog/mysqld-relay-bin.004041 /home/mysql/binlog/mysqld-relay-bin.004676 /home/mysql/binlog/mysqld-relay-bin.004664 /home/mysql/binlog/mysqld-relay-bin.004044 /home/mysql/binlog/mysqld-relay-bin.004468 /home/mysql/binlog/mysqld-relay-bin.004682 /home/mysql/binlog/mysqld-relay-bin.004094 /home/mysql/binlog/mysqld-relay-bin.004153 /home/mysql/binlog/mysqld-relay-bin.004051 /home/mysql/binlog/mysqld-relay-bin.004645 /home/mysql/binlog/mysqld-relay-bin.004516 /home/mysql/binlog/mysqld-relay-bin.003889 /home/mysql/binlog/mysqld-relay-bin.004715 /home/mysql/binlog/mysqld-relay-bin.004299 /home/mysql/binlog/mysqld-relay-bin.004483 /home/mysql/binlog/mysqld-relay-bin.004628 /home/mysql/binlog/mysqld-relay-bin.004088 /home/mysql/binlog/mysqld-relay-bin.004059 /home/mysql/binlog/mysqld-relay-bin.index /home/mysql/binlog/mysqld-relay-bin.004271 /home/mysql/binlog/mysqld-relay-bin.004077 /home/mysql/binlog/mysqld-relay-bin.004671 /home/mysql/binlog/mysqld-relay-bin.004513 /home/mysql/binlog/mysqld-relay-bin.004732 /home/mysql/binlog/mysqld-relay-bin.004096 /home/mysql/mysqld-relay-bin.06 /home/mysql/mysqld-relay-bin.08 /home/mysql/mysqld-relay-bin.02 /home/mysql/mysqld-relay-bin.03 /home/mysql/mysqld-relay-bin.04 /home/mysql/mysqld-relay-bin.07 /home/mysql/mysqld-relay-bin.10 /home/mysql/mysqld-relay-bin.index /home/mysql/mysqld-relay-bin.12 /home/mysql/mysqld-relay-bin.13 /home/mysql/mysqld-relay-bin.09 /home/mysql/mysqld-relay-bin.11 Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem. Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [ERROR] Failed to open the relay log '/home/mysql/binlog/mysqld-relay-bin.005693' (relay_log_pos 53058571) Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [ERROR] Could not find target log during relay log initialization -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table locking generally
I have a problem that I can't understand readily. I have a database that has a couple of tables that lock for a recognizable period of time. The reason I know is because during the lock the application stops responding totally. The storage engine is MyIsam. I have reread everything about locks in both InnoDB and MyIsam. I know that I can change to InnoDB and that would alleviate the problem, but I am curious as to what the problem is. This is a new problem for the application has been running for 4 years. The server is a 64bit server with 16GB of memory, raid 10, and a quad core CPU. The size of the database is 600MB. The lock occurred while two users were accessing the same table. Thanks for any insight, /ZK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard wrote: > I couldn't help but notice you have individual indexes on nearly all the > fields of your table. If you won't be using these fields exclusively as a > join or filter condition in a query, you are unlikely to benefit from the > extra indexes and, in fact, they could slow down your inserts and add to > your storage requirements. > > -Travis > > -Original Message- > From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] > Sent: Wednesday, October 13, 2010 8:37 AM > To: [MySQL] > Subject: Primary key not unique on InnoDB table > > I've the following table. But why isn't the primary key unique, e.g. > preventing duplicates if entered ? > > CREATE TABLE `players_master` ( > > `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > `default_teams_id` bigint(20) NOT NULL, > `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > `dob` date NOT NULL, > `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > `retirement_date` date DEFAULT NULL, > `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > `estimated_value` double NOT NULL DEFAULT '0', > `contract_wage` double NOT NULL DEFAULT '0', > `rating` int(11) NOT NULL, > PRIMARY KEY (`players_id`,`default_teams_id`), > KEY `FK_players_master_countries_id` (`countries_id`), > KEY `FK_players_master_positions_id` (`positions_id`), > KEY `IDX_first_name` (`first_name`), > KEY `IDX_known_as` (`known_as`), > KEY `IDX_second_name` (`second_name`), > KEY `IDX_dob` (`dob`), > KEY `IDX_estimated_value` (`estimated_value`), > KEY `IDX_contract_wage` (`contract_wage`), > KEY `IDX_rating` (`rating`), > KEY `FK_players_master_teams_id` (`default_teams_id`), > CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) > REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) > REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) > REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION > ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > COLLATE=utf8_unicode_ci > > I'm confused, I thought primary keys were always unique ? > > Cheers > Neil > >
RE: Primary key not unique on InnoDB table
I couldn't help but notice you have individual indexes on nearly all the fields of your table. If you won't be using these fields exclusively as a join or filter condition in a query, you are unlikely to benefit from the extra indexes and, in fact, they could slow down your inserts and add to your storage requirements. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, October 13, 2010 8:37 AM To: [MySQL] Subject: Primary key not unique on InnoDB table I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
Shawn it is fine. I thought my primary key was just 1 field. On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL) < shawn.l.gr...@oracle.com> wrote: > On 10/13/2010 11:37 AM, Tompkins Neil wrote: > >> Shawn, sorry my error, I didn't realise I had two fields as the primary >> key >> >> > That's misinformation. You can have multiple fields as a primary key. > > Show us what you think is duplicate data and I may be able to help you fix > your definition > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. > Office: Blountville, TN >
Re: Primary key not unique on InnoDB table
On 10/13/2010 11:37 AM, Tompkins Neil wrote: Shawn, sorry my error, I didn't realise I had two fields as the primary key That's misinformation. You can have multiple fields as a primary key. Show us what you think is duplicate data and I may be able to help you fix your definition -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
On 10/13/2010 10:37 AM, Tompkins Neil wrote: I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil I see no reason why this won't work. Show us some duplicate data and I may be able to explain how to fix your definition. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
Of course, sorry totally stupid should I recognised that. Thanks Neil On Wed, Oct 13, 2010 at 3:46 PM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi Neil, > > Yes, primary key is always unique. > > In your case, you are using composite key (players_id,default_teams_id). > > _Krishna > > On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> I've the following table. But why isn't the primary key unique, e.g. >> preventing duplicates if entered ? >> >> CREATE TABLE `players_master` ( >> >> `players_id` bigint(20) NOT NULL AUTO_INCREMENT, >> `default_teams_id` bigint(20) NOT NULL, >> `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, >> `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, >> `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, >> `dob` date NOT NULL, >> `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, >> `retirement_date` date DEFAULT NULL, >> `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, >> `estimated_value` double NOT NULL DEFAULT '0', >> `contract_wage` double NOT NULL DEFAULT '0', >> `rating` int(11) NOT NULL, >> PRIMARY KEY (`players_id`,`default_teams_id`), >> KEY `FK_players_master_countries_id` (`countries_id`), >> KEY `FK_players_master_positions_id` (`positions_id`), >> KEY `IDX_first_name` (`first_name`), >> KEY `IDX_known_as` (`known_as`), >> KEY `IDX_second_name` (`second_name`), >> KEY `IDX_dob` (`dob`), >> KEY `IDX_estimated_value` (`estimated_value`), >> KEY `IDX_contract_wage` (`contract_wage`), >> KEY `IDX_rating` (`rating`), >> KEY `FK_players_master_teams_id` (`default_teams_id`), >> CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) >> REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO >> ACTION, >> CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) >> REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO >> ACTION, >> CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) >> REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO >> ACTION >> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 >> COLLATE=utf8_unicode_ci >> >> I'm confused, I thought primary keys were always unique ? >> >> Cheers >> Neil >> > >
Re: Primary key not unique on InnoDB table
I see what you mean. Infact this is wrong and I will be dropping the second field in the primary key. 2010/10/13 João Cândido de Souza Neto > A primary key with an auto_increment is ok, but I cant think about a > primary > key with two fiels where one of them is autoincrement. Am I completely > wrong? > > -- > João Cândido de Souza Neto > > "Tompkins Neil" escreveu na mensagem > news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com... > Sorry Joao, I thought that was pretty standard to have a primary key with > auto_increment ?? > > > 2010/10/13 João Cândido de Souza Neto > > > Sorry, the word is counpound instead of composed. > > > > -- > > João Cândido de Souza Neto > > > > ""João Cândido de Souza Neto"" escreveu na > > mensagem news:20101013144314.9787.qm...@lists.mysql.com... > > > I´d never seen before a composed primary key that has an auto_increment > > > field on it. > > > > > > May be I can be wrong but I think it wont work properly. > > > > > > As far as I know, if you have an auto_increment field it must be your > > > single primary key. Am I wrong? > > > > > > -- > > > João Cândido de Souza Neto > > > > > > "Tompkins Neil" escreveu na mensagem > > > news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... > > >> I've the following table. But why isn't the primary key unique, e.g. > > >> preventing duplicates if entered ? > > >> > > >> CREATE TABLE `players_master` ( > > >> > > >> `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > > >> `default_teams_id` bigint(20) NOT NULL, > > >> `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > > >> `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > > >> `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > > >> `dob` date NOT NULL, > > >> `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > > >> `retirement_date` date DEFAULT NULL, > > >> `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > > >> `estimated_value` double NOT NULL DEFAULT '0', > > >> `contract_wage` double NOT NULL DEFAULT '0', > > >> `rating` int(11) NOT NULL, > > >> PRIMARY KEY (`players_id`,`default_teams_id`), > > >> KEY `FK_players_master_countries_id` (`countries_id`), > > >> KEY `FK_players_master_positions_id` (`positions_id`), > > >> KEY `IDX_first_name` (`first_name`), > > >> KEY `IDX_known_as` (`known_as`), > > >> KEY `IDX_second_name` (`second_name`), > > >> KEY `IDX_dob` (`dob`), > > >> KEY `IDX_estimated_value` (`estimated_value`), > > >> KEY `IDX_contract_wage` (`contract_wage`), > > >> KEY `IDX_rating` (`rating`), > > >> KEY `FK_players_master_teams_id` (`default_teams_id`), > > >> CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY > > (`countries_id`) > > >> REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE > > >> NO > > >> ACTION, > > >> CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY > > (`positions_id`) > > >> REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE > > >> NO > > >> ACTION, > > >> CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY > > (`default_teams_id`) > > >> REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE > NO > > >> ACTION > > >> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > > >> COLLATE=utf8_unicode_ci > > >> > > >> I'm confused, I thought primary keys were always unique ? > > >> > > >> Cheers > > >> Neil > > >> > > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com > >
Re: Primary key not unique on InnoDB table
A primary key with an auto_increment is ok, but I cant think about a primary key with two fiels where one of them is autoincrement. Am I completely wrong? -- João Cândido de Souza Neto "Tompkins Neil" escreveu na mensagem news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com... Sorry Joao, I thought that was pretty standard to have a primary key with auto_increment ?? 2010/10/13 João Cândido de Souza Neto > Sorry, the word is counpound instead of composed. > > -- > João Cândido de Souza Neto > > ""João Cândido de Souza Neto"" escreveu na > mensagem news:20101013144314.9787.qm...@lists.mysql.com... > > I´d never seen before a composed primary key that has an auto_increment > > field on it. > > > > May be I can be wrong but I think it wont work properly. > > > > As far as I know, if you have an auto_increment field it must be your > > single primary key. Am I wrong? > > > > -- > > João Cândido de Souza Neto > > > > "Tompkins Neil" escreveu na mensagem > > news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... > >> I've the following table. But why isn't the primary key unique, e.g. > >> preventing duplicates if entered ? > >> > >> CREATE TABLE `players_master` ( > >> > >> `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > >> `default_teams_id` bigint(20) NOT NULL, > >> `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > >> `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > >> `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `dob` date NOT NULL, > >> `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > >> `retirement_date` date DEFAULT NULL, > >> `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > >> `estimated_value` double NOT NULL DEFAULT '0', > >> `contract_wage` double NOT NULL DEFAULT '0', > >> `rating` int(11) NOT NULL, > >> PRIMARY KEY (`players_id`,`default_teams_id`), > >> KEY `FK_players_master_countries_id` (`countries_id`), > >> KEY `FK_players_master_positions_id` (`positions_id`), > >> KEY `IDX_first_name` (`first_name`), > >> KEY `IDX_known_as` (`known_as`), > >> KEY `IDX_second_name` (`second_name`), > >> KEY `IDX_dob` (`dob`), > >> KEY `IDX_estimated_value` (`estimated_value`), > >> KEY `IDX_contract_wage` (`contract_wage`), > >> KEY `IDX_rating` (`rating`), > >> KEY `FK_players_master_teams_id` (`default_teams_id`), > >> CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY > (`countries_id`) > >> REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE > >> NO > >> ACTION, > >> CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY > (`positions_id`) > >> REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE > >> NO > >> ACTION, > >> CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY > (`default_teams_id`) > >> REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > >> ACTION > >> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > >> COLLATE=utf8_unicode_ci > >> > >> I'm confused, I thought primary keys were always unique ? > >> > >> Cheers > >> Neil > >> > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.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: Primary key not unique on InnoDB table
Sorry Joao, I thought that was pretty standard to have a primary key with auto_increment ?? 2010/10/13 João Cândido de Souza Neto > Sorry, the word is counpound instead of composed. > > -- > João Cândido de Souza Neto > > ""João Cândido de Souza Neto"" escreveu na > mensagem news:20101013144314.9787.qm...@lists.mysql.com... > > I´d never seen before a composed primary key that has an auto_increment > > field on it. > > > > May be I can be wrong but I think it wont work properly. > > > > As far as I know, if you have an auto_increment field it must be your > > single primary key. Am I wrong? > > > > -- > > João Cândido de Souza Neto > > > > "Tompkins Neil" escreveu na mensagem > > news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... > >> I've the following table. But why isn't the primary key unique, e.g. > >> preventing duplicates if entered ? > >> > >> CREATE TABLE `players_master` ( > >> > >> `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > >> `default_teams_id` bigint(20) NOT NULL, > >> `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > >> `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > >> `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `dob` date NOT NULL, > >> `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > >> `retirement_date` date DEFAULT NULL, > >> `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > >> `estimated_value` double NOT NULL DEFAULT '0', > >> `contract_wage` double NOT NULL DEFAULT '0', > >> `rating` int(11) NOT NULL, > >> PRIMARY KEY (`players_id`,`default_teams_id`), > >> KEY `FK_players_master_countries_id` (`countries_id`), > >> KEY `FK_players_master_positions_id` (`positions_id`), > >> KEY `IDX_first_name` (`first_name`), > >> KEY `IDX_known_as` (`known_as`), > >> KEY `IDX_second_name` (`second_name`), > >> KEY `IDX_dob` (`dob`), > >> KEY `IDX_estimated_value` (`estimated_value`), > >> KEY `IDX_contract_wage` (`contract_wage`), > >> KEY `IDX_rating` (`rating`), > >> KEY `FK_players_master_teams_id` (`default_teams_id`), > >> CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY > (`countries_id`) > >> REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO > >> ACTION, > >> CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY > (`positions_id`) > >> REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO > >> ACTION, > >> CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY > (`default_teams_id`) > >> REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > >> ACTION > >> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > >> COLLATE=utf8_unicode_ci > >> > >> I'm confused, I thought primary keys were always unique ? > >> > >> Cheers > >> Neil > >> > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com > >
Re: Primary key not unique on InnoDB table
Hi Neil, Yes, primary key is always unique. In your case, you are using composite key (players_id,default_teams_id). _Krishna On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil wrote: > I've the following table. But why isn't the primary key unique, e.g. > preventing duplicates if entered ? > > CREATE TABLE `players_master` ( > > `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > `default_teams_id` bigint(20) NOT NULL, > `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > `dob` date NOT NULL, > `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > `retirement_date` date DEFAULT NULL, > `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > `estimated_value` double NOT NULL DEFAULT '0', > `contract_wage` double NOT NULL DEFAULT '0', > `rating` int(11) NOT NULL, > PRIMARY KEY (`players_id`,`default_teams_id`), > KEY `FK_players_master_countries_id` (`countries_id`), > KEY `FK_players_master_positions_id` (`positions_id`), > KEY `IDX_first_name` (`first_name`), > KEY `IDX_known_as` (`known_as`), > KEY `IDX_second_name` (`second_name`), > KEY `IDX_dob` (`dob`), > KEY `IDX_estimated_value` (`estimated_value`), > KEY `IDX_contract_wage` (`contract_wage`), > KEY `IDX_rating` (`rating`), > KEY `FK_players_master_teams_id` (`default_teams_id`), > CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) > REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) > REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) > REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION > ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > COLLATE=utf8_unicode_ci > > I'm confused, I thought primary keys were always unique ? > > Cheers > Neil >
Re: Primary key not unique on InnoDB table
Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto ""João Cândido de Souza Neto"" escreveu na mensagem news:20101013144314.9787.qm...@lists.mysql.com... > I´d never seen before a composed primary key that has an auto_increment > field on it. > > May be I can be wrong but I think it wont work properly. > > As far as I know, if you have an auto_increment field it must be your > single primary key. Am I wrong? > > -- > João Cândido de Souza Neto > > "Tompkins Neil" escreveu na mensagem > news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... >> I've the following table. But why isn't the primary key unique, e.g. >> preventing duplicates if entered ? >> >> CREATE TABLE `players_master` ( >> >> `players_id` bigint(20) NOT NULL AUTO_INCREMENT, >> `default_teams_id` bigint(20) NOT NULL, >> `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, >> `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, >> `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, >> `dob` date NOT NULL, >> `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, >> `retirement_date` date DEFAULT NULL, >> `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, >> `estimated_value` double NOT NULL DEFAULT '0', >> `contract_wage` double NOT NULL DEFAULT '0', >> `rating` int(11) NOT NULL, >> PRIMARY KEY (`players_id`,`default_teams_id`), >> KEY `FK_players_master_countries_id` (`countries_id`), >> KEY `FK_players_master_positions_id` (`positions_id`), >> KEY `IDX_first_name` (`first_name`), >> KEY `IDX_known_as` (`known_as`), >> KEY `IDX_second_name` (`second_name`), >> KEY `IDX_dob` (`dob`), >> KEY `IDX_estimated_value` (`estimated_value`), >> KEY `IDX_contract_wage` (`contract_wage`), >> KEY `IDX_rating` (`rating`), >> KEY `FK_players_master_teams_id` (`default_teams_id`), >> CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) >> REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO >> ACTION, >> CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) >> REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO >> ACTION, >> CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) >> REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO >> ACTION >> ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 >> COLLATE=utf8_unicode_ci >> >> I'm confused, I thought primary keys were always unique ? >> >> Cheers >> Neil >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Primary key not unique on InnoDB table
I´d never seen before a composed primary key that has an auto_increment field on it. May be I can be wrong but I think it wont work properly. As far as I know, if you have an auto_increment field it must be your single primary key. Am I wrong? -- João Cândido de Souza Neto "Tompkins Neil" escreveu na mensagem news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... > I've the following table. But why isn't the primary key unique, e.g. > preventing duplicates if entered ? > > CREATE TABLE `players_master` ( > > `players_id` bigint(20) NOT NULL AUTO_INCREMENT, > `default_teams_id` bigint(20) NOT NULL, > `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, > `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, > `dob` date NOT NULL, > `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, > `retirement_date` date DEFAULT NULL, > `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, > `estimated_value` double NOT NULL DEFAULT '0', > `contract_wage` double NOT NULL DEFAULT '0', > `rating` int(11) NOT NULL, > PRIMARY KEY (`players_id`,`default_teams_id`), > KEY `FK_players_master_countries_id` (`countries_id`), > KEY `FK_players_master_positions_id` (`positions_id`), > KEY `IDX_first_name` (`first_name`), > KEY `IDX_known_as` (`known_as`), > KEY `IDX_second_name` (`second_name`), > KEY `IDX_dob` (`dob`), > KEY `IDX_estimated_value` (`estimated_value`), > KEY `IDX_contract_wage` (`contract_wage`), > KEY `IDX_rating` (`rating`), > KEY `FK_players_master_teams_id` (`default_teams_id`), > CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) > REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) > REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION, > CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) > REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO > ACTION > ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 > COLLATE=utf8_unicode_ci > > I'm confused, I thought primary keys were always unique ? > > Cheers > Neil > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Primary key not unique on InnoDB table
I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
Re: Incremental Backup Script
On 10/13/2010 9:18 AM, kranthi wrote: Hi Please be send sample incremental backup script (bash Shell script Easy to understand) Thanks& Regards, Kranthikiran I think you missed the points of the previous replies. MySQL does not do incremental backups the the same way that other RDBMS products you may be familiar with. You can take full backups (all of the tables and all of the data) and partial backups (some of the tables or some of the data). With those, you can combine the contents of the Binary Log Files to provide yourself with the ability to perform a point-in-time-recovery (PITR). Which combination of backup techniques (and there are multiple techniques) you use depends on your hardware, software, and operational requirements. Please read the fine manual for more details: http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re:Incremental Backup Script
Hi Please be send sample incremental backup script (bash Shell script Easy to understand) Thanks & Regards, Kranthikiran
Re: Backing up the InnoDB tables
> The problem is I don't have any command line access, just direct MySQL > access to the database tables. > > whats wrong with mysqldump? -- bEsT rEgArDs| "Confidence is what you have before you tomasz dereszynski | understand the problem." -- Woody Allen | Spes confisa Deo| "In theory, theory and practice are much numquam confusa recedit | the same. In practice they are very | different." -- Albert Einstein -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backing up the InnoDB tables
Quoting Tompkins Neil : The problem is I don't have any command line access, just direct MySQL access to the database tables. I dont know xtra backup, but if thats not an option you can just use mysqldump. This can be run from a remote server to your DB server, just using MySQL network access to the DB(s)... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Backing up the InnoDB tables
The problem is I don't have any command line access, just direct MySQL access to the database tables. On Wed, Oct 13, 2010 at 1:19 PM, Suresh Kuna wrote: > use xtra backup > > On Wed, Oct 13, 2010 at 5:37 PM, Tompkins Neil < > neil.tompk...@googlemail.com> wrote: > >> Would really appreciate some help or suggestions on this please, if anyone >> can assist ? >> >> Regards >> Neil >> >> -- Forwarded message -- >> From: Tompkins Neil >> Date: Tue, Oct 12, 2010 at 5:45 PM >> Subject: Backing up the InnoDB tables >> To: "[MySQL]" >> >> >> Hi >> >> On a shared MySQL server with access just to my own database, what is the >> recommend backup methods and strategies for the InnoDB tables ? >> >> Cheers >> Neil >> > > > > -- > Thanks > Suresh Kuna > MySQL DBA >
Re: Backing up the InnoDB tables
use xtra backup On Wed, Oct 13, 2010 at 5:37 PM, Tompkins Neil wrote: > Would really appreciate some help or suggestions on this please, if anyone > can assist ? > > Regards > Neil > > -- Forwarded message -- > From: Tompkins Neil > Date: Tue, Oct 12, 2010 at 5:45 PM > Subject: Backing up the InnoDB tables > To: "[MySQL]" > > > Hi > > On a shared MySQL server with access just to my own database, what is the > recommend backup methods and strategies for the InnoDB tables ? > > Cheers > Neil > -- Thanks Suresh Kuna MySQL DBA
Fwd: Backing up the InnoDB tables
Would really appreciate some help or suggestions on this please, if anyone can assist ? Regards Neil -- Forwarded message -- From: Tompkins Neil Date: Tue, Oct 12, 2010 at 5:45 PM Subject: Backing up the InnoDB tables To: "[MySQL]" Hi On a shared MySQL server with access just to my own database, what is the recommend backup methods and strategies for the InnoDB tables ? Cheers Neil
Re: Can this query be done w/o adding another column?
After bashing at this for a while with no luck I replaced the "inner" with "left" and I got the desired result. Thanks for the help. On Tue, Oct 12, 2010 at 5:18 PM, Travis Ard wrote: > Sorry, try changing the column mappings.ip to use the table aliases (m.ip > and m2.ip). > > > > -Travis > > > > From: Paul Halliday [mailto:paul.halli...@gmail.com] > Sent: Tuesday, October 12, 2010 11:37 AM > To: Travis Ard > Cc: mysql@lists.mysql.com > Subject: Re: Can this query be done w/o adding another column? > > > > On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard > wrote: > > You could join your mappings table twice, once on src_ip and again on > dst_ip: > > SELECT COUNT(signature) AS count, > > MAX(timestamp) AS maxTime, > INET_NTOA(src_ip), > > m.cc as src_cc, > INET_NTOA(dst_ip), > m2.cc as dst_cc, > > signature, > signature_id, > ip_proto > FROM event > > INNER JOIN mappings m ON event.src_ip = mappings.ip > INNER JOIN mappings m2 ON event.dst_ip = mappings.ip > > WHERE timestamp BETWEEN "2010-10-12 03:00:00" AND "2010-10-13 03:00:00" > > GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, > signature_id, ip_proto > > ORDER BY maxTime DESC > LIMIT 10; > > -Travis > > > > > I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on > clause' > > -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
