Posts: 3,565
Threads: 36
Joined: Apr 2014
Reputation:
49
you have to create a backup of your production database using mysqldump:
mysqldump -u {user} -p {your_db_name} > dbbackup_Ymd.sql
this will create a total dump of your database into the file dbbackup_Ymd.sql
now you can take this file to any other server in the world and recover it there. Basically, create a new database there, with UTF8 charset and then load the dump into it with:
mysql -u {user} -p {new_db_name} < dbbackup_Ymd.sql
So, when I say "copy the database", I mean make a dump of the database and recover it into a new database using the procedure above
keep asking....
Joe
TSolucio
Posts: 169
Threads: 41
Joined: Apr 2015
Reputation:
24
(03-04-2017, 10:04 PM)joebordes Wrote: you have to create a backup of your production database using mysqldump:
mysqldump -u {user} -p {your_db_name} > dbbackup_Ymd.sql
this will create a total dump of your database into the file dbbackup_Ymd.sql
now you can take this file to any other server in the world and recover it there. Basically, create a new database there, with UTF8 charset and then load the dump into it with:
mysql -u {user} -p {new_db_name} < dbbackup_Ymd.sql
So, when I say "copy the database", I mean make a dump of the database and recover it into a new database using the procedure above
keep asking....
Thank you for the clarification... I'm getting an error when I import the dump and I'm not sure we'll get past this one unless you've got some magic in a hat somewhere.
Code: ]$ mysql -u mweaver corebos7 -p < vtcrmdump.sql
Enter password:
ERROR 1215 (HY000) at line 3166: Cannot add foreign key constraint
 When I log in, except for the home page, other pages are blank. Essentially anything that depends on the database for content to populate the page is blank, so unless you've got a little more magic in your hat I'm not sure we're going to get past this one. That poor old fart of a database has seen a few things over the years. Originally it was a vTiger database that got migrated through a few versions. Then, it was converted to CoreBOS a few years ago. I might just be asking too much of it. Unless there's a way to convert that I'm not aware of.
Mark
[i]If I could work while on horseback that's where I'd be doing it. There's nothing like riding... Gives new meaning to the phrase, ride to live... live to ride.[/i]
Posts: 169
Threads: 41
Joined: Apr 2015
Reputation:
24
Unless... I can dump the db without the keys.
Mark
[i]If I could work while on horseback that's where I'd be doing it. There's nothing like riding... Gives new meaning to the phrase, ride to live... live to ride.[/i]
Posts: 3,565
Threads: 36
Joined: Apr 2014
Reputation:
49
this one is easy to fix. edit the .sql dump file and add this to the top
Code: /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
the line that is doing the magic is:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
Joe
TSolucio
Posts: 169
Threads: 41
Joined: Apr 2015
Reputation:
24
(03-04-2017, 11:55 PM)joebordes Wrote: this one is easy to fix. edit the .sql dump file and add this to the top
Code: /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
the line that is doing the magic is:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
this is what was at the top of the file already:
Code: /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
This time the error was:
Code: ERROR 1215 (HY000) at line 3165: Cannot add foreign key constraint
The SQL after the line mentioned above...
Code: DROP TABLE IF EXISTS `vtiger_customview`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vtiger_customview` (
`cvid` int(19) NOT NULL,
`viewname` varchar(100) NOT NULL,
`setdefault` int(1) default '0',
`setmetrics` int(1) default '0',
`entitytype` varchar(25) NOT NULL,
`status` int(1) default '1',
`userid` int(19) default '1',
PRIMARY KEY (`cvid`),
KEY `customview_entitytype_idx` (`entitytype`),
KEY `userid_idx` (`userid`),
KEY `setmetrics` (`setmetrics`),
CONSTRAINT `fk_1_vtiger_customview` FOREIGN KEY (`entitytype`) REFERENCES `vtiger_tab` (`name`) ON DELETE CASCADE[/color]
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
It occurred to me to remove the foreign key references until I really looked at the statement and saw what it was there for.
Mark
[i]If I could work while on horseback that's where I'd be doing it. There's nothing like riding... Gives new meaning to the phrase, ride to live... live to ride.[/i]
Posts: 3,565
Threads: 36
Joined: Apr 2014
Reputation:
49
I see you have commented directives before the table definition. These are applied as they are found. So if you have the one we need at the top:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
and then after that assignment you have another one that undoes that, maybe something like this:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1 */;
then you will have this problem. In other words, search your dump for the FOREIGN_KEY_CHECKS directive and make sure you have only one at the top that deactivates it
Joe
TSolucio
Posts: 169
Threads: 41
Joined: Apr 2015
Reputation:
24
(03-05-2017, 10:40 AM)joebordes Wrote: I see you have commented directives before the table definition. These are applied as they are found. So if you have the one we need at the top:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
and then after that assignment you have another one that undoes that, maybe something like this:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1 */;
then you will have this problem. In other words, search your dump for the FOREIGN_KEY_CHECKS directive and make sure you have only one at the top that deactivates it
on it boss. I'll post back and let you know how it goes.
Mark
[i]If I could work while on horseback that's where I'd be doing it. There's nothing like riding... Gives new meaning to the phrase, ride to live... live to ride.[/i]
Posts: 169
Threads: 41
Joined: Apr 2015
Reputation:
24
Finally complete! Thank you Joe for your awesome support in getting this done. CoreBOS 7 is live and operating on new server.
Mark
[i]If I could work while on horseback that's where I'd be doing it. There's nothing like riding... Gives new meaning to the phrase, ride to live... live to ride.[/i]
Posts: 3,565
Threads: 36
Joined: Apr 2014
Reputation:
49
My pleasure :-)
Joe
TSolucio
|
