Solved One Problem (Fabrik): Maintenance: Database

[aijosh]

Hello,

I upgraded J!3/Fabrik3 to J!4/Fabrik4.

I'm getting this error message on the Maintenance: Database page.

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 ''#__fabrik_ratings WHERE field = 'listid' AND UPPER(type) REGEXP '^INT([(][0-...' at line 1

Table 'site.pre_fabrik_packages' doesn't exist

How do I fix issue

 

[troester]

I upgraded J!3/Fabrik3 to J!4/Fabrik4.

How?
https://fabrikar.com/forums/index.php?wiki/upgrade-instructions/

 

[aijosh]

Yes. I followed it step by step.

 

[troester]

Which exact J! version , PHP version are you on, which were the old ones?
Which exact Fabrik package did you install, from which old one?
What is Fabrik/Home showing?

Do you get the error when clicking on Maintenace/Database or when trying to "Update structure" on Fabrik?

 

[aijosh]

Old = Joomla! 3.10.12
New = Joomla! 4.4.3

Old PHP = 7.4
New PHP = 8.1

Old Fabrik = 3.10
New Fabrik = 4.0

Fabrik/Home displays ok

Version: 4.0 (GitDate: 2024/01/3)​

February 2024
GNU General Public License version 3 or later; see software_license.txt
Copyright (C) 2005-2024 Fabrikar - All rights reserved.

​

==
Error is displayed as soon as I visit: administrator/index.php?option=com_installer&view=database
Same error on page after "Update Structure" with Fabrik still having a red "One Problem"

Placing mouse over "One Problem" tag displays the info below

One Problem

• Table '\'et4cy_fabrik_ratings' has the wrong type or attributes for column 'listid' with type int. (From file 3.0.1.sql.)
• Table 'et4cy_fabrik_packages' does not have index 'package_key'. (From file 3.0.5.sql.)
• 44 database changes were checked.
• 177 database changes did not alter table structure and were skipped.

 

[troester]

Do you have any old files (beside 4.0-2023-05-27.sql) in administrator\components\com_fabrik\sql\updates\mysql

Do you get a tooltip when hovering over the red "One Problem"?

 

[aijosh]

yes.

	Name	Size	Last Modified	Type	Permissions
	3.0.1.sql	153 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.0.4.sql	91 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.0.5.sql	219 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.0.6.sql	864 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.4.sql	921 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.5.sql	86 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.6.1.sql	129 bytes	Sep 7, 2021, 5:33 PM	text/x-sql	0644
	3.7.sql

Tooltip message
One Problem

• Table '\'et4cy_fabrik_ratings' has the wrong type or attributes for column 'listid' with type int. (From file 3.0.1.sql.)
• Table 'et4cy_fabrik_packages' does not have index 'package_key'. (From file 3.0.5.sql.)
• 44 database changes were checked.
• 177 database changes did not alter table structure and were skipped.

 

[troester]

Ok, can you delete all these files? They should not be there in F4.

I thought these files would be removed during installation. Did you see any error message during the Fabrik4.0 package installation?

 

[aijosh]

Ok. Error message is gone now.

Does this apply to other sites?

I have a few of them and I'm noticing same pattern albeit with slightly varying error message.

WEBSITE B
One Problem

• Table '\'las0i_fabrik_ratings' has the wrong type or attributes for column 'listid' with type int. (From file 3.0.1.sql.)
• 4 database changes were checked.
• 27 database changes did not alter table structure and were skipped.

WEBSITE C
One Problem

• Table 'd7e3v_fabrik_cron' has the wrong type or attributes for column 'published' with type int. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_cron' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_elements' has the wrong type or attributes for column 'show_in_list_summary' with type int. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_elements' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_forms' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_groups' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_joins' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_jsactions' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_lists' has the wrong type or attributes for column 'params' with type TEXT. (From file 2.x-3.0.sql.)
• Table 'd7e3v_fabrik_packages' has the wrong type or attributes for column 'published' with type int. (From file 2.x-3.0.sql.)

 

[aijosh]

Did you see any error message during the Fabrik4.0 package installation?

No errors during installation

 

[troester]

Yes, these errors are coming from the outdated update-sql files.
So please delete them manually.

I'll add a hint to check for old sql-files to the upgrade WIKI.
Maybe there's some issue with file delete access during the installation in some server setups.

Can you check if your folder
libraries\fabrik\libs
contains only a libs folder + libs.xml
(so getid3 etc are only in the 2nd libs folder)

Because the liberary structure has changed, too, and the old files and folders should be deleted.

 

[aijosh]

	Name	Size	Last Modified	Type	Permissions

	Name	Size	Last Modified	Type	Permissions
	libs	4 KB	Yesterday, 7:28 PM	httpd/unix-directory	0755
	libs.xml	596 bytes	Yesterday, 7:28 PM	text/x-generic	0644

Thats all I have

 

[troester]

So this looks ok.