Posts tagged databases

MySQL comments

Made two quick comments today. I’d be a hypocrite disabling blog comments here, then encouraging people to blog their responses instead…. then not doing so for these. So here they are.


Apologies for commenting on an old post, but you’re on the first page of results on Google and just wanted to give everyone a heads up.

This isn’t really an error, just an indication that InnoDB is using the system’s internal memory allocator instead of its own. The default is yes/1, and is acceptable for production.

According to the docs, this command is deprecated, and will be removed in MySQL versions above 5.6 (and I assume MariaDB):

Cheers :)

And ECM Architect:

“I have no idea why [skip-networking] is turned on by default”

I’d say its a prudent security measure, especially for a package manager like MacPorts where users are generally creating stacks for personal local development. You don’t want to be exposing network services you weren’t expecting.

That said, if the default for MySQL/MariaDB is to enable it, it may be a cause of confusion, as it was for you here. Any deviations from the default should probably be documented somewhere.

Monday Postgres derp

Reloading a test Postgres server:

# /etc/init.d/postgresql reload
==>* Reloading PostgreSQL 9.3 database server
==>* pg_ctl: PID file "/var/lib/postgresql/9.3/main/" \
==> does not exist  [fail]

The problem? Yes, I attempted to reload a service I hadn’t started.

# /etc/init.d/postgresql start
==> * Starting PostgreSQL 9.3 database server    [ OK ] 

This is why people regard me as a professional.

Replacing MySQL with PostgreSQL in php-fpm

On autopilot, I generated a web server stack with nginx, PHP and MySQL. The company uses Postgres everywhere though, so I took that as a long, overdue excuse to use it myself.

# apt-get remove mysql-server mysql-client  
# apt-get install postgresql postgresql-client php5-pgsql

Then the requisite PHP packages:

# apt-get install php5-pgsql  
# apt-get remove 

The classic <?php phpinfo() ?> will still show MySQL capabilities though, so we need to remove the MySQL conf files from php-fpm:

$ ls /etc/php5/mods-available  
==> apc.ini  intl.ini  mysqli.ini  mysql.ini  pdo.ini  
==> pdo_pgsql.ini  pgsql.ini  
# rm mysql*ini

A PL/SQL induced surreal trip

I was going through my drafts from a week ago, and I found this:

This afternoon while awaiting the completion of a terribly inefficient PL/SQL query, I found myself staring into space. That kind of distant look where you don’t care if your eyes are even focusing or not; where even the act of blinking seems like a chore. I could almost feel the life energy draining out of me as my MacBook Air SQL Develop’d for its dear life.

I closed my eyes momumtarily [sic]

… and then I woke up.

I have no idea what it was about. It’s utterly pointless, but surreal enough to warrant me publishing it. Maybe you can read between the lines and see something I can’t.

When MariaDB has a brilliant About page

Photo of MariaDB developers from their About page

Speaking of MariaDB, that has to be one of the best About page photos I've seen in a long time!

In my part of the world, it appears we have MariaDB developers in Japan, Korea and Australia. The way the Malaysia developer has his feet in Borneo and the Peninsula is very cute :).

When MariaDB gets all tsundere

Shana goes HMMMMM

This is a cross–post from Anime@UTS, the University of Technology Sydney’s best (and only) anime club. Desu~

Hello club members! Over the coming days, your humble second term webmaster will be unfurling the new site design and back end. Developed with Kiri, it will be bolder and more interesting than the current theme, along with a few new features I hope will make the site more awesome for all ^_^.

As part of a behind the scenes look into the Daily Lives of Webmasters (and Webmistresses), I thought I'd share a local issue I had, and the solution so absurdly simple it'd make any other webmaster and sysadmin enter hammerspace to retrieve a weapon to beat me senseless with. Alex knows what I'm talking about.

It's not like I wanted you to start, or anything

I'd been rebuilding my local (and increasingly misnamed) LAMP stack on my MacBook Air to test prior to deployment. The world seems to be moving to MariaDB for reasons that are beyond the scope of this post, so rather than updating MySQL I figured I'd give it a shot.

The following errors in the log file said OH NO YOU DON'T:

140208 21:39:37 mysqld_safe mysqld from pid file /usr/local/var/mysql/ ended
140208 21:39:47 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
140208 21:39:47 InnoDB: The InnoDB memory heap is disabled
140208 21:39:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140208 21:39:47 InnoDB: Compressed tables use zlib 1.2.5
140208 21:39:47 InnoDB: Initializing buffer pool, size = 128.0M
140208 21:39:47 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
140208 21:39:47 [ERROR] Plugin 'InnoDB' init function returned error.
140208 21:39:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140208 21:39:47 [ERROR] Unknown/unsupported storage engine: InnoDB
140208 21:39:47 [ERROR] Aborting

140208 21:39:47 [Note] /usr/local/Cellar/mariadb/5.5.35/bin/mysqld: Shutdown complete

Well darn, I said, as I stroked my non existent beard. Frank would do that better than me.

The problem, as is perennially the case, existed between my keyboard and chair. In the process of uninstalling MySQL and installing MariaDB, I'd failed to remove MySQL's logfiles which differed in size to what MariaDB was expecting.

Removing these logfiles solved the issue. Once removed, MariaDB was able to recreate them to its taste:

% rm /usr/local/var/mysql/ib_logfile*
% echo Everybody's Heard that The Bird is The Word
% echo Desu

What does this have to with anime? Baka, it's everything! Which means, I got nothing. Happy Saturday!

SQL tried and true, but WXR still horribly broken

The Titanic

I've been blogging for long enough to have suffered a couple of disastrous data losses, but only once have I irreversibly lost stuff. Consequently I backup my blogs daily, but on a hunch early this morning I decided to test my backups on a local installation of WordPress. I choked!

Every evening my server is configured with a couple of cron jobs to do these backups which then get the bzip2 treatment:

  • An SQL query
  • A WordPress RSS/WXR file
  • A grilled cheese sandwich with avocado and gherkins

The SQL backup is fairly vanilla stuff, and always works, as one would expect. WordPress's automatically generated RSS/WXR files are much easier to work with, but from repeated painful experience over many years they're unreliable as heck. Perhaps I should phrase that to say "easier to work with… when they work"!

You’re telling us this… why?

I belabor all this because when I tested the WXR files my server has been exporting lately, they don't include categories when you reimport them into vanilla WordPress installs. None. Nada. Zippo. This despite setting the PHP memory ceiling higher as I talked about before and splitting up the WXR files as recommended by various folks.

Despite being introduced years ago, WXR is still horribly broken. As far as I can tell from trudging through the source WordPress doesn't even use a XML parser when importing them. I suppose that's another reason why I don't use sites like, when things like this mess up I can always access the database directly, and why on smaller projects I use SQLite3 databases which you can cheat on and backup by just copying over one file! Ah and I'm nostalgic for sqlplus already ;).

What do you guys do for data backups for your online stuff?

The Sun Oracle Database Machine

Sun Oracle Exadata V2 server

Ever since I postulated back in April as to what a combined Oracle-Sun company would do, in the back of my mind I've also been thinking what it would look like. Now we get a glimpse in the form of the Oracle Exadata V2 server, the second advertisement after their triumphant "Oracle Buys Sun" faux billboard graphic.

I'm surprised they kept the Sun Microsystems logo and the basic design of the hardware, but I'm even more surprised they positioned the Sun logo above the Oracle one. I get the feeling it's not something that should be read into much, but perhaps it's a symbolic tip of the hat and to appease people scared about what Oracle may do with Sun. Don't worry guys, Sun is still alive, and all that. Or maybe it's because "Sun Oracle" is much easier to say than "Oracle Sun". I have no idea what I'm saying here.

Most likely this is a transitionary product, we'll probably see completely new designs in coming quarters that probably won't include a Sun logo. Then again when Commodore Business Machines bought MOS Technology in the 1980s they ended up rebranding the subsidiary but it largely operated independently and still put their logo on all their integrated circuits, so who knows.

Sun Oracle Exadata V2 server

Personally I think it made far more sense for Oracle to be Sun's suitor instead of IBM given that they already had a lot of money and time invested in parallel products. Solaris is an excellent operating system, and an Oracle database on Sun software and hardware is a compelling mix.

I guess I'm still just a little sad to see such an iconic company slowly get eaten by another; as I've repeatedly said here I really admire Sun Microsystems. Perhaps I'll have a greater chance of owning my own Sun workstation after buying one on eBay after a few years. That is, if having original Sun hardware without an Oracle sticker doesn't increase its value too much.

Work struggle between SQLite over MySQL

A Green Hummer. I take that to mean just the colour!

It seems as with many technologies, regardless of your own abilities or competency if you’re using the "wrong" implementation you’re perceived as an amateur, and vice versa.

For example, I've read so many disparaging remarks by rude people on forums saying that people who run Ubuntu and Fedora are amateurs, and while I admit to being a FreeBSD and Debian guy I still replied by asking them to prove the automatic connection they were alleging. The inventor of the Linux kernel uses Fedora, so clearly their blanket assumption was false!

Anyway I'm getting sidetracked, in this case I wanted to talk about databases. I was implementing a simple website for a client that only involved one editor and a limited number of pages, so I opted to use SQLite instead of MySQL (or PostgreSQL) because its far simpler to implement, doesn't require a client/server connection and under the limited loads they'd be experiencing it could be faster.

I was so impressed by how well it performed and how easy it was to use I've started looking at other situations where it may be useful instead of just assuming everything should use MySQL. My blog here for example only has one editor and a limited number of tables with very simple foreign key relationships and a few thousand tuples. WordPress doesn't support SQLite natively, but I've been writing some basic blogging software of my own in Django and Ruby for a long time and when I started using SQLite with them I was really impressed.

But back to the client I was talking about before, no sooner had I proposed the solution and had a working prototype than someone working on another part of the project informed everyone I wasn't taking the task seriously because I was using SQLite which isn't a proper database. I didn't want to start a fight so I heaved a heavy sigh and rewrote the system in MySQL which I still maintain is far too heavy and has features that will never be needed for a database with only three tables and less than a hundred tuples each, but at least now I've been told I've corrected my childish approach.

The person did have a point, SQLite does implement only a subset of the features and SQL commands of MySQL; for example it doesn't use static typing though it can be worked around by using constraints. I also concede the possibility that in the future their needs may necessitate a more feature complete SQL implementation. To me though that's akin to Microsoft claiming Vista was better than Mac OS X Leopard because it was more efficient when using nine trillion cores, or that a Hummer is better than a bicycle because you can haul drums of nuclear waste in it more easily.

I guess I just like elegant systems that have a comfortable ceiling for growth but that at the same time are simpler and therefore easier to maintain and use. Einstein said "Make everything as simple as possible, but not simpler", and then there's the adage of the "right tool for the job.". Perhaps I just need to get used to eating humble pie more often and accepting that I'm working with other people with their own needs and expectations. Still, it'd be nice if people would stop assuming things just because of the tools you use.

Aside: I’ve hacked together a way to post blog entries from the computer pools at the university, but it’s such a long and convoluted procedure I still won’t be doing many of them until I get our home internet connection fixed.

Whoops, Oracle does auto not null primary keys

Update: It’s been brought to my attention that not defining primary keys as not null does have an impact after all, refer to Dale McGowan’s comment below. Suffice to say, I will continue to define them as not null from now on!

Much like a musician who despite getting the right sound realises they've been playing their instrument wrong throughout their career, I often find I'm not using computer software properly or I've been wasting effort. I guess I get myself into a predictable routine that I'm used to.

In this case I found out something about Oracle databases. I've been using them for years, but it took a practical class at university yesterday to realise I've been wasting time with a simple line of code that was completely unnecessary.

If you do any sort of work with databases you'd probably want to smack me on the head for this, but I didn't know that primary keys are automatically assigned as not null; presumably because it doesn't make logical sense and it would break a table to have a primary key that has null values!

Up until Friday this is the schema I would have created and entered into an Oracle database:

create table Jackarse (
  refID char(6) not null,
  name varchar(32),
  grilledCheeseSandwich boolean,
  primary key (refID)

And here's what I've since learned I can do instead:

create table Jackarse (
  refID char(6),
  name varchar(32),
  grilledCheeseSandwich boolean,
  primary key (refID)

That's right, both these schemas will result in the same table being created in Oracle. I guess it wasn't harming anything having the not null command there, it was just unnecessary.

You are on page 1 of 2. Where to now?