Mind Tree

Relational Database Design is one of the most powerful ways to ensure data integrity and a great way to kick-off any project. Very often the first thing developers do when starting a new project, or stub-project, is to design the database. This way the structure of the application is already in place and we just have to fill in the pieces with some server-side code. I’ve found when adding relational constraints to your database design you add in a very powerful error reporting tool that will let you know during the development process that you have allowed something to happen that shouldn’t have. In this article, I go through, step by step, showing how to set up a simple relational database and discuss the benefits that are enjoyed.

Let’s take a step back and describe what a relational database looks like. In any normal database design there are fields in one table that reference another table. For example, a books table might have a field labeled author_id which is meant to come from a table named authors. Creating hard-coded relations solidifies these associations and actually returns a MySQL error if violated.

As I hinted in the opening I have found this to be invaluable during the development and testing process as MySQL will immediately let me know that I have made a glaring error that otherwise may not have been noticed until after the service has launched. At that point the data could be irreparably corrupt and forced to start from scratch.

So let’s get right to it. For the purposes of this article, I’m going to pretend I’m creating a simple Books and Authors website with a simple 2-table setup. The first step is to create our tables.

CREATE TABLE `library`.`books` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR( 150 ) NOT NULL , `author_id` INT UNSIGNED NOT NULL , PRIMARY KEY ( `id` ) , INDEX ( `author_id` ) ) ENGINE = InnoDB

CREATE TABLE `authors` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(50) collate utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Nothing too fancy here. Couple things to notice:

1. Each table MUST be using the InnoDB storage engine. InnoDB is currently the only main-stream storage engine offered by MySQL to support relational design. More on this in my article: MyISAM vs InnoDB
2. The `author_id` field in the `books` table MUST be indexed and the same datatype as the `id` field in `authors`.

The next step is to set up the relations. Open the `authors` table and take a look at the view. Under the table there should be a link titled ‘Relation View’ – Click it.

phpMyAdmin has a great gui for setting up relations and actions. If the `author_id` row below doesn’t look like mine, make sure you have it indexed.

Here, I’ve setup a link on the `books` table and the `author_id` field. This will enforce the fact that any value inserted in this field MUST be present in the `authors.id` table as well. But what about these other settings?

ON DELETE:

• CASCADE:
  • This means if an author is deleted from the authors table, all of his books will also be automatically deleted.
  • This option is great to keep your data clean and reduce the number of delete quieries required when deleting an author.
• SET NULL:
  • Instead of deleting the book record when an author is deleted, books.author_id is set to NULL, effectively orphaning the book.
  • This feature is great if you want to be able to keep the books and come back at a later time to reassign them. Otherwise, without this feature, the books would still be referencing an author_id that doesn’t exist.
  • Note: If you try to set this option and phpMyAdmin tells you to check your datatypes, make sure the field is allowing null values.
• NO ACTION:
  • When a delete query is issued on an author that has books, MySQL will not allow this and return a Foreign_Key Constraint error.
  • It could be nice to identify this and re-word it to let the user know that if they would like to delete this author they need to re-assign his books or delete them all-together.
  • Note: If you use this option please remember to re-word the MySQL error to something the user can easily understand.
• RESTRICT:
  • Same as NO ACTION
  • From MySQL Manual: Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.

ON UPDATE:

• For the most part the options described above are going to act in the same manner they did for ON DELETE as they will with ON UPDATE. I’ll just run through some examples real quick.
• CASCADE:
  • If, for some reason, an author’s id gets updated than CASCADE will update all his corresponding books with the new value. Extremely handy.
• SET NULL
  • Same as CASCADE except instead of updating it with the new value, it will set it to null. I’m sure there is a perfectly good use for this but I haven’t run into it yet. If anyone can enlighten me please do
• NO ACTION / RESTRICT:
  • Same as ON DELETE, will throw an error if you try to update an author_id. I’m also having trouble finding a real-world example of when this could be useful

Once we have our simple relational database configured try to add a book with an author_id that doesn’t exist. MySQL should give you an error like this:

Cannot add or update a child row: a foreign key constraint fails (`library/books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

• Leave a comment... (0)

Every website has ‘em. Forms. Places for users to enter data into your website. Whether it be a search box, a “Contact Us” form, or variables in the website address, at some point in the flow of your script these suckers are going to touch your database. Oh, that’s no problem — We’ll just take what they type in and run a query in MySQL on it!

WHOA, there! Are you sure you want to do that? Any input from a user should be treated like a nuclear fuel rod. You can handle it, but you’ve got to make sure you do it right. You wouldn’t just pick it up with your bare hands, would you?

Why? Just what are MySQL Injection attacks anyway?

Lets say your database has a table inside called ‘tbl_Users’. Inside ‘tbl_Users’ are a list of your users, which all have usernames, passwords, first names, last names, addresses, etc. If these users are presented with a login box somewhere on your site, your php user verification query might be something like this:

SELECT * FROM `tbl_Users` WHERE `username`=’”.$_POST['username'].”‘ AND `password`=’”.md5($_POST['password']).”‘”The problem is that unscrupulous users (read: bad ones) could enter this into your form:

 

username: no_onepassword: ‘ OR ”=”Which would make your query look something like this:

SELECT * FROM `tbl_Users` WHERE `username`=’no_one’ AND `password`=” OR ”=”Which, if you read that correctly, would allow that user access to whatever it was you wanted hidden by logging them in. There are a multitude of other ways this can be dangerous, but this is by far the easiest example. Even more unscrupulous users (read: the real jackasses) could send in multiple queries including DELETE queries.

In which case, when you wake up the morning after the attack you are most likely to be heard saying:”Hey, where did all my users go?” 

Wow. Okay so I’ve got a friend… and his website isn’t secure. What can I do to help him out?

The good news is that with a few easy precautions, your “friend’s” website will be pretty secure against these types of attacks. I say pretty secure because there is no way to prevent every attack. We can only do our best to increase security to a point to take every realistic precaution to prevent these attacks.

#1: Escape your variables!

Using the php function ‘mysql_real_escape_string’ you can “escape” the single quote character from user input. This is probably the easiest method to prevent MySQL injection attacks. It works by adding a backslash (”\”) before each quote that the user enters into their input. So, to use our example from before:

username: hey’therebecomes

username: hey\’thereThis effectively stops MySQL injection in its tracks since it not only escapes the single quote (”‘”) character but also all other characters that the baddies can use to hijack your queries.

If you’ve got an array of data coming in, you can use this neat function that I found on the PHP mysql_real_escape_string page (code by “brian dot folts at gmail dot com”). It escapes all of the values in your array with ease.

To escape an array, use this function:

function mysql_real_escape_array($t){
return array_map(”mysql_real_escape_string”,$t);
}

Then you can call that function easily by passing your array to it:

$your_array = mysql_real_escape_array($your_array);

#2: Check the variable type of your input.

This is done by using the php functions “is_numeric()“, “is_string()“, “is_float()“, and “is_int()” to determine if the input the user is sending in is the same type that you were asking for. It’s not perfect, but if you were asking for a number and they sent in a word you know to discard it straight away and return an error thereby entirely avoiding any change of a MySQL injection attack.

#3: Always use proper MySQL syntax, including “`” and “‘” characters.

If your queries look something like this:

SELECT * FROM tbl_Users WHERE username=$value; Rewrite it so that it looks more like this:

$value = mysql_real_escape_string($value);mysql_query(SELECT * FROM `tbl_Users` WHERE `username`=’”.$value.”‘”); Proper MySQL syntax requires that all table and field names are surrounded by the reverse apostraphe (”`”) and values surrounded with single quotes / apostraphe (”‘”).

I hope this gives you a better indication of what you can do to help secure your websites. Keep in mind that this is in no way a complete list. Be ever vigilant in your efforts to prevent attacks of any kind on your code. Leave a comment or two if this helped you at all or if you have different suggestions on how to secure your code from injection attacks!

• Leave a comment... (0)

Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form). There are others but they are rarely if ever used. A database is said to be Normalised if it is in 3NF (or ideally in BCNF). These steps are descibed as follows:

Note: When attribute is used we are speaking of a field in the table

1NF
To put a database in 1N

• ensure that all attributes (columns) are atomic (which means that any single field should only have a value for ONE thing).

Examples:

In a database a table on Customers would have an address attribute. The address is made up of Company Name, Address Line1, Address Line2, Address Line3, City, Postcode. There are 6 values to this address and as such each should have it’s own field (column).

If your company sold furniture a table on products could have a description attribute. If for example that attribute was ‘Beech Desk 120w x 75h x 50d’. Ideally this would be broken down into a number attributes like ‘Colour’, ‘Type’, ‘Width’, ‘Height’ and ‘Depth’. The reason for this is it would allow you to seach the database for all Desks, for all pieces of Beech furniture, for all desks with a width of 120 etc.

• Create a separate table for each set of related data and Identify each set of related data with a primary key

Example:

In a general Invoicing table you would have a separate table for Customers, Orders, Products, Invoices and you would probably need tables for OrderDetails and InvoiceDetails as well. Each of these tables must have their own primary key. Each of these tables except for customers would have a foreign key reference to the primary key of another table. (See Relationships below)

• Do not use multiple fields in a single table to store similar data

Example:
(Underlined fields are Primary Keys and Italicised fields are Foreign Keys)

In a customer order you could have more than one product. That is the customer has ordered more than one item. If you tried to put all of this in one table as {OrderID, CustomerID, OrderDate, Product1, Product2, Product3} what would happen if the customer ordered more than 3 products. There would also be implications for querying the kind or quantiy of products ordered by a customer. Therefore these product fields don’t belong in the order table which is why we would have an OrderDetails table which would have a foreign key refernce to the Orders table {OrderDetailsID, OrderID, ProductID, Quantity}. Using productID as a foreign key to the product table means you don’t have to identify the product attributes here. This also allows you to enter a quantity figure for the product ordered.

Relationships:

All tables should have a 1 to 1 or 1 to many relationship. This means for example that 1 customer can have 1 or many orders and 1 order can have 1 or many details.

Therefore Orders table would have a foreign key reference to the Customer table primary key {OrderID, CustomerID, OrderDate} and the OrderDetails table would have a foreign key reference to the Order table primary key {OrderDetailsID, OrderID, ProductID, Quantity}. This table also contains a foreign key reference to the Products table. As a product is likely to be ordered more than once there is a many to 1 relationship between the OrderDetails and the Products table.

If any tables have a many to many relationship this must be broken out using a JOIN table. For example, Customers can have many Suppliers and Suppliers can supply to many Customers. This is known as a many to many relationship. You would need to create a JOIN table that would have a primary key made up of a foreign key reference to the Customers table and a foreign key reference to the suppliers table. Therefore the SuppliersPerCustomer table would be {SupplierID,CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCustomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCustomer table.

2NF

The database must meet all the requirements of the 1NF.

In addition, records should not depend on anything other than a table’s primary key (a primary key can be made up of more than one field, only if absolutely necessary like in a JOIN table).

Example:

A customers address is needed by the Customers table, but also by the Orders, and Invoices tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

3NF

The database must meet all the requirements of the 1NF and 2NF.

The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:

• A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key.

In other words, all nonkey attributes are functionally dependent only upon the primary key. All 3NF really means is that all fields (attributes) should be dependent on the tables primary key. If they are not they should be put in their own table. This means that every attribute unless it is a primary or foreign key must be DIRECTLY dependent on the Primary Key of this table and not on some other column.

Example:

The Customer table contains information such as address, city, postcode imagine it also contained a column called shipping cost. The value of shipping cost changes in relation to which city the products are being delivered to, and therefore is not directly dependent on the customer even though the cost might not change per customer, but it is dependent on the city that the customer is in. Therefore we would need to create another separate table to hold the information about cities and shipping costs.

BCNF

A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. BCNF is very similar to 3NF but deals with dependencies within the primary keys. BCNF in it’s simplist terms just says don’t have a primary key made up of more than one field unless it is a join table to disperse a many to many relationship and only contains the two primary keys of the tables it is joining.

Most relations that are in 3NF are also in BCNF. It only happens that a relation which is in 3NF is not in BCNF when the primary key in a table is made up of more than one field and the other columns are not dependent on both fields but only on one or the other.

• Leave a comment... (0)

 

Tips to Speed Up, Enhance and Manage MySQL

 

This article will show you few tips how you can enhance the performance of MySQL and you can fine tune MySQL using simple queries.

TIP 1: Find out who is doing what, and kill the process if needed.

This example kills Id 657

mysql> show processlist;
show processlist;
+—–+——+———–+———+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+———+———+——-+——-+——————+
| 657 | prog | localhost | weather | Sleep | 28619 | | NULL |
| 782 | prog | localhost | weather | Sleep | 853 | | NULL |
| 785 | prog | localhost | NULL | Query | 0 | NULL | show processlist |
+—–+——+———–+———+———+——-+——-+——————+
3 rows in set (0.00 sec)

mysql>kill 657

Or, from the command line, to kill process 782

[root@third-fl-71 mysql]# mysqladmin processlist
+—–+——+———–+———+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+———+———+——+——-+——————+
| 782 | prog | localhost | weather | Sleep | 2676 | | |
| 785 | prog | localhost | | Sleep | 1823 | | |
| 793 | root | localhost | | Query | 0 | | show processlist |
+—–+——+———–+———+———+——+——-+——————+
[root@third-fl-71 mysql]#

[root@third-fl-71 mysql]# mysqladmin kill 782

Note, the following can also be helpful

mysql> show status;
or
mysql> show status\G
also
mysql> show innodb status;

TIP 2: Clean up binary log files.

For a default install they may be in
/usr/local/var/
with names ending in -bin.000001,-bin.000002,..

mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)

TIP 3: Can the order of the columns in a create statement make a difference? YES

create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );

The first timestamp will always be the "automatically generated" time. So
if the record is updated, or inserted, this time gets changed. If the
order is changed, "timeEnter" is before "timeUpdate", then, "timeEnter"
would get updated. First timestamp column updates automatically.

Note, in the table above timeEnter will only get updated if passed a null
value.

insert into t (a,b,timeEnter) values (1,2,NULL);

Hints: Need mm-dd-yyyy hh:mm:ss format?

select a,b,DATE_FORMAT(timeUpdate,’%m-%d-%Y %T’),DATE_FORMAT(timeEnter,’%m-%d-%Y %T’) from t;
+——+——+—————————————+————————————–+
| a | b | DATE_FORMAT(timeUpdate,’%m-%d-%Y %T’) | DATE_FORMAT(timeEnter,’%m-%d-%Y %T’) |
+——+——+—————————————+————————————–+
| 3 | 2 | 04-15-2004 19:14:36 | 04-15-2004 19:15:07 |
| 3 | 2 | 04-15-2004 19:14:39 | 04-15-2004 19:15:07 |
| 5 | 5 | 00-00-0000 00:00:00 | 04-15-2004 19:15:53 |
| 1 | 2 | 00-00-0000 00:00:00 | 04-15-2004 19:20:15 |
+——+——+—————————————+————————————–+
4 rows in set (0.00 sec)

TIP 4: Connect, create table and select with Perl (Linux).

First the DBI module is needed, which
can be installed from the system prompt as follows:

# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql

The following is an example program:

#! /usr/bin/perl -w
# Copyright (GPL) Mike Chirico mchirico@users.sourceforge.net
#
# Program does the following:
# o connects to mysql
# o creates perlTest if it doesn’t exist
# o inserts records
# o selects and displays records
#
# This program assumes DBI
#
# perl -MCPAN -e shell
# cpan> install DBI
# cpan> install DBD::mysql
#

use strict;
use DBI;

# You will need to change the following:
# o database
# o user
# o password
my $database="yourdatabase";
my $user="user1";
my $passwd="hidden";
my $count = 0;
my $tblcreate= "
CREATE TABLE IF NOT EXISTS perlTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)

) ";

my $insert= "
insert into perlTest (a,b,c)
values (1,2,3),(4,5,6),(7,8,9)";

my $select="
select a,b,c from perlTest ";

my $dsn = "DBI:mysql:host=localhost;database=${database}";
my $dbh = DBI->connect ($dsn, $user, $passwd)
or die "Cannot connect to server\n";

my $s = $dbh->prepare($tblcreate);
$s->execute();
$s = $dbh->prepare($insert);
$s->execute();

$s = $dbh->prepare($select);
$s->execute();

while(my @val = $s->fetchrow_array())
{
print " $val[0] $val[1] $val[2]\n";
++$count;
}
$s->finish();

$dbh->disconnect ( );

exit (0);

TIP 5: Remove duplicate entires.

Assume the following table and data.

CREATE TABLE IF NOT EXISTS dupTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)

);

insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);

mysql> select * from dupTest;
select * from dupTest;
+——+——+——+——+———————+
| pkey | a | b | c | timeEnter |
+——+——+——+——+———————+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+——+——+——+——+———————+
4 rows in set (0.00 sec)

mysql>

Note, the first two rows contains duplicates in columns a and b. It contains
other duplicates; but, leave the other duplicates alone.

mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

mysql> select * from dupTest;
select * from dupTest;
+——+——+——+——+———————+
| pkey | a | b | c | timeEnter |
+——+——+——+——+———————+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+——+——+——+——+———————+
3 rows in set (0.00 sec)

TIP 6: Show status information on a table.

Note, if the database was started
with –safe-show-database or –skip-show-database some of these commands
may not work. Note the "\G" option my provide a nicer format.

Show the create statement:

mysql> show create table dupTest\G
show create table dupTest\G
*************************** 1. row ***************************
Table: dupTest
Create Table: CREATE TABLE `dupTest` (
`pkey` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
`b` int(11) default NULL,
`c` int(11) default NULL,
`timeEnter` timestamp NOT NULL,
PRIMARY KEY (`pkey`),
UNIQUE KEY `a` (`a`,`b`)
) TYPE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

TIP 7: Transactions:

Not all table types support transactions. BDB and INNODB type do support transactions.
Assuming the server has NOT been started with –skip-bdb or –skip-innodb the following should work:

mysql> create table tran_test (a int, b int) type = InnoDB;
mysql> begin;
mysql> insert into tran_test (a,b) values (1,2);

mysql> select * from tran_test;
select * from tran_test;
+——+——+
| a | b |
+——+——+
| 1 | 2 |
+——+——+
1 row in set (0.00 sec)

mysql> rollback;

mysql> select * from tran_test;
select * from tran_test;
Empty set (0.00 sec)

Summary: rollback undoes everything and commit will save.

TIP 8: MERGE:

Several tables can be merged into one.

CREATE TABLE log_01 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE log_02 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE log_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;

mysql> insert into log_01 (a,b) values (1,’log1′);
mysql> insert into log_02 (a,b) values (1,’log2′);

mysql> select * from log_summary;
select * from log_summary;
+——+——+——+———————+
| pkey | a | b | timeEnter |
+——+——+——+———————+
| 1 | 1 | log1 | 2004-04-16 11:59:55 |
| 1 | 1 | log2 | 2004-04-16 12:00:08 |
+——+——+——+———————+
2 rows in set (0.00 sec)

Reference:
http://dev.mysql.com/doc/mysql/en/MERGE.html

TIP 9: Updating foreign keys in a multiuser environment.

Using LAST_INSERT_ID().

The LAST_INSERT_ID() is unique to the login session. This allows updating
of foreign keys.

CREATE TABLE keytest (
pkey int(11) NOT NULL auto_increment,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)

);

CREATE TABLE foreignkeytest (
pkey int(11) NOT NULL auto_increment,
pkeykeytest int(11) NOT NULL,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)

);

mysql> insert into keytest(ptext,ptype) values (’one’,1);

mysql> select LAST_INSERT_ID() from keytest;
select LAST_INSERT_ID() from keytest;
+——————+
| last_insert_id() |
+——————+
| 1 |
+——————+
1 row in set (0.03 sec)

mysql> insert into foreignkeytest (ptext,pkeykeytest) values (’one’,LAST_INSERT_ID());

Note: If your session didn’t update any records, LAST_INSERT_ID() will be zero. Never
assume LAST_INSERT_ID()+1 will be the next record. If another session inserts a record,
this value may be taken. You are assured that this value will be unique to the "session".

TIP 10: Monitor port 3306:

tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not 192.168.1.102

The -s is length of each packet. This monitors all traffic on port 3306 excluding
the good client 192.168.1.102. The out will be in the following format:

[root@third-fl-71 root]# tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not 192.168.1.102
tcpdump: listening on eth0
13:05:01.857705 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0) win 16384 (DF) (ttl 128, id 2873, len 48)
0×0000 4500 0030 0b39 4000 8006 6b90 c0a8 0167 E..0.9@…k….g
0×0010 c0a8 0147 1023 0cea 9786 1ea5 0000 0000 …G.#……….
0×0020 7002 4000 ebe7 0000 0204 05b4 0101 0402 p.@………….
13:05:04.863552 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0) win 16384 (DF) (ttl 128, id 2878, len 48)

TIP 11: Create a C or C++ API

Download the following:

http://prdownloads.sourceforge.net/souptonuts/mysql_select-0.0.1.tar.gz?…

Also reference:
http://dev.mysql.com/downloads/other/plusplus/

TIP 12: Connect from Java

#
# mysql-connector-java-3.0.11-stable-bin.jar or later must be downloaded
# from:http://dev.mysql.com/downloads/connector/j/3.0.html
#
# Compile:
# javac Connect.java
#
# Run:
# java Connect

import java.sql.*;

public class Connect
{
public static void main (String[ ] args)
{
Connection conn = null;
String url = "jdbc:mysql://localhost/";
String userName = "root";
String password = "";

try
{
Class.forName ("com.mysql.jdbc.Driver").newInstance ( );
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("Connected");
}
catch (Exception e)
{
System.err.println ("Cannot connect to server");
}
finally
{
if (conn != null)
{
try
{
conn.close ( );
System.out.println ("Disconnected");
}
catch (Exception e) { /* ignore close errors */ }
}
}
}
}

TIP 13: Print defaults for the current client connection

$ /usr/local/bin/my_print_defaults client mysql
–port=3306
–socket=/tmp/mysql.sock
–no-auto-rehash

TIP 14: Quick Status:

mysql> \s
————–
/usr/local/bin/mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)

Connection id: 642
Current database:
Current user: prog@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 4.1.1-alpha-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1_swedish_ci
Server characterset: latin1_swedish_ci
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 15 hours 24 min 38 sec

Threads: 1 Questions: 4896 Slow queries: 0
Opens: 14 Flush tables: 1 Open tables: 8 Queries per second avg: 0.035
————–

TIP 15: "error: 1016: Can’t open file:"

If it’s from an orphaned file, not in the database
but on disk, then, the disk file may need to be deleted.

myisamchk can help with damaged files. It’s best to stop the database.

# su -
# mysqladmin shutdown

# cd /usr/local/var/database
# myisamchk *

# /etc/init.d/mysql restart

TIP 16: Finding records that do not match between two tables.

CREATE TABLE bookreport (
b_id int(11) NOT NULL auto_increment,
s_id int(11) NOT NULL,
report varchar(50),
PRIMARY KEY (b_id)

);

CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);

insert into student (name) values (’bob’);
insert into bookreport (s_id,report)
values ( last_insert_id(),’A Death in the Family’);

insert into student (name) values (’sue’);
insert into bookreport (s_id,report)
values ( last_insert_id(),’Go Tell It On the Mountain’);

insert into student (name) values (’doug’);
insert into bookreport (s_id,report)
values ( last_insert_id(),’The Red Badge of Courage’);

insert into student (name) values (’tom’);

To find the sudents where are missing reports:

select s.name from student s
left outer join bookreport b on s.s_id = b.s_id
where b.s_id is null;

+——+
| name |
+——+
| tom |
+——+
1 row in set (0.00 sec)

Ok, next suppose there is an orphan record in
in bookreport. First delete a matching record
in student:

delete from student where s_id in (select max(s_id) from bookreport);

Now, how to find which one is orphaned:

select * from bookreport b left outer join
student s on b.s_id=s.s_id where s.s_id is null;

+——+——+————————–+——+——+
| b_id | s_id | report | s_id | name |
+——+——+————————–+——+——+
| 4 | 4 | The Red Badge of Courage | NULL | NULL |
+——+——+————————–+——+——+
1 row in set (0.00 sec)

To clean things up (Note in 4.1 you can’t do subquery on
same table in a delete so it has to be done in 2 steps):

select @t_sid:=b.s_id from bookreport b left outer join
student s on b.s_id=s.s_id where s.s_id is null;

delete from student where s_id=@t_sid;

But, functions do work in delete. For instance the
following is possible:

delete from student where s_id=max(s_id);

It just a problem when joining the table where the
delete will occur with another table. Another
option is two create a second temp table and
locking the first one.

TIP 17: Getting a random roll of the dice:

CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);

insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);

select roll from dice order by rand() limit 1;

TIP 18: Creating and using your own password file.

This assumes MySQL has been configured with SSL support [ ./configure --with-openssl ]

CREATE TABLE password (
p_id int(11) NOT NULL auto_increment,
id varchar(20),
valid enum(’y',’n'),
password BLOB,
timeEnter timestamp,
PRIMARY KEY (p_id)
);

insert into password (id,password)
values (’bob’,des_encrypt(’secret’,’somekey’));
insert into password (id,password)
values (’tom’,des_encrypt(’password’,’somekey’));
insert into password (id,password)
values (’kate’,des_encrypt(’desmark’,’somekey’));
insert into password (id,password)
values (’tim’,des_encrypt(’tim’,’somekey’));
insert into password (id,password)
values (’sue’,des_encrypt(’SUM’,’somekey’));
insert into password (id,password)
values (’john’,des_encrypt(’dotgo86′,’somekey’));
insert into password (id)
values (’randal’);

mysql> select id,des_decrypt(password,’somekey’) from password;

+——–+———————————+
| id | des_decrypt(password,’somekey’) |
+——–+———————————+
| bob | secret |
| tom | password |
| kate | desmark |
| tim | tim |
| sue | SUM |
| john | dotgo86 |
| randal | NULL |
+——–+———————————+
7 rows in set (0.00 sec)

Note the bad passwords in the file (’secret’,'password’, and
password is the same as the id.

The following update statement will fill in the
value for valid:

update password set valid =
COALESCE(
concat(
substring(’y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,’somekey’))
,concat(’password,secret,simple,’,lcase(id),’,test’))))
),

substring(’n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,’somekey’))
,concat(’password,secret,simple,’,lcase(id),’,test’))))
)
)
,’n');

Which gives the following:

mysql> select id,valid from password;
select id,valid from password;
+——–+——-+
| id | valid |
+——–+——-+
| bob | n |
| tom | n |
| kate | y |
| tim | n |
| sue | y |
| john | y |
| randal | n |
+——–+——-+
7 rows in set (0.00 sec)

To understand it, try taking the following select apart:

select
COALESCE(
concat(
substring(’y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,’somekey’))
,concat(’password,secret,simple,’,lcase(id),’,test’))))
),

substring(’n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,’somekey’))
,concat(’password,secret,simple,’,lcase(id),’,test’))))
)
)
,’n')
as valid
from password;

TIP 19: Order in the count:

create table a (a varchar(10));

insert into a values (’a'),(’a'),(’b'),(’c');

Note

select a,count(a) as count from a group by a order by count;

+——+——-+
| a | count |
+——+——-+
| b | 1 |
| c | 1 |
| a | 2 |
+——+——-+
3 rows in set (0.00 sec)

but
See above name the function ——v
select a,count(a) from a group by a order by count(a);
ERROR 1111 (HY000): Invalid use of group function

TIP 20: Minimum configure tip:

./configure –with-openssl –enable-thread-safe-client

The –with-openssl is very helpful for creating your own
password file. Also, if doing C API, having thread safe
calls "could" come in handly…it’s what I use.

TIP 21: Getting 1 matching row from an OUTER join

CREATE TABLE parent (
pkey int(11) NOT NULL auto_increment,
pname varchar(5),
timeEnter timestamp,
PRIMARY KEY (pkey)

);

CREATE TABLE child (
ckey int(11) NOT NULL auto_increment,
pkey int(11) NOT NULL,
cname varchar(5),
timeEnter timestamp,
PRIMARY KEY (ckey)

);

insert into parent(pname) values (’A');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,’a1′);
insert into child(pkey,cname) values (@a_lid,’a2′);
insert into child(pkey,cname) values (@a_lid,’a3′);
insert into parent(pname) values (’B');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,’b1′);
insert into child(pkey,cname) values (@a_lid,’b2′);
insert into parent(pname) values (’C');

mysql> select p.*,c.cname,count(c.pkey) as number
from parent p left outer join child c on p.pkey=c.pkey
where c.pkey is not null group by c.pkey;

+——+——-+———————+——-+——–+
| pkey | pname | timeEnter | cname | number |
+——+——-+———————+——-+——–+
| 5 | A | 2004-04-28 09:56:59 | a1 | 3 |
| 6 | B | 2004-04-28 09:56:59 | b1 | 2 |
+——+——-+———————+——-+——–+
2 rows in set (0.01 sec)

For comparison, here is a listing of all the matching data:

mysql> select * from parent p left outer join child c on p.pkey=c.pkey where c.pkey is not null;
select * from parent p left outer join child c on p.pkey=c.pkey where c.pkey is not null;
+——+——-+———————+——+——+——-+———————+
| pkey | pname | timeEnter | ckey | pkey | cname | timeEnter |
+——+——-+———————+——+——+——-+———————+
| 5 | A | 2004-04-28 09:56:59 | 7 | 5 | a1 | 2004-04-28 09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 8 | 5 | a2 | 2004-04-28 09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 9 | 5 | a3 | 2004-04-28 09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 10 | 6 | b1 | 2004-04-28 09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 11 | 6 | b2 | 2004-04-28 09:56:59 |
+——+——-+———————+——+——+——-+———————+
5 rows in set (0.00 sec)

mysql>

TIP 22: Getting a virtual row count.

Assume the following table:

CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);

Add some rows and delete (just to convice you this works in all cases)

insert into student (name) values (’tom’);
insert into student (name) values (’bob’);
insert into student (name) values (’sue’);
insert into student (name) values (’mike’);
insert into student (name) values (’joe’);
insert into student (name) values (’zoe’);
insert into student (name) values (’harpo’);

delete from student where name = ‘bob’;

Now, note mc is the row count…independent of s_id;

select a.name,sum(1) as mc
from student a, student b
where a.s_id

TIP 23: Computing running and sliding aggregates.

Assume the following table
and data:

CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);

insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);

mysql> select a.roll, sum(b.roll) from dice a, dice b
where b.d_id select a.roll, avg(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;

+——+————-+
| roll | avg(b.roll) |
+——+————-+
| 1 | 1.0000 |
| 2 | 1.5000 |
| 3 | 2.0000 |
| 4 | 2.5000 |
| 5 | 3.0000 |
| 6 | 3.5000 |
+——+————-+
6 rows in set (0.00 sec)

LONG WINDED TIPS:

LONG WINDED TIP 1: (May want to skip, since it’s a long example)

SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT". There’s little
use for this now; but, it’s fast and provides a lot of control.

The simple secret, and it’s also why they work in almost all databases, is the
following functions:

o sign (x) returns -1,0, +1 for values x 0 respectively
o abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x select * from exams;
+——+——+——+——-+
| pkey | name | exam | score |
+——+——+——+——-+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+——+——+——+——-+
8 rows in set (0.00 sec)

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+——+——-+——-+——-+——-+
| name | exam1 | exam2 | exam3 | exam4 |
+——+——-+——-+——-+——-+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+——+——-+——-+——-+——-+
2 rows in set (0.00 sec)

Note, the above pivot table was created with one select statement

You may think IF’s would be clean bug WATCH OUT!
Look what the following gives (INCORRECT !!):

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+——+——-+——-+——-+——-+
| name | exam1 | exam2 | exam3 | exam4 |
+——+——-+——-+——-+——-+
| Bob | 75 | NULL | NULL | NULL |
| Sue | 90 | NULL | NULL | NULL |
+——+——-+——-+——-+——-+
2 rows in set (0.00 sec)

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) – sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) – sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) – sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
from exams group by name;
+——+——-+——-+——-+——-+———–+———–+———–+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+——+——-+——-+——-+——-+———–+———–+———–+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 |
+——+——-+——-+——-+——-+———–+———–+———–+
2 rows in set (0.00 sec)

Above delta_1_2 shows the difference between the first and second exams, with the numbers
being positive because both Bob and Sue improved their score with each exam. Calculating
the deltas here shows it’s possible to compare two rows, not columns which is easily done
with the standard SQL statements but rows in the original table.

mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) – sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) – sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) – sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

sum(score*(1-abs(sign(exam- 2)))) – sum(score*(1-abs(sign(exam- 1)))) +
sum(score*(1-abs(sign(exam- 3)))) – sum(score*(1-abs(sign(exam- 2)))) +
sum(score*(1-abs(sign(exam- 4)))) – sum(score*(1-abs(sign(exam- 3)))) as TotalIncPoints
from exams group by name;

+——+——-+——-+——-+——-+———–+———–+———–+—————-+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+——+——-+——-+——-+——-+———–+———–+———–+—————-+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 |
+——+——-+——-+——-+——-+———–+———–+———–+—————-+
2 rows in set (0.00 sec)

TotalIncPoints shows the sum of the deltas.

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) – sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) – sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) – sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

sum(score*(1-abs(sign(exam- 2)))) – sum(score*(1-abs(sign(exam- 1)))) +
sum(score*(1-abs(sign(exam- 3)))) – sum(score*(1-abs(sign(exam- 2)))) +
sum(score*(1-abs(sign(exam- 4)))) – sum(score*(1-abs(sign(exam- 3)))) as TotalIncPoints,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+——+——-+——-+——-+——-+———–+———–+———–+—————-+——-+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG |
+——+——-+——-+——-+——-+———–+———–+———–+—————-+——-+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 | 96.00 |
+——+——-+——-+——-+——-+———–+———–+———–+—————-+——-+
2 rows in set (0.00 sec)

It’s possible to combine Total Increasing Point TotalIncPoints with AVG. In fact, it’s possible to combine
all of the example cuts of the data into one SQL statement, which provides additional options for displaying
data on your page

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2 as AVG1_2,

(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,

(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+——+——-+——-+——-+——-+——–+——–+——–+——-+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG |
+——+——-+——-+——-+——-+——–+——–+——–+——-+
| Bob | 75 | 77 | 78 | 80 | 76.00 | 77.50 | 79.00 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 93.50 | 97.50 | 98.50 | 96.00 |
+——+——-+——-+——-+——-+——–+——–+——–+——-+
2 rows in set (0.00 sec)

Exam scores are listing along with moving averages…again it’s all with one
select statement.

• Leave a comment... (3)