1. 

   ajdjackson, Member

   Hi

   I attempting to move a MariaDB (Server version 10.3.7-MariaDB) instance of mydbr to MySql 8.0.

   I've managed I hope to move the mydbr database although I did have a few issues - (I had to remove NO_AUTO_CREATE_USER from dump and add SET GLOBAL log_bin_trust_function_creators = 1; to dump file).

   I'm now trying to move the data db. This database has several tables with generated columns and I'm getting stuck (on the first table with generated columns) with the following error: Error Code: 3105. The value specified for generated column 'ir_cost' in table 'tblir_saleshist' is not allowed.

   Is there a way around this that you know off? I'm using mysqldump 10.6.

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   While MySQL and MariaDB largely share same codebase, there are differencies. Just take a look at the syntax in the dump file for the generated column and see what is it in it that causes the error. You may have to do some changes to the dump file so that you can load it in.

   Without the actual code fot the table tblir_saleshist, it is difficult to be more specific.

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   It's a very simple table with a sample of the data:

   CREATE TABLE `tblir_saleshist` (
   
     `ir_prodid` int(11) NOT NULL DEFAULT 100,
   
     `ir_period` date NOT NULL,
   
     `ir_qty` int(11) DEFAULT NULL,
   
     `ir_val` float DEFAULT NULL,
   
     `ir_cost` double GENERATED ALWAYS AS (`ir_val` / `ir_qty`) VIRTUAL,
   
     `ir_netrev` float DEFAULT NULL,
   
     `ir_netper` float GENERATED ALWAYS AS (`ir_netrev` * 100 / `ir_val`) VIRTUAL,
   
     PRIMARY KEY (`ir_prodid`,`ir_period`)
   
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   /*!40101 SET character_set_client = @saved_cs_client */;
   
   --
   
   -- Dumping data for table `tblir_saleshist`
   
   --
   
   LOCK TABLES `tblir_saleshist` WRITE;
   
   /*!40000 ALTER TABLE `tblir_saleshist` DISABLE KEYS */;
   
   INSERT INTO `tblir_saleshist` VALUES (100,'2018-01-31',9419,1648000,174.96549527550695,609000,36.9539),(100,'2018-02-28',9460,1656000,175.05285412262157,539000,32.5483),(100,'2018-03-31',11944,2090000,174.98325519089082,1017000,48.6603);
   
   /*!40000 ALTER TABLE `tblir_saleshist` ENABLE KEYS */;
   
   UNLOCK TABLES;

   I manually recreated all the tables tables that have generated columns so I have the dbs copied.

   A couple of things I stumbled over when doing this was:

   • The 2 instances were not running the same mydbr versions
   • The Stored Procedures had different creators - probalby to do with using root to do the importing instead of the mydbr account.
   • A couple of reports fell over due to referencing temporary tables in same statement although this works in MariaDB.
   
   

   
   Cheers and thanks

   Jake

Reply

You must log in to post.