Date field throwing an error #252

New Issue

Closed

opened 2018-03-22 14:45:56 +00:00 by alan123489 · 7 comments

alan123489 commented

2018-03-22 14:45:56 +00:00

(Migrated from github.com)

Steps to reproduce the issue

I've been using the date field
I've mimicked the demo date field but it throws an error not on install but when I try to install the component.

I've tried lots of variations but it just throws an error on installation :(

Expected result

expected it to create a table with the date as a field

Actual result

it throws an error

CREATE TABLE IF NOT EXISTS `#__websitemanager_magazine` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`asset_id` INT(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',
	`date` DATE NOT NULL DEFAULT '',
	`magazine_file` TEXT NOT NULL,

Worth noting that the date field is actually set to null (it wasn't initially)

System information (as much as possible)

OS Name & Version:
MySql Version:
Apache Version:
PHP Version:
Joomla Version:
JCB Version:
Browser:

Additional comments

I had the exact problem with another field and I had to uninstall the component and then it installed no problem.

### Steps to reproduce the issue I've been using the date field I've mimicked the demo date field but it throws an error not on install but when I try to install the component. I've tried lots of variations but it just throws an error on installation :( ### Expected result expected it to create a table with the date as a field ### Actual result it throws an error ``` CREATE TABLE IF NOT EXISTS `#__websitemanager_magazine` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `asset_id` INT(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.', `date` DATE NOT NULL DEFAULT '', `magazine_file` TEXT NOT NULL, ``` Worth noting that the date field is actually set to null (it wasn't initially) ### System information (as much as possible) - OS Name & Version: - MySql Version: - Apache Version: - PHP Version: - Joomla Version: - JCB Version: - Browser: ### Additional comments I had the exact problem with another field and I had to uninstall the component and then it installed no problem.

Llewellyn commented

2018-03-22 14:49:24 +00:00

System information (as much as possible)

OS Name & Version: ???????????????
MySql Version: ???????????????
Apache Version: ???????????????
PHP Version: ???????????????
Joomla Version: ???????????????
JCB Version: ???????????????
Browser: ???????????????

This will help, since I have no idea where you are running this.

# System information (as much as possible) - OS Name & Version: ??????????????? - MySql Version: ??????????????? - Apache Version: ??????????????? - PHP Version: ??????????????? - Joomla Version: ??????????????? - JCB Version: ??????????????? - Browser: ??????????????? This will help, since I have no idea where you are running this.

Llewellyn commented

2018-03-22 15:20:14 +00:00

This is a mysql issue, you will see there has been some discussion about this on the forum.

I am not sure how we can address this, at this time.

This is a mysql issue, you will see there has been some discussion about this on [the forum](https://groups.google.com/a/vdm.io/d/msg/jcb/FrzJYeYL2jw/-gcbWTkAAQAJ). I am not sure how we can address this, at this time.

alan123489 commented

2018-03-22 18:19:23 +00:00

(Migrated from github.com)

OS Name & Version: Windows 10
MySql Version: 5.7
Apache Version:
PHP Version: 7.0.28
Joomla Version: 3.8.5
JCB Version: 2.6.17
Browser: CHrome

sorry I forgot them !

OS Name & Version: Windows 10 MySql Version: 5.7 Apache Version: PHP Version: 7.0.28 Joomla Version: 3.8.5 JCB Version: 2.6.17 Browser: CHrome sorry I forgot them !

alan123489 commented

2018-03-22 18:42:34 +00:00

(Migrated from github.com)

So I removed the view with the date field but the compiler is still trying to add the view(table) is there a way to force the compiler to do a fresh compile?

Llewellyn commented

2018-03-22 19:28:39 +00:00

Okay, so you have not watched all the tutorials... I can point in the direction. But hey you will have to be willing to do the work of watching all the tutorials.

Basically you need to open Component Updates and change the SQL values there, since they get created automatic but are not removed automatic.

You could also uninstall and reinstall the component, the above dynamic generated SQL update scripts will then not run... but it is always good to keep an eye on this area if you change things.

Okay, so you have not watched all the tutorials... I can point in the direction. But hey you will have to be willing to do the work of watching all the tutorials. Basically you need to open **Component Updates** and change the SQL values there, since they get created automatic but are not removed automatic. You could also uninstall and reinstall the component, the above dynamic generated SQL update scripts will then not run... but it is always good to keep an eye on this area if you change things.

alan123489 commented

2018-03-23 08:04:06 +00:00

(Migrated from github.com)

Thank you for pointing me in the right direction. I thought I'd write up how I solved my problem - I hope it helps someone.

First there is a video about component updates - worth watching.

Joomla has a system where it searches for and reads an sql update file on install ( the video explains this). In any component over time many of these files will created, generally one for each new release of the component. Joomla uses version_compare on these files and runs any that haven't already been run.
This is a good thing since it ensures that a component is always up to date. However in development it has an unforeseen (at least by me) consequence.

In my case I created a new date field with NOT NULL and DEFAULT '' - I simply used the defaults in component builder and didn't check - my bad! This however throws an error for mysql since default cannot be empty. So I realised this and deleted the field and went to compile again. But it kept throwing the same error.

This is a consequence of the compare function that Joomla runs. Although I had removed the field and tried to reinstall Joomla was still trying to run the previous sql file. So my sequence went like this:

sql ver 1.0
Install component - all working lots of time saved

Add new view (new sql table) with date field
Compiler creates
sql ver 1.1
this sql attempts to create a table with a date value that doesn't work and component doesn't install

Delete the field
Compiler creates
sql ver1.2
when attempting to install joomla throws an error because it sees sql ver 1.1 hasn't been installed and so tries to install it which of course fails!

The only way to resolve this it to manually adjust the sql ver 1.1 file and add in DEFAULT '0000-00-00'. I did this by adjusting as shown in the video. You can't just delete the field - it doesn't work!

Hope this helps

Thank you for pointing me in the right direction. I thought I'd write up how I solved my problem - I hope it helps someone. First there is a video about component updates - worth watching. Joomla has a system where it searches for and reads an sql update file on install ( the video explains this). In any component over time many of these files will created, generally one for each new release of the component. Joomla uses version_compare on these files and runs any that haven't already been run. This is a good thing since it ensures that a component is always up to date. However in development it has an unforeseen (at least by me) consequence. In my case I created a new date field with NOT NULL and DEFAULT '' - I simply used the defaults in component builder and didn't check - my bad! This however throws an error for mysql since default cannot be empty. So I realised this and deleted the field and went to compile again. But it kept throwing the same error. This is a consequence of the compare function that Joomla runs. Although I had removed the field and tried to reinstall Joomla was still trying to run the previous sql file. So my sequence went like this: sql ver 1.0 Install component - all working lots of time saved Add new view (new sql table) with date field Compiler creates sql ver 1.1 this sql attempts to create a table with a date value that doesn't work and component doesn't install Delete the field Compiler creates sql ver1.2 when attempting to install joomla throws an error because it sees sql ver 1.1 hasn't been installed and so tries to install it which of course fails! The only way to resolve this it to manually adjust the sql ver 1.1 file and add in DEFAULT '0000-00-00'. I did this by adjusting as shown in the video. You can't just delete the field - it doesn't work! Hope this helps

Llewellyn commented

2018-03-23 13:19:56 +00:00

Great, thanks for the feedback 👍

Great, thanks for the feedback :+1: