Our App is developed with the following requirements
Front end: HTML and Google apps script
Back end: Google Cloud SQL,MySQL VER 5.6.21, engine= InnoDB
We are using Stored Procedures to access CLOUD SQL. This stored procedure will be called via Google Apps Script.
Example Stored Procedure and the process of execution via Google Apps Script as below...
Step 1: Store procedure will create a user and giving grant access for store procedures, tables,triggers, views
DROP PROCEDURE IF EXISTS SP_TEST;
CREATE PROCEDURE SP_TEST(OUT SUCCESS_MESSAGE TEXT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET SUCCESS_MESSAGE=0;
END;
START TRANSACTION;
SET AUTOCOMMIT = 0;
DROP USER TESTUSER;
CREATE USER 'TESTUSER'@'%' IDENTIFIED BY 'TESTUSER';
GRANT EXECUTE ON PROCEDURE PROC_NAME1 TO 'TESTUSER'@'%';
GRANT EXECUTE ON PROCEDURE PROC_NAME2 TO 'TESTUSER'@'%';
SET SUCCESS_MESSAGE=1;
COMMIT;
END;
Step 2: script used in Google apps script to call store procedure/access sql table
try
{
//OPEN CONNECTION
var conn=Jdbc.getCloudSqlConnection("jdbc:google:rdbms://" + DB_INSTANCE + "/" + DB_SCHEMA, DB_USER, DB_PASSWORD);
conn.setAutoCommit(false);
//CALL STORE PROCEDURE
var create_stmt = conn.createStatement();
create_stmt.execute("CALL SP_TEST (@SUCCESS_MESSAGE)”);
create_stmt.close();
//SELECT RESULT RETURNED FROM STORE PROCEDURE
var stmt_rolecrinsrtflag = conn.createStatement()
var flag_rolecrinsrtselect="SELECT @SUCCESS_MESSAGE";
var flag_rolecrinsrtrs=stmt_rolecrinsrtflag.executeQuery(flag_rolecrinsrtselect);
if(flag_rolecrinsrtrs.next())
var flag_rolecrinsrtinsert=flag_rolecrinsrtrs.getString("@SUCCESS_MESSAGE");
flag_rolecrinsrtrs.close();
stmt_rolecrinsrtflag.close();
conn.commit();
//CLOSE CONNECTION
conn.close();
return flag_rolecrinsrtinsert;
}
catch(err)
{
//to do rollback,if any exception..
conn.rollback();
}
We have more than 200 sp’s and 98 tables. All the stored procedure and all tables will be loaded in Google Cloud Sql via root like user only.
After calling the Store Procedure via Google apps script , user will be created in user table and given stored procedure will be granted access for the created user.
But Sometimes we are getting the following issues when we call any store procedure/ accessing the sql table via Google Apps Script . Initially it worked well, but recently, only we are facing these issues.
Error 1:"INVALID CONNECTION ID" is thrown and the following things are observed
TESTUSER wiped in the mysql.user table as below
Execute permission revoked for the PROC_NAME1,PROC_NAME2.
Some Store procedure which is created already has been automatically dropped such as PROC_NAME2
cant able to proceed with the remaining execution of Apps Script
Error 2: "THIS CONNECTION IS CLOSED"
- We cant able to proceed the remaining execution..
In script side if any issue/exception occurs , we need to proceed the execution, i.e., we need to drop the temp table created during the STORE PROCEDURE execution process.
Since the above issues occurs, we can't able to proceed the script execution further.
And also when we execute the store procedure, sometime we are getting issue like
Error 3: "Incorrect key file for table './mysql/procs_priv.MYI'; try to repair it"
- After this issue, cant able to execute or run any Store Procedure/ access Google cloud Sql.
Pls refer the below link..
Having Result of sample Stored Procedure that is given above-Before Issue and After Issue...
https://docs.google.com/a/ssomens.com/document/d/1N5_-O2UfescCmWe9IorteUZUh7gtgkF9-RwubvF4MhM/edit
