Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Eric, There are ways to resync data that don't involve all this as well: Maatkit has some tools I've looked with great interest at Maatkit, but their tools are replete with warnings about dangers, bugs, and crashes. They certainly do not inspire confidence. I'm the primary author of Maatkit. What can I say -- you could go buy a commercial off-the-shelf tool and believe the song and dance they feed you about the tool being perfect. At least with Maatkit, you get transparency. We make a concerted effort to update the RISKS section of each tool with each release, so there is full disclosure. I think Maatkit is by far the best solution for live master-slave sync in most real-world situations. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
Hi, I just would like to know which version of MySQL support the creation of database triggers with the same action and event for one table. Because if i create the the triggers as below i am getting error. First trigger (Succesfull) ++ CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END Second trigger (generating error) +++ CREATE TRIGGER AUDIT_REPOSITORY_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUMER_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,old.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY );END - // ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' Or any other alternate to do the same ? Thanks Regards Jeetendra Ranjan
Re: ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
On Wed, 9 Dec 2009 16:39:33 Jeetendra Ranjan wrote: Hi, I just would like to know which version of MySQL support the creation of database triggers with the same action and event for one table. I'm not sure there is any plan to implement support for multiple triggers with the same action and timing. In your case, the solution is simply to combine the two: CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUMER_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,old.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END// - Jesper Because if i create the the triggers as below i am getting error. First trigger (Succesfull) ++ CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CON SUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GE NDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP ,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSU MER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_O F_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFI LE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END Second trigger (generating error) +++ CREATE TRIGGER AUDIT_REPOSITORY_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUME R_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_ VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,o ld.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW _VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY );END - // ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' Or any other alternate to do the same ? Thanks Regards Jeetendra Ranjan -- Jesper Wisborg Krogh Team Lead Noggin IT • Level 8, 28 Foveaux St, Surry Hills NSW Australia 2010 T: +61 2 9219 8800 • F: +61 2 9280 4669 • E: jes...@noggin.com.au http://www.noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Force index command in sql query
Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? Thanks Regards Jeetendra Ranjan
Re: ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
I just would like to know which version of MySQL support the creation of database triggers with the same action and event for one table. I'm not sure there is any plan to implement support for multiple triggers with the same action and timing. I certainly hope so... If not, it seems yet another half-baked implementation of a feature they never understood the power of ;-) /Martijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL variables
Good day guys (and girls if any) I am constantly in a position where variables on a production mysql database needs to be changed. The database runs a 24/7 system and thus to reboot is not preffered and should be the absolute last resort. How can I set variables to be effective immediately? ( I am still a junior in mysql dba and still learning) If I set these will it still be effective should the database be restarted somewhere in the future? Thanks in advance for your help. Regards
