My goal was to create a table which is partitioned and partially archived (compressed and read-only mode). I tried to compress only one partition and change the permissions. No success. It was partially achieved by using combination with engine called merge. CREATE TABLE IF NOT EXISTS `test_arch` ( `id` int(11) NOT NULL AUTO_INCREMENT, `txt` TEXT […]
Recently, I had a problem with a client connection (with iis webservices) which blocked other transactions. The client did nothing but sleep and hold a transaction (a problem probably connected with Gap and Next-Key Locks). Eventually I made a fresh install of MySQL 5.5 (which has a very useful table in ‘information_schema’) and created a […]
I use data files (.MYD) to keep some big tables for archiving purpose. When I need some archive data I am using to repair the table with option “use_frm”. mysql> REPAIR TABLE `test_table` use_frm; I think it is the same way how native statement RESTORE TABLE works (which is is deprecated and is removed in […]
Supposing that the partitioning in MySQL is not popular, in my opinion the some part of documentation would be the reason for that. I mean the following part Partitioning Keys, Primary Keys, and Unique Keys. It sounds like a magic story in belles-lettres. If you try to make partition of a table and you see […]
MySQL Server derives its own CRON from version 5.1.6. It is a relevant mechanism like DBMS_SCHEDULER | DBMS_JOB in Oracle or CRON on unix. This feature is perfect for DBA. I use it once it was introduced but now the time has come to classify them conforming to their function. 1. Maintenance (OPTIMIZE, ANALYZE, etc.); […]
I usually install all my database servers from source. This gives me control over server available options and allows me to disable any unwanted options. I am not dependent on OS, too. Typically I used following combination: CFLAGS=”-O3″ CXX=gcc CXXFLAGS=”-O3 -felide-constructors \ -fno-exceptions -fno-rtti” ./configure \ –prefix=/usr/local/services/mysql \ –with-mysqld-ldflags=-all-static \ –enable-assembler \ –with-big-tables \ –with-tcp-port=3306 […]
Option (and column) max_connections in the user grants stored in table user is used to represent the maximum of user connections per hour (conforming to the documentation). In order to fulfill the data in this column I use SQL syntax: mysql> GRANT USAGE ON *.* TO ‘test’@’localhost’ WITH MAX_CONNECTIONS_PER_HOUR 5; Query OK, 0 rows affected […]
Using Digest Access Authentication you probably encountered the problem with effective logout. I solved this with timeout option. My solution to this is as follows: MVC logout action: public function logoutAction() { $YourSessionObject->ForceLogout(True); // the rendered page should contains automatic redirection to page for action /auth/login // e.g. <meta http-equiv=”refresh” content=”2;url=http://domain/auth/login”> $this->Render(); } MVC login […]
One of my slaves works as a bridge between the old and the new structure. The master’s version did not allow to use triggers yet, so only the slave had its own triggers. After upgrade, the master supports triggers and has new ones (different than triggers on slave) on the same tables when comparing to […]
My own keylogger.