Mysql increase key length

We have recently been converting most of our databases from latin to UTF-8 and have encountered issues with smaller Mysql default keylength. Mysql by default allows a max key length of 1024 size. While it was quite a bit of work to change the current data to confirm to the key length, the easier way was to recompile the Mysql source with a custom key length. Here I will detail the steps to do that:

wget http://www.percona.com/mysql/5.0.67-b7/source/mysql-5.0.67-percona-b7-src.tar.gz
tar zxf mysql-5.0.67-percona-b7-src.tar.gz

Now you'll want to make the edits to the myisam.h file to use our longer key length instead of the default 1000 byte keylength. Below is a compare of the file before and after change:

--- /tmp/myisam.h       2008-05-28 13:36:22.000000000 -0700
+++ include/myisam.h    2007-03-15 14:15:41.000000000 -0700
@@ -48,12 +49,12 @@
 #define MI_MAX_KEY                  MAX_INDEXES         /* Max allowed keys */
 #endif
 
-#define MI_MAX_POSSIBLE_KEY_BUFF    (1024+6+6)      /* For myisam_chk */
+#define MI_MAX_POSSIBLE_KEY_BUFF    (4096+6+6)      /* For myisam_chk */
 /*
   The following defines can be increased if necessary.
   But beware the dependency of MI_MAX_POSSIBLE_KEY_BUFF and MI_MAX_KEY_LENGTH.
 */
-#define MI_MAX_KEY_LENGTH           1000            /* Max length in bytes */
+#define MI_MAX_KEY_LENGTH           4000            /* Max length in bytes */
 #define MI_MAX_KEY_SEG              16              /* Max segments for key */
 
 #define MI_MAX_KEY_BUFF  (MI_MAX_KEY_LENGTH+MI_MAX_KEY_SEG*6+8+8)

Next you'll need to run configure / make install

./configure --prefix={choose a name here like /tmp/mysql_mod}
sudo make install

mysql timeout issue

The sys admins had recently changed the timeout on the firewalls to a fairly low value to deal with the excessive connections issue. This was causing an issue with the mysql servers as the nightly etl and scripts were failing with stale connection issue. Googled around to find a way to set the connection timeout in mysql settings but couldn't find any lead. One of our sys admins suggested we change the tcp timeout on the mysql boxes and that seems to have resolved the issue. here's the command:

sudo /sbin/sysctl net.ipv4.tcp_keepalive_time=90

Fairly Beginner Programming Excercises

I have been trying to teach programming to one of my friend and occasionally I search online for fun projects which I can give it to him as programming exercises. I will try to document the ones I gave him with notes on what I intended him to learn from the exercise:

******* Loops and Constructs ********

1) Project 1: Print a pyramid. Read (in a loop of course) a number, n, and print a pyramid that has that size. The example below shows what would be printed for n=4.


*
* *
* * *
* * * *

2) Project 2: Printing prime numbers: This is more sort of a classic programming problem and involves knowledge of basic programming constructs such as conditions, looping etc.

3) Project 3: Printing the first n fibonacci numbers. This is again a classic programming problem and involves knowledge of basic programming constructs.


******* Arrays ***********

1) Project 1: Finding missing elements in a array. So basically a array is provided that has certain missing elements like numbers between 1 - 99. The task here is to find those missing numbers from the array. This project basically requires knowledge of arrays and array traversal.

2) Project 3: Sorting a number array: This is again a classic programming problem and can be done in various ways. Knowledge gained here is again arrays.

******* Hash *********

1) Project 1: Parse a file and display words that occur more than n times. Not a requirement that you use a hash here but ideally you would.

2) Project 2:

C# Web Request Using Client Certificate

Recently worked on this project where a service was accessible over http via client certificate. This is no biggie however took me 4-5 hrs to figure it out as the issue was not with code associated with importing client certificate but with another portion of the code that I overlooked. You know how it goes, stupid mistakes here and there and boom there goes your day. Thinking that the error was with the client certificate code I searched through the anals of Google pages to figure it out and various attempts/learnings made a worth to blog out. BTW kudos to the Fiddler (http://www.fiddlertool.com/fiddler/) tool which allowed me to sniff the http outgoing traffic helping me debug the issue.

So the http service I was trying to access is a secure one and my org was issued a client certificate. There are three ways that I tried to attach a client certificate with the request.

1) Providing direct links to cert files: You can do this via this sample code. Pretty self explanatory. The additional cert you see below is the intermediate certificate agency cert that I had to additionally download.

X509Certificate2Collection certCollect = new X509Certificate2Collection();

X509Certificate2 cert = new X509Certificate2(@"C:\Users\...\x509_verisign-certificate.cer");

X509Certificate2 cert1 = new X509Certificate2(@"C:\Users
\..\Certificate_From_SErvice.cer.pfx", "{client certificate password}");

certCollect.Add(cert);

certCollect.Add(cert1);

2) Exported combined cert file (.cer.p7b format): If you have imported the client/intermediate certificate onto your computer then you can export combined certificate (with all intermediariy certificate) + private key into a .cer.p7b file format. You can use this one file instead of the two used above. Here the code to make it work:

X509Certificate2Collection certCollect = new X509Certificate2Collection();

FileInfo file = new FileInfo(@"C:\Users\...\exported_certificate.cer.p7b");

BinaryReader br = new BinaryReader(File.OpenRead(file.FullName));

byte[] raw = br.ReadBytes((int)file.Length);

SignedCms cms = new SignedCms();

cms.Decode(raw);

certCollect.AddRange(cms.Certificates);

3) Referencing a imported certificate on the workstation: You can reference a imported certificate using the below code. There a various ways to uniquely identify a certificate using 'subject name' or 'serial id' etc. I have accessed it via the subject name.

X509Store store = new X509Store("MY", StoreLocation.CurrentUser);

store.Open(OpenFlags.ReadOnly | OpenFlags.OpenExistingOnly);

X509Certificate2Collection collection = (X509Certificate2Collection)store.Certificates;

X509Certificate2Collection fcollection = 
(X509Certificate2Collection)collection.Find(X509FindType.FindBySubjectName, "{certificate name}", false);

X509Certificate2Collection icollection = 
(X509Certificate2Collection)collection.Find(X509FindType.FindBySubjectName, "VeriSign
Class 1 Individual Subscriber CA - G2", false);

X509Certificate2Enumerator certEnum = fcollection.GetEnumerator();

certEnum.MoveNext();

X509Certificate2 primCertFromStore = certEnum.Current;

certEnum = icollection.GetEnumerator();

certEnum.MoveNext();

X509Certificate2 issuerCertFromStore = certEnum.Current;

certCollect.Add(primCertFromStore);

certCollect.Add(issuerCertFromStore);
            
// use the below code to test if the retrieved certificate is the one you intend to.
//X509Certificate2UI.DisplayCertificate(primCertFromStore);
//X509Certificate2UI.DisplayCertificate(issuerCertFromStore);
store.Close();

You can add the certificate to the request using the below code sample:

....
.......
// Handle any certificate errors on the certificate from the server.
string uri = "request uri";

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);

request.ProtocolVersion = HttpVersion.Version11;

request.KeepAlive = true;    
                              
request.Method = "POST";
            
request.Accept = "*/*";      
     
request.ClientCertificates.AddRange(certCollect); 
                       
string str = "POST DATA";

byte[] postBytes = Encoding.UTF8.GetBytes(str);

request.ContentType = "TEXT/XML";

request.UserAgent = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 
2.0.50727; .NET CLR 1.1.4322; InfoPath.1)";
            
request.ContentLength = postBytes.Length;

Stream requestStream = request.GetRequestStream();

requestStream.Write(postBytes, 0, postBytes.Length);

requestStream.Close();

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

MYSQL duplicate entry error with a non-unique index/key

This was a strange one. A insert was erroring out due to duplicate entry error on a key that is non -unique. The error was: ERROR 1062 (23000): Duplicate entry '445982' for key 2. Did a check table to check if everything was alright and there it was: an error with the table. Repair quick did not work so had to do a full repair. See below log for query statements:

Here's the log:

mysql> show create table tableA;

......
.....
.....
 PRIMARY KEY  ("category_id"),
  KEY "ix_campaign_category_unpacked_root" ("root_campaign_id"),
  KEY "ix_campaign_category_client" ("client_id","site_id"),
  KEY "ix_campaign_category_site" ("site_id","client_id")
) ENGINE=MyISAM DEFAULT CHARSET=utf8 

-- Error from insert

mysql> ERROR 1062 (23000): Duplicate entry '445982' for key 2

-- check table result

mysql> check table tableA;
+----------------------------------+-------+----------+--------------------------------------------------------------+
| Table                            | Op    | Msg_type | Msg_text                                                     |
+----------------------------------+-------+----------+--------------------------------------------------------------+
| tableA | check | warning  | Table is marked as crashed                                   |
| tableA | check | warning  | 2 clients are using or haven't closed the table properly     |
| tableA | check | warning  | Size of datafile is: 34234576       Should be: 34234400      |
| tableA | check | warning  | Found 0 deleted space in delete link chain. Should be 361132 |
| tableA | check | error    | Found 0 deleted rows in delete link chain. Should be 2385    |
| tableA | check | error    | record delete-link-chain corrupted                           |
| tableA | check | error    | Corrupt                                                      |
+----------------------------------+-------+----------+--------------------------------------------------------------+
7 rows in set (0.00 sec)
 
-- Repair table attempts

mysql> repair local table tableA quick;
+----------------------------------+--------+----------+-------------------------------------------------------------------------------+
| Table                            | Op     | Msg_type | Msg_text                                                                      |
+----------------------------------+--------+----------+-------------------------------------------------------------------------------+
| tableA | repair | error    | Couldn't fix table with quick recovery: Found wrong number of deleted records |
| tableA | repair | error    | Run recovery again without -q                                                 |
| tableA | repair | status   | OK                                                                            |
+----------------------------------+--------+----------+-------------------------------------------------------------------------------+
3 rows in set (9.64 sec)

mysql> repair local table tableA;
+----------------------------------+--------+----------+----------+
| Table                            | Op     | Msg_type | Msg_text |
+----------------------------------+--------+----------+----------+
| tableA | repair | status   | OK       |
+----------------------------------+--------+----------+----------+
1 row in set (2.43 sec)

Mysql duplicate entry error with auto-increment column

I have been looking at this issue with one of our tables where we were getting a duplicate entry error on key 1 which is basically the primary key on the table. The primary key field is set to auto-increment and was puzzling me as to why it was causing the duplicate entry. Ran the checks to ensure the values were right (see checks below):

Someone suggested to alter the auto-increment value to a higher value and that would reset the next value. So I did a alter statement which took a while as the table was huge. The query to check the next ID value came out correctly (see below for query)

However this still did not resolve the issue. Finally looked in the direction of index and turns out that index might have been corrupted. Ran a repair table to check index and that fixed the value. (see below for query)

*********** checks ********************

--   select max(id) from tableA;
returned: 70809185

--  insert into tableA values (xxxxxx);
Error:  Duplicate entry '70809186' for key 1

--  select * from tableA where id=70809186;
Returned 0 rows

************** alter index **************

-- query to change the next increment value
 ALTER TABLE tbl AUTO_INCREMENT = 70809190;

-- find out whats the next increment value
 SELECT Auto_increment FROM information_schema.tables WHERE table_name='tableA';

************* repair index *****************

--  repair table tableA quick;
quick option repairs the indexes

/

mysql duplicate key error due to sequence issue

I recently had to deal with this error so I am blogging it here so the rest can use.

Error: On one of our tables we kept getting a duplicate key exception on insets. The puzzling thing was that the value actually did not exist in the table. Here's a low down:

--- Table definition
CREATE TABLE "job_message" (
  "message_id" bigint(20) NOT NULL auto_increment,
  "run_id" int(11) NOT NULL,
  "object_id" bigint(20) default NULL,
  "date" datetime NOT NULL,
  "priority" tinyint(2) NOT NULL,
  "type" varchar(32) default NULL,
  "msg" varchar(255) NOT NULL,
  "stack_trace" varchar(255) default NULL,
  PRIMARY KEY  ("message_id"),
  KEY "ix_job_message" ("run_id","object_id","date"),
  KEY "ix_job_message_object_id" ("object_id"),
  KEY "ix_job_message_dt" ("date")
) 


-- proof that row did not exist
mysql> select max(message_id) from job_message_200836;
+-----------------+
| max(message_id) |
+-----------------+
|       753222920 |
+-----------------+
1 row in set (0.00 sec)


-- error with insert statement
mysql> insert into job_message (run_id, object_id, date, priority, msg) values (1279408,
 22231896, '2008-09-02 15:53:40', 4, 'test');

ERROR 1062 (23000): Duplicate entry '753222922' for key 1

Thats what was puzzling.

Solution:

Did a lot of googling around but got nothing. I did get some hint that the issue might be with the sequences and that the sequence is not updated but got no clue on how to resolve the issue. Out of desperation I tried to see if the table was healthy and voila there it was. The table was corrupted. The solution was to either repair the table or get a copy from the slave. I tried the repair and it worked fine. I used local in the repair command as I did not want the repair to propagate to slave as the slave copy was fine. See commands below to check and repair a table.

-- check table
mysql> check table job_message_200836;
+-------------------------+-------+----------+-----------------------------------------------------------+
| Table                   | Op    | Msg_type | Msg_text                                                  |
+-------------------------+-------+----------+-----------------------------------------------------------+
| main.job_message_200836 | check | warning  | Size of datafile is: 352975060       Should be: 352974784 |
| main.job_message_200836 | check | error    | Found 3189125 keys of 3189124                             |
| main.job_message_200836 | check | error    | Corrupt                                                   |
+-------------------------+-------+----------+-----------------------------------------------------------+
3 rows in set (0.69 sec)


-- repair table command
mysql> repair local table job_message_200836;
+-------------------------+--------+----------+------------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                       |
+-------------------------+--------+----------+------------------------------------------------+
| main.job_message_200836 | repair | warning  | Number of rows changed from 3189124 to 3189125 |
| main.job_message_200836 | repair | status   | OK                                             |
+-------------------------+--------+----------+------------------------------------------------+
2 rows in set (29.93 sec)

TwinView option disabled in Nvidia-Settings

I am trying to configure dual monitors on my Ubuntu hardy however the 'Twinview' option is disabled in my settings. To enable this option you would have to tweak the 'xorg.conf' file. First of make a backup of the current '/etc/X11/xorg.conf' so you can always revert to this copy. Here are the instructions to modify the file to enable 'Twinview'

- sudo gedit /etc/X11/xorg.conf
- Search for line which says "Twinview" "0". Change the "0" to "1"
- sudo nvidia-settings (now the nvidia driver should automatically detect the second connected screen)

In looking online I also see this option however it did not work for me:

- nvidia-xconfig --twinview
and restarting your X server.

There is a nice article on configuring twinview on the NVidia site: http://us.download.nvidia.com/XFree86/Linux-x86/1.0-9755/README/appendix-g.html

Wicd won't automatically connect to wireless network

I have been using Wicd on my Ubuntu for quite a while and never really bothered as to why it would not automatically connect to a specified network. Wicd provides an option to specify whether to automatically connect to a network. I finally figured out. You need to add Wicd to your system startup services for it to automatically connect. Here are the steps to do that:

To get the tray icon to automatically appear at boot, go to System > Preferences > Sessions. In the "Startup Programs" tab, click the "New" button. Give it a name ("Wicd" works fine). For the command, enter "/opt/wicd/tray.py"

Ubuntu: Installing NVidia video driver

I have a Dell Latitude D630 laptop with a NVidia Quadro NVS 135M video card. I recently struggled with installing the driver for this baby on Ubuntu 8.04. I will detail the various solutions I tried and finally the one that worked for me. I have always had issues with this driver however the desire to install the Awant window manager propelled me to deal with this issue. Awant requires that advanced desktop effects be enabled which in turn required a 3D compatible video card. I previously manually modified the xorg.conf to deal with the driver issues but it won't work when I enabled the restricted driver for this card.

The first solution is to simply enable the restricted driver and check if it works for you. Basically under System --> Administration --> Hardware Drivers. My issue was that resolution detection just won't work. Even if I manually chose the card type and monitor type the resolution would default to 800x600. This pointed me to reinstall the packages involved

sudo apt-get install --reinstall linux-restricted-modules-$uname -r} nvidia-glx-new

Re-enable the driver after doing the above step and it still won't detect my resolution. One of the sites suggested using the 'nvidia-xconfig' to generate the 'xorg.conf' and tweak it further via the 'nvidia-settings' util

sudo nvidia-xconfig --> this creates a new xorg.conf file
sudo nvidia-settings --> this is used to tweak the settings

The settings won't show my resolution options as the driver was not correctly installed.

So I looked at some other options and came across this article around installing a proprietary driver downloaded from NVidia site. You can download the latest driver from here:

http://www.nvidia.com/object/unix.html

Here are the instructions to install this driver:

For the driver to install the XServer must be stopped.

cntrl + alt + backspace
sudo /etc/init.d/gdm stop
sudo sh {installed driver}
sudo /etc/init.d/gdm start

I did a restart and upon boot up fired the 'nvidia-settings' on terminal which gave me the options to set the resolution. Hope it helps!!!!

Error installing windows security updates on upgrade from Windows XP Home to Professional

So I recently upgraded my windows XP Home version to Professional in need of IIS web
server. XP Home does now allow IIS installation (there are various hacks mentioned
on websites but I could not make it to work). The issue was that my windows automatic updates which were working totally fine before the upgrade, started erroring out after the upgrade. The updates were getting downloaded however they won't install. I had a queue of over 90 updates to be installed. The error on the installation screen was unhelpful just saying "Updates were not installed". My further analysis pointed me to the "windows update log file".

C:\WINDOWS\WindowsUpdate.log

To access the log file easily do:
Click Start > Run...
Type/paste %windir%\windowsupdate.log in the "Open:" field
Click OK

So in my logs I found this error (see below for stack trace). Re-Register the Windows Update dll files and that did the trick.

Run the following on command prompt:

regsvr32.exe c:\windows\system32\wuweb.dll
regsvr32.exe c:\windows\system32\wups2.dll
regsvr32.exe c:\windows\system32\wups.dll
regsvr32.exe c:\windows\system32\wucltui.dll
regsvr32.exe c:\windows\system32\wuaueng1.dll
regsvr32.exe c:\windows\system32\wuaueng.dll
regsvr32.exe c:\windows\system32\wuapi.dll

The windows update will automatically restart after a while and shows up the queued updates in system tray. Works from there.

Stacktrace
2008-07-09 22:18:22:703 3812 a78 Misc =========== Logging initialized (build: 7.0.6000.381, tz: -0700) ===========
2008-07-09 22:18:22:703 3812 a78 Misc = Process: C:\WINDOWS\system32\wuauclt.exe
2008-07-09 22:18:22:703 3812 a78 AUClnt FATAL: Error: 0x80004002. wuauclt handler: failed to spawn COM server
2008-07-09 22:18:22:718 1248 8c0 Handler FATAL: 0x80004002: ERROR: Remote update handler container process created (PID: 3812), but exited before signaling event
2008-07-09 22:18:25:906 1248 8c0 Agent * WARNING: Exit code = 0x80004002
2008-07-09 22:18:25:906 1248 8c0 Agent *********
2008-07-09 22:18:25:906 1248 8c0 Agent ** END ** Agent: Installing updates [CallerId = AutomaticUpdates]
2008-07-09 22:18:25:906 1248 e5c AU >>## RESUMED ## AU: Installing update [UpdateId = {707E6500-81C1-4165-8C43-631E09891716}]
2008-07-09 22:18:25:906 1248 8c0 Agent *************
2008-07-09 22:18:25:906 1248 e5c AU # WARNING: Install skipped
2008-07-09 22:18:25:906 1248 8c0 Agent WARNING: WU client failed installing updates with error 0x800040022008-07-09 22:18:25:906 1248 e5c AU >>## RESUMED ## AU: Installing update [UpdateId = {DD167F04-69E8-46A0-83DA-D4A5143EDBF1}]
2008-07-09 22:18:25:906 1248 e5c AU # WARNING: Install skipped
2008-07-09 22:18:25:906 1248 e5c AU >>## RESUMED ## AU: Installing update [UpdateId = {7CB0B704-B2FF-4D5C-B202-B5BA340B7007}]
2008-07-09 22:18:25:906 1248 e5c AU # WARNING: Install skipped
2008-07-09 22:18:25:906 1248 e5c AU >>## RESUMED ## AU: Installing update [UpdateId = {73F11106-7351-465D-9BA5-2881436C0D5A}]
2008-07-09 22:18:25:906 1248 e5c AU # WARNING: Install skipped
2008-07-09 22:18:25:906 1248 e5c AU >>## RESUMED ## AU: Installing update [UpdateId = {383B0108-D3E2-4F2A-9E32-7B7B599BB0F7}]

converting inputstream to octetstream

The other day I was working with soap attachments and stumbled across OctetStream. Had no idea on this stream type which made me lookaround and came up with this info:

org.apache.axis.attachments.OctetStream corresponds to mime type "application/octetstream"

-- Converting Inputstream to OctetStream

ByteArrayOutputStream baos = new ByteArrayOutputStream();
int byte1 = -1;
while ((byte1 = in.read()) != -1)
baos.write(byte1);
return new OctetStream(baos.toByteArray());