If you do a fair amount of SQL, then every now and then, you’ll likely run into a situation where you need to delete something from a table; but need to also reference that table in the query as well.
The trouble is MySQL won’t let you do this, in many circumstances. Fortunately, there is a simple workaround.
delete from tableA where id in ( select b.id from tableB b join tableA a on a.tableb_id = b.id );
The above query would throw an error similar to the following:
ERROR 1093 (HY000): You can't specify target table 'tableA' for update in FROM clause
We can skirt this limitation by nesting the subselect inside of another select statement. Then it will work just fine.
delete from tableA where id in ( select aId from ( select b.id as bId from tableB b join tableA a on a.tableb_id = b.id ) as apt );
You’ll get output to indicate that the query is successful.
Query OK, 183 rows affected (0.53 sec)
This saved me a bunch of time and kept me from having to rework my query completely, so I am sharing it with you!
I occasionally use RSYNC to synchronize large directories of files between servers. This is especially useful if you’re moving a client from one server to another and they have alot of static files that are always changing. You can copy the files and sync them up, all with RSYNC and if your connection gets cut off, it will start where it left off. It will also grab changes to files that have already been RSYNCd.
I ran into an issue with RSYNC recently, wherein the RSYNC process was running in the background; but was terminating due to errors similar to the following. These connections were probably related to the slow and unstable connection to the remote server.
rsync: writefd_unbuffered failed to write 998 bytes to socket [sender]: Broken pipe (32) rsync: connection unexpectedly closed (888092 bytes received so far) [sender] rsync error: error in rsync protocol data stream (code 12) at io.c(600) [sender=3.0.6]
Given that I was transferring files through a relatively bad internet connection and received this error a half dozen times over a couple of days, I decided the best way to handle it, would be to write a cron script. This cron script should check for the RSYNC process and start it if it isn’t running.
Customize this script for your own purpose, to check for your RSYNC process and start it if it isn’t running.
#!/bin/bash echo "checking for active rsync process" COUNT=`ps ax | grep rsync | grep -v grep | grep -v rsync_check.sh | wc -l` # see how many are running echo "there are $COUNT rsync related processes running"; if [ $COUNT -eq 0 ] then echo "no rsync processes running, restarting process" killall rsync # prevent RSYNCs from piling up, if by some unforeseen reason there are already processes running rsync -avz -e "ssh" firstname.lastname@example.org:/mnt/syncdirectory/ /home/ccase/syncdirectory/ fi
Save the script in the appropriate cron directory, or add it to the cron.d directory and put a crontab entry in, to run it at the desired interval. This will have it run every 10 minutes.
*/10 * * * * ccase /etc/cron.d/rsync_check.sh
No More Worries
Now you can move onto other things, with the knowledge that your RSYNC will not just fail and leave the work undone. It probably wouldn’t hurt to check on it at first and from time to time; but there’s alot less to worry about!
Linux makes it relatively easy to mount shared drives either manually, at the command line, or automatically, by configuring an entry in /etc/fstab. Here is the basic syntax of our mount command.
[ccase@midas ~]$ sudo mount -t cifs -o username=<share username>,password=<share password>,<additional options> //<name or ip of server>/<share name> <folder to mount to>
Here is an example of mounting our CIFS share to a folder named myshare. We are using the option ro to mount the share read only.
[ccase@midas ~]$ sudo mount -t cifs -o username=admin,password=secret,ro //192.168.1.200/myshare myshare
If we want to make this automatic, it can easily be configured in /etc/fstab/ to mount after the network comes up. Here is the basic syntax you would use in /etc/fstab/
//<name or ip of server>/<share name> <folder to mount to> cifs username=<share username>,password=<share password>,_netdev,<additional options> 0 0
Here is an example of mounting our CIFS share automatically to /mnt/myshare/. We are using the option _netdev, to tell it to attempt the mount only after the network has come up and ro, to mount the share read only.
//192.168.1.200/myshare /mnt/myshare cifs username=admin,password=secret,_netdev,ro 0 0
If we want to get just the date modified, for a file, in a format of our choosing. This can be done with a utility called stat.
The syntax is as follows:
stat -f <format> -t "<timestamp format>" <path to file>
In this example, we are printing just the date created in the format YYYYMMDD_HHMMSS.
stat -f "%Sm" -t "%Y%m%d_%H%M%S" filename.txt
We are using the –f “%Sm flag to specify that we want to print out only the date modified. The –t “%Y%m%d_%H%M%S” sets the date format.
In my example, the output was:
This translates to November 30, 2012 at 18:02:21.
Using the Linux Command Line to Find and Copy A Large Number of Files from a Large Archive, Preserving Metadata0
One of my recent challenges is to go through an archive on a NAS and find all of the .xlsx files, then copy them; preserving as much of the file metadata (date created, folder tree, etc) as possible, to a specified folder. After this copy, they will be gone through with another script, to rename the files, using the metadata, where they will then be processed by an application, which utilizes the name of the file in its process.
The part I want to share here, is finding the files and copying them to a folder, with metadata preserved. This is where the power of the find utility comes in handy.
Since this is a huge archive, I want to first produce a list of the files, that way I will be able to break this up into two steps. This will produce a list and write it into a text file. I am first going to run a find command on the volume I have mounted called data in my Volumes folder.
find /Volumes/data/archive/2012 -name '*.xlsx' > ~/archive/2012_files.txt
Now that the list is saved into a text file, I want to copy the files in the list, preserving the file metadata and path information, to my archive folder. The cpio utility accepts the paths of the files to copy from stdin, then copies them to my archive folder.
cat ~/archive/2012_files.txt | cpio -pvdm ~/archive
I ran into an issue recently, where an existing log4j.xml configuration file was built into a jar file I was referencing and I was unable to get Java to recognize another file that I wanted it to use instead. Fortunately, the solution to this problem is fairly straightforward and simple.
I was running a standalone application in linux, via a bash shell script; but this technique can be used in other ways too. You simply add a parameter to the JVM call like the example below.
So the syntax is basically:
java -Dlog4j.configuration="file:<full path to file>" -cp <classpath settings> <package name where my main function is located>
Lets say I have a file named log4j.xml in /opt/tools/myapp/ which I want to use when my application runs, instead of any existing log4j.xml files. This can be done by passing a JVM flag –Dlog4j.configuration to Java.
Here is an example:
java -Dlog4j.configuration="file:/opt/tools/myapp/log4j.xml" -cp $CLASSPATH my.standalone.mainClass;
With that change, as long as your log4j file is set up properly, your problems should be behind you.
I started using SD cards recently and had a heck of a time using it on my laptop at first. I tried using my 32 GB SDHC card in the USB adapter, to no avail, then I found the SD slot and it still did not work either. It turned out that the driver was not loading by default. This is a common problem in Linux, as the devices that are less commonly used are not going to always “just work”. You have to often get the driver yourself and install it.
Getting it working was not trivial, I had to figure out which driver to get, which took some guesswork. It turns out that this laptop uses a Realtek RTS5229 for its SD card interface. I found this information with lspci. (more…)
An idea came to mind the other day. I was pondering small ventures I could possibly spin up, to make a few dollars and in the process provide something of value for low cost. The possibility of starting a Friendica–based service, wherein a user can start their own SSL-secured self-contained Friendica node, via a web-based service front-end, came to mind.
The goal of this service would be to provide an inexpensive and easy way, for non-technical individuals to start their own personal Friendica nodes, complete with their own subdomain (possibly their own domain, as a later, more advanced feature) and complete SSL protection.
As I talk to people who are not familiar with Friendica, I notice a recurring theme, that they find it interesting; but getting something started is possibly too confusing or too technical for them. I want to offer something that eliminates many of the hurdles new users would face; things they typically don’t want to deal with, while providing them an environment that they can be comfortable interacting in and fully supported. (more…)
Friendica is a powerful tool, not only for social networking; but also for a variety of other purposes. The usage I would like to discuss today is content aggregation.
There are many ways to aggregate content on the web; but Friendica has something that none of the others have. Friendica not only allows you to aggregate content; but it also allows you to integrate that content with social networking content from a variety of sources. Generally aggregators only aggregate RSS feeds; but Friendica has been customized to handle a variety of different kinds of content, not just RSS feeds.
This means you can look at all of the latest posts from your favorite websites, via their RSS feeds, while also seeing the latest from your social networks (Friendica, Twitter, Identica, Youtube, Facebook, etc). This is a valuable tool for efficiently keeping up with the flow of information from websites you follow and social networks that you are part of.
The process of integrating the content from websites you want to follow, is similar to how you might add a contact to your social network. In fact, on Friendica, the posts from websites you follow, appear in the same way as post from your social networks.
I have recently started using a Toshiba P870 laptop and decided to install Linux Mint 13 Maya (Cinnamon Edition) on it, due to its ease of use and overall security soundness.
Being as the Toshiba P870 is a relatively new laptop, with some components’ drivers not having been included in the installation files of Mint, it has been a little tricky. I’m sharing this, for those who want to install mint on the P870 or similar laptops. This should save you a couple hours of searching. It will get you the drivers you need and get you up and running.
We’re going to discuss howto:
- Download, Burn and Run the Linux Mint Installer
- Install the missing network drivers, both WIFI and Ethernet, so you can connect to the internet
- Fix the internal sound problem that causes the internal speakers not to produce any sound
- Update: Installing the SD Card driver - since this article was originally written, I also figured out that the SD card driver needs to be installed as well.