Document MySQL version requirement for fleet

[groob]

Fleet doesn't work well with older version of MySQL, at least not without making adjustments to the default configuration.
It also doesn't work with mariadb.

We should ensure compatibility with these commonly used versions of mysql (esp mariadb), and we'll accept contributions if someone wants to take that on. But we also need to document more clearly exactly what the requirements are.

Aside from numerous questions in chat and other support discussions, there's also a number of github issues:

• Cannot initialize empty database #1641
• Use a working MySQL version #1598
• Error 1067 when running prepare db #1620
• FAIL 20161118212641_CreateTablePasswordResetRequests.go (Error 1067: Invalid default value for 'expires_at'), quitting migration #1643
• Ubuntu: FAIL 20161118212641_CreateTablePasswordResetRequests.go kolide-quickstart#31

Fleet is tested and developed against mysql 5.7 and works out of the box with that version.

[kd8drx]

Worth noting: Fleet is also incompatible with Amazon Aurora, which is billed as MySQL compatible.

[groob]

@kd8drx any details into what errors come up there?

[znb]

@groob Just a heads up...there are issues with MySQL 5.7 that comes with Ubuntu. Please let me know if you'd like help with testing

[groob]

@znb what kinds of issues? If you can provide a link or description I can start to test.

Or did you mean your bug report you filed earlier?

[znb]

@groob I meant the bug I filed earlier.

[sunilkal]

@znb were you able to resolve FAIL 20161118212641_CreateTablePasswordResetRequests.go error?
I'm running MySQL 5.7.20 on Ubuntu 17.10.1 unable to get around it.

[znb]

@sunilkal No...I'm using Ubuntu 16.04 and MySQL 5.7.something. Waiting for feedback from @groob

[groob]

@znb @sunilkal I just provisioned a Ubuntu 16.04 server on DigitalOcean and installed mysql with https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-16-04 then followed the instructions on the docs page https://github.com/kolide/fleet/blob/63d60d5f6a0ec3239279250aa0a103f93d74b4ae/docs/infrastructure/fleet-on-ubuntu.md

root@ubuntu-1gb-nyc3-01:~# mysqld --version
mysqld  Ver 5.7.20-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu))

./linux/fleet prepare db --mysql_address=127.0.0.1:3306 --mysql_database=kolide  --mysql_username=root --mysql_password=toor
Migrations completed.

./linux/fleet prepare db --mysql_address=127.0.0.1:3306 --mysql_database=kolide  --mysql_username=root --mysql_password=toor
Migrations already completed. Nothing to do.

[groob]

The error happens

fleet/server/datastore/mysql/migrations/tables/20161118212641_CreateTablePasswordResetRequests.go

Line 17 in 63d60d5

"`expires_at` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01'," +

because of strict mode in mysql, although we're in the range specified by the documentation

You can try to see if changing the 1970-01-01 00:00:01 value to 1970-01-01 00:00:02 changes the situation, but I can't reproduce the problem with a default ubuntu install.

[znb]

@groob Getting the same error with CentOS 7

fleet version 1.0.5
mysql-community-server-5.7.20-1.el7.x86_64

2017/11/27 21:44:30 FAIL 20161118212641_CreateTablePasswordResetRequests.go (Error 1067: Invalid default value for 'expires_at'), quitting migration.

[groob]

mind sharing the mysql config you have? Is strict_mode enabled?

[znb]

@groob Perhaps share a working mysql config ? I'm really not sure where this is going wrong.
strict_mode is disabled.

[groob]

docker pull mysql:5.7

The config for that mysql instance works, as does ubuntu on digital ocean. I'm not really sure how your distro is different, or whether the issue is coming up because of something else like TZ settings...

I've seen the bug report a number of times but was never able to reproduce it myself.

[znb]

@groob Thanks I'll have a look into it. If it helps I'm not using Docker. I'll be using a server deployed in the Cloud rather than Docker instances.

I'll keep tinkering.

[groob]

☂️

If you're using a specific cloud provider/ubuntu image it would be helpful to give us detailed steps that we could use to reproduce the error with.

[znb]

Weird...works fine with Ubuntu 16.04 in AWS, but not on a locally build Ubuntu server in Vmware Fusion.

[groob]

A diff of the two mysql configs and mysql version numbers would be extremely helpful.

[zbuc]

I'm also having issues using RDS in general with Fleet.

I tried both Aurora and regular RDS MySQL and both get the following error:

2018/01/25 21:40:27 FAIL 20170306075207_UseUTF8MB.go (alter table file_integrity_monitoring_files: Error 1071: Specified key was too long; max key length is 767 bytes), quitting migration.

I think switching to MyISAM might fix it, but only InnoDB is supported on RDS.

I also tried changing all the character set related configurations in the RDS parameter group to utf8mb but that didn't help, either.

[rubenrodr-versia]

Hi,

I'm trying to prepare db using mysql
Server version: 8.0.11 MySQL Community Server - GPL
and fleet version:
fleet version 1.0.7-1-g2ded63e
/bin/fleet prepare db -c /etc/kolide/kolide.yml
and i get
Error creating db connection: this authentication plugin is not supported

[CodyLeeWhite]

any update on the mariadb issue? Trying to use it with centos 7.4 and mariadb 5.5.56-2.el7 and fleet 1.0.8

After a while I ended up with this "2018/07/10 20:39:54 FAIL 20161118212436_CreateTableDistributedQueryCampaigns.go (Error 1293: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause), quitting migration."

That led me here eventually. For now I will find a different way for my dev work.

[CodyLeeWhite]

seems that it does play nice with MariaDB-server-10.2.11 for the prepare db command.

[CodyLeeWhite]

well. for MariaDB-server-10.1 we get this error @groob

2018/07/18 20:58:55 FAIL 20170306075207_UseUTF8MB.go (alter table file_integrity_monitoring_files: Error 1071: Specified key was too long; max key length is 767 bytes), quitting migration.

did some looking around

found this here
gogs/gogs#4894

Because of mysql 5.6 (includes prior versions) InnoDB max index length is 767 bytes, mysql 5.7.7 is up to 3072 bytes.
If some varchar column's length is 255, when the character format is utf-8 needs 2553=765 bytes for index length, It's OK.
But, an utf8mb needs 2554=1020 bytes for index length.

Solutions:

UPGRADE the mysql to 5.7.7(Mariadb 10.2.2)
Change the utf8mb column length to 191 (191*4=764)
Change the utf8mb to utf8
set innodb_file_format=Barracuda, innodb_large_prefix=on and create table using ROW_FORMAT=DYNAMIC or COMPRESSED (default for 5.7.7)

seems to be the same issue

[michaelsrpersaud]

Cannot prepare db

sudo /usr/bin/fleet prepare db --mysql_address=127.0.0.1:3306 --mysql_database=kolide --mysql_username=root --mysql_password=xxxx --logging_debug

Error creating db connection: this authentication plugin is not supported

I've added the port to firewalld, tested log in to mysql, changed the password policy requirements so that there are no special chars in the password

What version of fleet are you using (fleet version --full)?

fleet - version 1.0.9
branch: heads/1.0.9
revision: c1f4c42
build date: 2018-09-18T18:20:21Z
build user: zwass
go version: go1.10.2

What operating system are you using?

CentOS7

What did you do?

Installing as per documentation

What did you expect to see?

Migrations completed.

What did you see instead?

Error creating db connection: this authentication plugin is not supported

[zwass]

@michaelsrpersaud I wonder if this is fixed by #1805. You could try updating to the latest Fleet 2.0 release and see if that works. Otherwise let's open a separate issue to look into this.

[michaelsrpersaud]

@michaelsrpersaud I wonder if this is fixed by #1805. You could try updating to the latest Fleet 2.0 release and see if that works. Otherwise let's open a separate issue to look into this.

Thanks zwass. Still the same issue

Error creating db connection: this authentication plugin is not supported

fleet - version 2.0.0-rc5
branch: modify-user-auth
revision: dab8f21
build date: 2018-09-18T18:59:47Z
build user: zwass
go version: go1.10.2

[michaelsrpersaud]

Could there be any relationship with the OS being Cent7 Minimal (1804)?

[michaelsrpersaud]

So I also did a brand new vm with the full CentOS installer and it resulted in the same issue.

After a lot of hours, I pieced together the solution - http://myitengineer.com/fleet-error-creating-db-connection/

[zwass]

Ah, I assume you must be using MySQL 8? I think we can fix this by updating the MySQL adapter. Next time, please open a separate issue for debugging.

[joshuajon]

FWIW, MariaDB 10.3 on CentOS 7 is working.