One instance and one storage with indices is neither secure nor effective. That is why I created my replication for Sphinx Search . To put it shortly, my Sphinx’s architecture constists of tree indices and MVA (multivalue attributes stored in mva_updates_pool) – that is why I cannot use RT index. My first idea was to create independent Sphix index + [...]
Sometimes, I had encountered the problem with getting the full query which was divided into many rows (as a result of developer scripting or a literal containing NEW LINE). To obtaing the full query (I needed mainly REPLACE/INSERT) I use the following trick: #!/bin/bash # get the content w/o white NL, EOL. Input=$(cat < [...]
The gathering of the general logs would adversely affect the performance, it would take the significant space on your devices. The continuity of this log is neighter used. But sometimes it would be nice to see what is going on at runtime – I used to do the fallowing hack: # MySQL configuration file my.cnf [...]
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 \ ./configure \ -fno-exceptions -fno-rtti” ./configure \ –prefix=/usr/local/services/mysql \ –with-mysqld-ldflags=-all-static \ –enable-assembler \ –with-big-tables [...]
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 [...]