database structure

cqball2000

How can I extract database structure only. I currently use

mysqldump -h hostname -u user --opt dbname > db_bak.sql

to dump both the db structure and data. What is the option or method for retrieving the structure only.

Thanks,
Dave

lawnmowerman

Try:

mysqldump -h hostname -u user --opt -d dbname > db_bak.sql

The '-d' (same as '--no-data') tells mysqldump not to include row information.

The inverse is to use '-t' (same as '--no-create-info') which tells mysqldump not to dump the table creation info.

Check out 'mysqldump -h' for further options.

-Rich

cqball2000

Thanks Rich, that worked perfectly.

So now I've got my schema. When I try to creat the following:

CREATE TABLE account_logging (
gid varchar(20),
cid varchar(20),
fid varchar(20),
status tinyint(1),
amtdue varchar(20),
payby varchar(14),
last_amt_pd varchar(20),
last_pymt_date varchar(14),
balance varchar(50),
when varchar(14)
);

...it gives me an error at "when varchar(14)"...so i changed the field name to "test" and it worked fine. Why is it giving me an error? I can't change my field names, they must stay named as "when".

Thanks!
Dave

lawnmowerman

Glad to hear that command worked how you wanted 🙂

The reason that creating a table with that definition is because "when" is a reserved word.

Have you upgraded MySQL since this database was first created? An old version the only way I can think of to use a reserved word as a column name.

Can you elaborate on why you can't change the column name from "when"? I may be able to offer a solution.

Oh, one more thing you could try is copying the files from one directory to another. You can use 'mysqlhotcopy -u user currentdb newdb' to safely duplicate the database. At that point, you could use 'mysql -u user newdb' and run 'DELETE FROM Table' to remove every row. Just make sure you're in the NEW database or you'll delete EVERY row from the old database.

With this new database, you can copy the files to another server or whatever else you wish to do with them.

-Rich

cqball2000

Hey Rich,

Since I made that last post, I went ahead and changed all the columns referenced 'when' to 'date'. I dumped the schema from the old server running 3.22 mysql to a new server running 3.23. Then I ran replace when date -- ./* for my app files and it works perfect.

At first I didn't think I could change all references 'when' to 'date'. But then a light bulb turned on. Let's just hope the bulb doesn't burn out too soon.

Is chaning the field name to 'date' appropriate?

Thanks again,
Dave

lawnmowerman

Actually, I'm surprised 'date' worked when 'when' didn't. They are both reserved words. shrug

Check out:
http://www.mysql.com/doc/R/e/Reserved_words.html

This page lists the reserved words recognized by MySQL. It is just good practice to avoid all reserved words when naming columns, variables, etc.

Just because it works in the current version of the software, doesn't mean it'll work on all version of the software or other software...as you have seen 🙂

-Rich

cqball2000

AHHHH! Very frustrating. Thanks for pointing that page out to me. What would you recommend as a good field name for the date which includes the time as well?

lawnmowerman

That would depend on what the date was reflecting.

For example, in one of my databases, I have datetime fields which gets updated with the first and last time and date a cable modem was seen on the network. For this field, I used cmfirstseen and cmlastseen...which reads as "cable modem first seen" and "cable modem last seen".

Simply using the field name 'date' here, would not describe fully the use of that field. Even if there was only one field, say the last seen field, 'date' would not really tell me what the data in that column really represents.

I recommend using variable and field names which accurately represent the data contained therein while keeping the length to a minimum.

If you'd like to keep the date that a transaction was made, how about trans_date? If you'd like to keep track of the last time a credit card was used, how about cc_lastused? To keep track of the date the customer opened the account, how about account_created?

These are just a few examples based on the topic of your postings 🙂

Hope this helps.

-Rich