CODEWITHSUNDEEP
mysqlmariadb
Ryan Vetter
Ryan Vetter

Reputation: 41

Mariadb syntax error 1064 (42000)

So I'm getting an error when trying to run this script in MariaDB that reads as follows: "ERROR 1064 (42000):You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 

CREATE TABLE customers (
  customer_id int NOT NULL,
  customer_f

at line 1

The weird thing is that MariaDB seems to be reading the first line of the command and then a little bit of the next line as 1 line. The whole script is below: if anyone could help I would greatly appreciate it.

DROP TABLE customers;
DROP TABLE orders;
DROP TABLE products;
DROP TABLE orderitem;
​
CREATE TABLE customers (
customer_id INT NOT NULL AUTO_INCREMENT,
customer_firstname VARCHAR(20) NOT NULL,
customer_lastname VARCHAR(40) NOT NULL,
customer_phone CHAR(10) NOT NULL,
customer_email VARCHAR(60) NOT NULL,
customer_address VARCHAR(40) NOT NULL,
customer_city VARCHAR(40) NOT NULL,
customer_state CHAR(2) NOT NULL,
customer_zip VARCHAR(10) NOT NULL,
customer_aptnum VARCHAR(5) NOT NULL,
customer_pass CHAR(40) NOT NULL,
customer_type VARCHAR(10) NOT NULL,
PRIMARY KEY (customer_id),
INDEX customer_fullname (customer_firstname, customer_lastname),
UNIQUE (customer_email)
); 
​
CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT,
order_datetime DATETIME NOT NULL,
order_trackingnumber VARCHAR(20) NOT NULL,
order_shipdate DATETIME NOT NULL,
order_shipmethod VARCHAR(10) NOT NULL,
order_shipcarrier VARCHAR(10) NOT NULL,
order_totalprice DECIMAL,
customer_id  INT NOT NULL,
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
UNIQUE (order_trackingnumber)
); 
​
​
CREATE TABLE products (
product_id VARCHAR(30) NOT NULL AUTO_INCREMENT,
product_beginningstockdate DATETIME NOT NULL,
product_endstockdate DATETIME,
product_category VARCHAR(15) NOT NULL,
product_name VARCHAR(60) NOT NULL,
product_availablequantity SMALLINT NOT NULL,
product_totalquantity SMALLINT NOT NULL,
product_price  DECIMAL NOT NULL,
product_taxable DECIMAL NOT NULL,
product_itemstatus VARCHAR(15) NOT NULL,
product_discountpercent DECIMAL,
product_soldinstore char(3),
product_soldonwebsite char(3),
PRIMARY KEY (product_id),
UNIQUE (product_name)
); 
​
/*INSERT INTO products (product_description, product_beginningstockdate, product_endstockdate, product_category, product_name, product_availablequantity, product_totalquantity, product_price, product_taxable, product_itemstatus, product_discountpercent, product_soldinstore, product_soldonwebsite) 
VALUES 
(...),
(...),
........ */
​
CREATE TABLE orderitem (
orderitem_id INT NOT NULL AUTO_INCREMENT,
order_id INT NOT NULL,
product_id VARCHAR(30) NOT NULL,
orderitem_priceperunit DECIMAL NOT NULL,
orderitem_quantityordered TINYINT NOT NULL,
PRIMARY KEY (orderitem_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES orders(product_id)
);

Upvotes: 4

Views: 28459

Answers (3)

HYLORICH
HYLORICH

Reputation: 1

select * FROM rides AS r INNER JOIN cities AS c on r.starting_city_id = c.city_id ; WHERE city_name in ('Toulouse' , 'Lyon' , 'Bordeaux') ;

Upvotes: 0

Michael Lowden
Michael Lowden

Reputation: 103

I had a similar frustration and discovered I needed to change my delimiter around my procedure. Perhaps it'll work here too.

DELIMITER //
CREATE PROCEDURE
....
BEGIN
END //
DELIMITER ;

Upvotes: 2

C3roe
C3roe

Reputation: 96455

Copy&pasting your code into NotePad++ and then viewing it in the hex editor shows that you have 80 8b 0a in each of those empty lines between the statements.

That byte sequence is the UTF-8 encoded form of the zero-width space character.

See to it that you remove those – then it should work.

(If you’re using NotePad++ and the hex editor plugin, then in hex view you can simply replace e2 80 8b with an empty string. Otherwise, in any other text editor, going to the end of the previous line, selecting everything from there over the empty line until the beginning of the next line, and then replacing the selection by pressing enter should also work.)

Upvotes: 3

Related Questions