SqlErrorException when sending a form

[wowaTYPO3]

After a TYPO3 Upgrade from Version 9 to 10.4.8 and an Update from Powermail to 8.2.0, forms can no longer be sent in the Frontend.

(1/3) #1472074485 TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND ((`tx_powermail_domain_model_field`.`marker` = ?) AND (`tx_powermail_domai' at line 1

(2/3) Doctrine\DBAL\Exception\SyntaxErrorException
An exception occurred while executing 'SELECT `tx_powermail_domain_model_field`.* FROM `tx_powermail_domain_model_field` `tx_powermail_domain_model_field` LEFT JOIN `tx_powermail_domain_model_page` `tx_powermail_domain_model_page` ON `tx_powermail_domain_model_field`.`page` = `tx_powermail_domain_model_page`.`uid` LEFT JOIN `tx_powermail_domain_model_form` `tx_powermail_domain_model_form` ON `tx_powermail_domain_model_page`.`form` = `tx_powermail_domain_model_form`.`uid` WHERE () AND ((`tx_powermail_domain_model_field`.`marker` = ?) AND (`tx_powermail_domain_model_form`.`uid` = ?)) AND ((`tx_powermail_domain_model_field`.`deleted` = 0) AND (`tx_powermail_domain_model_field`.`t3ver_state` <= 0) AND (`tx_powermail_domain_model_field`.`t3ver_wsid` = 0) AND (`tx_powermail_domain_model_field`.`t3ver_oid` = 0) AND (`tx_powermail_domain_model_field`.`hidden` = 0) AND (`tx_powermail_domain_model_field`.`starttime` <= 1601967240) AND ((`tx_powermail_domain_model_field`.`endtime` = 0) OR (`tx_powermail_domain_model_field`.`endtime` > 1601967240))) AND (((`tx_powermail_domain_model_page`.`deleted` = 0) AND (`tx_powermail_domain_model_page`.`t3ver_state` <= 0) AND (`tx_powermail_domain_model_page`.`t3ver_wsid` = 0) AND (`tx_powermail_domain_model_page`.`t3ver_oid` = 0) AND (`tx_powermail_domain_model_page`.`hidden` = 0) AND (`tx_powermail_domain_model_page`.`starttime` <= 1601967240) AND ((`tx_powermail_domain_model_page`.`endtime` = 0) OR (`tx_powermail_domain_model_page`.`endtime` > 1601967240))) OR (`tx_powermail_domain_model_page`.`uid` IS NULL)) AND (((`tx_powermail_domain_model_form`.`deleted` = 0) AND (`tx_powermail_domain_model_form`.`t3ver_state` <= 0) AND (`tx_powermail_domain_model_form`.`t3ver_wsid` = 0) AND (`tx_powermail_domain_model_form`.`t3ver_oid` = 0) AND (`tx_powermail_domain_model_form`.`hidden` = 0) AND (`tx_powermail_domain_model_form`.`starttime` <= 1601967240) AND ((`tx_powermail_domain_model_form`.`endtime` = 0) OR (`tx_powermail_domain_model_form`.`endtime` > 1601967240))) OR (`tx_powermail_domain_model_form`.`uid` IS NULL)) LIMIT 1' with params ["kontakt_anrede", 1]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND ((`tx_powermail_domain_model_field`.`marker` = ?) AND (`tx_powermail_domai' at line 1

(3/3) Doctrine\DBAL\Driver\Mysqli\Exception\ConnectionError
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND ((`tx_powermail_domain_model_field`.`marker` = ?) AND (`tx_powermail_domai' at line 1

What I tried so far:

• Update from Powermail 8.2.0 to 8.2.1
• Database Compare
• Deactivate / Activate Powermail
• And, of course, multiple times delete all caches (via the button in the maintenance area)
• Create a new form, place a new plugin on a new page

[einpraegsam]

Hi Wolfgang,

thx for the very good analysis so far.

When I look into the SQL it is:

SELECT `tx_powermail_domain_model_field`.* 

FROM `tx_powermail_domain_model_field` `tx_powermail_domain_model_field` 

LEFT JOIN `tx_powermail_domain_model_page` `tx_powermail_domain_model_page` ON `tx_powermail_domain_model_field`.`page` = `tx_powermail_domain_model_page`.`uid` 
LEFT JOIN `tx_powermail_domain_model_form` `tx_powermail_domain_model_form` ON `tx_powermail_domain_model_page`.`form` = `tx_powermail_domain_model_form`.`uid` 

WHERE () AND ((`tx_powermail_domain_model_field`.`marker` = ?) AND (`tx_powermail_domain_model_form`.`uid` = ?)) AND ((`tx_powermail_domain_model_field`.`deleted` = 0) AND (`tx_powermail_domain_model_field`.`t3ver_state` <= 0) AND (`tx_powermail_domain_model_field`.`t3ver_wsid` = 0) AND (`tx_powermail_domain_model_field`.`t3ver_oid` = 0) AND (`tx_powermail_domain_model_field`.`hidden` = 0) AND (`tx_powermail_domain_model_field`.`starttime` <= 1601967240) AND ((`tx_powermail_domain_model_field`.`endtime` = 0) OR (`tx_powermail_domain_model_field`.`endtime` > 1601967240))) AND (((`tx_powermail_domain_model_page`.`deleted` = 0) AND (`tx_powermail_domain_model_page`.`t3ver_state` <= 0) AND (`tx_powermail_domain_model_page`.`t3ver_wsid` = 0) AND (`tx_powermail_domain_model_page`.`t3ver_oid` = 0) AND (`tx_powermail_domain_model_page`.`hidden` = 0) AND (`tx_powermail_domain_model_page`.`starttime` <= 1601967240) AND ((`tx_powermail_domain_model_page`.`endtime` = 0) OR (`tx_powermail_domain_model_page`.`endtime` > 1601967240))) OR (`tx_powermail_domain_model_page`.`uid` IS NULL)) AND (((`tx_powermail_domain_model_form`.`deleted` = 0) AND (`tx_powermail_domain_model_form`.`t3ver_state` <= 0) AND (`tx_powermail_domain_model_form`.`t3ver_wsid` = 0) AND (`tx_powermail_domain_model_form`.`t3ver_oid` = 0) AND (`tx_powermail_domain_model_form`.`hidden` = 0) AND (`tx_powermail_domain_model_form`.`starttime` <= 1601967240) AND ((`tx_powermail_domain_model_form`.`endtime` = 0) OR (`tx_powermail_domain_model_form`.`endtime` > 1601967240))) OR (`tx_powermail_domain_model_form`.`uid` IS NULL)) 

LIMIT 1

The () seems a bit strange in my eyes

Some tests for you:

1) The main change in the sql model of powermail in 8.x was to change single field names to a better fitting one. So tx_powermail_domain_model_field.pages was renamed to .page e.g. There is an upgrade wizard step that changes old data. Can you please check if this fields are correctly filled in the database:

• tx_powermail_domain_model_field.page
• tx_powermail_domain_model_page.form

2) Did you already disable any possible extensions that extend powermail? Can you please disable all TypoScript that is related to powermail? Same for HTML-Templates (and Partials and Layouts)? So that we can test if the failure is still there in a basic configuration?

3) Is the MySQL/MariaDB running in strict mode? This is not a problem in general, but just to find the possible issue.

Update: 4) It seems that \In2code\Powermail\Domain\Repository\FieldRepository::findByMarkerAndForm() is the source of the problem. Do you use replaceIrreWithElementBrowser in Extension Configuration?

[peerlight]

Hi, same problem here using TYPO3 10.4.7/8/9 and powermail 8.2.0/1 on PHP 7.4.x

`marker = ?` looks strange

all forms were newly generated with powermail 8.2, was a fresh install

To answer your questions: 1) seems ok 2) same error without custom TS or FLUID/CSS/JS 3) MariaDB with innodb_strict_mode OFF (had it ON, but setting OFF didn't help) 4) replaceIrreWithElementBrowser is not used

[wowaTYPO3]

Ok, I could do some more tests.

I downgraded Powermail to some other versions, 8.0.1, 8.0.2, but they didn't work because of another SQL-Error.

In version 8.1.0 the same error as described above happened.

Your questions: 1) OK 2) No change with default templates and TypoScript 3) I don't know where/how to look for that, it's a shared hosting, 4) No

[einpraegsam]

@peerlight Any chance to do some Debugging on your system or on a testsystem with the failure?

[nebrot]

I don't have read the whole issue, but could this be related with "Doctrine 2.11.x breaks prepared parameters" https://forge.typo3.org/issues/92376 ?

[einpraegsam]

Thx @nebrot - that sound really like the issue. Can anyone confirm?

[simond2020]

Hi @einpraegsam

We had similar/same issue with TYPO3 9.5.22 update and Powermail 7.4

Our current "temporary" fix was to wrap "$where = $where->with(...$args);" in if(count($args) != 0){ } in at line 837 vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php.

It looks like this TYPO3 update takes us from Doctine 2.10 to 2.11

We will look at downgrade/other options now we have sites running :)

[peerlight]

Downgrading doctrine/dbal (2.11.1 => 2.10.4) worked for me

[Bufallo]

I can confirm. Downgrading Doctrine Dbal to 2.10.4 fixes the Issue i had: Core: Exception handler (WEB): Uncaught TYPO3 Exception: Too few arguments to function Doctrine\DBAL\Query\Expression\CompositeExpression::with(), 0 passed in /var/www/XXXreleases/20201007105506/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php on line 837 and exactly 1 expected | ArgumentCountError thrown in file /var/www/XXX/releases/20201007105506/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/Expression/CompositeExpression.php in line 119

[Patta]

Can confirm that downgrading doctrine/dbal from 2.11.1 to 2.10.4 worked for me. (Composer mode, TYPO3 9.5 with Powermail 7.4)

[einpraegsam]

I will keep this issue open even if it is not powermail related for others with the same problem.

[freshman20]

This is a global TYPO3 issue because mapping like this query->matching( $query->equals('page.form.uid', $formUid) ); cause that error. Don't know if TYPO3 core team informed about this bug. They should limit the doctrine/dbal version in the core/composer.json until that is fixed Workaround: projects composer.json { "require": { ... "typo3/cms-tstemplate": "^10.4", "typo3/cms-viewpage": "^10.4", "doctrine/dbal": "2.10.*" }, "extra": { "typo3/cms": { ... } },

[mschwemer]

@freshman20 The core team is aware of it. The dbal version will not be pinned to 2.10, because this week a new release of doctrine/dbal wit this bugfix will be released.

See: https://github.com/doctrine/dbal/issues/4282#issuecomment-707849290

[Patta]

Fixed with https://github.com/doctrine/dbal/releases/tag/2.11.2