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
 |
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) {
// …
}
|
|
MySQL supports four TEXT field types (TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT)
MyISAM tables in MySQL have a maximum row size 65,535 bytes and all the data in a row must fit within that limit.
Luckily however TEXT field types are stored outside of the table itself and thus only contribute 9 – 12 bytes towards that limit.
Further reading is here.
Because TEXT data types are able to store so much more data than VARCHAR and CHAR field types it makes sense to use them when storing web pages or similar content in the database.
The maximum amount of data that can be stored for each data type is approximately:
TINYTEXT 256 bytes
TEXT 65,535 bytes ~64kb
MEDIUMTEXT 16,777,215 bytes ~16MB
LONGTEXT 4,294,967,295 bytes ~4GB
So most of the time TEXT will suffice, but if you are scratch building a CMS it might be an idea to think about MEDIUMTEXT
Update: (20/05/201) – I see a lot of hits on this page, so I thought I’d spell out the information here for the terms you seem to be searching for…
TINYTEXT is a string data type that can store up to to 255 characters.
TEXT is a string data type that can store up to 65,535 characters. TEXT is commonly used for storing blocks of text used for brief articles.
LONGTEXT is a string data type with a maximum length of 4,294,967,295 characters. Use LONGTEXT if you need to store large blocks of text, such as a chapter of a book.
|
|
I always seem to be digging around to find MySQL’s date formatting syntax, so here is a couple of common conversions…
select date_format(date, ‘%d %M %Y’) as new_date from tablename
where date is the name of your date field, and new_date is the variable name which you can use to reference the value.
| date_format String | Example |
| ‘%e/%c/%Y’ | 25/4/2009 |
| ‘%c/%e/%Y’ | 4/25/2009 |
| ‘%d/%m/%Y’ | 25/04/2009 |
| ‘%m/%d/%Y’ | 04/25/2009 |
| ‘%a %D %b %Y’ | Fri 25th Apr 2009 |
A more complete list of specifiers is available here.
|
|
Its been a while but I was getting characters like £ instead of £ (for example) and it took a quick refresher to realise what was going on. A fantastic background reading is available here.
So what is going on?
UTF-8 uses one or more 8-bit bytes to store a single character, whereas ASCII for example uses only one byte per character. This makes it more space-efficient than UTF-16 or UTF-32 as the majority of characters can be single byte encoded but with the added benefit that you can store any character, – should you need to. It uses the most significant bits of each byte as continuation bits (to signify that the following byte(s) form part of the same character) and it this is the reason that improperly displayed UTF-8 results in weird characters.
OK, enough history, – lets just fix it!
The solution is to make sure you set the character set correctly with a Content-type header.
Content-type: text/html; charset=utf-8
<meta http-equiv=”content-type” content=”text/html; charset=utf-8″>
<meta http-equiv=”content-type” content=”text/html; charset=utf-8″>
So now that we are ready to store UTF-8 data we store some rows of data and output to the browser looks fine. However, is it really?
MySQL needs to know the character set of the data sent to it in your queries. The default connection character set is ISO 8859-1 (latin1) — treating all your supplied data as if it was ISO 8859-1, which is then automatically converted to the underlying character set of the column (UTF-8 in our case). Taking our earlier example this means that the two-byte pound sign is perceived as two ISO 8859-1 characters: Â and £. MySQL will then encode these characters separately as UTF-8 requiring 2 bytes each – double encoding as we use 4 bytes to store a single pound character! When selecting data from the table, the reverse occurs when MySQL converts the UTF-8 back into ISO 8859-1 and the browser (correctly) interprets the two bytes as a pound sign. The problem here is that while everything looks correct it is needlessly using extra storage space and CPU cycles in the conversion.
It’s very simple to change the connection character set and avoid these overheads. The query “SET NAMES ‘utf8′” instructs MySQL to treat incoming data as UTF-8, which can be directly inserted into columns with a matching charset. In practice you may issue this query after initialising your connection to the MySQL server so all future communication will be in UTF-8.
One major problem occurs when you have a table full of double-encoded UTF-8 text because it was inserted before you knew about changing the connection character set. If you then add in the extra “SET NAMES” query and output the retrieved text to a browser you will notice all the extra characters have crept in.
You may be tempted to be selective about where you use SET NAMES ‘utf8′ however it is fairly simple to modify all of the data in place and end up with clean tables. Here is some pseudocode that pulls valid UTF-8 from the table and reinserts it after switching the connection charset:
SET NAMES ‘latin1′;
SELECT id, col1, col2, col3, col4 FROM table;
SET NAMES ‘utf8′;
for each row
INSERT INTO table (col1, col2, col3, col4) VALUES (, , , ) WHERE id = ;
SELECT id, col1, col2, col3, col4 FROM table;
SET NAMES ‘utf8′;
for each row
INSERT INTO table (col1, col2, col3, col4) VALUES (, , , ) WHERE id = ;
Back to topPowered by 
| Based on a theme by Ori Pearl
| Based on a theme by Ori Pearl