Joomla 3.1.1 with Postgresql issues with the associations table

Posted at 7:31:30 PM in Joomla - CMS (7) | Read count: 2334

I just completed a Joomla 3.1.1 fresh install and had some issues with installing to Postgresql. First, I was running my Postgres on a old Linux box Fedora 11 which is no longer supported. Joomla requires Postgres 8.3.14 or above and I was running 8.3.x not 14 on that box and couldn't install the Joomla files. So I am updating the server and the database, however; before that goes into production, I am testing so as to resolve problems before it goes live.

So my test environment is completely clean. Ubuntu 12.0.4T 32 bit and Postgresql 9.1 (I couldn't get 9.2 installed on that box, but 9.1 meets the requirements). 

At this point, I have managed to get a working version of Joomla installed on the Ubuntu box. In the production environment, the Postgres database will be on a different server from the web site. I have been trying to add templates and haven't been successful. I initially ignored the warning in the extension manager because until I tried to add a template, I had no problems at all. The error message i was getting was on on the *_associations table stating the field id was not type INT. That isn't a type in Postgresql that I know of. I found the query with debug turned on and cut and paste the query to see what info was actually being presented. Here is the query that was failing: 

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name='*_associations'  - note: the * is a replacement for the actual value.
AND column_name='id'
AND data_type='INT'

I took out the data_type line. The resulting query is: id, integer. Postgres is very particular about case and length matching. The command to alter the table in 3.0.3.sql statement kept failing also. After reading up on others who had similar problems at this link, I was able to locate the file and removed it. After that, the databases all checked out, but I'm still unable to add the particular template I'm working with. 

The issue was actually the id numbering. After the fresh install of Joomla went in, the id in the template_styles table was left at 8 and the increment control file was also at 8. It should have incremented to 9 and added, but the error message in the Postgresql error log was that the unique id 8 already existed. I could see no reason why it would insist on that number. To fix the problem, I added a dummy record to that table manually and then deleted it. Adding the template from within Joomla worked with the new id being 10. 

Written by Leonard Rogers on Friday, July 5, 2013 | Comments (0)

    Email address is not published
    Remember Me

    CAPTCHA Reload
    Write the characters in the image above