Database syntax error when upgrading to 4.0.2

Need help upgrading your Joomla! website or converting to Joomla! 4.x?

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Database syntax error when upgrading to 4.0.2

Post by northbra » Sat Sep 11, 2021 7:19 am

I have also followed the upgrade process, and I get the following errors / warnings.
500 Unknown column 'fp.featured_up' in 'field list'
JInstaller: :Install: Error SQL Duplicate entry '1' for key 'PRIMARY'
Table 'v4test.aeuxr_history' doesn't exist j4
Last edited by toivo on Sat Sep 11, 2021 8:47 am, edited 1 time in total.
Reason: mod note: split from someone else's topic - please create your own topics in the future! - merged with the current topic, please note that cross posting is not allowed

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Database syntax error when upgrading to 4.0.2

Post by northbra » Sat Sep 11, 2021 8:18 am

I have upgraded to 4.0.2, but unable to repair the database, get this message:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ADD AND `null` = 'NO'' at line 1.
How do I repair?
Last edited by toivo on Sat Sep 11, 2021 8:33 am, edited 1 time in total.
Reason: mod note: split from someone else's topic - please create your own topics in the future!

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 10:56 am

northbra wrote:
Sat Sep 11, 2021 8:18 am
I have upgraded to 4.0.2, but unable to repair the database, get this message:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ADD AND `null` = 'NO'' at line 1.
How do I repair?
The CMS core doesn't provide any update SQL script having such a statement 'ADD AND `null` = 'NO'', and so it must belong to a 3rd party extension.

In Joomla 4, the database checker doesn't check only the core but also 3rd party extensions, and so you have a list of what has been checked, the CMS core and the extensions where update SQL scripts were found which could be checked. For which extension are database errors shown which you have tried to fix? If there is an update available for that extension, try to update it and check again. If there is no update or still the same error after the update, check the update SQL scripts of that extension (where to find them is defined in the manifest XML of that extension) which script contains that "ADD AND" thing and fix the syntax error.

And what means you have updgraded to 4.0.2 and then unable to repair the database? Have you upgraded with use of the Joomla update component, which is the only valid method? Or have you only copied the files or unzipped the package and then just used the "Fix" button? This method is not valid anymore since Joomla 3.5.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Sun Sep 12, 2021 11:56 am

I updated via the joomla update, but the repair database option was not available as nothing was highlighted.
I will look at the extensions again.

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 12:18 pm

northbra wrote:
Sun Sep 12, 2021 11:56 am
I updated via the joomla update, but the repair database option was not available as nothing was highlighted.
I will look at the extensions again.
On a command line (CMD on Windows or bash on Linux), change directory to the Joomla root of your installation and then search for files with name "*.sql" and content "ADD AND".

On Windows:

Code: Select all

findstr /s /c:"ADD AND" *.sql
On Linux:

Code: Select all

find ./ -type f -name "*\.sql" -exec grep -Hn "ADD AND" {} \;
Then you might find the culprit update SQL of an extension which causes the database checker failing to load.
Last edited by Richard67 on Sun Sep 12, 2021 12:58 pm, edited 1 time in total.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Sun Sep 12, 2021 12:46 pm

Didnt find any files manifest.xml?

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 1:00 pm

northbra wrote:
Sun Sep 12, 2021 12:46 pm
Didnt find any files manifest.xml?
I don't know what you mean. My above suggestion (I have just corrected a typo in the version for Linux, was a dot missing) was for searching update SQL scripts which might still be present somewhere for an extension. I have not said anything about searching manifest files.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Sun Sep 12, 2021 1:07 pm

Apologies, you said manifest xml.

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 1:10 pm

northbra wrote:
Sun Sep 12, 2021 1:07 pm
Apologies, you said manifest xml.
That was the kind of file where information can be found about update SQL scripts of an extension. It is not necessarily called manifext.xml but is located in the administrator part of that extension and named like the extension, e.g. patchtester.xml, osmap.xml, ...

But if you use my commands above to find any SQL script containing the questionable SQL statement then you don't need to look for the manifest XML files of all your 3rd party extensions.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Sun Sep 12, 2021 3:00 pm

Thanks, have deleted all problem extensions, and upgraded, and was able to repair the database. When I open an existing article and try to close it following error message appears: 1048 Column 'checked_out' cannot be null

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 3:34 pm

northbra wrote:
Sun Sep 12, 2021 3:00 pm
Thanks, have deleted all problem extensions, and upgraded, and was able to repair the database. When I open an existing article and try to close it following error message appears: 1048 Column 'checked_out' cannot be null
Then something went wrong with your update because the checked_out column allows null values in Joomla 4.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Sun Sep 12, 2021 4:32 pm

Ok, not sure what to do now, as I used the joomla update.

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 4:37 pm

northbra wrote:
Sun Sep 12, 2021 4:32 pm
Ok, not sure what to do now, as I used the joomla update.
Does that mean you used the Joomla Update component to install the update (right), or does it mean you just unzipped the update package (wrong)?

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Sun Sep 12, 2021 4:49 pm

Used the joomla update package

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Sun Sep 12, 2021 4:59 pm

northbra wrote:
Sun Sep 12, 2021 4:49 pm
Used the joomla update package
Again: How did you use that package? Just unpacked it and then nothing else? That is wrong since Joomla 3.5. You have to use the Joomla Update Component's Upload & Install to install the update package.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Mon Sep 13, 2021 6:39 am

I used Joomla update, and then live update. I will try upload and install

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Mon Sep 13, 2021 6:41 am

northbra wrote:
Mon Sep 13, 2021 6:39 am
I used Joomla update, and then live update. I will try upload and install
Live update is also ok. I only focused on the Upload & Update because that's what has to be used with previously downloaded packages, and I understood that you had used such a package.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Mon Sep 13, 2021 6:53 am

Just looked at my joomla 3 site. Should the database also upgrade to 3.10.1?
Database schema version (in #__schemas): 3.10.0-2021-05-28.
Update version (in #__extensions): 3.10.1.

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Mon Sep 13, 2021 6:57 am

northbra wrote:
Mon Sep 13, 2021 6:53 am
Just looked at my joomla 3 site. Should the database also upgrade to 3.10.1?
Database schema version (in #__schemas): 3.10.0-2021-05-28.
Update version (in #__extensions): 3.10.1.
No, the database schema version "3.10.0-2021-05-28" is fine. With 3.10.1 there was no SQL update script added.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 28090
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: Database syntax error when upgrading to 4.0.2

Post by Per Yngve Berg » Mon Sep 13, 2021 7:26 am

Can you post your joomla_update.php log file?

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Mon Sep 13, 2021 3:46 pm

File attached
You do not have the required permissions to view the files attached to this post.

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Mon Sep 13, 2021 6:05 pm

The log shows first an update to 4.0.2 which has not been complete because the last SQL script which was run was 4.0.0-2019-07-13.sql, so 4.0.0-2019-09-13.sql and later scripts have not bee run for whatever reason. It would dig deeper to find that out.

But after that you have "updated" this partly updated 4.0.2 to 3.10.1, i.e. you tried to downgrade, using the Upload & Update of the Joomla update component and the Joomla_3.10.1-Stable-Update_Package.zip .

Sorry, but this is not really working. Joomla NEVER has supported a downgrade. Worst case now is you have a mix of the files from 3.10.1. and 4.0.2 and a database which is also a mix because of the broken update before.

You should have a backup from before you have tried the update from 3 to 4. Backup of both the database and the file system. Please restore that backup using an empty database and having the target folder = the Joomla root empty, i.e. all remainders from previous trials removed.

That's all I can suggest now.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 28090
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: Database syntax error when upgrading to 4.0.2

Post by Per Yngve Berg » Mon Sep 13, 2021 7:01 pm

Your update stops at 4.0.0-2019-07-13.
Mine run all the way to 4.0.0-2021-08-17

And the strange thing is that after 4.0.2, an update to 3.10.1 is run.

Try to crank up the max_execution_time to 300 on the server. That may let the update run to it's finished.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Mon Sep 13, 2021 8:13 pm

Will work on that and let you know outcome. Will take a few days. Thanks.

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Tue Sep 14, 2021 8:00 am

Am I ok in using Prostar as my default template in Joomla 3.10.1. The one my site was using is not compatible with Joomla 4.
I understand that Prostar is not compatible either, so what template do I use to migrate?

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 23353
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, Netherlands
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by pe7er » Tue Sep 14, 2021 8:02 am

northbra wrote:
Tue Sep 14, 2021 8:00 am
Am I ok in using Prostar as my default template in Joomla 3.10.1. The one my site was using is not compatible with Joomla 4.
I understand that Prostar is not compatible either, so what template do I use to migrate?
Joomla's default template is Cassiopeia. Maybe you could use that one?
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
The best website: https://the-best-website.com

northbra
Joomla! Apprentice
Joomla! Apprentice
Posts: 25
Joined: Thu Apr 16, 2015 4:52 pm

Re: Database syntax error when upgrading to 4.0.2

Post by northbra » Tue Sep 14, 2021 8:09 am

So how do I migrate, Cassiopeia is for joomla 4. What do I need as my default template in 3.10.1 to run JUpdate.

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 23353
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, Netherlands
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by pe7er » Tue Sep 14, 2021 11:04 am

northbra wrote:
Tue Sep 14, 2021 8:09 am
So how do I migrate, Cassiopeia is for joomla 4. What do I need as my default template in 3.10.1 to run JUpdate.
After the update, in the administrator back-end go to System > Templates and select Cassiopeia.
Backup (files + database) before doing so.
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
The best website: https://the-best-website.com

Richard67
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 236
Joined: Fri Sep 16, 2011 6:13 pm
Location: Germany
Contact:

Re: Database syntax error when upgrading to 4.0.2

Post by Richard67 » Tue Sep 14, 2021 4:56 pm

Before the update, switch the admin and site template to the defaults, i.e. Isis for admin and Protostar for the site. Then (and only then) during the update to J4, it will switch templates to Atum for admin and Cassiopeia for the site.


Post Reply

Return to “Migrating and Upgrading to Joomla! 4.x”