Column 'file_date' cannot be null, etc. - [Fixed in 4.0.12]

[jazzbird]

 - Joomla Version: 4.2.6
  - Tested jDownloads Version: 4.0.10
  - PHP Version: 8.0
  - Use you the standard backend template? YES
  - It is a new Joomla 4 installation? NO

Hi,

After multiple installs and uninstalls, deleting jD and its database and starting over with 4.0.10, we are still battling the same issues on our attempts to get jD working:

Save failed with the following error: Column 'file_date' cannot be null
Save failed with the following error: Column 'publish_down' cannot be null

It's clear from the DB table that all these values are set at 0000-00-00.

It's possible to work around this by manually relinking every file, and entering a value in every 'Finish Publishing' for each download. This is very slow and tedious. And this field is supposed to be optional. I don't want a date to finish publishing the download. So why can this value not be null?

I just checked on my old (J3.9) website, and these values for file_date and publish_down are all 0000-00-00 and this is not causing any issues. So why is jDownloads 4.0 still not working when those values are null?

[ColinM]

Hi
One of the significant database changes in Joola4 is the the default value for any datetime database entry is NULL
the Joomla 3 default was  0000-00-00 00:00:00

you could look at a Joomla article to see date-time databse settings there
Colin

[jazzbird]

Thanks for the tip! I went into the DB and changed all the date fields from 0000-00-00 00:00:00 to NULL (also setting NULL as default). It seems that this worked: the links now appear correctly, and I can also save the downloads without any error messages.

[Arno]

Hi,

It's clear from the DB table that all these values are set at 0000-00-00.

The update should actually correct this.

Since that obviously didn't work for you, could you please check the following:
- does the _jdownloads_files table have 60 data fields?
- is the field featured in position 56?
- does the table _jdownloads_usergroups_limits have 118 data fields?

Thank you.

[jazzbird]

As I mentioned in the other thread, the answer is YES to all three questions. I have no idea why the update didn't fix it, but changing the value of those date fields to NULL through phpMyAdmin worked. Just hoping that there aren't any other DB problems that will resurface later. For now, everything is finally working!

[Arno]

Thanks for the info!

Maybe I should add a special database routine that checks all tables for correct structures. I'll see how much extra work that would be. But then you could report such things in advance and cancel the update.

A last question:
Is the field 'preview' maybe at the end of the table _jdownloads_templates?

[jazzbird]

No, that table has 22 fields and the last one is checked_out_time. Which by the way has a default value of 0000-00-00 00:00:00. Could this cause problems, similar to the publish_down field in the other table?

[Arno]

Hi,
yes, that is the reason why your tables were not converted correctly.
The table structure does not correspond to what is expected and standard. I have already discovered a similar problem with another user. See also here:

In the meantime, I have extended the update script so that the structure of the tables is checked first. If this is unusual, the update is aborted.

The cause of this unexpected table structure may be due to a skipped update, but I am still looking into this.

In any case, these problems unfortunately delay the release of the first official beta. Because these problems have to be solved first.

And in their case it will be necessary to repeat the update completely. But then with a revised update routine that can take this different table structure into account.

It would be helpful if you could send me a backup of your jDownloads tables (i.e. the state of version 3.9.8.6 before the update).
Would this be possible?

[jazzbird]

Sure. Here you go. I'm attaching both the tables from my old J3 site, and the current tables with my J4 site (jD 4.0.10).

That is bad news. I spent all of last night creating new categories and downloads. I have a project that has been on hold for months now because of waiting for a functional jDownloads 4! My developer and I have already reverted to the old J3 site multiple times, redoing the same work over and over. Is there no way to update or manually fix the current table configuration without going back to 3.9? jDownloads actually seemed to work pretty well last night after I changed the default values as I told you.

[Arno]

Okay. But you know that pre beta releases can be very buggy.

I will see what we can do.
I think it is basicly possible with some special queries.
But I need time to check what all needs to be done in this case. I will try to check this tonight.

[jazzbird]

Thank you! Look forward to the developments. 

[Arno]

Hi,
I have now ready a new version but this will not help in your case, as you have to much new data added and cannot go back to the old 3.9.state.

So you must try to fix all wrong defined data fields manually.
Would it be possible to get a jDownloads table backup from the current situation? 
Then I could try to repair it here. This would be much easier as to describe all the steps there you need. 

A download link via PM would be perfect.

[jazzbird]

Sure, I will send you via PM. Thank you! I appreciate it.