Setup Scalable WordPress Sites on Kubernetes

Setup Scalable WordPress Sites on Kubernetes

This article is about how to deploy a scalable WordPress site on Google Kubernetes Engine.

Using the container version of the popular LEMP stack:

  • Linux (Docker containers)
  • NGINX
  • MySQL (Google Cloud SQL)
  • PHP (PHP-FPM)

Google Cloud Platform Pricing

Deploying a personal blog on Kubernetes sounds like overkill (I must admit, it does). Still, it is fun and an excellent practice to containerize a traditional application, WordPress, which is harder than you thought. More importantly, the financial cost of running a Kubernetes cluster on GKE could be pretty low if you use preemptible VMs which also means native Chaos Engineering!

ref:
https://cloud.google.com/pricing/list
https://cloud.google.com/sql/pricing
https://cloud.google.com/compute/all-pricing

Google Cloud SQL

Cloud SQL is the fully managed relational database service on Google Cloud, though it currently only supports MySQL 5.6 and 5.7.

You can simply create a MySQL instance with few clicks on Google Cloud Platform Console or CLI. It is recommended to enable Private IP that allows VPC networking and never exposed to the public Internet. Nevertheless, you have to turn on Public IP if you would like to connect to it from your local machine. Otherwise, you might see something like couldn't connect to "xxx": dial tcp 10.x.x.x:3307: connect: network is unreachable. Remember to set IP whitelists for Public IP.

Connect to a Cloud SQL instance from your local machine:

$ gcloud components install cloud_sql_proxy
$ cloud_sql_proxy -instances=YOUR_INSTANCE_CONNECTION_NAME=tcp:0.0.0.0:3306

$ mysql --host 127.0.0.1 --port 3306 -u root -p

ref:
https://cloud.google.com/sql/docs/mysql
https://cloud.google.com/sql/docs/mysql/sql-proxy

Google Kubernetes Engine

The master of your Google Kubernetes Engine cluster is managed by GKE itself, as a result, you only need to provision and pay for worker nodes. No cluster management fees.

You can create a Kubernetes cluster on Google Cloud Platform Console or CLI, and there are some useful settings you might like to turn on:

Node Pools

Over-provisioning is human nature, so don't spend too much time on choosing the right machine type for your Kubernetes cluster at the beginning since you are very likely to overprovision without real usage data at hand. Instead, after deploying your workloads, you can find out the actual resource usage from Stackdriver Monitoring or GKE usage metering, then adjust your node pools.

Some useful node pool configurations:

  • Enable preemptible nodes
  • Access scopes > Set access for each API:
    • Enable Cloud SQL

After the cluster is created, you can now configure your kubectl:

$ gcloud container clusters get-credentials YOUR_CLUSTER_NAME --zone YOUR_SELECTED_ZONE --project YOUR_PROJECT_ID
$ kubectl get nodes

If you are not familiar with Kubernetes, check out The Incomplete Guide to Google Kubernetes Engine.

WordPress

Here comes the tricky part, containerizing a WordPress site is not as simple as pulling a Docker image and set replicas: 10 since WordPress is a totally stateful application. Especially:

  • MySQL Database
  • The wp-content folder

The dependency on MySQL is relatively easy to solve since it is an external service. Your MySQL database could be managed, self-hosted, single machine, master-slave, or multi-master. However, horizontally scaling a database would be another story, so we only focus on WordPress now.

The next one, our notorious wp-content folder which includes plugins, themes, and uploads.

ref:
https://engineering.bitnami.com/articles/scaling-wordpress-in-kubernetes.html
https://dev.to/mfahlandt/scaling-properly-a-stateful-app-like-wordpress-with-kubernetes-engine-and-cloud-sql-in-google-cloud-27jh
https://thecode.co/blog/moving-wordpress-to-multiserver/

User-uploaded Media

Users (site owners, editors, or any logged-in users) can upload images or even videos on a WordPress site if you allow them to do so. For those uploaded contents, it is best to copy them to Amazon S3 or Google Cloud Storage automatically after a user uploads a file. Also, don't forget to configure a CDN to point at your bucket. Luckily, there are already plugins for such tasks:

Both storage services support direct uploads: the uploading file goes to S3 or GCS directly without touching your servers, but you might need to write some code to achieve that.

Pre-installed Plugins and Themes

You would usually deploy multiple WordPress Pods in Kubernetes, and each pod has its own resources: CPU, memory, and storage. Anything writes to the local volume is ephemeral that only exists within the Pod's lifecycle. When you install a new plugin through WordPress admin dashboard, the plugin would be only installed on the local disk of one of Pods, the one serves your request at the time. Therefore, your subsequent requests inevitably go to any of the other Pods because of the nature of Service load balancing, and they do not have those plugin files, even the plugin is marked as activated in the database, which causes an inconsistent issue.

There are two solutions for plugins and themes:

  1. A shared writable network filesystem mounted by each Pod
  2. An immutable Docker image which pre-installs every needed plugin and theme

For the first solution, you can either setup an NFS server, a Ceph cluster, or any of network-attached filesystems. An NFS server might be the simplest way, although it could also easily be a single point of failure in your architecture. Fortunately, managed network filesystem services are available in major cloud providers, like Amazon EFS and Google Cloud Filestore. In fact, Kubernetes is able to provide ReadWriteMany access mode for PersistentVolume (the volume can be mounted as read-write by many nodes). Still, only a few types of Volume support it, which don't include gcePersistentDisk and awsElasticBlockStore.

However, I personally adopt the second solution, creating Docker images contain pre-installed plugins and themes through CI since it is more immutable and no network latency issue as in NFS. Besides, I don't frequently install new plugins. It is regretful that some plugins might still write data to the local disk directly, and most of the time we can not prevent it.

ref:
https://serverfault.com/questions/905795/dynamically-added-wordpress-plugins-on-kubernetes

Dockerfile

Here is a dead-simple script to download pre-defined plugins and themes, and you can use it in Dockerfile later:

#!/bin/bash
set -ex

mkdir -p plugins
for download_url in $(cat plugins.txt)
do
    curl -Ls $download_url -o plugin.zip
    unzip -oq plugin.zip -d plugins/
    rm -f plugin.zip
done

mkdir -p themes
for download_url in $(cat themes.txt)
do
    curl -Ls $download_url -o theme.zip
    unzip -oq theme.zip -d themes/
    rm -f theme.zip
done

plugins.txt and themes.txt look like this:

https://downloads.wordpress.org/plugin/prismatic.2.2.zip
https://downloads.wordpress.org/plugin/wp-githuber-md.1.11.8.zip
https://downloads.wordpress.org/plugin/wp-stateless.2.2.7.zip

Then you need to create a custom Dockerfile based on the official wordpress Docker image along with your customizations.

FROM wordpress:5.2.4-fpm as builder

WORKDIR /usr/src/wp-cli/
RUN curl -Os https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar && \
    chmod +x wp-cli.phar && \
    mv wp-cli.phar wp

RUN apt-get update && \
    apt-get install -y --no-install-recommends \
    unzip && \
    apt-get purge -y --auto-remove -o APT::AutoRemove::RecommendsImportant=false && \
    rm -rf /var/lib/apt/lists/*

WORKDIR /usr/src/app/
COPY wordpress/ /usr/src/app/
RUN chmod +x install.sh && \
    sh install.sh && \
    rm -rf \
    install.sh \
    plugins.txt \
    themes.txt

###

FROM wordpress:5.2.4-fpm

RUN mv "$PHP_INI_DIR/php.ini-production" "$PHP_INI_DIR/php.ini"
COPY php/custom.ini /usr/local/etc/php/conf.d/
COPY php-fpm/zz-docker.conf /usr/local/etc/php-fpm.d/

COPY --from=builder /usr/src/wp-cli/wp /usr/local/bin/
COPY --from=builder /usr/src/app/ /usr/src/wordpress/wp-content/
RUN cd /usr/src/wordpress/wp-content/ && \
    rm -rf \
    plugins/akismet/ \
    plugins/hello.php \
    themes/twentysixteen/ \
    themes/twentyseventeen/

# HACK: `101` is the user id of `nginx` user in `nginx:x.x.x-alpine` Docker image
# https://stackoverflow.com/questions/36824222/how-to-change-the-nginx-process-user-of-the-official-docker-image-nginx
RUN usermod -u 101 www-data && \
    groupmod -g 101 www-data

ENTRYPOINT ["docker-entrypoint.sh"]
CMD ["php-fpm"]

The multiple FROM statements are for multi-stage builds.

See more details on the GitHub repository:
https://github.com/vinta/vinta.ws/tree/master/docker/code-blog

Google Cloud Build

Next, a small cloudbuild.yaml file to build Docker images in Google Cloud Build triggered by GitHub commits automatically.

substitutions:
  _BLOG_IMAGE_NAME: my-blog
steps:
- id: my-blog-cache-image
  name: gcr.io/cloud-builders/docker
  entrypoint: "/bin/bash"
  args:
   - "-c"
   - |
     docker pull asia.gcr.io/$PROJECT_ID/$_BLOG_IMAGE_NAME:$BRANCH_NAME || exit 0
  waitFor: ["-"]
- id: my-blog-build-image
  name: gcr.io/cloud-builders/docker
  args: [
    "build",
    "--cache-from", "asia.gcr.io/$PROJECT_ID/$_BLOG_IMAGE_NAME:$BRANCH_NAME",
    "-t", "asia.gcr.io/$PROJECT_ID/$_BLOG_IMAGE_NAME:$BRANCH_NAME",
    "-t", "asia.gcr.io/$PROJECT_ID/$_BLOG_IMAGE_NAME:$SHORT_SHA",
    "docker/my-blog/",
  ]
  waitFor: ["my-blog-cache-image"]
images:
- asia.gcr.io/$PROJECT_ID/$_BLOG_IMAGE_NAME:$SHORT_SHA

Just put it into the root directory of your GitHub repository. Don't forget to store Docker images near your server's location, in my case, asia.gcr.io.

Moreover, it is recommended by the official documentation to use --cache-from for speeding up Docker builds.

ref:
https://cloud.google.com/container-registry/docs/pushing-and-pulling#tag_the_local_image_with_the_registry_name
https://cloud.google.com/cloud-build/docs/speeding-up-builds

Deployments

Finally, here comes Kubernetes manifests. The era of YAML developers.

WordPress, PHP-FPM, and NGINX

You can configure the WordPress site as Deployment with an NGINX sidecar container which proxies to PHP-FPM via UNIX socket.

ConfigMaps for both WordPress and NGINX:

apiVersion: v1
kind: ConfigMap
metadata:
  name: my-blog-wp-config
data:
  wp-config.php: |
    <?php
    define('DB_NAME', 'xxx');
    define('DB_USER', 'xxx');
    define('DB_PASSWORD', 'xxx');
    define('DB_HOST', 'xxx');
    define('DB_CHARSET', 'utf8mb4');
    define('DB_COLLATE', '');

    define('AUTH_KEY',         'xxx');
    define('SECURE_AUTH_KEY',  'xxx');
    define('LOGGED_IN_KEY',    'xxx');
    define('NONCE_KEY',        'xxx');
    define('AUTH_SALT',        'xxx');
    define('SECURE_AUTH_SALT', 'xxx');
    define('LOGGED_IN_SALT',   'xxx');
    define('NONCE_SALT',       'xxx');

    $table_prefix = 'wp_';

    define('WP_DEBUG', false);

    if (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] === 'https') {
      $_SERVER['HTTPS'] = 'on';
    }

    // WORDPRESS_CONFIG_EXTRA
    define('AUTOSAVE_INTERVAL', 86400);
    define('WP_POST_REVISIONS', false);

    if (!defined('ABSPATH')) {
      define('ABSPATH', dirname( __FILE__ ) . '/');
    }

    require_once(ABSPATH . 'wp-settings.php');
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: my-blog-nginx-site
data:
  default.conf: |
    server {
      listen 80;
      root /var/www/html;
      index index.php;

      if ($http_user_agent ~* (GoogleHC)) { # https://cloud.google.com/kubernetes-engine/docs/concepts/ingress#health_checks
        return 200;
      }

      location /blog/ { # WordPress is installed in a subfolder
        try_files $uri $uri/ /blog/index.php?q=$uri&$args;
      }

      location ~ [^/]\.php(/|$) {
        try_files $uri =404;
        fastcgi_split_path_info ^(.+?\.php)(/.*)$;
        include fastcgi_params;
        fastcgi_param HTTP_PROXY "";
        fastcgi_pass unix:/var/run/php-fpm.sock;
        fastcgi_index index.php;
        fastcgi_buffers 8 16k;
        fastcgi_buffer_size 32k;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_param PATH_INFO $fastcgi_path_info;
      }
    }

The wordpress image supports setting configurations through environment variables, though I prefer to store the whole wp-config.php in ConfigMap, which is more convenient. It is also worth noting that you need to use the same set of WordPress secret keys (AUTH_KEY, LOGGED_IN_KEY, etc.) for all of your WordPress replicas. Otherwise, you might encounter login failures due to mismatched login cookies.

Of course, you can use a base64 encoded (NOT ENCRYPTED!) Secret to store sensitive data.

ref:
https://kubernetes.io/docs/tasks/configure-pod-container/configure-pod-configmap/
https://kubernetes.io/docs/concepts/configuration/secret/

Service:

apiVersion: v1
kind: Service
metadata:
  name: my-blog
spec:
  selector:
    app: my-blog
  type: NodePort
  ports:
  - name: http
    port: 80
    targetPort: http

ref:
https://kubernetes.io/docs/concepts/services-networking/service/

Deployment:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-blog
spec:
  replicas: 3
  selector:
    matchLabels:
      app: my-blog
  template:
    metadata:
      labels:
        app: my-blog
    spec:
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100 # prevent the scheduler from locating two pods on the same node
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchExpressions:
                  - key: "app"
                    operator: In
                    values:
                    - my-blog
      volumes:
      - name: php-fpm-unix-socket
        emptyDir:
          medium: Memory
      - name: wordpress-root
        emptyDir:
          medium: Memory
      - name: my-blog-wp-config
        configMap:
          name: my-blog-wp-config
      - name: my-blog-nginx-site
        configMap:
          name: my-blog-nginx-site
      containers:
      - name: wordpress
        image: asia.gcr.io/YOUR_PROJECT_ID/YOUR_IMAGE_NAME:YOUR_IMAGE_TAG
        workingDir: /var/www/html/blog # HACK: specify the WordPress installation path: subfolder
        volumeMounts:
        - name: php-fpm-unix-socket
          mountPath: /var/run
        - name: wordpress-root
          mountPath: /var/www/html/blog
        - name: my-blog-wp-config
          mountPath: /var/www/html/blog/wp-config.php
          subPath: wp-config.php
        resources:
          requests:
            cpu: 100m
            memory: 128Mi
          limits:
            cpu: 500m
            memory: 512Mi
      - name: nginx
        image: nginx:1.17.5-alpine
        volumeMounts:
        - name: php-fpm-unix-socket
          mountPath: /var/run
        - name: wordpress-root
          mountPath: /var/www/html/blog
          readOnly: true
        - name: my-blog-nginx-site
          mountPath: /etc/nginx/conf.d/
          readOnly: true
        ports:
        - name: http
          containerPort: 80
        resources:
          requests:
            cpu: 50m
            memory: 100Mi
          limits:
            cpu: 100m
            memory: 100Mi

Setting podAntiAffinity is important for running apps on Preemptible nodes.

Pro tip: you can set the emptyDir.medium: Memory to mount a tmpfs (RAM-backed filesystem) for Volumes.

ref:
https://kubernetes.io/docs/concepts/workloads/controllers/deployment/
https://kubernetes.io/docs/concepts/configuration/assign-pod-node/

CronJob

WP-Cron is the way WordPress handles scheduling time-based tasks. The problem is how WP-Cron works: on every page load, a list of scheduled tasks is checked to see what needs to be run. Therefore, you might consider replacing WP-Cron with a regular Kubernetes CronJob.

// in wp-config.php
define('DISABLE_WP_CRON', true);
apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: my-blog-wp-cron
spec:
  schedule: "0 * * * *"
  concurrencyPolicy: Forbid
  jobTemplate:
    spec:
      template:
        spec:
          volumes:
          - name: my-blog-wp-config
            configMap:
              name: my-blog-wp-config
          containers:
          - name: wp-cron
            image: asia.gcr.io/YOUR_PROJECT_ID/YOUR_IMAGE_NAME:YOUR_IMAGE_TAG
            command: ["/usr/local/bin/php"]
            args:
            - /usr/src/wordpress/wp-cron.php
            volumeMounts:
            - name: my-blog-wp-config
              mountPath: /usr/src/wordpress/wp-config.php
              subPath: wp-config.php
              readOnly: true
          restartPolicy: OnFailure

ref:
https://developer.wordpress.org/plugins/cron/

Ingress

Lastly, you would need external access to Services in your Kubernetes cluster:

apiVersion: networking.k8s.io/v1beta1
kind: Ingress
metadata:
  name: load-balancer
  annotations:
    kubernetes.io/ingress.class: "gce" # https://github.com/kubernetes/ingress-gce
spec:
  rules:
  - host: example.com
    http:
      paths:
      - path: /blog/*
        backend:
          serviceName: my-blog
          servicePort: http
      - backend:
          serviceName: frontend
          servicePort: http

There is a default NGINX Deployment to serve requests other than WordPress.

See more details on the GitHub repository:
https://github.com/vinta/vinta.ws/tree/master/kubernetes

ref:
https://kubernetes.io/docs/concepts/services-networking/ingress/
https://cloud.google.com/kubernetes-engine/docs/concepts/ingress

SSL Certificates

HTTPS is absolutely required nowadays. There are some solutions to automatically provision and manage TLS certificates for you:

Conclusions

If a picture is worth a thousand words, then a video is worth a million. This video accurately describes how we ultimately deploy a WordPress site on Kubernetes.

MySQL system error codes

MySQL system error codes

Print all OS error codes and MySQL error codes using the perror command.

$ for i in {1..190..1}; do perror "$i"; done

OS error code   1:  Operation not permitted
OS error code   2:  No such file or directory
OS error code   3:  No such process
OS error code   4:  Interrupted system call
OS error code   5:  Input/output error
OS error code   6:  No such device or address
OS error code   7:  Argument list too long
OS error code   8:  Exec format error
OS error code   9:  Bad file descriptor
OS error code  10:  No child processes
OS error code  11:  Resource temporarily unavailable
OS error code  12:  Cannot allocate memory
OS error code  13:  Permission denied
OS error code  14:  Bad address
OS error code  15:  Block device required
OS error code  16:  Device or resource busy
OS error code  17:  File exists
OS error code  18:  Invalid cross-device link
OS error code  19:  No such device
OS error code  20:  Not a directory
OS error code  21:  Is a directory
OS error code  22:  Invalid argument
OS error code  23:  Too many open files in system
OS error code  24:  Too many open files
OS error code  25:  Inappropriate ioctl for device
OS error code  26:  Text file busy
OS error code  27:  File too large
OS error code  28:  No space left on device
OS error code  30:  Read-only file system
OS error code  31:  Too many links
OS error code  32:  Broken pipe
OS error code  33:  Numerical argument out of domain
OS error code  34:  Numerical result out of range
OS error code  35:  Resource deadlock avoided
OS error code  36:  File name too long
OS error code  37:  No locks available
OS error code  38:  Function not implemented
OS error code  39:  Directory not empty
OS error code  40:  Too many levels of symbolic links
OS error code  42:  No message of desired type
OS error code  43:  Identifier removed
OS error code  44:  Channel number out of range
OS error code  45:  Level 2 not synchronized
OS error code  46:  Level 3 halted
OS error code  47:  Level 3 reset
OS error code  48:  Link number out of range
OS error code  49:  Protocol driver not attached
OS error code  50:  No CSI structure available
OS error code  51:  Level 2 halted
OS error code  52:  Invalid exchange
OS error code  53:  Invalid request descriptor
OS error code  54:  Exchange full
OS error code  55:  No anode
OS error code  56:  Invalid request code
OS error code  57:  Invalid slot
OS error code  59:  Bad font file format
OS error code  60:  Device not a stream
OS error code  61:  No data available
OS error code  62:  Timer expired
OS error code  63:  Out of streams resources
OS error code  64:  Machine is not on the network
OS error code  65:  Package not installed
OS error code  66:  Object is remote
OS error code  67:  Link has been severed
OS error code  68:  Advertise error
OS error code  69:  Srmount error
OS error code  70:  Communication error on send
OS error code  71:  Protocol error
OS error code  72:  Multihop attempted
OS error code  73:  RFS specific error
OS error code  74:  Bad message
OS error code  75:  Value too large for defined data type
OS error code  76:  Name not unique on network
OS error code  77:  File descriptor in bad state
OS error code  78:  Remote address changed
OS error code  79:  Can not access a needed shared library
OS error code  80:  Accessing a corrupted shared library
OS error code  81:  .lib section in a.out corrupted
OS error code  82:  Attempting to link in too many shared libraries
OS error code  83:  Cannot exec a shared library directly
OS error code  84:  Invalid or incomplete multibyte or wide character
OS error code  85:  Interrupted system call should be restarted
OS error code  86:  Streams pipe error
OS error code  87:  Too many users
OS error code  88:  Socket operation on non-socket
OS error code  89:  Destination address required
OS error code  90:  Message too long
OS error code  91:  Protocol wrong type for socket
OS error code  92:  Protocol not available
OS error code  93:  Protocol not supported
OS error code  94:  Socket type not supported
OS error code  95:  Operation not supported
OS error code  96:  Protocol family not supported
OS error code  97:  Address family not supported by protocol
OS error code  98:  Address already in use
OS error code  99:  Cannot assign requested address
OS error code 100:  Network is down
OS error code 101:  Network is unreachable
OS error code 102:  Network dropped connection on reset
OS error code 103:  Software caused connection abort
OS error code 104:  Connection reset by peer
OS error code 105:  No buffer space available
OS error code 106:  Transport endpoint is already connected
OS error code 107:  Transport endpoint is not connected
OS error code 108:  Cannot send after transport endpoint shutdown
OS error code 109:  Too many references: cannot splice
OS error code 110:  Connection timed out
OS error code 111:  Connection refused
OS error code 112:  Host is down
OS error code 113:  No route to host
OS error code 114:  Operation already in progress
OS error code 115:  Operation now in progress
OS error code 116:  Stale NFS file handle
OS error code 117:  Structure needs cleaning
OS error code 118:  Not a XENIX named type file
OS error code 119:  No XENIX semaphores available
OS error code 120:  Is a named type file
OS error code 121:  Remote I/O error
OS error code 122:  Disk quota exceeded
OS error code 123:  No medium found
OS error code 124:  Wrong medium type
OS error code 125:  Operation canceled
OS error code 126:  Required key not available
OS error code 127:  Key has expired
OS error code 128:  Key has been revoked
OS error code 129:  Key was rejected by service
OS error code 130:  Owner died
OS error code 131:  State not recoverable
OS error code 132:  Operation not possible due to RF-kill
OS error code 133:  Memory page has hardware error
MySQL error code 120: Did not find key on read or update
MySQL error code 121: Duplicate key on write or update
MySQL error code 122: Internal (unspecified) error in handler
MySQL error code 123: Someone has changed the row since it was read (while the table was locked to prevent it)
MySQL error code 124: Wrong index given to function
MySQL error code 125: Undefined handler error 125
MySQL error code 126: Index file is crashed
MySQL error code 127: Record file is crashed
MySQL error code 128: Out of memory in engine
MySQL error code 129: Undefined handler error 129
MySQL error code 130: Incorrect file format
MySQL error code 131: Command not supported by database
MySQL error code 132: Old database file
MySQL error code 126: Index file is crashed
MySQL error code 127: Record-file is crashed
MySQL error code 128: Out of memory
MySQL error code 130: Incorrect file format
MySQL error code 131: Command not supported by database
MySQL error code 132: Old database file
MySQL error code 133: No record read before update
MySQL error code 134: Record was already deleted (or record file crashed)
MySQL error code 135: No more room in record file
MySQL error code 136: No more room in index file
MySQL error code 137: No more records (read after end of file)
MySQL error code 138: Unsupported extension used for table
MySQL error code 139: Too big row
MySQL error code 140: Wrong create options
MySQL error code 141: Duplicate unique key or constraint on write or update
MySQL error code 142: Unknown character set used in table
MySQL error code 143: Conflicting table definitions in sub-tables of MERGE table
MySQL error code 144: Table is crashed and last repair failed
MySQL error code 145: Table was marked as crashed and should be repaired
MySQL error code 146: Lock timed out; Retry transaction
MySQL error code 147: Lock table is full;  Restart program with a larger locktable
MySQL error code 148: Updates are not allowed under a read only transactions
MySQL error code 149: Lock deadlock; Retry transaction
MySQL error code 150: Foreign key constraint is incorrectly formed
MySQL error code 151: Cannot add a child row
MySQL error code 152: Cannot delete a parent row
MySQL error code 153: No savepoint with that name
MySQL error code 154: Non unique key block size
MySQL error code 155: The table does not exist in engine
MySQL error code 156: The table already existed in storage engine
MySQL error code 157: Could not connect to storage engine
MySQL error code 158: Unexpected null pointer found when using spatial index
MySQL error code 159: The table changed in storage engine
MySQL error code 160: There is no partition in table for the given value
MySQL error code 161: Row-based binlogging of row failed
MySQL error code 162: Index needed in foreign key constraint
MySQL error code 163: Upholding foreign key constraints would lead to a duplicate key error in some other table
MySQL error code 164: Table needs to be upgraded before it can be used
MySQL error code 165: Table is read only
MySQL error code 166: Failed to get next auto increment value
MySQL error code 167: Failed to set row auto increment value
MySQL error code 168: Unknown (generic) error from engine
MySQL error code 169: Record is the same
MySQL error code 170: It is not possible to log this statement
MySQL error code 171: The event was corrupt, leading to illegal data being read
MySQL error code 172: The table is of a new format not supported by this version
MySQL error code 173: The event could not be processed no other hanlder error happened
MySQL error code 174: Got a fatal error during initialzaction of handler
MySQL error code 175: File to short; Expected more data in file
MySQL error code 176: Read page with wrong checksum
MySQL error code 177: Too many active concurrent transactions
MySQL error code 178: Record not matching the given partition set
MySQL error code 179: Index column length exceeds limit
MySQL error code 180: Index corrupted
MySQL error code 181: Undo record too big
MySQL error code 182: Invalid InnoDB FTS Doc ID
MySQL error code 183: Table is being used in foreign key check
MySQL error code 184: Tablespace already exists
MySQL error code 185: Too many columns
MySQL error code 186: Row in wrong partition
MySQL error code 187: InnoDB is in read only mode
MySQL error code 188: FTS query exceeds result cache memory limit
MySQL error code 189: Temporary file write failure
MySQL error code 190: Operation not allowed when innodb_forced_recovery > 0
MySQL error code 191: Too many words in a FTS phrase or proximity search
MySQL error code 192: Foreign key cascade delete/update exceeds max depth
MySQL error code 193: Required Create option missing
MySQL error code 194: Out of memory in storage engine
MySQL error code 195: Table corrupted
MySQL error code 196: Query interrupted
MySQL error code 197: Tablespace cannot be accessed
MySQL error code 198: Tablespace is not empty
MySQL error code 199: Incorrect file name
MySQL error code 200: Operation is not allowed
MySQL error code 201: Compute generate value failed

ref:
http://man7.org/linux/man-pages/man3/perror.3.html

Convert utf8 tables to utf8mb4 in MySQL

Convert utf8 tables to utf8mb4 in MySQL

Change Schema

# For each database:
ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

# For each table:
ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

# For each column:
ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE
    svcomments_svcomment
    CHANGE comment comment 
    longtext
    CHARACTER SET utf8mb4
    COLLATE utf8_general_ci;

SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';

# Don’t blindly copy-paste this!
# The exact statement depends on the column type, maximum length, and other properties.
# The above line is just an example for a `VARCHAR` column.

ref:
https://coderwall.com/p/pns4pa/setting-up-unicode-defaults-for-mariadb-or-mysql
https://mathiasbynens.be/notes/mysql-utf8mb4
http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
http://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5

Application Settings

Take a Django project as an example.

in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        ...
        'OPTIONS': {
            'charset': 'utf8mb4',
        },
        'TEST': {
            'CHARSET': 'utf8mb4',
            'COLLATION': 'utf8mb4_general_ci',
        },
    },
}

ref:
https://tzangms.com/use-emoji-in-mysql-with-django/

Create a custom migration in Django

Create a custom migration in Django

# create an empty migration file
$ ./manage.py makemigrations --empty --name convert_to_utf8mb4 your_app

in your_app/migrations/0002_convert_to_utf8mb4.py

from __future__ import unicode_literals

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
        ('your_app', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL(
            'ALTER TABLE app_repostarring CHANGE repo_description repo_description VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
        ),
    ]

ref:
https://docs.djangoproject.com/en/dev/ref/migration-operations/#runsql