Fwd: Audit Table storage for Primary Key(s)
Any advice anyone ? -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Thu, May 30, 2013 at 8:27 AM Subject: Audit Table storage for Primary Key(s) To: [MySQL] mysql@lists.mysql.com Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil
Re: Audit Table storage for Primary Key(s)
On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Audit Table storage for Primary Key(s)
Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dkwrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Audit Table storage for Primary Key(s)
Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dkwrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- 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: Audit Table storage for Primary Key(s)
The kind of look ups will be trying to diagnose when and by who applied a update. So the primary key of the audit is important. My question is for performance, should the primary key be stored as a indexed field like I mentioned before, or should I have a actual individual field per primary key On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen cars...@bitbybit.dkwrote: Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dk wrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- 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: Audit Table storage for Primary Key(s)
Based on the little information available, I would make a lookup field consisting of tablename and primary keys. (although I still believe that storing this information in the database in the first place is probably the wrong approach, but to each his own) / Carsten On 31-05-2013 12:58, Neil Tompkins wrote: The kind of look ups will be trying to diagnose when and by who applied a update. So the primary key of the audit is important. My question is for performance, should the primary key be stored as a indexed field like I mentioned before, or should I have a actual individual field per primary key On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dk wrote: Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11 tel:31-05-2013%2011:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dkwrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- 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: Audit Table storage for Primary Key(s)
There's been a thirst for this kind of thing for sometime but possibly you're looking for a cheaper option? Since 5.5 there's some incarnation of an audit plugin which can be extended for your own needs which should allow you to perform some persistence of the results with either a log file which could be processed into a table before you run your daily query? http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html http://karlssonondatabases.blogspot.co.uk/2010/03/mysql-audit-plugin-api.html HTH Andy On Fri, May 31, 2013 at 12:05 PM, Carsten Pedersen cars...@bitbybit.dkwrote: Based on the little information available, I would make a lookup field consisting of tablename and primary keys. (although I still believe that storing this information in the database in the first place is probably the wrong approach, but to each his own) / Carsten On 31-05-2013 12:58, Neil Tompkins wrote: The kind of look ups will be trying to diagnose when and by who applied a update. So the primary key of the audit is important. My question is for performance, should the primary key be stored as a indexed field like I mentioned before, or should I have a actual individual field per primary key On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dk wrote: Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11 tel:31-05-2013%2011:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dk**wrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having
RE: Audit Table storage for Primary Key(s)
UUID PRIMARY KEY (or even secondary index) -- Once the table gets big enough (bigger than RAM cache), each row INSERTed (or SELECTed) will be a disk hit. (Rule of Thumb: only 100 hits/sec.) This is because _random_ keys (like UUID) make caching useless. Actually, the slowdown will be gradual. For example, once the table is 5 times as big as the cache, 80% (1-1/5) of the INSERTs/SELECTs will hit disk. Bottom line -- Avoid UUIDs in huge tables, if at all possible. (Exception: The bits in type-1 UUIDs can be rearranged to be roughly chronological.) BIGINT -- You cannot possibly hit its max with any existing hardware. MyISAM -- PRIMARY KEY is just another secondary index. Secondary indexes are separate BTrees. InnoDB -- PRIMARY KEY and data coexist in the same BTree. Secondary indexes are separate BTrees. So, assuming this audit table will be huge (too big to be cached), you need to carefully consider every index, both for writing and for reading. You mentioned that you might audit 50 tables? An index that starts with table_name would be inserting/selecting in 50 spots. If the second part of the index is something 'chronological', such as an AUTO_INCREMENT or TIMESTAMP, then there would be 50 hot spots in the index. This is quite efficient. INDEX(table_name, UUID) would be bad because of the randomness. InnoDB may be the preferred engine, even though the footprint is bigger. This is because careful design of the PK could lead to INSERTs into hot spot(s), plus SELECTs being able to take advantage of locality of reference. With PRIMARY KEY(table_name, ...), and SELECT .. WHERE tablename='...', InnoDB will find all the rows together (fewer disk hits); MyISAM will find the data scattered (more disk hits, hence slower). Another aspect... Would your SELECTs say WHERE ... AND timestamp BETWEEN... ? And, would you _usually_ query _recent_ times? If so, there could be a boost from doing both of these ** PARTITION BY RANGE(TO_DAYS(timestamp)) ** Move timestamp to the _end_ of any indexes that it is in. I would be happy to discuss these principles further. To be able to discuss more specifically, please provide ** Your tentative SHOW CREATE TABLE ** how big you plan for the table to become (#rows or GB), ** how much RAM you have -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Friday, May 31, 2013 4:05 AM Cc: [MySQL] Subject: Re: Audit Table storage for Primary Key(s) Based on the little information available, I would make a lookup field consisting of tablename and primary keys. (although I still believe that storing this information in the database in the first place is probably the wrong approach, but to each his own) / Carsten On 31-05-2013 12:58, Neil Tompkins wrote: The kind of look ups will be trying to diagnose when and by who applied a update. So the primary key of the audit is important. My question is for performance, should the primary key be stored as a indexed field like I mentioned before, or should I have a actual individual field per primary key On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dk wrote: Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11 tel:31-05-2013%2011:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten
RE: Audit Table storage for Primary Key(s)
I'll ask the dumb question. Why not create individual history tables corresponding to your 'main' tables? So, if you have an 'address' table, then the original record could be written to an 'address_his' table via an update or delete trigger (depending on whether you allow deletions or not) when a change is made...and the updated address record would be in the 'address' table. The address_his table would really only need two additional fields to track your data - a user field and a journal date/time. Not sure how you're planning on writing to the changes to your audit table, but this would allow the database to do the work instead of having to write application code to do it. Note: This is based on how I see things for the current application where I work. Doesn't mean that it's right or wrong...it just works for us. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, May 31, 2013 3:43 AM To: [MySQL] Subject: Fwd: Audit Table storage for Primary Key(s) Any advice anyone ? -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Thu, May 30, 2013 at 8:27 AM Subject: Audit Table storage for Primary Key(s) To: [MySQL] mysql@lists.mysql.com Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Audit Table storage for Primary Key(s)
Ah-ha, excuse my earlier response, I was under the impression you were trying to track schema changes etc. A On Fri, May 31, 2013 at 7:54 PM, Rick James rja...@yahoo-inc.com wrote: UUID PRIMARY KEY (or even secondary index) -- Once the table gets big enough (bigger than RAM cache), each row INSERTed (or SELECTed) will be a disk hit. (Rule of Thumb: only 100 hits/sec.) This is because _random_ keys (like UUID) make caching useless. Actually, the slowdown will be gradual. For example, once the table is 5 times as big as the cache, 80% (1-1/5) of the INSERTs/SELECTs will hit disk. Bottom line -- Avoid UUIDs in huge tables, if at all possible. (Exception: The bits in type-1 UUIDs can be rearranged to be roughly chronological.) BIGINT -- You cannot possibly hit its max with any existing hardware. MyISAM -- PRIMARY KEY is just another secondary index. Secondary indexes are separate BTrees. InnoDB -- PRIMARY KEY and data coexist in the same BTree. Secondary indexes are separate BTrees. So, assuming this audit table will be huge (too big to be cached), you need to carefully consider every index, both for writing and for reading. You mentioned that you might audit 50 tables? An index that starts with table_name would be inserting/selecting in 50 spots. If the second part of the index is something 'chronological', such as an AUTO_INCREMENT or TIMESTAMP, then there would be 50 hot spots in the index. This is quite efficient. INDEX(table_name, UUID) would be bad because of the randomness. InnoDB may be the preferred engine, even though the footprint is bigger. This is because careful design of the PK could lead to INSERTs into hot spot(s), plus SELECTs being able to take advantage of locality of reference. With PRIMARY KEY(table_name, ...), and SELECT .. WHERE tablename='...', InnoDB will find all the rows together (fewer disk hits); MyISAM will find the data scattered (more disk hits, hence slower). Another aspect... Would your SELECTs say WHERE ... AND timestamp BETWEEN... ? And, would you _usually_ query _recent_ times? If so, there could be a boost from doing both of these ** PARTITION BY RANGE(TO_DAYS(timestamp)) ** Move timestamp to the _end_ of any indexes that it is in. I would be happy to discuss these principles further. To be able to discuss more specifically, please provide ** Your tentative SHOW CREATE TABLE ** how big you plan for the table to become (#rows or GB), ** how much RAM you have -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Friday, May 31, 2013 4:05 AM Cc: [MySQL] Subject: Re: Audit Table storage for Primary Key(s) Based on the little information available, I would make a lookup field consisting of tablename and primary keys. (although I still believe that storing this information in the database in the first place is probably the wrong approach, but to each his own) / Carsten On 31-05-2013 12:58, Neil Tompkins wrote: The kind of look ups will be trying to diagnose when and by who applied a update. So the primary key of the audit is important. My question is for performance, should the primary key be stored as a indexed field like I mentioned before, or should I have a actual individual field per primary key On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dk wrote: Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11 tel:31-05-2013%2011:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I
Audit Table storage for Primary Key(s)
Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Sveta Smirnova at Mysql just confirmed this bug in 5.5.13: http://bugs.mysql.com/45670 On Wed, Jun 15, 2011 at 5:38 PM, Claudio Nanni claudio.na...@gmail.comwrote: No worries! I think I would have figured that out! I'll feedback you tomorrow. Thanks again Claudio 2011/6/15 Hank hes...@gmail.com Oops... big typo in above steps... add the following line: replicate-ignore-table=db.log to the SLAVE my.cnf, and restart the SLAVE server. The master does not need to be restarted or changed. Just the SLAVE. Sorry about that. -Hank Eskin On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.com wrote: Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio -- Claudio
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Very interesting. Waiting for update. On Jun 15, 2011 4:51 AM, Hank hes...@gmail.com wrote: The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
Oops... big typo in above steps... add the following line: replicate-ignore-table=db.log to the SLAVE my.cnf, and restart the SLAVE server. The master does not need to be restarted or changed. Just the SLAVE. Sorry about that. -Hank Eskin On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.comwrote: Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio
Re: Found serious replication data-corruption bug in 5.5.8/5.5.11 (auto-increment in primary key)
No worries! I think I would have figured that out! I'll feedback you tomorrow. Thanks again Claudio 2011/6/15 Hank hes...@gmail.com Oops... big typo in above steps... add the following line: replicate-ignore-table=db.log to the SLAVE my.cnf, and restart the SLAVE server. The master does not need to be restarted or changed. Just the SLAVE. Sorry about that. -Hank Eskin On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni claudio.na...@gmail.com wrote: Great investigation Hank, congratulations. I will try this tomorrow morning(11:20pm now) and let you know if I can reproduce it on my environments. Thanks! Claudio 2011/6/15 Hank hes...@gmail.com Two additional notes: 1. Using the replicate-wild-ignore-table option in my.cnf produces the same results. 2. If the my.cnf replicate-ignore-table=db.log setting on the master is removed and mysql restarted so db.log is no longer ignored in replication, this bug goes away and correct results are reported on the slave. -Hank Eskin On Wed, Jun 15, 2011 at 4:38 PM, Hank hes...@gmail.com wrote: This is a follow-up to my previous post. I have been narrowing down what is causing this bug. It is a timing issue of a replication ignored table with an auto-increment primary key values leaking over into a non-ignored table with inserts immediately after the ignore table has had rows inserted. Basically, data from the ignored table is corrupting a non-ignored table on the slave upon immediate inserts. Here is how to repeat: On a master issue: use db; drop table test; CREATE TABLE test (id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; drop table log; CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log varchar(20), PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 4; make sure those two tables are created on the slave through regular replication. on slave desc test; desc log; Once replicated, on the master, add the following line to the [mysqld] section of my.cnf, and then restart mysql. replicate-ignore-table=db.log The on the master, issue the following statements as a copy/paste of all of them at once. It's critical that the statements are executed in immediate succession (no delays) insert into log values (null,info1); insert into log values (null,info2); insert into log values (null,info3); insert into log values (null,info4); insert into test values (1,null); insert into log values (null,info5); insert into test values (1,null); insert into log values (null,info6); insert into test values (2,null); insert into log values (null,info7); insert into test values (2,null); insert into log values (null,info8); Here are the results from the master (all correct): masterselect * from log; +---+---+ | id| log | +---+---+ | 4 | info1 | | 5 | info2 | | 6 | info3 | | 7 | info4 | | 8 | info5 | | 9 | info6 | | 44450 | info7 | | 44451 | info8 | +---+---+ masterselect * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | ++-+ Here are the results from the slave: slaveselect * from log; Empty set (0.00 sec) --- as expected, since it is ignored slaveselect * from test; ++---+ | id | cnt | ++---+ | 1 | 7 | -- should be 1, but has values from log on the master | 1 | 8 | -- should be 2 | 2 | 9 | -- should be 1 | 2 | 44450 | -- should be 2 ++---+ If there is the slightest delay between the inserts into log and test, the replication happens correctly. Thoughts? -Hank Eskin -- Claudio -- Claudio
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
You should also have a look at the slave relay log. But in any case sounds like a bug. Claudio On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote: Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.com wrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
That is the slave relay log dump I posted (and mis-labeled). Thanks. -Hank On Tue, Jun 14, 2011 at 2:34 AM, Claudio Nanni claudio.na...@gmail.comwrote: You should also have a look at the slave relay log. But in any case sounds like a bug. Claudio On Jun 14, 2011 5:18 AM, Hank hes...@gmail.com wrote: Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave relay log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
The slave is receiving null as the statement based insert, not an out of range number from the master. I've been doing more research all day on this bug and have a bit more information as to what's causing it. I plan to write it up tomorrow and post it. Basically, everything works perfectly, until I add a replication-ignore-table=xxx statement in my.cnf where xxx is a different table with a unique id INT auto-increment as the single primary key And then the values being inserted into the test table (above, not ignored) represent the last-insert-id of the replication *ignored* table on the slave Yeah, pretty strange, I know. But totally repeatable. -Hank 2011/6/14 Halász Sándor h...@tbbs.net 2011/06/13 22:38 -0400, Hank But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but insert into test values (1,469422). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=hes...@gmail.com
Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | NO | PRI | NULL|| | cnt | int(11) | NO | PRI | NULL| auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | NO | PRI | NULL|| | cnt | int(11) | NO | PRI | NULL| auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt| +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt| +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL|| | cnt | smallint(5) unsigned | NO | PRI | NULL| auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt| +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | ++-+ slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | ++-+ So something in the replication code is munging the 'null' into some random value and trying to insert it. Seems strange that direct statements would work, but replicated statements do not. Nothing really changed on my system, but for some reason, this all started happening about a week or so ago. I've been running this 5.5.8/5.5.11 configuration for months now (since 5.5.8 was released).The PHP code that does this hasn't changed one bit, and this is a simplified version of the database and code that is running in production. Additional note: If I drop the 'id' field, and the primary key is just the auto-increment field, it works correctly in replication. Any ideas? Can anyone else replicate
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | ++-+ slave: insert into test values (1,null); slave: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | | 1 | 2 | ++-+ So something in the replication code is munging the 'null' into some random value and trying to insert it. Seems strange that direct statements would work, but replicated statements do not. Nothing really changed on my system, but for some reason, this all started happening about a week or so ago. I've been running this 5.5.8/5.5.11 configuration for months now (since 5.5.8 was released). The PHP code that does this hasn't changed one bit, and this is a simplified version of the database and code that is running in production. Additional note: If I drop
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master desc test; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | +---+--+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 65535 | should be 1 +++ but this is different: master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | correct! | 1 | 2 | correct! ++-+ slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, missing second record, too ++---+ slave show slave status; Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test values (1,null)' .. at which point I have to restart the slave due to the error: slave SET GLOBAL sql_slave_skip_counter=1; slave start; slave select * from test; ++---+ | id | cnt | ++---+ | 1 | 65535 | should be 1, still missing second record, too (of course) ++---+ Now if I manually replicate the statements just on the slave - it works perfectly: slave: truncate table test; slave: insert into test values (1,null
Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)
Both my master and slave bin logs look OK (I think).. master bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; slave bin log: /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; SET TIMESTAMP=1308012505/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; use test/*!*/; SET TIMESTAMP=1308012505/*!*/; insert into test values (1,null) /*!*/; SET TIMESTAMP=1308012505/*!*/; COMMIT /*!*/; -Hank On Mon, Jun 13, 2011 at 10:38 PM, Hank hes...@gmail.com wrote: Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670 But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: insert into test values (1,null) to replicate, but when it is executed, the null is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. http://bugs.mysql.com/bug.php?id=45670 -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea. Claudio On Jun 14, 2011 3:07 AM, Hank hes...@gmail.com wrote: Hello All, I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 32 and 64-bit slaves (statement based replication). I'm finding an auto-increment field (part of a compound primary key) updates correctly using null to insert the next value on the master.. but when this statement is replicated on the slaves, instead of inserting the next value of the auto-increment field, it inserts 65535 for 'smallint' definitions of 'cnt' and seemingly high random numbers around 469422 for definitions of 'int' or 'bigint'. Easy to repeat: master: CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; master: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- looks good! ++-+ slave: desc test; +---+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+-++ | id | int(11) | NO | PRI | NULL | | | cnt | int(11) | NO | PRI | NULL | auto_increment | +---+-+--+-+-++ slave: select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 +++ But the problem continues... master: insert into test values (1,null); master: select * from test; ++-+ | id | cnt | ++-+ | 1 | 1 | --- correct ! | 1 | 2 | --- correct ! ++-+ slave select * from test; +++ | id | cnt | +++ | 1 | 469422 | should be 1 | 1 | 470673 | should be 2 +++ Now if I repeat the entire scenario using smallint for the 'cnt' field, here are the results: master CREATE TABLE test (id int
How to protect primary key value on a web page?
I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to protect primary key value on a web page?
Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? How do fear your db would be violated? On Mar 10, 2011 6:13 PM, mos mo...@fastmail.fm wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: How to protect primary key value on a web page?
Am 10.03.2011 18:10, schrieb mos: I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? the most sites will handle this by checking permissions security by obscurity is simple crap if i have access to record 738 and get z39 by changing the url your application is simply broken signature.asc Description: OpenPGP digital signature
Re: How to protect primary key value on a web page?
On Thursday 10 March 2011 11:45:27 am Reindl Harald wrote: Am 10.03.2011 18:10, schrieb mos: I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? the most sites will handle this by checking permissions security by obscurity is simple crap if i have access to record 738 and get z39 by changing the url your application is simply broken I think the original poster knows/suspects his application is broken and thats why he's asking. I think he has a case where he allows a user to edit their own records and doesn't have the ability to require a username/password from them, I have a similar situation. What I do is store a random number in their record, which I also include in the url. Access to the record is gained by the combination of id, and tag. Just a thought. -- Take care and have fun, Mike Diehl. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to protect primary key value on a web page?
At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. Mike How do fear your db would be violated? On Mar 10, 2011 6:13 PM, mos mailto:mo...@fastmail.fmmo...@fastmail.fm wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.comhttp://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: How to protect primary key value on a web page?
Am 10.03.2011 21:09, schrieb mos: At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. sorry but this is foolish leave the id in peace and add a colum with some checksum signature.asc Description: OpenPGP digital signature
Re: How to protect primary key value on a web page?
On Mar 10, 2011 9:13 PM, mos mo...@fastmail.fm wrote: At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) Please, google for me I am cooking right now :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. then you have the solution! I actually I am not a GET lover for your same reasons, and I would just store an handle in the cookie and keep all the state in a session on the server. Mike How do fear your db would be violated? On Mar 10, 2011 6:13 PM, mos mailto:mo...@fastmail.fm mo...@fastmail.fm wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. So I need some efficient way of 'cloaking' the real primary key so a hacker won't try to generate random values to access info he shouldn't have access to. How do most web sites handle this? I thought of using UUID_Short() for the primary key instead of an auto-inc, and this isn't really random. It generates near sequential numbers based on time. So I need a way of encrypting the cust_id before sending it to the web page. The user can bookmark this page in his browser so I need to be able to decrypt it back to the real cust_id to retrieve the data. Doing the encryption and decryption is easy enough for me to do on the web server. I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except the string is very long at 64 characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string. Another alternative is to store an MD5 hash value of Cust_Id in the table under a different column Cust_Id_Hash and display that on the web page. So the table joins would still use Cust_Id and Cust_Id_Hash would be used only as a lookup when communicate with the web page. But Innodb's ability to store large random strings will slow down inserts and will consume more disk space. What is the best way to solve the problem? I don't want to re-invent the wheel because I'm sure this problem has been solved by other web developers. Maybe an efficient solution is staring me in the face, so I'm open to some suggestions. :-) TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: How to protect primary key value on a web page?
On Mar 10, 2011 9:23 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 10.03.2011 21:09, schrieb mos: At 12:37 PM 3/10/2011, Claudio Nanni wrote: Hi there, Yes I think its actually a pattern a few hundreds million sites solved already :) Great. How did they do it? :) And any way to encrypt (scramble)the http get string would do. But my question is , are you afraid of sql injection? I'm using parameterized queries and validating user input so SQL injection shouldn't be a problem. I just don't want to give the hacker any more useful information than necessary. Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. The private documents of course need a user name and password to access them, but public documents do not require passwords. So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. sorry but this is foolish leave the id in peace and add a colum with some checksum Wordpress guys are also foolish? They do not even encrypt. And what's the difference between passing in a GET an encrypted Id or passing another column with a checksum deriving from the Id?
Re: How to protect primary key value on a web page?
Am 10.03.2011 21:56, schrieb Claudio Nanni: On Mar 10, 2011 9:23 PM, Reindl Harald h.rei...@thelounge.net wrote: So hashing or encrypting the id column will make the id's non-contiguous and impossible to guess. sorry but this is foolish leave the id in peace and add a colum with some checksum Wordpress guys are also foolish? of course they are look at their awful code you will not really tell me that quality looks like wordpress? And what's the difference between passing in a GET an encrypted Id or passing another column with a checksum deriving from the Id? what exactly do you not understand? fecth the record by its primary key is pretty fast decide the data-output by a checksum which is independent to the key how will you do this any other way? you can not use hash functions because you can not revert them for fetch the record, so you have to use obfusction you can revert to the key and if you can do this anybody will sooner or later signature.asc Description: OpenPGP digital signature
Re: How to protect primary key value on a web page?
On 3/10/2011 12:10, mos wrote: I want to bounce some ideas off of MySQL developers that use it for web development. Maybe I'm a little paranoid, but when dealing with the Internet, I want to make my web app as secure as possible. I'm hoping some of you can offer me some ideas in this respect. I am building a web application that uses MySQL 5.5 with Innodb tables and I don't want the user to see the actual primary key value on the web page. The primary key could be the cust_id, bill_id etc and is usually auto increment. This primary key can appear in the url and will be used to pull up a record and display it on the web page. ... You could follow some of the basic security designs already in use. 1) use https:// 2) Don't worry about the URLs, worry about authenticating the requesting user to the session to the data. Only allow the users access to what they are supposed to have access to in the quantities they are allowed to view it. 3) You could include the session identifier as part of the URL. Once the session expires, that URL is now dead. One of your worries was a BOT coming along and scraping off all of your public files. That's pretty easy to catch if you actively monitor usage patterns. Another way of doing that is to have two unique identifiers for each data object, one is the sequential private number, the other is the non-incremental (random or hash) value that you can expose via URL. It's not really securing anything but it is making it harder for random successes. If they fail to randomly find a valid value enough times, you lock out that IP address. However this really isn't a great topic for a database list as most of solution to your problems reside in how you design your application. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. 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: How to protect primary key value on a web page?
Hi. On Thursday 10 Mar 2011 at 20:09 mos wrote: [snip] Let's say I have a Document_Id column and the url is www.mydocuments.com/public?docid=4 to retrieve document_id=4, I don't want someone to write a program to retrieve all of my public documents and download them. I want them to go through the user interface. Leaving aside the silliness of making a document public then trying to stop people downloading it, there is at least one common solution available to you - Apache's mod_rewrite. http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html Obviously this is dependant on you running Apache, but it is a simple and common approach that will give you what you want. You could also consider rate-limiting your application so that users who request too many pages for your tastes (indicating a possible program) are deliberately slowed down. Beware that this solution will likely have a detrimental effect on search engine spiders, and therefore your site rankings. However, neither of these solutions are appropriate for discussion on a MySQL mailing list, and I agree with many of the other responses you have had - your plan to do this by changing your database is pointless and misdirected. Cheers, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Two Identical Values on Primary Key Column
Hi all, I have just experienced a strange problem with mysql production database. The table faqsessions have a primary key on column `Code` and the above select return 2 rows! mysql mysql mysql show create table faqsessions; +-+--+ | Table | Create Table | +-+--+ | faqsessions | CREATE TABLE `faqsessions` ( `Code` int(11) unsigned NOT NULL auto_increment, `sid` int(11) NOT NULL, `ip` text NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 | +-+--+ 1 row in set (0.00 sec) mysql mysql mysql select * from faqsessions where time in (1268650281, 1268650260); +++--++ | Code | sid | ip | time | +++--++ | 611179 | 312713 | 66.249.68.87 | 1268650281 | | 611179 | 312713 | 66.249.68.89 | 1268650260 | +++--++ 2 rows in set (1.49 sec) mysql mysql mysql select * from faqsessions where Code = 611179; +++--++ | Code | sid | ip | time | +++--++ | 611179 | 312713 | 66.249.68.87 | 1268650281 | +++--++ 1 row in set (0.00 sec) Any idea? Rodrigo Ferreira CMDBA, CMDEV
Re: Two Identical Values on Primary Key Column
Is it possible that someone did an alter table disable keys at some point, maybe for a bulk load, and forgot to re-enable them ? - Original Message - From: Rodrigo Ferreira rodrigof_si...@yahoo.com To: mysql@lists.mysql.com Sent: Wednesday, 2 March, 2011 3:04:31 PM Subject: Two Identical Values on Primary Key Column Hi all, I have just experienced a strange problem with mysql production database. The table faqsessions have a primary key on column `Code` and the above select return 2 rows! mysql mysql mysql show create table faqsessions; +-+--+ +-+--+ `Code` int(11) unsigned NOT NULL auto_increment, `sid` int(11) NOT NULL, `ip` text NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 | +-+--+ 1 row in set (0.00 sec) mysql mysql mysql select * from faqsessions where time in (1268650281, 1268650260); +++--++ +++--++ +++--++ 2 rows in set (1.49 sec) mysql mysql mysql select * from faqsessions where Code = 611179; +++--++ +++--++ +++--++ 1 row in set (0.00 sec) Any idea? Rodrigo Ferreira CMDBA, CMDEV -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Two Identical Values on Primary Key Column
Hi Johan,
It seems InnoDB doesn't support disable/enable keys.
mysql alter table faqsessions enable keys;
Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql show warnings;
+---+--+-+
| Level | Code |
Message |
+---+--+-+
| Note | 1031 | Table storage engine for 'faqsessions' doesn't have this
option |
+---+--+-+
1 row in set (0.00 sec)
mysql alter table faqsessions disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql show warnings;
+---+--+-+
| Level | Code |
Message |
+---+--+-+
| Note | 1031 | Table storage engine for 'faqsessions' doesn't have this
option |
+---+--+-+
1 row in set (0.00 sec)
mysql
--- On Wed, 3/2/11, Johan De Meersman vegiv...@tuxera.be wrote:
From: Johan De Meersman vegiv...@tuxera.be
Subject: Re: Two Identical Values on Primary Key Column
To: Rodrigo Ferreira rodrigof_si...@yahoo.com
Cc: mysql@lists.mysql.com
Date: Wednesday, March 2, 2011, 11:21 AM
#yiv704254679 p {margin:0;}Is it possible that someone did an alter table
disable keys at some point, maybe for a bulk load, and forgot to re-enable them
?
From: Rodrigo Ferreira rodrigof_si...@yahoo.com
To: mysql@lists.mysql.com
Sent: Wednesday, 2 March, 2011 3:04:31 PM
Subject: Two Identical Values on Primary Key Column
Hi all,
I have just experienced a strange problem with mysql production database. The
table faqsessions have a primary key on column `Code` and the above select
return 2 rows!
mysql
mysql
mysql show create table faqsessions;
+-+---
---+
+-+--+
`Code` int(11) unsigned NOT NULL auto_increment,
`sid` int(11) NOT NULL,
`ip` text NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 |
+-+--+
1 row in set (0.00 sec)
mysql
mysqlg
t;
mysql select * from faqsessions where time in (1268650281, 1268650260);
+++--++
+++--++
+++--++
2 rows in set (1.49 sec)
mysql
mysql
mysql select * from faqsessions where Code = 611179;
+++--++
+++--++
+++--++
1 row in set (0.00 sec)
Any idea?
Rodrigo Ferreira
CMDBA, CMDEV
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Fwd: Primary key not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard travis_...@hotmail.com Cc: [MySQL] mysql@lists.mysql.com 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 travis_...@hotmail.com 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
You obviously know best how your application will query your database, and you may have already thought through your indexing strategy. If so, please disregard my comments. In my experience, it is not often you need separate indexes on most or all the columns in a table (excepting very narrow tables, perhaps), so I would think about how you anticipate the database might use each of these indexes. Even though you may have multiple indexes available, most of the time a database query optimizer will only choose one when deciding how to retrieve data for a query. So, if you have a column like first_name that is indexed, your database engine may never use this index unless you have a query like select * from players_master where first_name = 'xyz'. If a column is part of your select list, but is not used as your WHERE clause expression or as part of a table join, indexing that column may not be a benefit. Running EXPLAIN will tell you whether or not the index you anticipate is actually being used for your query. -Travis From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, October 15, 2010 3:43 AM To: [MySQL]; Travis Ard Subject: Fwd: Primary key not unique on InnoDB table Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard travis_...@hotmail.com Cc: [MySQL] mysql@lists.mysql.com 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 travis_...@hotmail.com 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
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´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 neil.tompk...@googlemail.com 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
Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto João Cândido de Souza Neto j...@consultorweb.cnt.br 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 neil.tompk...@googlemail.com 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
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
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 j...@consultorweb.cnt.br Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto João Cândido de Souza Neto j...@consultorweb.cnt.br 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 neil.tompk...@googlemail.com 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
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 neil.tompk...@googlemail.com 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 j...@consultorweb.cnt.br Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto João Cândido de Souza Neto j...@consultorweb.cnt.br 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 neil.tompk...@googlemail.com 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
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 j...@consultorweb.cnt.br 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 neil.tompk...@googlemail.com 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 j...@consultorweb.cnt.br Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto João Cândido de Souza Neto j...@consultorweb.cnt.br 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 neil.tompk...@googlemail.com 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
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
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
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
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
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
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 travis_...@hotmail.com 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
how to get the name of primary key ?
Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
Re: how to get the name of primary key ?
Hi, Use *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()* It will help you. Regards, Thiyaghu CK, MySQL DBA www.mafiree.com 2010/2/1 曹凯 tx...@hotmail.com Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
RE: how to get the name of primary key ?
Hi Thiyaghu, I have already got the last_insert_id. now I wanna know if there are any variables or constants to instead of game_log's primary key cos I don't know its name. Date: Mon, 1 Feb 2010 14:24:59 +0530 Subject: Re: how to get the name of primary key ? From: theyaho...@gmail.com To: tx...@hotmail.com CC: mysql@lists.mysql.com Hi, Use *SELECT * FROM game_log WHERE this_table's_PK = last_insert_id()* It will help you. Regards, Thiyaghu CK, MySQL DBA www.mafiree.com 2010/2/1 曹凯 tx...@hotmail.com Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1 _ 约会说不清地方？来试试微软地图最新msn互动功能！ http://ditu.live.com/?form=TLswm=1
Re: how to get the name of primary key ?
On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY'; +-+ | COLUMN_NAME | +-+ | GameLogID | +-+ 1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper
RE: how to get the name of primary key ?
hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday
Re: how to get the name of primary key ?
u can also get information of table using use use urdbname mysqlshow create table game_log \G 2010/2/1 曹凯 tx...@hotmail.com hi Jesper, thanks a lot! That's what I want. thank u againCC: mysql@lists.mysql.com From: jes...@noggin.com.au Subject: Re: how to get the name of primary key ? Date: Mon, 1 Feb 2010 20:26:36 +1100 To: tx...@hotmail.com On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY';+-+| COLUMN_NAME |+-+| GameLogID | +-+1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper _ Windows Live社区两周年，拿奖过新年！ http://events.livetome.cn/2010/2birthday -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: auto_increment without primary key in innodb?
On Mon, Jan 25, 2010 at 10:08 PM, Yong Lee yong@gogoants.com wrote: yah, mysql only allows one auto increment field n that's used as the primary key in tables. I don't think it has to be the primary key as long as it is a unique key i think that's okay. so u should be able to do : create table (myid int unsigned not null auto_increment., unique key (myid)); but this is effectively a primary key Only mostly true :-) It *is* the same for MyISAM, but for InnoDB the primary key is special, as that is the one that stores the data inline (clustered index). Additional unique keys will only contain a reference to the primary key value for the record. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Fwd: auto_increment without primary key in innodb?
Yeah, Paul... This is so clear...the auto_increment column may be indexed like: - KEY(); - UNIQUE(); - PRIMARY KEY() ...when you create or alter a table. -- Wagner Bianchi 2010/1/25 Paul DuBois paul.dub...@sun.com The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
auto_increment without primary key in innodb?
In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: auto_increment without primary key in innodb ?
it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
2010/1/25 Yang Zhang yanghates...@gmail.com: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. Non-primary key works for me, as documented: --8 mysql create table test_ai (i int PRIMARY KEY, c int auto_increment, index(c)); Query OK, 0 rows affected (0,07 sec) mysql desc test_ai; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | i | int(11) | NO | PRI | NULL|| | c | int(11) | NO | MUL | NULL| auto_increment | +---+-+--+-+-++ 2 rows in set (0,00 sec) mysql insert into test_ai (i) values (100), (200); Query OK, 2 rows affected (0,00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from test_ai; +-+---+ | i | c | +-+---+ | 100 | 1 | | 200 | 2 | +-+---+ 2 rows in set (0,00 sec) --8 Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.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: auto_increment without primary key in innodb?
yah, mysql only allows one auto increment field n that's used as the primary key in tables. I don't think it has to be the primary key as long as it is a unique key i think that's okay. so u should be able to do : create table (myid int unsigned not null auto_increment., unique key (myid)); but this is effectively a primary key if u want some auto incrementing behavior but have it do so only on certain scenarios and possibly hold null values, you can write an insert trigger that would update the field on every insert. Yong. On Mon, 2010-01-25 at 10:21 -0500, Yang Zhang wrote: In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to REPLACE updating when it's a subset of the primary key what is duplicated or inserting otherwise?
Hello I have a table with a compound primary key (a1,a2) and I want to insert a record (b1,b2) in th cases where there's no a1 value matching b1, and if there's already a b1 value in the form (b1,c2) then just update it so that it turns into (b1,b2). So, If I want to insert-update the record (a1,b2),b3 the two cases would be: a) record (a1,a2),a3 exists and has a matching a1 --update-to--(a1,b2),b3 b) there doesn't exist any record matching a1 insert--- (a1,b2),a3 This would be trivial if the primary key was only a1, (REPLACE would do the job) however, I need a2 as a primary key in my model, because it's possible to have different records with the same a1 if they have different a2. I could do this by doing a SELECT on the key, then doing an UPDATE if anything comes back, and INSERT otherwise. But this seems rather clunky, and I'm wondering if there is any other way that's preferred for doing this operation. Thank you very much in advance -- Fernando -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to REPLACE updating when it's a subset of the primary key what is duplicated or inserting otherwise?
Fernando, On Wed, Oct 7, 2009 at 5:08 PM, Fer C. ferk...@gmail.com wrote: Hello I have a table with a compound primary key (a1,a2) and I want to insert a record (b1,b2) in th cases where there's no a1 value matching b1, and if there's already a b1 value in the form (b1,c2) then just update it so that it turns into (b1,b2). Why not use INSERT ON DUPLICATE KEY UPDATE. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html So, If I want to insert-update the record (a1,b2),b3 the two cases would be: a) record (a1,a2),a3 exists and has a matching a1 --update-to-- (a1,b2),b3 b) there doesn't exist any record matching a1 insert--- (a1,b2),a3 So, INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=2,c=3; Ewen This would be trivial if the primary key was only a1, (REPLACE would do the job) however, I need a2 as a primary key in my model, because it's possible to have different records with the same a1 if they have different a2. I could do this by doing a SELECT on the key, then doing an UPDATE if anything comes back, and INSERT otherwise. But this seems rather clunky, and I'm wondering if there is any other way that's preferred for doing this operation. Thank you very much in advance -- Fernando -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@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: How to REPLACE updating when it's a subset of the primary key what is duplicated or inserting otherwise?
Thank you for your fast reply! On Wed, Oct 7, 2009 at 6:25 PM, ewen fortune ewen.fort...@gmail.com wrote: Why not use INSERT ON DUPLICATE KEY UPDATE. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html So, If I want to insert-update the record (a1,b2),b3 the two cases would be: a) record (a1,a2),a3 exists and has a matching a1 --update-to-- (a1,b2),b3 b) there doesn't exist any record matching a1 insert--- (a1,b2),a3 So, INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=2,c=3; That has the same effect as REPLACE for me. The primary key is a,b so, it's possible that there's a (1,4,5) record already in the table that I want updated but INSERT ON DUPLICATE KEY UPDATE will instead insert a (1,2,3) record (the a field is the same but it still has a different b and thus it's a different primary key, so it's not considered a duplicate). -- Fernando -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does InnoDB ever not cluster data by primary key?
Michael, We're counting on batch inserts of constant 2 leftmost columns of the primary key. We would be selecting within constant values for the leftmost columns as well. For example, our primary key is country_id, city_id, auto_inc, ... We'll always be looking for data from within the same country and city. Kyong On Thu, Jul 30, 2009 at 9:44 PM, Michael Dykmanmdyk...@gmail.com wrote: With your auto-increment in the right-most position, it seems to me that it would tend to make your inserts non-sequential (assuming the fields to the left are not sequential) causing inserts to occur all over the tree. With the auto-increment as the first field in the key, the inserts would be going to the same place in the tree allowing it to build out nicely. I have definitely found that sequential inserts perform much better than random ones. - md On Fri, Jul 31, 2009 at 12:25 AM, Kyong Kimkykim...@gmail.com wrote: Michael, Yeah. We're trying to maximize the benefits of clustering and had to sacrifice on the length of the primary key. And we got fairly good results from query profiling using maatkit. One thing that shocked me was the overhead of random inserts primary key updates. It's definitely a tradeoff. We're reasonably certain that we'll see a lot of ordered bulk inserts. It ran counter to the results that we were seeing so I had to verify that InnoDB always clusters by primary key regardless of the position of the auto increment column in the primary key. Kyong On Thu, Jul 30, 2009 at 7:08 PM, Michael Dykmanmdyk...@gmail.com wrote: InnoDb storage format is (always) a b-tree based on the primary key, so the simple answer is: no, InnoDB never clusters by anything other than a primary key. The size of that key can have significant impact on performance though, so be careful with the multi-icolumn primary key. Assuming your primary key remains constant over the lifetime of the record. I don't think it matters much where you put the auto-increment key. - michael On Thu, Jul 30, 2009 at 10:00 PM, Kyong Kimkykim...@gmail.com wrote: We have a multi-column primary key with an auto-increment column as the 3rd column in the primary key in InnoDB. Is there a requirement to have the auto-increment column as the leftmost column in the primary key in order for InnoDB to cluster by the multi-column primary key? I don't believe this to be the case but there has been some discussion on this topic. I haven't been able to find any definitive answers. Judging by the query profiling results and explain output, we are seeing the benefits of clustering by primary key. If you have any insight on this matter, it would be much appreciated. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Does InnoDB ever not cluster data by primary key?
We have a multi-column primary key with an auto-increment column as the 3rd column in the primary key in InnoDB. Is there a requirement to have the auto-increment column as the leftmost column in the primary key in order for InnoDB to cluster by the multi-column primary key? I don't believe this to be the case but there has been some discussion on this topic. I haven't been able to find any definitive answers. Judging by the query profiling results and explain output, we are seeing the benefits of clustering by primary key. If you have any insight on this matter, it would be much appreciated. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does InnoDB ever not cluster data by primary key?
Michael, Yeah. We're trying to maximize the benefits of clustering and had to sacrifice on the length of the primary key. And we got fairly good results from query profiling using maatkit. One thing that shocked me was the overhead of random inserts primary key updates. It's definitely a tradeoff. We're reasonably certain that we'll see a lot of ordered bulk inserts. It ran counter to the results that we were seeing so I had to verify that InnoDB always clusters by primary key regardless of the position of the auto increment column in the primary key. Kyong On Thu, Jul 30, 2009 at 7:08 PM, Michael Dykmanmdyk...@gmail.com wrote: InnoDb storage format is (always) a b-tree based on the primary key, so the simple answer is: no, InnoDB never clusters by anything other than a primary key. The size of that key can have significant impact on performance though, so be careful with the multi-icolumn primary key. Assuming your primary key remains constant over the lifetime of the record. I don't think it matters much where you put the auto-increment key. - michael On Thu, Jul 30, 2009 at 10:00 PM, Kyong Kimkykim...@gmail.com wrote: We have a multi-column primary key with an auto-increment column as the 3rd column in the primary key in InnoDB. Is there a requirement to have the auto-increment column as the leftmost column in the primary key in order for InnoDB to cluster by the multi-column primary key? I don't believe this to be the case but there has been some discussion on this topic. I haven't been able to find any definitive answers. Judging by the query profiling results and explain output, we are seeing the benefits of clustering by primary key. If you have any insight on this matter, it would be much appreciated. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does InnoDB ever not cluster data by primary key?
With your auto-increment in the right-most position, it seems to me that it would tend to make your inserts non-sequential (assuming the fields to the left are not sequential) causing inserts to occur all over the tree. With the auto-increment as the first field in the key, the inserts would be going to the same place in the tree allowing it to build out nicely. I have definitely found that sequential inserts perform much better than random ones. - md On Fri, Jul 31, 2009 at 12:25 AM, Kyong Kimkykim...@gmail.com wrote: Michael, Yeah. We're trying to maximize the benefits of clustering and had to sacrifice on the length of the primary key. And we got fairly good results from query profiling using maatkit. One thing that shocked me was the overhead of random inserts primary key updates. It's definitely a tradeoff. We're reasonably certain that we'll see a lot of ordered bulk inserts. It ran counter to the results that we were seeing so I had to verify that InnoDB always clusters by primary key regardless of the position of the auto increment column in the primary key. Kyong On Thu, Jul 30, 2009 at 7:08 PM, Michael Dykmanmdyk...@gmail.com wrote: InnoDb storage format is (always) a b-tree based on the primary key, so the simple answer is: no, InnoDB never clusters by anything other than a primary key. The size of that key can have significant impact on performance though, so be careful with the multi-icolumn primary key. Assuming your primary key remains constant over the lifetime of the record. I don't think it matters much where you put the auto-increment key. - michael On Thu, Jul 30, 2009 at 10:00 PM, Kyong Kimkykim...@gmail.com wrote: We have a multi-column primary key with an auto-increment column as the 3rd column in the primary key in InnoDB. Is there a requirement to have the auto-increment column as the leftmost column in the primary key in order for InnoDB to cluster by the multi-column primary key? I don't believe this to be the case but there has been some discussion on this topic. I haven't been able to find any definitive answers. Judging by the query profiling results and explain output, we are seeing the benefits of clustering by primary key. If you have any insight on this matter, it would be much appreciated. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MAC address as primary key - BIGINT or CHAR(12)
Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia
Re: MAC address as primary key - BIGINT or CHAR(12)
Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thursday 14 May 2009 09:53:58 am Fish Kungfu wrote: Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia And, if you use default charsets of anything else, make sure you set this column to CHARACTER SET ascii. A smaller index is a happier index. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
You might even want to plan for longer MAC addresses. I don't follow developments in that area, but they had to go from IP4 to IP6 and they might have to introduce longer MAC addresses. It isn't hard to change a MySQL field definition, but your applications would be more of a problem. -Original Message- From: Ilia KATZ [mailto:ik...@dane-elec.co.il] Sent: Thursday, May 14, 2009 9:26 AM To: mysql@lists.mysql.com Subject: MAC address as primary key - BIGINT or CHAR(12) Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
Not exactly. The allowed letters are A,B,C,D, E, F. Every 2 characters (not including separators) can be treated as a hexadecimal number that can be represented with one byte. 6 bytes in total. for example: 00:1D:7D:48:08:8F pair value 00 0 1st byte 1D29 2nd byte 7D125 3rd byte 48 72 4th byte 08 8 5th byte 8F143 6th byte the last 2 bytes (of the BIGINT) left unused. Ilia From: Fish Kungfu [mailto:fish.kun...@gmail.com] Sent: Thursday, May 14, 2009 3:54 PM To: Ilia KATZ Cc: mysql@lists.mysql.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals computer viruses.
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thu, 14 May 2009, Ilia KATZ wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia Hi It depends. You may convert the MAC address to a decimal integer and store it as a BIGINT. Use UNSIGNED as well; there are no negative numbers involved. This may gain some speed and saves storage space. The drawback I can see is that these numbers are not human readable, but you may convert back to HEX when retrieving data. And it may break when they start using larger MAC addresses eventually. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MAC address as primary key - BIGINT or CHAR(12)
Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MAC address as primary key - BIGINT or CHAR(12)
I'm new to MySQL so can't answer the OP's question, but: MAC addresses do not by design contain letters. Native MAC addresses are 48-bit (6-byte) integers: http://standards.ieee.org/getieee802/download/802-2001.pdf The confusion arises because a MAC address is usually /represented/ as hexadecimal, and that might contain letters, but MAC addresses natively are pure 6-byte integers. So the issue is whether you want to index by a 48-bit number or a 12-character ascii string. For efficiency's sake, I'm guessing you'd choose the former. I'll be interested in the answer, though, from someone with experience. -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) To: Ilia KATZ ik...@dane-elec.co.il Cc: mysql@lists.mysql.com Date: Thursday, May 14, 2009, 11:38 AM Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.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: MAC address as primary key - BIGINT or CHAR(12)
As I said in my post, this is a general principle for me. Nominal data should have a data type of some sort of character. You will never run into unexpected problems down the line. On Thu, May 14, 2009 at 11:04 AM, Pete Wilson pete...@yahoo.com wrote: I'm new to MySQL so can't answer the OP's question, but: MAC addresses do not by design contain letters. Native MAC addresses are 48-bit (6-byte) integers: http://standards.ieee.org/getieee802/download/802-2001.pdf The confusion arises because a MAC address is usually /represented/ as hexadecimal, and that might contain letters, but MAC addresses natively are pure 6-byte integers. So the issue is whether you want to index by a 48-bit number or a 12-character ascii string. For efficiency's sake, I'm guessing you'd choose the former. I'll be interested in the answer, though, from someone with experience. -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) To: Ilia KATZ ik...@dane-elec.co.il Cc: mysql@lists.mysql.com Date: Thursday, May 14, 2009, 11:38 AM Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MAC address as primary key - BIGINT or CHAR(12)
I agree, and didn't mean to say that I disagreed. This is certainly one of the top five principles to follow, imo. Too many times, while trouble-shooting, I've run up on the rock of a binary (meaning: indecipherable) field. What is the cost of including the binary representation (for indexing) and also the readable representation (just for debugging/problem-solving)? (Well, of course I know what the cost is: it's 12 bytes, plus overhead, per row.) It all depends, but in general, would you call that too costly, given the benefit? -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com As I said in my post, this is a general principle for me. Nominal data should have a data type of some sort of character. You will never run into unexpected problems down the line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
(.) and - characters only) and
* it does not conatain a period (yes, this is contrary to the previous
statement)
*
* @access public
* @paramstring $hostname
* @return boolean
* @since4.2.6
* @date 7/11/2007
*/
function isValidHostnameRFC952( $hostname )
{
return ( isValidDomain( $hostname ) ( preg_match( '/^\d/',
$hostname) == 0 ) ( strlen( $hostname ) = 60 ) );
}
/**
* Will return true if the value passed in contains only the characters A-Z,
0-9, period (.) and hyphen (-)
* Any sub part of the domain, like www, google and com cannot start or end
with a hyphen. While I believe that
* you shouldn't be allowed to have a domain starting with a numeric, that
seems to be allowed now with domains
* such as www.123.com, which means that a hostname can also contain that.
Each part of a domain can only be 63
* characters long.
*
* @param string $value The domain to test
* @return boolean
* @since 4.5.5
* @date 7/11/2007
* @access public
* @author Adam Randall adam.rand...@lockdownnetworks.com
*/
function isValidDomain( $value = '' )
{
foreach( explode( '.', strtolower( $value ) ) as $i )
{
if( ( strlen( $i ) == 0 ) || ( strlen( $i ) 63 ) || (
$i[0] === '-' ) || ( $i[ strlen( $i ) - 1 ] === '-' ) || ( preg_match(
'/^[a-z0-9\-]+$/', $i ) == 0 ) )
return( false );
}
return true;
}
/**
* Returns true if the string is a wildcard IP (e.g. 192.*, 192.168.*,
192.168.1.*).
*
* @access public
* @paramstring $ip
* @return boolean
* @since4.2.6
* @date 10/05/2006
*/
function isValidWildcardIP( $ip )
{
return ( 1 == preg_match( /((\d{1,3})\.){1,3}\*/, $ip ) );
}
/**
* Converts a wildcard IP (e.g. 192.*, 192.168.*, 192.168.1.*) to a CIDR
form.
*
* @access public
* @paramstring $ip
* @return string
* @since4.2.6
* @date 10/05/2006
*/
function wildcardIP2CIDR( $ip )
{
$result = ;
if ( isValidWildcardIP( $ip ) )
{
$parts = explode( ., $ip );
unset( $parts[ count( $parts ) - 1 ] ); // Get rid of the
asterisk.
$cidr_bits = count( $parts ) * 8;
for ( $i = 3; $i = 0; $i-- )
{
if ( !isset( $parts[ $i ] ) )
{
$parts[ $i ] = '0';
}
else
{
break;
}
}
$temp = implode( '.', $parts );
// We should have an IP like 192.168.0.0 now. Validate it.
if ( false !== ip2long( $temp ) )
{
$result = $temp./.$cidr_bits;
}
}
return $result;
}
/**
* Returns true if the string is a valid CIDR.
*
* @access public
* @paramstring $cidr
* @return boolean
* @since4.2.6
* @date 10/05/2006
*/
function isValidCIDR( $ip )
{
$parts = explode( /, $ip );
if ( 2 != count( $parts ) ) return false;
$cidr_bits = -1;
if ( 1 == preg_match( /^[0-9]+$/, $parts[ 1 ] ) )
{
$temp = intval( $parts[ 1 ] );
if ( ( $temp = 0 ) ( $temp = 32 ) )
{
$cidr_bits = $temp;
}
}
return ( ( $cidr_bits = 0 ) isDottedQuad( $parts[ 0 ] ) );
}
/**
* Returns true if the string is a valid MAC
*
* @access public
* @paramstring $mac
* @return boolean
*/
function isValidMAC( $mac )
{
return
preg_match(/^([0-9A-Fa-f]{2}):([0-9A-Fa-f]{2}):([0-9A-Fa-f]{2}):([0-9A-Fa-f
]{2}):([0-9A-Fa-f]{2}):([0-9A-Fa-f]{2})$/,$mac);
}
/**
* Returns true if the string is a valid NetBIOS name.
*
* @access public
* @paramstring $mac
* @return boolean
*/
function isValidNetBIOS( $name )
{
// See http://support.microsoft.com/kb/q188997/
return preg_match( /^[0-9a-za-...@#\$%\^\(\)\-'\{\}\.\~]{1,15}$/,
$name );
}
?
--- 8
---
-Original Message-
From: Ilia KATZ [mailto:ik...@dane-elec.co.il]
Sent: Thursday, May 14, 2009 6:26 AM
To: mysql@lists.mysql.com
Subject: MAC address as primary key - BIGINT or CHAR(12)
Hi.
Currently I have a table:
1. MAC address defined as BIGINT
2. MAC address set as primary key
Should I consider changing it to CHAR(12)?
Replies will be appreciated.
Ilia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
A MAC address is just a number, it doesn't contain letters unless you're doing something silly like storing the HEX representation of it. Do not use CHAR! This does DOUBLE for all of you storing IP addresses! Since a MAC address is going to be between 48 and 64 bits, then BIGINT is appropriate. When you select the value you can do SELECT HEX(mac) FROM table; to get a more readable version of it. When you're storing values you can do: INSERT INTO table (mac) VALUES (x'FFEEDDCCBBAA9988'); to convert a hex string to the numeric value. Date: Thu, 14 May 2009 09:53:58 -0400 To: Ilia KATZ ik...@dane-elec.co.il From: Fish Kungfu fish.kun...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) Message-ID: f76e38f90905140653o4f6513aft103e8c3b526b3...@mail.gmail.com --001636e90cddd7f9c70469dfa8fe Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: MAC address as primary key - BIGINT or CHAR(12)
I've run up on the rock of a binary (meaning: indecipherable) field. SELECT hex(some_binary_field) FROM table; Solved. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: How to generate unique primary key in MySQL?
If you just don't want a primary key in your major data tables, then create a table for the express purpose of generating primary keys that uses auto_increment. Something like: create table myseq (x serial;) Each time you need a new key, get the next value from that table. It would be more like an Oracle sequence that an auto_increment. On Mon, Apr 27, 2009 at 11:59 PM, yuan edit edit.y...@gmail.com wrote: I have a shopping cart table like this: CREATE TABLE shopping_cart( id VARCHAR(20) NOT NULL, product_id INT NOT NULL, product_quantity INT NOT NULL, ... ... user_id INT NOT NULL, current_timestamp TIMESTAMP, primary key (id) ); I will not use auto_increment Is there other way to generate unique primary key in MySQL? Thank you -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How to generate unique primary key in MySQL?
I wish my id has the same length,auto_increment can do this? I have a idear to generate unique primary key: select concat(cast(unix_timestamp() as char) , cast(substr(rand(),3,4) as char(4))); Is this ok? any good idear? Your routine does not really guarantee uniqueness, If you mean that you want your keys to have the same display length, then the 36 character string produced by UUID() satisfies this. Alternatively, and much simpler, is to use the auto_increment which will give you the efficiency of an integer primary key, When you want to display it format it for display using LPAD. ie.: mysql SELECT lpad(5,8,'0'); +---+ | lpad(5,8,'0') | +---+ | 0005 | +---+ -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to generate unique primary key in MySQL?
I have a shopping cart table like this: CREATE TABLE shopping_cart( id VARCHAR(20) NOT NULL, product_id INT NOT NULL, product_quantity INT NOT NULL, ... ... user_id INT NOT NULL, current_timestamp TIMESTAMP, primary key (id) ); I will not use auto_increment Is there other way to generate unique primary key in MySQL? Thank you
Re: How to generate unique primary key in MySQL?
BTW,i am using MySQL 5.0
Re: How to generate unique primary key in MySQL?
Ok, I will ask the obvious question: why do you refuse to use auto_increment? If this was Oracle or Postgresql, of course we would use sequences, but that isn't available in MySQL. Personally, I would not go to Rome to order the sushi. However, there is the function uuid() which can be used ie. SELECT uuid(); and produces a guaranteed unique 36 character sitrng, but this might not be very efficient in joins as your dataset grows. - michael dykman On Tue, Apr 28, 2009 at 12:59 AM, yuan edit edit.y...@gmail.com wrote: I have a shopping cart table like this: CREATE TABLE shopping_cart( id VARCHAR(20) NOT NULL, product_id INT NOT NULL, product_quantity INT NOT NULL, ... ... user_id INT NOT NULL, current_timestamp TIMESTAMP, primary key (id) ); I will not use auto_increment Is there other way to generate unique primary key in MySQL? Thank you -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- 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 / foreign key question
Ok, I'm a little new a this, so be gentle!! :) I was looking into the InnoDB engine for some tables I have, and would like to use the PK/FK on some of the data. It appears that the PK/FK is mainly used for updating/deleting data, correct? I can't use it to retreive data from multiple tables and have them linked, other than a join? Or am I wrong? Is there a good visual tool that I can use to build my tables, create the PK/FK definations, and test how inserts, deletes, updates affect all the tables involved?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary key / foreign key question
Hello Steve, Ok, I'm a little new a this, so be gentle!! :) I was looking into the InnoDB engine for some tables I have, and would like to use the PK/FK on some of the data. It appears that the PK/FK is mainly used for updating/deleting data, correct? I can't use it to retreive data from multiple tables and have them linked, other than a join? Or am I wrong? It's mainly used for generating exceptions when the data is about to become invalid if you delete something that is used by another table. You can set it to automatically delete child-data or prevent these deletions so that the data in your database stays consistent. When retrieving data, you need to JOIN tables yourself into a result set. Alternatively, you can create a server side object called a VIEW that is basically a result set that looks like a single table. Is there a good visual tool that I can use to build my tables, create the PK/FK definations, and test how inserts, deletes, updates affect all the tables involved?? What OS? For example, on Windows, we offer a free Lite version of our developer tool Database Workbench. Martijn Tonies Database Workbench Lite for MySQL - FREE developer tool for MySQL! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select does too much work to find rows where primary key does not match
Hi Patrick, all ! Patrick J. McEvoy wrote: I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows where the primary keys match is efficient: mysql explain select bar.phone from foo,bar where foo.phone=bar.phone; [[...]} Ok, let us take some simple example. Say tables foo and bar both have three rows each, with phone values 1, 2, and 3. Then your matching lines will be: foo.phone bar.phone 1 1 2 2 3 3 Column foo.phone is shown for explanation only, your select would not return it. Each individual value in bar.phone will be returned as often as there is an identical value in foo.phone. I trust that is close to what you expect. My sample data here do not show what will happen if values in foo.phone are not distinct - figure yourself. Finding rows in one table that do not match a row in the other table is wildly inefficient: mysql explain select bar.phone from foo,bar where foo.phone!=bar.phone; [[...]] Your resulting data from this select would be: foo.phone bar.phone 1 2 1 3 2 1 2 3 3 1 3 2 Again, foo.phone is shown for explanation only. (This is the same for 'NOT', '!=', or ''.) Correct. The amount of work should be identical in both cases: grab a row, look up by primary key in the other table, proceed. No, it isn't: A select ... from foo, bar where CONDITION effectively creates the cartesian product of both tables and then removes all lines (combinations) which do not meet the condition. Of course, the system uses better strategies if possible, evaluating indexes etc, but the resulting data will be the same. Assuming tables foo and bar each have a column num with the values 1 to 100, a condition ... where foo.num = bar.num will lead to a result with 100 rows. But ... where foo.num != bar.num will lead to a table of 9,900 rows: For each of the 100 values in foo.num, there will be 99 entries in bar.num that satisfy the inequality condition. My real goal is to delete rows in the smaller table if there is no match in the larger table: delete from bar using foo,bar where not bar.phone=foo.phone; See above - wrong approach. What you need is a subquery or an outer join, as proposed in the other replies. [[...]] HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select does too much work to find rows where primary key does not match
I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows where the primary keys match is efficient: mysql explain select bar.phone from foo,bar where foo.phone=bar.phone; ++-+---++---+- +-+---+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+- +-+---+---+-+ | 1 | SIMPLE | bar | index | PRIMARY | PRIMARY | 10 | NULL | 77446 | Using index | | 1 | SIMPLE | foo | eq_ref | PRIMARY | PRIMARY | 10 | ssa.bar.phone | 1 | Using index | ++-+---++---+- +-+---+---+-+ 2 rows in set (0.00 sec) Finding rows in one table that do not match a row in the other table is wildly inefficient: mysql explain select bar.phone from foo,bar where foo.phone!=bar.phone; ++-+---+---+---+-+- +--+-+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra| ++-+---+---+---+-+- +--+-+--+ | 1 | SIMPLE | bar | index | NULL | PRIMARY | 10 | NULL | 77446 | Using index | | 1 | SIMPLE | foo | index | NULL | PRIMARY | 10 | NULL | 3855468 | Using where; Using index | ++-+---+---+---+-+- +--+-+--+ 2 rows in set (0.00 sec) (This is the same for 'NOT', '!=', or ''.) The amount of work should be identical in both cases: grab a row, look up by primary key in the other table, proceed. My real goal is to delete rows in the smaller table if there is no match in the larger table: delete from bar using foo,bar where not bar.phone=foo.phone; but it runs for hours. I suppose I could SELECT INTO a new table and rename the tables, but that seems dorky. Is there any way to force SELECT/DELETE to look up the primary key rather than scan the entire index? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select does too much work to find rows where primary key does not match
I would have thought your not = though is matching a lot more rows every time.. I would look into using where not exists as a subselect delete from bar where not exists (select 'y' from foo where foo.phone = bar.phone); something like that. On Tue, Apr 15, 2008 at 5:00 PM, Patrick J. McEvoy [EMAIL PROTECTED] wrote: I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows where the primary keys match is efficient: mysql explain select bar.phone from foo,bar where foo.phone=bar.phone; ++-+---++---+-+-+---+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+-+-+---+---+-+ | 1 | SIMPLE | bar | index | PRIMARY | PRIMARY | 10 | NULL | 77446 | Using index | | 1 | SIMPLE | foo | eq_ref | PRIMARY | PRIMARY | 10 | ssa.bar.phone | 1 | Using index | ++-+---++---+-+-+---+---+-+ 2 rows in set (0.00 sec) Finding rows in one table that do not match a row in the other table is wildly inefficient: mysql explain select bar.phone from foo,bar where foo.phone!=bar.phone; ++-+---+---+---+-+-+--+-+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra| ++-+---+---+---+-+-+--+-+--+ | 1 | SIMPLE | bar | index | NULL | PRIMARY | 10 | NULL | 77446 | Using index | | 1 | SIMPLE | foo | index | NULL | PRIMARY | 10 | NULL | 3855468 | Using where; Using index | ++-+---+---+---+-+-+--+-+--+ 2 rows in set (0.00 sec) (This is the same for 'NOT', '!=', or ''.) The amount of work should be identical in both cases: grab a row, look up by primary key in the other table, proceed. My real goal is to delete rows in the smaller table if there is no match in the larger table: delete from bar using foo,bar where not bar.phone=foo.phone; but it runs for hours. I suppose I could SELECT INTO a new table and rename the tables, but that seems dorky. Is there any way to force SELECT/DELETE to look up the primary key rather than scan the entire index? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com !
Re: select does too much work to find rows where primary key does not match
How about using a left outer join. Find all the rows in bar without a matching row in foo: To verify: select * from bar left outer join foo on bar.phone=foo.phone where foo.phone is null Then delete bar.* from bar left outer join foo on bar.phone=foo.phone where foo.phone is null Phil [EMAIL PROTECTED] wrote on 04/15/2008 05:32:38 PM: I would have thought your not = though is matching a lot more rows every time.. I would look into using where not exists as a subselect delete from bar where not exists (select 'y' from foo where foo.phone = bar.phone); something like that. On Tue, Apr 15, 2008 at 5:00 PM, Patrick J. McEvoy [EMAIL PROTECTED] wrote: I have two MyISAM tables; each uses 'phone' as a primary key. Finding rows where the primary keys match is efficient: mysql explain select bar.phone from foo,bar where foo.phone=bar.phone; ++-+---++---+- +-+---+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---++---+- +-+---+---+-+ | 1 | SIMPLE | bar | index | PRIMARY | PRIMARY | 10 | NULL | 77446 | Using index | | 1 | SIMPLE | foo | eq_ref | PRIMARY | PRIMARY | 10 | ssa.bar.phone | 1 | Using index | ++-+---++---+- +-+---+---+-+ 2 rows in set (0.00 sec) Finding rows in one table that do not match a row in the other table is wildly inefficient: mysql explain select bar.phone from foo,bar where foo.phone!=bar.phone; ++-+---+---+---+- +-+--+-+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra| ++-+---+---+---+- +-+--+-+--+ | 1 | SIMPLE | bar | index | NULL | PRIMARY | 10 | NULL | 77446 | Using index | | 1 | SIMPLE | foo | index | NULL | PRIMARY | 10 | NULL | 3855468 | Using where; Using index | ++-+---+---+---+- +-+--+-+--+ 2 rows in set (0.00 sec) (This is the same for 'NOT', '!=', or ''.) The amount of work should be identical in both cases: grab a row, look up by primary key in the other table, proceed. My real goal is to delete rows in the smaller table if there is no match in the larger table: delete from bar using foo,bar where not bar.phone=foo.phone; but it runs for hours. I suppose I could SELECT INTO a new table and rename the tables, but that seems dorky. Is there any way to force SELECT/DELETE to look up the primary key rather than scan the entire index? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com ! -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
