Tag Archives: mySQL

Useful Linux Commands 01/2014

a) Bulk rename on the commandline. I needed this one to re-import bulk files for a BI database. All already processed files get a prefix ‘proc_’ in order to know, which files have already been imported into the BI database. Use http://tips.webdesign10.com/how-to-bulk-rename-files-in-linux-in-the-terminal

TEST YOUR EXPRESSION:
$ rename -n ‘s/^proc_order_list_(201312d{2}-d{6}-[A-Z]{3}-[A-Z]{2}).csv$/order_list_$1.csv/’ *.csv

proc_order_list_20131211-025914-AMZ-EU.csv renamed as order_list_20131211-025914-AMZ-EU.csv
proc_order_list_20131211-031130-ENG-DE.csv renamed as order_list_20131211-031130-ENG-DE.csv

DO THE ACTUAL RENAMING:
$ rename ‘s/^proc_order_list_(201312d{2}-d{6}-[A-Z]{3}-[A-Z]{2}).csv$/order_list_$1.csv/’ *.csv

There is a second level of ‘argument list too long’. If you touch it, you need a bash-script like this:

#!/bin/bash

find in/ -type f |
  while read old
  do
  	new=`echo $old | sed "s/proc_//g"`
   	if [ ! -f $new ]; then
	  echo $old '->' $new
	  mv $old $new 
	fi
  done

Or more selectively using a filename pattern:

#!/bin/bash

valid='proc_201403.*'

find in/ -type f |
  while read old
  do
        new=`echo $old | sed "s/proc_//g"`
        if [ ! -f $new ]; then
          if [ [ $old =~ $valid ] ]; then
            echo $old '->' $new
            mv $old $new
          #else
            #echo 'not matched' valid
          fi
        fi
  done

b) Output results from SQL query to file, the quick way – in case you have been using phpMyAdmin for this ;):
$ mysql -u -p -D -e “SELECT …” > quick_result.txt

c) Find directories with count of subdirectories or files (had to use this in order to find cluttered directories that caused problems with a server having software RAID and rsync backups):

$ find . -type d | cut -d/ -f 2 | uniq -c | sort -g

d) Prevent cp/mv/rm – cannot execute [Argument list too long] using find to copy a long list of files when you have long filelists in directories from which you would like to copy/move/remove:
$ cd /var/www/project/incoming/staging;
$ find ../production/data/sources/orderstatus/in/ -name ‘*.xml’ -exec cp {} data/sources/orderstatus/in/ ;

e) Copy all files from multiple backup sub-directories (structure like this 1122/3344/112233445566.xml) into ONE directory:
$ find ./dumpdirs/11* -name “*.xml” -type f -exec cp {} ./flatToFolder ;

f) Count all files in subdirectories with the pattern proc.*.xml:
$ find in/ -name “proc_*.xml” | wc -l

g) Filelist too long using tar and wildcards, use a filelist:
$ find in/ -name ‘*.xml’ -print > tarfile.list.txt
$ tar -cjvf evelopmentOrderstati-20140306.tar.bz2 in/*.xml
$ rm tarfile.list.txt

h) Filelist too long using grep:
Problem:
$ grep -r “4384940″ *
-bash: /bin/grep: Argument list too long
Too many files in your directory

Check:
$ ls -1 | wc -l
256930

Solution:
$ find . -type f | xargs grep “4384940″

Another way to avoid this problem is to substitute the “*” with a “.”:
$ grep -r “4384940″ .

Migrate arbitrary Content to WordPress – e.g. from typo3 tt_content

If you see yourself confronted with the following task, here is a quick receipe: Setup a wordpress instance for us and import all our content from system x. In this case we focus on the import part. ‘System x’ was a somewhat customized typo3 setup. I got myself an ordinatry editors login to identify the articles which had to be migrated. After some fulltext searching and access to phpMyAdmin I identified the area, where I hat to extract the content from. It boiled down to the table tt_content. At this point you can forget about typo3. All articles could be extracted with this SQL query:

SELECT uid, pid, header, bodytext FROM tt_content WHERE header<>'' AND CType='editorialcontent';

Export the content using phpMyAdmin’s export to PHP array functionality. You end up with a file like shown in the screenshot. If you have HTML-content and doubt, if this could be valid php, save the file under export.php and do ‘$ php -l export.php’ – it says ‘No syntax errors.’ – Good.

OK, next you need to get familiar with wp_insert_post(). You need to loop your exported content over it. And that is it. This is how I did it:

  • Create a directory in your wordpress setup ‘wp-import’.
  • Copy/move your export.php array into this directory.
  • And execute the following self-explanatory file located also in your wp-import directory: import.php

That’s it.

Stuff I need to look-up every time

You lost your leading zeros of your varchars importing/exporting your data. You have this example data in your table storagebins:

area = L, row = 1, rack = 2, shelf = 3
and it is supposed to look like this
area = L, row = 001, rack = 02, shelf = 03

Use this SQL to correct this:

UPDATE storagebins SET row=LPAD(row, 3, '0');
UPDATE storagebins SET rack=LPAD(rack, 2, '0');
UPDATE storagebins SET shelf=LPAD(shelf, 2, '0');

VBA Macro to Convert Dates in a Range – the Dirty Way

I used this hacked Excel-Macro to convert all dates in a selected range from a manual data import using Excel to generate all required INSERTs for our MySql database

  • from 03.08.2011 16:51:01
  • to 2011-08-03 16:51:01

In case you wonder, where you can find your Visual-Basic editor and execute your VBA-code… follow these instructions. Here is the code:

Sub PrefixSingleQuoteToRange()
Dim cell As Range
Dim year As String
Dim month As String
Dim day As String

For Each cell In Selection.Cells
' Convert 03.08.2011 16:51:01 to 2011-08-03 16:51:01
year = Mid(DateValue(cell), 7, 4)
month = Mid(DateValue(cell), 4, 2)
day = Mid(DateValue(cell), 1, 2)
cell = "'" & year & "-" & month & "-" & day & " " & TimeValue(cell)
Next

End Sub

If you scratch your head… This is how I ‘generated’ my INSERT-statements:

  • One sheet equals one table.
  • Each line represents a record.
  • For example you have A to L fields paste the following into your cell M:

="INSERT INTO orders (order_id, processing_start_datetime, processing_end_datetime, delivery_datetime, staff_id, store_id, name, phone, comment_order, comment_procession, created_at, updated_at) VALUES ('"&A2&"', '"&B1&"' ,'"&C1&"' ,'"&D1&"' ,'"&E1&"' ,'"&F1&"' ,'"&G1&"' ,'"&H1&"' ,'"&I1&"','"&J1&"','"&K1&"','"&L1&"');"

  • It is a lot of copy and pasting. But in the end all your INSERTS can be copied and pasted into a txt-file to be dumped into your database.

If you know a smarter way to ‘import’ MSSQL data into MySql, please let me know. And I guess there must be many of them ;).

Backup MySql-Database using only FTP

We have just had the case of having ftp access to a site on a shared hosting LAMP webspace but needed also the database and mainly the database scheme to setup our own development-system for the app. This is actually very straight forward using the following snippet uploaded and executed on the webserver:

<?php
exec( 'mysqldump -h dbXXX.1und1.de -u dboYYY -pZZZ dbYYY > dump.sql', $ret );
var_dump($ret);

This is what you do:

  • Find the configuration with the db-credentials in the source of the application and replace
    • dbXXX.1und1.de with the hostname,
    • dboYYY with the username,
    • ZZZ with the password (notice there is NO blank between the option -p and the password!),
    • dbYYY with the database you would like to backup.
  • Save the snippet as file with extension .php and copy it via ftp to the webspace.
  • Find the URL to execute the script.
  • If it worked, there should be a new file dump.sql in the same directory, download it, it contains the db-schema and all data.
  • Delete both files to wipe your traces.

If it does not work try something like “exec(‘ls -la’);”. If you see the contents of the directory you can use the exec-function and the problem is something else.

Happy hacking!

Use of MySql Variables

Case: I was building a CLI script to aggregate data from one MySql database and write results to another MySql database using 2 open db-connections. This way the script’s execution time was in the some-minutes-range… not good! I tried a more effective attempt: I let the CLI script output all necessary statements to drop and create all needed tables and fill them with insert-statements. I called it like this from the command line and diverted output to a file:

$ php aggregatData.php > all_sql_i_need.sql

I could then import the whole thing in one go into my target database like this:

$ mysql the_dbname -h localhost -uMarco -pPass < all_sql_i_need.sql

Insight: The execution-time was way better than my first attempt and took only seconds. I could also have combined the two lines in a shell-script.

The problem with the generated SQL statements was, that the data also contained relations between primary and foreign keys of records and that these IDs were generated at import-time by auto_increment fields. I solved this issue by using MySql variables which can be set and use d again like this:

INSERT INTO gruppen (name) VALUES ('Gruppe 1');
SET @temp_gruppe_id = LAST_INSERT_ID();
INSERT INTO gruppe_eigenschaften (id_gruppe, name, wert) VALUES
     (@temp_gruppe_id, 'email1', 'email1@emailxxx.com');

The variable @temp_gruppe_id is set and can be used in the subsequent SQL statements. It also worked in MySql4.

Here is an example of SQL statements illustrating this mechanism in the above mentioned script. You can cut-and-paste it into your PhpMyAdmin.

Save Serialized Objects to MySql

I have just discovered an issue if you store serialized objects into MySql.

At first I used fieldtype TEXT. If in this case somebody edited another field of such a record with phpMyAdmin and pushed the save button, the stored object in that record got currupted and the object could not be deserialized and instanciated anymore.

We now use the fieldtype BLOB instead, so phpMyAdmin does not give you the chance to edit the fieldcontent. And it works.

Import .dbf File Contents into MySql

I had a bunch of .dbf (source was Lotus Approach) files, at which I wanted to shoot SQL-like queries to generate some meaningful reports. So there were 2 tasks to be accomplished:

  1. Read the data into a tool or database and
  2. formulate and perform the queries to generate results to base your reports on.

As of opening and saving, I first tried MS Access. But some of the tables could not be imported (reasons unknown). Then I tried to open those files with MS Excel. It worked, but I had more than 65536 records per table, so I could not actually use Excel. After some investigation I came across dbftools which promised to help me converting the data into a text-based format.

I simply unpacked the C source and typed ‘make’ from the console and I had a working dbf2txt binary. I created a directory with all the dbf-files in it and hacked together the following simple shell script: convert.sh

#!/bin/bash
for i in data/*.dbf
do
dbf/src/dbf2txt -v "$i" > "$i.txt";
done

It supplied me with all txt-files each containing a table with fieldnames in line 1 and the data in tab-separated format. Nice!

To get this data into MySql, I hacked this little tab2sql script which converts the txt-files into SQL statements. It is called like this:

$ php tab2sql.php data/my_table.dbf.txt > sql/my_table.dbf.txt.sql;

You can then dump all sql-files in the sql-dir into MySql via:

$ for dir in data/*.sql; do mysql dbf_import -h localhost -u root < “${dir}”; done;

Thanks Frank Koormann for the dbftools!

Sidenote: If you have big tables and you plan to work with joins… Do not forget to set field-types and indices properly after the import! This could otherwise stress your machine.