MySQLDump for federate tables

Problem Description

As you probably already know federate tables have an special usage. They are not less flexible at manipulate, less performance and hard to join, you also rather have problems to make a backup for them, especially if you want to filter columns and such of things. I ran into this handicap too and below I describe how I've jumped across it.

Bases concepts

Probably you must know though concept is needed to understand what will be explained below. MySQL has the option of running queries by Mysql Cli via command line using just '-e', like:

mysql -u{user} -p{pass} {database_name} -e "{query}" > output.txt

This command also has the option to redirect the output into a file, having kind of the same structure for select queries like "INTO OUTFILE", separated by TAB and escaped by ".

Decorating our output

Lets play a bit more with the above command.

1. Skip column names

In my case I didn't need to use the column names to build my "fake" dump file, so you could add to the previous command the following argument to skip them.

--skip-column-names

2. Escaping row values

Due to some column values might contain  special characters which would provoke your dump file breaks, I recommend to escape every column on the SELECT query by the usage of QUOTE method, like:

mysql -u{user} -p{pass} {database_name} -e "SELECT QUOTE(col1), QUOTE(col2)..."

3. Sed Command Line

Output from the previous command will have an structure like the following one:

"value1row1"     "value2row1"     "value3row1"    "value4row1"
"value1row2"     ......

.....

and we likely want to something like this:

INSERT INTO {table_name} VALUES (value1row1, value2row1,value3row1,value4row1), (value1row2, ...) .....

How could we make it without difficulty and trying to achieve the closest way a original mysqldump works? My solution was to use sed command therefore we could parse the original output into a usual mysqldump file.

{ mysql command} | sed 's/\\\\/\\/g' | sed "s/\t/,/g;s/^/(/;s/$/)/;s/\n//g" | sed "s/\(.*\)/\1,/;1iINSERT INTO tmp_import VALUES " | sed '$s/.$/;/g'  > dump.sql

If you want to understand a bit more how to interpret those sed command regex I do it below:

1. sed 's/\\\\/\\/g'
Replace every double escaping which could have been produced by QUOTE function.

2.  sed "s/\t/,/g;s/^/(/;s/$/)/;s/\n//g" 
Replace every TAB into a comma, include parentheses at the beginning and the end of every line and finally add a semicolon at the last position of last line. 

3. sed "s/\(.*\)/\1,/;1iINSERT INTO tmp_import VALUES "
The easiest one, just add at the beginning "INSERT INTO tmp_import VALUES"


I hope that you may take advantage of all this work, at least for me it was the best way to create backup files from federate tables.

Thanks for reading

Problems updating filesystem on ArchLinux (Error of GRUB: Minimal BASH)

Hi, my hopeless friend. If you are reading this post means that you are desperate because you OS crashed and you can't explain how this could have happened, maybe about to give up of using ArchLinux, hold out !

Two weeks ago, when I decided to upgrade my wonderful OS (5.4Gb) as it is usual when you didn't do it for two long months. I was scary but I went ahead. Everything seemed okay, but as long as I restarted my laptop, just second after restarting an ERROR on GRUB boot showed up with a "Minimal Bash console".

What I did as soon as this came out was taking my old laptop and starting to look up on google about the issue. After a lot of goes I found the key. To sum up, we have to restart our ArchLinux by grub commands. Following steps describe how I got it:

Step1: Choose the OS kernel and which partition is stored (remember tab key shows you suggestions)

$ kernel /vmlinuz##PUSH TAB##   root=/dev/sdaX ro

Step2: Launch linux image file:

$initrd /initranfs##PUSH TAB##.img quiet spash

Step3: Boot (good luck!!! xD)

$boot

In case you hit the right partition for your OS, this should be running correctly and then the last step left is going to be reinstall your grub package.

In case you didn't get you OS running, another mode is set up. On this mode you are able to make some probes which will help you to find out why your OS didn't run. On this new console mode you can mount partitions using the command "mount", and within dev folder you have every partitions are installed on your machine, then only mount one by one until you find the correct one, restart your computer, probe again above steps(1,2,3) and good luck ;)

JDBC Driver Connection Problem - ArchLinux

Hi people,

today I found a error on my squirrel-sql software when I was trying to access to my localhost database using com.jdbc.mysql.Driver.

The problem came up using the following message:

"...com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure ...."""

After a really long research, doing on other many similar software, re-setting each configuration files on my computer (ArchLinux), at the end I've figured out how that error came and how we could solve it.

To check whether your problem has the same reasons that mine was, execute the following line, when mysqld service is still running:

Input: $ sudo nmap -p 3306 localhost
output:
PORT     STATE SERVICE
3306/tcp close  mysql

If this is your case, that has a easy resolution, just you should comment the following line on your my.cnf file:

.....
# skip-networking
.....

And the the output for last command will be:


PORT     STATE SERVICE3306/tcp open  mysql


PROBLEM SOLVED !!!

Updating Gnome3.6 on ArchLinux

Hi people,

yesterday I upgraded my cool Archlinux and one of upgrading was on Gnome desktop (versión 3.6).

Problem 1: Description

Everything was right during downloading and installation process, including the typical warning and these kind of stuff. Then, when I restart my OS I realized that gnome session doesn't lunch at first time, I restart again and the same crap, I just got my tty console. After checking every logs file on my /var/logs/ folder and making sure none of them has not any errors I decided to figure out whether someone got the some problem and I found out :

Problem 1: Solved

# Migrates to systemd booting (just install the following package)

Globish

Most of people around the world are trying to learn other language as it is English. But now, the linguists have figured out that all of us are designing shape to a new language, it is known as Globish. This new term is defined as a basic style of English which people from countries where their language is not English. Currently, Globish is used to communicate between million of people on the world.

If you want to get more knowledge about this subject, you can click on link.

Deploy Virtual Enviroment

When a new application is deployed this could need a specific version of libraries for example a old version of python. Also, there are many advantages using a environment deployment isolate. Below it is a manual about how make a virtual environment for python.

First Step : Install pip

In a terminal we install via "pacman" the tool python-pip to make the installation of python packages easier.

$ sudo pacman -S python-pip

Second Step : Install virtualenv

Now, virtualenv is installed using python-pip.

$ sudo pip install virtualenv

Thirst Step : Install virtualenvwrapper

Virtualenvwapper is a set of extensions to Ian Bicking’s virtualenv tool. The extensions include wrappers for creating and deleting virtual environments and otherwise managing your development workflow, making it easier to work on more than one project at a time without introducing conflicts in their dependencies.

$ sudo pip install virtualenvwrapper

Fourth Step: Configure our first virtualenv

In this step we must define a folder to install the virtual environments and to keep the setting up files.  Once every variables and folder have been created, the last step is adding a folder and create the environment with python2.7 and don't define any news folder for the packages of the environment.

$ export WORKON_HOME=$HOME/.virtualenvs
$ source /usr/local/bin/virtualenvwrapper.sh
$ mkdir -p $WORKON_HOME

$ mkdir -p $WORKON_HOME/env1
$ mkvirtualenv -p python2.7 --no-site-packages --distribute $WORKON_HOME/env1

When in the console we'd same like that (env1) $, we are inside of virtual environment and now we can install every thing are needed to develop our app. For example we start with django.

(env1) $ pip install django

(env1) $ pip install mysql-python

Sixth Step: Restore in virtualenv
When the terminal start, the environment is not active, if we want to use it again, we just have to run the following command from anywhere in the console.

$ workon env1