SI 2011 can not see the invoices

If you are running SI 2011, you should try to update to master_2019.2. I should work with your level of PHP. The update is pretty straight forward.

1. Backup your database
2. Copy your SI in to another backup directory.
3. Delete all the sub-directories in your SI directory.
4. Unzip the master_2019.2 zip file content into your empty SI directory
5. Copy the config/custom.config.php file from your copy of the old SI into the config directory of your existing SI directory. If you don’t have a custom.config.php, copy the config/config.php after renaming it to custom.config.php
6. Run SI. I should show you that there are a number of updates that need to be applied. Do this.

You might be stopped at the update that adds foreign key support if you have orphaned records in your database. If so, look at the instructions in the error message and see if you can resolve it. If not, get back to me.

For SI master_2019.2, you need to be on PHP 7.2 at a minimum. PHP 7.4 preferably and 7.4 required for SI master_2020. Any reason you can’t do this?

Check the content of your si_index table. There is probably only one record in it but could be more. Make sure each record contains a numeric 0 in the sub_node_2 column. It is probably blank but should be 0.

The error you are getting is:

[14-Apr-2021 14:02:04 Europe/Paris] SqlPatchManager::runSqlPatch() - PdoDb - query(): Execute error. See error_log.
[16-Apr-2021 17:05:34 Europe/Paris] PdoDb - debugger(): ALTER TABLE <code>si_invoices</code>
                        ADD UNIQUE INDEX <code>UniqDIB</code> (<code>index_id</code>, <code>preference_id</code>, <code>biller_id</code>, <code>domain_id</code>),
                        ADD INDEX <code>IdxDI</code> (<code>index_id</code>, <code>preference_id</code>, <code>domain_id</code>);
[16-Apr-2021 17:05:34 Europe/Paris] PdoDb - query(): Execute error.Array
(
    [0] => 42000
    [1] => 1067
    [2] => Invalid default value for 'date'
)

If you look at the si_invoices structure, you should see that the date column is set to NULL = NO and Default of 0000-00-00 00:00:00. You need to make sure that zero dates are allowed in your database. Do this by commenting out the “sql_mode=” in the my.ini file.

For reference, make the current setting for this value into a comment by adding a # at the front of the line. An example of what this line might be set to is, sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION.

After you make this change, restart your mysql server and access SI again.

• This reply was modified 3 years, 7 months ago by RRowley.