[RRowley]

Make sure you change the local.locale setting in the custom.config.php from en_US to your locale (ex: en_GB). This should make your currency symbol show correctly.

[RRowley]

I suspect it is an issue that evolved for the dataset settings over time. Can’t see what table structure was originally but at some point field altered to be INT(11) and nullable. Recommend that you check the table structure to verify that it allows NULL and then change all 0 values to NULL.

The new master_2019.2.1_beta which implements foreign keys will eliminate this problem by tying the tax id fields to the id field in the si_tax table.

[RRowley]

The essential data prompt occurs when a database exists and can be accessed but there is no data in the si_sql_patchmanager table. Check what you are importing and make sure the database tables are populated with data.

I recommend dropping all tables and then importing your backup.

[RRowley]

You need to set up a custom invoice template. Refer to the “Invoice Templates & Styles” section on the menu to the left. Note that the menu link, “System Preferences,” has been renamed to “SI Defaults” to allow room for new menu options. I’ll update the web page to reflect this shortly.

[RRowley]

Yes, having the create table statements is what the problem is. I thought that I had specified that you should not include the structure but in reviewing step 2 of the instructions, I didn’t spell this out. Sorry about that. The instructions and database structure I provided are for the master_2019.2.1_beta version and at this point, I would not use them per the following info.

At this point if you are up on master_2019 (version 2019.1.1) and want to get to the most current master_2019.2.1_beta version (version 2019.2.4), load the updated SI software and run it. In theory, it should update the database and run smoothly, especially with fixes I implemented per your previous load attempts.

[RRowley]

Which step is this occurring on? The import of structure.sql, or the import of your exported data?

I just tested the import of structure.sql without an issue, so don’t understand if that is issue.

If in the import of exported data, make sure that there is no logic to create tables. There should only be INSERT statements in that file. If it has CREATE and ALTER statements, delete them all and keep only the statements that will import data. Also when importing make sure to UNCHECK the “Enable foreign key checks” option prior to executing the import.

[RRowley]

You need to follow the steps I outlined exactly. This error tells me that you did not DROP the tables per 3c.

[RRowley]

There are a number of settings in your database structure that do not reflect changes that the patch manager should have implemented. I think this all stems from the problems you initially had. In any case, I’d like you to do the following with your database.

1. Download the “tsp_fixes.zip” file I’ve attached and extract the two files in it. You can keep these in the download directory. The two files in this archive are structure.sql and si_sql_patchmanager.sql.
2. Export all data except the content of the si_sql_patchmanager table.
3. Delete all tables from the database by doing the following:
   • In phpMyAdmin select the database name (tsp_invoices) to display all its tables.
   • Check the “Check all” box at the bottom of the list of tables.
   • Click on the “With selected” drop down list adjacent to the “Check all” option and select the “Drop” option in the “Delete data or table” category.
   • Uncheck the “Enable foreign key checks” box and then click the “Yes” button to delete all the tables and data.
   • Import the “structure.sql” file extracted in step 1. This will create all the tables with the proper structure.
   • Import your database content extracted in step 2. Be sure to uncheck the “Enable foreign key checks” in the “Other options” section of the Import screen.
   • Import the si_sql_patchmanager.sql file extracted in step 1.

At this point, your database should be properly structured with your data in it. Access SI now to verify it is working properly. Let me know what happens and I’ll keep my fingers crossed for you.

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

Attachments:

1. tsp_fixes.zip

[RRowley]

I figured out the issue. The product_id field in si_expense is unsigned and the id in the si_products is not. This characteristic difference exists on other fields that will be set as foreign keys. So I’ll have to figure out why this is (thought previous patches fixed this) and what should be done to fix it.

[RRowley]

No. I wanted an export of all database tables. There were only two tables in what you provided.

[RRowley]

Would you please export the database structure and attach it to a response to this message? I need the structure, not the data.

[RRowley]

Yes. Select the database in phpMyAdmin then select the SQL tab. Copy and paste the commands, one at a time, into the command box and execute it. When you encounter one that fails, record the issue and the command and report that. Thanks…

[RRowley]

The error for patch #318 foreign keys needs to be resolved. Any more information on this? The following are the commands that comprise this patch. Try applying them one at a time in the given order and see where it fails.

ALTER TABLE si_cron ADD FOREIGN KEY (invoice_id) REFERENCES si_invoices (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_cron_log ADD FOREIGN KEY (cron_id) REFERENCES si_cron (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense ADD FOREIGN KEY (biller_id) REFERENCES si_biller (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense ADD FOREIGN KEY (customer_id) REFERENCES si_customers (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense ADD FOREIGN KEY (invoice_id) REFERENCES si_invoices (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense ADD FOREIGN KEY (product_id) REFERENCES si_products (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense ADD FOREIGN KEY (expense_account_id) REFERENCES si_expense_account (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense_item_tax ADD FOREIGN KEY (expense_id) REFERENCES si_expense (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_expense_item_tax ADD FOREIGN KEY (tax_id) REFERENCES si_tax (tax_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_inventory ADD FOREIGN KEY (product_id) REFERENCES si_products (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoices ADD FOREIGN KEY (biller_id) REFERENCES si_biller (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoices ADD FOREIGN KEY (customer_id) REFERENCES si_customers (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoices ADD FOREIGN KEY (type_id) REFERENCES si_invoice_type (inv_ty_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoices ADD FOREIGN KEY (preference_id) REFERENCES si_preferences (pref_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoice_items ADD FOREIGN KEY (invoice_id) REFERENCES si_invoices (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoice_items ADD FOREIGN KEY (product_id) REFERENCES si_products (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoice_item_tax ADD FOREIGN KEY (tax_id) REFERENCES si_tax (tax_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_invoice_item_attachments ADD FOREIGN KEY (invoice_item_id) REFERENCES si_invoice_items (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_log ADD FOREIGN KEY (user_id) REFERENCES si_user (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_payment ADD FOREIGN KEY (ac_inv_id) REFERENCES si_invoices (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_payment ADD FOREIGN KEY (ac_payment_type) REFERENCES si_payment_types (pt_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_products ADD FOREIGN KEY (default_tax_id) REFERENCES si_tax (tax_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_products ADD FOREIGN KEY (default_tax_id_2) REFERENCES si_tax (tax_id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_products_attributes ADD FOREIGN KEY (type_id) REFERENCES si_products_attribute_type (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_products_values ADD FOREIGN KEY (attribute_id) REFERENCES si_products_attributes (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_user ADD FOREIGN KEY (domain_id) REFERENCES si_user_domain (id) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE si_user ADD FOREIGN KEY (role_id) REFERENCES si_user_role (id) ON UPDATE CASCADE ON DELETE RESTRICT;

[RRowley]

Haven’t heard back from you. Did the change I provided solve your problem?

[RRowley]

This is an error from the library the generates the table that list things like invoices, products, customers, etc. I believe the issue is related to your implementation not having completed the patch process. I responded to your other request earlier today with a zip file containing an sql file to import to your database. This will create a table you are missing and hopefully, allow the patch process to complete. Then see if this is resolved.

[Author]

Posts