BigSmoke

Smokes your problems, coughs fresh air.

Tag: MySQL (Page 1 of 3)

Upgrading Roundcube on Ubuntu 18.04 and Ubuntu 20.04

June 18, 2023 /

What a mess… After postponing this for many months because I knew what awaited me, I double-upgraded an Ubuntu 18.04 server today, and aside from MySQL breaking in some spectacular ways (corrupt redo log for one), the Roundcube package was broken, primarily due to MySQL.

In MySQL 8, ‘system’ is now a reserved keyword; a change that in my opinion was quite risky, because many people use that word. This resulted in the Roundcube Debian migration scripts failing, because the word ‘system’ was used without back-ticks around it. 

mysql said: ERROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system SET value='2018021600' WHERE name='roundcube-version'' at line 1.

These scripts are actually part of the .deb file, to be executed by dbconfig.

I reported the bug, but there was no interest in fixing it. Today I posted my work-around there too. Look there for the SQL files I used. I can’t upload them to this blog.

I had more problems, so I wanted to lay out what I did to fix it.

Ubuntu 20.04 installs this deb. In it are the SQL migration scripts in ‘usr/share/dbconfig-common/data/roundcube/upgrade/mysql’. I extacted it with ‘dpkg –extract’ and I took out ‘1.4.1-1’ and removed everything above the line that failed (because in MySQL the migration is not atomic…) and ran it like:

mysql roundcube < 1.4.1-1__starting_from_first_failed_statement.sql

Then on the next upgrade, I had to do the same with the deb for 1.5.

This one was more problematic, because the migration steps contained a change to case insensitive collation. This breaks if you have multiple users with different collation:

ERROR 1062 (23000) at line 33: Duplicate entry 'Info@example.nl-mail.example.nl' for key 'users.username'

I had to remove the users in question, from the table ‘users’ and ‘identities’, and continued running the script from the offending line (because again, DDL is not atomic in MySQL).

To be honest, I didn’t check if it cascaded foreign keys, like users’ address books and such… It was primarily ancient users, so I didn’t care.

A weird observation, is that the ‘system’ table is actually empty. That “SET value=’2018021600′” was a no-op. Now I wonder if I should add that row…?

The ‘system’ problem is likely to happen again on the next upgrade. At least I’m prepared now.

G.B.D. Calamari Joomla and phpBB upgrade

September 6, 2014 /

As the chairman of G.B.D. Calamari, a Dutch student diving association, it falls to me to upgrade an aging Joomla configuration that is a relict of the last time that the board was chaired by somebody with web-development expertise (Joris van Alphen). This happened to be during a time when Joomla 1.5.20 was cutting-edge. It no longer is, so, although the website still looks nifty enough for this day and age (in my ageing eyes, at least), a software upgrade is long overdue.

[Note that the below is more of a disjointed collection of notes than it is an article of interest to anyone not directly involved with the maintenance of www.gbdcalamari.nl.]

Backup

The first step in any upgrade, of course, is backing up everything. Everything means all the files and the complete database. I did this using the “Export” option in phpmyadmin.

One peculiarity that I noticed during the database backup was that while there there were two MySQL databases — one called calamari_phpbb and one called calamari_site — the one called calamari_phpbb actually contained the tables for both the phpBB forum and the Joomla 1.5 site; calamari_site seemed to be a legacy of an older (custom-written) incarnation of the website.

phpBB.nl upgrade

The phpBB upgrade went surprisingly well. To update phpBB, I used phpbb.nl‘s automatic update packages to update from 3.0.8 to 3.0.12 in 4 steps:

  1. 3.0.8 to 3.0.9 (common.php merged)
  2. 3.0.9 to 3.0.10
  3. 3.0.10 to 3.0.11
  4. 3.0.11 to 3.0.12 (common.php merged again)

One by one, for each of these packages, I

  1. unpacked and uploaded the install dir,
  2. visited http://www.gbdcalamari.nl/phpbb/install/,
  3. updated the database,
  4. downloaded the ‘diff’, and
  5. copied it through WebDAV,
  6. checked if the update was successful and
  7. deleted the install dir.

It was quite fun (albeit a bit cumbersome) to update web software the noob way for a change.

Joomla 1.5.20 to 1.5.26

Joomla 2.5.19 is the current Joomla version (not counting the version 3 branch). The docs on upgrading from 1.5.x to 2.5 mention specifically that it’s a good idea to first upgrade to the latest 1.5.x version. Again, as with phpBB, I played the n00b game and performed the file updates by uploading the files in the the latest 1.5.x patch, overwriting the old Joomla stuff.

Joomla 1.5.26 to 2.5.19

The tricky part was yet to come: the upgrade from one major version to the next, from Joomla 1.5.26 to 2.5.19. The official guide on this process uses the jUpgrade extension. I followed the steps. jUpgrade installed the new version in a jupgrade/ subdirectory in the document root, after which I moved the old installation temporarily into a subdirectory called joomla1.5. (‘Temporarily’ may not be the most accurate qualification; now, five months later, it’s still sitting around as I’m finally polishing the last rough edges left after the upgrade.) With the old installation safed, I moved the Joomla 2.5 files from jupgrade/ to the document root. In this way, I still had access to the old, fully functional site while transitioning the templates and modules, etc.

Joomla components, modules and plug-ins

There are several Joomla components that I had to also upgrade, the most important of which is JFusion, which is necessary for the phpBB integration.

JFusion

It took some tweaking to get JFusion behave as well as it did previously. The following error caused a bit of a headache (and might do so again, during future phpBB updates):

Fatal error: Cannot redeclare utf8_substr() (previously declared
in /home/calamari/public_html/libraries/phputf8/mbstring/core.php:98)
in /home/calamari/public_html/phpbb/includes/utf/utf_tools.php on line 225

The solution was to patch utf_tools.php with a version provided by the JFusion project.

JA Purity

Another process that can be challenging when upgrading between major versions of a CMS is updating the template. The template that has been used by the previous site administrator (JA Purity) seems to be quite popular, however, with a Joomla 2 and 3 version available. Too bad that he did modify it, so that I had to depend om my good old friend that is diff to figure out which changes he made. To be able to more seamlessly upgrade JA Purity in the future, I modified Joris van Alphen’s JA Purity fork to use a Calamari template theme for most of his customizations.

Favicon

I re-enabled the old favicon file, by copying it to the ja_purity_ii/themes/calamari/ directory and copying a modified head.php to a new blocks/ subdirectory for that theme.

File attachments

Migrating file attachment from Joomla 1.5 to 2.5 was non-trivial. Initially, I tried this while running 2.5.7, but the new version of the attachments plugin required 2.5.7 or higher. Besides my initial confusion about how I ended up with Joomla 2.5.7 instead of 2.5.19, following the cumbersome instructions in the project‘s upgrade manual worked out well.

Event Manager

Eventually, Joomla Event Manager is set to replace the EvenList component by Schlu that Joris had used for the Joomla 1.5 incarnation of the Calamari website. For now, though, Schlu’s EventList component will do. Data migration only required copying the MySQL table rows from the Joomla 1.5 prefixed tables (“jos_”) to the Joomla 2.5 prefixed tables (“j25_”):

INSERT INTO j25_* SELECT * FROM jos_*;
INSERT INTO j25_eventlist_categories SELECT * FROM jos_eventlist_categories;
INSERT INTO j25_eventlist_events SELECT * FROM jos_eventlist_events;
INSERT INTO j25_eventlist_groupmembers SELECT * FROM jos_eventlist_groupmembers;
INSERT INTO j25_eventlist_groups SELECT * FROM jos_eventlist_groups;
INSERT INTO j25_eventlist_register SELECT * FROM jos_eventlist_register;
INSERT INTO j25_eventlist_settings SELECT * FROM jos_eventlist_settings;
INSERT INTO j25_eventlist_venues SELECT * FROM jos_eventlist_venues;

Contact form

To upgrade the ALFcontact component
again only, the factory contacts had to be replaced with the old Calamari contacts, after downloading and installing the new version:

DELETE FROM j25_alfcontact;
INSERT INTO j25_alfcontact SELECT * FROM jos_alfcontact;

Polls

The Joomla 1.5 website used mod_poll for some fun (but mostly useless polls), a module which was removed somewhere during the further development of the 1.5 branch. I found no ready replacement which could import the old mod_poll content, so I decided to not install a new module.

Picasa integration

For the continued use use wgPicassa, the most important step is to upload years worth of photos to Picasa, which was supposedly inconvenient, because the Picasa account was said to be linked to Joris’ private Google Account. I thought I’d thus have to create a new Picasa account, linked to an @gbdcalamari.nl email address and ask him to re-upload all the photo’s in his account. Luckily, it turned out that the Picassa account was already associated with an @gbdcalamari.nl email address. So, after resetting the password using that email adress, we can now finally update the gallery.

Slideshow on public homepage

A slideshow with promotional pictures was originally displayed on the home page for non-logged in visitors (using the Unite Nivo Slider extension). It took me some trouble to find out how to display this module before the main content in JA Purity. In fact, that was the only place where I couldn’t position anything. All I could find were clumsy work-arounds, until I stumbled upon the existance of “mass positions” in the JA developer guide.

Joomla 2.5.19 to 2.5.24

I left the upgrade process were it was on Mach 31 of this year, finally continuing the process this August. In the meantime, the Joomla 2.5 branch has arrived at version 2.5.24. Luckily for me, it’s much easier to update an existing 2.5.x installation. After making a fresh backup of everything, I unpacked the archive remotely, after which I updated the database. That was it.

Left to do

  • Cleanup excess JUpgrade table.
  • Get rid of the cpanel thingy in the header
  • Move font size control from cpanel thingy to header?
  • Restore search in header
  • Reinstall XMap extension
  • Rename database to avoid future confusion

Dumping MySQL users and grants

January 17, 2014 /

MySQL stores its users and grants in a very annoying, non-clonable format. I found this post, describing how you can extra them.

This bash function generates grants:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

Trying to reduce MySQL InnoDB disk usage after major reduction of data

October 24, 2012 /

So, two days ago, I tried to shrink my MediaWiki database and it almost worked, except the MySQL process wouldn’t shrink along with it.

Of course I tried all the obvious things such as dropping the database, stopping and restarting the process, followed by reloading the database. Optimizing tables, altering tables, all the obvious. But, to no avail, because there’s this bug. Well, technically it’s not a bug. Like most of MySQL, it has been “designed” to be counter-intuitive, non-standard, riddled with special cases, exceptions, work-arounds and duct tape. So, the “bug” is really just a feature request from dumb people much like myself who want stupid things like a database that becomes smaller in size when you delete most of your data.

I should really move to a virtual private server environment, where I can just run a real database (PostgreSQL), but I’m still on NFSN, whom (besides the sky-rocketing storage costs as of late) have given me no reason to complain so far.

I thought I’d ask them to help me out.

Recently, due to inattention to spam, one of my wiki databases has grown to well over 10 GiB. After finally getting around to removing most of this spam and tweaking some settings to reduce the table size from over 11Gig to a couple of MiB, I thought my Last Reported Size would go down accordingly.

But no such luck. Apparently it’s a MySQL issue, with the only solution being to drop the offending database (after a dump, of course), stop the MySQL process, remove the offending table, restart the process and then reload the database.

Instead, you could use the innodb_file_per_table option, which is enabled by default on MariaDB.

Without that option set, OPTIMIZE, ALTER TABLE and all that stuff will do nothing to reduce a table size. It’s one of those issues which the MySQL devs are really not interested in solving: http://bugs.mysql.com/bug.php?id=1341

I hope you can help me out with this, either by setting the innodb_file_per_table option or by removing all my database files. In the latter case, I’d hope you ask me to green light this first so that I can make some other data size reductions in various databases before I make a final backup.

But then I thought better of it, when I learned that—contrary to my expectations—the option really was enabled:

SHOW VARIABLES LIKE ‘innodb_file_per_table’;

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    | 
+-----------------------+-------+
1 row in set (0.00 sec)

So the option was enabled. I had to look elsewhere for an answer, which made me decide to do the following.

OPT="--host=myhost --user=myuser --password=mypassword --skip-column-names --batch hardwood";
mysql $OPT --execute="show tables" | grep -v mw_searchindex | tablename;
  mysql $OPT --execute="alter table $tablename ENGINE=InnoDB; optimize table $tablename"

Needless to say, this didn’t help. So I wrote the support request for my hosting provider:

Recently, due to inattention to spam, one of my wiki databases has grown to well over 11 GiB. After finally getting around to removing most of this spam and tweaking some settings to reduce the table size of the most offending table from over 11Gig to a couple of MiB, I thought my Last Reported Size would go down accordingly.

Since you have the innodb_file_per_table option enabled (the default in MariaDB and confirmed by “SHOW VARIABLES LIKE 'innodb_file_per_table';”), I’d expect “ALTER TABLE mw_text ENGINE=InnoDB” to shrink the table (the command should move the table to its own file, even if it wasn’t already in its own file before the move to MariaDB). It didn’t. Last Reported Size is still approximately the same. Dropping and reloading the entire database didn’t do much to help either.

I suspect that the problem is that that the old shared file still exists and that this file won’t be deleted even if there are no more tables in it with the only solution then being to dump the database, drop it, remove all its files and then reload the dump.

Anyway, I’d like it to be so that my database will actually shrink when I delete stuff and the way I understand it this should actually be possible thanks to innodb_file_per_table. If it’s the same old story as without innodb_file_per_table, that would just be awful, because then I’d need your intervention every time that I’m trying to reduce my database process size.

I hope that you can somehow reload the database and remove the bloated
ibdata1 file.

Now, I’m just waiting patiently for their response while my funds whither…

Shrinking/compressing a MediaWiki database

October 24, 2012 /

As of late, I haven’t had a lot of time to chase after spammers, so – despite of anti-spam captchas and everything – a couple of my wikis have been overgrowing with spam. One after the other I’ve been closing them down to anonymous edits, even closing down user registration alltogether, but some a little too late.

The last couple of months my hosting expenses shot through the roof, because my Timber Investments Wiki database kept expanding to well over 14 GiB. So I kind of went into panic mode and I even made time for another one of my famous spam crackdowns—the first in many, many months.

The awfully inefficient bulk deletion of spam users

Most of this latest tsunami of spam was in the form of “fake” user pages filled with bullshit and links. The only process that I could think of to get rid of it was quite cumbersome. First, I made a special category for all legitimite users. From that I created a simple text file (“realusers.txt”) with one user page name per line.

Then, I used Special:AllPages to get a list of everything in the User namespace. After struggling through all the paginated horror, I finally found myself with another copy-pasted text file (“unfilteredusers.txt”) that I could filter: 

cp unfilteredusers.txt todelete.txt
cat realusers.txt | u
  sed -i -e "/$u/d" todelete.txt

(I’d like to know how I could have done this with less code, by the way.)

This filtered list, I fed to the deleteBatch.php maintenance script:

php maintenance/deleteBatch.php -u BigSmoke -r spam todelete.txt

By itself, this would only increase the size of MW’s history, so, as a last step, I used deleteArchivedRevisions.php to delete the full revision history of all deleted pages.

This work-flow sucked so bad that I missed thousands of pages (I had to copy-paste this listings by hand, as I mentioned earlier above), and had to redo it again. This time, the mw_text table size shrunk from 11.5 GiB to about 10 GiB. Not enough. Even the complete DB dump was still way over 5 Gig [not to mention the process size which remained stuck at around 15 GiB, something which I woudn’t be able to solve even with the configuration setttings mentioned after this].

Enter $wgCompressRevisions and compressOld.php

The huge size of mw_text was at long last easily resolved by a MW setting that I had never heard about before: $wgCompressRevisions. Setting that, followed by an invocation of the compressOld.php maintenance script took the mw_text table size down all the way from >10 GiB to a measly few MiB:

php maintenance/storage/compressOld.php

SELECT table_schema 'DB name', sum(data_length + index_length) / 1024 / 1024 "DB size in MiB"
FROM information_schema.TABLES
WHERE table_schema LIKE 'hard%'
GROUP BY table_schema;

+----------+----------------+
| DB name  | DB size in MiB |
+----------+----------------+
| hardhout |    41.88052750 | 
| hardwood |   489.10618973 | 
+----------+----------------+

But it didn’t really, because of sweet, good, decent, old MySQL. 🙁 After all this action, the DB process was still huge (still ~15 GiB). This far exceeded the combined reported database sizes. Apparently, MySQL’s InnoDB engine is much like our economy. It only allows growth and if you want it to shrink, you have to stop it first, delete everything and then restart and reload.

Future plans? Controlled access only?

One day I may reopen some wikis to new users with a combination of ConfirmAccount and RevisionDelete and such, but combatting spam versus giving up on the whole wiki principle is a topic for some other day.

Converting all tables in MySQL DB to InnoDB

October 2, 2012 / 

#!/bin/bash
 
exit 1
 
dbname="eorder"
 
echo 'SHOW TABLES;'  | mysql $dbname  | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}'  | column -t 
echo 'SHOW TABLES;'  | mysql $dbname  | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}'  | column -t | mysql $dbname

Listing MySQL table sizes

July 12, 2012 /

This query lists the sizes of all tables in MySQL:

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME, 
  CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA, 
  CONCAT(ROUND(data_free  / ( 1024 * 1024 ), 2), 'MB') FREE 
from information_schema.TABLES 
where TABLE_SCHEMA NOT IN ('information_schema','mysql', 'performance_schema');

This query lists the database sizes:

SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME, 
  CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 ), 2), 'MB') DATA 
from information_schema.TABLES 
where TABLE_SCHEMA NOT IN ('information_schema','mysql', 'performance_schema') 
group by TABLE_SCHEMA;

Converting a MySQL database from latin1 to utf8

August 16, 2011 / 

mysqldump dbname > dbname_bak_before_messing_with_it.sql
mysqldump --default-character-set=latin1 --skip-set-charset dbname > dump.sql
sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql
mysql --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;" 
mysql --default-character-set=utf8 dbname < dump_utf.sql

Getting a better MySQL prompt

April 18, 2011 /

When you want to see which user you are and which database you’re working with, put this in .my.cnf:

prompt=(\\u@\\h) [\\d]>\\_

Too bad mysql doesn’t support color. You can make it work with rlwrap, but that’s kind of clumsy.

edit: hmm, this destroys mysqldump… argh.

« Older posts

© 2024 BigSmoke

Theme by Anders Noren — Up ↑