Finally a MySQL connector that works with Django and Python 3

The django community isn't the most supportive of mysql. They have valid arguments on why postgres is superior is to mysql and I don't disagree with them. Except that I have a level of comfort with MySQL that I've gotten from being paid to work with it the past six years. I'm not necessarily forgiving MySQL for its quirks, but I know I can depend on the large community and the vast resources if I ever run into problems. On the chance that one of my projects 'hockey sticks', there are experts who have gotten MySQL to scale with the traffic.

For those that are techno puritans, they sometimes forget that MariaDB is a solid open initiative that deserves support from the community as it adheres to the same open source principles as Postgres.

I've been patiently creating my app in Python 2.7 eagerly waiting for one of the MySQL connectors to work with Python 3 and Django. Finally in the past month MySQL released an official one.

I tested this connector with the Django tutorial app and got ./manage.py syncdb to create some tables for my test models. More testing will be needed to see if south and other parts of the Django ORM are working correctly.

http://dev.mysql.com/doc/connector-python/en/connector-python-django-backend.html

System:
Fedora 20
Django
Python 3.3

Step 1:
sudo yum install python-virtualenv

Step 2 - Create your python 3 environment:
virtualenv -p /usr/bin/python3 myenv

# On fedora python 3 is sym linked to python3.3

cd myenv
source bin/activate

Step 3 - Install Django and the official mysql connector:
pip install django # at the time of this writing it is 1.6.2.
pip install mysql-connector-python

# This matches the connector version from the mysql link above.
# Downloading mysql-connector-python-1.1.5.zip (337kB): 337kB downloaded

Step 4 - Configure Django settings.py to use this database connector:

DATABASES = {
    'default': {
        'NAME': 'mydatabase',
        'ENGINE': 'mysql.connector.django',
        'USER': 'myuser',
        'PASSWORD': 'secretpassword',
        'OPTIONS': {
          'autocommit': True,
        },
    }
}

Step 5:
Create your models and run ./manage syncdb

Update: Feb 12, 2014
http://dev.mysql.com/doc/relnotes/connector-python/en/news-1-1-5.html

Changes in MySQL Connector/Python 1.1.5 (2014-01-31)
Functionality Added or Changed
- Connector/Python is now compatible with Django 1.6. (Bug #17857712)

You can find the Fedora 20 rpm from here as 1.1.5 is being tested:
https://dl.fedoraproject.org/pub/fedora/linux/updates/testing/20/x86_64/mysql-connector-python3-1.1.5-1.fc20.noarch.rpm 

I tested with Django 1.6.x

Can't watch mpeg-4 aac h.264 video in Linux Firefox

Updated for Fedora 19 - July 2013

Every new Fedora install, I forget how to watch apple trailers in my browser. I get the mpeg-4 aac and h.264 decoder missing message.

This can be solved in three steps:

1) Add the rpmfusion repository.

su -c 'yum localinstall --nogpgcheck http://download1.rpmfusion.org/free/fedora/releases/19/Everything/x86_64/os/rpmfusion-free-release-19-1.noarch.rpm http://download1.rpmfusion.org/nonfree/fedora/releases/19/Everything/x86_64/os/rpmfusion-nonfree-release-19-1.noarch.rpm'

2) Install vlc

sudo yum install vlc

3) Lastly this one extra command allowed the movie to play

sudo yum install gstreamer1-libav

Fedora 19 and Gnome 3.8 - Get back your terminal background transparency

Gnome Terminal in 3.8 had the transparency option dropped/removed not because it wasn't a useful feature, but because implementation between new and old code was difficult.

Anyways with Fedora 19 and Gnome 3.8, there is a way to get back this feature. I ended up using the window manager cinnamon as it has advanced features of gnome, yet includes lots of the intuitive right click menu options you expect on any desktop.

Step 1:
sudo yum install devilspie

Step 2:
mkdir ~/.devilspie

Step 3:
// Add any file in this dir ending in .ds and devilspie will process it.

vim ~/.devilspie/terminal-opacity.ds

Step 4:
// In this file add this line of code
// Terminal is what fedora uses as the name of these terminals.

(if
  (matches (window_name) "Terminal")
  (opacity 85)
)

Step 5:
// Add in .bash_profile the following line. It will
devilspie -a &

Step 6: Logout and log back in.

Any new terminal console windows will now be transparent - including the menu bars.

Django change the trailing slash url convention to no trailing slash

URL Specs and search engines state that urls with and without trailing can affect your SEO rankings (and a user's ability to type in a link).

eg. example.com/page and example.com/page/

This affects some web frameworks, such as Django, as the regular expression for routing these URLs will be defined for only one of these cases. By convention Django will add a trailing slash, if a non trailing slash url is typed into the browser.

I prefer to not have a trailing slash as it is one less character to type in. I should note that some sites, like stackoverflow.com or twitter.com does not give a crap either way.

We can add this feature to Django by creating a custom middleware class that intercepts the request.path and rewrites it to the non trailing slash url.

What is middleware?

Middleware is a framework of hooks into Django’s request/response processing. It’s a light, low-level “plugin” system for globally altering Django’s input or output.

Or in layman's terms, it processes values/urls before it is sent to Django's views. You can find Django's middleware that redirects to the trailing slash url in:

django/middleware/common.py

Which was added to your project in
settings.py

MIDDLEWARE_CLASSES = (     'django.middleware.common.CommonMiddleware',


How to get Django to redirect trailing slashes to the non trailing slash url if it exists.

Step 1:

Add this line in settings.py

APPEND_SLASH = False

Step 2:

Create these paths and files

./common/
./common/__init__.py
./common/redirect.py

Step 3: Add the class to the MIDDLEWARE definition

MIDDLEWARE_CLASSES = (
    'common.redirect.RedirectTrailingSlashMiddleware',
    'django.middleware.common.CommonMiddleware',

# It must be the first class as we want to catch it before django does.

Step 4: Add this code to redirect.py

For /admin urls, I have kept the trailing slash as I don't want to break Django's admin methods

from django.conf import settings
from django.core import urlresolvers
from django import http

'''
Based on django/middleware/common.py

Django convention is to add trailing slashes to most urls
This method does the opposite and redirects trailing slashes to the
no trailing slash url if it exists
'''
class RedirectTrailingSlashMiddleware(object):

    def process_request(self, request):
        if settings.APPEND_SLASH:
           return 
        
        if '/admin' in request.path:
            settings.APPEND_SLASH = True         
            return

        new_url = old_url = request.path

        if (old_url.endswith('/')):
            urlconf = getattr(request, 'urlconf', None)
            if (not urlresolvers.is_valid_path(request.path_info, urlconf) and
                urlresolvers.is_valid_path(request.path_info[:-1], urlconf)):
                new_url = new_url[:-1]
                if settings.DEBUG and request.method == 'POST':
                    raise RuntimeError((""
                    "You called this URL via POST, but the URL ends "
                    "in a slash and you have APPEND_SLASH set. Django can't "
                    "redirect to the non-slash URL while maintaining POST data. "
                    "Change your form to point to %s (note no trailing "
                    "slash), or set APPEND_SLASH=True in your Django "
                    "settings.") % (new_url))

        if new_url == old_url:
            # No redirects required.
            return

        return http.HttpResponsePermanentRedirect(new_url)        
        

Basic Master Slave setup on MySQL 5.5

There are more than enough master slave mysql tutorials, and I'm adding another one to the pile for my own personal reference.

Setup:
Two micro ec2 instances on amazon with mysql55 installed. Make sure port 3306 is open between servers.

Step 1: Edit /etc/my.cnf on master and slave

On master:

[mysqld]
log-bin=mysql-bin
server-id=1

On slave

[mysqld]
server-id=2

Side Note:  you can't put master-host settings here as they've been deprecated since 5.1.17. MySQL will save the values internally and will automatically reference them when a reboot is done.

Restart Both Servers

service mysqld restart

Step 2: Create Slave User On Master

On Master run the mysql command:

CREATE USER 'slave2'@'[SLAVE IP OR ADDRESS]' IDENTIFIED BY '[Fancy Password]';
GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'[SLAVE IP OR ADDRESS]';

Side Note: mydbname.* will not work, must use *.*

Step 3: Finish current commands and lock the tables

On Master:

flush tables with read lock

This will finish all commands and stop new ones from happening. To release the locks we need to run unlock tables, but not till later.

show master status

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |     1859 |              |                  |
+------------------+----------+--------------+------------------+

We'll have to keep track of the file and position if we use
--lock-all-tables instead of --master-data. I'm using the latter

Don't close your mysql console as this will unlock the tables. For the next step create a new terminal on the master server.

Step 4: Create a dump of the data on the master server

On master run:

mysqldump -u root -p --all-databases --master-data > moodle.sql

and copy the file to the slave server.

Step 5: Setup the slave server with the master login info.

On slave go to mysql command prompt

stop slave;

CHANGE MASTER TO
MASTER_HOST='[MASTER IP OR ADDRESS]',
MASTER_USER='slave2',
MASTER_PASSWORD='[Fancy Password]';

# MASTER_LOG_FILE='mysql-bin.000002',
# MASTER_LOG_POS=1859;

On the mysql doc site, these two lines are not needed as they are included in the dump file when use used --master-data

Step 6: Import the dump sql file on the slave server.

Use this to import the dump file. (As mentioned this will add the master log file pos too.)

mysql -u root -p < moodle.sql

Step 7: Start the replication

We still have that mysql command prompt on master so we are now ready to release the lock.

unlock tables;

Then on slave we can begin the replication

start slave;

Step 8: Confirm the slave is working

show slave status\G

and you should see

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

---

Moodle Query to Retrieve Outcomes Completed in Recent Weeks

To track outcome progress in Moodle for the past week, this ad-hoc aka custom query reports, lists the numbers of outcomes completed each week. It is formatted to show the weekly total for each course as shown in this image:

-- GROUP_CONCAT lists all the weeks and the standards completed in that week in a single table cell
-- By adding a order by in the group concat, we get the proper ordering of weeks (weeks were in the incorrect order without it)
-- CHAR(63) because the '?' is not accepted by Moodle Ad-Hoc Queries.

SELECT
    outcomes.courseid,
    CONCAT('', C.fullname,''
) as course_name,
    CONCAT(
        GROUP_CONCAT(
            CONCAT(outcomes.weekstr, concat(' [', outcomes.count, ']'))
            ORDER BY courseid ASC, w ASC
            SEPARATOR '
'
        ), '
Total:', SUM(outcomes.count)
    ) as outcomes_per_week
FROM
(

-- This sub query combines the three queries used by Moodle to get the outcome count
-- The WHERE statement
--  a) gets the day 16 weeks ago
--  b) We want to include results beginning on Sunday of that week
-- The SELECT weekstr - each day belongs to a week. We match each day with the start of its week.
-- eg. For the week of Wed Jan 5, the weekstr would be Sun-2

    SELECT
        goc.courseid, COUNT(gg.finalgrade) AS count,

        DATE_FORMAT(
            str_to_date(
                concat(year(from_unixtime(gg.timemodified)), LPAD(week(from_unixtime(gg.timemodified)), 2, 0), 'sunday'  ),
                '%X%V %W'),
            '%b-%e') weekstr,

        week(from_unixtime(gg.timemodified)) as w
    FROM prefix_grade_outcomes go
    JOIN prefix_grade_outcomes_courses goc
        ON go.id = goc.outcomeid
    JOIN prefix_grade_items as gi
        ON goc.outcomeid = gi.outcomeid AND
            gi.courseid = goc.courseid
    JOIN prefix_grade_grades as gg
        ON gg.itemid = gi.id
    WHERE gg.timemodified >= UNIX_TIMESTAMP(STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 16 week), '%Y%V Sunday'), '%X%V %W'))
    GROUP BY courseid, week(from_unixtime(gg.timemodified))
) as outcomes

JOIN prefix_course as C
    ON C.id = outcomes.courseid
GROUP BY courseid
ORDER BY C.fullname asc 

Note: Optimization can be done to move the weekstr to the outside query.

Run only a single instance of a cron job and prevent overlap

When dealing with web applications often a cron process must be executed at most once. If a cron job overlaps, database queries can cause performance issues or at worse deadlocks and stale processes.

Here is a script that solves this problem. Explained with detail as there are a lot of things happening in 5 lines of code.

#!/bin/bash

# Installation
# mkdir /var/run/moodle
# chown root.apache /var/run/moodle
# chmod 775 /var/run/moodle
# copy this file and make it executable by the cron user. 

# chmod this file 744

# add it to crontab or crontab -e -u apache 

# Explanation
# 1. set -e tells a bash script to exit whenever a non zero value is i
#  returned (0 means function executed without error)
# 2. flock needs 200 or any int to label the file descriptor
# 3. The ( brackets ) execs each line of commands in order and check to 
#  see if they return 0. 
# 4. 200> tells the fd 200 to create the lock file if it doesn't already 
#  exist 
# 5. -n nonblock, will return 1 if the lock is taken. as cron is being 
#  ran every 5-10 mins we can wait for the next one
# 6. trap, if cntrl-c is called or a command is killed, it will execute 

#  the command and exit. Our case it removes the lock file.

# Note: This works great for cron and flock files. May be an issue with 

#  race conditions if something other than flock eg. echo 'busy' > file.pid.

LOCKFILE=/var/run/moodle/moodlecron.lock
set -e
(
    flock -n 200 
    trap "rm $LOCKFILE" EXIT 
    # Add commands to execute
    /usr/bin/php /var/www/html/moodle/admin/cli/cron.php  
) 200>$LOCKFILE