Archive for the ‘ MySQL ’ Category
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 < /tmp/my.file) […]
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 […]
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 […]
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 […]
New challange – data warehouse for the purpose of specific customer (not sales aggregation). Before I started to read know how about data warehouse I prepared my own plan. The result of basic structure was almost fantastic, I only had to borrow the names for the elements from official sources. The type of data warehouse […]