Hello,

I have a mysql database that keeps track of some users and their files.
The database is manipulated with an iphone app that communicates with a php
script on a server.

I put this together last year, and it was my first foray into mysql. It has
worked great this whole time, but now I need to make some changes.

Being new to mysql, I just wanted to ask if it poses any problems to go in
there and add attributes to tables and possibly add other tables? You can
modify the structure once it is already in use, right? As long as the
existing structure still functions like it did before?

I just don't want to screw anything up with it as I make changes!
Thanks
B

Bint:


Exactly.
Keep in mind that you can define default values for new table fields.
Your existing records will also contain the new fields, so it might
make sense to have appropriate default values for records that haven't
been assigned values so far.

Oh: have you considered copying your database first and use it as a
test database? That way, you can experiment all you like, without
destroying any valuable data in your production database.
And...at least make a backup first (if you don't have one yet).

Enjoy and good luck.

--
Erick

On 09-02-11 18:18, Bint wrote:
Adding tables is not a problem,

The only problem i see in changing tables is, when you do something like
this now:
SELECT * from tablename;

If you do it like above, than you should first change the code to look like:
SELECT field1, field2 FROM tablename;
before you can add/change fields in this tables.

And, of course, *backup* is a safe to to begin with......

--
Luuk

Thanks, I did make a backup. Hopefully I will not have to use it!

Would it be easy to make a copy of the current database to use as an
experiment? How would I transfer the changes back to the original -- just
redo them?

Thanks
B


On 2/9/11 11:23 AM, in article 8rg0sdFcn4U1 (AT) mid (DOT) individual.net, "Erick T.
Barkhuis" <erick.use-net (AT) ardane (DOT) c.o.m> wrote:

Bint:

[Fullquote and toppost corrected.
Please, refrain from that. Thank you!]

You might want to use it to copy the database!

What I had in mind was:
1. Backup table definitions and data
2. Create a second database (Test-DB), and import the backup from (1).
A tool like phpMyAdmin will come very handy
3. Copy your current scripts to another subdomain/directory and change
the DB-connection in that version to reflect the name of the Test-DB.

Now, you should be set. You have a working copy of your old application
(software and database) while the live system is still running.
You want to make changes to your Test-DB, and expand the functionality
in your Test-scripts to see if it works. Also, you will find out
whether or not you need to program, test and perform a data conversion.

[This is assuming you don't use some type of software version
management tool].

Once you're done, you can implement the changes in your live system.
All by hand, or partly automated (e.g. using MySql Workbench or such
tool).


--
Erick

Bint wrote:
yes. You can change tables by adding fields, or add tables, seamlessly.
Modifying fields or removing them is much riskier, as you then have to
consider if your application code will try to explicitly access fields
that no longer exist and throw an error.

But I have e.g. turned a BLOB into a MEDIUM_BLOB (IIRC) to get an
oversize image to fit, with no side effects beyond thecode suddenly
working like it should, and not sending me truncated pikkies! :-)
test afterwards, but yes, in general this is not a huge issue at the
MySQL level. It is far more an issue with the applications that use the
database.

On Feb 9, 5:26*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:
Not as devastating as INSERT INTO tablename VALUES (...)

Learn how to use ALTER TABLE (and how to undo changes you make).
Also it's probably best to have a test database and a production
database. And learn how to make backups and restore them.

There are some gotchas which you need to watch out for, but you
can avoid these with careful SQL programming. You might want
to change some existing code.

Adding new tables should not cause problems.

Adding columns to tables might cause problems (especially if they
are not added at the end). Re-ordering columns within tables might
cause problems.

For example:

INSERT INTO members VALUES (756, 'John Doe', 'john (AT) doe (DOT) com', 'Y');
will quit working if you add a 5th column and/or re-order the
columns. Fix: list the column names.

INSERT INTO members (id, name, email, active)
VALUES (756, 'John Doe', 'john (AT) doe (DOT) com', 'Y');

SELECT * from members;
might quit working, especially if you re-order the columns and
your access to the columns is by order, not by name. Fix: list
the columns you want. Not retrieving columns you don't need will
save a small amount of CPU time and network activity.


Make backups.

On Feb 9, 5:18*pm, Bint <b... (AT) ign (DOT) com> wrote:
Surely you are not making changes to the live system first???

You make changes on your development system, then test the changes and
put them live when you are happy that all is working fine.

On 2/9/11 2:36 PM, in article
d06a4383-dd44-4f4d-b097-c49e4d8c49ef...oglegroups.com, "Captain
Paralytic" <paul_lautman (AT) yahoo (DOT) com> wrote:

Yikes! I have that in my upload script. It's ok if I name the values,
right? But I don't. I just use INSERT INTO files VALUES (...).

Still, though, if I added the new field at the end and gave it a default
value, won't it still work ok?

Thanks
B