Hi,
I am using InnoDb for all my tables. But Mysql.proc table is in MyISAM engine.
All were working very nicely, until recently in my First Generation Cloud SQL instance, I m facing this serious issue in my PRODUCTION instance. Most of the Stored Procedures installed are being automatically roll-backed to the previous state. I tried FLUSH TABLES as per this StackOverflow also, but no use.
I updated all logs here [1].
But the installed Procedures(modified version of an existing version) is roll backed to its previous original state. We did not restart our instance at any given time at least for the past 3-6 months.
Plz let us know how to fix this, is this our issue or Google issue? I can send you privately MySQL instance details if you want to inspect the behaviour. This is a serious reliability issue in production, where the installed procedures get rollbacked, now this happens ONLY to procedures so far and not to tables/views or any other objects.
For First Generation instances, the InnoDB storage engine is recommended, because it provides stronger data consistency guarantees. You may consider converting to InnoDB format, as recommended in the “What storage engine should I use?” paragraph of the FAQs.
You may send us your instance and project IDs through a private email, in view of closer situation inspection.
Education is the most powerful weapon which you can use to change the world
The last reply in your Stackoverflow reference contains a recommendation to switch to InnoDB as well.
Here, I cannot issue CLI commands for your ssomens-allprojects instance, that looks otherwise free of errors.
Education is the most powerful weapon which you can use to change the world
Education is the most powerful weapon which you can use to change the world
--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/L7pvTcaARtU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/ce8c302c-c28a-4e78-a9cb-62387fab6b09%40googlegroups.com.
The following list of information items may help us progress towards a resolution:
- Store procedure name.
- How was stored procedure created.
- Which command was used to list your stored procedures when they rolled back.
- How did you modify the stored procedures.
- Date and time when the rollbacks first happened.
- Do your stored procedures run without issues on other instances in the same project?
- What happens if you create a simple test procedure? Does it roll back as the others?
- What is the result of running the SHOW PROCEDURE STATUS; command?
- Store procedure name : SP_ACTIVE_CUSTOMERLIST
- How was stored procedure created. : MyISAM engine
- Which command was used to list your stored procedures when they rolled back.
SHOW PROCEDURE STATUS WHERE DB = 'EI_INT';
- How did you modify the stored procedures.
ACTIVE CC LIST stored procedure has temp table, I added new column.
- Date and time when the rollbacks first happened. : 2017-03-01 05:04:41
- Do your stored procedures run without issues on other instances in the same project?
No, I got issue in other instance in the same project
- What happens if you create a simple test procedure? Does it roll back as the others?
I created simple test procedure, But that one not roll back like the others
Simple Test Procedure:
DROP PROCEDURE IF EXISTS SP_CANDIDATE_DETAILS_INSERT; CREATE PROCEDURE SP_CANDIDATE_DETAILS_INSERT( IN CANDIDATENAME VARCHAR(50), IN EMAILID VARCHAR(50), IN DATEOFBIRTH DATE, IN CONTACTNO VARCHAR(10), IN REMARK TEXT, OUT SUCCESS_FLAG INTEGER) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET SUCCESS_FLAG=0; END; START TRANSACTION; SET AUTOCOMMIT = 0; SET SUCCESS_FLAG = 0; IF(REMARK='') THEN SET REMARK=NULL; END IF; IF(CANDIDATENAME IS NOT NULL AND EMAILID IS NOT NULL AND DATEOFBIRTH IS NOT NULL AND CONTACTNO IS NOT NULL) THEN INSERT INTO CANDIDATE_DETAILS (CANDIDATE_NAME, EMAIL_ID, DATE_OF_BIRTH, CONTACT_NO, REMARKS) VALUES (CANDIDATENAME, EMAILID, DATEOFBIRTH, CONTACTNO, REMARK); SET SUCCESS_FLAG=1; END IF; COMMIT; END;
- What is the result of running the SHOW PROCEDURE STATUS; command?
Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
EI_INT | SP_ACTIVE_CUSTOMERLIST | PROCEDURE | EIPHPDEV@% | 28-02-2017 06:01:58 | 28-02-2017 06:01:58 | DEFINER | utf8 | utf8_general_ci | utf8_general_ci |
--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/L7pvTcaARtU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/7c560991-fd47-4a31-9d3f-d429f0ccddec%40googlegroups.com.
Education is the most powerful weapon which you can use to change the world
It seems new procedures work. Have you attempted to drop your SP_ACTIVE_CUSTOMERLIST procedure and re-create it anew afterwards?As mentioned before, this being a MySQL specific issue, you may consider to rather open a specific question in Stackoverflow, where you are offered access to programming experts and a wealth of expertise.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/L7pvTcaARtU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/3f869a78-3de8-498c-96be-54e4ad821b82%40googlegroups.com.
Education is the most powerful weapon which you can use to change the world
At this stage, a text copy of the failing procedure may provide needed information. You may send this confidentially by private email.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/L7pvTcaARtU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/410cf2d8-20b1-4882-9395-bd8d887e307d%40googlegroups.com.
George,Plz send your personal email id. I 'll give the text copy of the failing procedure
Regards,SATTANATHAN D
PROJECT MANAGEREducation is the most powerful weapon which you can use to change the world