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....
(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.
Unless... I can dump the db without the keys.
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 */;
(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.
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
(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.
Finally complete! Thank you Joe for your awesome support in getting this done. CoreBOS 7 is live and operating on new server.