Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
2013/2/14 Mike Franon kongfra...@gmail.com 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. Do not forget to leave that spare server running for several days before upgrading the rest of machines to 5.6. If possible, I would do some stress tests or benchmarking to make sure it performs as you expect. Manuel.
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: Upgrading form mysql 5.0.90 to 5.5 or 5.6
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
RE: Upgrading form mysql 5.0.90 to 5.5 or 5.6
Ditto. I would mysqldump 5.0, load it onto a 5.5 (or 5.6) box that you have as a slave of the 5.0 master. The load may uncover some issues. Testing reads may uncover issues. The replication stream will test the writes; it may uncover issues. After being comfortable with that, build new slaves off the 5.5/5.6 box. Then cutover writes to that box. And jettison the 5.0 boxes. 5.5 - 5.6 may have more changes/improvements that all of 5.0-5.1-5.5. (Or, at least, Oracle salesmen would like you to believe it.) There is clearly a lot new optimizations in 5.6. So should you go all the way to 5.6? Maybe. You need to do a lot of shakedown anyway. -Original Message- From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] Sent: Thursday, February 14, 2013 2:22 PM To: Mike Franon Cc: Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 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. 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 -- 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: slave replication with lots of 'duplicate entry' errors
Recommend, for security reasons, you rectify that. If you need some SUPER action, create a stored procedure with privileges 'creator', so the security hole is still mostly contained. -Original Message- From: Robert Citek [mailto:robert.ci...@gmail.com] Sent: Thursday, February 14, 2013 2:59 PM To: Rick James Cc: mysql Subject: Re: slave replication with lots of 'duplicate entry' errors On Thu, Feb 14, 2013 at 5:46 PM, Rick James rja...@yahoo-inc.com wrote: Is it in read only mode? Furthermore, are all users logging in as non-SUPER users? Note: root bypasses the readonly flag! No. The user that is commonly used does have Super privileges. I am not sure why, but it does. Regards, - Robert
RE: slave replication with lots of 'duplicate entry' errors
Is it in read only mode? Furthermore, are all users logging in as non-SUPER users? Note: root bypasses the readonly flag! -Original Message- From: Manuel Arostegui [mailto:man...@tuenti.com] Sent: Thursday, February 14, 2013 1:55 PM To: Robert Citek Cc: mysql Subject: Re: slave replication with lots of 'duplicate entry' errors 2013/2/13 Robert Citek robert.ci...@gmail.com On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com wrote: Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, which are the most likely? [from off-list responder]: Other possibility: The replication is reading from master not from the point when the dump was done, but some time before and is fetching insert statements which are already in the dump. To prevent that I used the coordinates in the dump file included with --master-data=2. Could the coordinates still be off? Hello, Are you sure nothing is getting inserted directly into the slave? Is it in read only mode? If you're starting replication using the values provided by --master- data=2 (which should be something like): -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974', MASTER_LOG_POS=240814775; And if you're using the right IP, there's no reason to have duplicate entries unless someone is writing directly into the slave. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
Are you using all InnoDB? S On Thu, Feb 14, 2013 at 5:55 PM, Robert Citek robert.ci...@gmail.comwrote: On Thu, Feb 14, 2013 at 4:54 PM, Manuel Arostegui man...@tuenti.com wrote: 2013/2/13 Robert Citek robert.ci...@gmail.com On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com wrote: Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, which are the most likely? [from off-list responder]: Other possibility: The replication is reading from master not from the point when the dump was done, but some time before and is fetching insert statements which are already in the dump. To prevent that I used the coordinates in the dump file included with --master-data=2. Could the coordinates still be off? Hello, Are you sure nothing is getting inserted directly into the slave? Is it in read only mode? If you're starting replication using the values provided by --master-data=2 (which should be something like): -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974', MASTER_LOG_POS=240814775; And if you're using the right IP, there's no reason to have duplicate entries unless someone is writing directly into the slave. Manuel. According to the client, nothing is writing to the slave and everything is being logged at the master. I have not had the opportunity to independently verified any of this, yet. I do know that the slave is not in read-only mode, but rather we promise not to write to it mode. At the moment, I am trying to come up with plausible explanations for the observations. Thanks for your feedback. Regards, - Robert -- 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
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. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
On Thu, Feb 14, 2013 at 5:46 PM, Rick James rja...@yahoo-inc.com wrote: Is it in read only mode? Furthermore, are all users logging in as non-SUPER users? Note: root bypasses the readonly flag! No. The user that is commonly used does have Super privileges. I am not sure why, but it does. Regards, - Robert -- 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
Are you saying there was a regression in the Optimizer? (Sounds like a workaround is to do STRAIGHT_JOIN -- yuck!) I compared several hundred slow queries on 5.1 versus MariaDB 5.5. I found several improvements. -Original Message- From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] Sent: Thursday, February 14, 2013 3:30 PM To: Rick James Cc: Singer Wang; Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6 The ones that didn't work for me required table rearrangement in the query. MySQL 5.5 was very particular about the table join order. On Feb 14, 2013, at 6:11 PM, Rick James 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. 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 -- 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: Upgrading form mysql 5.0.90 to 5.5 or 5.6
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.idhttp://a1.id=a2.idhttp://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.idhttp://a1.id=a2.idhttp://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.commailto:rja...@yahoo-inc.com wrote: Singer, do you have some examples? -Original Message- From: Singer Wang [mailto:w...@singerwang.commailto:w...@singerwang.com] Sent: Thursday, February 14, 2013 2:59 PM To: Mihail Manolov Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.commailto: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.commailto: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.commailto: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.commailto: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
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. 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 -- 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
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. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
On Thu, Feb 14, 2013 at 4:54 PM, Manuel Arostegui man...@tuenti.com wrote: 2013/2/13 Robert Citek robert.ci...@gmail.com On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com wrote: Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, which are the most likely? [from off-list responder]: Other possibility: The replication is reading from master not from the point when the dump was done, but some time before and is fetching insert statements which are already in the dump. To prevent that I used the coordinates in the dump file included with --master-data=2. Could the coordinates still be off? Hello, Are you sure nothing is getting inserted directly into the slave? Is it in read only mode? If you're starting replication using the values provided by --master-data=2 (which should be something like): -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974', MASTER_LOG_POS=240814775; And if you're using the right IP, there's no reason to have duplicate entries unless someone is writing directly into the slave. Manuel. According to the client, nothing is writing to the slave and everything is being logged at the master. I have not had the opportunity to independently verified any of this, yet. I do know that the slave is not in read-only mode, but rather we promise not to write to it mode. At the moment, I am trying to come up with plausible explanations for the observations. Thanks for your feedback. Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
Yes. Except for a handful of static MyISAM tables. But the tables that are experiencing the issues are all InnoDB and large (a dozen or so fields, but lots of records.) Regards, - Robert On Thu, Feb 14, 2013 at 5:59 PM, Singer Wang w...@singerwang.com wrote: Are you using all InnoDB? S -- 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
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. 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 -- 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
The ones that didn't work for me required table rearrangement in the query. MySQL 5.5 was very particular about the table join order. On Feb 14, 2013, at 6:11 PM, Rick James 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. 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 -- 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: slave replication with lots of 'duplicate entry' errors
2013/2/13 Robert Citek robert.ci...@gmail.com On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com wrote: Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, which are the most likely? [from off-list responder]: Other possibility: The replication is reading from master not from the point when the dump was done, but some time before and is fetching insert statements which are already in the dump. To prevent that I used the coordinates in the dump file included with --master-data=2. Could the coordinates still be off? Hello, Are you sure nothing is getting inserted directly into the slave? Is it in read only mode? If you're starting replication using the values provided by --master-data=2 (which should be something like): -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974', MASTER_LOG_POS=240814775; And if you're using the right IP, there's no reason to have duplicate entries unless someone is writing directly into the slave. Manuel.
RE: Please check the stored procedure
To adjust a table name within a SQL statement, you need to create a
string with the updated values and use PREPARE/EXECUTE
See below, I am replacing your CONCAT with the complete insert statement
The example below is also assuming the value in the WHERE clause should
be adjusted to the number, instead of hard-coded to '9'
set @str = concat('Insert Into test (Panel_Id) select Panel_Id
from ',Project_Number_val,'_List where Project_Number_val='
,',Project_Number_val,');
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
-Original Message-
From: Girish Talluru [mailto:girish.dev1...@gmail.com]
Sent: Tuesday, February 12, 2013 1:37 AM
To: mysql@lists.mysql.com
Subject: Please check the stored procedure
DROP PROCEDURE IF EXISTS Cursor_Test;# MySQL returned an empty result
set
(i.e. zero rows).
DELIMITER $$
CREATE PROCEDURE Cursor_Test()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Project_List_val VARCHAR(255);
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE projects_cur CURSOR FOR
SELECT Project_Id
FROM Project_Details;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN projects_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH projects_cur
INTO Project_Number_val;
IF no_more_rows THEN
CLOSE projects_cur;
LEAVE the_loop;
END IF;
SET Project_List_val=CONCAT(`Project_Number_val`,'_List') Please
check am I doing CONCAT correct here?
Insert Into test (Panel_Id) select Panel_Id from Project_List_val where
Project_Number_val='9'; ---Is this taking 9_List as table name?
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
select num_rows, loop_cntr;
END $$# MySQL returned an empty result set (i.e. zero rows).
DELIMITER
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.1: incorrect arithmetic calculation
bkp0# mysql h5000_bill Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1643184 Server version: 5.1.68-log FreeBSD port: mysql-server-5.1.68 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); +--++---+---+--++-++ | ID | ContractID | Month | ServiceID | Comment | Cost | Discont | Amount | +--++---+---+--++-++ | 10551851 | 10369 | 497 | 1 | №20440 | 1.67 | 0.10 |365 | | 10551854 | 10369 | 497 | 2 | №20441 | 150.00 | 1.00 | 1 | +--++---+---+--++-++ 2 rows in set (0.00 sec) mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); ++ | Summ | ++ | 548.59 | ++ 1 row in set (0.00 sec) mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); ++ | Summ | ++ | 548.594985 | ++ 1 row in set (0.00 sec) mysql SELECT 365 * 1.67 * ( 1 - 0.10); +--+ | 365 * 1.67 * ( 1 - 0.10) | +--+ | 548.5950 | +--+ 1 row in set (0.00 sec) mysql === but, my desktop calculator gives the result 548.60 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
Agreed. Will do that along with several other possible changes. But for the moment, I'm still gathering information and coming up with plausible models. Will also be turning on general mysql logging on both Master and Slave, at least briefly, to see what statements are being run on both. Regards, - Robert On Thu, Feb 14, 2013 at 6:13 PM, Rick James rja...@yahoo-inc.com wrote: Recommend, for security reasons, you rectify that. If you need some SUPER action, create a stored procedure with privileges 'creator', so the security hole is still mostly contained. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
