taghandl0r

How to clean clustered MySQL tables

Assuming you have three tables in a MySQL cluster, each using the ndb storage engine. The tables are called A, B and C. C references B and B references A, but there are no foreign keys in clustered tables – or if there are, they are simply ignored. So how would you do a cascaded delete operation on the clustered tables? Here’s how to do it.

› Continue reading

Tags: mysql

Qt Jambi and not MySQL

Yes, the title is correct. I just tried to connect to a MySQL database from a Qt Jambi application, using the following code:

QSqlDatabase db = QSqlDatabase.addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName("qttest");
db.setUserName("qt");
db.setPassword("jambi");
boolean ok = db.open();

Which provokes:

QSqlDatabase: QMYSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE

Nice. Googled that, result: I would need to compile the Qt MySQL plugin from source. No precompiled DLL is available for download.

I suppose I’ll switch over to JDBC/Hibernate :-)

By the way, the current Qt Jambi documentation doesn’t mention database connections at all. The code snippet I posted was taken directly from the API documentation (javadoc).

Tags: mysql, qtjambi

JRuby on Rails oddities

Having installed JRuby 1.1.3, I wanted to get the Rails gem. I followed this article, because it explains how to use JRuby, Rails and JDBC/MySQL.

But I got an unexplainable error: gem kept telling me that the connection had been reset by the server (Rubyforge). After adding the -p switch (use a proxy) without specifying a proxy host, it worked.

Thanks to Google (best result: this) and the guys from #jruby :)

Tags: gem, jdbc, jruby, mysql, rails

Finding duplicate db entries

Supposing you have a MySQL table like this:

id
login
client
password
email
...

without a UNIQUE constraint for (login,client) – you want to find out which combinations of login and client are there more than once – use this kind of query:

SELECT login,client,count(*) as cnt
FROM yourtable
GROUP BY 1,2
HAVING cnt > 1
ORDER BY 3;

Thanks to Mike for the hint.

Tags: database, mysql