[Fuquar7]

I was having so many problems, I ultimately setup a new database. I imported all my customers and products into the new database and wrote a perl script to extract each invoice from the SQL database, then the script used the web interface to one by one enter all the invoices and payments into the new database.

It was a crude way to do it, but it was the simplest way I could figure to maintain data integrity and still have all my records. (only dealing with 400 invoices, 1500 line items and 450 payments or so)

The last thing I did was change the start index number for any new invoices so I could quickly tell if I was dealing with something from the old version/database versus new just in case their is a discrepancy.

[Fuquar7]

My test data

Attachments:

1. Fresh-data-dbs11545668.zip

[Fuquar7]

So after running around in circles, I downloaded the newest version of SI and setup a fresh database.
I ran it, added a biller, customer and three inventory items.
Exported the data using myphpadmin.
Dropped all tables/data from the DB
Ran the new install of SI to the point of the web interface setup the structure.
Went to myPHPAdmin and tried to import the data I just exported and got this error:

Error
SQL query:

—
— Dumping data for table si_invoices
—

INSERT INTO si_invoices (id, index_id, domain_id, biller_id, customer_id, type_id, preference_id, date, custom_field1, custom_field2, custom_field3, custom_field4, note, owing, last_activity_date, aging_date, age_days, aging, sales_representative) VALUES
(1, 1, 1, 1, 1, 2, 1, ‘2023-07-21 15:43:09’, ”, ”, ”, ”, ”, ‘113.710000’, ‘2023-07-21 03:43:09’, ‘2023-07-21 03:43:09’, 0, ”, ”)
MySQL said: Documentation

#1452 – Cannot add or update a child row: a foreign key constraint fails (dbs11545668.si_invoices, CONSTRAINT si_invoices_ibfk_3 FOREIGN KEY (type_id) REFERENCES si_invoice_type (inv_ty_id) ON UPDATE CASCADE)

So with that, I dropped all tables again.
Used the instructions you sent and manually installed the structure
tried to import the fresh data and again got the same error.

So I am wondering if I am not exporting the data correctly to begin with or have the wrong options checked while exporting the data.

[Fuquar7]

I am making notes as I go, I have a MariaDB 10.6 database with utf8mb4_general_ci Collation

There are no problems Importing the structure

With Enable foreign key checks I get the following error

Error
SQL query:

—
— Dumping data for table si_inventory
—

INSERT INTO si_inventory (id, domain_id, product_id, quantity, cost, date, note) VALUES
(1, 1, 1, ‘1000.000000’, ‘0.000000’, ‘2014-03-29’, ”),
(2, 1, 2, ‘100.000000’, ‘0.000000’, ‘2014-03-29’, ”),
(3, 1, 18, ‘1.000000’, ‘124.990000’, ‘2014-03-29’, ”),
(4, 1, 19, ‘1.000000’, ‘43.000000’, ‘2014-03-29’, ”),
(5, 1, 20, ‘1.000000’, ‘5.990000’, ‘2014-03-29’, ”),
(6, 1, 21, ‘1.000000’, ‘44.990000’, ‘2014-03-29’, ”),
(7, 1, 4, ‘2.000000’, ‘14.920000’, ‘2014-03-29’, ”),
(8, 1, 5, ‘2.000000’, ‘0.940000’, ‘2014-03-30’, ”),
(9, 1, 6, ‘2.000000’, ‘0.340000’, ‘2014-03-30’, ”),
(10, 1, 3, ‘5.000000’, ‘1.270000’, ‘2014-03-30’, ”),
(11, 1, 14, ‘1.000000’, ‘2.290000’, ‘2014-03-30’, ”),
(12, 1, 13, ‘1.000000’, ‘3.450000’, ‘2014-03-30’, ”),
(13, 1, 8, ‘1.000000’, ‘6.760000’, ‘2014-03-30’, ”),
(14, 1, 9, ‘1.000000’, ‘9.530000’, ‘2014-03-30’, ”),
(15, 1, 16, ‘2.000000’, ‘0.390000’, ‘2014-03-30’, ”),
(16, 1, 17, ‘1.000000’, ‘1.770[…]
MySQL said: Documentation

#1452 – Cannot add or update a child row: a foreign key constraint fails (dbs11545668.si_inventory, CONSTRAINT si_inventory_ibfk_1 FOREIGN KEY (product_id) REFERENCES si_products (id) ON UPDATE CASCADE)

Without foreign key checks the data imports successfully. I’m assuming it’s a matter of eliminating all the orphaned records… the how? I have no idea but I’m assuming my database is in this state because I’ve been using it since March 2013

[Fuquar7]

utf8_general_ci is not an option these are the options I am given.

I treid utf8mb4_general_ci it was the closest I could find, no dice. I was able to import the structure but not the data.

I tried several DB versions, MySQL 5.7, MySQL 8.0 and MariaDB 10, no matter I’m unable to get any results.

My host is ionos (used to be 1and1) which I have been using for at least 10+ years. I started using SI early 2014 and have tried to keep up with the updates ever since.

I am hoping that somehow this can be straightened out.

Below are all the collation choices I am offered.

Collation

armscii8_bin
armscii8_general_ci
armscii8_general_nopad_ci
armscii8_nopad_bin

ascii_bin
ascii_general_ci
ascii_general_nopad_ci
ascii_nopad_bin

big5_bin
big5_chinese_ci
big5_chinese_nopad_ci
big5_nopad_bin

binary

cp1250_bin
cp1250_croatian_ci
cp1250_czech_cs
cp1250_general_ci
cp1250_general_nopad_ci
cp1250_nopad_bin
cp1250_polish_ci

cp1251_bin
cp1251_bulgarian_ci
cp1251_general_ci
cp1251_general_cs
cp1251_general_nopad_ci
cp1251_nopad_bin
cp1251_ukrainian_ci

cp1256_bin
cp1256_general_ci
cp1256_general_nopad_ci
cp1256_nopad_bin

cp1257_bin
cp1257_general_ci
cp1257_general_nopad_ci
cp1257_lithuanian_ci
cp1257_nopad_bin

cp850_bin
cp850_general_ci
cp850_general_nopad_ci
cp850_nopad_bin

cp852_bin
cp852_general_ci
cp852_general_nopad_ci
cp852_nopad_bin

cp866_bin
cp866_general_ci
cp866_general_nopad_ci
cp866_nopad_bin

cp932_bin
cp932_japanese_ci
cp932_japanese_nopad_ci
cp932_nopad_bin

dec8_bin
dec8_nopad_bin
dec8_swedish_ci
dec8_swedish_nopad_ci

eucjpms_bin
eucjpms_japanese_ci
eucjpms_japanese_nopad_ci
eucjpms_nopad_bin

euckr_bin
euckr_korean_ci
euckr_korean_nopad_ci
euckr_nopad_bin

gb2312_bin
gb2312_chinese_ci
gb2312_chinese_nopad_ci
gb2312_nopad_bin

gbk_bin
gbk_chinese_ci
gbk_chinese_nopad_ci
gbk_nopad_bin

geostd8_bin
geostd8_general_ci
geostd8_general_nopad_ci
geostd8_nopad_bin

greek_bin
greek_general_ci
greek_general_nopad_ci
greek_nopad_bin

hebrew_bin
hebrew_general_ci
hebrew_general_nopad_ci
hebrew_nopad_bin

hp8_bin
hp8_english_ci
hp8_english_nopad_ci
hp8_nopad_bin

keybcs2_bin
keybcs2_general_ci
keybcs2_general_nopad_ci
keybcs2_nopad_bin

koi8r_bin
koi8r_general_ci
koi8r_general_nopad_ci
koi8r_nopad_bin

koi8u_bin
koi8u_general_ci
koi8u_general_nopad_ci
koi8u_nopad_bin

latin1_bin
latin1_danish_ci
latin1_general_ci
latin1_general_cs
latin1_german1_ci
latin1_german2_ci
latin1_nopad_bin
latin1_spanish_ci
latin1_swedish_ci
latin1_swedish_nopad_ci

latin2_bin
latin2_croatian_ci
latin2_czech_cs
latin2_general_ci
latin2_general_nopad_ci
latin2_hungarian_ci
latin2_nopad_bin

latin5_bin
latin5_nopad_bin
latin5_turkish_ci
latin5_turkish_nopad_ci

latin7_bin
latin7_estonian_cs
latin7_general_ci
latin7_general_cs
latin7_general_nopad_ci
latin7_nopad_bin

macce_bin
macce_general_ci
macce_general_nopad_ci
macce_nopad_bin

macroman_bin
macroman_general_ci
macroman_general_nopad_ci
macroman_nopad_bin

sjis_bin
sjis_japanese_ci
sjis_japanese_nopad_ci
sjis_nopad_bin

swe7_bin
swe7_nopad_bin
swe7_swedish_ci
swe7_swedish_nopad_ci

tis620_bin
tis620_nopad_bin
tis620_thai_ci
tis620_thai_nopad_ci

ucs2_bin
ucs2_croatian_ci
ucs2_croatian_mysql561_ci
ucs2_czech_ci
ucs2_danish_ci
ucs2_esperanto_ci
ucs2_estonian_ci
ucs2_general_ci
ucs2_general_mysql500_ci
ucs2_general_nopad_ci
ucs2_german2_ci
ucs2_hungarian_ci
ucs2_icelandic_ci
ucs2_latvian_ci
ucs2_lithuanian_ci
ucs2_myanmar_ci
ucs2_nopad_bin
ucs2_persian_ci
ucs2_polish_ci
ucs2_roman_ci
ucs2_romanian_ci
ucs2_sinhala_ci
ucs2_slovak_ci
ucs2_slovenian_ci
ucs2_spanish2_ci
ucs2_spanish_ci
ucs2_swedish_ci
ucs2_thai_520_w2
ucs2_turkish_ci
ucs2_unicode_520_ci
ucs2_unicode_520_nopad_ci
ucs2_unicode_ci
ucs2_unicode_nopad_ci
ucs2_vietnamese_ci

ujis_bin
ujis_japanese_ci
ujis_japanese_nopad_ci
ujis_nopad_bin

utf16_bin
utf16_croatian_ci
utf16_croatian_mysql561_ci
utf16_czech_ci
utf16_danish_ci
utf16_esperanto_ci
utf16_estonian_ci
utf16_general_ci
utf16_general_nopad_ci
utf16_german2_ci
utf16_hungarian_ci
utf16_icelandic_ci
utf16_latvian_ci
utf16_lithuanian_ci
utf16_myanmar_ci
utf16_nopad_bin
utf16_persian_ci
utf16_polish_ci
utf16_roman_ci
utf16_romanian_ci
utf16_sinhala_ci
utf16_slovak_ci
utf16_slovenian_ci
utf16_spanish2_ci
utf16_spanish_ci
utf16_swedish_ci
utf16_thai_520_w2
utf16_turkish_ci
utf16_unicode_520_ci
utf16_unicode_520_nopad_ci
utf16_unicode_ci
utf16_unicode_nopad_ci
utf16_vietnamese_ci

utf16le_bin
utf16le_general_ci
utf16le_general_nopad_ci
utf16le_nopad_bin

utf32_bin
utf32_croatian_ci
utf32_croatian_mysql561_ci
utf32_czech_ci
utf32_danish_ci
utf32_esperanto_ci
utf32_estonian_ci
utf32_general_ci
utf32_general_nopad_ci
utf32_german2_ci
utf32_hungarian_ci
utf32_icelandic_ci
utf32_latvian_ci
utf32_lithuanian_ci
utf32_myanmar_ci
utf32_nopad_bin
utf32_persian_ci
utf32_polish_ci
utf32_roman_ci
utf32_romanian_ci
utf32_sinhala_ci
utf32_slovak_ci
utf32_slovenian_ci
utf32_spanish2_ci
utf32_spanish_ci
utf32_swedish_ci
utf32_thai_520_w2
utf32_turkish_ci
utf32_unicode_520_ci
utf32_unicode_520_nopad_ci
utf32_unicode_ci
utf32_unicode_nopad_ci
utf32_vietnamese_ci

utf8mb3_bin
utf8mb3_croatian_ci
utf8mb3_croatian_mysql561_ci
utf8mb3_czech_ci
utf8mb3_danish_ci
utf8mb3_esperanto_ci
utf8mb3_estonian_ci
utf8mb3_general_ci
utf8mb3_general_mysql500_ci
utf8mb3_general_nopad_ci
utf8mb3_german2_ci
utf8mb3_hungarian_ci
utf8mb3_icelandic_ci
utf8mb3_latvian_ci
utf8mb3_lithuanian_ci
utf8mb3_myanmar_ci
utf8mb3_nopad_bin
utf8mb3_persian_ci
utf8mb3_polish_ci
utf8mb3_roman_ci
utf8mb3_romanian_ci
utf8mb3_sinhala_ci
utf8mb3_slovak_ci
utf8mb3_slovenian_ci
utf8mb3_spanish2_ci
utf8mb3_spanish_ci
utf8mb3_swedish_ci
utf8mb3_thai_520_w2
utf8mb3_turkish_ci
utf8mb3_unicode_520_ci
utf8mb3_unicode_520_nopad_ci
utf8mb3_unicode_ci
utf8mb3_unicode_nopad_ci
utf8mb3_vietnamese_ci

utf8mb4_bin
utf8mb4_croatian_ci
utf8mb4_croatian_mysql561_ci
utf8mb4_czech_ci
utf8mb4_danish_ci
utf8mb4_esperanto_ci
utf8mb4_estonian_ci
utf8mb4_general_ci
utf8mb4_general_nopad_ci
utf8mb4_german2_ci
utf8mb4_hungarian_ci
utf8mb4_icelandic_ci
utf8mb4_latvian_ci
utf8mb4_lithuanian_ci
utf8mb4_myanmar_ci
utf8mb4_nopad_bin
utf8mb4_persian_ci
utf8mb4_polish_ci
utf8mb4_roman_ci
utf8mb4_romanian_ci
utf8mb4_sinhala_ci
utf8mb4_slovak_ci
utf8mb4_slovenian_ci
utf8mb4_spanish2_ci
utf8mb4_spanish_ci
utf8mb4_swedish_ci
utf8mb4_thai_520_w2
utf8mb4_turkish_ci
utf8mb4_unicode_520_ci
utf8mb4_unicode_520_nopad_ci
utf8mb4_unicode_ci
utf8mb4_unicode_nopad_ci
utf8mb4_vietnamese_ci

[Fuquar7]

I’ve added the structure of my database as well, I’m completely lost. I’m having to dig to my college days from 25 years ago (I’m a commercial contractor now)

Attachments:

1. si_Repairs.zip

[Fuquar7]

In custom.config.ini change the line

authenticationEnabled = true

to false. This will disable the authentication and allow you to change the password.

make sure to change authenticationEnabled back to true when finished.

• This reply was modified 9 months ago by Fuquar7.

[Fuquar7]

A couple of artifacts I am dealing with.

1. I was only able to create one invoice with one line item. It added the line item to si_invoice_items giving it a index number of 0. When I try to add any other invoice/item it errors out with a warning of a duplicate index number.

2. When entering line items, I get an warning “Invalid quantity value entered” when adding a fractional number like 1.5, I am forced to use a comma like 1,5 (I’m not in Spain yet, I don’t want to be forced to do that)

Also some house keeping things I’d like to help out with. Embedding fonts to be included when exporting pdf, I finally figured out how get that done and would like to share that with others. I also found that tooltipster.bundle.min.css (from the Tooltipster – The jQuery Tooltip Plugin) is missing from the Master 2023 download. I have a set of instructions to correct that as well I’d like to share.

It’s amazing how far all of this has evolved. I just noticed that my first invoice 03/28/2014.

I appreciate all your help, I hope I am not to bothersome.

Thank you.

• This reply was modified 9 months, 1 week ago by Fuquar7.

Attachments:

1. php.log_-4.txt

[Fuquar7]

I got an error, however I am now able to use the database.

Fatal error: Uncaught PDOException: There is no active transaction in
/si/Inc/Claz/PdoDb.php:946 Stack trace: #0
/si/Inc/Claz/PdoDb.php(946): PDO->commit() #1
/si/Inc/Claz/SqlPatchManager.php(223): Inc\Claz\PdoDb->commit() #2
/si/index.php(238): Inc\Claz\SqlPatchManager::runPatches() #3 {main} thrown in
/si/Inc/Claz/PdoDb.php on line 946

Fatal error: Uncaught PDOException: There is no active transaction in
/si/Inc/Claz/PdoDb.php:928 Stack trace: #0
/si/Inc/Claz/PdoDb.php(928): PDO->rollBack() #1
/si/Inc/Claz/PdoDb.php(100): Inc\Claz\PdoDb->rollback() #2 [internal function]: Inc\Claz\PdoDb->__destruct() #3 {main} thrown in /si/Inc/Claz/PdoDb.php on line 928

Attachments:

1. php.log_-3.txt

[Fuquar7]

No dice. I still get the wildly non specific error (errno: 150 “Foreign key constraint is incorrectly formed”) I’ve attached php.log

Attachments:

1. php.log_-2.txt

[Fuquar7]

Highest value, 330.

I have attached the exported structure of my working database.

Attachments:

1. db5013632319_hosting-data_io.zip

[Fuquar7]

I deleted the records one by one, which took a little bit of AI to build the SQL to delete those records… it only took 4 minutes for Bard to get me the correct string I needed. Now I have the following (See attached)

Attachments:

1. php.log_-1.txt

[Fuquar7]

I can’t attach my DB structure.

• This reply was modified 9 months, 2 weeks ago by Fuquar7.

[Fuquar7]

Further progress, but stuck at this point

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint in /simpleinvoices/Inc/Claz/PdoDb.php:1294 Stack trace: #0 /simpleinvoices/Inc/Claz/PdoDb.php(1294): PDOStatement->execute() #1 /simpleinvoices/Inc/Claz/SqlPatchManager.php(145): Inc\Claz\PdoDb->query() #2 /simpleinvoices/Inc/Claz/SqlPatchManager.php(215): Inc\Claz\SqlPatchManager::runSqlPatch() #3 simpleinvoices/index.php(238): Inc\Claz\SqlPatchManager::runPatches() #4 {main} thrown in /simpleinvoices/Inc/Claz/PdoDb.php on line 1294

[Fuquar7]

I’ve made progress, I now have problems with
SqlPatchManager::runSqlPatch() error. See error log for more information.

the FOREIGN KEY TABLE or some values in those seem to be at issue. I really only need to know if I can just delete the date or modify

Attachments:

1. php.log-2.txt

[Author]

Posts