Copying user settings for MySQL

When releasing a copy of the database, a bunch of table data gets sent off. I consider the user privileges and setup just as important a part of the system, but this is a far trickier thing to accomplish.

Typically, a copy of the database is made as follows: mysqldump -u root databasename > dbbk.sql

To copy user information, first make a copy of the 'mysql' db:

mysqldump -u root mysql > mysqlbk.sql

Then open this file in a text editor and delete everything except the statements for INSERTing into tables 'db' and 'user'. Then search these for appearances of the username you are looking for, e.g. (for releasing to the Divinity dept) 'divt_todb_read'. Delete any inserts that are not related to the users you are interested in.

You will be left with a file that looks something like this:

INSERT INTO `db` VALUES

('localhost','divinity_todb','divt_todb_read','Y','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N'),

('localhost','divinity_todb','divt_todb_wrt','Y','Y','Y','Y','Y','Y','N','N','N','N','N','Y','N','N','N','N','N');

INSERT INTO `user` VALUES

('localhost','divt_todb_wrt','hashedpwd','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0),

('localhost','divt_todb_read','hashedpwd','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0);

This is everything that is needed to create the MySQL users.

EDIT: Something I have noticed:

It is worth getting a list of column names for user and db above and putting these into the insert query along the lines of

'insert into table(col1, col2, ..., coln) values ( ... )'

because the number of columns in these tables seems sensitive to SQL version.

Do this by running a 'describe' command and copying + pasting the list of columns into Excel and using the transpose command to make a horisontal list; select the row, copy and paste into Wordpad/Notepad/x and find & replace all tab characters with ', '.

Resetting auto_increment fields

Resetting the auto_increment id field in the tables can be a hassle. Here is how to reset id to run from 0; this is done from the MySQL client.

mysql> set @var_name = 1000;

# offset ids by 1000 first

mysql> update tablename set id = (@var_name := @var_name + 1);

# reset to 0

mysql> set @var_name = 0;

# set ids to run from 0:

mysql> update tablename set id = (@var_name := @var_name + 1);

Comparing SQL tables

I ran into a small problem with one of the TODBs, and I needed to create new years of data using the admin years facility. I wanted to copy the current year to the previous year, then delete the current year and recreate it as a new year.

Before deleting the current year, I wanted to be sure that the copy was complete. I tried mysqldumping the two tables for comparison, but some differences appeared (there was a 1-byte difference in the file sizes). So I used diff. However, all it told me was that there was a difference in one line, but that line contains all the data for the table - 1000s of characters! Not helpful.

So instead, I figured this out (with the generous help of Google):

1. Dump the tables using mysqldump:
   mysqldump -u root databasename tablename_old > table_old.sql;
   mysqldump -u root databasename tablename_new > table_new.sql;
2. Reformat the files to trim the lines to manageable lengths:
   fmt -s table_old.sql > table_old_fmt.sql;
   fmt -s table_new.sql > table_new_fmt.sql
3. Then run the diff on them:
   diff table_old_fmt.sql table_new_fmt.sql

Voila! Instant diff with information on where the differences occurred in that one long line!

If you are interested, it turns out that every record was different because the id numbers had auto-incremented... Back to square one (almost - see next post for sorting out the id numbers).

Remove 'NOT NULL' constraint in MySQL

Perhaps the title is a misnomer... The 'NOT NULL' limitation on a MySQL table apparently does not count as a constraint. But anyway...

How is it removed? The Quota column of the TODB people table was originally designed not to allow a null value. This has been annoying me until I finally cracked and removed it. This is how:

alter table people_2009_10 modify column quota int(11) null;

I've written it here to help me remember...

Fixing MySQL collations/character sets

One of the small but slightly annoying errors that the TODB crops up occasionally is an error something like:

'Query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '=''

Why does it do this?

Ok, apparently two columns being compared in a join (or whatever) are different collations. So, fair enough - you can't compare apples and oranges or (in this case) APPLES. Still, that is fair enough. But why are the chracter sets different? I do not think that the collation was specified (for any of the columns), so it seems strange that one of the columns should magically change by itself.

My private theory (unsubstantiated) is that if you enter a dodgy character (e.g. Excel likes to replace single quote characters with something special of its own that MySQL has trouble with), MySQL changes the collation to accommodate the funny character. That is all very well, but a complete pain if it breaks your queries.

Still, the short-term solution is:

1. List the collations for the all columns in the offending table:

select table_name, column_name, collation_name from information_schema.columns where table_schema = 'jbs_new' and table_name like 'units%' and collation_name is not null order by collation_name;

+---------------+-------------+-------------------+

| table_name | column_name | collation_name |

+---------------+-------------+-------------------+

| units_2007_08 | sgrps | latin1_general_cs |

| units_2008_09 | sgrps | latin1_general_cs |

| units_2009_10 | sgrps | latin1_general_cs |

| units_2006_07 | uname | latin1_swedish_ci |

| units_2006_07 | course | latin1_swedish_ci |

| units_2006_07 | sgrps | latin1_swedish_ci |

| units_2006_07 | name | latin1_swedish_ci |

| units_2006_07 | assessmode | latin1_swedish_ci |

| units_2006_07 | note | latin1_swedish_ci |

| units_2007_08 | uname | latin1_swedish_ci |

| units_2007_08 | course | latin1_swedish_ci |

| units_2007_08 | name | latin1_swedish_ci |

| units_2007_08 | assessmode | latin1_swedish_ci |

| units_2007_08 | note | latin1_swedish_ci |

| units_2008_09 | uname | latin1_swedish_ci |

| units_2008_09 | course | latin1_swedish_ci |

| units_2008_09 | name | latin1_swedish_ci |

| units_2008_09 | assessmode | latin1_swedish_ci |

| units_2008_09 | note | latin1_swedish_ci |

| units_2009_10 | uname | latin1_swedish_ci |

| units_2009_10 | course | latin1_swedish_ci |

| units_2009_10 | name | latin1_swedish_ci |

| units_2009_10 | assessmode | latin1_swedish_ci |

| units_2009_10 | note | latin1_swedish_ci |

+---------------+-------------+-------------------+

In the example above, some of the columns are latin1_general and others are latin1_swedish

2. Then, change the offending ones:

alter table units_2006_07 modify sgrps varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

...etc for the each of the tables and columns.

This sorts out the error... but for how long??

Merging branches with SVN

'Subversion' (SVN) is used to maintain the various branches of the TODB. As features and functionality are developed in one branch, is often necessary to propogate some of these changes to other branches.

Every time I do this, I seem to have to start from first principles, figuring out the correct svn commands and so on.

Here I have decided to blog on what actually worked!

Example: bringing the Divinity TODB branch largely up-to-date with Trunk (the main development = 'generic version' branch):

1. Create a new (temp) working directory for Divinity: svn checkout https://source.caret.cam.ac.uk/TODB/branches/divinity
2. Merge the differences between Divinity and Trunk into the local working directory: svn merge https://source.caret.cam.ac.uk/TODB/branches/divinity https://source.caret.cam.ac.uk/TODB/trunk
3. Run 'svn status' to list changes (i.e. files that need attention, the ones that have changed)
4. Run 'svn diff' for each of these files to get a list of the changes in that file (or using the TortoiseSVN Explorer contextual menu: SVN -> Diff)
5. When the files have been edited to satisfaction, indicate that the conflicts are resolved (if possible, otherwise continue to the next step) (using the TortoiseSVN right-click command).
6. When all file conflicts are resolved, commit (using the TortoiseSVN)
7. Go back to the original branch working directory, and update (using Tortoise or the command prompt). This will update the usual Divinity working directory.

This only applies to the PHP files, of course - the database schema and data need to be updated manually.

Accessing MySQL table metadata from PHP

I was wanting to pull the column comments from a MySQL table to display as 'tooltips' on a webpage display of the table, for users unsure of the meaning of a particular column. I thought this would be trivial. It is not.

MySQL stores table information in an additional special schema called 'information_schema'. You connect to this as you would connect to the database that contains the orginal data from which the web page table is populated. If you have access to that database/table, your same login credentials will allow you access to the corresponding entries in information_schema. It is necessary to connect to the information_schema DB and retrieve the column information from there.

PHP has a default setting where if you send the same connection string to the database twice, you will get the same link id. So the following lines:

// connect to DB via read user
$dbread = mysql_connect('localhost', 'readuser', 'password')
or die('Could not connect: ' . mysql_error());
mysql_select_db('divinity_todb', $dbread);

// connect to DB's information schema:
$db_metadata = mysql_connect('localhost', 'readuser', 'password')
or die('Could not connect: ' . mysql_error());
mysql_select_db('information_schema', $db_metadata);

Will not work ($dbread and $db_metadata point to the same resource id).

You will need to do the following:

// connect to DB via read user
$dbread = mysql_connect('localhost', 'readuser', 'password', true)
or die('Could not connect: ' . mysql_error());
mysql_select_db('divinity_todb', $dbread);

// connect to DB's information schema:
$db_metadata = mysql_connect('localhost', 'readuser', 'password', true)
or die('Could not connect: ' . mysql_error());
mysql_select_db('information_schema', $db_metadata);

Update!

The easier and better way to get the information is simply to specify the schema name in an ordinary connection:

- having selected the database and connected with $dbread

- do something like 'select * from information_schema.tables ...'

This means a separate connection is no longer required.

Importing CSV files into MySQL

One day, the computer world will agree on its line endings... Here is a code snippet for importing a CSV file into an already-constructed table in MySQL:

LOAD DATA INFILE 'C:\\Documents and Settings\\mrj35\\My Documents\\JISC_eAdmin_project\\New_sites\\Divinity\\staff_divisions.csv'
INTO TABLE tmp_names
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY "\r\n";

Query OK, 154 rows affected (0.00 sec)Records: 154 Deleted: 0 Skipped: 0 Warnings: 0

Restarting Apache

I can never remember where apache2ctl is stored, so here is the full graceful restart command:

sudo /usr/sbin/apache2ctl -k graceful

Robust well-behaved DIVs

As my web expertise has been dragged kicking and screaming into the 21st century, I've more-or-less had to move away from nice, reliable and robust table-based layouts to DIV-based layouts that are anything but.

One of my bugbears is the general failure of a outer DIV to 'go around' any nested DIVs... or frankly, any other tags at all. So if I want have a right-aligned ('floated') P tag and I want it in a grey box with a blue border, my intuition says, 'Put it in a DIV with a grey background and a blue border'. This appears not to work out of the box (so to speak), with internet explorer at least... IE will generally display a blue line with the P text underneath it.

Having spent countless hours trying to figure this out, here are some tips that might help make the darkness a little less dark:

DIV
{
overflow: auto;
border: solid 1px blue;
background: silver;
}

If you are floating the DIV anywhere (for 20th-C websmiths, this is a bit like 'align'), and things are not working, it try setting the width attribute as well:
DIV
{
width: 30%;
float: right;
}

I hope this helps someone. I suspect it might help me one day too.