Unable to parse ini file: config/custom.config.ini

[RRowley]

I’ve put instructions in the attached zip file. For some reason, it get an error if I try and save them outright in this comment box.

Attachments:

1. instructions.zip

[fearless359]

FYI, your password recovery directions to another forum user, were excellent. I hadn’t thought of it before. I tested it and other than some template warnings related to not having a role name stored for the user’s session, it works. So I just delivered an update that fixes the warnings and formalized recovery procedures in the How To menu section.

[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

[RRowley]

The utf8mb4 should be OK for the database. That is actually what I have but I changed to utf8… to verify it would work with the schema I provided.

I would like to know what the errors were you encountered when you imported the data. If it had to do with foreign key issues, you can turn off the foreign key check in import. If this allows your data to be loaded, you are no worse off than you are now but I expect you have number of orphaned records that should be resolved.

[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

[RRowley]

You need to determine which product numbers in the inventory table do not have a match to an id field value in the product table. If the product_id of an inventory record is wrong, change it to a correct value. If the inventory product_id should be for a product that is missing, you can add the missing product, get its id and then change the inventory record to the new product. Or you could delete the orphaned inventory record.

Sorry you have to go through this, but once fixed, the database will enforce foreign key settings to prevent future issues.

[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]

My test data

Attachments:

1. Fresh-data-dbs11545668.zip

[RRowley]

I notice that the sql file inserts into the si_invoices table before the load of the si_invoice_type table.

Make sure you turn off the check foreign keys option being importing the sql file.

[RRowley]

Just following up to see if your issues have been addressed.

[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.

[Author]

Posts