- This topic has 28 replies, 9 voices, and was last updated 7 years ago by 0303062650.
-
a bit OT: any Database / SQL people around?
-
0303062650Free Member
Hi,
I’m trying to assemble a fairly basic ‘star schema’ style data warehouse for some coursework. I obviously have some issues somewhere with it and my fellow students don’t seem to be able to identify why I’m getting this message when I try and import it into my mysql server. “ERROR 1064 (42000) at line 158: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)
REFERENCESnhs1
.Patient
ON DELETE NO ACTION
ON UPDATE NO ACTIO’ at line 13″I wondered if anyone would be kind enough to cast their eyes over my Workbench file and offer their opinion?
total longshot, I know.
Thanks!
orangespydermanFull MemberON DELETE NO ACTION
ON UPDATE NO ACTIOIs it not just a typo?
whitestoneFree Membercorresponds to your MySQL server version for the right syntax to use near ‘ <=
MSQL uses ‘near’ to signify that what follows is the first thing it can’t understand. From what the OP has posted this would appear to be the single quote character.
0303062650Free MemberERROR 1064 (42000) at line 157: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)
REFERENCESnhs1
.Treatment
()
ON DELETE NO ACTION
ON UPDATE NO ‘ at line 40not sure why the weird paste error, I’ve just re-ran the export after making some minor updates, I’ll go back again and check as obviously it’s now saying ‘Treatment’ not ‘Patient’ (in backtick, but I’m using a Mac with a pc keyboard and haven’t quite figured that out yet…sigh)
orangespydermanFull MemberI’d avoid copy/pasting text like that. Export to a file, move the file across (scp) then import from the file.
0303062650Free MemberYeah, have tried the export to a .sql file but still nada.
It’s a ‘backtick’ and is legal for use in mysql, I should hope so as I’m using the latest versions of mysql workbench + mysql server 5.7
I have the fact table with each column entry except the first (which is a primary key) linked/associated by way of a foreign key to 8 other tables (dimension tables).
totally stumped ;(
orangespydermanFull MemberI’m not familiar with workbench, but can’t you just do this from the command line?
mysqldump > export_file.sql
It’s a ‘backtick’ and is legal for use in mysql
Possibly, but I think the problem is why the statement appears to be being truncated (NO ACTIO in the first case, just NO in the second).
toby1Full MemberREFERENCES nhs1.Treatment ()
ON DELETE NO ACTION
ON UPDATE NO ‘ at line 40Which of these lines is 157?
What do you mean a backtick, I can’t see that in the script. I feel like we are working blind.
orangespydermanFull MemberWhat do you mean a backtick, I can’t see that in the script.
I just assumed he was referring to the single quote (‘) – which isn’t a backtick (`)
I feel like we are working blind.
Agreed. OP, export it using mysqldump and use that file (not some copy/paste) to import into your mysql. Then you can easily look at the statements that are actually being produced by the export and not risk strange copy/paste truncation (or even conversion) issues. Just my €0.02
0303062650Free Memberok, here’s the resultant .sql file
https://gist.github.com/anonymous/3bcc1da249c30bd10e662577fac7c174and many thanks so far!!
orangespydermanFull MemberThat looks fine. Are you trying to import it doing
mysql -u username -p database_name < star_schema
?
0303062650Free Memberthanks @orangespyderman. I have done it via command line and the gui, still no joy ;(
Just tried it again and still the same error message.
Here’s the workbench file: https://drive.google.com/file/d/0BxFCRQ9DShDkV1hGMkdKalJXWkE/view?usp=sharing just incase there’s something visible there.
The dimension tables are created, the fact table is not. So I must have something weird set up – I just can’t find it ;(
orangespydermanFull Memberand still the same error message.
Which one – I have seen several (first one was on Patient, second was on Treatment). Does it fail randomly at different places? If so then I still think it’s the way you copy/paste or the way you transfer the file. Your errors don’t look like the file you shared. The first one complains about line 13 but line 13 in the file you shared looks nothing like the statement that’s failing. The second complains about line 40, and again that doesn’t look anything like that in the file you shared.
Have you imported that file (the exact same one) from the command line and what error did it generate, and what was actually in the file on your system at the line mysql complained about?
0303062650Free MemberHey. Patient was pre-amendment to my workbench file, so it is now Treatment. No, the error remains consistently the same regardless of whether I scp the file over, copy/paste or using mysql workbench’s ‘forward engineer’ (and subsequent ‘import’ function).
Yeah, I don’t know why line 13 is mentioned either.
What’s at line 157? a comment. So if I guess at removing the comments from the file and referencing the error:
ERROR 1064 (42000) at line 157: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)
REFERENCESnhs1
.Treatment
()
ON DELETE NO ACTION
ON UPDATE NO ‘ at line 40It’s this block of code:
CREATE TABLE IF NOT EXISTS
nhs1
.Treatment_Fact
(
TreatmentFact_ID
INT NOT NULL,
Patient_ID
INT NULL,
Unit_ID
INT NULL,
Staff_ID
INT NULL,
Operation_ID
INT NULL,
Drug_ID
INT NULL,
ContCare_ID
INT NULL,
DrugStock_ID
INT NULL,
Treat_ID
INT NULL,
Treatment_Date
DATETIME NULL,
PRIMARY KEY (TreatmentFact_ID
),
INDEXOperation_ID_idx
(Operation_ID
ASC),
INDEXDrug_ID_idx
(Drug_ID
ASC),
INDEXContCare_ID_idx
(ContCare_ID
ASC),
INDEXStaff_ID_idx
(Staff_ID
ASC),
INDEXTreat_ID_idx
(Treat_ID
ASC),
INDEXUnit_ID_idx
(Unit_ID
ASC),
CONSTRAINTPatient_ID
FOREIGN KEY (Patient_ID
)
REFERENCESnhs1
.Patient
(Patient_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTTreat_ID
FOREIGN KEY (Treat_ID
)
REFERENCESnhs1
.Treatment
(Treat_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTUnit_ID
FOREIGN KEY (Unit_ID
)
REFERENCESnhs1
.Treatment_Unit
(Unit_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTStaff_ID
FOREIGN KEY (Staff_ID
)
REFERENCESnhs1
.Staff
(Staff_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTTreat_WaitTime
FOREIGN KEY ()
REFERENCESnhs1
.Treatment
()
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTOperation_ID
FOREIGN KEY (Operation_ID
)
REFERENCESnhs1
.Operation
(Operation_Code
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTDrug_ID
FOREIGN KEY (Drug_ID
)
REFERENCESnhs1
.Treatment_Drug
(Drug_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTContCare_ID
FOREIGN KEY (ContCare_ID
)
REFERENCESnhs1
.Continuing_Care
(ContCare_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTDrug_ID
FOREIGN KEY (Drug_ID
)
REFERENCESnhs1
.Treatment_Drug
(Drug_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;which is, according to my text editor, line 41.
ta.
0303062650Free MemberOK, update. I removed a column from the fact table which was titled “Treatment_WaitTime” and I now get a different error (at command line)
ERROR 1215 (HY000) at line 157: Cannot add foreign key constraint
;(
orangespydermanFull MemberOK, update. I removed a column from the fact table which was titled “Treatment_WaitTime” and I now get a different error (at command line)
ERROR 1215 (HY000) at line 157: Cannot add foreign key constraint
Right. I really don’t get what you’re doing, nor why. I don’t know anything about the workbench so, as a disclaimer, if this is tool specific I will not be able to help. However, you say you removed a “column” from the “fact” table that was called “Treatment_WaitTime”
I can’t find a “column” called that in the SQL you shared. I can find a field called Treat_WaitTime that was in Treatment, and I can find a constraint in the definition of Treatment_Fact called Treat_WaitTime. It does give the impression, from where I’m sitting, that you’re not very methodological. Apologies if that offends, but it would make me look more for user error than a “bug”…
If you remove random fields from the schema design I would expect errors like the one you just posted (foreign keys etc; why did you decide Treatment_WaitTime (whatever that is) was an issue?).
Can I ask (again) if you have just tried using mysqldump on the source DB and have you cleared the target database instance between import attempts?
radtothepowerofsikFree MemberCONSTRAINT Treat_WaitTime
FOREIGN KEY ()
REFERENCES nhs1.Treatment ()This constraint doesn’t have a column or a foreign key defined like the rest do. Take it out, it’s not doing anything and it’s probably causing your error
radtothepowerofsikFree MemberI guess it was referencing the Treatment_WaitTime column? Although that was on the FACT table not the TREATMENT one?
petecFree MemberA foreign key constraint is looking in another table to verify the data exists of course. If it doesn’t, it won’t go in. Does that wait time exist in a waittime table elsewhere?
radtothepowerofsikFree MemberWhen he gets to the point of doing an insert… we aren’t there yet!
‘Wait time’, unless it’s the ID from a list of pre-determined wait times, doesn’t sound like it should be a foreign key. Sounds like just a bit of data on this table.
deadkennyFree MemberHave I wondered into Stack Overflow? 😀
Actually probably not, the replies are far too helpful 😉
radtothepowerofsikFree Member😀
Aaaand you’ve got DrugStock_ID which looks like a foreign key but no actual constraint to go with it.
I’m assuming you’ve made a choice with which columns to index?
A bit of space and ordering things properly helps
(by the way I don’t use MySQL so providing no guarantees!)
CREATE TABLE IF NOT EXISTS nhs1.Treatment_Fact (
TreatmentFact_ID INT NOT NULL,
Patient_ID INT NULL,
Unit_ID INT NULL,
Staff_ID INT NULL,
Operation_ID INT NULL,
Drug_ID INT NULL,
ContCare_ID INT NULL,
DrugStock_ID INT NULL,
Treat_ID INT NULL,
Treatment_Date DATETIME NULL,
———————————————-
PRIMARY KEY (TreatmentFact_ID),
INDEX Unit_ID_idx (Unit_ID ASC),
INDEX Staff_ID_idx (Staff_ID ASC),
INDEX Operation_ID_idx (Operation_ID ASC),
INDEX Drug_ID_idx (Drug_ID ASC),
INDEX ContCare_ID_idx (ContCare_ID ASC),
INDEX Treat_ID_idx (Treat_ID ASC),
———————————————-
CONSTRAINT Patient_ID
FOREIGN KEY (Patient_ID)
REFERENCES nhs1.Patient (Patient_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
———————————————-
CONSTRAINT Unit_ID
FOREIGN KEY (Unit_ID)
REFERENCES nhs1.Treatment_Unit (Unit_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
———————————————-
CONSTRAINT Staff_ID
FOREIGN KEY (Staff_ID)
REFERENCES nhs1.Staff (Staff_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
———————————————-
CONSTRAINT Operation_ID
FOREIGN KEY (Operation_ID)
REFERENCES nhs1.Operation (Operation_Code)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
———————————————-
CONSTRAINT Drug_ID
FOREIGN KEY (Drug_ID)
REFERENCES nhs1.Treatment_Drug (Drug_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
———————————————-
CONSTRAINT ContCare_ID
FOREIGN KEY (ContCare_ID)
REFERENCES nhs1.Continuing_Care (ContCare_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
———————————————-
CONSTRAINT Treat_ID
FOREIGN KEY (Treat_ID)
REFERENCES nhs1.Treatment (Treat_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;GlennQuagmireFree MemberI’ve run this entire script without error so this should work!
— MySQL Script generated by MySQL Workbench
— Tue Dec 13 14:41:45 2016
— Model: New Model Version: 1.0
— MySQL Workbench Forward EngineeringSET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;— —————————————————–
— Schema nhs1
— —————————————————–— —————————————————–
— Schema nhs1
— —————————————————–
CREATE SCHEMA IF NOT EXISTSnhs1
DEFAULT CHARACTER SET utf8 ;
USEnhs1
;— —————————————————–
— Tablenhs1
.Patient
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Patient
(
Patient_ID
INT NOT NULL,
Patient_DoB
DATE NULL,
Patient_Country
VARCHAR(70) NULL,
Patient_City
VARCHAR(70) NULL,
Patient_Occupation
VARCHAR(70) NULL,
Patient_Name
VARCHAR(255) NULL,
Patient_TelNo
VARCHAR(10) NULL,
Patient_Address
VARCHAR(255) NULL,
Patient_Email
VARCHAR(255) NULL,
Patient_County
VARCHAR(70) NULL,
Treat_ID
VARCHAR(70) NULL,
Op_Code
INT NULL,
Patient_NextOfKin
VARCHAR(255) NULL,
Patient_QtyOfMissedAppts
INT NULL,
ContdCare_ID
INT NULL,
Patient_AtRisk
VARCHAR(45) NULL,
PRIMARY KEY (Patient_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Treatment_Unit
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Treatment_Unit
(
Unit_ID
INT NOT NULL,
Unit_Type
VARCHAR(45) NULL,
Unit_County
VARCHAR(70) NULL,
Unit_City
VARCHAR(70) NULL,
Treatment_Cost
VARCHAR(45) NULL,
PRIMARY KEY (Unit_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Staff
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Staff
(
Staff_ID
INT NOT NULL,
Staff_Level
VARCHAR(20) NULL,
Staff_Specialisation
VARCHAR(70) NULL,
Staff_Name
VARCHAR(255) NULL,
Staff_WeeklyHrs
INT NULL,
Staff_Salary
INT NULL,
Staff_OvertimeRate
INT NULL,
Staff_OnCall
TINYINT(1) NULL,
Unit_City
VARCHAR(70) NULL,
PRIMARY KEY (Staff_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Drug_Stock
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Drug_Stock
(
DrugStock_ID
INT NOT NULL,
DrugStock_Cost
VARCHAR(45) NULL,
DrugStock_Type
VARCHAR(45) NULL,
DrugStock_Dosage
VARCHAR(45) NULL,
DrugStock_Manufacturer
VARCHAR(45) NULL,
DrugStock_Restrictions
VARCHAR(45) NULL,
DrugStock_Trial
VARCHAR(45) NULL,
DrugStock_ReceivedDate
DATE NULL,
DrugStock_IssueDate
VARCHAR(45) NULL,
DrugStock_ExpiryDate
VARCHAR(45) NULL,
PRIMARY KEY (DrugStock_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Treatment
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Treatment
(
Treat_ID
INT NOT NULL,
Treat_Type
VARCHAR(45) NULL,
Patient_ID
VARCHAR(45) NULL,
Diagnosis
VARCHAR(45) NULL,
Unit_Code
VARCHAR(45) NULL,
Staff_ID
VARCHAR(45) NULL,
Treat_WaitTime
DATETIME NULL,
PRIMARY KEY (Treat_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Operation
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Operation
(
Operation_Code
INT NOT NULL,
Operation_Type
VARCHAR(45) NULL,
Operation_Cost
VARCHAR(45) NULL,
Operation_Time
VARCHAR(45) NULL,
Operation_Equipment
VARCHAR(45) NULL,
Operation_Budget
VARCHAR(45) NULL,
Patient_ID
INT NULL,
Staff_ID
INT NULL,
Unit_Code
INT NULL,
PRIMARY KEY (Operation_Code
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Treatment_Drug
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Treatment_Drug
(
Drug_ID
INT NOT NULL,
TD_Date
VARCHAR(45) NULL,
TD_Duration
VARCHAR(45) NULL,
TD_Cost
VARCHAR(45) NULL,
Patient_ID
VARCHAR(45) NULL,
Unit_Code
VARCHAR(45) NULL,
PRIMARY KEY (Drug_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Continuing_Care
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Continuing_Care
(
ContCare_ID
INT NOT NULL,
Patient_ID
VARCHAR(45) NULL,
Unit_Code
VARCHAR(45) NULL,
Staff_ID
VARCHAR(45) NULL,
Treat_ID
VARCHAR(45) NULL,
ContCare_ApptDate
VARCHAR(45) NULL,
ContCare_ApptLength
VARCHAR(45) NULL,
ContCare_TotalNumberOfAppts
VARCHAR(45) NULL,
PRIMARY KEY (ContCare_ID
))
ENGINE = InnoDB;— —————————————————–
— Tablenhs1
.Treatment_Fact
— —————————————————–
CREATE TABLE IF NOT EXISTSnhs1
.Treatment_Fact
(
TreatmentFact_ID
INT NOT NULL,
Patient_ID
INT NULL,
Unit_ID
INT NULL,
Staff_ID
INT NULL,
Operation_ID
INT NULL,
Drug_ID
INT NULL,
ContCare_ID
INT NULL,
DrugStock_ID
INT NULL,
Treat_ID
INT NULL,
Treatment_Date
DATETIME NULL,
PRIMARY KEY (TreatmentFact_ID
),
INDEXOperation_ID_idx
(Operation_ID
ASC),
INDEXDrug_ID_idx
(Drug_ID
ASC),
INDEXContCare_ID_idx
(ContCare_ID
ASC),
INDEXStaff_ID_idx
(Staff_ID
ASC),
INDEXTreat_ID_idx
(Treat_ID
ASC),
INDEXUnit_ID_idx
(Unit_ID
ASC),
CONSTRAINTPatient_ID
FOREIGN KEY (Patient_ID
)
REFERENCESnhs1
.Patient
(Patient_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTTreat_ID
FOREIGN KEY (Treat_ID
)
REFERENCESnhs1
.Treatment
(Treat_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTUnit_ID
FOREIGN KEY (Unit_ID
)
REFERENCESnhs1
.Treatment_Unit
(Unit_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTStaff_ID
FOREIGN KEY (Staff_ID
)
REFERENCESnhs1
.Staff
(Staff_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTTreat_WaitTime
FOREIGN KEY (Treat_ID
)
REFERENCESnhs1
.Treatment
(Treat_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTOperation_ID
FOREIGN KEY (Operation_ID
)
REFERENCESnhs1
.Operation
(Operation_Code
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTContCare_ID
FOREIGN KEY (ContCare_ID
)
REFERENCESnhs1
.Continuing_Care
(ContCare_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINTDrug_ID
FOREIGN KEY (Drug_ID
)
REFERENCESnhs1
.Treatment_Drug
(Drug_ID
)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;0303062650Free Membermuchas gracias 😉 all sorted now (well, late last night)
I think it was as simple as the foreign keys not being correctly set – it’s been a while since I have done any sql stuff from scratch and it was definitely a case of ‘cant see the wood for the trees’.
Now building sample / dummy data, it’s a bit tedious!
thanks all!
bailsFull MemberIf you need anything to base the dummy data on look at the DH’s reference cost datasets, tariff price lists and pbr excluded drug lists. Also, opcs codes for treatments and icd10 codes for diagnosiseseses.
(Apologies if you already know about all of those, they just might save you hours of making stuff up!)
0303062650Free Memberhmm, bails … I’m using something called Mockaroo to generate the data which handily has ICD10 or ICD9 codes.
I did not know about the OPCS codes and that’s enormously helpful – exactly what I need!!! Thank You!
The topic ‘a bit OT: any Database / SQL people around?’ is closed to new replies.