Sign in to follow this  
Drethon

[web] Copying a database

Recommended Posts

I copied my database to another computer but my functions don't work properly on the new computer. I used the command "mysqldump DBName > backup.sql" one one end and "mysql < backup.sql", with all the appropriate usernames and password (I think all the formatting is correct). The database copied over properly and a quick scan showed that everthing was in place. When I entered my functions into the new database, I keep them in a text pad for quick loading, most of them worked fine but a couple produced a result of no rows or columns read or modified or something like that. These functions work just fine in the origional database. One of the functions updates a table but the other one only reads tables so there is no real relationship between the functions I can see. In the new database I gave full access to the root user, which is what I'm using for development, for this new database. Is there something else I may have missed at the database level? If its not a simple answer I'll start debugging the functions in more detail but I don't see how one can work on one database but not the other...

Share this post


Link to post
Share on other sites
There is not enough information above to know the answer.

Quote:

When I entered my functions into the new database, I keep them in a text pad for quick loading...


Do you mean user-defined functions?

If so, what do they do?

Quote:

, most of them worked fine but a couple produced a result of no rows or columns read or modified or something like that.


"Or something like that?" please be more specific.

Quote:

These functions work just fine in the origional database. One of the functions updates a table but the other one only reads tables so there is no real relationship between the functions I can see.


1. Are the versions of MySQL exactly the same on the two machines?
2. Are any relevant server-variables set the same on the two machines?
3. Are any database-level parameters the same in the two databases (I am not sure how many of these there are or how much they affect things; the only one I'm aware of is DEFAULT CHARACTER SET).

Mark

Share this post


Link to post
Share on other sites
One other factor that can cause problems is if you have at any point manually modified values in an autonumbered field. If the original database had a zero, this will not be imported correctly (instead of being set to zero, it is set to the next autoincrement value, which means that subsequent inserts will fail).

Share this post


Link to post
Share on other sites
My biggest problem is these are the first two SQL databases I've made. I created them with the create command and thats about it. I have no idea what could possibly be different between the databases or where to look. Both databases are MySQL 5.0.

Its going to be a couple days before I can run the functions (yes, user defined) on my home machine. I'll get more detailed information on the error message then but I believe the message was pretty close to no columns or rows read or modified.

One of the functions is as follows, its a little disorganized at the moment as I need to go back and reorder some of the if blocks:

delimiter //drop function CM_GET //
create function CM_GET ( ItemID int , PlayerID int ) returns varchar ( 528 )
BEGIN

declare actionResult varchar(528);
declare success int unsigned ;
declare playerStrength int unsigned ;
declare itemWeight int unsigned ;
declare weightCarried int unsigned ;
declare handID int unsigned ;
declare itemInHandID int unsigned ;
declare itemContainerID int unsigned ;
declare playerContainerID int unsigned ;

IF ( ItemID=PlayerID )
THEN

set actionResult = "You can not pick up yourself!\n" ;

ELSE

select container.ContainerOf
into itemContainerID
from item
join container
where
item.ID=ItemID AND
item.ItemConnection=container.id ;

select container.ContainerOf
into playerContainerID
from item
join container
where
item.ID=PlayerID AND
item.ItemConnection=container.id ;

IF ( itemContainerID!=playerContainerID )
THEN

select CONCAT( item.name , " is not in the same room as you.\n" )
into actionResult
from item
where
ID=ItemID ;

ELSE


select StrengthStat
into playerStrength
from Stats
where
id=PlayerID ;

select Weight
into itemWeight
from item
where
id=ItemID ;

set weightCarried=0 ;

set success = ( playerStrength * 5000 ) / ( itemWeight + weightCarried );

IF ( success >= 1000 )
THEN

select id
into handID
from container
where
ContainerOf=PlayerID AND
Location=50;
IF ( itemcontainerID!=PlayerID )
THEN

select id
into handID
from container
where
ContainerOf=PlayerID AND
Location=50;

select id
into itemInHandID
from item
where
container=handID ;

IF ( itemInHandID IS NULL )
THEN

update item
set container=handID
where
id=ItemID ;

select CONCAT( "You pick up the " , item.name , " in your right hand\n" )
into actionResult
from item
where
ID=ItemID ;

ELSE

set itemInHandID = NULL ;

select id
into handID
from container
where
ContainerOf=PlayerID AND
Location=51;

select id
into itemInHandID
from item
where
container=handID ;

IF ( itemInHandID IS NULL )
THEN

update item
set container=handID
where
id=ItemID ;

select CONCAT( "You pick up the " , item.name , " in your left hand.\n" )
into actionResult
from item
where
ID=ItemID ;

ELSE

set actionResult = "Both of your hands are full.\n" ;

END IF ;


END IF;

ELSE

select CONCAT( "You already have the " , item.name , ".\n")
into actionResult
from item
where
ID=ItemID ;

END IF ;

ELSE

select CONCAT( "The " , item.name , " was too heavy for you to pick up!\n")
into actionResult
from item
where
ID=ItemID ;

END IF ;
END IF ;
END IF ;

return actionResult ;

END //
delimiter ;

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this