Hello, fellow forum-goers.

This is kind of a random question and searching in all the usual places didn't turn-up any answers. If I have missed this information in the MySQL manual (should somebody find it contained therein), please bring the relevant section to my attention. I have looked and there was no mention of whether or not spaces are permitted in configuration files (I would think they'd have to be) and if so, what sort of escaping might be required.

The manual does say this (about 1/3 of the way down the page - http://dev.mysql.com/doc/refman/5.0/en/option-files.html ):

"This is equivalent to --opt_name=value on the command line. In an option file, you can have spaces around the ‘=’ character, something that is not true on the command line. You can enclose the value within single quotes or double quotes, which is useful if the value contains a ‘#’ comment character or whitespace."

But that doesn't seem to be true, as I shall attempt to support below.

Basically, I want to use a directory with spaces in its name as the data directory for MySQL ("datadir" in the my.cnf configuration file). I am running MySQL 5.0 on Ubuntu Linux. The default my.cnf file looks like this:

[mysqld]
#

* Basic Settings

#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

I want to change the datadir value to something more like:

/home/username/MySQL Data/Version 5/

Notice the spaces in the path. Now, if I paste that path name into the my.cnf file in place of the default (/var/lib/mysql), as you would expect, mysql spits back:

df: `/home/username/MySQL/.': No such file or directory
* /etc/init.d/mysql: ERROR: The partition with /home/username/MySQL is too full!

So, clearly, mysql is interpreting the space character as an attempt to denote a new directive (or add an additional parameter to the current value). Naturally, I then attempted to put quotes (both double and single quotes) around the path name. I get the same error.

Is it possible to use spaces the mysql my.cnf file, in the datadir path name value? If so, how? A syntax example would be great.

Thanks for any insight!

    Thanks for the quick response.

    I didn't paste the entire my.cnf because the problem is clearly with that one particular line (as the server starts just fine when I leave the default path in-place). But here's what that section looks like in the three different things I've tried:

    [mysqld]
    #

    * Basic Settings

    #
    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = "/home/ben/storage/Important/My Documents/MySQL Data"
    tmpdir = /tmp
    language = /usr/share/mysql/english

    I have also tried that troublesome line without the quotes, in which case the line looked like this:

    datadir = /home/ben/storage/Important/My Documents/MySQL Data

    and, finally, I have tried it with single quotes:

    datadir = '/home/ben/storage/Important/My Documents/MySQL Data'

    Whenever I try to start the server, I receive the error message (in the terminal) that I posted before. Is there somewhere else I can look? I have looked in the only two MySQL-related log files I can find (/var/log/mysql.log and /var/log/mysql.err) and both files are empty. But, in the same directory, daemon.log shows the expected:

    Jan 30 21:12:34 prick /etc/init.d/mysql[3452]: ERROR: The partition with /home/ben/storage/Important/My is too full!

    Of course, the error states that the partition is too full, but it would appear that that's a poorly-handled "I can't find the location you specified because the space characters are confusing me" error.

    Thanks again!

      How do you start the MySQL server? Have you tried passing the data dir on the CLI? Simple example:

      mysqld --datadir="/home/ben/storage/Important/My Documents/MySQL Data"

      EDIT: The obvious solution/workaround would be to simply use a path without spaces in it, in case you get that frustrated, but you're right - it should work.

        When I try your suggestion and set the directive on the command line, the server starts just fine. So, it seems to be a problem with the my.cnf file, specifically. But to answer your question, I was starting the server with:

        $ sudo /etc/init.d/mysql start

        You're right, I could change the path name to something without spaces, but I have other dependencies (all of my Mozilla accounts, Apache-related configuration files, etc.). But above all else, I want to know what's causing the problem so I can avoid future problems of a similar nature. My understanding is that Linux treats spaces just like every other character, so they aren't usually a problem (as long as quotes are used).

        Any other ideas I might try?

        Thanks again for the help!

          Last thing I can think of is to put \ in front of the spaces. But I'm betting it won't work.

          My\ Directory

          with and without quotes. See which, if either works.

          Have you checked the mysql bug database to see if this was reported and resolved? You could be hitting a bug that's fixed in later versions.

            Thanks for the reply, Sxooter.

            I had actually tried escaping the spaces with backslashes earlier (and thought, "This will never work..."), but I did try it. And, needless to say, it didn't work. I also tried replacing the spaces with \s as the manual suggests doing on the command line. That didn't work, either. I have tried all combinations: with no quotes, with single quotes, with double quotes, escaping spaces with \, escaping spaces with \s, etc.

            Now I'm pouring over the MySQL bug reports (there are lots that fit the search terms). I just find it hard to believe that nobody in the modern Linux community has tried using a datadir path that contains spaces. Perhaps the Linux natives would never even think of putting a space in a file name, but those of us who are migrating over from Windows are still hung-up on our pretty file names. Similarly, those Mac users on OS X love them some spaces in folder names, yet nobody has run into this issue before? It seems ludicrous.

            The Windows version handles spaces just fine (using double quotes), and that version seems to be the shabbiest of them all. Go figure.

            I didn't think it would make any difference, but I am on a 64-bit version of Ubuntu. Do one of you guys mind duplicating your data directory real quick, putting a space in the name, and then pointing your my.cnf file to the new directory with the space in its name? (And start the server, of course.) Do you have the same problem, or could this be some bizarre 64-bit bug?

              Sorry, I don't run MySQL on my laptop. And not on anything I can shut down for testing.

              And I have pretty path names. they_look_like_this... 🙂

                cbj4074 wrote:

                Do one of you guys mind duplicating your data directory real quick, putting a space in the name, and then pointing your my.cnf file to the new directory with the space in its name?

                Sorry - only server I have this kind of access to is my own, which is running on Windows. Otherwise, I definitely would - this problem is intriguing to me as well.

                I do have a crapload of Ubuntu CD's, though, just can't seem to find a single one.. perhaps I've given them all away?... 🙁

                  Thanks for the replies, fellas.

                  I do have a crapload of Ubuntu CD's, though, just can't seem to find a single one.. perhaps I've given them all away?...

                  If that's true, I'd rather see a smile than a frown! That means that more and more people are choosing not to be slaves to Windows, even if it's an uncomfortable transition.

                  Anyhow, I couldn't find an official bug report on this issue, and I looked through virtually all of them (some 250 that matched the relevant search string). Some were close, but no bug particularly identified this issue.

                  So, I guess I'll file a bug over there and see what the developers say. I'm going to leave the thread open until I hear back from them as to whether or not it's an actual bug.

                  Thanks!

                    Make sure you post back here with their suggestion/resolution... not only so other people who search for this problem can see the solution, but as I said, I'm curious as well! 😉

                      I just noted that it said to enclose it in SINGLE QUOTES.

                      i.e.:

                      datadir = '/mnt/data/My Database Server'

                      Not double quotes. Have you tried single quotes?

                        8 months later

                        It's only about a year later, at this point, but I now find myself coming back to this thread after another MySQL user confirmed the issue I had described in the initial bug report on the MySQL bug tracker, which can be found here:

                        http://bugs.mysql.com/bug.php?id=26033

                        I still do not have a solution to this problem, and, unfortunately, am temporarily confined to a Windows machine, and therefore cannot, at present, continue working to find a solution.

                        And, to answer your question, Sxooter, yes, I had tried it with single-quotes, too, which is apparent in post #3 of this thread. It was a lot of info and I neglected to use the appropriate tags, so I'm not surprised you missed it; no problem! The bug report on mysql.com also states that I had tried single-quotes.

                        Is there anybody out there who can test this on an AMD64 Ubuntu installation? They obviously haven't fixed the bug yet, since the ticket is still open, but it's worth a shot.

                          2 years later

                          Have you tried creating a link to the folder (without spaces in the name) and then using that in your configuration file?

                            2 years later
                            Write a Reply...