Re: Mysql Monitoring with Graphite
I am using graphite, but what exactly do you want to graph? For mysql graphing I actually use nagios plugin called check_mysql_health along with check_mk/pnp4nagios That works really well. On Fri, Feb 22, 2013 at 2:24 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Adarsh Sharma eddy.ada...@gmail.com Anyone has any idea about this. Unless someone else here is using Graphite (I've never even heard of it, tbh) I think this may be something for the Graphite support channels, instead. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- 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: replication fails after upgrade to 5.6
HI Trimurthy, Just curious won't it make that data inconsistent on the slave? Thanks On Fri, Feb 22, 2013 at 12:15 AM, Trimurthy trimur...@tulassi.com wrote: Dear Mike Franon, i have also faced the same problem while setting up the replication. by that time i have added the error no to slave-skip-errors=1062 configuration file and restarted the server. after that replication gets work properly. once try this one. and one thing that i would like to inform that master server version always should be less than or equal to the slave version. Thanks Kind Regards, TRIMURTHY From: Mike Franon kongfra...@gmail.com Sent: Thursday, February 21, 2013 11:43 PM To: Reindl Harald h.rei...@thelounge.net Subject: Re: replication fails after upgrade to 5.6 Unfortunately that is not possible at the moment, I have 6 slaves off the one master, also I want to test it as much as possible before upgrading the master. Is the only way to really fix this is to upgrade master? I thought you can replicate from master - slave if version is higher on slave, just not the other way around? Thanks On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote: update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- 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: replication fails after upgrade to 5.6
So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication fails after upgrade to 5.6
Unfortunately that is not possible at the moment, I have 6 slaves off the one master, also I want to test it as much as possible before upgrading the master. Is the only way to really fix this is to upgrade master? I thought you can replicate from master - slave if version is higher on slave, just not the other way around? Thanks On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote: update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers On Tue, Feb 19, 2013 at 6:34 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 19.02.2013 23:53, schrieb Divesh Kamra: Hi Reindi Thanks for solution . Can u share complete steps ? which steps? * update * call mysql_upgrade -u root -p in doubt mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p and if you do mysql_upgrade -u root -p and are always up-to-date that was it, no matter if you move your data from windows to MacOSX and finally to linux or whatever OS On 20-Feb-2013, at 2:50, Reindl Harald h.rei...@thelounge.net wrote: surely * use mysql_upgrade -u root -p after EACH update * upgrade regulary we went from MySQL 3.x to 5.5.30 until know without any dump and here are around 5000 tables Am 19.02.2013 22:12, schrieb Divesh Kamra: Is there any better way for grade MySQL version without taking backup with mysqldump Or if there any tool for this R's DK On 16-Feb-2013, at 16:07, Reindl Harald h.rei...@thelounge.net wrote: Am 16.02.2013 09:42, schrieb Manuel Arostegui: 2013/2/15 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net our database is 400 GB, mysqldump is 600MB was not a typo and you honestly believed that you can import this dump to somewhat? WTF - as admin you should be able to see if the things in front of you are theoretically possible before your start any action and 1:400 is impossible, specially because mysql-dumps are ALWAYS WAY LARGER then the databasses because they contain sql-statements and not only data That's not completely true. If you have a poor maintained database or just tables with lot of writes and deletes and you don't periodically optimize it - you can end up with lot of blank spaces in your tables which will use _a lot_ of space. If you do a du or whatever to measure your database size...you can get really confused. mysqldump obviously doesn't backup blank spaces and once you get rid of them, your database will use much less space. ok, normally i expect there is a admin and doing his job especially for large datasets -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
OK I got it to work. I dumped the tables that it was complaining about first, and then dumped the triggers. I then uninstalled anything to do with mysql, and installed 5.1 Then imported the tables and triggers, and and able to run mysql_upgrade without any errors. This is all without using a full mysqldump. I am now going to go from 5.1 to 5.5 On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon kongfra...@gmail.com wrote: I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication fails after upgrade to 5.6
This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
going form 5.1 - to 5.5 was easy, I did not have to dump any tabels or triggers, just upgraded binary, ran mysql_upgrade and worked in no time. Thanks everyone for the help! On Wed, Feb 20, 2013 at 2:33 PM, Reindl Harald h.rei...@thelounge.net wrote: fine and much faster and probably safer too :-) a backup with rsync is faster as dump/import and can be done with minimize downtime by use it twice, the first time hot-backup with running server and the second time after stop server to get the diffs doing rsync - stop - rsync - start in a script may reduce the downtime to a few seconds Am 20.02.2013 20:29, schrieb Mike Franon: OK I got it to work. I dumped the tables that it was complaining about first, and then dumped the triggers. I then uninstalled anything to do with mysql, and installed 5.1 Then imported the tables and triggers, and and able to run mysql_upgrade without any errors. This is all without using a full mysqldump. I am now going to go from 5.1 to 5.5 On Wed, Feb 20, 2013 at 12:54 PM, Mike Franon kongfra...@gmail.com wrote: I am pretty sure I did, and when I did I got the following errors: Error: Table Upgrade Required, Please dump/reload to fix it I got that on 10 tables, and also got the following: Warning: Triggers for table ' have no creation context. I think it has to do with no triggers. I know hen I ran the mysql_upgrade it tired to auto repair but did not work and failed. But I will give it a shot again, maybe I missed something. The other thing I was thinking was maybe I can just mysqldump those 10 tables that it fails on, and just restore those instead of my entire db. On Wed, Feb 20, 2013 at 12:43 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 18:26, schrieb Mike Franon: So I did a full mysqldump over the weekend for a second time and this time it is 220GB, no clue what happened last time, I should have realized looking at the file size something was wrong, but since I got no errors did not think about it, and this time I timed it, took 7 hours to do a complete mysqldump Restoring it is not fun 18+ hours and counting, at this rate it will be a week, there has to be a better way of doing this, and this is only going form 5.0 to 5.1 I know some are saying don't need to do a mysqldump, but if i don't do it, the upgrade errors out on 10 tables, and then gives me errors about triggers and did you ALWAYS mysql_upgarde -root -p after ANY mysql-update? at least before try a major upgrade? did you try mysqlcheck -h localhost --check-upgrade --all-databases --auto-repair --user=root -p BEFORE the upgrade? did you try it ALSo after the upgrade? sorry, i do not believe that dump/import is needed and idoubt it will not give better results -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
replication fails after upgrade to 5.6
So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 There are queries that works with 5.1/5.0 that do not work with 5.5, I would test extensively.. S On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov mihail.mano...@liquidation.com wrote: You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any issues. There are some configuration file change, which you may want to consider checking. I definitely recommend upgrading your development servers for an extensive testing. Some queries _may_ run slower or not work at all and you may have to rearrange how you join tables in your queries. The upgrade from 5.5 to 5.6 should me smoother, though. On Feb 14, 2013, at 4:28 PM, Mike Franon wrote: Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database: DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; Now alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases. Statements containing alias constructs that are no longer permitted must be rewritten. (Bug #27525) On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Your right I am going to run another mysqldump, maybe something happened and pick this up next week.. Thanks all. On Fri, Feb 15, 2013 at 5:03 PM, Keith Murphy bmur...@paragon-cs.com wrote: Something doesn't add up. If the data set is 400 GB then your dump has to bigger than 600 mb. That is better than a 400:1 ratio. Maybe the dump isn't working correctly or your data set is much smaller? If the dump output is less than a gig I would just edit it with something like vi and look at the offending line. Keith On Feb 15, 2013 3:55 PM, Mike Franon kongfra...@gmail.com wrote: I am having a real hard time upgrading just from 5.0.96 to 5.1 I did a full mysqldump and then restore the database, keep in mind our database is 400 GB, mysqldump is 600MB file, about 30 minutes into the restore get this error on one table on an insert: ERROR 1064 (42000) at line 1388: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2010-04-10 20' at line 1 It weird because If I upgrade 5.1 right over 5.0 without doing a mysqldump, and then do a mysqlcheck it works, except for 5 tables, and triggers, so trying to think of the best way to get to 5.1 On Fri, Feb 15, 2013 at 12:39 PM, Keith Murphy bmur...@paragon-cs.com wrote: While it might be GA I would not recommend that you deploy it for a while. ... at least several point releases. There will be new bugs uncovered as it moves out to a wider audience. Upgrade to 5.5 (through 5.1) first as it is quite proven. Slave 5.6 off it and test. Be patient. Save yourself some heartache. Just my two cents. Keith On Feb 15, 2013 9:27 AM, Mike Franon kongfra...@gmail.com wrote: Thanks everyone for suggestions. I am doing this on a test box with a copy of our db before doing this on production db servers. I just upgraded from 5.0 to 5.1, and ran mysql_upgrade and see I have a few tables with the following error: error: Table upgrade required. Please do REPAIR TABLE `tablename` or dump/reload to fix it! I got this on 4 tables so far, but it still checking, my database is huge so might be a while. The question I have what is the best way to fix this? To install all I did was remove all of the 5.0, and then did a yum install 5.1 on my AWS machine. and then just started mysql. Should I instead do a complete mysqldump, and use that instead? On Thu, Feb 14, 2013 at 7:40 PM, Rick James rja...@yahoo-inc.com wrote: Sounds like something that, once discovered, can be fixed in the old version -- then it works correctly in both. That is what happened with a 4.0-5.1 conversion years ago. With 1000 different tables and associated code, we encountered two incompatibilities. One had to do with NULLs, the other with precedence of commajoin vs explicit JOIN. From: Singer Wang [mailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 3:41 PM To: Rick James Cc: Mihail Manolov; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 Its a very pedantic case, but we had a few instances where it was an issue at my last job. It basically involved multi-table deletes and aliasing.. I quote the change notes for MySQL 5.5.3 Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases. § In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for theUSING variant of multiple-table DELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. Now alias declarations outside table_references are disallowed for all multiple-table DELETE statements. Alias declarations are permitted only in the table_references part. Incorrect: DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2; Correct: DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2; DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2; § Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1: § § DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2 WHERE a1.id=a2.id; To correctly match an alias that refers to a table outside the default
Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Great thanks for the info, I guess the best way to do this is take a spare server, set it up with our standard setup, and then start the upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6 and test. On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Mike, 5.6 is GA now, so its stable release. Also you should not jump to 5.6 directly, atleast from 5.0. There are many bug fixes and changes in 5.1, so you should consider this way. 5.0--5.1--5.5 (all slaves first, and then the master) And further 5.5 -- 5.6 (again all slaves first and then the master) Hope this helps. Cheers! On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com wrote: I have 1 master with many slaves, using the master only for inserts and the rest are readers. Is 5.6 stable? Or better off to go to 5.5? If so do I need to make a few steps or can go straight from 5.0 to 5.6? Any best practices and recommendations? Thanks -- 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: file level encryption on mysql
Thanks! I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: file level encryption on mysql
Which is the best way ? I see you can do it from PHP itself http://coding.smashingmagazine.com/2012/05/20/replicating-mysql-aes-encryption-methods-with-php/ or can use mysql AES? http://security.stackexchange.com/questions/16473/how-do-i-protect-user-data-at-rest From what I understand we need two way and one way encryption. Is the best way what the first article is recommending? On Tue, Feb 5, 2013 at 9:20 AM, Reindl Harald h.rei...@thelounge.net wrote: you have to encrypt them in the application and make the key stored as safe as possible, however for a full intrution there is no way to protect data which can not be only hashed somewhere you need the information how to encrypt them Am 05.02.2013 15:18, schrieb Mike Franon: I tried all these methods and you are right this is not going to work for us. I am not a developer, does anyone have any good links or reference to the best way I can share with my developers on best way to encrypt and decrypt personal user info. We do not store credit cards, but want to store 3 tables that have email address, ip address, and personal info. On Sun, Feb 3, 2013 at 12:57 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 03.02.2013 18:52, schrieb Mike Franon: Hi, I was wondering what type of encryption for linux would you recommend to encrypt the database files on the OS level? I had a hard time starting the database after I moved it to a partiton with encryptFS I only need 3 tables encrypted and know it is better to do it from the application, but unfortunately that cannot happen for a while. Has anyone done OS file level encryption, and if so which one did they use? https://wiki.archlinux.org/index.php/Dm-crypt_with_LUKS but this all is useless in case of intrusion because the FS is unlocked and you have no gain - FS encryption only matters if your notebook or disks get stolen which is unlikely on a server -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
