Forum

Ask, reply and learn. Join the community of Akaunting.

New Discussion

Database Structure for created_from & created_by

Scott Carson   ( User )

Commented 2 years ago

Hi,

Could somebody please look at the database of their .27+ installation and tell me the structure for the fields created_from and created_by? I upgraded from .23 to .27 (skipping .25 because it was full of bugs) and these new fields were created as part of .25 but then assumed to be present for .27 and now I'm getting hammered with errors every time I save saying, for example SQLSTATE column not found 'created_from' in wj9_taxes
I've hacked on the database and made a created_from column as varchar(191) and create_by as int(10) but I'm just guessing.

Thanks!
-Scott.

Scott Carson   ( User )

Commented 2 years ago

An example of the exact error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created_from' in 'field list' (SQL: insert into `wj9_taxes` (`name`, `rate`, `enabled`, `type`, `company_id`, `created_by`, `created_from`, `updated_at`, `created_at`) values (Ontario HST, 13, 1, normal, 1, 1, core::ui, 2021-12-22 14:51:54, 2021-12-22 14:51:54))

Scott Carson   ( User )

Commented 2 years ago

Sigh ...

And there's more ... an error upon saving a customer that the columns city, state, zip_code, and country are missing from the wj9_contacts table. I took a guess and added them after the address column as follows:

city - varchar(191) - default NULL
state - varchar(191) - default NULL
zip_code - varchar(10) - default NULL
country - varchar(191) - default NULL

... but I have no clue if varchar or the length is correct.

My Afox   ( User )

Commented 2 years ago

I am not a Laravel Developer but I believe there must be some kind of Migrations in latest release for previous one to update tables with new fields.

However, to overcome the above mentioned errors, I made some table comparisons between old and latest table definitions, found differences and applied them to my database. It's working perfect now.

Pasting here for you, may help someone in future. My unique table identifier was 81o, you must change it according to yours.

ALTER TABLE 81o_accounts ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_categories ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_companies ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_contacts ADD COLUMN city VARCHAR(191) AFTER address;
ALTER TABLE 81o_contacts ADD COLUMN zip_code VARCHAR(191) AFTER city;
ALTER TABLE 81o_contacts ADD COLUMN state VARCHAR(191) AFTER zip_code;
ALTER TABLE 81o_contacts ADD COLUMN country VARCHAR(191) AFTER state;
ALTER TABLE 81o_contacts ADD COLUMN created_from VARCHAR(100) AFTER reference;

ALTER TABLE 81o_currencies ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_dashboards ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_document_histories ADD COLUMN created_from VARCHAR(100) AFTER description;
ALTER TABLE 81o_document_histories ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_document_item_taxes ADD COLUMN created_from VARCHAR(100) AFTER amount;
ALTER TABLE 81o_document_item_taxes ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_document_items ADD COLUMN created_from VARCHAR(100) AFTER total;
ALTER TABLE 81o_document_items ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_document_totals ADD COLUMN created_from VARCHAR(100) AFTER sort_order;
ALTER TABLE 81o_document_totals ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_documents ADD COLUMN contact_city VARCHAR(191) AFTER contact_address;
ALTER TABLE 81o_documents ADD COLUMN contact_zip_code VARCHAR(191) AFTER contact_city;
ALTER TABLE 81o_documents ADD COLUMN contact_state VARCHAR(191) AFTER contact_zip_code;
ALTER TABLE 81o_documents ADD COLUMN contact_country VARCHAR(191) AFTER contact_state;
ALTER TABLE 81o_documents ADD COLUMN created_from VARCHAR(100) AFTER parent_id;

ALTER TABLE 81o_email_templates ADD COLUMN created_from VARCHAR(100) AFTER params;
ALTER TABLE 81o_email_templates ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_item_taxes ADD COLUMN created_from VARCHAR(100) AFTER tax_id;
ALTER TABLE 81o_item_taxes ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_items ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_media ADD COLUMN created_from VARCHAR(100) AFTER original_media_id;
ALTER TABLE 81o_media ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_mediables ADD COLUMN created_from VARCHAR(100) AFTER `order`;
ALTER TABLE 81o_mediables ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_module_histories ADD COLUMN created_from VARCHAR(100) AFTER description;
ALTER TABLE 81o_module_histories ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_modules ADD COLUMN created_from VARCHAR(100) AFTER enabled;
ALTER TABLE 81o_modules ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_reconciliations ADD COLUMN created_from VARCHAR(100) AFTER reconciled;

ALTER TABLE 81o_recurring ADD COLUMN created_from VARCHAR(100) AFTER count;
ALTER TABLE 81o_recurring ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_reports ADD COLUMN created_from VARCHAR(100) AFTER settings;

ALTER TABLE 81o_roles ADD COLUMN created_from VARCHAR(100) AFTER description;
ALTER TABLE 81o_roles ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_settings ADD COLUMN deleted_at timestamp AFTER value;

ALTER TABLE 81o_taxes ADD COLUMN created_from VARCHAR(100) AFTER enabled;

ALTER TABLE 81o_transactions ADD COLUMN created_from VARCHAR(100) AFTER parent_id;

ALTER TABLE 81o_transfers ADD COLUMN created_from VARCHAR(100) AFTER income_transaction_id;

ALTER TABLE 81o_user_companies drop COLUMN user_type;

ALTER TABLE 81o_user_dashboards drop COLUMN user_type;

ALTER TABLE 81o_users ADD COLUMN created_from VARCHAR(100) AFTER enabled;
ALTER TABLE 81o_users ADD COLUMN created_by VARCHAR(30) AFTER created_from;

ALTER TABLE 81o_widgets ADD COLUMN created_from VARCHAR(100) AFTER settings;

Scott Carson   ( User )

Commented 1 year ago

YES, THANK YOU SO MUCH!!!!

Raenstorm   ( User )

Commented 1 year ago

I know this is an old thread but I'm getting these same types of errors not finding the title in my documents table etc...is the above ALTER TABLE list current? Can someone post with all the current tables required for 3.0.17??

Scott Carson   ( User )

Commented 1 year ago

If this thread can help someone else, I'm smitten. My installation was deemed by akaunting support to be so corrupt that I had to install new and lost months of data. In fact, going forward, I've decided that I'm going to install akaunting every year to save my data. Anyway, here's what I *think* is the cleanest list - my prefix is 1y1, yours will be different.

1y1_accounts
1y1_categories
1y1_companies
1y1_contacts
1y1_credits_transactions
1y1_credit_debit_notes_credits_transactions
1y1_credit_debit_notes_credit_note_details
1y1_credit_debit_notes_debit_note_details
1y1_credit_notes
1y1_credit_note_histories
1y1_credit_note_items
1y1_credit_note_item_taxes
1y1_credit_note_totals
1y1_currencies
1y1_custom_fields_fields
1y1_custom_fields_field_type_options
1y1_custom_fields_field_values
1y1_dashboards
1y1_debit_notes
1y1_debit_note_histories
1y1_debit_note_items
1y1_debit_note_item_taxes
1y1_debit_note_totals
1y1_documents
1y1_document_histories
1y1_document_items
1y1_document_item_taxes
1y1_document_totals
1y1_double_entry_accounts
1y1_double_entry_account_bank
1y1_double_entry_account_item
1y1_double_entry_account_tax
1y1_double_entry_classes
1y1_double_entry_journals
1y1_double_entry_ledger
1y1_double_entry_types
1y1_email_templates
1y1_employees_departments
1y1_employees_employees
1y1_estimates
1y1_estimates_documents
1y1_estimates_extra_parameters
1y1_estimate_histories
1y1_estimate_items
1y1_estimate_item_taxes
1y1_estimate_totals
1y1_failed_jobs
1y1_firewall_ips
1y1_firewall_logs
1y1_items
1y1_item_taxes
1y1_jobs
1y1_media
1y1_mediables
1y1_migrations
1y1_modules
1y1_module_histories
1y1_notifications
1y1_password_resets
1y1_payroll_employees
1y1_payroll_employee_benefits
1y1_payroll_employee_deductions
1y1_payroll_pay_calendars
1y1_payroll_pay_calendar_employees
1y1_payroll_positions
1y1_payroll_run_payrolls
1y1_payroll_run_payroll_employees
1y1_payroll_run_payroll_employee_benefits
1y1_payroll_run_payroll_employee_deductions
1y1_payroll_setting_pay_items
1y1_permissions
1y1_personal_access_tokens
1y1_reconciliations
1y1_recurring
1y1_reports
1y1_roles
1y1_role_permissions
1y1_sessions
1y1_settings
1y1_taxes
1y1_transactions
1y1_transfers
1y1_users
1y1_user_companies
1y1_user_dashboards
1y1_user_invitations
1y1_user_permissions
1y1_user_roles
1y1_widgets

Raenstorm   ( User )

Commented 1 year ago

Awesome thank you!

Please login or register to leave a response.

Showing 1 to 8 of 8 discussions