RE: a
d > -Original Message- > From: ryan.esca...@gmail.com [mailto:ryan.esca...@gmail.com] On Behalf > Of Ryan Escarez > Sent: Friday, October 16, 2015 2:47 AM > To: Ryan Coleman > Cc: Dennis Ruiz; mysql-le...@lists.mysql.com > Subject: Re: a > > c > > On Fri, Oct 16, 2015 at 3:01 PM, Ryan Coleman> wrote: > > > b > > > > > On Oct 15, 2015, at 10:07 PM, Dennis Ruiz wrote: > > > > > > a > > > > > > -- > > > 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 > > > > > > > -- > Ryan Escarez > CEBB F1E4 1E39 EC48 F05D 6B72 9C11 DD88 5E39 E471 $ gpg --keyserver > pgp.mit.edu --recv-keys 0x5E39E471 main(k){float i,j,r,x,y=- > 16;while(puts(""),y++<15) > for(x=0;x++<84;putchar(" .:-;!/>)|%*#"[k&15])) for(i=k=r=0;j=r*r-i*i- > 2+x/25,i=2*r*i+y/10,j*j+i*i<11&++<111;r=j);} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How do I mysqldump different database tables to the same .sql file?
Except that it outputs the USE statement if you have more than one database, so your theory doesn't hold a lot of water IMHO. Not to mention it's near the very top of the output so it's pretty easy to trim it off if you REALLY needed to move the DB (which I presume is not as frequently as simply wanting a backup/dump of a database to restore). Thanks for the shell script suggestion, that is what I've done already to work around this silliness. -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Thursday, November 21, 2013 1:59 PM To: MySql Subject: Re: How do I mysqldump different database tables to the same .sql file? There is a good reason that the USE database is not output in those dumps.. it would make the tool very difficult to use for moving data around. If I might suggest, a simple workaround is to create a shell script along these lines.. you might to do something a little more sophisticated. # #!/bin/sh echo USE `database1`; outflfile.sql mysqldump -(firstsetofoptions) outfile.sql echo USE `database2`; outflfile.sql mysqldump -(secondsetofoptions) outfile.sql On Thu, Nov 21, 2013 at 4:44 PM, Daevid Vincent dae...@daevid.com wrote: I'm working on some code where I am trying to merge two customer accounts (we get people signing up under different usernames, emails, or just create a new account sometimes). I want to test it, and so I need a way to restore the data in the particular tables. Taking a dump of all the DBs and tables is not feasible as it's massive, and importing (with indexes) takes HOURS. I just want only the tables that are relevant. I can find all the tables that have `customer_id` in them with this magic incantation: SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME` Then I crafted this, but it pukes on the db name portion. :-( mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --tables member_sessions.users_last_login support.tickets mydb1.clear_passwords mydb1.crak_subscriptions mydb1.customers mydb1.customers_free mydb1.customers_free_tracking mydb1.customers_log mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players mydb1content.actors_comments mydb1content.actor_collections mydb1content.actor_likes_users mydb1content.collections mydb1content.dvd_likes_users mydb1content.free_videos mydb1content.genre_collections mydb1content.playlists mydb1content.poll_votes mydb1content.scenes_comments mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections mydb1content.scene_likes_users mydb1content.videos_downloaded mydb1content.videos_viewed merge_backup.sql -- Connecting to localhost... mysqldump: Got error: 1049: Unknown database 'member_sessions.users_last_login' when selecting the database -- Disconnecting from localhost... I searched a bit and found that it seems I have to split this into multiple statements and append like I'm back in 1980. *sigh* mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database member_sessions --tables users_last_login merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database support --tables tickets merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database mydb1 --tables clear_passwords customers customers_free customers_free_tracking customers_log customers_subscriptions customers_transactions players merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database content --tables actors_comments actor_collections actor_likes_users collections dvd_likes_users free_videos genre_collections playlists poll_votes scenes_comments scenes_ratings_users_new2 scene_collections scene_likes_users videos_downloaded videos_viewed merge_backup.sql The critical flaw here is that the mysqldump program does NOT put the necessary USE DATABASE statement in each of these dumps since there is only one DB after the -database apparently. UGH. Nor do I see a command line option to force it to output this seemingly
How do I mysqldump different database tables to the same .sql file?
I'm working on some code where I am trying to merge two customer accounts (we get people signing up under different usernames, emails, or just create a new account sometimes). I want to test it, and so I need a way to restore the data in the particular tables. Taking a dump of all the DBs and tables is not feasible as it's massive, and importing (with indexes) takes HOURS. I just want only the tables that are relevant. I can find all the tables that have `customer_id` in them with this magic incantation: SELECT `TABLE_NAME`,`TABLE_SCHEMA` FROM `information_schema`.`COLUMNS` WHERE `COLUMN_NAME` = 'customer_id' ORDER BY `TABLE_SCHEMA`, `TABLE_NAME` Then I crafted this, but it pukes on the db name portion. :-( mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --tables member_sessions.users_last_login support.tickets mydb1.clear_passwords mydb1.crak_subscriptions mydb1.customers mydb1.customers_free mydb1.customers_free_tracking mydb1.customers_log mydb1.customers_subscriptions mydb1.customers_transactions mydb1.players mydb1content.actors_comments mydb1content.actor_collections mydb1content.actor_likes_users mydb1content.collections mydb1content.dvd_likes_users mydb1content.free_videos mydb1content.genre_collections mydb1content.playlists mydb1content.poll_votes mydb1content.scenes_comments mydb1content.scenes_ratings_users_new2 mydb1content.scene_collections mydb1content.scene_likes_users mydb1content.videos_downloaded mydb1content.videos_viewed merge_backup.sql -- Connecting to localhost... mysqldump: Got error: 1049: Unknown database 'member_sessions.users_last_login' when selecting the database -- Disconnecting from localhost... I searched a bit and found that it seems I have to split this into multiple statements and append like I'm back in 1980. *sigh* mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database member_sessions --tables users_last_login merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database support --tables tickets merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database mydb1 --tables clear_passwords customers customers_free customers_free_tracking customers_log customers_subscriptions customers_transactions players merge_backup.sql mysqldump -uroot -proot --skip-opt --add-drop-table --extended-insert --complete-insert --insert-ignore --create-options --quick --force --set-charset --disable-keys --quote-names --comments --verbose --database content --tables actors_comments actor_collections actor_likes_users collections dvd_likes_users free_videos genre_collections playlists poll_votes scenes_comments scenes_ratings_users_new2 scene_collections scene_likes_users videos_downloaded videos_viewed merge_backup.sql The critical flaw here is that the mysqldump program does NOT put the necessary USE DATABASE statement in each of these dumps since there is only one DB after the -database apparently. UGH. Nor do I see a command line option to force it to output this seemingly obvious statement. It's a pretty significant shortcoming of mysqldump if you ask me that I can't do it the way I had it in the first example since that's pretty much standard SQL convetion of db.table.column format. And even more baffling is why it wouldn't dump out the USE statement always even if there is only one DB. It's a few characters and would save a lot of headaches in case someone tried to dump their .sql file into the wrong DB on accident. Plus it's not easy to edit a 2.6GB file to manually insert these USE lines. Is there a way to do this with some command line option I'm not seeing in the man page?
Why is mySQL not respecting foreign characters as different
How come MySQL is not differentiating between these characters? SELECT text_id, us, de, es, fr FROM texts WHERE us = fr; Results in matching here. Notice the difference in the scene vs scène text_id us es de fr -- -- -- - all_page_scene scene escena Filmszene scène I wold expect this NOT to match. Do I have to add something to my query to tell MySQL to respect other character sets as different? CREATE TABLE `texts` ( `text_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', `us` text, `es` text, `de` text, `fr` text, PRIMARY KEY (`text_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FieldType Collation NullKey Default Extra Privileges Comment --- --- - -- -- --- -- --- - text_id varchar(50) latin1_general_ci NO PRI select,insert,update,references us text utf8_general_ciYES (NULL) select,insert,update,references es text utf8_general_ciYES (NULL) select,insert,update,references de text utf8_general_ciYES (NULL) select,insert,update,references fr text utf8_general_ciYES (NULL) select,insert,update,references -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Google swaps out MySQL, moves to MariaDB
http://www.theregister.co.uk/2013/09/12/google_mariadb_mysql_migration/
#1341 [Com]: InnoDB ibdata1 never shrinks after data is removed
For 10 YEARS we have been asking for a way to reclaim the ibdata files (or even .MYD files) and finally someone from mysql/oracle replied. It's not great news, but at least they acknowledge and give some explanations. -Original Message- From: Bug Database [mailto:do-not-re...@mysql.com] Sent: Tuesday, July 23, 2013 2:30 PM Subject: #1341 [Com]: InnoDB ibdata1 never shrinks after data is removed View this bug at: http://bugs.mysql.com/1341 Updated by: James Day Reported by: Scott Ellsworth Category: Server: InnoDB Severity: S4 (Feature request) Status: Verified Version: All versions OS: Any Defect Class: D5 (Feature request) [23 Jul 21:29] James Day Ten years is because we know it is desired but it is a hard problem. To free the space takes moving lots of internal pointers and data. That is almost as much work as alter table, but in theory it could be done gradually in the background. But would be very complicated with high chance for bugs and performance problems due to the locking needed. With 5.6 the best combination that is implemented so far is: 1. Innodb_file_per_table, on by default in 5.6. Prevents data from going to shared tablespace. 2. Undo logs in their own tablespace, see http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html Separate Tablespaces for InnoDB Undo Logs . This will prevent undo logs from causing the shared tablespace to grow, but there is still no way to shrink the undo logs. 3. Shared tablespace then contains data dictionary, change buffer and statistics, so is much less likely to grow to big sizes. 4. For some workloads the compressed tables feature will help to reduce data sizes and hence the times. Still some way to go on performance of this for OLTP but it's way better in 5.6 than 5.5, in part due to many improvements suggested by Facebook. Best used in general for tables that don't have the highest change rates but do have big column sizes that compress well. This definitely does not solve all problems: 5. The ibd files can have free space and the only way to release that is slow alter table or truncate. Slow for big tables, not practical on a live server. But is practical on a system that has failover capability. Can take a window of slow failover time and do it during this window with a known risk that there might be slower failover than usual. If you do this, drop all the non-unique secondary indexes then add them back later, the fast index recreate will save you time and rebuild the indexes with less free space inside the pages. 6. The portable tablespaces feature in 5.6 could then be used to load the tablespaces into a new copy of Mysql with small shared tablespace and redo log. This still requires downtime, so still requires a failover solution, but it's far faster for big tables than mysqldump and reload. 7. Dropping tables should be faster in 5.6 and 5.5.20 or later but it's still going to be slower using innodb_file_per_table than having the tables in the shared tablespace. More practical for big tables that aren't dropped often than for small tables that are very regularly dropped. For the big/infrequent combination, most people already use innodb_file_per_table. The work on this in bug http://bugs.mysql.com/bug.php?id=51325 helps a lot but there's still scope for more. So we know it's desirable, some major improvements that help the workarounds, but still no way to make it practical online. For now, failover based workarounds are still the way to go. Not ideal, but at least doable. James Day, MySQL Senior Principal Support Engineer, Oracle Earlier comments can be viewed at http://bugs.mysql.com/1341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Need query to determine different column definitions across tables
I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name). For example in one table `foo_id` might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid latin1_swedish_ci and fixed to be utf8 in others. Stuff like that. I want to see everything where there is some difference.
RE: Need query to determine different column definitions across tables
-Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, July 08, 2013 2:11 PM To: mysql@lists.mysql.com Subject: Need query to determine different column definitions across tables I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name). For example in one table `foo_id` might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid latin1_swedish_ci and fixed to be utf8 in others. Stuff like that. I want to see everything where there is some difference. Well, here's the query I'm using currently. Will post updates as I tweak it. USE `information_schema`; SELECT t1.`COLUMN_NAME`, t1.`TABLE_NAME`, t1.`COLUMN_TYPE`, -- CONCAT(t1.`TABLE_NAME`,'.',t1.`COLUMN_TYPE`) as t1_type, t2.`TABLE_NAME`, t2.`COLUMN_TYPE` -- CONCAT(t2.`TABLE_NAME`,'.',t2.`COLUMN_TYPE`) AS t2_type FROM `COLUMNS` AS t1 LEFT JOIN `COLUMNS` AS t2 ON t1.`COLUMN_NAME` = t2.`COLUMN_NAME` AND t1.`COLUMN_TYPE` t2.`COLUMN_TYPE` WHERE t1.`TABLE_SCHEMA` = 'mydatabase' AND t2.`TABLE_NAME` IS NOT NULL -- HAVING t2_type IS NOT NULL ORDER BY `COLUMN_NAME` ASC; Having separate columns there is easier to read/compare than CONCAT() I think. Another bulk version that comes in handy: SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `TABLE_SCHEMA`, `TABLE_NAME`, `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `COLUMNS` WHERE `TABLE_SCHEMA` = ' mydatabase ' ORDER BY `COLUMN_NAME`; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
This idea is so fancy pants and clever I *wish* it could have worked for me. I checked and we actually have 65 genres currently (with more to come I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some bitmasks and this solution is so elegant. It's unfortunate there isn't a way to use more than 64-bits natively. You're RICK JAMES Bitch! :-p (please tell me you know the Dave Chappelles skit I'm referring to) -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, June 12, 2013 9:39 AM To: Daevid Vincent; mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) Thinking out of the box... (And posting my reply at the 'wrong' end of the email.)... Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED. AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) -- AND genre ((110) | (138)) != 0 AND genre ((122) | (161)) = 0 This would avoid having that extra table, and save a lot of space. If you have more than 64 genres, then Plan A: clump them into some kind of application grouping and use multiple INTs/SETs. Plan B: do mod div arithmetic to compute which genre field to tackle. For B, something like: AND (genre1 (10)) + (genre3 (18)) != 0 AND (genre2 (12)) + (genre6 (11)) = 0 (That's assuming 10 bits per genre# field. I would use 32 and INT UNSIGNED.) -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Tuesday, June 11, 2013 4:17 PM To: mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, On 6/11/2013 3:59 PM, Daevid Vincent wrote: I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` 22 AND `genre_id` 61 ) And straight up like this failure too... JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61) So I'm sort of out of ideas at this point and hoping someone has a way to do this. Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Oh! I must have misread. I didn't see how you had a solution for 64 bits. I may have to experiment with that! -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Wednesday, June 12, 2013 11:26 AM To: mysql@lists.mysql.com Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) This idea is so fancy pants and clever I *wish* it could have worked for me. I checked and we actually have 65 genres currently (with more to come I'm sure) LOL *FML*. I'm almost ashamed I didn't think of this. I 3 me some bitmasks and this solution is so elegant. It's unfortunate there isn't a way to use more than 64-bits natively. You're RICK JAMES Bitch! :-p (please tell me you know the Dave Chappelles skit I'm referring to) -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, June 12, 2013 9:39 AM To: Daevid Vincent; mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) Thinking out of the box... (And posting my reply at the 'wrong' end of the email.)... Are there fewer than 64 genres? Use a SET or BIGINT UNSIGNED. AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) -- AND genre ((110) | (138)) != 0 AND genre ((122) | (161)) = 0 This would avoid having that extra table, and save a lot of space. If you have more than 64 genres, then Plan A: clump them into some kind of application grouping and use multiple INTs/SETs. Plan B: do mod div arithmetic to compute which genre field to tackle. For B, something like: AND (genre1 (10)) + (genre3 (18)) != 0 AND (genre2 (12)) + (genre6 (11)) = 0 (That's assuming 10 bits per genre# field. I would use 32 and INT UNSIGNED.) -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Tuesday, June 11, 2013 4:17 PM To: mysql@lists.mysql.com Cc: 'shawn green' Subject: RE: How do I select all rows of table that have some rows in another table (AND, not OR) -Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, On 6/11/2013 3:59 PM, Daevid Vincent wrote: I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` 22 AND `genre_id` 61 ) And straight up like this failure too... JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61) So I'm sort of out
How do I select all rows of table that have some rows in another table (AND, not OR)
I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` 22 AND `genre_id` 61 ) And straight up like this failure too... JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61) So I'm sort of out of ideas at this point and hoping someone has a way to do this. Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres 17 1|3|10|19|38|53|58|59| SELECT * FROM scene_all_genres WHERE scene_id = 11; scene_id genres --- 11 1|10|19|31|32|59| SELECT DISTINCT s.`scene_id` AS `id`, sg.`genres` FROM `scene_all_genres` AS sg, `dvds` AS d, `scenes_list` AS s WHEREdvd_id` = d.`dvd_id` AND sg.`scene_id` = s.`scene_id` AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]' AND sg.`genres` NOT REGEXP '(([[::]]22[[::]])|([[::]]61[[::]]))' ; http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp [[::]], [[::]] These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
-Original Message- From: shawn green [mailto:shawn.l.gr...@oracle.com] Sent: Tuesday, June 11, 2013 2:16 PM To: mysql@lists.mysql.com Subject: Re: How do I select all rows of table that have some rows in another table (AND, not OR) Hello Daevid, On 6/11/2013 3:59 PM, Daevid Vincent wrote: I am trying to implement a filter so that a user could select various genres they want in or out. Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...) So I want something sort of like this, however IN() is using an OR comparison when I need it to be an AND SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`; This is giving me way way too many rows returned. For example, I would expect this scene_id to be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 17; scene_id genre_id -- 17 1 17 3 17 10 -- 17 19 17 38 -- 17 53 17 58 17 59 And this scene ID to NOT be in the result set: SELECT * FROM scenes_genres WHERE scene_id = 11; scene_id genre_id -- 11 1 11 10 -- 11 19 11 31 11 32 -- but does not have 38 11 59 I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND) JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` 22 AND `genre_id` 61 ) And straight up like this failure too... JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61) So I'm sort of out of ideas at this point and hoping someone has a way to do this. Also, just for SG this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while clever is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically... SELECT * FROM scene_all_genres WHERE scene_id = 17; scene_id genres 17 1|3|10|19|38|53|58|59| SELECT * FROM scene_all_genres WHERE scene_id = 11; scene_id genres --- 11 1|10|19|31|32|59| SELECT DISTINCT s.`scene_id` AS `id`, sg.`genres` FROM `scene_all_genres` AS sg, `dvds` AS d, `scenes_list` AS s WHEREdvd_id` = d.`dvd_id` AND sg.`scene_id` = s.`scene_id` AND sg.`genres` REGEXP '[[::]]10[[::]].*[[::]]38[[::]]' AND sg.`genres` NOT REGEXP '(([[::]]22[[::]])|([[::]]61[[::]]))' ; http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp [[::]], [[::]] These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_). To me it looks like you want to know how to match N for N when looking for subset of properties. It's a multi-step process using plain SQL but it's fast: 1) Build a list of scenes containing the genres you want to see CREATE TEMPORARY TABLE tmpList(key(scene_id, genre_id)) SELECT scene_id, genre_id FROM scenes_genres WHERE genre_id` IN (10,38) -- in this case you have 2 terms to mach 2) From that list, determine which of those scenes also contain unwanted genres and remove them. DELETE tmpList FROM tmpList t INNER JOIN scenes_genres sg on sg.scene_id = t.scene_id and sg.genre_id IN (22,61) # at this point, tmpList contains all scenes that have any of the desired genres but none of the unwanted ones. 3) Check to see if any scene has all N matches. SELECT scene_id, count(genre_id) as matches FROM tmpList GROUP BY scene_id HAVING matches = 2 /* ^--- this is the number of terms you are trying to match. */ Of course you can modify the last query to eliminate the HAVING clause and pick
Major MySQL Revision Takes on NoSQL
http://adtmag.com/articles/2013/02/06/mysql-update.aspx
Help with purging old logs for each customer ID
I have a customer log table that is starting to rapidly fill up (we have hundreds of thousands of users, but many are transient, and use the service for a few months, or use the free trial and quit, etc.) CREATE TABLE `customers_log` ( `customer_log_id` bigint(20) unsigned NOT NULL auto_increment, `customer_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `type` enum('View','Action','Admin','Search','Login','Logout','Access','General','A PI'), `source` enum('web','mobile','system'), `body` text, PRIMARY KEY (`customer_log_id`), KEY `created_on` (`created_on`), KEY `customers_id` (`customer_id`) ) ENGINE=InnoDB What I'd like to do now is make a 'rolling log' in that I want to DELETE any entries older than 90 days for EACH `customer_id`. I'm not sure how to do that in a query? I'd rather not iterate over each customer_id if I can help it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Help with purging old logs for each customer ID
Well, the customer_id is relevant in that I want the last 90 days relative to each customer. customer_id = 123 might have logs from jan - mar customer_id = 444 might have logs from feb - may So it's a rolling log of THEIR last 90 days from their last log (most recent) back 90 days from there. Does that make more sense? I guess I was trying to avoid looping over every customer ID and computing if I could help it. I thought by using a GROUP BY or something it could group all the logs for a given customer and then trim them that way. But maybe brute force is the way to go? -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Thursday, October 25, 2012 1:09 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: Help with purging old logs for each customer ID Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows at a time, DELETEing any that need to be purged. I would use a Perl or PHP loop, or write a stored procedure. More discussion of huge deletes (which this _could_ be): http://mysql.rjweb.org/doc.php/deletebig (PARTITIONing does not apply in your case, as I understand it.) I like the loop: SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1; DELETE FROM customers_log WHERE customer_log_id = @left_off AND customer_log_id @z AND created_on NOW() - INTERVAL 90 DAY; sleep a few seconds (to be a nice guy) Plus code to take care of iterating and terminating. That loop could be done continually. It seems that customer_id is irrelevant?? -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Thursday, October 25, 2012 11:46 AM To: mysql@lists.mysql.com Subject: Help with purging old logs for each customer ID I have a customer log table that is starting to rapidly fill up (we have hundreds of thousands of users, but many are transient, and use the service for a few months, or use the free trial and quit, etc.) CREATE TABLE `customers_log` ( `customer_log_id` bigint(20) unsigned NOT NULL auto_increment, `customer_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `type` enum('View','Action','Admin','Search','Login','Logout','Access','Genera l','A PI'), `source` enum('web','mobile','system'), `body` text, PRIMARY KEY (`customer_log_id`), KEY `created_on` (`created_on`), KEY `customers_id` (`customer_id`) ) ENGINE=InnoDB What I'd like to do now is make a 'rolling log' in that I want to DELETE any entries older than 90 days for EACH `customer_id`. I'm not sure how to do that in a query? I'd rather not iterate over each customer_id if I can help it. -- 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: Help with purging old logs for each customer ID
Thank you Rick! You're a super freak! ;-p This gets me in the vicinity of where I'm trying to go and I learned a few new tricks with the StartLoop: stuff too! Neat! d. -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Thursday, October 25, 2012 1:54 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: Help with purging old logs for each customer ID If the 90 days is back from MAX(created_on) for a given customer... INDEX(customer_id, created_on) will probably be needed. And that should replace KEY `customers_id` (`customer_id`). Maybe... DELETE FROM customers_log AS a WHERE a.customer_log_id = @left_off AND a.customer_log_id @z AND a.created_on ( SELECT MAX(created_on) FROM customers_log WHERE customer_id = a.customer_id ) - INTERVAL 90 DAY ); (Since this has the subquery, I would do only 100 at a time, not 1000) Or... CREATE TEMPORARY TABLE tmp SELECT customer_id, MAX(created_on) - INTERVAL 90 DAY AS cutoff FROM customers_log GROUP BY customer_id; DELETE FROM customers_log AS a JOIN tmp ON a.customer_id = tmp.customer_id WHERE a.customer_log_id = @left_off AND a.customer_log_id @z AND a.created_on tmp.cutoff; If you have millions of rows, a delete without some kind of loop is asking for trouble. Or... Turning things around to base it on customers... Loop through customer_ids (yeah, you did not want to do this) SELECT @id := 0; StartLoop: SELECT @id := customer_id WHERE customer_id @id ORDER BY customer_id LIMIT 1; if @id is NULL, exit DELETE FROM customers_log AS a WHERE a.customer_id = @id AND a.created_on ( SELECT MAX(created_on) FROM customers_log WHERE customer_id = @id ) - INTERVAL 90 DAY ); EndLoop. Since there is no rush for the purging, there is little need to optimize it other than to keep it from interfering with other queries. To that end, the compound index I propose is important. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Thursday, October 25, 2012 1:33 PM To: Rick James; mysql@lists.mysql.com Subject: RE: Help with purging old logs for each customer ID Well, the customer_id is relevant in that I want the last 90 days relative to each customer. customer_id = 123 might have logs from jan - mar customer_id = 444 might have logs from feb - may So it's a rolling log of THEIR last 90 days from their last log (most recent) back 90 days from there. Does that make more sense? I guess I was trying to avoid looping over every customer ID and computing if I could help it. I thought by using a GROUP BY or something it could group all the logs for a given customer and then trim them that way. But maybe brute force is the way to go? -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Thursday, October 25, 2012 1:09 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: Help with purging old logs for each customer ID Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows at a time, DELETEing any that need to be purged. I would use a Perl or PHP loop, or write a stored procedure. More discussion of huge deletes (which this _could_ be): http://mysql.rjweb.org/doc.php/deletebig (PARTITIONing does not apply in your case, as I understand it.) I like the loop: SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1; DELETE FROM customers_log WHERE customer_log_id = @left_off AND customer_log_id @z AND created_on NOW() - INTERVAL 90 DAY; sleep a few seconds (to be a nice guy) Plus code to take care of iterating and terminating. That loop could be done continually. It seems that customer_id is irrelevant?? -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Thursday, October 25, 2012 11:46 AM To: mysql@lists.mysql.com Subject: Help with purging old logs for each customer ID I have a customer log table that is starting to rapidly fill up (we have hundreds of thousands of users, but many are transient, and use the service for a few months, or use the free trial and quit, etc.) CREATE TABLE `customers_log` ( `customer_log_id` bigint(20) unsigned NOT NULL auto_increment, `customer_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `type` enum('View','Action','Admin','Search','Login','Logout','Access','Gen era l','A PI'), `source` enum('web','mobile','system'), `body` text, PRIMARY KEY (`customer_log_id`), KEY `created_on` (`created_on
need list of country ISO code to demonyms
Anyone have a SQL dump or other programmatically useable map of country ISO codes to demonyms? http://www.geography-site.co.uk/pages/countries/demonyms.html I can parse the strings I suppose there, but that's not quite as accurate, and ripping that out of the HTML page to parse seems painful too.
RE: need list of country ISO code to demonyms
HA! No $hit! Well isn't that clever. I didn't know that you can highlight a table like that, and paste it into Excel. That darn Microsoft -- they think of everything! :) Thanks for the tip. -Original Message- From: Nuno Tavares [mailto:nuno.tava...@dri.pt] Sent: Wednesday, October 03, 2012 3:10 PM To: mysql@lists.mysql.com Subject: Re: need list of country ISO code to demonyms I don't have it, sorry. But it took me 10 seconds to copy paste it to an Excel, so I could save it as CSV and import it directly to MySQL. Further on, it'd be as simples as JOIN by name with a table with ISO-to-country-names, which you can fetch in hundreds of places, such as Wikipedia. -NT Em 03-10-2012 22:22, Daevid Vincent escreveu: Anyone have a SQL dump or other programmatically useable map of country ISO codes to demonyms? http://www.geography-site.co.uk/pages/countries/demonyms.html I can parse the strings I suppose there, but that's not quite as accurate, and ripping that out of the HTML page to parse seems painful too. -- 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: New Fast MySQL Compatible Server Released under the GPL License
Well, considering that MySQL/Sun/Oracle can't even figure out how to reduce an ibdata1 file from ever-expanding after a decade (http://bugs.mysql.com/1341), it doesn't surprise me that parallel computing is a brain-stumper for them. :-\ Besides, I would suspect that Oracle would see this as one more threat against their cash cow product if they were to make MySQL even faster/better. I've not tried this offering below, nor do I know anything about it, but when a big company is involved, usually it comes down to money -- making it or spending it. d -Original Message- From: Gael Martinez [mailto:gael.marti...@gmail.com] Sent: Wednesday, August 22, 2012 10:13 AM To: Hiromichi Watari Cc: mysql@lists.mysql.com Subject: Re: New Fast MySQL Compatible Server Released under the GPL License On Wed, Aug 22, 2012 at 11:43 AM, Hiromichi Watari hiromichiwat...@yahoo.com wrote: [..] Available at www.paralleluniverse-inc.com Hello Pardon my question on that one, but why wasn't the concept detailed in that white paper implemented into the core version of mysql or the percona version (and MariaDB?) ? Too many hardware dependencies ? Regards -- Gaël Martinez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: console input - finding duplicate entries
-Original Message- From: Gary Aitken [mailto:my...@dreamchaser.org] Sent: Thursday, June 14, 2012 2:58 PM I can get the table loaded by specifying REPLACE INTO TABLE, but that still leaves me with not knowing where the duplicate records are. To find duplicate entries select dupe_column, count(*) as n from mytable group by dupe_column having n 1; or select n, m, count(*) as c from foo group by n, m having c 1; Here's one solution that will find the oldest duplicate(s) SELECT prod.prod_num,prod.prod_title,prod.updated FROM prod JOIN ( SELECT prod_title,max(updated) maxdate FROM prod GROUP BY prod_title ) AS proda ON prod.prod_title = proda.prod_title AND prod.updated != proda.maxdate A simple and fast way is via an exclusion join delete t1 from tbl t1 left join tbl t2 on t1.value=t2.value and t1.idt2.id where t2.id is not null; Deleting duplicate rows via temporary table DROP TABLE IF EXISTS `dupes`; CREATE TEMPORARY TABLE dupes SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) 1 ORDER BY BID; ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is critical. DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes); DROP TABLE IF EXISTS `dupes`; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
There are a bunch of other columns in all these tables. A quick reason is need the dvd.title too therefore the dvd table is needed. Another reason is that the query is generated programmatically based upon parameters passed to a method. But yes, I do she your point and maybe I can refactor some things in this special case. I haven't tried your query as I'm home and not at work right ATM, but I think you need a DISTINCT dvd_id right? Otherwise I'll get a bunch of rows all with the same dvd_id since multiple scene_ids will match. d -Original Message- From: Mark Kelly [mailto:my...@wastedtimes.net] Sent: Saturday, May 19, 2012 3:34 PM To: mysql@lists.mysql.com Subject: Re: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format Hi. On Friday 18 May 2012 18:21:07 Daevid Vincent wrote: Actually, I may have figured it out. Is there a better way to do this? I don't see why you need the dvds table when the dvd_id is in the scene table: SELECT a.dvd_id FROM scenes_list a, moviefiles b WHERE a.scene_id = b.scene_id AND b.format_id = '13'; or am I misunderstanding something? Cheers, Mark -- 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
SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: SQL query help. Retrieve all DVDs that have at least one scene of a certain encoding format
-Original Message- Sent: Friday, May 18, 2012 5:34 PM I have a table of DVDs, another of scenes and a last one of encoding formats/files... I want to find in one query all the dvd_id that have 0 scene_id that's encoded in format_id = 13. In other words all DVDs that are format_id = 13 despite not having a direct link. CREATE TABLE `dvds` ( `dvd_id` smallint(6) unsigned NOT NULL auto_increment, `dvd_title` varchar(64) NOT NULL default '', `description` text NOT NULL, PRIMARY KEY (`dvd_id`), ) CREATE TABLE `scenes_list` ( `scene_id` int(11) NOT NULL auto_increment, `dvd_id` int(11) NOT NULL default '0', `description` text NOT NULL, PRIMARY KEY (`scene_id`), ) CREATE TABLE `moviefiles` ( `scene_id` int(11) NOT NULL default '0', `format_id` int(3) NOT NULL default '0', `filename` varchar(255), `volume` smallint(6) NOT NULL default '0', PRIMARY KEY (`scene_id`,`format_id`), ) Actually, I may have figured it out. Is there a better way to do this? SELECT DISTINCT d.`dvd_id` AS `id`, (SELECT COUNT(s_sl.scene_id) AS s_tally FROM scenes_list AS s_sl JOIN moviefiles AS s_mf USING (scene_id) WHERE s_sl.dvd_id = d.`dvd_id` AND s_mf.format_id = 13) AS s_tally FROM `dvds` AS d WHEREd.`date_release` = '2012-05-18' HAVING s_tally 0 ORDER BY d.`date_release` DESC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Database Workbench 4.2.3, the multi-DBMS IDE now available!
Any plans to add sqlite to your list of supported DBs there? I develop Android and use LAMP as the server backend. Currently I use SQLYog as I have for like 10 years. But I would really like one GUI to work on both the android sqlite and the mysql backend since they usually tie together. -Original Message- From: Martijn Tonies [mailto:m.ton...@upscene.com] Sent: Friday, March 23, 2012 2:10 AM To: mysql@lists.mysql.com; firebird-to...@yahoogroups.com Subject: ANN: Database Workbench 4.2.3, the multi-DBMS IDE now available! ANN: Database Workbench 4.2.3, the multi-DBMS IDE now available! Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular Windows-based multi-DBMS development tool: Database Workbench 4.2.3 Pro For more information, see http://www.upscene.com/go/?go=newsid=20120323 Today's release includes the FREE Lite Editions. For a full list of fixes in this release, see: http://www.upscene.com/go/?go=trackerv=4.2.3id=3 http://www.upscene.com/go/?go=trackerv=4.2.2id=3 http://www.upscene.com/go/?go=trackerv=4.2.1id=3 http://www.upscene.com/go/?go=trackerv=4.2.0id=3 Database Workbench supports: - Borland InterBase ( 6.x - XE ) - Firebird ( 1.x, 2.x ) - MS SQL Server/MSDE ( 7, 2000, 2005, 2008 ) - MySQL 4.x, 5.x - Oracle Database ( 8i, 9i, 10g, 11g ) - Sybase SQL Anywhere ( 9, 10, 11 and 12 ) - NexusDB ( 3.0 and up ) Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- 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
USING WHERE; USING TEMPORARY; USING filesort
Anyone have any thoughts on how I might optimize this query? It takes about 2 seconds. I know that seems quick, but we get nearly 30,000 hits per second and so if we can shave this down, it helps. Also we do use memcache, but even with that, we still see this in the slow-log sometimes. I have indexes on everything used in this query and even a compound one as you see in the EXPLAIN. I'm not going to lose sleep over it, but I thought if there was something simple or a way to refactor I'd give it a shot. I thought changing the query to use JOIN ON syntax would have helped, but it didn't do anything really?! Also, this is all being used with PHP, so I'm fine with pulling things out into two or three queries if you suggest it will make a faster difference. -- old query: SELECT sg.`scene_id`, COUNT(*) AS num FROM `scenes_list` AS s, `dvds` AS d, `scenes_genres` AS sg, `scenes_downloads_new` AS sd WHERE sg.`genre_id` IN ('1','8','10','19','38','58','65') AND d.`dvd_id`=s.`dvd_id` AND sg.`scene_id`=s.`scene_id` AND d.`status`='ok' AND d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30; -- refactored: is this correct order?? Smallest table and most filters first right to narrow the dataset as quick as possible? -- EXPLAIN SELECT s.`scene_id`, COUNT(*) AS num FROM`dvds` AS d JOIN `scenes_list` AS s ON d.`dvd_id` = s.`dvd_id` AND d.`date_release` != '-00-00' AND d.`status` = 'ok' JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ('1', '8', '10', '19', '38', '58', '65') JOIN `scenes_downloads_new` AS sd ON sd.`scene_id` = s.`scene_id` GROUP BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30 ; SELECT COUNT(*) FROM dvds; -- 12181 SELECT COUNT(*) FROM scenes_downloads_new; -- 66054 SELECT COUNT(*) FROM scenes_list; -- 67197 SELECT COUNT(*) FROM scenes_genres; -- 344272 -- why do I still hit the filesort and temp table? how can I get rid of that? id select_type TABLE TYPEpossible_keys KEY key_len ref ROWS Extra -- --- -- -- -- -- --- - -- -- 1 SIMPLE d ref PRIMARY,date_release,STATUS,status_release,dvd_release status_release 1 const2436 USING WHERE; USING TEMPORARY; USING filesort 1 SIMPLE s ref PRIMARY,dvd_id_2,dvd_id dvd_id 4.d.dvd_id 6 USING WHERE 1 SIMPLE sd eq_ref PRIMARY PRIMARY 3.s.scene_id 1 USING WHERE 1 SIMPLE sg ref PRIMARY,scene_id,genre_id scene_id4.s.scene_id 5 USING WHERE If I take off the ORDER BY (just to see what happens), basically it's exactly the same EXPLAIN output (wrong results of course), but it does add Using index to the scene_id row (weird). If I take off the GROUP BY (just to see what happens), basically it's exactly the same EXPLAIN output (wrong results of course), but it does remove Using Filesort. Taking them both off is optimal I suspect, and again, barring the fact the results are wrong, it takes 1 second for the query. Should I read that to mean, it is what it is and that's the best I can expect from that multi-join query? Is there any benefit to splitting this up and if so, how should I split it? d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How often should we upgrade MySQL version
Ever heard the old saying, If it ain't broke, don't fix it. ;-) I'd say that as a general rule: 1. if you aren't experiencing problems then don't upgrade. 2. if you aren't subject to any vulnerabilities that may be found, then don't upgrade 3. if you don't need a new feature introduced, then don't upgrade 4. if you need to be up 100%, then don't upgrade Having said that, it is possible to upgrade with minimal downtime if you're smart about it. Get another server (or two or three) that is a clone of the existing ones. Upgrade those. Test those. Swap over. Then for the next release, do the same thing with the servers you now have as the spares. If you're in such a mission critical situation, you should have spare servers and live hot-swapable backups anyways right. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, November 18, 2011 11:54 AM To: MySQL ML Subject: How often should we upgrade MySQL version We are running MySQL 5.1.46 with master to master replication with 3 other servers for 3 different websites in 3 different parts of the world. My question is how often should we be looking to upgrade our MySQL version considering we can't really afford any downtime. Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Within-group aggregate query help please - customers and latest subscription row
I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way is when I drill into the customer, I already know the customer_id and so don't need to JOIN with that table, I just want to get the proper row from the customers_subscriptions table itself. SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY `date` DESC; subscription_id processor customer_id date --- - --- -- 134126370 chargem 7 2005-08-04 1035167192 billme 7 2004-02-08 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a crap on your face when you try what would seem to be the right query: SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; subscription_id MAX(`date`) --- --- 1035167192 2005-08-04 Notice how I have the correct DATE, but the wrong subscription_id. In the example web sites above, they seem to deal more with finding the MAX(subscription_id), which in my case will not work. I need the max DATE and the corresponding row (with matching subscription_id). Thanks, d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
A kind (and shy) soul replied to me off list and suggested this solution, however, this takes 28 seconds (that's for a single customer_id, so this is not going to scale). Got any other suggestions? :-) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; There are 781,270 customers (nearly 1 million) and 1,018,092 customer_subscriptions. Our tables have many indexes on pretty much every column and for sure the ones we use here. EXPLAIN says: id select_type table typepossible_keys key key_len refrows Extra -- --- -- -- --- --- -- --- --- 1 PRIMARY c const PRIMARY PRIMARY 4 const 1 1 PRIMARY s ref date,customer_id customer_id 4 const 2 1 PRIMARY derived2 ALL (NULL)(NULL) (NULL) (NULL) 781265 Using where 2 DERIVED cs ALL (NULL)(NULL) (NULL) (NULL) 1018092 Using temporary; Using filesort -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 1:46 PM To: mysql@lists.mysql.com Subject: Within-group aggregate query help please - customers and latest subscription row I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way is when I drill into the customer, I already know the customer_id and so don't need to JOIN with that table, I just want to get the proper row from the customers_subscriptions table itself. SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY `date` DESC; subscription_id processor customer_id date --- - --- -- 134126370 chargem 7 2005-08-04 1035167192 billme 7 2004-02-08 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a crap on your face when you try what would seem to be the right query: SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; subscription_id MAX(`date`) --- --- 1035167192 2005-08-04 Notice how I have the correct DATE, but the wrong subscription_id. In the example web sites above, they seem to deal more with finding the MAX(subscription_id), which in my case will not work. I need the max DATE and the corresponding row (with matching subscription_id). Thanks, d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
Okay, it seems I am learning... slowly... So there needs to be a second WHERE in the sub-select... To get ONE customer's last subscription (0.038s): SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs WHERE cs.customer_id = 7 GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; To get ALL customers and their last subscription row (1m:28s) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate ORDER BY customer_id LIMIT 10; Thanks to you know who you are for pointing me in the right direction. Hopefully this helps someone else. d. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 4:06 PM To: mysql@lists.mysql.com Subject: RE: Within-group aggregate query help please - customers and latest subscription row A kind (and shy) soul replied to me off list and suggested this solution, however, this takes 28 seconds (that's for a single customer_id, so this is not going to scale). Got any other suggestions? :-) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; There are 781,270 customers (nearly 1 million) and 1,018,092 customer_subscriptions. Our tables have many indexes on pretty much every column and for sure the ones we use here. EXPLAIN says: id select_type table typepossible_keys key key_len refrows Extra -- --- -- -- --- --- -- --- --- 1 PRIMARY c const PRIMARY PRIMARY 4 const 1 1 PRIMARY s ref date,customer_id customer_id 4 const 2 1 PRIMARY derived2 ALL (NULL)(NULL) (NULL) (NULL) 781265 Using where 2 DERIVED cs ALL (NULL)(NULL) (NULL) (NULL) 1018092 Using temporary; Using filesort -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 1:46 PM To: mysql@lists.mysql.com Subject: Within-group aggregate query help please - customers and latest subscription row I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way
RE: Vista crashes
-Original Message- From: Halász Sándor [mailto:h...@tbbs.net] Sent: Tuesday, October 11, 2011 9:28 PM To: mysql@lists.mysql.com Subject: Vista crashes I find that, when under Vista the MySQL daemon has been shut down There's your first two problems: [a] why are you running Vista. Period. [b] why are you running MySQL under windows and not a Unix flavor as the rest of the world does? ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Episode 1 - Mongo DB Is Web Scale
http://www.youtube.com/watch?v=b2F-DItXtZs LOLercopter.
RE: Database Workbench 4.1.3, the multi-DBMS IDE now available!
No SQLite support? http://www.sqlite.org/ Seems curious you wouldn't have this yet, especially given its popularity on both embedded systems and Android to say the least. -Original Message- From: Martijn Tonies [mailto:m.ton...@upscene.com] Sent: Tuesday, August 16, 2011 6:56 AM To: mysql@lists.mysql.com Cc: firebird-to...@yahoogroups.com Subject: ANN: Database Workbench 4.1.3, the multi-DBMS IDE now available! ANN: Database Workbench 4.1.3, the multi-DBMS IDE now available! Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular multi-DBMS development tool: Database Workbench 4.1.3 Pro This release includes the FREE Lite versions for InterBase, Firebird and MySQL. For more information, see http://www.upscene.com/go/?go=newsid=20110816 For a full list of fixes in this release, see: http://www.upscene.com/go/?go=trackerv=4.1.3id=3 Database Workbench supports: - Borland InterBase ( 4.x - XE ) - Firebird ( 1.x, 2.x ) - MS SQL Server/MSDE ( 7, 2000, 2005, 2008, MSDE 1 2, SQL Express ) - MySQL 4.x, 5.x - Oracle Database ( 8i, 9i, 10g, 11g ) - Sybase SQL Anywhere ( 9, 10, 11 and 12 ) - NexusDB ( 3.0 and up ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
NoSQL to MySQL with Memcached
http://dev.mysql.com/tech-resources/articles/nosql-to-mysql-with-memcached.h tml
RE: Next Unique Number - Generation
I can think of several ways to accomplish this (or close to it). * Assign each server a number and prefix/append that number to the unique ID. * initialize each table on each server at a different huge number so they don't ever collide: ALTER TABLE `students` AUTO_INCREMENT=1000; Other server: ALTER TABLE `students` AUTO_INCREMENT=2000; * don't re-invent the wheel and just use UUID() * make the primary key a combination key of a regular auto-increment and NOW() or RAND() or some other unique column of the table itself or something like IP number of the server, etc. * setup a trigger to alter the primary key accordingly prior to insert. Maybe one server is even numbers and the other is odd numbers, or use MOD() or other math to have a pool of servers. http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with -auto-increment -Original Message- From: Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Wednesday, July 20, 2011 11:18 PM To: MY SQL Mailing list Subject: Next Unique Number - Generation Hi there, I need a technical help fro you, I have developed a software for college school. Here we have concept called register number/admission number. These are two unique umber for each student. My application resides Client/server model. These numbers will be generated (some defined format) to each student when they are admitted at first time. This admission process is taken place at different nodes at a time. In this scenario, I am facing difficulty that, the same number is generated at time in two machines. (Logic is newest number will be displayed in the node before admission). How can I stop this logical issue? Can i have solution for this? Thank you in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Facebook Trapped In MySQL a 'Fate Worse Than Death'
http://developers.slashdot.org/story/11/07/09/1256241/Facebook-Trapped-In-My SQL-a-Fate-Worse-Than-Death According to database pioneer Michael Stonebraker, Facebook is operating a huge, complex MySQL implementation equivalent to 'a fate worse than death,' and the only way out is 'bite the bullet and rewrite everything http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ .' Not that it's necessarily Facebook's fault, though. Stonebraker says the social network's predicament is all too common among web startups that start small and grow to epic proportions.
Using where; Using temporary; Using filesort
I'm trying to optimize a query that doesn't seem all that complicated, however I can't seem to get it to not use a temp table and filesort. developer@vm_vz_daevid:~$ mysql --version mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2 EXPLAIN EXTENDED SELECT -- d.date_release, -- d.dvd_title, -- s.type, -- s.id_place, s.scene_id AS index_id, s.dvd_id FROM dvds AS d JOIN scenes_list AS s ON s.dvd_id = d.dvd_id AND d.status = 'ok' AND d.date_release != '-00-00' ORDER BY d.date_release DESC, d.dvd_title ASC, s.type ASC, s.id_place ASC; *** 1. row *** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY,date_release,status,status_release key: status_release key_len: 1 ref: const rows: 1976 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: s type: ref possible_keys: dvd_id_2,dvd_id key: dvd_id key_len: 4 ref: videoszcontent.d.dvd_id rows: 6 Extra: Using where 2 rows in set, 1 warning (0.00 sec) There are proper indexes on most every column in both tables (as you can see there). [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! [b] The commented out columns above I thought might help with the ORDER BY for some reason from my reading here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html they did not. [c] lopping off the ORDER BY all together stops the Using temporary; Using filesort of course. Yeah! But now I'm left with a table of data in random order. Re-sorting it in PHP seems like an even bigger waste of cycles, when no doubt MySQL can sort hella-faster. [d] just doing ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents the using temporary but still does filesort and again I'm in the boat of [c] I guess my question is this: Is it ALWAYS possible to fabricate a query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where' extra -- you just have to keep at it? Or is it the case that sometimes you're just S.O.L. and no matter what, MySQL is going to give you a Cleveland Steamer? In other words, am I wasting my time trying to tweak my query and indexes here with the idea there's some magic incantation that will get this right or do I just have to accept it is what it is and it's not going to do any better. d. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using where; Using temporary; Using filesort
I sent this Friday, but it never made it to the list?! -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, May 27, 2011 12:27 PM To: mysql@lists.mysql.com Subject: Using where; Using temporary; Using filesort I'm trying to optimize a query that doesn't seem all that complicated, however I can't seem to get it to not use a temp table and filesort. developer@vm_vz_daevid:~$ mysql --version mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2 EXPLAIN EXTENDED SELECT -- d.date_release, -- d.dvd_title, -- s.type, -- s.id_place, s.scene_id AS index_id, s.dvd_id FROM dvds AS d JOIN scenes_list AS s ON s.dvd_id = d.dvd_id AND d.status = 'ok' AND d.date_release != '-00-00' ORDER BY d.date_release DESC, d.dvd_title ASC, s.type ASC, s.id_place ASC; *** 1. row *** id: 1 select_type: SIMPLE table: d type: ref possible_keys: PRIMARY,date_release,status,status_release key: status_release key_len: 1 ref: const rows: 1976 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: s type: ref possible_keys: dvd_id_2,dvd_id key: dvd_id key_len: 4 ref: videoszcontent.d.dvd_id rows: 6 Extra: Using where 2 rows in set, 1 warning (0.00 sec) There are proper indexes on most every column in both tables (as you can see there). [a] the EXTENDED keyword doesn't seem to do anything different? I get the same columns and results??! [b] The commented out columns above I thought might help with the ORDER BY for some reason from my reading here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html they did not. [c] lopping off the ORDER BY all together stops the Using temporary; Using filesort of course. Yeah! But now I'm left with a table of data in random order. Re-sorting it in PHP seems like an even bigger waste of cycles, when no doubt MySQL can sort hella-faster. [d] just doing ORDER BY d.date_release DESC, d.dvd_title ASC; , prevents the using temporary but still does filesort and again I'm in the boat of [c] I guess my question is this: Is it ALWAYS possible to fabricate a query/schema in such a way that MySQL ALWAYS uses the ideal 'Using where' extra -- you just have to keep at it? Or is it the case that sometimes you're just S.O.L. and no matter what, MySQL is going to give you a Cleveland Steamer? In other words, am I wasting my time trying to tweak my query and indexes here with the idea there's some magic incantation that will get this right or do I just have to accept it is what it is and it's not going to do any better. d. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Renaming a database?
-Original Message- On Tue, May 24, 2011 at 4:19 PM, Jerry Schwartz je...@gii.co.jp wrote: It looks like there’s no way to rename a database. Is that true? I know. Retarded right? Version 5.1.x and still what would seem the most basic of tasks is still impossible. Maybe Oracle can fix what MontyAB and Sun couldn't... that feature alone would be well worth the $5.6 Billion dollars spent. I just had a need to do this last week and was astonished to find it's still not implemented after all these... well ever! I unfortunately had to dump the database to a .sql file, edit said file, re-dump in, then dump in the new database, then run a SQL diff tool to find out what schemata and data was different. The whole process delayed me a few hours that I didn't budget for. Talk about a hindrance. - And before anyone gets all upset with my use of the word Retarded... Read this: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html And then the definition: re•tard•ed –verb (used with object) 1. to make slow; delay the development or progress of (an action, process, etc.); hinder or impede. –verb (used without object) 2. to be delayed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
FW: MySQL shell...with parameters
Here’s an email from a good friend of mine that may be of interest. It is done in Ruby. From: Brad Robel-Forrest [mailto:b...@gigglewax.com] Sent: Sunday, May 15, 2011 7:01 PM To: Daevid Vincent Subject: MySQL shell...with parameters Remember a long while back I was bitching about the need for parameterized MySQL when working at the prompt? Well, it took me down a road and I've fulfilled that need and many more. If you're interested, I've opened a project on a shell that allows you to write all manner of SQL queries with variable interpolation and even generated query for programmatic searching through your database. Plus, it has the ability to do all of this remotely over an SSH connection without needing to have a mysql binary installed either locally or remotely. Main project site: https://github.com/bradrf/rsql Installing on Windows: https://github.com/bradrf/rsql/wiki/Install-on-Windows Tutorial: https://github.com/bradrf/rsql/raw/master/example.rsqlrc I've been using it a ton for analyzing our database at work for the past several months. It's a rare day that goes by where I don't fire this up. -brad
How do I get more pages in mySQL Workbench?? WAS: Any table visualization tools with wires connecting the actual columns?
I just discovered that MySQL Workbench actually WILL connect columns. Use the #6 wire type shortcut (bottom one). However, I must be retarded or blind because I can't figure out how to make the diagram have more than the default of 2 pages. I have a database with 50 tables and they just don't fit. In fact I only noticed this after printing out the page that many were missing. Turns out you can't scroll down to see them, you can only zoom way out and see that tables are off the white page. :-\ Oddly though, I imported another database with 88 tables and it automatically made 4 pages for me. Anyone know how to get more pages added? From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid.
RE: WHERE does not work on calculated view field
-Original Message- From: Daniel Kraft [mailto:d...@domob.eu] Sent: Friday, April 22, 2011 12:37 PM To: mysql@lists.mysql.com Subject: WHERE does not work on calculated view field Hi all, I'm by no means a (My)SQL expert and just getting started working with VIEWs and stored procedures, and now I'm puzzled by this behaviour: DROP DATABASE `test`; CREATE DATABASE `test`; USE `test`; CREATE TABLE `mytable` (`ID` SERIAL, `Type` INTEGER UNSIGNED NULL, PRIMARY KEY (`ID`)); INSERT INTO `mytable` (`Type`) VALUES (NULL); CREATE TABLE `types` (`ID` SERIAL, `Name` TEXT NOT NULL, PRIMARY KEY (`ID`)); INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B'); DELIMITER | CREATE FUNCTION `EMPTY_STRING` (value TEXT) RETURNS TEXT DETERMINISTIC BEGIN RETURN IF(value IS NULL, '', value); END| DELIMITER ; CREATE VIEW `myview` AS SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName` FROM `mytable` a LEFT JOIN `types` b ON a.`Type` = b.`ID`; SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL; SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL; SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%'; (I tried to simplify my problem as far as possible.) When I run this against MySQL 5.0.24a, I get three times 0 as output from the SELECTs at the end -- shouldn't at least one of them match the single row? (Preferably first and third ones.) What am I doing wrong here? I have no clue what's going on... Thanks a lot! Try this maybe: SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL; SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL; SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Any table visualization tools with wires connecting the actual columns?
It only seems to do the lines for InnoDB tables, not MyISAM... I mean, it not only won't auto-connect them, it won't even allow ME to connect them. :( Thanks though. -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 07, 2011 8:50 PM To: mysql@lists.mysql.com Subject: RE: Any table visualization tools with wires connecting the actual columns? At 05:36 PM 4/7/2011, Daevid Vincent wrote: I am a paid subscriber to SQLYog -- I love that tool, but AFAIK it doesn't do diagrams (with wires between tables and all that glory). Am I wrong? Is that feature there and I just never noticed it? David, SqlYog Ultimate has a schema designer. Take a look at the screen shots. http://www.webyog.com/en/screenshots.php Mike -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 07, 2011 1:36 PM To: mysql@lists.mysql.com Subject: Re: Any table visualization tools with wires connecting the actual columns? At 02:17 PM 4/7/2011, Daevid Vincent wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. Take a look at SqlYog from www.webyog.com. I use their community version but their paid version has a schema designer. They are responsive to emails and forum posts. Mike From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub
Any table visualization tools with wires connecting the actual columns?
Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11
RE: Any table visualization tools with wires connecting the actual columns?
I am a paid subscriber to SQLYog -- I love that tool, but AFAIK it doesn't do diagrams (with wires between tables and all that glory). Am I wrong? Is that feature there and I just never noticed it? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 07, 2011 1:36 PM To: mysql@lists.mysql.com Subject: Re: Any table visualization tools with wires connecting the actual columns? At 02:17 PM 4/7/2011, Daevid Vincent wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. Take a look at SqlYog from www.webyog.com. I use their community version but their paid version has a schema designer. They are responsive to emails and forum posts. Mike From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Any table visualization tools with wires connecting the actual columns?
Whoa! I never realized Toad did that. Man that is one robust program. I'm half minded to switch away from 'the Yog'... especially for FREE! Yeah, and it does do the sticky wires!! It only guessed some of them, but at least it's something. It seems to be missing an auto arrange kind of feature so once I make the wires, it can optimize the layout, but compared to other other crappy options, this is way better... and did I mention it's FREE! zOMGz. d -Original Message- From: Jason Trebilcock [mailto:jason.trebilc...@gmail.com] Sent: Thursday, April 07, 2011 12:38 PM To: mysql@lists.mysql.com Subject: Re: Any table visualization tools with wires connecting the actual columns? Toad for MySQL can do the diagramming piece...but, it looks and feels like you might have some of the same frustrations with it as well. But, another tool worth exploring nonetheless. On Thu, Apr 7, 2011 at 2:17 PM, Daevid Vincent dae...@daevid.com wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Any table visualization tools with wires connecting the actual columns?
I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: best practice: mysql_multi, VMs w/single instance per or doesn't matter?
There is almost no VM overhead these days. mySQL is disk I/O bound, not CPU bound. With VMWare you can setup your partitions to be raw disks (not virtual disk files) so you get native I/O. If you were to get some SSD's, I bet you would even see some significant performance increase too even over a true native system. Also consider sharding your tables to put some on raw/ssd/vmdk depending on how they're used. VMWare has options that are nearly bare-metal. There are other free options like KVM that are built right into the kernel. I personally use VirtualBox here at work for development, but I use VMWare Workstation at home. At previous jobs, we used VMWare Server (free) for all the UAT from the test servers themselves to the test guest OS (XP, Win7, OSX, Linux, browser variants, etc.) Virtual Machines are the ONLY way to go these days IMHO. It's silly to try and setup mySQL on different ports and go through all that hassle and configuration. With VM's you can just clone one to setup a new instance, you have fail-over, backups, they're easy to move to new hardware, they have console GUIs, intelligent shuffling of resources, maximizing hardware, minimizing costs (electric, carbon, space, etc). There are so many benefits and almost no detriments to a VM these days with computers as powerful as they are. Even updating the VMs (patching) is fairly straight forward with the major Linux distros (many even have web GUI front ends to push patches to all VMs, not to mention automated unattended updates if you desire) Just do it. DO IT! You won't ever look back, and like Reindl said, you'll wonder how you got this far without VMs. :-) -Daevid. There are only 11 types of people in this world. Those that think binary jokes are funny, those that don't, and those that don't know binary. -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 03, 2011 2:14 PM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: best practice: mysql_multi, VMs w/single instance per or doesn't matter? Just know that there is not-a-problem in running multiple instances on the same host, then all you have to do is to evaluate the performance factor. In your case I would not introduce the overhead of the VMs, but take advantage of this to learn how to manage multiple instances on the same host that is always useful. You can have a look at Giuseppe Maxia's MySQL Sandboxhttp://mysqlsandbox.net/ Or if you wish I can share my technique I use since 3.23. Cheers Claudio 2011/3/3 Reindl Harald h.rei...@thelounge.net i would use virtual machines because port/socket/configuration after running our whole infrastructure on vmware i can not understand how i could live without machine-snapshots and auto-failover :-) on hardware with virtualization support performance is also not a problem and ESXi is free without support on hardware matching the HCL Am 03.03.2011 22:52, schrieb Sid Lane: I've always had a single physical server that is the qc mysql database for all our applications but it's now up to 85 schemas so I want to break it up along the same lines as production (where there's redundant pools of mysql servers by application class). my basic question is whether it's better to run multiple instances on the same host or run single instances on multiple VMs on the same physical server. I can see slight advantages/disadvantages to each but no obvious upside nor downside to either. remember, this is dev/qc, not prod, so I'm leaning toward VMs so I don't have to manage port #s in configs or expect developers to remember that (also, I don't have to modify scripts for multiple instances, paths, etc). not big reasons for sure but all else equal I'll go the less work route and the only upside to multi I see is not having to reload the box as VM host. any compelling argument for either approach? -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Multi slave-single master
Absolutely true. We have a master/slave pair and a secondary slave that is our 'live backup' and we take offline every night to rsync the tarballs to tape backup too. When it comes online, it syncs up with master. Rinse repeat. Works awesome and seemless. -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Tuesday, February 15, 2011 4:50 AM To: mysql@lists.mysql.com Subject: Re: Multi slave-single master As far as I know your correcct. You can set as much slave servers as you need. -- João Cândido de Souza Neto Machiel Richards machi...@rdc.co.za escreveu na mensagem news:1297774004.1798.25.camel@machielr-laptop... Good day all I just have a quick question in order to confirm something.. If I remember correctly, one master are allowed to have more than one slave server (i.e. serverA can be master to both serverB and serverC) Am I correct in this matter? We are busy with a migration to new hardware this weekend and my plan is to setup the new master server as a slave to current production master in order to get all data in sync and during switchover to simply shutdown current production and change over to the new servers using relevant configuration. Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: best way to have a unique key
http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/ -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, January 20, 2011 10:45 AM To: Anthony Pace Cc: mysql. Subject: Re: best way to have a unique key uuid() Krishna On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace anthony.p...@utoronto.cawrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@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: This just seems to slow
Another option would be to mangle your insert statement with some other language like PHP, Python, Ruby, etc. so that the inserts are MULTI inserts instead. Something like: INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299519), VALUES (13071, 299520), VALUES (13071, 299521), ... That will radically speed up the inserts. Also delete your INDEX / KEYs and add them at the very end instead. -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Sunday, January 02, 2011 8:42 PM To: mysql@lists.mysql.com Subject: Re: This just seems to slow Jerry, Use Load Data Infile when loading a lot of data. Whoever is giving you the data should be able to dump it to a CSV file. Your imports will be much faster. Mike At 07:51 PM 1/2/2011, you wrote: I'm trying to load data into a simple table, and it is taking many hours (and still not done). I know hardware, etc., can have a big effect, but NOTHING should have this big an effect. = us-gii show create table t_dmu_history\G *** 1. row *** Table: t_dmu_history Create Table: CREATE TABLE `t_dmu_history` ( `t_dmu_history_id` int(11) NOT NULL AUTO_INCREMENT, `DM_History_DM_ID` int(11) DEFAULT NULL, `DM_History_Customer_ID` int(11) DEFAULT NULL, PRIMARY KEY (`t_dmu_history_id`), KEY `DM_History_DM_ID` (`DM_History_DM_ID`), KEY `DM_History_Customer_ID` (`DM_History_Customer_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1446317 DEFAULT CHARSET=utf8 = Here's a snip of what the input file looks like: = SET autocommit=1; # # Dumping data for table 'T_DMU_History' # INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299519); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299520); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299521); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299522); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299524); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299526); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299527); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299528); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299529); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299531); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299532); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299533); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299534); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13071, 299535); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298880); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298881); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298882); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298883); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298884); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298885); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298886); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298887); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298889); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298890); INSERT INTO `T_DMU_History` (`DM_History_DM_ID`, `DM_History_Customer_ID`) VALUES (13073, 298891); = There are about 87 records. I realize that using one INSERT per row is going to hurt, but I don't control the format of the incoming data. Besides, I'd have thought this would be pretty quick regardless of how clumsy the method was. Is that autocommit a problem? This is a bulk load into an empty table, so I'm not worried about ACID. Any suggestions? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
RE: /etc/init.d/mysql start WITHOUT integrity check?
-Original Message- From: andrew.2.mo...@nokia.com [mailto:andrew.2.mo...@nokia.com] Sent: Thursday, December 30, 2010 2:45 AM To: dae...@daevid.com; mysql@lists.mysql.com Subject: RE: /etc/init.d/mysql start WITHOUT integrity check? Daevid, I'm not quite sure I understand why you want to restart your master. Adding a slave shouldn't require any restarts/reloads. What have you changed in the my.cnf to solicit a restart? It requires a restart of the slave mysql server. It also requires shutting down the master so that no data is written while we copy the snapshot to the slave. http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html Andy From: ext Daevid Vincent [dae...@daevid.com] Sent: 29 December 2010 20:25 To: 'mysql' Subject: /etc/init.d/mysql start WITHOUT integrity check? Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity check? Can I pass in a command line parameter or set something in the my.cnf file? Our DB is a Billion rows (with a B) and that check can take HOURS. All we want to do is restart the server to put another slave online because sadly /etc/init.d/mysql reload does NOT re-load the config file (as one might hope), it is only to reload PRIVS (how useless is that since GRANT already does that). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.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: /etc/init.d/mysql start WITHOUT integrity check?
Comment WHAT lines? I looked through /etc/init.d/mysql and don't see anything related to check or chk. I eyeballed each line in the file and nothing stands out as the culprit causing an integrity check of the databases. develo...@mypse:/etc/init.d$ ps aux | grep mysql 46:root 10239 0.0 0.1 1680 520 ?SDec25 0:00 /bin/sh /usr/bin/mysqld_safe 49:mysql11165 0.0 5.4 129924 27864 ?Sl Dec25 0:58 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 50:root 11167 0.0 0.1 2920 692 ?SDec25 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld 81:1000 19149 0.0 0.1 3004 788 pts/0R+ 21:54 0:00 grep -n -i --color=auto mysql mailto:develo...@mypse:/etc/init.d$ develo...@mypse:/etc/init.d$ cat /proc/11165/cmdline /usr/sbin/mysqld--basedir=/usr--datadir=/var/lib/mysql--user=mysql--pid-fil e=/var/run/mysqld/mysqld.pid--skip-external-locking--port=3306--socket=/var /run/mysqld/mysqld.sockd Does anyone know if a kill -SIGHUP 11165 will cause a reload of the configuration? _ From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Thursday, December 30, 2010 5:01 AM To: andrew.2.mo...@nokia.com Cc: dae...@daevid.com; mysql@lists.mysql.com Subject: Re: /etc/init.d/mysql start WITHOUT integrity check? Let me know with you whether I understood what do you want to do. Normally, after mysqld restart on OSs as Ubuntu/Debian, we can observe a script execution, which will check integrity of all databases tables and present a message of Corrupt What I did when I wanted to get rid this check of was comment the lines inside the file script with # character. Please, let us know if it is the operation do you want to avoid when restart mysqld. Best regards. -- Wagner Bianchi 2010/12/30 andrew.2.mo...@nokia.com Daevid, I'm not quite sure I understand why you want to restart your master. Adding a slave shouldn't require any restarts/reloads. What have you changed in the my.cnf to solicit a restart? Andy From: ext Daevid Vincent [dae...@daevid.com] Sent: 29 December 2010 20:25 To: 'mysql' Subject: /etc/init.d/mysql start WITHOUT integrity check? Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity check? Can I pass in a command line parameter or set something in the my.cnf file? Our DB is a Billion rows (with a B) and that check can take HOURS. All we want to do is restart the server to put another slave online because sadly /etc/init.d/mysql reload does NOT re-load the config file (as one might hope), it is only to reload PRIVS (how useless is that since GRANT already does that). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
/etc/init.d/mysql start WITHOUT integrity check?
Is there a way to /etc/init.d/mysql start WITHOUT doing an integrity check? Can I pass in a command line parameter or set something in the my.cnf file? Our DB is a Billion rows (with a B) and that check can take HOURS. All we want to do is restart the server to put another slave online because sadly /etc/init.d/mysql reload does NOT re-load the config file (as one might hope), it is only to reload PRIVS (how useless is that since GRANT already does that). -- 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 mysql cache strip out /* comments */ first?
Ewen thank you! You've opened my eyes to something I didn't even know about and made my special purpose tingle. Have you used Percona personally? What are your opinions/thoughts? If you haven't used it, I'd be curious why not or what turned you away from it? -Original Message- From: Ewen Fortune [mailto:ewen.fort...@gmail.com] Sent: Thursday, November 18, 2010 4:56 AM To: Daevid Vincent Cc: mysql Subject: Re: Does mysql cache strip out /* comments */ first? Daevid, snip My concern is, my gut tells me that the built in mysql cache system is dumb. And by that I mean, I suspect that mySQL isn't smart enough to strip out comments from the SQL statement string BEFORE storing it as the cache hash key (yet I have no facts either way to back it up and hence the reason for this email). http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Can anyone please tell me I'm wrong and that it is smarter than I give it credit for, as I think this would be a very useful feature (or bug-fix as the case may be). Your suspicions are correct, the query cache does not strip comments before storing the statement. This can however be done in the Percona build. http://www.percona.com/docs/wiki/percona-server:features:query _cache_enhance#query_cache_strip_comments http://www.percona.com/docs/wiki/percona-server:features:imple mentation_details:details_query_cache_with_comments Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Does mysql cache strip out /* comments */ first?
Like most developers, I have a wrapper that all of my SQL queries go through in PHP. We have a dedicated NOC screen that shows the mytop status of each DEV/TEST/PROD master/slave pair. http://daevid.com/content/examples/snippets.php (Automatic Monitoring of remote servers) We sometimes see stuck queries and are always hesitant to kill them off because we never know WHO is executing that SQL. Is it a customer? Is it a developer? Is it the boss? Is it rogue from some script gone awry? Mytop doesn't give the full query due to screen real-estate amongst other reasons. The downside is they bog down the server until they eventually time-out or complete. Anyways, today I implemented a simple, transparent and effective step towards this puzzle. I prefix ALL SQL (since it goes through my sql_query() function) with /* ${SCRIPTNAME} */ Now all sql in the mytop shows up as: /* foo.php */ SELECT * FROM foo WHERE id = 1; /* bar.php */ UPDATE bar SET a = b WHERE id = 2; Etc... What I'd REALLY like to do is add more information in there. Perhaps add the FUNCTION/METHOD and the logged-in web USER that is actually executing that SQL, etc. My concern is, my gut tells me that the built in mysql cache system is dumb. And by that I mean, I suspect that mySQL isn't smart enough to strip out comments from the SQL statement string BEFORE storing it as the cache hash key (yet I have no facts either way to back it up and hence the reason for this email). http://dev.mysql.com/doc/refman/5.0/en/query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html Can anyone please tell me I'm wrong and that it is smarter than I give it credit for, as I think this would be a very useful feature (or bug-fix as the case may be). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: FW: [USN-1017-1] MySQL vulnerabilities
I don't think you understand how many exploits work. Through some social engineering or plain brute force or rainbow tables I can get the user/pass for many typical users. I could also give you some code and tell you to run it and thereby my code is executed as an authenticated user without you even knowing it. And here's another statistic you might not be aware of -- most hacking attempts are done BY people INSIDE a company, not external to it. It's extremely foolish and short-sighted to think that your system is safe unless it's in a glass jar and YOU are the ONLY user on it. Even then, YOUR account could be compromised too. -Original Message- From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Saturday, November 13, 2010 1:33 PM To: mysql@lists.mysql.com Subject: RE: FW: [USN-1017-1] MySQL vulnerabilities From: Daevid Vincent dae...@daevid.com my point exactly. there is NONE. and if you don't patch your mysql as needed, then you will need a lot more help when you're hacked. ;-p I note that the impact of every single one of these vulnerabilities was An authenticated user could exploit this to make MySQL crash, causing a denial of service. That's a pretty low threat level. No mention was made of gaining or increasing access, nor of corrupting data. First, you need an authenticated user who is trying to exploit a vulnerability to cause denial of service. If you're allowing a publicly accessible pseudo-user to exploit such vulnerabilities through script injection, that's YOUR problem! If an authenticated user causes a MySQL crash on my system, they get de-authenticated pretty quickly. :-) No rational person can see how using up the topsoil or the fossil fuels as quickly as possible can provide greater security for the future, but if enough wealth and power can conjure up the audacity to say that it can, then sheer fantasy is given the force of truth; the future becomes reckonable as even the past has never been. -- Wendell Berry Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
FW: [USN-1017-1] MySQL vulnerabilities
How come these kinds of notices are not sent to the mysql list? I realize this particular one is from Ubuntu, but the vulnerability is not ubuntu specific, it's mysql. Why aren't the mysql, er um, Oracle people more pro-active about letting us know these things? -Original Message- From: ubuntu-security-announce-boun...@lists.ubuntu.com [mailto:ubuntu-security-announce-boun...@lists.ubuntu.com] On Behalf Of Marc Deslauriers Sent: Thursday, November 11, 2010 7:49 AM To: ubuntu-security-annou...@lists.ubuntu.com Cc: full-disclos...@lists.grok.org.uk; bugt...@securityfocus.com Subject: [USN-1017-1] MySQL vulnerabilities === Ubuntu Security Notice USN-1017-1 November 11, 2010 mysql-5.1, mysql-dfsg-5.0, mysql-dfsg-5.1 vulnerabilities CVE-2010-2008, CVE-2010-3677, CVE-2010-3678, CVE-2010-3679, CVE-2010-3680, CVE-2010-3681, CVE-2010-3682, CVE-2010-3683, CVE-2010-3833, CVE-2010-3834, CVE-2010-3835, CVE-2010-3836, CVE-2010-3837, CVE-2010-3838, CVE-2010-3839, CVE-2010-3840 === A security issue affects the following Ubuntu releases: Ubuntu 6.06 LTS Ubuntu 8.04 LTS Ubuntu 9.10 Ubuntu 10.04 LTS Ubuntu 10.10 This advisory also applies to the corresponding versions of Kubuntu, Edubuntu, and Xubuntu. The problem can be corrected by upgrading your system to the following package versions: Ubuntu 6.06 LTS: mysql-server-5.05.0.22-0ubuntu6.06.15 Ubuntu 8.04 LTS: mysql-server-5.05.0.51a-3ubuntu5.8 Ubuntu 9.10: mysql-server-5.15.1.37-1ubuntu5.5 Ubuntu 10.04 LTS: mysql-server-5.15.1.41-3ubuntu12.7 Ubuntu 10.10: mysql-server-5.15.1.49-1ubuntu8.1 In general, a standard system update will make all the necessary changes. Details follow: It was discovered that MySQL incorrectly handled certain requests with the UPGRADE DATA DIRECTORY NAME command. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 9.10 and 10.04 LTS. (CVE-2010-2008) It was discovered that MySQL incorrectly handled joins involving a table with a unique SET column. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3677) It was discovered that MySQL incorrectly handled NULL arguments to IN() or CASE operations. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 9.10 and 10.04 LTS. (CVE-2010-3678) It was discovered that MySQL incorrectly handled malformed arguments to the BINLOG statement. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 9.10 and 10.04 LTS. (CVE-2010-3679) It was discovered that MySQL incorrectly handled the use of TEMPORARY InnoDB tables with nullable columns. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3680) It was discovered that MySQL incorrectly handled alternate reads from two indexes on a table using the HANDLER interface. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3681) It was discovered that MySQL incorrectly handled use of EXPLAIN with certain queries. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 6.06 LTS, 8.04 LTS, 9.10 and 10.04 LTS. (CVE-2010-3682) It was discovered that MySQL incorrectly handled error reporting when using LOAD DATA INFILE and would incorrectly raise an assert in certain circumstances. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 9.10 and 10.04 LTS. (CVE-2010-3683) It was discovered that MySQL incorrectly handled propagation during evaluation of arguments to extreme-value functions. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 8.04 LTS, 9.10, 10.04 LTS and 10.10. (CVE-2010-3833) It was discovered that MySQL incorrectly handled materializing a derived table that required a temporary table for grouping. An authenticated user could exploit this to make MySQL crash, causing a denial of service. (CVE-2010-3834) It was discovered that MySQL incorrectly handled certain user-variable assignment expressions that are evaluated in a logical expression context. An authenticated user could exploit this to make MySQL crash, causing a denial of service. This issue only affected Ubuntu 8.04 LTS, 9.10, 10.04 LTS and 10.10. (CVE-2010-3835) It was discovered that MySQL incorrectly
RE: FW: [USN-1017-1] MySQL vulnerabilities
my point exactly. there is NONE. and if you don't patch your mysql as needed, then you will need a lot more help when you're hacked. ;-p http://lists.mysql.com/ _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Friday, November 12, 2010 12:18 PM To: Daevid Vincent Cc: mysql Subject: Re: FW: [USN-1017-1] MySQL vulnerabilities I suspect that that is because this is not a security list, but a general help list. If you want those things, you'll get them from either your vendor, bugtraq, or the mysql security-specific mailing list that undoubtedly exists somewhere. Don't ask me where, though - I'm not on it either :-) On Fri, Nov 12, 2010 at 8:02 PM, Daevid Vincent dae...@daevid.com wrote: How come these kinds of notices are not sent to the mysql list? I realize this particular one is from Ubuntu, but the vulnerability is not ubuntu specific, it's mysql. Why aren't the mysql, er um, Oracle people more pro-active about letting us know these things? -- 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: Order by in clause
-Original Message- From: Joeri De Backer [mailto:fons...@gmail.com] Sent: Tuesday, November 09, 2010 1:16 AM To: mysql Subject: Re: Order by in clause On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge m...@good-stuff.co.uk wrote: Hi, I have a query like this: select id, title from product where id in (1,3,5,8,10) What I want it to do is return the rows in the order specified in the in clause, so that this: select * from product where id in (10,3,8,5,1) will give me results in this order: +--+-+ | id | title | +--+-+ | 10 | foo | +--+-+ | 3 | baz | +--+-+ | 8 | bar | +--+-+ | 5 | wibble | +--+-+ | 1 | flirble | +--+-+ Is this possible? If so, how? select * from product where id in (10,3,8,5,1) order by field(id,10,3,8,5,1) should do the trick... Regards, Joeri ...ya learn something new every day... ;-) Here's more on this topic: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Death of MySQL popularity?
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, November 04, 2010 2:26 AM To: jcbo...@yahoo.com Cc: MySQL Subject: Re: Death of MySQL popularity? You may want to read that again, but with your glasses on :-) Subscription means roughly commercial support. The (1) subscript means Features only available in Commercial Editions, and is noted *only* for Workbench SE, Enterprise Monitor, Enterprise Backup and Cluster Manager. I will join you in wondering whether that means Workbench is gonna go payware, though. On Thu, Nov 4, 2010 at 10:13 AM, Christoph Boget christoph.bo...@gmail.comwrote: http://www.mysql.com/products/ So the free version is going to include only MyISAM? And you won't be able to connect using MySQL Workbench (and presumably apps like MySQL Query Browser)? Otherwise you have to shell out $2k? Wow. I think it might be time to start seriously looking at Postgres... So there definitely is some confusion out there. Can someone from the @mysql / @oracle camp please confirm or deny the allegations? http://blog.herlein.com/2010/11/oracle-is-the-borg-enterprise-software-deve lopment-will-be-assimilated/ http://digitizor.com/2010/11/05/innodb-dropped-from-oracle-mysql-classic-ed ition/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Is SSD suitable for mysql server?
I guess it depends on how important your data is too. Quite a few of the SSDs on the market have been proven to not honour flush requests, so if the power goes out you've got corrupted data. Uh. If you're not using a UPS battery backup then you deserve to loose your data. And if you don't have it configured to auto-power off when it's low on battery (extended outtage) then you also deserve to feel the resulting pain. (http://www.apcupsd.com/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql_tzinfo_to_sql tz_file rebuilds tables for each tzdata!
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html#c11545 Note that if you are trying to keep your own database of cities and their timezones, you can NOT use the mysql.time_zone_name.Time_zone_id as your FK because the key will change the next time you get a new tzdata update (like from an apt-get update) and re-run mysql_tzinfo_to_sql tz_file. You'll have to store the Name column instead otherwise you loose data integrity. That script wipes the tables and re-creates them! rather than doing an UPDATE to existing records as you would reasonably expect. *sigh* -- 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 do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
-Original Message- From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au] Sent: Friday, October 15, 2010 2:33 PM To: MY SQL Mailing list Subject: Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?! Hi On 16/10/2010, at 1:47 AM, Suresh Kuna wrote: Hey Daevid, As this time zone table won't change once it is set up. Do a copy of the table data into another database and give grants to it. Copy the data is not a good solution. First of all, time zone data does change. Secondly if you need to use functions such as CONVERT_TZ () I believe you need access to the time zone tables in the mysql database. On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers joh...@pixelated.netwrote: I think this is one of those times you would update the mysql.user table directly, then flush privileges. You can grant access to the time zone tables just as you would do to any other table. GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''; GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) As mentioned above, granting access to the time zone tables works exactly as it does for all other tables, so. e.g. granting SELECT to '%' will not allow everybody to do a SELECT on the table, but rather allow users logging in as the '%'@'%' user to select from the mysql.time_zone_name table. If the users used in the above GRANT statements don't exist, they will also end up being created. This means that you suddenly might have opened access to the database for a user called '%' from everywhere (although they only can select from the time_zone_name table). Note that the new user can login without using a password. (none) SELECT User, Host FROM mysql.user; +--+---+ | User | Host | +--+---+ | root | localhost | | testuser | localhost | +--+---+ 2 rows in set (0.37 sec) (none) GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; Query OK, 0 rows affected (0.18 sec) (none) SELECT User, Host FROM mysql.user; +--+---+ | User | Host | +--+---+ | %| % | | root | localhost | | testuser | localhost | +--+---+ 3 rows in set (0.00 sec) $ mysql -u % -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. ... Jesper Thanks for the reply Jesper, but either there isn't a solution in your response, or I'm missing it? Any user can get into mysql, it's what they can do after that's the interesting part. I used your GRANT example above and get this... develo...@mypse:~$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2275 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. (develo...@localhost) [(none)] SELECT * FROM mysql.time_zone_name; ++--+ | Name | Time_zone_id | ++--+ | Africa/Abidjan |1 | | Africa/Accra |2 | | Africa/Addis_Ababa |3 | | Africa/Algiers |4 | ... But then when I try an existing user that I use for all my PHP/DB connections: develo...@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core (omt_mas...@mypse) [agis_core] SELECT * FROM mysql.time_zone_name; ERROR 1142 (42000): SELECT command denied to user 'OMT_Master'@'mydomain.com' for table 'time_zone_name' Here's the current user's I have in my VM so far: SELECT `User`, `Host` FROM mysql.user; User Host -- % % % OMT_Master% OMT_Web % View_ReadOnly % developer % diagnostics % diagnostics 10.10.10.% root 127.0.0.1 localhost debian-sys-maint localhost root localhost At this point of frustration, unless someone has a way to do this -- which seems like it should be a pretty straight forward thing to do -- I'll just add this particular OMT_Master user to have this particular table's SELECT GRANT. Or am I missing something? -- 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 do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
-Original Message- From: Jesper Wisborg Krogh [mailto:jes...@noggin.com.au] Sent: Friday, October 15, 2010 5:54 PM To: MY SQL Mailing list Subject: Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?! Any user can get into mysql, it's what they can do after that's the interesting part. Sorry, I'm not sure what you mean. Unless a username and host combination matches a record in the mysql.user table, then the user cannot log into the server. $ mysql -u random_user -p Enter password: ERROR 1045 (28000): Access denied for user 'random_user'@'localhost' (using password: NO) $ mysql -u random_user -p Enter password: ERROR 1045 (28000): Access denied for user 'random_user'@'localhost' (using password: YES) Don't use the -p password prompt and you get into mysql... develo...@mypse:~$ mysql -u random_user Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2393 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu) (random_u...@localhost) [(none)] show databases; ++ | Database | ++ | information_schema | | mysql | ++ 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
I'm pulling my hair out. How do I GRANT the SELECT ability to ANY USER for the very specific mysql.time_zone_name table?? I don't want to GRANT it to every individual user manually, I want one single GRANT that encompasses every user simultaneously. I've tried all of these, and they all are valid in mySQL but none of them actually have the desired result. GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''; GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) Here are the results: SELECT * FROM mysql.time_zone_name LIMIT 0, 5000 Error Code : 1142 SELECT command denied to user 'daevid'@'mycompany.com' for table 'time_zone_name' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Are Ubuntu 64-bit /var/lib/mysql/ibdata (etc) compatible with 32-bit ??
We have a PSE05 Master and PSE06 Slave (PRODUCTION servers) both are Ubuntu 32-bit. We have a third slave PSE07 which is Ubuntu 64-bit. This is our 'live backup' so to speak. We take mysqld down daily on there and tarball the /var/lib/mysql and /var/log/mysql as snapshots (since mysqldump would take a week literally to re-import). Our data is about 100GB and nearly 1 Billion records and growing by several hundred thousand per day. We had some replication hose-up where someone accidentally wrote to the PSE06 slave. This wasn't caught right away and so it cascaded and queued up about 130 rows to be written. Obviously going through this whole business: mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Would take WAY too long and painful. So we thought we'd shut down PSE06, scp the .tgz file from PSE07, dump it in, set the pointers on the slave and let replication catch up. Not so much. So is this even possible. Are the ibdata files binary compatible between bit versions (or even different OS's for that matter) We are getting errors: 101013 23:56:22 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.^M r...@pse06:~# find /home/mysql/ | grep relay /home/mysql/mysqld-relay-bin.01 /home/mysql/relay-log.info /home/mysql/mysqld-relay-bin.05 /home/mysql/binlog/mysqld-relay-bin.004055 /home/mysql/binlog/mysqld-relay-bin.004287 /home/mysql/binlog/mysqld-relay-bin.005693 /home/mysql/binlog/mysqld-relay-bin.004041 /home/mysql/binlog/mysqld-relay-bin.004676 /home/mysql/binlog/mysqld-relay-bin.004664 /home/mysql/binlog/mysqld-relay-bin.004044 /home/mysql/binlog/mysqld-relay-bin.004468 /home/mysql/binlog/mysqld-relay-bin.004682 /home/mysql/binlog/mysqld-relay-bin.004094 /home/mysql/binlog/mysqld-relay-bin.004153 /home/mysql/binlog/mysqld-relay-bin.004051 /home/mysql/binlog/mysqld-relay-bin.004645 /home/mysql/binlog/mysqld-relay-bin.004516 /home/mysql/binlog/mysqld-relay-bin.003889 /home/mysql/binlog/mysqld-relay-bin.004715 /home/mysql/binlog/mysqld-relay-bin.004299 /home/mysql/binlog/mysqld-relay-bin.004483 /home/mysql/binlog/mysqld-relay-bin.004628 /home/mysql/binlog/mysqld-relay-bin.004088 /home/mysql/binlog/mysqld-relay-bin.004059 /home/mysql/binlog/mysqld-relay-bin.index /home/mysql/binlog/mysqld-relay-bin.004271 /home/mysql/binlog/mysqld-relay-bin.004077 /home/mysql/binlog/mysqld-relay-bin.004671 /home/mysql/binlog/mysqld-relay-bin.004513 /home/mysql/binlog/mysqld-relay-bin.004732 /home/mysql/binlog/mysqld-relay-bin.004096 /home/mysql/mysqld-relay-bin.06 /home/mysql/mysqld-relay-bin.08 /home/mysql/mysqld-relay-bin.02 /home/mysql/mysqld-relay-bin.03 /home/mysql/mysqld-relay-bin.04 /home/mysql/mysqld-relay-bin.07 /home/mysql/mysqld-relay-bin.10 /home/mysql/mysqld-relay-bin.index /home/mysql/mysqld-relay-bin.12 /home/mysql/mysqld-relay-bin.13 /home/mysql/mysqld-relay-bin.09 /home/mysql/mysqld-relay-bin.11 Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem. Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [ERROR] Failed to open the relay log '/home/mysql/binlog/mysqld-relay-bin.005693' (relay_log_pos 53058571) Oct 13 23:55:27 pse06 mysqld[9185]: 101013 23:55:27 [ERROR] Could not find target log during relay log initialization -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How do I use and JOIN the mysql.time_zone% tables?
I'm trying to figure out how to join the mysql.time_zone% tables and make sense of this. YES, I know how to use them with SET time_zone = timezone; and all that. http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html That is NOT what I need them for (yet). I have a list of airports and those airports have cities and countries. I need to correlate those cities (which may or may not have an exact match in the time_zone_name table, so for each airport/city, I will need to hunt down the right offset via some page like this: http://www.timeanddate.com/worldclock/search.html THEN store the Time_zone_id in my city table (or whatever the unique combination is that I'd need and at this point I'm very confused as to what that is). The problem is I can find no real documentation what all these 5 tables are each for (some are obvious, but so cryptic it's hard to digest them) Moreover, I don't understand the results I'm getting... SELECT * FROM time_zone_name WHERE `Name` LIKE '%brussels%'; Name Time_zone_id - Europe/Brussels 412 posix/Europe/Brussels 993 right/Europe/Brussels 1574 First, WTF are there THREE Brussels?? SELECT * FROM time_zone_transition_type WHERE Time_zone_id IN (412, 993, 1574); Time_zone_id Transition_type_id Offset Is_DST Abbreviation -- -- -- 412 0 0 0 WET 412 13600 0 CET 412 27200 1 CEST 412 33600 0 CET 412 47200 1 CEST 412 53600 1 WEST 412 6 0 0 WET 412 7 0 0 WET 412 87200 1 CEST 412 93600 0 CET 993 0 0 0 WET 993 13600 0 CET 993 27200 1 CEST 993 33600 0 CET 993 47200 1 CEST 993 53600 1 WEST 993 6 0 0 WET 993 7 0 0 WET 993 87200 1 CEST 993 93600 0 CET 1574 0 0 0 WET 1574 13600 0 CET 1574 27200 1 CEST 1574 33600 0 CET 1574 47200 1 CEST 1574 53600 1 WEST 1574 6 0 0 WET 1574 7 0 0 WET 1574 87200 1 CEST 1574 93600 0 CET Now WTF are there TEN rows PER? This page: http://www.timeanddate.com/worldclock/city.html?n=48 says Brussels, Belgium is CEST so why are there 10 time_zone_transition_types SELECT * FROM time_zone_name JOIN time_zone_transition_type ON time_zone_name.Time_zone_id = time_zone_transition_type.Time_zone_id -- AND time_zone_transition.Transition_type_id = -- time_zone_transition_type.Transition_type_id WHERE `Name` LIKE '%brussels%'; Name Time_zone_id Transition_type_id Offset Is_DST Abbreviation - -- -- -- Europe/Brussels 412 0 0 0 WET Europe/Brussels 412 13600 0 CET Europe/Brussels 412 27200 1 CEST Europe/Brussels 412 33600 0 CET Europe/Brussels 412 47200 1 CEST Europe/Brussels 412 53600 1 WEST Europe/Brussels 412 6 0 0 WET Europe/Brussels 412 7 0 0 WET Europe/Brussels 412 87200 1 CEST Europe/Brussels 412 93600 0 CET posix/Europe/Brussels 993 0 0 0 WET posix/Europe/Brussels 993 13600 0 CET
RE: Can this be done with a single query?
Absolutely was just going to suggest this Travis. Another option and this is untested, but is to use HAVING and an alias. Something to this effect... SELECT INET_NTOA(e.src_ip) AS source_ip WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' HAVING source_ip BETWEEN '10.0.0.0' and '10.255.255.255' But yeah, Travis suggestion is cleaner and more efficient. -Original Message- From: Travis Ard [mailto:travis_...@hotmail.com] Sent: Tuesday, October 12, 2010 9:51 AM To: 'Paul Halliday' Cc: mysql@lists.mysql.com Subject: RE: Can this be done with a single query? You may get better performance from your query, and be able to make better use of indexes if you use integer comparisons for your IP address expressions instead of converting to strings with pattern matching. You might consider something like the following: SELECT DISTINCT(e.src_ip) FROM event e left outer join mappings m on mappings.src_ip = e.src_ip WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' AND e.src_ip NOT BETWEEN 167772160 AND 184549375 AND e.src_ip NOT BETWEEN 2886729728 AND 2886795263 AND e.src_ip NOT BETWEEN 3232235520 AND 3232301055 AND m.src_ip IS NULL; -Travis -Original Message- From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 10:08 AM To: Johnny Withers Cc: mysql@lists.mysql.com Subject: Re: Can this be done with a single query? On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers joh...@pixelated.netwrote: I would try: SELECT DISTINCT(e.src_ip) FROM event AS e LEFT JOIN mappings AS m ON e.src_ip=m.src_ip WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%' AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%' AND INET_NTOA(e.src_ip) NOT LIKE '192.168.%.%' AND m.src_ip IS NULL ; I would also modify the where clause to use: AND src_ip NOT BETWEEN INET_ATON('10.0.0.0') AND INET_ATON(10.255.255.255) AND src_ip NOT BETWEEN INET_ATON('172.16.0.0') AND INET_ATON(172.16.255.255) AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND INET_ATON(192.168.255.255) instead of AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%' AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%' AND INET_NTOA(src_ip) NOT LIKE '192.168.%.% You should also ensure there is an index on src_ip in events and mappings tables. Using the INET_NTOA() function on the src_ip column will prevent index usage during the query. This and the suggestion by Nathan both work. Thanks for the help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mySQL vs. NoSQL
You guys hear talk about NoSQL and here's a good article on the topic especially as to how it pertains to mySQL... http://www.linuxjournal.com/article/10770 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Any way to change timezone WITHOUT mysqld restart?
Trust me, I read it. We had an I18N product at my last company and all our time was stored in UTC in mySQL and we'd alter it on the fly for each user. This isn't rocket science. It's done every day in probably many of the sites you visit and don't even know it. To clarify for you (again): * Per-connection time zones. Each client that connects has its own time zone setting, given by the session http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar _time_zone time_zone variable. Initially, the session variable takes its value from the global http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar _time_zone time_zone variable, but the client can change its own time zone with this statement: mysql SET time_zone = timezone; The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#functi on_now NOW() or http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#functi on_curtime CURTIME(), and values stored in and retrieved from http://dev.mysql.com/doc/refman/5.1/en/datetime.html TIMESTAMP columns. Values for http://dev.mysql.com/doc/refman/5.1/en/datetime.html TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. Don't forget to do this stuff too: http://dev.mysql.com/doc/refman/5.1/en/mysql-tzinfo-to-sql.html So if it's not clear by now, you store all your dates/times in UTC (convert them via some script if you didn't start out that way). Then per web page connection, you read the user's profile TZ (presumably from the user session object or some other persistent means), execute that SQL statement above as one of the first things on the page, and FM ensues. All your properly saved mysql rows will display in the LOCAL timezone instead of UTC. You ALSO have to set the TZ in PHP too don't forget or you'll get whacky discrepencies. http://php.net/manual/en/function.date-default-timezone-set.php There's plenty of info on this out there for using PHP MySQL if that's what you're using too... http://www.ferdychristant.com/blog//archive/DOMM-84NEJN _ From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Saturday, October 02, 2010 5:18 AM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: Any way to change timezone WITHOUT mysqld restart? As a matter of fact I did, the real question is : Did you even read my email? I said WITHOUT a restart... The manual states that a restart of the mysqld is required. The reason for the post to such a list is because on many occasions, user have suggestions on some workaround for things that do work in spite of what the manual says. On Fri, 2010-10-01 at 15:42 -0700, Daevid Vincent wrote: Did you even look at the manual? http://lmgtfy.com/?q=mysql+set+timezone First link. -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Friday, October 01, 2010 10:25 AM To: mysql@lists.mysql.com Subject: Any way to change timezone WITHOUT mysqld restart? Any way to change timezone WITHOUT mysqld restart? It would be a lifesaver if there were some way for me not to have to restart because if mysql restarts then I have to go through a lot of other issues with my other apps.
RE: Any way to change tinezone WITHOUT mysqld restart?
Did you even look at the manual? http://lmgtfy.com/?q=mysql+set+timezone First link. -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Friday, October 01, 2010 10:25 AM To: mysql@lists.mysql.com Subject: Any way to change tinezone WITHOUT mysqld restart? Any way to change timezone WITHOUT mysqld restart? It would be a lifesaver if there were some way for me not to have to restart because if mysql restarts then I have to go through a lot of other issues with my other apps. -- 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 get hanging 1:M table rows as single column in main query?
Given three basic tables. An fmr table which has Field Maintenance Reports, a Seat table and a hanging or glue table to map Seats to FMRs. [See below] How do I get all the Seats to be in a single row with the FMR data? If I make this kind of query, they come in as separate rows: SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, `seat` FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3; id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C 3 320237274 2333 JFK 2010-09-24 04:35:31 35D 3 320237274 2333 JFK 2010-09-24 04:35:31 35E I want something more like: id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E Now, I'm going to be showing a few thousand FMR rows (and ideally their seats). What I do now is use PHP to pull the FMR records that match a certain criteria/filter. Then I pull in the entire dim_seats as an array and store it in a session since it's not going to change ever. Then I loop over all the id_fmr that I have pulled and look up in the fmr_has_seat table by id_fmr and implode() the seats from the session array. It saves me a few joins and gets the job done, but I keep feeling like there's a better way to do it. I'm thinking there's some magic with a subselect and concat or something in SQL, but then I wonder if that's any more efficient as mySQL still has to do two SELECTs per FMR row. This feels to me like a common problem and there must be an optimal mySQL way of doing it. Hanging tables of 1:M relationships are used everywhere. === == CREATE TABLE `fmr` ( `id_fmr` int(11) NOT NULL auto_increment, `fmr_number` varchar(32) NOT NULL default '', `fmr_system` smallint(6) default NULL, `fmr_station` varchar(4) NOT NULL default '', `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id_fmr`) ) ENGINE=InnoDB id_fmr fmr_number fmr_system fmr_station created_ts -- -- -- --- --- 1 319235F2A 2333 JFK 2010-09-24 04:35:31 2 319235F29 2333 JFK 2010-09-24 04:35:31 3 320237274 2333 JFK 2010-09-24 04:35:31 --- 4 32023726D 2333 JFK 2010-09-24 04:35:31 5 32023725A 2333 JFK 2010-09-24 04:35:31 6 32023724F 2333 JFK 2010-09-24 04:35:31 7 320237241 2333 LAX 2010-09-24 04:35:31 8 32023723A 2333 LAX 2010-09-24 04:35:31 9 320237232 2333 JFK 2010-09-24 04:35:31 10 320237230 2333 JFK 2010-09-24 04:35:31 .... .. .. .. CREATE TABLE `fmr_has_seat` ( `id_fmr` int(11) NOT NULL auto_increment, `id_dim_seat` int(10) unsigned NOT NULL, PRIMARY KEY (`id_fmr`,`id_dim_seat`), KEY `id_dim_seat` (`id_dim_seat`), CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr` (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB id_fmr id_dim_seat -- --- 3 888 --- 3 889 --- 3 890 --- 4 422 4 423 4 551 4 552 4 553 5 420 5 550 5 628 5 629 5 706 5 707 5 811 ... ... CREATE TABLE `dim_seat` ( `id_dim_seat` int(10) unsigned NOT NULL auto_increment, `seat` varchar(4) default NULL, PRIMARY KEY (`id_dim_seat`), KEY `seat` (`seat`) ) ENGINE=InnoDB id_dim_seat seat --- -- ... ... 888 35C --- 889 35D --- 890 35E --- 891 35F 892 35G ... ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
INSERT DELAYED and created_on timestamps
I'm doing some reading on INSERT DELAYED http://dev.mysql.com/doc/refman/5.0/en/insert.html I have a user_log table: CREATE TABLE `user_log` ( `id_user_log` bigint(20) unsigned NOT NULL auto_increment, `id_user` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `type` enum('View','Action','Admin','Search','Login','Logout','Access','General',' API') NULL, `source` enum('web','mobile') character set latin1 collate latin1_general_ci default 'web', `body` text character set latin1 collate latin1_general_ci, ) ENGINE=InnoDB We are noticing a lot of these in the logs however: Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 62715480, ACTIVE 0 sec, process no 14639, OS thread id 2904791952 inserting Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1 Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 1 Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, query id 799424 10.10.10.46 OMT_Master update Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log (`id_user`, `type`, `source`, `body`) VALUES ...) Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: So I'm thinking we could use the DELAYED or LOW_PRIORITY. My concern is the created_on time. Is there any difference in the actual timestamp recorded in the database if I use: INSERT INTO user_log (id_user) VALUES (3); INSERT DELAYED INTO user_log (id_user) VALUES (3); INSERT LOW_PRIORITY INTO user_log (id_user) VALUES (3); INSERT INTO user_log (id_user, created_on) VALUES (3, NOW()); INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3, NOW()); (or set the date via PHP): 'INSERT INTO user_log (id_user, created_on) VALUES (3, '.gmdate().')'; 'INSERT DELAYED INTO user_log (id_user, created_on) VALUES (3, '.gmdate().')'; My point is, is mySQL smart enough to know what the time WAS when the INSERT was supposed to be written by default, or if I DELAY it will it process the NOW() at INSERT time or DELAYED time or what time is NOW() and lastly if I set it with gmdate() in PHP, then that seems like it's the exact server time at the right moment?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: INSERT DELAYED and created_on timestamps
-Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Wednesday, September 29, 2010 2:26 PM To: Daevid Vincent Cc: 'MySQL' Subject: Re: INSERT DELAYED and created_on timestamps In the last episode (Sep 29), Daevid Vincent said: I'm doing some reading on INSERT DELAYED http://dev.mysql.com/doc/refman/5.0/en/insert.html I have a user_log table: CREATE TABLE `user_log` ( `id_user_log` bigint(20) unsigned NOT NULL auto_increment, `id_user` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `type` enum('View','Action','Admin','Search','Login','Logout','Access ','General',' API') NULL, `source` enum('web','mobile') character set latin1 collate latin1_general_ci default 'web', `body` text character set latin1 collate latin1_general_ci, ) ENGINE=InnoDB We are noticing a lot of these in the logs however: Sep 29 03:05:02 pse05 mysqld[14640]: TRANSACTION 0 62715480, ACTIVE 0 sec, process no 14639, OS thread id 2904791952 inserting Sep 29 03:05:02 pse05 mysqld[14640]: mysql tables in use 1, locked 1 Sep 29 03:05:02 pse05 mysqld[14640]: LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 1 Sep 29 03:05:02 pse05 mysqld[14640]: MySQL thread id 8330, query id 799424 10.10.10.46 OMT_Master update Sep 29 03:05:02 pse05 mysqld[14640]: INSERT INTO user_log (`id_user`, `type`, `source`, `body`) VALUES ...) Sep 29 03:05:02 pse05 mysqld[14640]: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: So I'm thinking we could use the DELAYED or LOW_PRIORITY. INSERT DELAYED only works on MyISAM, MEMORY, and ARCHIVE tables. You'll get a 1616 error if you try it on InnoDB. MySQL 5.5 is supposed to have a lot of concurrency improvements in; can you test your application on that and see if it's any faster than 5.0? Yeah, I just discovered that. However LOW_PRIORITY works on InnoDB tables it seems (at least, no error). But my original question still applies (even if for curiosity sake). Does mySQL account for the DELAY or LOW_PRIORITY time it took to write to the DB and adjust the timestamp accordingly or does it do the timestamp at the time of actual write vs. the time it was originally called? -- 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 get hanging 1:M table rows as single column in main query?
BRILLIANT SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, GROUP_CONCAT(`seat`) FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3 GROUP BY id_fmr; id_fmr fmr_number fmr_system fmr_station created_ts group_concat(`seat`) -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Wednesday, September 29, 2010 1:35 PM To: Daevid Vincent Cc: MySQL Subject: Re: How to get hanging 1:M table rows as single column in main query? GROUP_CONCAT() ? And group by id_fmr ? JW On Wed, Sep 29, 2010 at 2:38 PM, Daevid Vincent dae...@daevid.com wrote: Given three basic tables. An fmr table which has Field Maintenance Reports, a Seat table and a hanging or glue table to map Seats to FMRs. [See below] How do I get all the Seats to be in a single row with the FMR data? If I make this kind of query, they come in as separate rows: SELECT `id_fmr`, `fmr_number`, `fmr_system`, `fmr_station`, `created_ts`, `seat` FROM `fmr` JOIN `fmr_has_seat` USING (id_fmr) JOIN `dim_seat` USING (id_dim_seat) WHERE id_fmr = 3; id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C 3 320237274 2333 JFK 2010-09-24 04:35:31 35D 3 320237274 2333 JFK 2010-09-24 04:35:31 35E I want something more like: id_fmr fmr_number fmr_system fmr_station created_ts seat -- -- -- --- --- --- 3 320237274 2333 JFK 2010-09-24 04:35:31 35C,35D,35E Now, I'm going to be showing a few thousand FMR rows (and ideally their seats). What I do now is use PHP to pull the FMR records that match a certain criteria/filter. Then I pull in the entire dim_seats as an array and store it in a session since it's not going to change ever. Then I loop over all the id_fmr that I have pulled and look up in the fmr_has_seat table by id_fmr and implode() the seats from the session array. It saves me a few joins and gets the job done, but I keep feeling like there's a better way to do it. I'm thinking there's some magic with a subselect and concat or something in SQL, but then I wonder if that's any more efficient as mySQL still has to do two SELECTs per FMR row. This feels to me like a common problem and there must be an optimal mySQL way of doing it. Hanging tables of 1:M relationships are used everywhere. == = == CREATE TABLE `fmr` ( `id_fmr` int(11) NOT NULL auto_increment, `fmr_number` varchar(32) NOT NULL default '', `fmr_system` smallint(6) default NULL, `fmr_station` varchar(4) NOT NULL default '', `created_ts` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id_fmr`) ) ENGINE=InnoDB id_fmr fmr_number fmr_system fmr_station created_ts -- -- -- --- --- 1 319235F2A 2333 JFK 2010-09-24 04:35:31 2 319235F29 2333 JFK 2010-09-24 04:35:31 3 320237274 2333 JFK 2010-09-24 04:35:31 --- 4 32023726D 2333 JFK 2010-09-24 04:35:31 5 32023725A 2333 JFK 2010-09-24 04:35:31 6 32023724F 2333 JFK 2010-09-24 04:35:31 7 320237241 2333 LAX 2010-09-24 04:35:31 8 32023723A 2333 LAX 2010-09-24 04:35:31 9 320237232 2333 JFK 2010-09-24 04:35:31 10 320237230 2333 JFK 2010-09-24 04:35:31 .... .. .. .. CREATE TABLE `fmr_has_seat` ( `id_fmr` int(11) NOT NULL auto_increment, `id_dim_seat` int(10) unsigned NOT NULL, PRIMARY KEY (`id_fmr`,`id_dim_seat`), KEY `id_dim_seat` (`id_dim_seat`), CONSTRAINT `fmr_has_seat_ibfk_1` FOREIGN KEY (`id_fmr`) REFERENCES `fmr` (`id_fmr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fmr_has_seat_ibfk_2` FOREIGN KEY (`id_dim_seat`) REFERENCES `dim_seat` (`id_dim_seat`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB id_fmr id_dim_seat -- --- 3 888 --- 3 889 --- 3 890 --- 4 422 4 423 4 551
ENGINE=ARCHIVE doesn't support INDEX!!??
I gotta ask... http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html#c11511 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: ORDER BY with field alias issue
Easy. SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time_Format` FROM `reservation` ORDER BY `Time` -Original Message- From: BMBasal [mailto:bmb37...@gmail.com] Sent: Wednesday, September 29, 2010 3:50 PM To: 'Chris W'; 'MYSQL General List' Subject: RE: ORDER BY with field alias issue It is inherent in your naming. As long as your alias time is the same as the column name time, MySQL will have no way to distinguish which one you refers to exactly in your order-by clause, and chooses the alias in the select-clause as the one you intended. You confused MySQL. First, why you have to hang on time as alias. Second, if you don't mind adding another column in your select-clause as a throw-away, say, select DATE_FORMAT(`Time`, '%h:%i%p') as `Time`, `time` as `timex` Then, you could use `timex` in your order clause. This works, but with extra output, not elegant. -Original Message- From: Chris W [mailto:4rfv...@cox.net] Sent: Tuesday, September 28, 2010 8:10 PM To: MYSQL General List Subject: ORDER BY with field alias issue I have the following query that is giving me problems. SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time` FROM `reservation` ORDER BY `Time` Problem is it sorts wrong because of the date format function output with am and pm. I guess I should have named things differently but I would rather not do that. Is there a standard way to get around this and have it sort by the non-formatted time value? Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@cs.albany.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Capitalize Input via Auto Complete?
Get this tool: http://sqlyog.com/ it rocks. There is also http://www.quest.com/toad-for-mysql/ which is pretty great. -Original Message- From: Carlos Mennens [mailto:carlosw...@gmail.com] Sent: Tuesday, September 14, 2010 9:03 AM To: MySQL Subject: Capitalize Input via Auto Complete? I am new to MySQL coming from PostgreSQL noticed a really annoying issue. When I select a database, and try to auto complete in MySQL, it doesn't capitalize the MySQL statements. It's irritating to me to only be able to auto complete statements like 'SELECT', 'ALTER', 'INSERT' only if I hold down the shift key or caps lock key while typing. Is there a way to force MySQL to auto complete commonly used statements while typing them in lowercase which is normal behavior in PostgreSQL? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Capitalize Input via Auto Complete?
-Original Message- From: Carlos Mennens [mailto:carlosw...@gmail.com] Sent: Tuesday, September 14, 2010 12:54 PM To: MySQL Subject: Re: Capitalize Input via Auto Complete? On Tue, Sep 14, 2010 at 2:24 PM, Daevid Vincent dae...@daevid.com wrote: Get this tool: http://sqlyog.com/ it rocks. There is also http://www.quest.com/toad-for-mysql/ which is pretty great. I can't use any graphical or 3rd party add-on's. I was hoping MySQL had this native / built in. I guess not... Is there some reason you can't use a graphical tool? You do know you can use ssh tunnels and such to connect to your server from your desktop right? I do it all day long. It's pretty easy to do and built in to these programs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Unique ID's across multiple databases
-Original Message- From: Kiss Dániel [mailto:n...@dinagon.com] Sent: Monday, September 13, 2010 5:59 AM Well, thanks, but I'm afraid using UUID's (even with hex compression) is kind of a suicide, when it comes to performance. This is a good summary about the issues: http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/ Is this UUID issue unique to mySQL or are there other RDBMS's that handle it better (Postgress, Oracle, SQL Server, etc?) I too have a need for a unique identifier that will mesh with other databases periodically. So that a user in one local DB/server will get migrated to a master DB which in turn will sync up with remote sites so that all sites will have all users in it each night (for example). Having a mapping of UUID to local ID seems one way, but I feel there is a lot of room for collisions and integrity issues that way no? There are some solutions at the bottom of that blog post. Are those not good then? They seem interesting to me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Does putting a LIMIT on a DELETE clause make any difference?
I am curious about something. I have a glue or hanging table like so: CREATE TABLE `fault_impact_has_fault_system_impact` ( `id_fault_impact` int(10) unsigned NOT NULL, `id_fault_system_impact` smallint(5) unsigned NOT NULL, KEY `fault_impact_key` (`id_fault_impact`,`id_fault_system_impact`), KEY `id_fault_system_impact` (`id_fault_system_impact`), CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_1` FOREIGN KEY (`id_fault_impact`) REFERENCES `fault_impact` (`id_fault_impact`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fault_impact_has_fault_system_impact_ibfk_2` FOREIGN KEY (`id_fault_system_impact`) REFERENCES `fault_system_impact` (`id_fault_system_impact`) ON DELETE CASCADE ON UPDATE CASCADE ) And a lookup table like this: CREATE TABLE `fault_system_impact` ( `id_fault_system_impact` smallint(5) unsigned NOT NULL auto_increment, `fault_sytem_impact_name` varchar(50) NOT NULL, PRIMARY KEY (`id_fault_system_impact`) ) I have a bunch of checkboxes in a FORM and so in order to update properly, I wipe out all the PK IDs and then start inserting. It looks like this: UPDATE `fault_impact` SET `bite_code` = '12', `bite_code_type` = '32', `bite_subcode` = '21', `description_text` = 'Some random fault description here.', `fault_id` = '11-1', `fault_impact_other_explain` = '', `id_fault_area_impact` = '3', `symptom_lru_id` = '232', `symptom_lru_subid` = '34', `sys_perf_affected` = '', `update_date` = '2010-09-09 00:04:29' WHERE id_fault_impact = '2495' LIMIT 1; DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495; INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 1); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 3); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 2); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 7); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 10); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 14); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 9); INSERT INTO fault_impact_has_fault_system_impact(`id_fault_impact`,`id_fault_system_imp act`) VALUES(2495, 4); Given that I know there can only be a maximum of id_fault_system_impact IDs -- currently there are 17 rows in the fault_system_impact table -- and they're unique to any given id_fault_impact, would it benefit me to change my DELETE statement to something like this: DELETE FROM fault_impact_has_fault_system_impact WHERE id_fault_impact = 2495 LIMIT 17; Since the fault_impact_has_fault_system_impact table could have thousands of rows and it seems that mySQL would do a table scan? Unfortunately, you can't EXPLAIN on a DELETE to see what it might be doing. :( OR is mySQL smart enough to know that the id_fault_impact is an index and therefore it will just be right quick and stop after deleting those 8 rows above? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Symlinks not working when pointing to another table.
-Original Message- From: Julien Lory [mailto:julien.l...@gmail.com] Sent: Thursday, September 02, 2010 12:31 PM To: mysql@lists.mysql.com Subject: Symlinks not working when pointing to another table. Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Just out of curiosity. If you're using apparmor and this new directory is on another partion or mount point or anything, you might have to add a rule in apparmor's config for them... -- 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 have a temp table space?
InnoDB is one of MANY engines in the RDBMS mySQL. There IS in fact a few ways to store in temporary tables (both RAM and DISK based) http://dev.mysql.com/doc/refman/5.1/en/create-table.html Look at: TABLESPACE PARTITIONS ENGINE -Original Message- From: neutron [mailto:neutronsh...@gmail.com] Sent: Wednesday, September 01, 2010 6:05 PM To: mysql@lists.mysql.com Subject: Does innodb have a temp table space? Hi all, As far as I know, some DB has a separate table space to store temp data (such as for external sort). My questions are: 1. Does innodb also has a separate temp-tablespace? 2. If I don't use innodb_file_per_table, where is innodb's temporary tablespace? Is it in the shared tablespace? Thanks all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Even or Odds numbers
Not sure what you're trying to do, but if all you want to do is toggle between two things (as in row color zebra stripes or something) then I would suggest you not use some MOD() routine and instead just flip a boolean. For example in PHP just do: tr class=?= ($r == !r) ? 'light' : 'dark' ? just sayin'. -Original Message- From: Mike Blezien [mailto:mick...@frontiernet.net] Sent: Tuesday, August 31, 2010 11:13 AM To: jcbo...@yahoo.com Cc: MySQL List Subject: Re: Even or Odds numbers - Original Message - From: Christoph Boget christoph.bo...@gmail.com To: Mike Blezien mick...@frontiernet.net Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, August 31, 2010 1:06 PM Subject: Re: Even or Odds numbers is there a function, using MySQL 5.0v, that can detect if a numerical value is either an Even or Odd number MOD() http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions. html#function_mod SELECT MOD( X, 2 ) where X is your number (or column name). If 0, it's even if 1 it's odd. thnx, Christoph Thanks that should do the trick. Appreciate the other response too. Big help :) Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ Office: 1.712.395.0670 Skype Contact: cgimickalo -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Creating a dedicated reporting server for management?
You misunderstand. The slave is LIVE. Every write to the master writes to BOTH slaves. You can add as many slaves to a master as you want. My point was IN ADDITION to that benefit, it also affords you the ability to take down one of the slaves for an offsite archive. So if you need master/slave for prod, and slave for reports that all have to be up 100% then setup a 3rd slave then that is a live backup and you can then take it down at whatever interval you like to make a tarball for offsite backups. When you start the slave up again, it will re-synch itself to the master again automatically. Rinse. Repeat. I don't care how many slaves you have, they don't count as a real backup. If I say DROP DATABASE foo on the master, it will happily replicate to the slaves and you're whole company is now fsck'd in a way you can't even begin to comprehend. :) Ala http://www.geek.com/articles/news/disgruntled-employee-kills-journalspace-w ith-data-wipe-2009015/ -Original Message- From: nunziodav...@yahoo.com [mailto:nunziodav...@yahoo.com] Sent: Friday, August 27, 2010 5:01 PM To: Daevid Vincent Subject: Re: Creating a dedicated reporting server for management? Thanks for the idea. The sales guys want real time access so tarballing daily or even several times a day won't work. Nunzio Sent via BlackBerry from T-Mobile -Original Message- From: Daevid Vincent dae...@daevid.com Date: Mon, 23 Aug 2010 16:11:47 To: 'Travis Ard'travis_...@hotmail.com; 'Nunzio Daveri'nunziodav...@yahoo.com; mysql@lists.mysql.com Subject: RE: Creating a dedicated reporting server for management? While not elegant, you could setup multiple instances of mysql on different ports on the same box so each instance is a slave to a different master. From your web GUI, this is transparent to the end user since your configuration files and DB wrappers would handle the connections to 4. We do something similar now where we have DEV 01/02 TEST 03/04 PROD 05/06 and have an 07 slave that runs several instances so each 01,03,05 have a dedicated slave and the 07 secondary slave. Then we have a script that takes 07 mysql down, tarballs (7zip) the /var/lib/mysql and /var/log/mysql dirs (it's WAY faster than a mysql_dump for both import and export), rsyncs them to a master offsite backup server/tape. -Original Message- From: Travis Ard [mailto:travis_...@hotmail.com] Sent: Monday, August 23, 2010 3:52 PM To: 'Nunzio Daveri'; mysql@lists.mysql.com Subject: RE: Creating a dedicated reporting server for management? You could try doing a multi-master configuration by setting up mysql proxy to receive changes from 1, 2, and 3 and replicate to 4. -Travis -Original Message- From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] Sent: Monday, August 23, 2010 3:28 PM To: mysql@lists.mysql.com Subject: Creating a dedicated reporting server for management? Hello Gurus, I have a customer who wants to create a reporting server for his management team. He wants to take server 1,2,3 and move the 3 databases from all 3 servers to one server server 4 and then have the management team run all the reports from server 4 since there are tons and tons of joins. How can I accomplish this? I can't do replication since server 4 is going to be a slave AND a slave can only have one master so I am sitting here trying to figure out how to get this done? This is an ongoing transfer of data as the reports have to be in sync with the 3 master servers so replication would be perfect. Any help, docs and directions is most appreciated. Thanks In Advance :-) Nunzio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: master-slave replication sync problems.
ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creating a dedicated reporting server for management?
While not elegant, you could setup multiple instances of mysql on different ports on the same box so each instance is a slave to a different master. From your web GUI, this is transparent to the end user since your configuration files and DB wrappers would handle the connections to 4. We do something similar now where we have DEV 01/02 TEST 03/04 PROD 05/06 and have an 07 slave that runs several instances so each 01,03,05 have a dedicated slave and the 07 secondary slave. Then we have a script that takes 07 mysql down, tarballs (7zip) the /var/lib/mysql and /var/log/mysql dirs (it's WAY faster than a mysql_dump for both import and export), rsyncs them to a master offsite backup server/tape. -Original Message- From: Travis Ard [mailto:travis_...@hotmail.com] Sent: Monday, August 23, 2010 3:52 PM To: 'Nunzio Daveri'; mysql@lists.mysql.com Subject: RE: Creating a dedicated reporting server for management? You could try doing a multi-master configuration by setting up mysql proxy to receive changes from 1, 2, and 3 and replicate to 4. -Travis -Original Message- From: Nunzio Daveri [mailto:nunziodav...@yahoo.com] Sent: Monday, August 23, 2010 3:28 PM To: mysql@lists.mysql.com Subject: Creating a dedicated reporting server for management? Hello Gurus, I have a customer who wants to create a reporting server for his management team. He wants to take server 1,2,3 and move the 3 databases from all 3 servers to one server server 4 and then have the management team run all the reports from server 4 since there are tons and tons of joins. How can I accomplish this? I can't do replication since server 4 is going to be a slave AND a slave can only have one master so I am sitting here trying to figure out how to get this done? This is an ongoing transfer of data as the reports have to be in sync with the 3 master servers so replication would be perfect. Any help, docs and directions is most appreciated. Thanks In Advance :-) Nunzio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Possible tricks to ALTER on huge tables?
-Original Message- From: Rob Wultsch [mailto:wult...@gmail.com] Sent: Thursday, August 05, 2010 6:05 PM To: Daevid Vincent Cc: MySQL List Subject: Re: Possible tricks to ALTER on huge tables? Having significant amount of overhead for unused columns will without doubt harm performance significantly for certain operations. I don't see this as significant overhead. A few null columns that are dormant are not going to impact performance in any measurable way I don't think. Altering .frm files should is always be tried on a non-prod box before even considering using it on prod. Thanks Capt'n Obvious. ;-p Isn't that the case for ANY and ALL DB operations? There are some well known use cases (adding enums values, enlarging varchar columns) where altering a .frm is useful, but it should always be considered very dangerous. Of course. You could of course consider using PostgreSQL which would only need a very brief exclusive lock for adding a default null column... ...an interesting thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Possible tricks to ALTER on huge tables?
Driving to work today, I had an epiphany thought, but wanted to see if anyone could prove my theory or not. We currently have some tables that are approaching 1 BILLION rows (real Billion, with nine zeros, not that silly six zero version). Trying to do an ALTER on them to add a column can sometimes take hours. I'm wondering if we had the foresight to create the tables, and then tack on extra dormant columns of various common types, such as: future_uint int(10) unsigned null, future_int int(10) signed null, future_var varchar(255) null, Etc. So basically they'd be unused, then when we wanted a new column of that type, we would just rename the dormant one. I'm not sure if mySQL is smart enough to realize that if the schema definition for a column is identical, then it's just a simple rename, or if it treats any change the same and will still take hours to complete (if so, perhaps there's an optimization for you mysql developer team) Another option I considered, was is it possible to just go in with a hex editor and rename the field in the .frm file? Is there some kind of .frm editor available anywhere? r...@mypse:/var/lib/mysql/agis_core# hexdump -C country.frm 1000 01 01 00 00 0a 00 00 00 02 00 01 00 00 00 01 80 || 1010 02 00 00 12 00 02 00 ff 50 52 49 4d 41 52 59 ff |PRIMARY.| 1020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 || * 13f0 00 00 00 00 00 00 00 00 00 00 00 00 06 00 49 6e |..In| 1400 6e 6f 44 42 00 00 00 00 00 00 00 00 00 00 00 00 |noDB| 1410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 || * 2150 04 00 0b 69 64 5f 63 6f 75 6e 74 72 79 00 05 00 |...id_country...| 2160 05 69 73 6f 32 00 06 00 05 69 73 6f 33 00 07 00 |.iso2iso3...| 2170 0d 63 6f 75 6e 74 72 79 5f 6e 61 6d 65 00 04 0b |.country_name...| 2180 05 05 00 02 00 00 12 00 0f 00 00 02 c0 00 00 05 || 2190 05 06 06 00 04 00 00 00 80 00 00 00 fe c0 00 00 || 21a0 06 05 09 09 00 0a 00 00 00 80 00 00 00 fe c0 00 || 21b0 00 07 0d 42 fd 02 13 00 00 00 00 00 00 00 0f c0 |...B| 21c0 00 00 ff 69 64 5f 63 6f 75 6e 74 72 79 ff 69 73 |...future_var...| 21d0 6f 32 ff 69 73 6f 33 ff 63 6f 75 6e 74 72 79 5f |...future_int...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
There is something wrong with bugs.mysql.com email server I think.
I don't know if anyone from mysql is monitoring this list, but I think there is something goofy going on with your email servers. I just now got this email below. Uhm. I wrote that bug over a year and a half ago, and the reply by Susanne was the day after. WTF? ...better late than never I suppose, but maybe you should look into your mail server Q and see if there are more stuck emails?! -Original Message- From: Bug Database [mailto:do-not-re...@mysql.com] Sent: Tuesday, July 27, 2010 12:43 PM To: dae...@daevid.com Subject: #42774 [Com]: mysqldump command line switch to ignore temporary tables ATTENTION! Do NOT reply to this email! To reply, use the web interface found at http://bugs.mysql.com/?id=42774edit=2 Comment by: Bugs System Reported by: Daevid Vincent Category: Client Severity: S4 (Feature request) Status: Verified Version: 5.0.51a OS: Linux OS Details: Ubuntu Tags: IGNORE, temporary, mysqldump Defect Class: D5 (Feature request) [12 Feb 2009 12:53] Susanne Ebrecht Many thanks for writing a bug report. Changed category because mysqldump is CLI. Verified as described. The problem is related to general indexing. [11 Feb 2009 21:05] Daevid Vincent Description: We have a live database with tables that are over 500M rows. We cannot take the server down to do a backup, or in this case to delete temporary tables. However we use temporary tables constantly to take offloads (in SQL format, create a temp database/tables, then normalize and re-insert that offload data into our main database). This happens every minute or so and is increasing as we expand. We would like a way to ignore temp tables from mysqldump's command line. /*!4 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d` DISABLE KEYS */; /*!4 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d` ENABLE KEYS */; /*!4 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec` DISABLE KEYS */; /*!4 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec` ENABLE KEYS */; So as you can see, we end up with HUGE amounts of temporary tables being backed up and then on occasion re-imported. Then we don't know which temp tables are stale vs. active (the active ones do remove themselves, but our naming convention doesn't have dates) How to repeat: mysqldump --opts to dump out our entire database. Suggested fix: mysqldump --ignore-temp-tables -- http://bugs.mysql.com/42774 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: There is something wrong with bugs.mysql.com email server I think (another one)
And now I just got yet another email for a different bug from 2008!! What's going on here? BTW, this bug is super annoying! How sad that in FOUR YEARS AND FOURTEEN DAYS it is yet to be fixed. Seriously? This can't be that hard to fix, in fact, it seems that Innodb would have to go out of it's way to be different than the standard SQL logic. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, August 02, 2010 12:35 PM To: 'MySQL List' Subject: There is something wrong with bugs.mysql.com email server I think. I don't know if anyone from mysql is monitoring this list, but I think there is something goofy going on with your email servers. I just now got this email below. Uhm. I wrote that bug over a year and a half ago, and the reply by Susanne was the day after. WTF? ...better late than never I suppose, but maybe you should look into your mail server Q and see if there are more stuck emails?! -Original Message- From: Bug Database [mailto:do-not-re...@mysql.com] Sent: Sunday, August 01, 2010 4:04 AM To: dae...@daevid.com Subject: #21641 [Ver]: nonpreserving of InnoDB auto_increment values can lead to unintended inheritance Unsubscribe from updates to this bug at: http://bugs.mysql.com/21641 Updated by: Shane Bester Reported by: Stephen Dewey Category: Server: InnoDB Severity: S4 (Feature request) Status: Verified -Version: 4.1.20 +Version: 4.1, 5.1, 5.5 -OS: Linux +OS: Any -OS Details: Linux (Red Hat) +OS Details: Tags: auto_increment, innodb Defect Class: D5 (Feature request) [20 Oct 2008 23:40] Mattias Jonsson Closed bug#34077 as a duplicate of this. [13 Feb 2008 23:29] Dave Latham The current functionality is definitely not intuitive. A persistent auto-increment would be much appreciated! [19 Dec 2007 22:59] Mike Schumacher I agree that this is a critical problem that needs to be addressed. We are having all kinds of issues related to it. What is the status of this bug? [4 Jun 2007 22:06] Dimitriy Alekseyev What's the status of this feature request? [8 May 2007 3:20] Daevid Vincent This bug is critical and is causing us all kinds of grief with database integrity. Please escalate it. mysqladmin create INNODB_IS_BROKEN locutus ~ # mysql INNODB_IS_BROKEN mysql CREATE TABLE `foo` ( - `id` int(10) unsigned NOT NULL auto_increment, - `name` varchar(20), - PRIMARY KEY (`id`) - ) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql INSERT INTO foo values (null, 'a'); mysql INSERT INTO foo values (null, 'b'); mysql INSERT INTO foo values (null, 'c'); mysql SELECT * FROM foo; ++--+ | id | name | ++--+ | 1 | a| | 2 | b| | 3 | c| ++--+ 3 rows in set (0.00 sec) mysql DELETE FROM foo; Query OK, 3 rows affected (0.00 sec) mysql QUIT; locutus ~ # /etc/init.d/mysql restart * Stopping mysql ... * Starting mysql (/etc/mysql/my.cnf) locutus ~ # mysql INNODB_IS_BROKEN mysql INSERT INTO foo values (null, 'd'); Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM foo; ++--+ | id | name | ++--+ | 1 | d| ++--+ 1 row in set (0.00 sec) [28 Aug 2006 13:08] Valeriy Kravchuk Thank you for a reasonable feature request. Earlier comments can be viewed at http://bugs.mysql.com/21641 -- http://bugs.mysql.com/21641 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to PHP?
-Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Friday, July 16, 2010 5:25 AM To: Michael Dykman; mysql Subject: RE: Why is MySQL always linked to Php? [snip] PHP applications are, for the most part, not that ambitious and mysql is simply the most accessible database with the best developed API. [/snip] I know that you said for the most part and you are absolutely correct. I just want to point out that there are many corporations relying on PHP and MySQL to deliver robust, scalable and enterprise capable applications each and every day. I suppose that is part of the appeal - low barriers to entry with infinite possibilities. Yeah, I took a bit of offense to that original statement too. I work for Panasonic Avionics, and we have no less than 12 servers here running LAMP boxes in my department alone. They are in master/slave pairs. We have almost 100GB of data with about 1 BILLION rows. Everytime an aircraft lands, we get an offload about the (IFE) In Flight Entertainment system. What movies are watched, for how long, what games were played, what level reached, did the system reboot, was it commanded to reboot, all sorts of things. We get about an offload per minute or so 24/7. These servers are reliable and the SaaS we sell along with the IFE costs MILLIONS of dollars per airline. The GUI is all PHP with some Python as the backend to parse the offloads. We run Ubuntu 8.04LTS. When I founded WildTangent, everything there was LAMP boxes. All the games checked into a LAMP server. All the backend tools we created were LAMP. They switched to IIS I believe a few years ago because I can only assume that Alex St. John (the creator of DirectX) is a Microsoft guy, and our game technology is based upon Microsoft tech, so it probably didn't look so good to be running Linux for them. ;-) At Lockdown Networks, we sold $50k rack mount units that were all LAMP based with Ruby backends. These units secured many of the worlds networks from banks to nuclear reactors to colleges to government/military agencies. In all cases, we could have spent thousands to millions on Oracle servers or used the free alternatives like Postgress. We researched and CHOSE to use mySQL -- even paying the licensing fees at Lockdown over porting to Postgress (which we seriously looked into) So, I think someone needs to recognize that mySQL and PHP are extremely powerful and robust tools and are used in VERY ambitious projects. ;-) d http://daevid.com There are only 11 types of people in this world. Those that think binary jokes are funny, those that don't, and those that don't know binary. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [ANN] PBXT 1.5.02 Beta Released!
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Friday, July 16, 2010 7:41 AM To: MySQL List Subject: Re: [ANN] PBXT 1.5.02 Beta Released! Can I somehow unsubscribe (that is opt-out , as opposed to the more civilised opt-in) from the myriad of commercial announcements on this list, or do I just have to spambrand all senders ? Some weeks it feels as if there's more productspamming than posting going on. Interesting. I actually LIKE the announcements of new products related to mySQL. Each new product (commercial or FOSS) makes mySQL that much more robust and gives more credibility to our favorite RDBMS. Keep 'm comin guys! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to Php?
-Original Message- From: alba.albetti [mailto:alba.albe...@libero.it] Sent: Thursday, July 15, 2010 7:00 AM To: mysql Subject: Why is MySQL always linked to Php? Browsing the Web I've seen that usually companies look for developers working on MySQL and Php. Why are the two things linked? I mean I've not found any requests for just a MySQL developer or DBA (as for example it happens for Oracle), but it's always requested a MySQL/Php expert. I ask for it 'cause I've always been a developer/DBA on RDBMS (Oracle and DB2) and as I've been learning MySQL for few weeks, I'd like to know whether and why it's so important to learn Php as well. It would be so difficult to find a job as MySQL developer/DBA without knowing Php as well. Thanks! I would say it's a combination of things. MySQL is pretty much fire and forget for the most part for most sites. Unless you're dealing with HUGE amounts of data or massive amounts of hits, you rarely have to configure or DBA it. Oracle on the other hand is this huge mega-monstrosity that costs millions of dollars to implement and therefore you want someone presumably trained so they don't brick your box. With mySQL, if you brick the box, you pretty much just setup a new LAMP install and put your DB backups on. I'm of course oversimplifying. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: finding exact query being run
Well, you should be using a wrapper around your DB library class already right? Like you don't use PEAR:DB directly, you have a wrapper class to handle making a connection, doing the query, handling the error, etc. Otherwise you're wasting a LOT of code/time. Not to mention it makes it trivial to swap out the calls to another database library without chaning all of YOUR code. So all I did is wedge a logger/profiler into my sql_query() routine. This has various levels of logging and saving the start/end times, etc. -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, July 13, 2010 1:17 AM To: Daevid Vincent Cc: Machiel Richards; mysql@lists.mysql.com Subject: Re: finding exact query being run Which will still not be particularly helpful for subsecond queries :-) Either turn on the full query log (beware of I/O implications, use separate spindles if possible) or use mk-query-analyzer with tcpdump to get a running analysis of what passes the line. The addition of tcpdump support is my all-time favourite feature of maatkit :-D On Tue, Jul 13, 2010 at 12:05 AM, Daevid Vincent dae...@daevid.com wrote: get mytop _ From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Monday, July 12, 2010 8:40 AM To: mysql@lists.mysql.com Subject: finding exact query being run Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing sleep Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: finding exact query being run
get mytop _ From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Monday, July 12, 2010 8:40 AM To: mysql@lists.mysql.com Subject: finding exact query being run Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing sleep Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo
RE: opening a server to generalized queries but not too far
-Original Message- From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] The http request I have in mind will be something like https://server.foo.com?user=johnpassword=wxyz;... and the resulting query something like select ... from table where user=john and ... (I will first have verified the password.) For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! Do something like this instead: http://us.php.net/manual/en/features.http-auth.php Or use mod_auth_mysql to maintain your 'authorized' users to your page. And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you want. This is also a good time to normalize the data and column names so that you don't expose what their internal names are. http://dev.mysql.com/doc/refman/5.0/en/create-view.html http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views http://www.devshed.com/c/a/MySQL/Views-and-More-in-MySQL-50/ But also has he said, I don't see what you're trying to accomplish. If someone is technically literate to format SQL statements, then just give them a read-only account to the mysql (or view) directly. Let them use their own GUI tool like SQLYog or whatever -- it will be far more robust than anything you can write yourself. If you're trying to do some reports, then just code up the reports and use select boxes for the options you want someone to choose. Use jQuery and table_sorter plugin and you're done. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: opening a server to generalized queries but not too far
-Original Message- From: Don Cohen [mailto:don-mysq...@isis.cs3-inc.com] Sent: Wednesday, June 16, 2010 2:48 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: opening a server to generalized queries but not too far Daevid Vincent writes: For the love of God and all that is holy, do NOT put the user/pass on the URL like that!! What's so unholy (or even unwise) about it? Oh my goodness, where to begin... Well barring the fact that it'll be in the user's cache and browser history, you are sending this information in plain text across the internet, open to ANYONE on the way who wishes to packet sniff. Or use mod_auth_mysql to maintain your 'authorized' users to your page. Why is this so much better? In my case it's worse cause I don't want this to be interactive. I want to install something on a user's machine that will access his data without him having to do anything. The url is to be used by a program, not a person on a browser. One has nothing to do with the other. Mod_auth_mysql will serve as the user/password prompt and authentication between the user and the page you're creating. It is just an easy way for YOU to manage the users. If you have their user/password in a database -- presumably you already do, then leverage that. That's all this apache module will do. It's no different than you having to use $_GET['user'] and $_GET['password'] from your URL, querying the DB and granting some data back, except you've removed the horrific thought of forcing the user to expose their vitals in plain text in the URL/GET/POST/whatever. And as Adam beat me to, use a VIEW to expose ONLY the columns and joins you want. This is also a good time to normalize the data and column names so that you don't expose what their internal names are. So far I don't like this solution. Interesting. Perhaps you don't understand it? A view seems to suit your needs very well from what you've explained. It allows a READ ONLY access to your data. It prevents exposing your column/table names. It normalizes your data so your customers get it without having to jump through hoops and knowing intimate details about your schema. Pretty much this is why VIEWs were invented. But also has he said, I don't see what you're trying to accomplish. If I'm trying to let a large number of users each access his own data and not everyone else's in a very flexible way, in particular, allowing selection using functions, aggregations, filters. Right. And for YOU to code all that up in some fancy web GUI is going to be a nightmare and will never be all things to all people that you desire. So you either give people a GUI front end with select boxes and parameters, OR you give them some direct SQL access to a VIEW, OR you give them some XML/JSON/Excel/CVS/whatever output that they can then pull into their tool of choice to manipulate. Why not provide daily SQL dumps of their normalized data to your users and let them run their reports -- if they're trying to run SQL queries themselves? someone is technically literate to format SQL statements, then just give them a read-only account to the mysql (or view) directly. Let them use their own GUI tool like SQLYog or whatever -- it will be far more robust than anything you can write yourself. In this case there may be a lot of users but the queries are likely to be written by a small number. If you're trying to do some reports, then just code up the reports and use select boxes for the options you want someone to choose. Use jQuery and table_sorter plugin and you're done. I can't predict what options will be needed. Well you don't have to. You just make select boxes and such for each field that someone would want to select on and various filters. I bet you can cover 80% of your users with that if not all of them. And this seems much easier. Oh grasshopper... So Naïve. :) Here's what I think will happen -- and you can tell me nah nah nah nah in 2 years when you finish writing this uber-tool that does all this fancy SQL magic manipulation. It will either still fall short of what you wanted it to do b/c it's too complicated to code, OR it will be too complicated to use and nobody will understand it enough to use it. But hey, more power to ya Don if you want to tackle it, go ahead. I would suggest maybe looking at http://www.phpmyadmin.net Daevid. http://daevid.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: int(10) va int(11)
AFAIK, the number in parenthesis is ONLY for display purposes in formatting the size of the column in mySQL command line output, NOT the size of the data that can be held. I think they use (11) because unsigned will need one extra character for the minus sign. INT SIGNED = -2147483648 to 2147483647 Now this changes for things like a CHAR(2) where that *IS the column is only going to handle 2 characters. Or VARCHAR(10) where that column will handle from 0 to 10 characters. Same goes for FLOAT(7,4) which means 7 total digits and 4 of them are decimal places. But for *INT columns, I don't think the same is true. It's cosmetic only. Someone PLEASE correct me if I'm wrong. It's kind of silly if you ask me. This one special case just adds confusion. http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.) The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters. -Original Message- From: Ryan Chan [mailto:ryanchan...@gmail.com] Sent: Monday, June 14, 2010 9:14 AM To: MySql Subject: int(10) va int(11) Assume MySQL int range (unsigned) is from 0 to 4294967295 There are total 10 digits. Why a lot of tutorial in the web tell you to declare, e.g. CREATE TABLE t1 (f INT(11) UNSIGNED); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: list rows with no recent updates
The only way I could think of is to have a column that's an auto updated timestamp and then just query using that time. `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP So for your mass update, I'd SET @updated_time = NOW(); and then you could use that in your future query where @updated_time +/- some fuzzy amount of seconds. -Original Message- From: MadTh [mailto:madan.feedb...@gmail.com] Sent: Monday, June 14, 2010 2:02 PM To: mysql@lists.mysql.com Subject: list rows with no recent updates Hi, I ran a update command on around 2700 rows inside a mysql database table which has around 3000 table rows to change the ( say) price of each item ( with unique ID. unique product code). like: mysql UPDATE tbl_xyz set listprice='9.45' where prod_id='3069' and prod_code='a0071'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How can I list rows with no recent updates ( or the once where the above updates were not done) or say with no updates in last 2 hours? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org