MyDump BackupQuery long string problem

[Khemiri Aladdine]

Hi,
I have problem with restoring mysql table with generated columns, so as a workaround, I dump schema table and data in separate sql script files. the data file contain all columns but not generated ones.

schema file is successfully restored,
data file can be restored only if tmpsql string query parameter is not too long, otherwise it freeze.
i tried either with

Code: Select all

TMyDump1.BackupQuery(tmpsql) 

and

Code: Select all

TMyDump1.BackupToFile('c:\table.sql',tmpsql)

thanks in advance for your kind help.

[ViktorV]

Unfortunately we could not reproduce the issue.
Please compose a small sample demonstrating the described behavior and send it to us to us via e-support form: https://www.devart.com/company/contactform.html, including scripts for creating database objects.

[Khemiri Aladdine]

Hi Viktor,
Sorry, i found it, it's because I did not quote fields name, I by mistake use a mysql keyword as a column name.

but to avoid this workaround, is it possible to backup and restore a table containing generated column ?

these are steps to reproduce the issue :
1.creating database (use script below)

Code: Select all

-- 
-- Database medical structure
-- 

USE mysql;
DROP DATABASE IF EXISTS medical;
CREATE DATABASE medical;
USE medical;

-- 
-- Table structure for table patients
-- 

DROP TABLE IF EXISTS patients;
CREATE TABLE `patients` (
  `IdPatient` int(11) NOT NULL AUTO_INCREMENT,
  `DateNaissance` date DEFAULT NULL,
  `Nom` varchar(30) DEFAULT NULL,
  `Prenom` varchar(30) DEFAULT NULL,
  `Sexe` varchar(1) DEFAULT NULL,
  `Adresse` varchar(255) DEFAULT NULL,
  `City` varchar(30) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  `Mail` varchar(60) DEFAULT NULL,
  `Notes` text,
  `v_PatientName` varchar(255) GENERATED ALWAYS AS (trim(concat(if(isnull(`Prenom`),'',trim(`Prenom`)),' ',if(isnull(`Nom`),'',trim(`Nom`))))) VIRTUAL,
  PRIMARY KEY (`IdPatient`),
  UNIQUE KEY `IdPatient` (`IdPatient`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=208;

-- 
-- Dumping data for table patients
-- 

LOCK TABLES patients WRITE;
INSERT INTO patients(IdPatient, DateNaissance, Nom, Prenom, Sexe, Adresse, City, Phone, Mail, Notes) VALUES
 (1,NULL,'KHEMIRI73','Aladdine23',NULL,NULL,NULL,NULL,NULL,NULL),
 (2,NULL,'KHEMIRI34','Aladdine17',NULL,NULL,NULL,NULL,NULL,NULL),
 (3,NULL,'KHEMIRI31','Aladdine28',NULL,NULL,NULL,NULL,NULL,NULL);
UNLOCK TABLES;

2.do backup with

Code: Select all

MyDump1.BackupToFile('mybackup.sql');

3.try to restore :

Code: Select all

MyDump1.RestoreFromFile('mybackup.sql');

Then i get this error :

#HY000The value specified for generated column 'v_PatientName' in table 'patients' is not allowed.

thank you again for your kind help!
Aladdine.

[ViktorV]

1. Since you are using the reserved word mysql, to solve the issue, please, set the TMyQuery.Options.QuoteNames property to True.
2. At the moment, MyDAC does not support this functionality. If you want us to implement the feature, please post this suggestion at our user voice forum: https://devart.uservoice.com/forums/104 ... ual-stored If the suggestion gets a lot of votes, we will consider the possibility to implement it.
As a workaround, you can use the TMyDump.BackupQuery method for backuping tables, which contain the Virtual fields.
In the query, you should list all the fields except the Virtual fields. You can get more details about this method in MyDAC help: http://www.devart.com/mydac/docs/?devar ... tring).htm

[Khemiri Aladdine]

Hi Viktor,
it will be nice if this functionnality will be supported, hope it get a lot of votes...
you may consider this issue as closed.
great thanks.

[ViktorV]

Thank you for the interest in our products.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.