LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 02-05-2014, 10:16 AM   #1
unclesamcrazy
Member
 
Registered: May 2013
Posts: 189

Rep: Reputation: 1
mysql command line - create database with hyphen, dot and space


I am not able to create mysql database if database name has, hyphen (-), dot (.) or space ( ).
I am running following command.

Code:
# mysql -D mysql -e "CREATE DATABASE $var;"
Now database name is in variable $var, it is not able to create databases.

The databases names are like
data-base
data.base
data base

I do not have to create databases on mysql prompt, I have to create it on shell prompt.

Please help.
 
Old 02-05-2014, 10:38 AM   #2
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 9,767

Rep: Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886Reputation: 2886
see here: https://dev.mysql.com/doc/refman/5.7...-database.html about how can you handle special chars (there are two links to section 9.2 and 9.2.3)
 
Old 02-05-2014, 11:32 AM   #3
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 19,250

Rep: Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432Reputation: 4432
Quote:
Originally Posted by unclesamcrazy View Post
I am not able to create mysql database if database name has, hyphen (-), dot (.) or space ( ).
I am running following command.
Code:
# mysql -D mysql -e "CREATE DATABASE $var;"
Now database name is in variable $var, it is not able to create databases.

The databases names are like
data-base
data.base
data base

I do not have to create databases on mysql prompt, I have to create it on shell prompt.Please help.
While you *CAN* do what you want, it is a bad idea to do it. Even MySQL recommends strongly against it, and for good reason. The MySQL prompt isn't the only thing that has trouble with those characters...things like backup utilities and other database administration tools will also. Good luck getting things like phpmyadmin to work.

The best way for us to help you, is to tell you not to do it. You're only going to cause yourself MAJOR headaches in the future.
 
Old 02-06-2014, 12:37 AM   #4
unclesamcrazy
Member
 
Registered: May 2013
Posts: 189

Original Poster
Rep: Reputation: 1
Thanks pan64, I read that page before posting my question here. It was not much help for me because I wanted to create database desperately with these special characters (I did not read TB0ne's answer )

Thanks TB0ne for your accurate suggestion.
We use phpmyadmin as a mysql client in our organization. Phpmyadmin allows user to create database with these special characters. The problem came forward when a developer created a database name "vpc-1.2 3" (Now I understand how dangerous name was it), generally developers took back up of their all databases frequently using import export facility of phpmyadmin. When I studied it deeply, I found phpmyadmin converted a space ( ) into underscore (_) in the name of exported sql file. Actually problem was not found yet because developer can import any sql file into any database, it is not necessary that sql file name and database name should be same.That's why developer did not know that the sql file name was "vpc-1.2_3.sql" and database name was still "vpc-1.2 3"

Developers run a script frequently which saves every database of phpmyadmin individually in a directory (Ex- database-name1.sql, database-name2.sql, database-name3.sql)

The problem was found when a developer's all mysql databases are dropped , the phpmyadmin was completely empty.
There was another script which imports all databases from sql files. First it creates all databases then it imports them.
Since our script exports sql files and the name of sql file is same as name of the database so it is easy for us to create database and import them using script.
Code:
# mysql -h 127.0.0.1 -u root -p'' -e "CREATE DATABASE $var;"
# mysql "$var" < "/path of/sql/file"
Now sql file name contains hyphens and dots that's why variable contains these special characters but create database does not allow with these characters so simply it does create the database and when database is not created it does not import the sql file in it.
That the problem developer reported
"Your script does not import all databases in phpmyadmin, I have to import 7 databases manually. Here are the list of databases which are skipped."
and in the list, the name was there vpc-1.2 3.

--Thanks & Kind Regards
Sam
 
Old 02-06-2014, 06:22 AM   #5
eklavya
Member
 
Registered: Mar 2013
Posts: 622

Rep: Reputation: 136Reputation: 136
Quote:
Originally Posted by unclesamcrazy View Post

Code:
# mysql -D mysql -e "CREATE DATABASE $var;"
Now database name is in variable $var, it is not able to create databases.
The databases names are like
data-base
data.base
data base
If database name is stored in the variable and there are special characters in the database name, you can replace special characters from database name. Use another variable and replace all special characters with underscore. Underscore is allowed by mysql.
Code:
var2=$(echo $var | sed -e 's/\./_/g' -e 's/-/_/g' -e 's/ /_/g')
As you said vpc-1.2 3 is like nightmare for you
Code:
var2=$(echo "vpc-1.2 3"  | sed -e 's/\./_/g' -e 's/-/_/g' -e 's/ /_/g')
var2 will store value vpc_1_2_3 in this case, now you can run your commands.
Code:
# mysql -h 127.0.0.1 -u root -p'' -e "CREATE DATABASE $var2;"
# mysql -h 127.0.0.1 -u root -p'' "$var2" < "/path of/sql/file"
I am not giving you any solution, I am just suggesting you. You can find your own as you are the one who knows about your environment and problem.

Last edited by eklavya; 02-06-2014 at 06:23 AM.
 
Old 02-06-2014, 12:40 PM   #6
unclesamcrazy
Member
 
Registered: May 2013
Posts: 189

Original Poster
Rep: Reputation: 1
Thanks Eklavya
Right now I have implemented it temporarily according to your suggestion until I get better solution. It replaces these three special characters with single underscore, double underscore and triple underscore respectively so data-base, data.base or data base will not have same names and it will not give database already created for rest two databases.I wonder why it is friendly with underscore yet it is another special character.
Now developers will not complain that script is not importing all databases.

If script finds a database name with special characters which is going to be replaced, I have echoed a line like this..."Your database 'db-name' contains special character, its name has been changed to 'db_name'. Change in your project's config file according to it"

Here in LQ there are very smart and expert people, you have mentioned some name in your signature or my senior admins will change their mind and help me then I will get perfect solution and I will use it.

Thanks.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] yum install php-mysql create problem to my running mysql database jsaravana87 Linux - Server 1 08-11-2011 04:24 AM
How do I create a new mysql user and a mysql database at the shell prompt? puppymagic Linux - Newbie 1 08-07-2010 07:33 AM
How to create mysql database from command line narendra1310 Linux - Software 3 02-15-2010 08:55 AM
mysql: cannot create database deostroll Linux - Server 4 08-04-2009 02:17 PM
MySQL - command - create user/single database dmedici Linux - Software 3 06-21-2004 04:17 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 12:37 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration