Category 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″ .

Use GROUP BY to concatenate strings in MySQL?

I just had the case with mySQL where I needed to aggregate via GROUP BY but at the same time concatenate the content in the body-field.

The test result (see image) came using:

SELECT m.template_id, m.site, m.path, m.name, tf.body FROM menu AS m
LEFT JOIN template_fields AS tf
ON tf.template_id=m.template_id
WHERE m.type='template' AND m.inactive=0

The solution to get the concatenated content:

SELECT m.template_id, m.site, m.path, m.name, GROUP_CONCAT(tf.body SEPARATOR ' ') AS body FROM menu AS m
LEFT JOIN template_fields AS tf
ON tf.template_id=m.template_id
WHERE m.type='template' AND m.inactive=0 GROUP BY m.template_id

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 ;).

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.

Concat many Files on Command Line into One

Case: I had many .sql-files from a backup containing one table-dump each which should be loaded into another MySql-DB.

Simply using phpMyAdmin would not have worked with regard to the size of the tables. So I was thinking about a way to do it on the commandline under Linux. A foreach-solution would have caused me to enter the MySql password for each file… which was what I wanted to avoid since the DB contained 70 tables.

Solution: I concatenated all .sql files into one and imported it into MySql in one go. Supposed your present working directory is the directory containing all .sql-files do the following:

cat *.sql >> ../complete_dump.sql;
cd ..;
mysql my_database -h mysql_host -u myuser -p < complete_dump.sql;
password: ****

And your’re done!

Import Data into InnoDB Tables disregarding Foreign Key Constraints

I am currently migrating MySQL databases. One DB uses InnoDB tables with many foreign-key constraints. In this case you can not just dump your CREATE-TABLEs and INSERTs into an empty database. This would lead to many relation violations and incomplete imports, since records are not imported in ‘the right order’ by the dump.

To prevent errors you can temporarily switch off this feature. Like this:

SET FOREIGN_KEY_CHECKS = 0;

<do your import without errors>

SET FOREIGN_KEY_CHECKS = 1;

mySQL mass update with REPLACE()

A nice shortcut I came across to update content in a mySQL-table without writing a script to do it – which was the first thing that came to my mind…

Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as we switched the overall site domain from ‘www.mydomain.de’ to ‘info.mydomain.de’? Much of the content seemed to have gone.

This snippet was the elegant way of solving this directly in mySQL without headaches:

UPDATE freepages SET body=REPLACE(body, 'www.mydomain.de', 'info.mydomain.de');

or in case you forgot to switch off magic quotes and have all sorts of noisy data like this “Mary\\\\\’s house” with a random number of backslashes. Use this multiple times:

UPDATE applicant_publication SET title=REPLACE(title, "\\'", "'");
UPDATE applicant_publication SET authors=REPLACE(authors, "\\'", "'");
OR
UPDATE applicant SET research_skills=REPLACE(research_skills, '\\"', '"');

Of course you need one line for each field with that problem.

Sweet!

Macro to export data from Excel to mySQL

I have been struggeling many times to do initial filling of databases for projects – especially if the data is a deliverable of a customer and the database structure is already fixed. So I built a VBA macro that generates SQL statements for creation of tables and data inserts from a given XLS-file. The idea is to build a ‘template’ of tables (=worksheets with fieldnames in the column headings) which have to be filled with data by the customer. As soon as this file is returned you execute the macro and voilà you have generated SQL statements which you can easily be feed into phpMyAdmin.

You can understand it as follows:

  • The XLS-file represents one database with structural information and content.
  • Worksheets represent the tables (their name being the tablename). This results in a CREATE TABLE statement.
  • Headings (the first line, their name being the fieldname) of each worksheet for as many consecutive cells as you need represent the fields of that table. This results in the fields created in the CREATE TABLE statements.
  • Lines from line 2 on downwards until the first empty line is met will be converted to INSERT statements, INSERTS INTO the fields of each cell’s column.

Puzzled? Check out the examples, they should be self explanatory:

VBA macro source

Excel file containing example ‘tables’/worksheets to be exported to SQL and the macro

Resulting SQL saved to C:exported.sql

The VBA snippet has to be added as a ‘module’ in the Excel VBA editor in the XLS-file holding your structure and data and can be saved with it as a macro. Triggering the macro will then export your SQL statements. You can see this from the example XLS-file above.