Invalid SQL generated by export for `contact_photo` table

monicahq / monica

Personal CRM. Remember everything about your friends, family and business relationships.

https://beta.monicahq.com

GNU Affero General Public License v3.0

21.77k stars 2.18k forks source link

Invalid SQL generated by export for `contact_photo` table #5953

Open mnkhouri opened 2 years ago

mnkhouri commented 2 years ago

Describe the bug SQL export on 3.6.1 has malformed SQL for contact_photo table;

Which version are you using:

Export from app.monicahq.com version 3.6.1
Import to self-hosted 3.6.1

Additional context Attempting to import my .sql fails on this line from the export:

INSERT IGNORE INTO `contact_photo` (`contact_id`, `photo_id`, `created_at`, `updated_at`) VALUES
;

I believe this is invalid SQL -- the docs here indicate that VALUES should have at least one value following it.

particleflux commented 2 years ago

Yes, this is invalid SQL.

This does not only affect the contact_photo table, but all empty tables. Whether they're empty or not depends on your dataset. The SQL exporter does not check whether there is actual data when writing out the INSERT statements.

Edit: Hm, actually, no, but it definitely is an issue with this self-grown exporter. The generated SQL is far away from nice. Try using mysqldump instead, it can also lock tables cleanly to ensure data integrity, and generates better queries.