There are no good mysql connectors for Common Lisp, whatever you decide to use, you're not going to have a good time. clsql is pretty much the best we've got, and since I had such a delightful experience configuring it myself, I hope to smooth the process of getting this shitware up and running with my own little blogpost here.
Connecting to mysql with clsql looks something like this
(clsql:connect
'("host" "database" "username" "password" &optional "port") ;; connection-spec
:database-type :mysql) ;; Defaults to clsql:*default-database-type*
clsql builds ffi libraries the first time you try to connect to a database, and you can get some pretty arcane error messages if you don't have the right shared libraries installed already. These are (hopefully) packaged by your distro already, in Gentoo you want
$ sudo emerge --ask dev-db/mariadb-connector-c
in openSUSE it's
$ zypper in mariadb-connector-odbc
if you're on Debian or Ubuntu then God save your soul, etc.
Despite mariadb being a drop-in replacement for mysql, the clsql developers have made little to no effort to actually support it, to the point where mariadb's versioning system isn't even accounted for when loading the libraries, causing clsql to just error out for absolutely no reason. (actually, this shitware doesn't even 'support' modern versions on mysql because the version numbers have increased above what they check for, but they load fine as well)
We can modify the version checking script, to fix this, which you
can find here:
~/quicklisp/dists/quicklisp/software/clsql-*-git/db-mysql/mysql-client-info.lisp
(If your quicklisp is installed in its default directory you can
paste this straight into a terminal or VIM or some such and it'll
expand itself, which is kinda neat.)
If we add the top three lines just before the bottom two it'll stopping giving you stupid error messages when you try and connect.
((and (eql (schar *mysql-client-info* 0) #\1)
(eql (schar *mysql-client-info* 0) #\0))
(pushnew :mysql-client-v6 cl:*features*))
(t
(error "Unknown mysql client version '~A'." *mysql-client-info*)))))
After (finally) connecting, when we first query the database we might get an error like this:
debugger invoked on a BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE in thread
#<THREAD "main thread" RUNNING {B3E2151}>: Illegal :UTF-8 character starting at position 18.
We can identify the issue like so:
(defvar charsets
"SELECT VARIABLE_NAME, SESSION_VALUE \
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES \
WHERE VARIABLE_NAME LIKE 'character_set_c%' \
OR VARIABLE_NAME LIKE 'character_set_re%' \
OR VARIABLE_NAME LIKE 'collation_c%';")
;; CHARSETS
(clsql:query charsets)
;; (("COLLATION_CONNECTION" "latin1_swedish_ci")
;; ("CHARACTER_SET_CONNECTION" "latin1")
;; ("CHARACTER_SET_RESULTS" "latin1")
;; ("CHARACTER_SET_CLIENT" "latin1"))
;; ("VARIABLE_NAME" "SESSION_VALUE")
These are actually the default collation and character sets in MYSQL; Smarter mysql drivers would set their charset and collation to utf8 by themseleves, but not clsql! (the collation is set to latin1_swedish_ci by default because David Axmark, one of the co-founders of MYSQL, is Swedish. No, really.)
We can fix this on the database connection level by setting names:
(clsql:execute-command "SET NAMES 'utf8mb4'")
And now when we query charsets again they're utf8!
(clsql:query charsets)
;; (("COLLATION_CONNECTION" "utf8mb4_general_ci")
;; ("CHARACTER_SET_CONNECTION" "utf8mb4")
;; ("CHARACTER_SET_RESULTS" "utf8mb4")
;; ("CHARACTER_SET_CLIENT" "utf8mb4"))
;; ("VARIABLE_NAME" "SESSION_VALUE")
However, this has to be set per-database connection. Really, we want to be using utf8 by default, because it's fucking 2020 and utf8 has been the standard character encoding for over a decade.
You can find you mariadb config files with this shell command:
$ mysql --help --verbose | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
... then add add these values under their respective headings in one of those files and hey presto, you're charsetting like it's 2008!
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect= 'SET NAMES utf8mb4'
character-set-server = utf8mb4
Now the charset for our initial connection should be set to utf8, and we're free to hack away~
I hope you found this somewhat helpful, thanks for reading~
These links were useful in learning to deal with these piles of trash: