MacLochlainns Weblog

Rebuilding new reference environments on my MacPro, I started with Ubuntu 10.04.01 LTS (64 bit), I had to recall the step to install VMWare Tools. It’s quite simple but I know my students may need the steps to configure a VMWare virtual machine and it may benefit others. While this Ubuntu help page is a good start it isn’t a step-by-step configuration guide.

1. Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Ubuntu virtual machine.
2. Open a terminal session by choosing Applications, within the drop down choose Accessories, and in the subsequent drop down choose Terminal. It will launch a terminal session for command-line entry. The screen shot will look like the following.

3. From the command-line perform the following tasks:

cd /media/VM*
cp VMwareTools*.gz /tmp
cd /tmp
gunzip VMwareTools*.gz
tar -xvf VMwareTools*.tar
cd vmware-tools-distrib
sudo ./vmware-install.pl

4. After starting the vmware-install.pl, accept all the default prompts. Alternatively, as Josh points out enter the following to skip the prompts:

sudo ./vmware-install.pl --default

5. After the configuration completes, you’re prompted to restart the X-Windows. The easiest way is to reboot. You click on the upper right corner to get the drop down menu to restart Ubuntu. A screen shot follows.

This completes the VMWare Tools installation on Ubuntu but unfortunately, you may need to setup the network connection. In a couple instances, the Ubuntu installation appears to have corrupted the VMWare networking process. The result is that the DNS setup on Ubuntu didn’t work.

When the Ubuntu /etc/resolv.conf file is empty. You should first restart the VMWare network. This can be done without rebooting your native Mac OS X. Open a Terminal session, and navigate to the following directory and restart the VMWare network or use this command with backquoting.

sudo /Library/Application\ Support/VMware\ Fusion/boot.sh  --restart

If your Ubuntu /etc/resolv.conf file is empty, you can manually edit it. The last line in this sample depends on your IP subnet. I’ve entered it by assuming that you’re on 192.168.75.0 to 192.168.75.255 with a 255.255.255.0 network mask. You can refer to this prior post for the details on how you find your VMWare NAT subnet.

When your Ubuntu /etc/resolv.conf file is empty, add these values:

# Generated by NetworkManager
domain localdomain
search localdomain
nameserver 192.168.75.2

Hope this helps some folks.

It’s always interesting when I have to sort out problems with VMWare Fusion on my Mac OS X. Right, as you guessed, interesting means frustrating. 😉 What started the whole thing was my investigating why VMWare networking would sometimes not start. I noticed the problem began after my upgrade to VMWare Fusion 3.1.0 (261058).

Rather than reboot the Mac OS X, which has fixed the problem, I tried to restart the service after closing my VMs. You can find how to do that in this older post of mine.

When I tried to restart it with the following command:

# sudo /Library/Application\ Support/VMware\ Fusion/boot.sh  --restart

I got the following error on VMWare file permissions:

VMware Fusion 261058: Shutting down VMware Fusion: 
Stopped DHCP service on vmnet1
Disabled hostonly virtual adapter on vmnet1
Stopped DHCP service on vmnet8
Stopped NAT service on vmnet8
Disabled hostonly virtual adapter on vmnet8
Stopped all configured services on all networks
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
(kernel) Kext com.vmware.kext.vmcrosstalk not found for unload request.
Failed to unload com.vmware.kext.vmcrosstalk - (libkern/kext) not found.
(kernel) Kext com.vmware.kext.vmmon not found for unload request.
Failed to unload com.vmware.kext.vmmon - (libkern/kext) not found.
 
VMware Fusion 261058: Starting VMware Fusion: 
2010-06-10 22:22:30.588 repair_packages[455:607] PackageKit: *** Missing bundle identifier: /Library/Receipts/vpn.pkg
Verifying files from package 'com.vmware.fusion.application' on '/'.
	Permissions differ on "Library/Application Support/VMware Fusion/VMDKMounter.app/Contents/MacOS/vmware-vmdkMounter", should be -rwxr-xr-x , they are -rwsr-xr-x .
	Warning: SUID file 'Library/Application Support/VMware Fusion/VMDKMounter.app/Contents/MacOS/vmware-vmdkMounter' has been modified and will not be repaired.
Finished verifying files from package 'com.vmware.fusion.application' on '/'.
Started network services
Verifying and re-installing files from /Library/Application Support/VMware Fusion/thnuclnt

Navigating to the directory, an ls -al found the two files below and their respective permissions.

drwxr-xr-x  4 root  wheel      136 Jun 10 22:51 .
drwxr-xr-x  5 root  wheel      170 May 27 21:22 ..
-rwsr-xr-x  1 root  wheel  1593620 May 21 03:51 vmware-vmdkMounter
-rwsr-xr-x  1 root  wheel  1475396 May 21 03:51 vmware-vmdkMounterTool

I thought perhaps both files required the same permissions but I was wrong. If you change the permissions on the vmware-vmdkMounterTool file, you’ll raise an error telling you that it should be -rwsr-xr-x. If you make that same mistake too, I’ve got the reset syntax at the bottom of the post.

You should only change the file permissions of vmware-vmdkMounter file. The following syntax lets you remove the sticky bit from the user permissions but you’ll need the root password (the administrator password).

sudo chmod u=rwx,go=rx vmware-vmdkMounter

That should leave you with the following permissions:

drwxr-xr-x  4 root  wheel      136 Jun 10 22:51 .
drwxr-xr-x  5 root  wheel      170 May 27 21:22 ..
-rwxr-xr-x  1 root  wheel  1593620 May 21 03:51 vmware-vmdkMounter
-rwsr-xr-x  1 root  wheel  1475396 May 21 03:51 vmware-vmdkMounterTool

When you restart you should get the following pseudo clean output. Pseudo because apparently the two errors are not meaningful. At least, I couldn’t find anything on them and VMWare Fusion now works. I’ll probably investigate this a bit more later, and I’ll update anything in this post. If you know something, post it as a comment to help everybody.

VMware Fusion 261058: Shutting down VMware Fusion: 
Stopped DHCP service on vmnet1
Disabled hostonly virtual adapter on vmnet1
Stopped DHCP service on vmnet8
Stopped NAT service on vmnet8
Disabled hostonly virtual adapter on vmnet8
Stopped all configured services on all networks
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
No matching processes were found
(kernel) Kext com.vmware.kext.vmcrosstalk not found for unload request.
Failed to unload com.vmware.kext.vmcrosstalk - (libkern/kext) not found.
(kernel) Kext com.vmware.kext.vmmon not found for unload request.
Failed to unload com.vmware.kext.vmmon - (libkern/kext) not found.
 
VMware Fusion 261058: Starting VMware Fusion: 
2010-06-10 22:58:45.276 repair_packages[861:607] PackageKit: *** Missing bundle identifier: /Library/Receipts/vpn.pkg
Verifying files from package 'com.vmware.fusion.application' on '/'.
Finished verifying files from package 'com.vmware.fusion.application' on '/'.
Started network services
Verifying and re-installing files from /Library/Application Support/VMware Fusion/thnuclnt

If you fat fingered the resetting command and also changed the vmware-vmdkMounterTool file permissions, you can reset them to shared user by using the following syntax:

sudo chmod u=rwxs,go=rx vmware-vmdkMounterTool

As always, I hope this helps others.

I’d upgraded from VMWare Fusion 2 to 3 and taken care of most instances. A NASTY surprise awaited me when I tried to upgrade my Linux installations this morning.

You MUST to uninstall VMWare tools from your repository of Linux operating systems before upgrading your VMWare Fusion or ESX server. If you don’t, you can’t access the console because the drivers make the console look like this:

Perhaps I missed this note when, as an early adopted I opted to move straight to VMWare Fusion 3. I would have complied with these instructions to avoid this headache.

All that’s required now, is that: (a) I export 500 GBs worth of virtual machines to another machine running VMWare Fusion 2; (b) Individually start each machine and run the uninstall VMWare Tools command individually; and, (c) Shutdown and reposition all virtual machines on the original server.

As stated in the note, the command to remove it is:

/usr/bin/vmware-tools-uninstall.pl

Click on the note in the event the link fails to resolve, which would mean the note vanishes into oblivion some day in the future …

While I’ve another machine that’s not yet upgraded, this is a major inconvenience. It’s a shame that the Linux components don’t install automatically. It’s a shame that the install didn’t say something like, “Don’t do this if you’ve Linux virtual machines, unless you’ve removed their VMWare Tools installation.”

Another word to the wise, you don’t get the Linux Tools automatically when you download the product. The software prompts you to download the additional components when you attempt to launch a Linux environment. A cruel irony since by the time you see the prompt, you can’t! This is a change from the prior upgrade process.

Yes, haste makes waste but now I know. In the future, treat all VMWare upgrades like those from Microsoft test, re-test, re-authenticate in a small way before upgrading. Do you think VMWare really want to send that message to its customer base?

I got back to this tonight, and thanks to Red Hat’s Session Manager I was able to fix the Red Hat VMs. Launching it, I simply switched to the Failsafe Terminal and ran the following command, as per the note:

# /usr/bin/vmware-uninstall.pl

After that, I rebooted. Then, installed (mounted the VMWare Tools disk) from the VMWare Fusion menu. Opening a terminal as root, I then re-installed and configured VMWare Fusion. Those are done. As more time allows, I’ll update about the others.

I’m now reconfiguring the network since the VMWare Fusion 2 bridged at a 172.16.153 subdomain and VMWare Fusion 3 bridges at a 172.16.123 subdomain.

Alas, poor ereg() I abused you well. PHP 5.3 has deprecated ereg() and now we must move forward with preg_match(). Along with that change, somebody asked me to show how to upload images to the file system as opposed to the database. Personally, I think they should be stored in the database.

With my bias toward databases, I threw in a virtual directory mapping in a MySQL database because it doesn’t natively support an Oracle equivalent BFILE data type. You can see this older post how to use the DBA_DIRECTORIES view in Oracle to mimic this behavior.

Naturally, MySQL is the preferred database of the person asking the question. You could also implement this exactly the same in Oracle but you really don’t want to do so. Using Oracle’s virtual directories has it’s own pre-built set of security features. They provide a more robust solution.

The code is presented as follows (setup for MySQL instructions):

1. Create and seed the DIRECTORY table in MySQL:

-- Create a directory table.
CREATE TABLE directory
( directory_id   INT PRIMARY KEY AUTO_INCREMENT
, virtual_name   VARCHAR(30)
, directory_name VARCHAR(60));
 
-- Seed the table with a virtual directory mapping.
INSERT INTO directory VALUES ( NULL,'CMS_IMAGES','C:\\Data' );

2. Create a MySQLCredentials.inc credentails file for inclusion in the PHP program:

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

3. Create the PHP uploading program, named MySQLFileUpload.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Declare input variables.
  $id = (isset($_POST['id'])) ? $_POST['id'] : 1021;
 
  // Upload a file to server in a mapped physical drive location.
  if (process_uploaded_file(map_virtual_directory($id)))
    print "Successfully Uploaded the file.<br />";
 
  // Map a virtual directory to a physical directory.
  function map_virtual_directory($virtual) {
 
    // Return successful attempt to connect to the database.
    if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
 
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Return error message.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a case insensitive dynamic SQL statement.
      $sql = "SELECT directory_name FROM directory WHERE virtual_name = UCASE(?)";
 
      // Prepare the statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
 
        // Bind the input parameter to the prepared statement.
        mysqli_stmt_bind_param($stmt,'s',$virtual);
 
        // Execute the prepared statement.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind the result to a local variable.
          mysqli_stmt_bind_result($stmt,$directory);
 
          // FetchPrepare statement and link it to a connection.
          while (mysqli_stmt_fetch($stmt))
            return $directory;
        }
        else
          // Return error message.
          print mysqli_error();
      }
      else
        // Return error message.
        print mysqli_error();
 
          // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload.
  function process_uploaded_file($directory) {
 
    /* Assume the application may allow a virtual directory with a trailing backslash or forward
       slash to be stored in the database, and manage both scenarios across Windows and Linux. */
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      if (preg_match("/\b\\\/",$directory));
      else if (preg_match("/\b\//",$directory)) {
        $directory = substr($directory,0,strlen($directory)-1);
        $directory = $directory."\\";
      }
      else $directory = $directory."\\";
    else
      if (preg_match("/\b\//",$directory))
        $directory = substr($directory,0,strlen($directory)-1);
      else
        $directory = $directory."/";  
 
    // Check for, move uploaded file, and confirm processing.
    if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
 
      // Move temporary cache into a file directory with the uploaded file name.
      move_uploaded_file($_FILES['userfile']['tmp_name'],$directory.$_FILES['userfile']['name']);
 
      // Remove this from real code, it's here for example only. ;-)
      print "Uploaded [".$_FILES['userfile']['name']."] to".$directory."<br />";
 
      // Return true to encapsulate the functional logic on success.
	    return true;
    }
    else
      // Return false to encapsulate the functional logic on failure.
	    return false;
  }
?>

4. Create a web page to test it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

<html>
<head>
<title>
  UploadFileFormMySQL.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="MySQLFileUpload.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

Hope this helps a few folks. I imagine that the prepared statement with bound variables may help a few folks because it’s not found (at writing) on the php.net web site.

When I run into failures on VMWare Fusion, they’re always a bit tedious. This one happened on my iMac (OS X Leopard) running VMWare 2 (both constrained to old releases by university governance policies). The VM is Microsoft Vista in an IDE partition, it hung after running too long. I had to force quit the application. On reboot the socket file was still there, and it gave the following error message when trying to start it.

Here’s the error in plain text, so search engines can find it for others.

Virtual device serial0: File "/var/folders/Sf/SfvoJITAHMq1Vp8bNI7QZU+++TM/-Tmp-//vmware-mmclaugh/thnuclnt-641/socket" exists, but no server is listening to it.
 
There are three possible causes for this:
 - The server is alive but not ready yet, and you can retry later.
 - The server is busy communicating with another client, so you cannot run this client at the same time.
 - A previous server exited abruptly, and you can remove the file and try again.
 
The device will be disconnected.

How to fix it?

Delete the file, right? Yes, but there’s a trick. Navigating through the -Tmp- directory required a Unix shell trick because the - (dash) is a switch and backquoting it with a \ (backslash) didn’t work. Jeff Yoder, told me the trick to change directory into a dash leading directory name. It was this:

cd -- -Tmp-

The -- is how most shells mark the end of options to a command. After a -- all - (dashes) are treated as ordinary characters.

Mark Olaveson reminded me that using the present working directory before the directory name also worked. It demotes the dash to an ordinary character too.

cd ./-Tmp-

When I got to the directory, there was the socket file. I deleted it and everything worked like a charm.

srwxrwxrwx  1 mmclaugh  staff     0 Dec  8 13:04 socket

Coming from Solaris Unix and Red Hat Linux to Ubuntu, Mac OS X and other distributions of Linux was interesting a couple years ago. The fact that you couldn’t su to the root account was tedious. It didn’t take too long (a couple momentes) to recall that I could assume those privileges in a shell, like:

admin_user@machine_name:~$ sudo sh
#

Naturally, this avoids entering sudo before a series of commands and makes administering any box simpler. It should work on all distributions but I’ve not checked ALL of them because they’re too numerous anymore. I know it works on the Mac OS X, Ubuntu, and now Fedora distributions.

Today, I got a kick from the message provided by Fedora 10 when you assume root permissions. It’s been over 20 years since I got that lecture on an AT&T box at First Interstate Bank. I imagine that any equivalent box to that is in a museum, while that bank was acquired by Wells Fargo in the early 1990s. The message from Fedora is just too funny to pass on making a comment. Here’s the screen shot:

Hope it brought a smile to some faces …

While SQL Developer is a nice tool and free, Toad is still an awesome development platform, albeit for Windows. In fact, it was annoying to have to install the Microsoft .NET Framework before installing it. It is free for MySQL!

Since my students have to do all their work in Oracle and then port it to MySQL, I demonstrate Quest’s Toad for MySQL’s at the end of the term. I don’t want them to leverage the automatic ERD diagramming while they’re learning how to do it.

There’s only one real trick to making automatic ERD diagramming work. That trick requires that you write your loading scripts for the Inno DB and use referential integrity constraints. My sample Video Store scripts for my database class are updated for MySQL referential integrity.

Unlike the friendly CASCADE CONSTRAINTS clause you can use in Oracle, MySQL won’t let you create a re-runnable script with only DDL statements. Actually, the constraint comes from the InnoDB engine. You must issue a specialized InnoDB command before running your script:

11
12
13

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0;

Primary keys are a bit different from Oracle and it appears you can’t name them, at least I couldn’t see how to do it. Here’s an example of primary and foreign key constraints in MySQL. The primary key is inline and the foreign key constraints are out of line. This example from the downloadable scripts uses self referencing foreign key constraints.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE system_user
( system_user_id              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, system_user_name            CHAR(20)     NOT NULL
, system_user_group_id        INT UNSIGNED NOT NULL
, system_user_type            INT UNSIGNED NOT NULL
, first_name                  CHAR(20)
, middle_name                 CHAR(20)
, last_name                   CHAR(20)
, created_by                  INT UNSIGNED NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             INT UNSIGNED NOT NULL
, last_update_date            DATE         NOT NULL
, KEY system_user_fk1 (created_by)
, CONSTRAINT system_user_fk1 FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, KEY system_user_fk2 (last_updated_by)
, CONSTRAINT system_user_fk2 FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Once installed (instructions are here) and connected to the MySQL database, you simply click the ERD icon in the top panel and drag the tables onto the canvas. You’ll see something like this (by the way click on the image to see its full size):

Have fun with it. It’ll be interesting to see how Oracle positions MySQL when they own it. My hunch is that they’ll continue to sell it and provide it as an open source product.

I got all the screen shots out last night, then I realized that the configuration instructions were missing. They’ve been added now, and you can check if you’re interested.

In the process, I noticed that Zend Server Community Edition is using connection pooling by default with Oracle. This means that the TNS alias must be qualified by a hostname.

For example, on your local PC with Oracle Database 10g Express Edition, the default TNS alias is XE. You can’t use that anymore when you’re running the Zend Server. You must qualify it as localhost/XE or hostname/XE as the connection string. A natural alternative is to insert a fully qualified TNS network map

Here are three possible connection patterns:

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:32:11 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1405)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "hostname/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:29:16 * (CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1403)) * establish * xe * 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<?php
  $tns = "(DESCRIPTION =
              (ADDRESS=(PROTOCOL = TCP)(HOST = mclaughlinxp32)(PORT = 1521))
              (CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = XE)))";
 
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", $tns);
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    print "Congrats! You've connected to an Oracle database!";
    oci_close($c);
  }
?>

02-JUL-2009 23:27:10 * (CONNECT_DATA=(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program?Files\Zend\ZendServer\bin\php-cgi.exe)(HOST=MCLAUGHLINXP32)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.153.138)(PORT=1401)) * establish * XE * 0

I’m sure it was in the documents but then again, I didn’t read them. 😉

I finally got around to summarizing how to use PHP to store, retrieve, and display CLOBs and BLOBs from an Oracle database. I think too often we default to BFILEs. I put all the code in zip files with instructions and suggestions for locations. This is really the second entry that goes with configuring Zend Server Community Edition or the DEPRECATED Zend Core for Oracle.

If you’re new to PHP, check out the Underground PHP and Oracle book from Christopher Jones and Alison Holloway. It’s FREE!

The Oracle LOB Processing entry is in this blog page. I know it means another click, but I’ll probably add and modify it over time. If you’ve got time and interest, take a look and let me know what you think and what improvements you’d like to see. Thanks.

I put together a post on how to upload to MySQL from a CSV file with dates. It was more or less for my students but one of them was curious how the mega formula worked. As commented, the easier solution is to create a custom format. Oddly, Open Office does support the MySQL default format natively.

Excel doesn’t support the native MySQL date format as a default format mask, which is YYYY-MM-DD, or 2009-06-02 for June 2, 2009. That means you have to convert it from a scalar date to a string or create a custom format mask (see Dmitri’s comment below). If you just shook your head at the term scalar date, maybe a custom format mask is best. However, if you want a programming solution let me explain that Excel supports only three data types. They’re a string literal, a numeric literal, and a formula. Dates in Excel are merely formatted numbers. When the numbers are integers, the date is a date, but when the number has a fractional component, the date is really a timestamp.

Here’s a brief description of the process required to convert a date in Excel into a MySQL date format string literal in a CSV file. You need the following Excel functions:

Date Functions

• The DAY(date) function returns a 1 or 2 digit numeric value for the day of the month, with ranges of 1 to 28, 1 to 29, 1 to 30, or 1 to 31 dependent on the month and year.
• The MONTH(date) function returns a 1 or 2 digit numeric value for the month of the year.
• The YEAR(date) function returns a 4 digit numeric value for the year.

Logical Functions

• The IF(logical_expression,truth_action,false_action) function returns the truth action when the expression is true, and the false action when the expression isn’t true.

MySQL Server

• CONCATENATE(string_1, string_2, ...) glues strings together.
• LEN(numeric_value) function returns the length of a string or number.

MySQL requires that you return an eight character string of numbers. The first four numbers must be a valid year, the fifth and sixth numbers a valid month, and the seventh and eigth numbers a valid day in the context of the year and month provided. Unfortunately, the DAY() and MONTH() functions may return a 1 or 2 digit value. That can’t happen in the CSV file’s string for a date, so you use the IF() and LEN() functions to guarantee a 2 digit return value.

Here are the examples that guarantee 2 digit day and month values, assuming that the base date is in the A1 cell. The concatenation of a "0" (zero between two double quotes) or the "" (two double quotes or a string null) ensures the number data types become strings.

=IF(LEN(DAY(A1))=1,CONCATENATE("0",DAY(A1)),DAY(A1))
=IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1))

A zero is placed before the day or month when the logical condition is met, which means the day or month value is a single digit string. A null is place before the day or month when the logical condition isn’t met, which means the day or month value is a two digit string. There’s only one problem with these mega functions. They return a number.

The year calculation doesn’t require the explicit casting when you concatenate it with the other strings because it is implicitly cast as a string. However, it’s a better practice to include it for clarity (most folks don’t know about the implicit casting behaviors in Excel).

=CONCATENATE(YEAR(A1),"-",IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1)),"-",IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),DAY(A1)))

As Goodwin reported in a comment, there’s an easier way that I missed. You can simply use the TEXT function when the source column is a valid serialized date value.

=TEXT(A1,"YYYYMMDD")

You can see the full MySQL import from CSV in the previous post. Naturally, you may want to copy and paste special the value before creating the CSV file. Also, don’t forget to delete any unused columns to the right or rows beneath because if you don’t your file won’t map to your table definition.