Lenovo x100e Cell C 3G (mobile broadband) setup

I recently purchased a Lenovo X100e. I could not get an x120e, which I actually wanted... but it was only R2500 from PC Zone, so it's ok!  The battery life is terrible - on the 3-cell battery, it barely lasts an hour...  I will try to buy a 5200 mAh battery.
Anyway, the 3G setup: I was hassled by this for days...  The Cell C setting do not work out of the box - although they ought to.  You open the Access Connections thing, click on Locations and then 'Advanced' at the top of the window.  Click Create (to create a new connection profile), and select Mobile broadband, HSDPA etc.  There is a bit that says something like 'use automatic settings'.  Change this to manual, and click on edit settings.  The APN / access point name should be 'internet'.  The service name must be 'Cell-C' - not the 'Cell C-is' business that was set up by default.  Username, password should be blank and authentication/encryption should be 'NONE'.
This worked for me (eventually).
Good luck.

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.