boolean.co.nz
Where there is more to logic than TRUE or FALSE
Welcome to the Boolean blog
Here you will find details of news, projects, releases and other bits and pieces that we are working on at Boolean.
We hope that you find something of interest amongst the archives.
Code snippets, methodologies and useful links will also be mentioned here.
0
 |
The best way to store a (version 4) IP address in a MySQL database is as an integer. This may sound strange but MySQL has two powerful functions to enable you to store an IP address as an unsigned INT. Searching an integer is much faster than searching a string and additionally integers take up less storage space which is great when working with large datasets.
mysql> SELECT INET_ATON(’10.0.5.9′);
———————
INET_ATON(’10.0.5.9′)
———————
167773449
———————
1 row in set (0.00 sec)
———————
INET_ATON(’10.0.5.9′)
———————
167773449
———————
1 row in set (0.00 sec)
and the reverse function
mysql> SELECT INET_NTOA(167773449);
———————
INET_NTOA(167773449)
———————
10.0.5.9
———————
1 row in set (0.00 sec)
———————
INET_NTOA(167773449)
———————
10.0.5.9
———————
1 row in set (0.00 sec)
|
|
MySQL has a feature for numerical types known as zerofill which effects the display size of numerical types. Unlike string types, the number inside the parentheses is not the storage size in characters for the type. For numerical types the type name itself solely determines storage size.
| Column Type | Bytes On Disk | Signed Storage Range | Unsigned Storage Range |
| tinyint | 1 byte | -128 to 127 | 0 to 255 |
| smallint | 2 bytes | -32768 to 32767 | 0 to 65535 |
| mediumint | 3 bytes | -8388608 to 8388607 | 0 to 16777215 |
| int | 4 bytes | -2147483648 to 2147483647 | 0 to 4294967295 |
| bigint | 8 bytes | -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615 |
So zerofill with default width – the same as int(10):
mysql> create table test_table (t int zerofill);
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_table set t = 10;
Query OK, 1 row affected (0.02 sec)
mysql> select * from test_table;
+————+
| t |
+————+
| 0000000010 |
+————+
1 row in set (0.08 sec)
And without zerofill:
mysql> create table test_table (t int);
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_table set t = 10;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_table;
+——+
| t |
+——+
| 10 |
+——+
1 row in set (0.01 sec)
A common usage for is creating invoice ids such as INV00000945.
Notes:
If you do not have zerofill specified there is no difference between int(3) and int(10)
When doing comparisons: if compared as integers then the values are the same; if you compare as strings the values are different.
|
Sometimes MySQL doesn’t come up again when attempting to start MAMP, – it just hangs after starting Apache. If you don’t want to have to reboot (and shutting down and restarting MAMP hasn’t helped) then quit MAMP, open a Terminal window and type:
ps aux | grep mysql
lsof -i
killall -9 mysqld
lsof -i
killall -9 mysqld
|
|
A quick way to create a view to show file size statistics across databases on a server.
DROP VIEW IF EXISTS alldb;
CREATE VIEW AllDatabases AS
SELECT
s.schema_name AS ‘Schema’,
SUM (t.data_length) AS Data,
SUM ( t.index_length ) AS Indexes,
SUM (t.data_length) + SUM (t.index_length) AS ‘Mb Used’,
IF (SUM(t.data_free)=0,”,SUM (t.data_free)) As ‘Mb Free’,
IF (SUM(t.data_free)=0,”, 100 * (SUM (t.data_length) + SUM (t.index_length)) / ((SUM (t.data_length)+SUM (t.index_length) + SUM (IFNULL(t.data_free,0))) ) ) AS ‘Pct Used’, COUNT (table_name) AS Tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name
WITH ROLLUP
CREATE VIEW AllDatabases AS
SELECT
s.schema_name AS ‘Schema’,
SUM (t.data_length) AS Data,
SUM ( t.index_length ) AS Indexes,
SUM (t.data_length) + SUM (t.index_length) AS ‘Mb Used’,
IF (SUM(t.data_free)=0,”,SUM (t.data_free)) As ‘Mb Free’,
IF (SUM(t.data_free)=0,”, 100 * (SUM (t.data_length) + SUM (t.index_length)) / ((SUM (t.data_length)+SUM (t.index_length) + SUM (IFNULL(t.data_free,0))) ) ) AS ‘Pct Used’, COUNT (table_name) AS Tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name
WITH ROLLUP
|
Dumping a database schema is a an often required quick task. This script will read the schema from a MySQL database, and output it XML to describe it.
<?php
// Define a few constants
define(“DB_SERVER”, “localhost”);
define(“DB_USER”, “root”);
define(“DB_PASS”, “password”);
define(“DB_NAME”, “example”);
// Define a few constants
define(“DB_SERVER”, “localhost”);
define(“DB_USER”, “root”);
define(“DB_PASS”, “password”);
define(“DB_NAME”, “example”);
// Lets connect to the db
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
or die(“Unable to connect to MySQL”);
// Choose a database to work with
$selected = mysql_select_db(DB_NAME, $dbhandle)
or die(“Could not select examples”);
// Return all of the available tables
$result_tbl = mysql_query( “SHOW TABLES FROM “.DB_NAME, $dbhandle );
$tables = array();
while ($row = mysql_fetch_row($result_tbl)) {
$tables[] = $row[0];
}
$output = “<?xml version=\”1.0\” ?>\n”;
$output .= “<schema>”;
// Now iterate through each table and return the fields
foreach ( $tables as $table ) {
$output .= “<table name=\”$table\”>”;
$result_fld = mysql_query( “SHOW FIELDS FROM “.$table, $dbhandle );
while( $row1 = mysql_fetch_row($result_fld) ) {
$output .= “<field name=\”$row1[0]\” type=\”$row1[1]\”";
$output .= ($row1[3] == “PRI”) ? ” primary_key=\”yes\” />” : ” />”;
}
$output .= “</table>”;
}
$output .= “</schema>”;
// Notify the browser the type of file being dealt with
header(“Content-type: text/xml”);
// Display the XML to describe the schema
echo $output;
// Dont forget to close the connection
mysql_close($dbhandle);
?>
Remember, this is an example, and real world code should escape characters, follow best security practices etc. Proof of concept code here, – not a snippet to put live.
|
|
I’m sure there is no need to explain the virtues of regular backups. Below are three steps to peace of mind
1) Configure a backup directory on the server
mkdir /var/lib/backupfolder
cd /var/lib/backupfolder
cd /var/lib/backupfolder
2) Make a backup script
vi mysqlbackup.sh
#!/bin/sh
# MySQL server username goes here
USERNAME=”username”
# MySQL server password goes here
PASSWORD=”password”
# List of DBNAMES for Backup
DBNAME=”dbname”
#date timestamp used in the log
DATE=`/bin/date +%Y-%m-%d_%Hh%Mm`
# format the output file
OUTDIR=”/var/lib/backupfolder/”
OUTFILE=”ip_bindass”.$DATE.”sql.gz”
#working directory
DIR=”/var/lib/backupfolder/”
#cd $DIR
# Do the MySQL Backup
/usr/bin/mysqldump –database $DBNAME –opt –single-transaction -u$USERNAME -p$PASSWORD | /usr/bin/gzip -9 > $OUTDIR$OUTFILE
# MySQL server username goes here
USERNAME=”username”
# MySQL server password goes here
PASSWORD=”password”
# List of DBNAMES for Backup
DBNAME=”dbname”
#date timestamp used in the log
DATE=`/bin/date +%Y-%m-%d_%Hh%Mm`
# format the output file
OUTDIR=”/var/lib/backupfolder/”
OUTFILE=”ip_bindass”.$DATE.”sql.gz”
#working directory
DIR=”/var/lib/backupfolder/”
#cd $DIR
# Do the MySQL Backup
/usr/bin/mysqldump –database $DBNAME –opt –single-transaction -u$USERNAME -p$PASSWORD | /usr/bin/gzip -9 > $OUTDIR$OUTFILE
Dont forget to change permissions
chmod +x mysqlbackup.sh
3) Schedule the backup in crontab
crontab -e
Add something along the lines of:
30 23 * * * /var/lib/backupfolder/mysqlbackup.sh
To backup at 11:30PM every day. Just as important as to backup up is to test restores; – it’s no good having backups and feeling at ease if you cant recover in the event of catastrophe!
|
|
A big part of the Internet is built on LAMP (Linux, Apache, MySQL and PHP/Perl/Python). Now Oracle is trying to buy Sun, which owns MySQL.
It’s not in the Internet users interest that one key piece of the net would be owned by an entity that has more to gain by severely limiting and in the long run even killing it as an open source product than by keeping it alive. If Oracle were allowed to acquire MySQL, we would be looking at less competition among databases, which will mean higher license and support prices. In the end it’s always the consumers and the small businesses that have to pay the bills, in this case to Oracle.
Read on here at Monty’s blog (creator of MySQL) or head here for the official website.
|
|
Sometimes you know you are only looking for one row when you are querying your tables. It may be that you are fetching a unique record or checking the existence of any number of records that satisfy your WHERE clause.
Adding LIMIT 1 to your query can increase performance in this situation; – the database engine will stop scanning for records after it finds just one, instead of going through the whole table or index.
For example:
$record = mysql_query(“SELECT username FROM user WHERE country = ‘UK’”);
if (mysql_num_rows($record) > 0) {
// …
}
if (mysql_num_rows($record) > 0) {
// …
}
Can become:
$record = mysql_query(“SELECT username FROM user WHERE country = ‘UK’ LIMIT 1″);
if (mysql_num_rows($record) > 0) {
// …
}
if (mysql_num_rows($record) > 0) {
// …
}
Back to topPowered by 
| Based on a theme by Ori Pearl
| Based on a theme by Ori Pearl