Brandon Checketts

Web Programming, Linux System Administation, and Entrepreneurship in Athens Georgia

Category: Linux System Administration (Page 4 of 11)

MySQL [ERROR] Invalid (old?) table or database name

April 25, 2009 / / 1 Comment

After upgrading to MySQL 5, one of my databases appeared empty even though the MyISAM tables existed in /var/lib/mysql/database-name. I found this error in the mysql log file in /var/log/mysqld.log:

090425 23:10:52 [ERROR] Invalid (old?) table or database name 'database-name'

Turns out that MySQL 5 doesn’t like database names that contain a hyphen. In the mysql directory, I see that it created a second directory for the database, but with the hyphen replaced with ‘@002d’. 

drwx------  2 mysql mysql     4096 Apr 25 23:10 database-name
drwx------  2 mysql mysql     4096 Apr 24 00:30 database@002dname

To resolve that I decided to replace the hyphen with an underscore. I stopped mysql, renamed /var/lib/mysql/database-name to /var/lib/mysql/database_name, and then ran this SQL to update the permissions:

UPDATE mysql.db SET Db = 'database_name' WHERE Db = 'database\-name';
FLUSH PRIVILEGES;

I could then see the tables using mysql from the command line. From there, I just updated the setting for the application to use the new name, and everything worked out find.

What is in a gclid?

April 15, 2009 / / 3 Comments

When you use auto-tagging with your Adwords campaign, all request that are generated by Google Adwords contain a ?glcid parameter in the Request. Adwords uses this to pass some information to Analytics for traffic analysis.

I was curious, about what data the gclid parameter contained. My guess was that it contained some encoded or encrypted information regarding the origin of the click, so I did some analysis on the clicks that I received. Some discussion about it was available on this post.

I ended up writing a quick PHP script that parses through an Apache log file. It finds requests that contain a gclid and then produces a report of which letters occur in which positions of the gclid.

Found 32507 appropriate lines
Character  1 [ 1] C
Character  2 [ 8] IJKLMNOP
Character  3 [32] -CDGHKLOPSTWX_abefijmnqruvyz2367
Character  4 [64] -CDEFG0ABHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz123456789
Character  5 [32] -_0ghijklmnopqrstuvwxyz123456789
Character  6 [32] -IJKLMNOPYZ_abcdefopqrstuv456789
Character  7 [32] -CDGHKLOPSTWX_abefijmnqruvyz2367
Character  8 [64] -ABCDEFG0HIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz123456789
Character  9 [32] 0-_ghijklmnopqrstuvwxyz123456789
Character 10 [ 4] JZp5
Character 11 [ 8] IMQUYcgk
Character 12 [ 1] C
Character 13 [ 1] F
Character 14 [10] QRSUWYZcde
Character 15 [61] -ABCEFGHIJKLMNOPQRSTUVWXYZ_ab0cdefghiklmnopqrstuvwxy123456789
Character 16 [63] -ABCDEFGHIJKLMNOQRSTUVWXYZ_abcde0fghijklmnopqrstuvwxyz123456789
Character 17 [17] DFGHIQabgiknrsx57
Character 18 [ 4] AQgw
Character 19 [ 1] o
Character 20 [ 1] d
Character 21 [64] -ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwx0yz123456789
Character 22 [32] ABCDEFGHQRSTUVWXghijklmnwyz0x123
Character 23 [64] -ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuv0wxyz123456789
Character 24 [64] -ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrs0tuvwxyz123456789
Character 25 [62] 0-ABCDEFHIJKLMOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz123456789
Character 26 [ 4] AQgw

This makes it clear that the parameter has some structure, but I’m still no closer to determining what it contains. Counting up the unique values, it would seem that they have about 95 bits of information available, which might be enough room to store everything it would need to know about the search that created it. Based on the reporting details in Analytics, I would presume that it somehow contains at least the following information:

  • Campaign (id)
  • Keyword (id)
  • Ad Variation (id)
  • Position

I did some research by clicking an ad multiple times and examining the glcids for those:

        12345678901234567890123456
/?gclid=CNHz5eD_8pkCFRCdnAodzniYQg
/?gclid=CIX_u-X_8pkCFQKenAodlWprSg
/?gclid=CMyI_4OA85kCFRIhnAodc2_oRg
/?gclid=CO_0pYyA85kCFQghnAodDDpaRQ
/?gclid=CIXo9JeA85kCFRIhnAodc2_oRg
/?gclid=CLitgp2A85kCFQubnAod1nx7Qg
/?gclid=CN3_1aOA85kCFQghnAodDDpaRQ
/?gclid=CPyi1quA85kCFRabnAodWnZbRQ 
/?gclid=COq-67OA85kCFRMhnAodyQvSRg
/?gclid=COOplrmA85kCFRCdnAodzniYQg

I noticed that most of the characters which use 32-64 characters vary quite a bit except for character #9, which was always an 8, and character #10 which was a ‘p’ for the first two clicks, and then a ‘5’ for all subsequent clicks. That likely has some significance, but I’m out of time for playing with it for now.

Hopefully the script and this basic analysis might be of use for somebody else to use in digging into it further.

One other thought that I had is that the data (or each field) is somehow encrypted and when you ‘link’ your Analytics account to your Adwords account it shares the decryption key so that it can get at the detail.

SpamAssassin + Postfix to Deliver Spam to an Alternate Mailbox

April 10, 2009 / / 1 Comment

A fairly simple method of having postfix send messages through SpamAssassin, and then delivering the messages identified as spam to a separate mailbox:

Configure SpamAssassin as normal in master.cf:

smtp      inet  n       -       -       -       -       smtpd -o content_filter=spamassassin
2525      inet  n       -       -       -       -       smtpd -o content_filter=spamassassin
spamassassin unix -     n       n       -       -       pipe user=spamd argv=/usr/bin/spamc -f -e    /usr/sbin/sendmail -oi -f ${sender} ${recipient}

Configure Spamassassin to log the status and score in a new header by adding this to /etc/spamassassin/local.cf:

## Add a spamassassin header to all messages so that we can filter them out
add_header all Status _YESNO_, score=_SCORE_ required=_REQD_ tests=_TESTS_ autolearn=_AUTOLEARN_ version=_VERSION_

And then configure postfix to look for that header and deliver it to an alternate mailbox:

/etc/postfix/main.cf:

header_checks = regexp:/etc/postfix/header_checks

/etc/postfix/header_checks:

/^X-Spam-Status: Yes/ REDIRECT [email protected]

Now your messages identified as spam will be sent to [email protected] so that you can occasionally check for false positives.

Note that there is more work that goes into setting up SpamAssassin, but there are plenty of howtos around for getting that configured.

Upgrading an OpenVZ CentOS 5.2 guest to 5.3

April 8, 2009 / / 0 Comments

Doing a simple ‘yum update’ on a CenOS 5.2 guest server would go through the process of downloading all of the updates, but would only install libgcc and then quit suddenly without any mention of a problem.

With a little bit of experimenting, I came up with a process to get it to upgrade correctly. By updating some of the essential packages first, it somehow convinced the process to work:

# yum upgrade libgcc
# yum update yum
# yum update rpm
# yum update

Announcing WebPasswd

March 15, 2009 / / 0 Comments

Do you have users who need access to web-based applications on multiple servers? Managing those users can be a pain when dealing with normal htpasswd-based permissions. Adding or removing users means editing each htpasswd file and remembering where all of them are.

Mod_auth_mysql is a good way to centralize that user database so that you can avoid having all of the separate htpasswd files. The apache module is available from any modern Linux distribution, so installing and configuring it takes less than 5 minutes. I started using it almost 2 years ago, and over that time have made a simple web application for managing the users and granting them permission to each application.

I’ve released the program as WebPasswd for anybody else who wants to use it. Now adding users and granting them access to application can be don with just a few clicks. Granting and revoking access to an application takes just seconds and is applied immediately. Configuring a new application takes a couple clicks, and then you just copy/paste the Apache configuration into the appropriate place on your web server. Try it out with this demo.

I think this will be useful to people. I have not seen another application that does something similar. Let me know if it works for you.

Installing Software RAID on a running Ubuntu Box

March 12, 2009 / / 0 Comments

I’ve done this several times on several distro’s now. My last post was about setting this up on CentOS 5. Setting it up on Ubuntu is mostly similar, but requires a couple of significant changes.

Again, Falco’s HowToForge article does a good job of explaining the process. I followed the guide for Debian Etch with the following changes:

I don’t think that Step 2 (installinginitramfs-tools and mdadm) is required.

Prior to Step 3, when editing /etc/fstab, you need to know the UUID’s of your new volumes. You can use the ‘vol_id’ command to get those

root@host:~# vol_id /dev/sda1|grep UUID=
ID_FS_UUID=97b2e1f2-2f16-47be-8fac-8091a4d5817f

Instead of replacing /dev/sdX with /dev/mdX, you need to replace to UUID’s for those with the UUID’s from the new /dev/mdX devices. Also, when editing /boot/grub/menu.lst, you’ll need to replace the UUID for /dev/sda3 with the UUID for /dev/md2

The rest of the process should work as documented.

Apt ‘MMap ran out of room’ errors

February 28, 2009 / / 0 Comments

I’m seeing this problem more and more frequently when running apt-get or apt-cache commands. It seems to get most of the way through updating itself and then dies with this error:

E: Dynamic MMap ran out of room
E: Error occured while processing rapidsvn (NewVersion1)
E: Problem with MergeList /var/state/apt/lists/something_repodata_primary.xml
E: The package lists or status file could not be parsed or opened.

My understanding is that apt is running of some cache space and quits. I think this is referring to some memory limit, but am not sure (if anybody has a better explanation of the actual problem, please comment).

The fix is easy enough. Just create a file named /etc/apt/apt.conf.d/local.conf with this line:

APT::Cache-Limit 50000000;

That raises the limit from the default (I believe 8Mb) to 50Mb and has worked well for me.

CSV from the MySQL Command Line

February 18, 2009 / / 0 Comments

I frequently need to generate a quick report generated from a quick database query. I have been doing this by creating a simple PHP script that queries the database and then displays the results as a CSV. However, I just found a way to do it directly from the MySQL command line, which makes it possible to skip the steps of creating the PHP script.

Simply run the command like this:

mysql>SELECT * FROM sometable INTO OUTFILE '/tmp/output.csv'
   FIELDS TERMINATED BY ',';

That will save the results to the file specified in a simple CSV format. There are also options for enclosing fields in quotes, the line terminators, and several other options.

« Older posts Newer posts »

© 2025 Brandon Checketts

Theme by Anders Noren — Up ↑