Urgent - All Stored procedures(MYISAM engine) is being automatically roll-backed in our First Generation Google Cloud SQL.
Dhandapani Sattanathan
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.
George (Cloud Platform Support)
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.
SN
PROJECT MANAGER
Education is the most powerful weapon which you can use to change the world
George (Cloud Platform Support)
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.
To convert a table to InnoDB, you are supposed to use a statement containing “engine=InnoDB”. This is detailed in the MySQL documentation, at “15.8.5 Converting Tables from MyISAM to InnoDB”.
SN
I want to migrate only mysql.proc(mysql system) table to InnoDB engine, but as per the below MySQL documentation, it seems not a better solution to do that. it states not to migrate from MyISAM to InnoDB for those system oriented tables.
PROJECT MANAGER
Education is the most powerful weapon which you can use to change the world
George (Cloud Platform Support)
SN
PROJECT MANAGER
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.
George (Cloud Platform Support)
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?
SN
- 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.
George (Cloud Platform Support)
SN
PROJECT MANAGER
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.
George (Cloud Platform Support)
SN
PROJECT MANAGER
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.
Nicholas (Google Cloud Support)
On Monday, March 13, 2017 at 11:18:44 PM UTC-4, Dhandapani Sattanathan wrote:
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