Kauli – Open Source Grown Up

Kuali is proof that the Open Source model works for more than nerds and geeks. Kuali is a movement where colleges and universities pool resources to develop the software they need to run their institutions. The parties involved provide money and bodies to work on the various projects. And if an school needs a feature in a hurry, they provide money or bodies to get the work done. This is a highly collaborative effort spans the globe.

Many of these same schools found themselves locked into very expensive software that had to be extensively tailored to meet their needs. Each upgrade was an expensive and time consuming process in an era of shrinking staffs and budgets. Regular upgrades were needed to support changes in regulations or latent needs. The cost of upgrades and customization was taking too much of scarce funds. This drove them into an open …

Currently you cannot specify the current time as a default value for a DATETIME column. It works for TIMESTAMP but not for DATETIME,and even with TIMESTAMP this only work for the one timestamp column in the table, if you have more than 1 TIMESTAMP column you have to specify a default for all but the first.

This is fixed in MySQL 5.6 and MariaDB 10 which allows you to use a construct like this:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());

So what can you do if you are on MariaDB? Well, you can use a trigger to achieve a similar result:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());
delimiter //
mysql> CREATE TRIGGER td1_ins BEFORE INSERT ON td1 FOR EACH ROW
BEGIN
  IF new.c2 IS NULL THEN
    …

This blog talks about MySQL Enterprise Backup integration with Workbench and how the Workbench UI can be used to configure and operate MEB.

Join 15,000 others and follow Sean Hull on twitter @hullsean. 1. MySQL does not compete with Oracle It’s a myth that MySQL somehow poses a threat to Oracle. Oracle’s customers tend to be large enterprises running apps like e-business suite. These are certified to run on Oracle, and further they sit close to finance. MySQL […]

Do you have basic computer literacy skills? Then you can take the first step to using the MySQL Server by taking the MySQL for Beginners course.

Why get started on MySQL? Well, with MySQL, you can power the most demanding Web, E-commerce, and Online Transaction Processing applications.

In the 4-day MySQL for Beginners course, you develop an understanding of relational databases and how to design a robust and efficient database. You will install and configure the MySQL server and clients. You also get an introduction to advanced MySQL tools and features. You can take this live, instructor-led course as a:

• Live-virtual event: Take this course from your own desk, no travel required. Choose from a selection of events on the schedule to suit …

Somebody asked how to create a SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.

The following SQL statements create two databases and grant appropriate privileges to the student as the root superuser:

/* Create two databases. */
CREATE DATABASE seussdb;
CREATE DATABASE appdb;
 
/* Grant privileges to a student user. */
GRANT ALL ON seussdb.* TO student;
GRANT ALL ON appdb.* TO student;

Log out from the root superuser and reconnect as the student user. Then, the following code connects to the seuss …

One of the latest labs releases of Oracle MySQL brings multi source replication. This lifts the limitation found in earlier releases that a MySQL slave can only have one master.

To be fair, there were other ways of doing this already:

• Using a time based switch as described in MySQL High Availability
• Using the multi source feature in the yet-to-be released MariaDB 10
• Using Tungsten Replicator

There are many good uses of multi source replication. You could use it to combine data from multiple shards or applications.

If MySQL is used with …

As Frederic posted, its time to submit talks for the MySQL & Friends Devroom at FOSDEM 2014. The next year, it will be on Saturday February 1 2014. I look forward to being in Brussels again, and I hope to see you there too.

Submit to the MySQL track here, and don’t forget to be there on Friday evening for the start of the beers. I’m told by Frederic & Kenny that we’re likely to have a much more interesting community dinner since things are getting larger year by year. See you at FOSDEM and remember, submit talks!

…

Amazon recently announced support for 5.6, unfortunately, direct upgrade from lower versions is not yet supported. On a recent migration work – running mysqldump flat out would’ve meant 6+hrs of downtime. How did we cut it off to 1h45m? Simple, run dump per table and pipe it directly to the new 5.6 instance in parallel using Percona Server’s mysqldump utility to take advantage of –innodb-optimize-keys.

Here’s the base script we used – of course, YMMV and make sure to optimize the destination instance as well!

#!/bin/bash
# export-run.sh
# This is the wrapper script which builds up the list of tables to split into $parallel parts and calls export-tables.sh

parallel=6
dblist="db1 db2 db3"
smysql="mysql -hsource-55.us-east-1.rds.amazonaws.com"
dmysql="mysql -hdest-56.us-east-1.rds.amazonaws.com" …

Everyone who works with MySQL (or MariaDB) query optimizer has to spend a lot of time looking at EXPLAIN outputs. You typically first look at the tabular form, because it is easier to read. You can immediately see what the join order is, what numbers of records will be read, etc:

MariaDB [dbt3sf1]> explain select * from customer, orders where c_custkey= o_custkey;
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+
| id   | select_type | table    | type | possible_keys | key         | key_len | ref                        | rows   | Extra |
+------+-------------+----------+------+---------------+-------------+---------+----------------------------+--------+-------+
|    1 | SIMPLE      | customer | ALL  | PRIMARY       | NULL        | NULL    | NULL                       | 150303 |       |
|    1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | …