Publisher does not support the Fluid field type. Please do not contact asking when support will be available.

If you purchased an add-on from expressionengine.com, be sure to visit boldminded.com/claim to add the license to your account here on boldminded.com.

Ticket: Saving Previews fails due to invalid SQL query due to empty form values

Status	Resolved
Add-on / Version	Publisher 1.6.2
Severity
EE Version	2.8.1

Jarrett M. Barnett

Apr 28, 2015

Description:
When empty form values are submitted via the Template Previews page…
/cp/addons_modules/show_module_cp?module=publisher&method=previews
...an invalid SQL query is generated

I was able to work-around the issue by setting the ‘template_id’ column to be a column type that allows string values. Since the column does in fact allow for NULL values, the INSERT query string should generate NULL values in place of the empty strings (”“) for all columns of type integer.

e.g. this:
INSERT INTO `exp_publisher_previews` (`channel_id`, `site_id`, `template_id`, `append`, `custom`, `override`) VALUES (31, ‘1’, ‘’, ‘’, ‘’, ‘’)
should be:
INSERT INTO `exp_publisher_previews` (`channel_id`, `site_id`, `template_id`, `append`, `custom`, `override`) VALUES (31, ‘1’, NULL, ‘’, ‘’, ‘’)

Detailed steps to reproduce the issue:
1. Submit Template Previews form w/o setting a Preview Template for all of the channels
2. Observe error:

A Database Error Occurred
Error Number: 1366

Incorrect integer value: ‘’ for column ‘template_id’ at row 1

INSERT INTO `exp_publisher_previews` (`channel_id`, `site_id`, `template_id`, `append`, `custom`, `override`) VALUES (31, ‘1’, ‘’, ‘’, ‘’, ‘’)

Filename: /var/www/public_html/lib/publisher/models/publisher_model.php

Line Number: 125

BoldMinded (Brian)

Comment has been marked private.

Jarrett M. Barnett

Still no go. The SQL statement is still using an empty string instead of a NULL value for the template_id column. It should be:

UPDATE exp_publisher_previews SET channel_id = 31, site_id = ‘1’, template_id = NULL, append = ”, custom = ”, override = ” WHERE channel_id = 31 AND site_id = ‘1’

Either way, it’s not a blocker for me currently since I simply modified the column to be a string supported column temporarily for the form submission; then reverse the column back to an INT type afterwards.

— Here’s the full error:

A Database Error Occurred Error Number: 1366

Incorrect integer value: ” for column ‘template_id’ at row 1

UPDATE `exp_publisher_previews` SET `channel_id` = 31, `site_id` = '1', `template_id` = '', `append` = '', `custom` = '', `override` = '' WHERE `channel_id` = 31 AND `site_id` = '1'

Filename: /var/www/public_html/lib/publisher/models/publisher_model.php

Line Number: 118

Jarrett M. Barnett

Was getting this error on the homepage with the new code; the SQL seems to be missing a JOIN statement for whichever table is tied to the “exp_d” alias.

Error Number: 1054

Unknown column ‘exp_d.publisher_status’ in ‘where clause’

SELECT ct.*, t.*, c.*, ct.url_title AS default_url_title, t.title AS title FROM (exp_publisher_titles AS t) JOIN exp_channel_titles AS ct ON ct.entry_id = t.entry_id JOIN exp_channels AS c ON c.channel_id = t.channel_id WHERE t.publisher_status = ‘open’ AND exp_d.publisher_status = ‘open’ AND t.publisher_lang_id = 1 AND exp_d.publisher_lang_id = ‘1’ AND t.site_id = ‘1’ AND t.entry_id IN (‘15’, ‘730’, ‘663’, ‘728’, ‘16’, ‘650’, ‘17’, ‘18’, ‘791’, ‘643’, ‘740’, ‘359’, ‘778’, ‘357’, ‘358’, ‘340’, ‘13’, ‘24’, ‘26’, ‘360’, ‘415’, ‘361’, ‘688’, ‘546’, ‘19’, ‘20’, ‘815’, ‘28’, ‘817’, ‘818’, ‘34’, ‘35’, ‘27’, ‘31’, ‘32’, ‘544’, ‘346’, ‘349’, ‘351’, ‘353’, ‘556’, ‘33’, ‘23’, ‘29’, ‘30’, ‘21’, ‘36’, ‘22’, ‘483’, ‘37’, ‘38’, ‘39’, ‘51’, ‘44’, ‘45’, ‘52’, ‘53’, ‘40’, ‘631’, ‘681’, ‘766’, ‘841’, ‘536’, ‘551’, ‘596’, ‘604’, ‘635’, ‘761’, ‘443’, ‘811’)

Filename: /var/www/public_html/lib/publisher/models/publisher_entry.php

Line Number: 1362

Jarrett M. Barnett

Interestingly enough. I did not run into this issue when installing it on the client’s server. So I fetched the phpinfo() on both my sandbox and the client server. I can send you the phpinfo() on both environments if you were interested.

Client Server: PHP 5.3.15 on Apache/2.2.3 (Red Hat) - MySQL: 5.0.77 Sandbox: PHP 5.5.23 on Apache/2.4.12 (Ubuntu) - MySQL: 5.0.11

Also, the preview template_id’s were set to “0” when I saved the changes on the client server.

BoldMinded (Brian)

Sounds like a MySQL strict mode setting or something.

BoldMinded (Brian)

Comment has been marked private.

BoldMinded (Brian)

Pretty sure I fixed this one, closing this.