[Feature]: Add MySQL 8 support

[jsiddall]

Type of Feature

✨ New Feature

OpensourcePOS Version

opensourcepos 3.3.9

Description

MySQL 8 has been out for almost 6 years now but still isn't supported by OSPOS. Would be good to get this added so it can be installed on modern servers.

Additional Information

Support should be pretty easy with only a couple of things broken. The install documentation here:

https://github.com/opensourcepos/opensourcepos/wiki/Getting-Started-installations

Says to create the database with:

mysql -u root -e "CREATE SCHEMA ospos;CREATE USER 'admin'@'%' IDENTIFIED BY 'pointofsale';GRANT ALL PRIVILEGES ON ospos . * TO 'admin'@'%' IDENTIFIED BY 'pointofsale' WITH GRANT OPTION;FLUSH PRIVILEGES;"

But requires this command instead (note the change to the database username):

mysql -u root -p -e "CREATE SCHEMA ospos;CREATE USER 'ospos'@'%' IDENTIFIED BY 'pointofsale';GRANT ALL PRIVILEGES ON ospos.* TO 'ospos'@'%' WITH GRANT OPTION;FLUSH PRIVILEGES;"

This statement also seems to be needed in the my.cnf:

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Verify you searched open requests in OpensourcePOS

• I agree I have searched Open Requests

[SteveIreland]

I went through the "start from scratch" process on the new ci4 seasoned master branch, using MySQL 8.3 as the database. I followed my usual steps (I have not yet incorporated the above mentioned steps). The database.php script failed when building a constraint on the inventory table. I hadn't noticed that the employees table is uniquely identified by employee name instead if the person_id. Version 8 of MySQL must be, by default, requiring foreign keys to be unique and the person_id is not unique. I believe this should be easily corrected by making the person_id on the employee table unique. I would have thought it would be the primary key and the name would have been an alternate unique key.

It's odd that we have constraints that reference the employees table using the person_id but the primary key is the employee name. I could expand the effort to correct that oddity if there is general agreement. Otherwise, I'll just fix the uniqueness requirement.

[jekkos]

Great to see you back @SteveIreland Quite a surprise to me that the employee name is the primary key here. I agree to change this, normally it should not introduce any issue as the person_id is using the autoincrement and should be more unique then the name which is assigned by the user.