Mailcow-dockerized: Unable to login to MailcowUI - Database initialisation failed: SQLSTATE[42000] - Now FIXED

[laymonk]

Fresh install of mailcow .. first attempt to get to the UI:

When the page loads, the error message below slides out:

Database initialisation failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM `admin`)' at line 3

Then it pops out again when one tries to login with the default username/password .. I vaguely remember resolving this the last time I installed mailcow (a few months ago) by editing some of the xml files ..

[laymonk]

Going through the various containers, it seems the problem lies in the phpfpm container ..

bash-4.3# grep -rHn 'WHERE NOT EXISTS' /web/
/web/inc/init_db.inc.php:653:        WHERE NOT EXISTS (SELECT * FROM `admin`);");

• And the issue is the backticks quoting .. which PDO is protecting against due to concerns about sql injection .. Suggestion is to use a prepared statement instead
• Mind you, I am using maraidb:10.2 .. perhaps I should go back to 10.1?

[laymonk]

Indeed, the problem goes away with mariadb:10.1 .. So, you may need to update your docs to indicate that mariadb max version is 10.1, until that query is adapted to use a prepared query.

[K2rool]

Am not quite sure why you would need to add warning about the usage of MariaDB10.2, Mailcow come with mariadb:10.1 which works fine with the current syntax.

The only users that will see this issue are user that changed the version of Mariadb in docker-compose.yml so it not as such a Mailcow issue.

[laymonk]

@K2rool, there is a history here, and a reason why I ended up using mariadb 10.2 (I tried so many others too, with @andryyy's awareness) .

Perhaps you should read through linked tickets before jumping to conclusions?

[K2rool]

I did read it, I was talking generally if a user changes a version of the docker container e.g. Dovecot,Mysql,Rspamd etc to a different version that Mailcow ships and it break it the user caused it not rely a Mailcow issue.

sorry if i'm annoying you over my last reply.

[laymonk]

'Annoyed' is the wrong word.

If you did read it, then the usefulness of your response is what I am struggling to understand, because in that other issue, @andryyy himself suggested I try other mysql distros (and did not specify a version limit). So, I'd thought that perhaps he was unaware of the version problem.

In any case, what's wrong with spelling out a list of requirements for a system? I would think this is standard practice .. no?

[mkuron]

Instead of documenting that it's broken with 10.2, the affected SQL statements need to be fixed. They probably rely on some non-standard behavior of old versions. That should be reasonably easy to change and we need it done anyway as the day will come when we need to upgrade MariaDB.

[andryyy]

My awareness, yes, but I also wrote that it may/will break things if we just switch to 10.2. 👍

There shouldn't be more problems, though. The given query is also fine to run unprepared (we use prepared stmts everywhere else, I think). But not with strict SQL. I switched to 10.2 and disabled the strict mode for now (dev).

Thanks for catching that error! 👍

[andryyy]

PS: The backticks are not a problem. You need to add a "from admin" after selecing the hard coded values.

[laymonk]

@andryyy, thanks for all your support .. :100:

I felt a bit like I was beginning to pester you yesterday .. and have one more quick question, and I am trying to avoid opening a new issue over such a simple question ..

• I am trying to configure gogs to sendmail via postfix .. your docs suggest to use 172.22.1.8 (but there is no such address) .. instead in docker-compose, postfix has no ipv4-address, and dns of 1.254
• If I point to 1.254:587 it says connection refused

Question

• Please can you clarify how to connect gogs to postfix
• How best to deal with trivial questions like this? Github issues OK, or forum preferred?

Thanks again ..

[K2rool]

The ip address for Postfix in Mailcow is 172.22.1.1 that page in the documentation need to be edited to show the right ip address.

Sorry i forgot the .1 in end I've edited my comment to the correct ip of 172.22.1.1

[laymonk]

Thanks @K2rool .. will work with that.

I did enter the container to check, but none of the ip discovery commands worked .. and docker-compose did not indicate that either. So, much appreciated ..

[andryyy]

Argh, sorry! Will correct it now.

Edit: Issues are fine. :)

[laymonk]

I can confirm that 172.22.1.1 works .. needs a username and password defined as well to work, because smtp sending is only authorised for authenticated users ..

[andryyy]

Try port 588. :-)

Am 13.09.2017 um 14:34 schrieb laymonk [e-mail]:

Thanks @K2rool .. will work with that.

I did enter the container to check, but none of the ip discovery commands worked .. and docker-compose did not indicate that either. So, much appreciated ..

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[laymonk]

588 doesn't work .. Nothing listening there ? Firewalled?

# netcat -vvvz 172.22.1.1 588
netcat: connect to 172.22.1.1 port 588 (tcp) failed: Connection refused
# netcat -vvvz 172.22.1.1 587
Connection to 172.22.1.1 587 port [tcp/submission] succeeded!

[laymonk]

Forgot to mention, on that your mailcow guide for gogs, the stated DB server IP address is also wrong

• should be 172.22.1.250 (instead of the stated 172.22.1.2)

• Good guide indeed, which I have now used successfully to deploy gitea .. which is a fork of gogs
• I prefer gitea because there are more people able to contribute to the code, and they are working hard on getting a good API functional.
• It's a relatively recent fork and the app.ini use more or less the same settings.
• So, I used your guide exactly, apart from changing:
  
  
  
  • the docker mapped ports
  
  
  • the nginx proxy port
  
  
  • the ipv4 address
  
  
  • image: gitea/gitea
  
  

Only issue is that when I restart the container, gitea-mailcow, I get the following errors

# docker-compose restart gitea-mailcow
WARNING: The DBROOT variable is not set. Defaulting to a blank string.
WARNING: The DBNAME variable is not set. Defaulting to a blank string.
WARNING: The DBUSER variable is not set. Defaulting to a blank string.
WARNING: The DBPASS variable is not set. Defaulting to a blank string.
WARNING: The MAILCOW_HOSTNAME variable is not set. Defaulting to a blank string.
WARNING: The TZ variable is not set. Defaulting to a blank string.
WARNING: The ADDITIONAL_SAN variable is not set. Defaulting to a blank string.
Restarting mailcowdockerized_gitea-mailcow_1 ... done

• The system runs well nonetheless, and I can login and use it.
• I don't get similar errors with gogs, and I am wondering what the difference is

Perhaps, we need a new guide for gitea?

[andryyy]

You need to run the docker-compose command from within the mailcow-dockerized folder. It needs to parse the .env file (a link to mailcow.conf).

[laymonk]

Indeed, that was the problem .. I was in a sub-directory of it .. thanks