MariaDB LOAD DATA INFO can't import single column file to single column table?
I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.
The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.
Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?
Here's what I am trying to do:
LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;
And here's the table:
CREATE TABLE `common_passwords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;
It seems like we should be able to do this rather easily in SQL...
mysql mariadb
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.
The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.
Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?
Here's what I am trying to do:
LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;
And here's the table:
CREATE TABLE `common_passwords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;
It seems like we should be able to do this rather easily in SQL...
mysql mariadb
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Remove settingidfrom the query - its value will be set automatically. And read user manual about LOAD DATA INFILE carefully - you do not need in SET section at all.
– Akina
Sep 24 '18 at 13:49
Without the fields orSET, I get this error:Duplicate entry '123456' for key 'PRIMARY'(123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.
– DrDamnit
Sep 24 '18 at 13:53
1
As I have said read user manual about LOAD DATA INFILE carefully. The file you load contains data forpasswordfield only - you MUST show it in a query.
– Akina
Sep 24 '18 at 13:55
Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements forINTO OUTFILE(Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).
– DrDamnit
Sep 24 '18 at 14:36
1
note:AUTO_INCREMENTmeans its automatic. The mysql manual has more examples and its the same syntax as mariadb.
– danblack
Sep 25 '18 at 3:56
add a comment |
I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.
The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.
Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?
Here's what I am trying to do:
LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;
And here's the table:
CREATE TABLE `common_passwords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;
It seems like we should be able to do this rather easily in SQL...
mysql mariadb
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.
The file is a single column TXT file. Trying to LOAD DATA INFILE that into a table with a single column gives me a primary key error. So, I thought, I'll set an id column with autoincrement, and then pass null for each row to trigger the keys to be indexed. That doesn't work either (or I have a syntax problem) because that gives me 10,000 rows with numbers and null password fields.
Can this be done, or should I just write a python script to add an index to each text line, and then import it that way?
Here's what I am trying to do:
LOAD DATA INFILE '/tmp/passwords.txt' INTO TABLE `common_passwords` (password,id) SET id=@id+1,password=@password;
And here's the table:
CREATE TABLE `common_passwords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;
It seems like we should be able to do this rather easily in SQL...
mysql mariadb
mysql mariadb
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
asked Sep 24 '18 at 13:34
DrDamnitDrDamnit
15029
15029
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Remove settingidfrom the query - its value will be set automatically. And read user manual about LOAD DATA INFILE carefully - you do not need in SET section at all.
– Akina
Sep 24 '18 at 13:49
Without the fields orSET, I get this error:Duplicate entry '123456' for key 'PRIMARY'(123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.
– DrDamnit
Sep 24 '18 at 13:53
1
As I have said read user manual about LOAD DATA INFILE carefully. The file you load contains data forpasswordfield only - you MUST show it in a query.
– Akina
Sep 24 '18 at 13:55
Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements forINTO OUTFILE(Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).
– DrDamnit
Sep 24 '18 at 14:36
1
note:AUTO_INCREMENTmeans its automatic. The mysql manual has more examples and its the same syntax as mariadb.
– danblack
Sep 25 '18 at 3:56
add a comment |
1
Remove settingidfrom the query - its value will be set automatically. And read user manual about LOAD DATA INFILE carefully - you do not need in SET section at all.
– Akina
Sep 24 '18 at 13:49
Without the fields orSET, I get this error:Duplicate entry '123456' for key 'PRIMARY'(123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.
– DrDamnit
Sep 24 '18 at 13:53
1
As I have said read user manual about LOAD DATA INFILE carefully. The file you load contains data forpasswordfield only - you MUST show it in a query.
– Akina
Sep 24 '18 at 13:55
Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements forINTO OUTFILE(Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).
– DrDamnit
Sep 24 '18 at 14:36
1
note:AUTO_INCREMENTmeans its automatic. The mysql manual has more examples and its the same syntax as mariadb.
– danblack
Sep 25 '18 at 3:56
1
1
Remove setting
id from the query - its value will be set automatically. And read user manual about LOAD DATA INFILE carefully - you do not need in SET section at all.– Akina
Sep 24 '18 at 13:49
Remove setting
id from the query - its value will be set automatically. And read user manual about LOAD DATA INFILE carefully - you do not need in SET section at all.– Akina
Sep 24 '18 at 13:49
Without the fields or
SET, I get this error: Duplicate entry '123456' for key 'PRIMARY' (123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.– DrDamnit
Sep 24 '18 at 13:53
Without the fields or
SET, I get this error: Duplicate entry '123456' for key 'PRIMARY' (123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.– DrDamnit
Sep 24 '18 at 13:53
1
1
As I have said read user manual about LOAD DATA INFILE carefully. The file you load contains data for
password field only - you MUST show it in a query.– Akina
Sep 24 '18 at 13:55
As I have said read user manual about LOAD DATA INFILE carefully. The file you load contains data for
password field only - you MUST show it in a query.– Akina
Sep 24 '18 at 13:55
Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements for
INTO OUTFILE (Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).– DrDamnit
Sep 24 '18 at 14:36
Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements for
INTO OUTFILE (Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).– DrDamnit
Sep 24 '18 at 14:36
1
1
note:
AUTO_INCREMENT means its automatic. The mysql manual has more examples and its the same syntax as mariadb.– danblack
Sep 25 '18 at 3:56
note:
AUTO_INCREMENT means its automatic. The mysql manual has more examples and its the same syntax as mariadb.– danblack
Sep 25 '18 at 3:56
add a comment |
1 Answer
1
active
oldest
votes
To quote the manual:
If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY 'n' STARTING BY ''
and
Interpret characters preceded by the escape character as escape sequences. For example, t, n, and signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.
and
If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.
and
... empty ('')
-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html
That is, if you have tabs or backslashes, the data will be mangled as it is read. This may explain the problem you are having.
I guess this may be the fix:
FIELDS ESCAPED BY ''
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218419%2fmariadb-load-data-info-cant-import-single-column-file-to-single-column-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To quote the manual:
If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY 'n' STARTING BY ''
and
Interpret characters preceded by the escape character as escape sequences. For example, t, n, and signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.
and
If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.
and
... empty ('')
-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html
That is, if you have tabs or backslashes, the data will be mangled as it is read. This may explain the problem you are having.
I guess this may be the fix:
FIELDS ESCAPED BY ''
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
add a comment |
To quote the manual:
If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY 'n' STARTING BY ''
and
Interpret characters preceded by the escape character as escape sequences. For example, t, n, and signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.
and
If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.
and
... empty ('')
-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html
That is, if you have tabs or backslashes, the data will be mangled as it is read. This may explain the problem you are having.
I guess this may be the fix:
FIELDS ESCAPED BY ''
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
add a comment |
To quote the manual:
If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY 'n' STARTING BY ''
and
Interpret characters preceded by the escape character as escape sequences. For example, t, n, and signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.
and
If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.
and
... empty ('')
-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html
That is, if you have tabs or backslashes, the data will be mangled as it is read. This may explain the problem you are having.
I guess this may be the fix:
FIELDS ESCAPED BY ''
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
To quote the manual:
If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'
LINES TERMINATED BY 'n' STARTING BY ''
and
Interpret characters preceded by the escape character as escape sequences. For example, t, n, and signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.
and
If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.
and
... empty ('')
-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html
That is, if you have tabs or backslashes, the data will be mangled as it is read. This may explain the problem you are having.
I guess this may be the fix:
FIELDS ESCAPED BY ''
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
answered Oct 10 '18 at 16:27
Rick JamesRick James
43.6k22259
43.6k22259
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218419%2fmariadb-load-data-info-cant-import-single-column-file-to-single-column-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Remove setting
idfrom the query - its value will be set automatically. And read user manual about LOAD DATA INFILE carefully - you do not need in SET section at all.– Akina
Sep 24 '18 at 13:49
Without the fields or
SET, I get this error:Duplicate entry '123456' for key 'PRIMARY'(123456 is one of the passwords on the list). This is what started thsi problem to start with. In a perfect world, I'd get rid of the PRIMARY and indexes to get this imported, then go back and add an id column and re-index.– DrDamnit
Sep 24 '18 at 13:53
1
As I have said read user manual about LOAD DATA INFILE carefully. The file you load contains data for
passwordfield only - you MUST show it in a query.– Akina
Sep 24 '18 at 13:55
Not trying to be dense here, but I've read this over and over. What am I missing? mariadb.com/kb/en/library/load-data-infile I don't see anything about including it in a query except for using SELECT statements for
INTO OUTFILE(Incidentally, I wrote a python script to add the ids and got it imported. But, this is still something I want to know / understand).– DrDamnit
Sep 24 '18 at 14:36
1
note:
AUTO_INCREMENTmeans its automatic. The mysql manual has more examples and its the same syntax as mariadb.– danblack
Sep 25 '18 at 3:56