sql-info.de

"Ignoring query to other database"

2011-10-19T01:19:00+01:00

A bit of a gotcha maybe, especially if you're used to the PostgreSQL CLI (where -U specifies the user name):

[user@example.com]# mysql -Uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.41-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database some_database; Ignoring query to other database mysql> show databases; Ignoring query to other database mysql> Bye

Although the connection to MySQL was seemingly successful, any commands only seem to result in the laconic message "Ignoring query to other database". I'm not quite sure why this happens, but -U has the following meaning in MySQL:

-U, --safe-updates Only allow UPDATE and DELETE that uses keys. -U, --i-am-a-dummy Synonym for option --safe-updates, -U.

Once again a bit of a head-scratcher which would be helped at the very least by less ambiguous notification messages.

permalink

Long time no post...

2010-10-04T23:10:00+01:00

Whoops, looks like it's been the better part of three years since I last made a post on this blog. Life, etcetera, has got in the way for a while, and in the intervening period I've undergone some changes to my personal life and also moved countries - to Japan, where I've long wanted to live and work and which has a thriving PostgreSQL culture.

Luckily I was able to step into a job involving running PostgreSQL as the core of a multi-million dollar business, and while it's not always been plain sailing, PostgreSQL (even the clapped-out 7.4 version running a legacy app) has proved by far and away the most reliable part of the operation and I'm looking forward to implementing the replication features available in 9.0.

Meanwhile this blog, and several other sites on the same server, are now of course on 9.0 (and the supporting application has even played an ever so minor role finding a bug in a 9.0 beta ). Once again, my thanks to everyone who puts in much more effort than myself for making PostgreSQL such a great product!

permalink

Converting tsearch2 to 8.3

2007-11-20T07:00:00+01:00

One of the major features in the upcoming 8.3 release is the integration of the tsearch2 full text search extension as a core PostgreSQL feature . While there are no fundamental changes, there are some differences which make upgrading from an existing installation a little tricky. The following are my notes from upgrading a test version of the database which powers this website.

more...

PostgreSQL vs MySQL website backend fieldtest

2007-10-10T06:03:00+01:00

(I'm back in the wonderful world of SQL in general and PostgreSQL in particular after a long hiatus).

Just came across this article describing (briefly) someone's practical experience converting a website backend from MySQL to PostgreSQL, with PostgreSQL coming out faster. Not an exhaustive scientific test, but the comments are interesting reading.

permalink

PostgreSQL on Windows primer from Microsoft

2007-02-26T06:25:00+01:00

PostgreSQL on Windows: A Primer (via Slashdot )

permalink

Elephants and (Trench) Mice

2007-02-10T21:57:00+01:00

It's not often that you click through from {insert name of popular Web 2.0 community site here} to {insert name of Web 2.0 startup-type site here} and notice they're running the site on a decently pachydermal database engine, but this one called TrenchMice certainly is.

permalink

Search function working again

2007-01-19T17:36:00+01:00

The site search function is now working properly again. Apologies for the interruption to service - the site software is currently being upgraded and the gremlins are out and about.

P.S. to whoever was looking for "types of aspirin": I know databases can be a headache at times, but pharmaceuticals are usually not the cure.

permalink

PostgreSQL in Thailand

2006-11-23T17:54:00+01:00

I've just got back from a few weeks in Thailand doing some consulting work for an international organisation, some of which thankfully involves open source in general and PostgreSQL in particular.

I took the opportunity to poke around the IT sections of a few bookshops and get the impression that the IT book market at least is dominated by publications dealing with proprietary software, mainly Windows in general and as far as databases go Oracle and SQL Server. Books on open source were pretty much in the minority, mainly dealing with Linux and / or PHP. I did find one book each on PostgreSQL and MySQL though, which as far as I could tell were both written in Thailand (i.e. not translations). I can only speculate that the infamous availability of proprietary software at very low prices through, erm, inofficial channels means open source software is at a comparative disadvantage. The current government seems to have issues with open source too, for whatever reason.

On the other hand, I had to source a dedicated server, and most providers offer Linux as the base package, with Windows servers available at a premium. In several cases the specs for the Linux servers detailed both MySQL and PostgreSQL as "features".

(Apologies to anyone who has sent mails to me in the last month or so and hasn't had a reply, I'm still up to my neck in things to do).

permalink

Exporting TRUE and FALSE to MySQL

2006-09-19T12:12:00+01:00

One of the many pitfalls of web-related work is that sometimes you just have to deal with MySQL, as it's the only database option some clients have. Particularly irksome is the penchant many web hosters have for older MySQL versions, which is a major PITA when trying to deal with even slightly complex data.

One of the many things lacking in MySQL is a true boolean datatype. It does possess a pseudo-type BOOL , which is silently transmuted to TINYINT(1) , and from 4.1 onwards, to quote the manual, " the constants TRUE and FALSE evaluate to 1 and 0, respectively ".

more...

MySQL dropping BDB support

2006-08-24T13:21:00+01:00

According to the changelog for the 5.1.12 beta release :

Incompatible change: Support for the BerkeleyDB (BDB) engine has been dropped from this release. Any existing tables that are in BDB format will not be readable from within MySQL from 5.1.12 or newer. You should convert your tables to another storage engine before upgrading to 5.1.12.

more...