workaround for MySQL server has gone away error with flask

I had this issue when my flask simple app kept failing the next day it was started.
It kept giving the error "MySQL server has gone away".

I tried a couple of things like, starting the mysql session for every request instead of for each flask process (app-wide). But apparently flask doesn't like that.
At least not when using flaskext.mysql's MySQL.

I think I didn't have much luck trying to change the timeout on flask either, as this module just didn't let me.

I could have changed the module to use mysql, but I didn't have much time to finish the damn thing, so in the end I just set a cron job to access the mysql select query using page every 10 min or so. Ugly, but simple workaround.

It was like
curl -k https://localhost/some_app/index.html

every 10m.

flask app returns select with old data

I had this issue where my flask app kept giving me different results (doing a select query).

And it only happened on production, not in the development one.
Apparently due to the fact that flask has a couple of fork processes running, each with their own mysql session.
Even when you commit after an insert/update query, you still need for some reason to do a commit before a select in order to see the latest results.

The following is a sample code with some basic functions in order to do select and update queries.
I just call validate_sql_conn()  for pretty much everything I need to do in mysql, so it's always committing itself.

from flask import Flask, render_template, url_for, request, send_from_directory
from flaskext.mysql import MySQL
import yaml

app = Flask(__name__, template_folder='views')

def get_mysql_conf(mysql_conf_file="some_app_name_conf/mysql_conf.yml"):
    "gets the conf to use for the mysql connection"
    with open(mysql_conf_file, 'r') as f:
        content = f.read()
    yaml_content = yaml.load(content, Loader=yaml.FullLoader)
    return yaml_content

mysql = MySQL()
mysql_conf = get_mysql_conf()
app.config['MYSQL_DATABASE_USER'] = mysql_conf['mysql_database_user']
app.config['MYSQL_DATABASE_PASSWORD'] = mysql_conf['mysql_database_password']
app.config['MYSQL_DATABASE_DB'] = mysql_conf['mysql_database_db']
app.config['MYSQL_DATABASE_HOST'] = mysql_conf['mysql_database_host']
mysql.init_app(app)

conn = mysql.connect()
cursor = conn.cursor()


def validate_sql_conn():
    """
    uses global connection variable (conn) and recreates 
    it if seems to have dropped
    """
    global conn
    global cursor
    try:
        cursor.execute("show tables")
    except:
        conn = mysql.connect()
        cursor = conn.cursor()
    conn.commit()
    return True
        

def run_sql_and_get_results(sql_query, cursor=cursor):
    validate_sql_conn()
    cursor.execute(sql_query)
    data = cursor.fetchall()
    return data


def run_sql_and_commit(sql_query, cursor=cursor, conn=conn):
    validate_sql_conn()
    cursor.execute(sql_query)
    data = cursor.fetchall()
    commit_data = conn.commit()
    return commit_data

connect to mysql with perl

Just as the title says:
Install the package libdbi-perl.

Create the databases/tables/users/permissions as needed.
This is a sample script:

#!/usr/bin/env perl

use DBI;

# MySQL CONFIG VARIABLES
$host = "localhost";
$database = "your_database";
$tablename = "test";
$user = "this_is_me";
$pw = "this_is_my_password";

#connect to database
$connect = DBI->connect('DBI:mysql:database=' . $database . ';host=' . $host, $user, $pw);


# select stuffs
my $sel = $connect->prepare("select * from $tablename");
$sel->execute;
while( @row = $sel->fetchrow_array) {
    foreach (@row) {
 print;
 print "\t";
    }
    print "\n";
}

This code prints every row, tab-separated from the selected table.

backup a mysql table

Take a backup:
mysqldump -p database_name table_name >some_file.sql


Restore:
mysqldump -p database_name <some_file.sql