Member Avatar for lewashby

File

drop database if exists contacts

create database contacts;

use contacts;

create table names
(
    ID mediumint not null auto_increment primary key,
    firstName varchar(20),
    lastName varchar(20)
);

create table addresses
(
    ID mediumint not null auto_increment primary key,
    nameID mediumint not null,
    addr1 varchar(30),
    addr2 varchar(30),
    city varchar(30),
    state varchar(10),
    postal_code varchar(10)
);

insert into names (firstName, lastName)
values ('John', 'Doe'), ('Mary', 'Smith'), ('Bill', 'Jones'), ('Dave', 'White'), ('Jane', 'Tate');

insert into addresses (nameID, addr1, addr2, city, state, postal_code)
select ID, '123 4th Street', '', 'Anytown', 'OH', '12345' from names where firstName='John' and lastName='Doe';

insert into addresses (nameID, addr1, addr2, city, state, postal_code)
select ID, '16 7th Avenue', 'Apt. 9', 'Big City', 'AZ', '23456' from names where firstName='Mary' and lastName='Smith';

insert into addresses (nameID, addr1, addr2, city, state, postal_code)
select ID, '5432 Rose Ct.', 'Number 3', 'Townburg', 'VT', '34567' from names where firstName='Bill' and lastName='Jones';

insert into addresses (nameID, addr1, addr2, city, state, postal_code)
select ID, '456 7th Street', '', 'Hilltop', 'FL', '98756' from names where firstName='Dave' and lastName='White';

insert into addresses (nameID, addr1, addr2, city, state, postal_code)
select ID, '98 Long Road', '', 'Noplaceville', 'AK', '74839' from names where firstName='Jane' and lastName='Tate';

I used this line, source /home/user/db.sql

and I got these errors.

ERROR 1064 (42000) at line 1 in file: '/home/user/db.sql': 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 database contacts' at line 3
ERROR 1049 (42000) at line 5 in file: '/home/user/db.sql': Unknown database 'contacts'
ERROR 1046 (3D000) at line 7 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 14 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 25 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 28 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 31 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 34 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 37 in file: '/home/user/db.sql': No database selected
ERROR 1046 (3D000) at line 40 in file: '/home/user/db.sql': No database selected

Any ideas? Thanks.

  • 2 Contributors
  • 1 Reply
  • 586 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by cereal

Dani AI

Generated

Good catch by — the symptom you saw is exactly what happens when the client never sees a statement terminator. Missing the semicolon after the first statement makes the parser treat the following CREATE as part of the same statement, which triggers the initial syntax error and causes the subsequent CREATE, USE and INSERT lines not to execute (hence the “unknown database” / “no database selected” cascade).

Fix: terminate that first statement, save the file, and re-run the import. For example:

DROP DATABASE IF EXISTS contacts;

After saving, import the file either from inside the mysql client (at the mysql> prompt) with source /path/to/db.sql or from the shell with input redirection:

mysql -u youruser -p < /path/to/db.sql

If you still get errors, check for invisible characters or Windows file issues: a UTF‑8 BOM or CRLF line endings can confuse the client. Convert the file or strip a BOM with tools such as dos2unix db.sql or sed -i '1s/^\xEF\xBB\xBF//' db.sql, then retry. Also confirm the account you use has privileges to create databases/tables.

If problems remain, paste the first 10 lines of the SQL file (showing any invisible characters with xxd or hexdump -C) and the exact error and line number — that will make it easy to pinpoint any remaining cause.

Member Avatar for cereal

Hi,

in the first line (drop ...) is missing the separator (the semi-colon in this case), so all the following falls into a syntax error.

Edited by cereal
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.