I’ve got a lot of Virtual Machines for development, and that means a lot of SQL installs. They’re not all the same edition, though, or versions, or service packs. It can be a bit confusing knowing which one I’m dealing with. And I can never remember how to find out – so here is the reminder…
So:
select serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')
Securing MySQL at Sitepoint. Particularly relevant, and something I need to refresh on – here’s an article at MySQL AB, and Securityfocus. Bit Unix-y (we use all Windows at work) but lots is still pertinent…
A bit of an example. I must try and find a package for dealing with this automatically – certainly creating the connection to database is pretty slow. Sharing those connections is well worth it. Need a bit of a play sometime. I’m also wondering if connection pooling with Javascript’s XMLHTTPRequest object might be a good idea.
So, MySQL has this lovely feature – you can have a column which will default to the next integer value when you insert some data. You don’t need to set it, it just takes the next value itself.
Unsurprising, the column that is auto incrementing has to be part of a PRIMARY KEY. I mean, that’s what makes the auto increment useful.
However, sometimes I find it can be necessary to have the auto incrementing column as a secondary column in a composite Primary Key. For example, if I have two columns, ‘msg’ and ‘thread’ that are the Primary Key, and I run:
Insert into myTable (thread, text ) values ( 1, 'Andy' );
Insert into myTable (thread, text ) values ( 2, 'Fred' );
Three times, then I would expect to see:
| thread | msg | Text | ----------------------- | 1 | 1 | Andy | | 1 | 2 | Andy | | 1 | 3 | Andy | | 2 | 1 | Fred | | 2 | 2 | Fred | | 2 | 3 | Fred |
What I was getting recently was different. First, I couldn’t have a secondary column in a primary key that was auto incrementing. Odd, I was sure I’d done it that way before. So I tried using a separate key for my composite. That worked, but the data I was getting now was:
| thread | msg | Text | ----------------------- | 1 | 1 | Andy | | 1 | 3 | Andy | | 1 | 5 | Andy | | 2 | 2 | Fred | | 2 | 4 | Fred | | 2 | 6 | Fred |
Not what I wanted. Anyway, to cut a long story short, I figured out the problem – table engines. When I’d build composite primary keys using auto increment, it was on a MyISAM table type (or table engine, as I believe the new parlance is). I’ve now got the latest version of MySQL, and it seems to be defaulting to INNODB table engines. INNODB, while adding cool stuff like transaction support, won’t allow auto increment on a secondary column in a primary key.
So, there you go, hopefully someone will find that a useful thing to know. Just change your table type, and that’ll fix it.
Okay, so I had a play with Realm Authentication…
Continue reading “More on Realm Authentication with Tomcat”
Slow day today, so I finally (after 2 years of saying I’d give it a go) got around to trying to use JDBC.
I wrote a simple class to query a mySql database. It worked like a charm! Now all I need to learn is good design practice (which could, I fear, include Beans). I’m not entirely sure how the object model matches up to the database structure – I guess that this is what I need to learn…
import java.sql.*;
import java.util.Properties;
public class JDBCTest {
public static void main( String args[] ){
Connection conn= null;
try{
String cURL = "jdbc:mysql://localhost/jtest";
Properties p = new Properties();
p.put("user","root");
p.put("password","voodoo");
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection( cURL, p );
Statement s = conn.createStatement();
ResultSet r = s.executeQuery( "SELECT * FROM jtable WHERE iID = 1" );
r.first();
System.out.println ( r.getString("cFEN"));
}
catch ( Exception e ) {
e.printStackTrace();
}
finally {
if( conn != null ) {
try { conn.close(); }
catch ( Exception e ) {}
}
}
}
}
Andy Burns' Blog 