Export database tables, fields comments as markup with Ruby on Rails

When you have to export the comments from the database you can use this short snippet to get the schema as markup.

content = ""
database_name = "DATABASE_NAME"
ActiveRecord::Base.connection.tables.each do |table_name|
  content << "h5. #{table_name}\n"
  rows = ActiveRecord::Base.connection.execute("SELECT table_comment 
    WHERE table_schema='#{database_name}' 
        AND table_name='#{table_name}';");
  puts rows.to_a.inspect
  content << rows.to_a.first.first << "\n"

  rows = ActiveRecord::Base.connection.execute("select table_name, column_name, DATA_TYPE, column_comment from INFORMATION_SCHEMA.COLUMNS where 1 AND TABLE_SCHEMA='#{database_name}' AND TABLE_NAME = '#{table_name}'")
  rows.each(:as => :hash) do |row| 
    puts row.inspect
    j = [ row["column_name"], row["DATA_TYPE"], row["column_comment"] ]

    content << "|#{j.join('|')}|\n"
end; ''
puts content

The output should be something like

Table name

table description

column name, type, description

….. the next table

Protected: Query multiple tables

This content is password protected. To view it please enter your password below:

Archiving data – great article

Continue reading

Postgres vs MySQL

Full Text search with MySQL

Goodbye MySQL

I was optimistic that I could make, a price monitoring website to use the fulltext search of mysql. Unfortuntly I have found so many drawbacks that I have to leave this idea. The main source for information for me was:

At first the speed was wonderfull. I was searching in 300k> records apx. 350mb. But then I have to surrender. I cant configure mysql to work as I want. I know that If I spend two days to become expert on C/C++ with Unicode I will success but this is not the case. I want working solution.

The resons that make me not to use mysql for searching:

There is no way to change the default operator by default it is OR. You must parse the user query and rewrite it.

I want automaticaly truncation on all my terms.

There is no way to tell MySQL what are characters, and what are not…..sorry, there are two ways:

1. Touching the sources,

2. Configuring in xmls

No documentation on both. Maybe there is …somewhere.

If you use the default configuration then you will wonder how you get or not get the required results.

Here is a summary of the comands that I use to tweak my mysql server:

SET @global.ft_min_word_len=2;
SET @local.ft_min_word_len=2;

But it is better to have those options in my.conf


To check what characterset is using your mysql:

SHOW VARIABLES LIKE ‘character_sets_dir’;

/usr/share/mysql/charsets | E:MySQL Server 5.1sharecharsets

After that you need to rebuild your index with one of those commands
slow: REPAIR TABLE products QUICK;
slow: myisamchk –recover –ft_min_word_len=3 tbl_name.MYI


Everyday SQL statements



SHOW status where Variable_name like ‘Th%’ or Variable_name like ‘%Connec%’ ;

Check/Repair tables

mysqlcheck -u root -p***** –auto-repair –check –optimize –all-databases


watch -n 0.5 ‘mysql -u root -ppass -e “SHOW FULL PROCESSLIST” | grep Query’

mysql> set profiling=1;
mysql> select count(*) from mysql.user;
mysql> show profile;


pg_dump -U test arachnid_archiv_test –inserts -h –encoding=utf8 -f pgsql.sql

mysqldump -c -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -r $tfile --add-drop-table $DB
mysqldump -c -h localhost -u system3 system3_production -psomepassword -r system3_production.sql  --add-drop-table $DB

Dump for full backup with flushing of the log files

–single-transaction –all-databases –delete-master-logs –flush-logs –master-data=2
> backup_sunday_1_PM.sql

Encoding problems

SHOW VARIABLES LIKE ‘character_set_%’;
curl -i

Creating a database

create database re_production DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Creating a user

GRANT ALL PRIVILEGES ON arachnid_production.* TO ‘payak’@’%’ IDENTIFIED BY ‘payakpassword’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON system3_production.* TO ‘payak’@’%’ IDENTIFIED BY ‘payakpassword’ WITH GRANT OPTION;

mysqladmin -u [user] -h localhost -p password ‘[new_password]’

SQL for a table


mysql tunel to another machine

ssh -N -f -l root -L s1
open port 3307 on the local machine to and login into s1 with root

Replace text

UPDATE script_histories SET cod_script = replace(cod_script,”observer.ArchiveObserver(siteId)”,”observer.ArchiveObserver(siteId, script_id, owned_source_id)”);

Copy from one table to another

DELETE FROM system3_production.articles;
INSERT INTO system3_production.articles SELECT * FROM arachnid_from_screen.articles;

Sessions for Rails

select count(*) from sessions where updated_at < DATE_SUB(now(), INTERVAL 3 DAY);

Binnary loging

Check this attachment here: mysql-presentation on replication etc.

  • See the status of the log files
  • Clean the binary logs instantly
  • Clean binary logs to date/name
    PURGE BINARY LOGS TO ”mysqld-bin.00XXXX’;
  • Configurations in my.cnf
    server-id = 1
    expire_logs_days = 1
    max_binlog_size = 100M


max_allowed_packet = 50M

query_cache_limit=8M #~~~ removed, 1M def. max pozwl. razmer za cache-hirane na edna zajawka
query_cache_size=128M #~~~ 32M, 0 def.

Restoring the maintian Debian User

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;
Replace <password> with your debian-sys-maint password.

Database migration from cp1251 -> utf8

It was time to migrate database from cp1251 -> utf8, I have prepared one nice script which will show you step by step the commands that you should run in order to migrate your database.

Copy the content to or use the commands manually

The script is making echo instead of running the commands because this will give you a chance to fix an error if occurs.

echo "Set the params in the script and you will get the commands that you must run in order to get your database converted"



# ----------------------------------- No need to touch bellow
echo -e  "\n\n# Lets export the source database"
echo "mysqldump -u $USER -p -h $HOST --default-character-set=cp1251 --max_allowed_packet=64M $SOURCE_DATABASE > db1.sql"

# recode latin1..utf8 or what ever...
echo -e "\n\n# Lets convert it"
echo "recode -v -f windows-1251..UTF-8 < db1.sql > db2.sql"

echo -e "\n\n# Lets replace some sql creation statements. maybe you will want to do it manually"
echo perl -pi -e "s/cp1251/utf8/g" db2.sql
echo perl -pi -e "s/utf8_bulgarian_ci/utf8_general_ci/g" db2.sql

echo -e "\n\n# Lets create the target database"
echo mysql -u $USER -p -h $HOST mysql -e \"drop database $TARGET_DATABASE\; CREATE DATABASE $TARGET_DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci\";

echo -e "\n\n# Lets do the initial import"
echo "mysql -u $USER -p -h $HOST $TARGET_DATABASE --max_allowed_packet=64M --default-character-set=utf8 < db2.sql"

As a bonus here is a command with which you can convert an all your html pages to utf8 also

find . -name '*.html' -exec recode -v -f windows-1251..UTF-8 \{\} +

This would recursively find all htmls in the current directory.

How to detect character sets

Migrating Latin1 -> UTF8

We have the mistake to enter all the data in the database (utf8) without setting the right connection encoding (set names utf8). In this case our content is stored as latin1 characters in the utf8 database.

Here is the magic that fixes the encoding found by my bright colleague bl8cki

alter table articles convert to character set latin1;
alter table articles change content content blob;
alter table articles change title title blob;
alter table articles change author author blob;
alter table articles change content content text character set utf8;
alter table articles change title title text character set utf8;
alter table articles change author author text character set utf8;

here is an example

mysql> CREATE TABLE `articles` (`id` int(11) NOT NULL auto_increment,   `title` mediumtext, PRIMARY KEY  (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=10313630 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into articles (title) values('закъсал');
Query OK, 1 row affected (0.00 sec)

mysql> alter table articles convert to character set latin1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table articles change title title blob;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table articles change title title text character set utf8;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from articles;
| id       | title          |
| 10313630 | закъсал        |
1 row in set (0.00 sec)

As the example shows it works with cyrilic (pasted in utf8) !

© 2024 Gudasoft

Theme by Anders NorénUp ↑