Testing Latest Version (error)

[joebordes]

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....

[mweaver]

(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.

[mweaver]

Unless... I can dump the db without the keys.

[joebordes]

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 */;

[mweaver]

(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.

[joebordes]

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

[mweaver]

(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.

[mweaver]

Finally complete! Thank you Joe for your awesome support in getting this done. CoreBOS 7 is live and operating on new server.

[joebordes]

My pleasure :-)