sql-info.de

MySQL Notes

1 | 2 | 3 | 4 | 5 | 6 | Index

Some quick notes on installing MySQL 5 on a Debian or Ubuntu server using the command line.

1. Determine the available MySQL version(s)

This step is optional; to find out what MySQL versions are available, execute

root@server ~ # apt-cache search mysql-server
auth2db - Powerful and eye-candy IDS logger, log viewer and alert generator
mysql-cluster-server - MySQL database server (metapackage depending on the latest version)
mysql-cluster-server-5.1 - MySQL database server binaries
torrentflux - web based, feature-rich BitTorrent download manager
mysql-server - MySQL database server (metapackage depending on the latest version)
mysql-server-5.1 - MySQL database server binaries
mysql-server-core-5.1 - MySQL database core server files
cacti - Frontend to rrdtool for monitoring systems and services

Normally the mysql-server package will exist as a meta package which will install the latest MySQL version available on the system - in this case 5.1.

Posted at 3:43 AM

Use the function UNIX_TIMESTAMP():

mysql> SELECT ts, UNIX_TIMESTAMP(ts) FROM ts_test;
+---------------------+--------------------+
| ts                  | UNIX_TIMESTAMP(ts) |
+---------------------+--------------------+
| 2013-02-17 16:34:09 |         1361086449 |
+---------------------+--------------------+
1 row in set (0.00 sec)

To get the current epoch, use UNIX_TIMESTAMP() with out any arguments:

mysql> SELECT UNIX_TIMESTAMP();
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1361088422 |
+-----------------------+
1 row in set (0.00 sec)

A slight MySQL oddity: SELECT UNIX_TIMESTAMP(NOW()) is equivalent to SELECT UNIX_TIMESTAMP(), however SELECT UNIX_TIMESTAMP(UTC_TIMESTAMP()) will produce an epoch number in the past.

mysql> SELECT UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(UTC_TIMESTAMP());
+-----------------------+---------------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP(UTC_TIMESTAMP()) |
+-----------------------+---------------------------------+
|            1361088547 |                      1361056147 |
+-----------------------+---------------------------------+
1 row in set (0.00 sec)
Posted at 9:05 AM

A bit of a gotcha maybe, especially if you're used to the PostgreSQL CLI (where -U specifies the user name):

[user@example.com]# mysql -Uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database some_database;
Ignoring query to other database
mysql> show databases;
Ignoring query to other database
mysql> Bye

Although the connection to MySQL was seemingly successful, any commands only seem to result in the laconic message "Ignoring query to other database". I'm not quite sure why this happens, but -U has the following meaning in MySQL:

  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.

Once again a bit of a head-scratcher which would be helped at the very least by less ambiguous notification messages.

Posted at 3:19 AM
4 comments  | 

August 24, 2006

MySQL | MySQL dropping BDB support

According to the changelog for the 5.1.12 beta release:

Incompatible change: Support for the BerkeleyDB (BDB) engine has been dropped from this release. Any existing tables that are in BDB format will not be readable from within MySQL from 5.1.12 or newer. You should convert your tables to another storage engine before upgrading to 5.1.12.
Posted at 3:21 PM

An interesting perspective from Lisa Vaas of eWeek.com on Oracle's purchase of InnoDB: The Truth Comes Out: Oracle Bought InnoDB Without a Clue

Posted at 6:58 PM

1 | 2 | 3 | 4 | 5 | 6 | Index