Aimeos + postgresql: boolean column type problem

Questions around the Aimeos bundle for the Symfony framework
Forum rules
Always add your Symfony, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
etienne.bonnin
Posts: 4
Joined: 30 May 2018, 15:16

Aimeos + postgresql: boolean column type problem

Post by etienne.bonnin » 30 May 2018, 18:15

I followed the default aimeos symfony bundle installation.

Symfony version 3.4.11
Aimeos version: 2018.04

My config.yml is:

Code: Select all

...
...
fos_user:
    db_driver: orm
    firewall_name: main
    user_class: AppBundle\Entity\User
    from_email:
        address: email@mycompany.zzz
        sender_name: 'My sender name'

...
...
doctrine:
    dbal:
        driver: %database_driver%
        host: '%database_host%'
        port: '%database_port%'
        dbname: '%database_name%'
        user: '%database_user%'
        password: '%database_password%'
        charset: UTF8

    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true

...
...

aimeos_shop:
    mshop:
        customer:
            manager:
                name: FosUser
                password:
                    name: Bcrypt
    resource:
            db:
                adapter: pgsql
                host: %database_host%
                port: %database_port%
                database: %database_name%
                username: %database_user%
                password: %database_password%
                stmt: null
                #stmt: ["SET NAMES 'utf8'", "SET SESSION sql_mode='ANSI'"]
                #limit: 2
                #opt-persistent: 0
But when I want add admin user:

php bin/console aimeos:account --admin email@mycompany.zzz

I obtain the following error:

Code: Select all

red@barbalbero:~/symfony/ecommerce$ php bin/console aimeos:account --admin email@mycompany.zzz
Password

In Simple.php line 97:
                                                                                                                                                                                      
  An exception occurred while executing '                                                                                                                                             
  					INSERT INTO "fos_user" (                                                                                                                                                       
  						"siteid", "username_canonical", "username", "company", "vatid", "salutation", "title",                                                                                        
  						"firstname", "lastname", "address1", "address2", "address3",                                                                                                                  
  						"postal", "city", "state", "countryid", "langid", "telephone",                                                                                                                
  						"email_canonical", "email", "telefax", "website", "longitude", "latitude",                                                                                                    
  						"birthday", "enabled", "vdate", "password", "mtime", "editor", "roles", "salt",                                                                                               
  						"ctime"                                                                                                                                                                       
  					) VALUES (                                                                                                                                                                     
  						1,'email@mycompany.zzz','email@mycompany.zzz','','','','','','','','','','','','',NULL,NULL,'','email@mycompany.zzz','email@mycompany.zzz','','',NULL,NULL,NULL,1,NULL,'$2y$  
  10$XZUZul7qxTYvRgdyXcBJ8.B0InI2ujAHtmu//pogU96BCA8sgApzO','2018-05-30 19:57:40','aimeos:account','a:0:{}','','2018-05-30 19:57:40'                                                  
  					)                                                                                                                                                                              
  				':                                                                                                                                                                              
                                                                                                                                                                                      
  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "enabled" is of type boolean but expression is of type integer                                                                 
  LINE 10: ...y.zzz','email@mycompany.zzz','','',NULL,NULL,NULL,1,NULL,'$2...                                                                                                         
                                                                ^                                                                                                                     
  HINT:  You will need to rewrite or cast the expression.                                                                                                                             
                                                                                                                                                                              
The same error occours when the initial process try to add demo data into the "fos_user" table (for the same field, enabled::boolean).
It seems a mysql vs postgresql compatibility problem. Did I forget something in doctrine configuration?

Thanks in advance

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Aimeos + postgresql: boolean column type problem

Post by aimeos » 31 May 2018, 21:16

You are right, this seems like a compatibility problem because for PostgreSQL "1" isn't the same as "true"
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Aimeos + postgresql: boolean column type problem

Post by aimeos » 31 May 2018, 21:40

Changed the code in dev-master so it stores a true/false value now:
https://github.com/aimeos/ai-fosuser/co ... 490af50f07

Can you check if it works in your environment too? Then, we can make a new 2018.04.x release.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

etienne.bonnin
Posts: 4
Joined: 30 May 2018, 15:16

Re: Aimeos + postgresql: boolean column type problem

Post by etienne.bonnin » 04 Jun 2018, 09:24

Sorry but the patch you provided doesn't work in my environment. It seems that "true" value is translated to "1" and "false" to "0" in the bind (?) method. The error is the same.

etienne.bonnin
Posts: 4
Joined: 30 May 2018, 15:16

Re: Aimeos + postgresql: boolean column type problem

Post by etienne.bonnin » 04 Jun 2018, 09:43

I found a temporary solution in:

vendor/aimeos/aimeos-core/lib/mwlib/src/MW/DB/Statement/DBAL/Simple.php

Code: Select all

59c59
< 				$this->binds[$position] = (int) (bool) $value; break;
---
> 				$this->binds[$position] = ((bool) $value)?"true":"false"; break;
Hope this helps!

Thanks

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Aimeos + postgresql: boolean column type problem

Post by aimeos » 05 Jun 2018, 07:09

Yes, that was really helpful! :-)
We've integrated your fix and released a new version (core: 2018.04.7, ai-fosuser: 2018.04.2). Now it should work flawlessly with the FOSUser bundle and PostgreSQL.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

Post Reply