MySQL: Drop all tables with prefix

I use the mysql command line tool to administrate my MySQL. I needed a quick way to drop all tables with the following prefix.

SELECT CONCAT('DROP TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix%' and TABLE_SCHEMA = 'dbname';

Then all you have to do is take a text editor and replace all the “|” signs around the result.

PS: As you can see the query can be modified to get all tables with suffix, or all tables in a schema, or TRUNCATE all tables in the criteria.

Coldfusion Cfform bug/error: “_b is undefined”

While coding a HTML form using Coldfusion’s CFForm, I encountered a bug that threw a javascript error while submitting it.

"_b is undefined" from cfform.js

around line 3:

_CF_hasValue=function(_b,_c,_d){
if(_c=="TEXT"||_c=="FILE"||_c=="PASSWORD"||_c=="CFTEXTAREA"||_c=="TEXTAREA"||_c=="CFTEXTINPUT"||_c=="DATEFIELD"){
if(_b.value.length==0){
return false; 
}else{
if(_d){
str=_b.value.replace(/^\s+/,"").replace(/\s+$/,"");
if(str.length==0){
return false;
}
} 

One of the problems with errors while submission is that it happens very quickly and the browser redirects. So the user doesn’t see the javascript error. It also breaks execution of any (onsubmit/event) javascript code.

I went through my cfform to find any problems code. However at the end, I noticed that the cfform tag was missing the “name” attribute, and by simply adding the “name” attribute I was able to avoid the error.

This is one of the reasons I do not like ColdFusion’s helpers, because the errors that they sometimes generate are very misleading and not documented.

Yum install rpm files manually

I love yum on CentOS/Red Hat/Fedora. It saves so much of my time installing software on servers. Sometimes, the need comes when I cannot find the package inside the usual “safe” repositories, such as RHEL repos and RPMForge. So I search it and find it on pbone, rpmfind, or epel packages. I have always tried installing with rpm, but often end up with dependencies problems.

Example:

# rpm -Uvh icecast-2.3.2-4.el5.x86_64.rpm 
warning: icecast-2.3.2-4.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 217521f6
error: Failed dependencies:
        libogg.so.0()(64bit) is needed by icecast-2.3.2-4.el5.x86_64
        libspeex.so.1()(64bit) is needed by icecast-2.3.2-4.el5.x86_64
        libtheora.so.0()(64bit) is needed by icecast-2.3.2-4.el5.x86_64
        libtheora.so.0(libtheora.so.1.0)(64bit) is needed by icecast-2.3.2-4.el5.x86_64
        libvorbis.so.0()(64bit) is needed by icecast-2.3.2-4.el5.x86_64

Then I would have to go hunt down each of those dependencies. Too much time wasted. If only you could use yum to resolve those “resolvable” dependencies (using rpmforge/rhel repositories). Well you can:

# yum localinstall icecast-2.3.2-4.el5.x86_64.rpm
    However, there are two caveats:

  1. Sometimes yum can’t find the dependencies, so you must search those out. (obvious)
  2. You probably haven’t imported the gpgcheck for the rpm package that you are trying to download.
    Example:

    warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID 217521f6
    Public key for icecast-2.3.2-4.el5.x86_64.rpm is not installed

    So you can either import it, or you can ignore the gpg check by doing this

    # yum --nogpgcheck localinstall icecast-2.3.2-4.el5.x86_64.rpm

Django/Python: UnicodeDecodeError error printing Youtube unicoded data

I was having a problem printing Youtube’s Unicode data using my print method:

print "<p>Video: desc=%s</p>" % (vid.desc)

I’m not well versed with Unicode data, so I was just able to brute force out of this problem, and get rid of the UnicodeDecodeError “ordinal out of range”, by doing the following:

print "<p>Video: desc=%s</p>" % (unicode(vid.desc,'iso-8859-1'))

PS: My database information is in utf-8 format. So, in my understanding, this is converting that utf-8 data into iso-8859-1 to show to the users.

Installing VirtualMin – “Error: Missing Dependency: libGeoIP.so.1 is needed by package proftpd”

On my Red Hat 5.5 box (similar to CentOS), I was trying to install Virtualmin.

I got two errors while installing.

#1.
-This system is not registered with RHN.
RHN support will be disabled.
\Error: Missing Dependency: ruby-rdoc is needed by package rubygems-0.9.2-1.el5.noarch (virtualmin)
Error: Missing Dependency: perl(XML::Parser) is needed by package perl-XML-Simple-2.14-4.fc6.noarch (virtualmin)
Error: Missing Dependency: libdistcache.so.1()(64bit) is needed by package 2:mod_ssl-2.2.3-43.3.vm.x86_64 (virtualmin)
Error: Missing Dependency: libaprutil-1.so.0()(64bit) is needed by package 1:httpd-2.2.3-43.3.vm.x86_64 (virtualmin)
Error: Missing Dependency: libnal.so.1()(64bit) is needed by package 2:mod_ssl-2.2.3-43.3.vm.x86_64 (virtualmin)

This simply meant that I had to run rhn_register to register using the Red Hat login/password that I had received. Because you cannot run yum/update without registering with RHN, unlike CentOS which can update without registering. Simple enough.

#2.
Error: Missing Dependency: libGeoIP.so.1 is needed by package proftpd
This happens because apparently Virtualmin updated to proftpd from 1.3.2 to 1.3.3 without testing properly (source: Virtualmin Forums). To fix, I installed RPMForge and ran install script again.

Coldfusion solution to Oracle’s “string literal too long” (4k chars limit)

Working on a Coldfusion app with Oracle database, I wanted to import large amounts of data into “CLOB” fields (capable of handing GBs of data). I tried using SQL Developer (by Oracle) and another user tried SQL Loader, but we were both getting the same error on the INSERT statement:

ORA-01704: string literal too long
Cause: The string literal is longer than 4000 characters.
Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

We searched online and noticed that the fix to this problem required bind variables and/or creating a procedure, etc. Really a roadblock if you’re not familiar with PL/SQL and Oracle.

But the problem could be solved using Coldfusion’s JDBC connector to Oracle. I simply wrote up the following cfml code, and noticed that the cfsqltype=”cf_sql_clob” takes care of this 4000 (4k) chars limit problem.

Code:

<cfquery datasource="dsn" username="user" password="pass">
	INSERT INTO logs_table VALUES (
		1,
		<cfqueryparam value="TEST" cfsqltype="cf_sql_varchar">,
		<cfqueryparam value="HUGE AMOUNT OF TEXT HERE (use cfsavecontent and output here)" cfsqltype="cf_sql_clob">
	)
</cfquery>

Chrome’s javascript sort array function is different, yet proper

Chrome’s javascript sort function behaves more like the ECMA standards than the sort function implemented in other browsers (like Firefox, IE, etc), which try to maintain some backwards compatibility with legacy javascript code.

Generally, when sorting an array, I’ve always found the following (incorrect) code:

result.sort(function(a,b) { return a > b } );

According to the ECMA standards, the right way to do it is not to return true or false, like the above function, but to return -1, 0, or 1, depending on how the two strings compare, like the following:

function sortfn (a,b) {
	var upA = a.toUpperCase();
	var upB = b.toUpperCase();
	return (upA < upB) ? -1 : (upA > upB) ? 1 : 0;
}

Django Problems and Quick Fixes

Problems:
Getting the following when trying to use MySQLdb in virtualenv’s django:
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb
Activate and Run:
. bin/activate
pip install MySQLdb

Django on CentOS Python 2.6 VirtualEnv Using GeekyMedia RPMs

Django on centos geekymedia

for setuptools (easy_install):

wget http://pypi.python.org/packages/2.6/s/setuptools/setuptools-0.6c11-py2.6.egg#md5=bfa92100bd772d5a213eedd356d64086
easy_install *setuptools*

use it to install pip:
easy_install pip

download MySQLdb and install by:
python26 setup.py build
python26 setup.py install

download virtualenv
mkdir ~/.virtualenvs

add to .bashrc
VIRTUALENVWRAPPER_PYTHON=/usr/bin/python26
source /usr/bin/virtualenvwrapper.sh

initialize virtualenv
mkdir dev
virtualenv dev

Start virtualenv for current session
source dev/bin/activate

now install packages, they will go inside virtualenv (since we are activated)
pip install django
pip install south
pip install pil

Create django project and app
cd dev/
django-admin.py startproject myproj
cd myproj
python manage.py startapp polls

Install Python 2.6 on CentOS 5.x

Steps to success:

  1. Download all the Python 2.6 rpm for your CentOS (i386 or x86_64) from geekymedia.
  2. Install tcl, tk, tix (required dependencies): yum -y install tcl tk tix
  3. Install the geekymedia rpms.

    Note that the python26-libs-2.6-geekymedia1.*.rpm and python26-2.6-geekymedia1.*.rpm must be installed together like this rpm -Uvh python26-libs-2.6-geekymedia1.*.rpm python26-2.6-geekymedia1.*.rpm. Similarly, *tools* and *tkinter* rpm must be installed together like this rpm -Uvh *tools*rpm *tkinter*rpm.

Return top