Macrotone Blogs

Macrotone blogs upon Joomla, our products and other matters.

MySQL 1071 and associated errors.

mysqlOur users have reported a few MySQL errors over the years but one of the most common appears to be the

ERROR 1071 (42000): Specified key was too long; max key length is xxx bytes

Where xxx is some specified value often, but not always, a value of 767.

For that reason we decided that we would make this blog entry to try and explain the situation.

We are concerned here mainly with InnoDB tables since that is what we tend to use in our extensions.

Continue reading

MariaDB and Joomla ?

MariaDBWe were looking at the possibilities of upgrading the version of MySQL we are using on out NAS system and were reminded of the existence of the MariaDB database as a possible alternative. Alternative because our NAS does not easily permit the upgrade of the MYSQL part of the system mainly because it is so tightly tied into the other features.

What is MariaDB one might ask. Well there is probably no better explanation that that upon the MariaDB web site.

Continue reading

MySQL and JSON data structures

mysqlHaving been working with JSON data structures recently our thoughts turned to how this could reasonably be handled by SQL queries in the production of items such as reports etc.  Leaving aside the question of how one would ‘know’ and handle the various constituents in the specific JSON object on a generic basis, we looked specifically at what was currently available.

Coming from a strong Oracle background we were familiar with the use of Java within the Oracle database and indeed have made use of it ourselves in the past, but our specific interest this time was MySQL and its use by Joomla, and we were not aware of any feature implementing Java with the MySQL database.

Continue reading

Install IIS, PHP, MySQL & Joomla on Windows 7

iisWe had cause to investigate a problem with a Joomla component installed upon an IIS platform.  We usually use Apache as our web server, so were not totally familiar with the use of IIS, so this blog covers the installation, configuration and the basic options of IIS, PHP, MySQL and Joomla.

There is no intent to make use of such a setup upon a regular basis, and would anticipate only having to perform the task infrequently, hence the decision to document the steps.  We encountered a number of problems as we performed the set up, and we searched on the web in many placed before achieved our goal. For this reason this is somewhat long, but the benefit being that we have all the details in one location.

The installation is assuming the use of a local installation upon a single workstation.

Continue reading

Changing Joomla MySQL database connection details

mysqlWe recently had cause to have to modify the connection details that a Test Joomla setup was using to connect to the underlying database.  The reason was that we wanted to test out a component installation where some specific database privileges had not been granted.

In out case we were interested in the database privileges ‘CREATE VIEW’, ‘CREATE ROUTINE’ and ‘TRIGGER’. Since our component would make use of database triggers, procedures and views we needed to test out an installation on a system where such grants were not present.

Our systems had the required privileges granted and we knew that editing the ‘configuration.php’ file in the existing Joomla installation should enable us to achieve this easily, but despite our attempts this wouldn’t work and instead gave us an error: ‘Database connection error (3): Could not connect to database ‘.

We played with the username and password settings in the ‘configuration.php’ file without any success it just wouldn’t connect and we knew that we had another database set up with the same username and password.

Then we remembered that we had only granted permissions for this ‘restricted’ database account to a specific database.  All we had to do was ensure that the correct grants were made for our 'second database’.

   1:  CREATE USER 'test'@'localhost' IDENTIFIED BY 'password';           -- Not required if the user already exists.
   2:  GRANT SELECT, INSERT, UPDATE, DELETE ON DBX.* TO 'test'@'localhost';
   3:  CREATE USER 'test'@'%' IDENTIFIED BY 'password';                    -- Not required if the user already exists.


Where test is the name of your connection user, password is whatever your desired password is, and DBX is the name of your MySQL database.  Obviously if your database is not on your localhost you need to modify it for the appropriate host name or IP address, which ever is most apt.

Once we had done this it all worked perfectly and our testing could commence.

A new Audit tool for Joomla

auditAbout a week ago we released a new component for Joomla named JAudit.

Running upon Joomla 2.5 and Joomla 3.x this is a comprehensive auditing component that makes use of the underlying database to log changes made to the Joomla tables. It does not require any changes to Joomla core code and should work without impacting any Joomla functionality. The component requires that the system has been granted the appropriate database privilege to create database triggers.

We have long believed that if you are using a fully featured database upon your Joomla site it seems ‘silly’ to not make use of the features that the database provides.  Databases these days are very feature rich and it is only necessary to look at the possibilities of these features to realise just how much one is missing out upon. One analogy that brings this home, might be ‘Would you pay for a Ferrari and only drive it around in first gear?’

Continue reading

Nested tables, MySQL and Joomla Smart Search.

b2ap3 icon joomlaWe have been looking at refining a finder plugin in Joomla which is accessing a nested MySQL table.  Nothing complex about that one would think.

However the desire was to expand out the different sub record titles into a single ‘title’ comprised of the individual sub record titles linked together offered a few ‘opportunities’ for want of a different word!

To explain further.  We have a classic nested table structure as follows:

Continue reading

Oracle announces MySql changes

At Oracle OpenWorld this week there were a few changes announced for MySql.  Oracle has issued an open source MySQL 5.6 release candidate.

Oracle issued its first MySQL 5.6 preview in July of 2011. For certain types of queries, there has been a performance gain of as much as 200 percent.

MySQL 5.6 includes sub-query optimizations that improve the query execution speeds. The underlying InnoDB storage engine has also been enhanced for throughput performance.

Under Oracle's guidance the number of new features per release has been cut down to be more manageable. While at the same time, there is more effort going into testing and overall quality.

From a release perspective, Oracle believe that users don't want to upgrade their databases often with big feature releases.  It is noted that Oracle has been coming out with a regular stream of maintenance releases though, as the 5.6 release is being developed. The current plan is for major new releases of MySQL every other year. That would place a MySQL 5.6 generally available release for early in 2013.

Technical Article links

The following are interesting articles that are generally informative and may prove useful to someone in the future.

Scaling a PHP MySQL Web Application, Part 1

Scaling a PHP MySQL Web Application, Part 2

The DBA’s Guide to Setting Up Oracle RAC One Node and Oracle Data Guard

The DBA’s Guide to Setting Up Oracle RAC One Node and Oracle Data Guard, Part 2 (Advanced)

MySQL and Oracle Goldengate

MySQL versions and binlog_format settings

Following on from an earlier post, there has been further investigations into the settings of the binlog_format setting for MySQL.  The problem is possibly aggravated by the use of InnoDB tables, which are the default in MySQL 5.5, the use of which offers some distinct advantages.  This setting seems to have been introduced in version 5.1.5 of MySQL.  Prior to that date it didn't exist and attempts to use the setting would generate an error, which is not totally surprising.

Continue reading
Go To Top

The Macrotone Consulting Web site would like to use cookies to store information on your computer, to improve our website. Cookies used for the essential operation of the site have already been set. To find out more about the cookies we use and how to delete them, see our Privacy Policy.

I accept cookies from this site.