Over on Two simple ways to version control your MariaDB schema changes I learned a neat hack you can use to check for database schema revisions by reporting off the binlog.
The example command given was:
$ mysqlbinlog --no-defaults -R -u foo -p -d foodb -h db.home.arpa mysqld-bin.000001 > foodb.txt
My copy of MySQL Workbench doesn’t play 100% nice with my MariaDB install, so I checked out the Database Workbench software recommended by MariaDB and in the end I downloaded a trial version. It runs okay under Wine, so gonna check it out.
Here’s some good info for configuring ODBC for MariaDB on Ubuntu for use from LibreOffice Base: LibreOffice Base and MariaDB. The same article also has some info about how to use ENGINE=CONNECT for importing JSON from web services.
So my `mysqldump` script wasn’t backing up views. I found a good solution using information_schema on Stack Overflow: Backing Up Views with Mysql Dump.
I had this problem which is explained in greater depth over here.
Here’s some info about Dovecot SASL. I integrate this facility for Postfix authentication too. So my SMTP system has a dependency on my IMAP/POP3 system. In the backend it’s a MySQL database…
Here’s some good documentation on PHP prepared statements and stored procedures including how to call stored procedures with output parameters.
You can disable foreign key checks with
SET foreign_key_checks = 0;
And you can disable unique index checks with
SET unique_checks = 0;
I found this great MySQL session over here. It has a clever approach for creating a big table, and shows how to invoke shell commands from the `mysql` client.
USE test; SET GLOBAL innodb_file_per_table=1; SET GLOBAL autocommit=0; -- Create an uncompressed table with a million or two rows. CREATE TABLE big_table AS SELECT * FROM information_schema.columns; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; INSERT INTO big_table SELECT * FROM big_table; COMMIT; ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment; SHOW CREATE TABLE big_table\G select count(id) from big_table; -- Check how much space is needed for the uncompressed table. \! ls -l data/test/big_table.ibd CREATE TABLE key_block_size_4 LIKE big_table; ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed; INSERT INTO key_block_size_4 SELECT * FROM big_table; commit; -- Check how much space is needed for a compressed table -- with particular compression settings. \! ls -l data/test/key_block_size_4.ibd