Tag: mysql

Kamailio v5.3 and MySQL 8

As installed on Ubuntu 19.10, Kamailio v5.3 will not work out of the box with MySQL 8 due to changes in the way in which users are created and privileges granted between MySQL 5.x and 8.

To fix this, edit /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql as follows:

# diff /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql.orig  /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql
163,164c163,166
<       sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
<               GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';"
---
>       sql_query "" "CREATE USER '$DBRWUSER'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
>                     CREATE USER '$DBROUSER'@'$DBHOST' IDENTIFIED BY '$DBROPW';
>               GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST';
>               GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST';"
172,173c174,177
<               sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost' IDENTIFIED  BY '$DBRWPW';
<                       GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';"
---
>               sql_query "" "CREATE USER '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
>                               CREATE USER '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';
>                       GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost';
>                       GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost';"
181,182c185,188
<               sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED  BY '$DBRWPW';
<                       GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';"
---
>               sql_query "" "CREATE USER '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
>                             CREATE USER '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';
>                       GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST';
>                       GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST';"

The above worked fine for me but do note:

  • Make sure the database and users do not already exist on the database (or delete them if they do).
  • Use a different username for the read-only and read-write users.
  • MySQL 8 has a bug so issue FLUSH PRIVILEGES if you have trouble manually removing a user.

Doctrine2 with GROUP_CONCAT and non-related JOIN

Doctrine2 ORM is a fantastic and powerful object relational mapper (ORM) for PHP. We use it for IXP Manager to great effect and we only support MySQL so our hands are not tied to pure Doctrine2 DQL supported functions. We also use the excellent Laravel Doctrine project with the Berberlei extensions.

Sometimes time is against you as a developer and the documentation (and StackOverflow!) lacks the obvious solutions you need and you end up solving what could be a single elegant query very inefficiently in code with iterative database queries. Yuck. 

I spent a bit of time last night trying to unravel one very bad example of this where the solution would require DQL that could:

  1. group / concatenate multiple column results from a one-to-many relationship;
  2. join a table without a relationship;
  3. ensure the joining of the table without the relationship would not exclude results where the joint table had no matches;
  4. provide a default value for (3).

Each of these was solved as follows:

  1. via MySQL’s GROUP_CONCAT() aggregator. The specific example here is that when a MAC address associated with a virtual interface can be visible in multiple switch ports. We want to present the switch ports to the user and GROUP_CONCAT() allows us to aggregate these as a comma separated concatenated string (e.g. "Ethernet1,Ethernet8,Ethernet9").
  2. Normally with Doctrine2, all relationships would be well-defined with foreign keys. This is not always practical and sometimes we need to join tables on the result of some equality test. We can do this using a DQL construct such as: JOIN Entities\OUI o WITH SUBSTRING( m.mac, 1, 6 ) = o.oui.
  3. This is as simple as ensuring you LEFT JOIN.
  4. The COALESCE() function is used for this: COALESCE( o.organisation, 'Unknown' ) AS organisation.

We have not yet pushed the updated code into IXP Manager mainline but the above referenced function / code is not replaced with the DQL query:

SELECT m.mac AS mac, vi.id AS viid, m.id AS id, 
    m.firstseen AS firstseen, m.lastseen AS lastseen,  
    c.id AS customerid, c.abbreviatedName AS customer,
    s.name AS switchname, 
    GROUP_CONCAT( sp.name ) AS switchport, 
    GROUP_CONCAT( DISTINCT ipv4.address ) AS ip4, 
    GROUP_CONCAT( DISTINCT ipv6.address ) AS ip6,
    COALESCE( o.organisation, 'Unknown' ) AS organisation

FROM Entities\\MACAddress m
    JOIN m.VirtualInterface vi
    JOIN vi.VlanInterfaces vli
    LEFT JOIN vli.IPv4Address ipv4
    LEFT JOIN vli.IPv6Address ipv6
    JOIN vi.Customer c
    LEFT JOIN vi.PhysicalInterfaces pi
    LEFT JOIN pi.SwitchPort sp
    LEFT JOIN sp.Switcher s
    LEFT JOIN Entities\\OUI o WITH SUBSTRING( m.mac, 1, 6 ) = o.oui

GROUP BY m.mac, vi.id, m.id, m.firstseen, m.lastseen, 
    c.id, c.abbreviatedName, s.name, o.organisation

ORDER BY c.abbreviatedName ASC

 

Virtual Mail with Ubuntu, Postfix, Dovecot and ViMbAdmin

As part of pushing our new release of ViMbAdmin, I wrote up a mini how-to for setting up a virtual email system on Ubuntu where the components are:

  • Postfix as the SMTP engine;
  • Dovecot for IMAP. POP3, Sieve and LMTP;
  • ViMbAdmin as the domain / mailbox / alias management system via web interface.

It supports a number of features including mailbox archival and deletion, quota support and display of mailbox sizes (as well as per domain totals).

Find the how-to at:

Recovering MySQL Master-Master Replication

MySQL Master-Master replication is a common practice and is implemented by having the auto-increment on primary keys increase by n where n is the number of master servers. For example (in my.conf):

auto-increment-increment = 2
auto-increment-offset = 1

This article is not about implementing this but rather about recovering from it when it fails. A word of caution – this form of master-master replication is little more than a useful hack that tends to work. It is typically used to implement hot stand-by master servers along with a VRRP-like protocol on the database IP. If you implement this with a high volume of writes; or with the expectation to write to both without application knowledge of this you can expect a world of pain!

It’s also essential that you use Nagios (or another tool) to monitor the replication on all masters so you know when an issue crops up.

So, let’s assume we have two master servers and one has failed. We’ll call these the Good Server (GS) and the Bad Server (BS). It may be the case that replication has failed on both and then you’ll have the nightmare of deciding which to choose as the GS!

1) You will need the BS to not process any queries from here on in. This may already be the case in a FHRP (e.g. VRRP) environment; but if not, use combinations of stopping services, firewalls, etc. to stop / block access to the BS. It is essential that the BS does not process any queries besides our own during this process.

2) On the BS, execute STOP REPLICA to prevent it replicating from the GS during the process.

3) On the GS, execute:

STOP SLAVE; (to stop it taking replication information from BS);
FLUSH TABLES WITH READ LOCK; (to stop it updating for a moment);
SHOW MASTER STATUS; (and record the output of this);

4) Switch to the BS and import all the data from the GS via something like:

$ mysqldump -h GS -u root -psoopersecret --all-database --quick \
  --lock-all-tables | mysql -h BS -u root -psoopersecret;

Note that I am assuming that you are  replicating all databases here. Change as appropriate if not.

5) You can now switch back to the GS and execute UNLOCK TABLES to allow it to process queries again.

6) On the BS, set the master status with the information your recorded from the GS via: 

CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.xxxxxx', SOURCE_LOG_POS=yy;

7) Then, again on the BS, execute START REPLICA. The BS should now be replication from the GS again and you can verify this via SHOW REPLICA STATUS.

8) We now need to have the GS replicate from the BS again. On the BS, execute SHOW MASTER STATUS and record the information. Remember that we have stopped the execution of queries on the BS in step 1 above. This is essential.

9) On the GS, using the information just gathered from the BS, execute: 

CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.xxxxxx', SOURCE_LOG_POS=yy;

10) Then, on the GS, execute START REPLICA. You should now have two way replication again and you can verify this via SHOW REPLICA STATUS on the GS.

11) If necessary, undo anything from step 1 above to put the BS back into production.

There is a --master-data switch for mysqldump which would remove the requirement to lock the GS server above but in our practical experience, there are various failure modes for the BS and the --master-data method does not work for them all.

MySQL 5.6 – Memcached / NoSQL Support and More

MySQL 5.6 has been released with some interesting new features and performance increases:

  • What’s New in MySQL 5.6
  • DBA and Developer Guide to MySQL 5.6
  • InnoDB Integration with memcached:MySQL 5.6 includes a NoSQL interface, using an integrated memcached daemon that can automatically store data and retrieve it from InnoDB tables, turning the MySQL server into a fast “key-value store” for single-row insert, update, or delete operations. You can still also access the same tables through SQL for convenience, complex queries, bulk operations, application compatibility, and other strengths of traditional database software.

    With this NoSQL interface, you use the familiar memcached API to speed up database operations, letting InnoDB handle memory caching using its buffer pool mechanism. Data modified through memcached operations such as ADD, SET, INCR are stored to disk, using the familiar InnoDB mechanisms such as change buffering, the doublewrite buffer, and crash recovery. The combination of memcached simplicity and InnoDB durability provides users with the best of both worlds.

  • Multi-threaded Slaves
  • Improved IPv6 Support – both in the bind to address option and the INET_ATON() function.
  • Replication improvements.

All in all, some nice new features. Especially the memcached integration.

That said, MariaDB seems to be making inroads on MySQL with some distributions considering a switch. Some interesting reading from that project includes:

Analysing MySQL Slow Query Logs

MySQL has a really useful feature that allows it to log slow queries where slow is a minimum time defined by you in micro seconds. It helps a lot is diagnosing website outages or slow responsiveness issues after the fact.

Unfortunately I couldn’t find any nice graphical tools for analysing these but there are a few command line tools:

mysqldumpslow

MySQL’s own tool, mysqldumpslow, which aggregates queries and allows you to sort them by: query time or average query time; lock time or average lock time; rows sent or average rows sent; or the number of queries.

Percona’s MySQL Slow Query Log Analyser

Dating from 2006, Percona’s Peter Zaitsev wrote about their own version of a slow query log analyser (local copy) which has given me good results. Note that their micro time patch has since been incorporated into MySQL mainstream.

One of the main differences over MySQL’s own version is that as well as printing the aggregated query (with number and string literals wildcarded), it also prints a real example of the query allowing a copy and paste to MySQL for execution with EXPLAIN.

Example output with query details redacted:

### 230 Queries 
### Total time: 4708.948293, Average time: 20.4736882304348
### Taking 0.093420 to 203.693466 seconds to complete
### Rows analyzed 0 - 141008
SET timestamp=XXX;
SELECT ... FROM ... AS A 
        INNER JOIN ... AS C ON C.item_id = A.item_id 
    WHERE XXX AND C.item_lang = 'XXX' AND ... 
    ORDER BY CATALOG.item_sort LIMIT XXX;

SET timestamp=1348032761;
SELECT ... FROM ... AS A 
        INNER JOIN ... AS C ON C.item_id = A.item_id 
    WHERE 1 AND C.item_lang = '1' AND ... 
    ORDER BY C.item_sort LIMIT 1;

 

New Replication Features in MySQL 5.6

I’ve just been reading an article on replication in MySQL 5.6 and there are quite a few new cool features that will vastly improve replication environments with MySQL. Some of these include:

  • Optimised row based replication (documentation here) – replication used to mean execute the exact same queries on the slave server(s) as the master. This, as you can imagine, was incredibly inefficient. With row based replication, the resultant changed row of an INSERT, UPDATE or DELETE operation was replicated saving significant resources on slave servers. This was a big win. It now looks like it’s been further optimised in that only the changed elements (and a primary key) need to be replicated.
  • Introduction of Global Transaction Identifiers (GTIDs) allowing the source and sequence of a replication statement to be globally unique within a replication cluster. This with some cool new utilities (mysqlfailover and mysqlrpladmin) create a lot of native options for failover for maintenance or failure (see more about GTIDs and the utilities).
  • Time delayed replication allowing a slave to purposefully remain behind the master for any given delay. This may be a life saver for that table you accidently dropped!

There’s a lot more too and you can read about it all here.

We’ve Just Launched TallyStick

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

[TallyStick Logo]

Overt at Open Solutions, we manage networks, build Internet infrastructure, consult and develop web applications for many customers. Tracking the time our engineers spent on different projects – especially those on the front line hopping from customer to customer – was becoming an issue.

And we had enough. So we built TallyStick. Necessity, as the saying goes, is the mother of invention.

We believe in web applications that are simple, beautiful and functional. And after an exhaustive search, we couldn’t find one to fit our needs… so we built it. And who better to build a time tracking application for IT and other consulting professionals than those same professionals!

TallyStick allows you to focus on what you love to do while it takes care of tracking and accumulating the time you spend on projects. We believe it’s simple, efficient and quick. And we hope you agree.

As of today, TallyStick is live and open for business. Please sign up, take advantage of the one month’s free trial (with no credit card details required) and tell us what you think.

Also, all users signing up during the beta period will receive a lifetime discount of 10% on that account!

Using Doctrine ORM with Zend Application

In this first of a serious of articles where we delve into some of the hidden treasures in our ViMbAdmin application, we look at how to integrate Doctrine ORM with Zend – and specifically Zend_Application and Zend_Controller.

In this article we delve into our ViMbAdmin application and we look at how to integrate Doctrine ORM with Zend – and specifically Zend_Application and Zend_Controller.

The first assumption (and requirement) we are going to make is that you are using Zend_Application. If you want to see a working application set up and configured for this, please checkout or browse our ViMbAdmin source code – which we’ll reference throughout this document.

Zend Application has a resource framework which allows us to bootstrap various resources on demand. We have created a Doctrine resource for this very purpose which you can download from here (and you may need to edit the class name and change the plugin path in the config code below to match your setup). Our implementation does many things:

  • instantiates the Doctrine object;
  • sets up an autoloader for Doctrine models;
  • instantiates the Doctrine manager;
  • opens the connection to the database;
  • sets all collations and character sets to UTF8 (this is hard coded but can easily be changed);
  • sets various hard coded Doctrine attributes which can also be changed.

We the add various configuration parameters to the application.ini file:

 

Or the following where $application is the instance of Zend_Application:

$application->getBootstrap()->bootstrap( 'doctrine' );

From that, you can use Doctrine to your hearts content!

We also have a Doctrine CLI script which works from the same resource. See:

http://code.google.com/p/vimbadmin/source/browse/trunk/bin/doctrine-cli.php

 

Doctrine ORM – Find Many to Many Objects Without a Relationship

Hmmm, does the title of this post make sense? Probably not but it’s not an easy concept to squeeze into a few words.

Here’s the scenario, I have two tables A and B in Doctrine ORM with a many-to-many relationship defined in table AB.

Now, I want to find all objects in A that do not have a relationship with an object in B via AB.

Here’s what I have:

Doctrine_Query::create()
    ->from( 'A a' )
    ->leftJoin( 'A.AB ab' )
    ->where( 'ab.id IS NULL' )
    ->fetchArray()

This works but is it the best way?