Ivan Villareal

Today I was developing a script that was going to get some data from an uploaded file to start a batch job, I did the front end with the help of  uploadify and everything was ok, I promptly did a php script to get the data.

I’m using the file() function, (I’m lazy I know), It was working fine, I did a test case and was displaying the output to the console, everything was working as expected, I’ve played a little with the flags and did a couple of validations to handle Windows, MAC and Unix line endings, and it was good, so I moved on to the model part, and after my first test inserting some data to the db I had a very weird problem.

The first line, was definitely something wrong, I wasn’t able to see any weird character from the mysql console, but I’ve noticed some strange character when i viewed with mysql browser, whenever I printed the result seemed ok, I’ve also did some debug on the queries and it was ok, but in the mysql query browser this weird square was showing for the first row.

I thought that this was an encoding issue, so I’ve started looking at this, and what I’ve learned Today was that some editors, add this apparently useless Byte Order Mark to the beginning of UTF encoded files, this are the bytes added 0xEF,0xBB,0xBF.

I’ve spent almost an hour trying to get a workaround for this, so after some testing this was my solution to get this done:

            $data= file($fullFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES );
            if ($data) {
            //Remove BOM
            if(substr($data[0], 0,3) == pack("CCC",0xef,0xbb,0xbf)) {
                $data[0]=substr($data[0], 3);
            }
                $this->processData($data);
            }

I’ve tested this with ANSI, UTF-8 encodings and with Windows, Unix, and Mac CR endings, and it works, I think that there might be a better solution, but this has taken me too much time Today.

I have the need to install php5 and mysql 5 on a centos 4 server, so I found that the Centos Plus repository had this, and this is what I did to get these two working.

First check what is the version of the distro:

cat /etc/*release*

Then make an update:

yum update

After 496 package updates I’ve installed php5

yum --enablerepo=centosplus install php

This were the installed packages:

php-5.1.6-3.el4s1.10
php-cli-5.1.6-3.el4s1.10
php-common-5.1.6-3.el4s1.10

So I’ve created a info.php file, and restarted the apache server:

echo "<?php phpinfo(); ?>" > /var/www/html/info.php && service httpd restart

It was working correctly, so the next thing was to have mysql 5 in it, so I did:

yum --enablerepo=centosplus install mysql-server

I got some problems here, first I got this:

Transaction Check Error: file /etc/my.cnf from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/Index.xml from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/README from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/armscii8.xml from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/ascii.xml from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4

So what I found was that yum was trying to install mysql.i386 but the architechture is x86_64, to fix this I had to specify the arch

yum --enablerepo=centosplus install mysql.x86_64 mysql-server

After this I was able to install it, but when I tried to start the server I received some errors about error messages not right, I’ve checked the installed packages and it was installed mysqlclient10, so I removed the error messages problem disappeared, however I still couldn’t startup the server, checking at the logs I found this:

InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
091012 11:01:18  InnoDB: Started; log sequence number 0 0                       091012 11:01:18 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist                                                     091012 11:01:18  mysqld ended                                               

To fix this I cleared the data dir, that had som files, this was in ‘/var/lib/mysql’ and after that I ran mysql_install_db:

[root@prestant /]# cd /var/lib/mysql 
[root@prestant mysql]# rm -rf *
[root@prestant mysql]# mysql_install_db --user=mysql -ldata=/var/lib/mysql
[root@prestant mysql]# service mysqld start

After the server was up, I changed the root password and added a new user:

[root@prestant mysql]# mysqladmin -u root password NEWPASSWORD
[root@prestant mysql]# mysql -pNEWPASSWORD
 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.82sp1 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE USER 'ivan'@'%' IDENTIFIED BY 'passhere';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ivan'@'%' IDENTIFIED BY 'passhere' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE USER 'ivan'@'localhost' IDENTIFIED BY 'passhere';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ivan'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit
Bye
 
 

I’m working on an application that stores unique values on a field, so I used the unique index for this column, everything was ok, until I’ve started inserting multi byte characters in it.

The charachters inserted fine, the problem was that I’m using INSERT … ON DUPLICATE KEY UPDATE syntax to avoid looking if the field exists, so after debugging where the application was breaking I got this:

Query failed: Cannot add or update a child row: a foreign key constraint fails (`trubaseek/mapKeywordName`, CONSTRAINT `fkDomainMapNameId` FOREIGN KEY (`NameId`) REFERENCES `names` (`nameId`) ON DELETE NO ACTION ON UPDATE NO ACTION)<br> SQL: INSERT INTO `mapKeywordName` (`keywordId`, `nameId`) VALUES (’1′, ‘326′);

so I’v spent about an hour changing the encodings without success, but after some testing I discovered that hôteles was the same as hotelës, I did several testing from the linux console, the mysql browser, and from a php script, and all revealed the same.

Whe I did this

INSERT INTO `names` (`name`, `price`, `priceCurrency`, `bids`, `traffic`, `lastUpdate`) 
VALUES ('hotelës', '0', '$US', '0', '0', NOW()) 
ON DUPLICATE KEY UPDATE `price`='0', `priceCurrency`='$US', `bids`='0', `traffic`='0', `lastUpdate` = NOW();

I’ve got this:

Query OK, 2 rows affected (0.01 sec)

Called from a php script mysql_insert_id(); returned me an Id like if it was inserted not updated, but when if I select the returned Id, an error were thrown, because it didn’t existed.

So after some research, I’ve found that adding a collation utf8_bin to that column fixed this issue.

I haven’t tested this enough but for now it appears to be working.

About 2 years ago I purchased this 500Gb Acomdata drive that was on sale at Fry’s, it was a good deal back then, cost me about 100 bucks, it had an aluminium case and a power off switch, and I was in the need to expand the storage capacity of my multimedia server, which is linux based.

I didn’t researched this product, I didn’t even knew that acomdata was a brand, anyway I remember seeing the “Hybrid Drive” statement, but didn’t care, I was going to wipe this drive an reformat it as ext3, so I was aware that all the “software features” it had were useless to me, like the PushButton™ Backup and the Nomad Mobile Desktop.

After I arrived home, I plugged this thing on my server, which had ivman and a custom script to mount anything, but after few seconds nothing happened, I look at the kernel messages and I started to worry, It showed as a CD drive instead of a Mass Storage Device, I tried unsuccessfully to mount this manually, so I thought this drive must be bad.

The next day I connected this drive to a Windows XP, and after some drivers install, it was working, I noticed that it created a virtual CD drive on Windows it had some software on it, so I thought that this was some kind of partition that would be erased after I formatted the drive, so I did this, but the partition was still there.

I did some research on the internet but didn’t found anything use full, so I was again trying to make this stupid drive worked on linux, I hate when I have to fight with the stuff I buy just to make it work on linux, I was very annoyed by this, finally after looking the kernel messages I’ve found that the drive was acting as a SCSI device, so I activated “Probe all LUNs on each SCSI device” in the kernel and after a kernel restart both partitions were available on Linux, however this still had a problem, because I was using ivman to detect the drive and mounted, ivman  didn’t worked for hard drive partition, just for the virtual CD, I’ve tried unsuccessfully to delete that stupid CD partition on windows and Linux.

I had enough of this and I was going to take this drive back to the store, but in a desperation moment I took my screwdriver and started to tearing apart the drive, I wasn’t even stopped by the warranty void seal, my intention was to take the drive format it and put it back without the cd part, I did this, and the format was successful, the drive was totally empty, but when I put this back to the enclosure the stupid CD partition was there, empty this time, but it was there, so it was a firmware issue and I didn’t found a firmware upgrade to remove this stupid feature.

I did some scripts to detect this unmounted the CD part and mount the hard drive partition, it took me about a week to have this thing working the way I wanted,  this was  unbelievable, why they don’t put the software to remove this “hybryd feature”.

However this drive has been working fine, with the virtual CD partition still there until Yesterday, I was in the need to transfer some large files from work to home, and this was the drive more reachable from all the drives I have, so I bring it her to the office, and as soon as I plugged in the CD partition appeared on my Desktop, so I thought it has been a while now, lets see if I can finally reflash the firmware to remove this so called feature, and I’ve found a forum with several users trying to remove this, and suddenly I’ve found this  post:

You need this software from http://www.mediafire.com/?bgwuwq5xzbm

unzip and click on MP251MFG, click on “configure hdd” and once it says pass just unplug and plug in the device. CD PART gone. 

I quickly downloaded the file in question, it was a zip file called “REMOVE CD PART.zip” with about 1.5Mb in size, it has  a Readme.pdf that describes the MP251MFG program, I booted up in Windows, installed the fsdriver because this drive was an ext3, plugged the drive and run the MP251MFG.exe application this is what I saw:

I didn’t backed up the drive, I was just exploring the options and I clicked the Configure HDD, a process was started and finished successfully, I was affraid that my data were gone, so I headed to check if the data was there, and it was, I copied the important stuff somewhere else, unplugged the drive and plugged back in, and voilà, the CD partition was gone along with the data in the drive, but that din’t matter the CD partition was really gone

The drive was reformatted to FAT32, so I reformatted back to ext3, and now it is working pretty well, 2 years later.

I’ve found that this program works for other drives too, here are some links where this program can be found

For Windows:

• xSil251 Switch HDD to CD+HDD_Nomad Mobile Desktop_V1.04_091007
• xSil251 Switch CD+HDD to HDD 3.5_Toshiba_V1.02_041307 

For Macintosh:

• Switch to CD+Secure Nomad for Mac
• CD to HDD Convertor for Mac