1

I have a text file with millions of rows.

Example: sorage.txt

foo
bar
baz

File was loaded as follows:

LOAD DATA INFILE '/var/lib/mysql-files/sorage.txt'
INTO TABLE `storage` (@word)
SET col1 = @word, col2 = CONCAT('prefix_', @word)

Afterwards I doesn't see any rows in the storage table. But table statistics shows that rows exist.

I'm using MySQL 5.7 and HeidiSQL as GUI.

Pic 1

table statistics

Pic 2

select rows from table

Why there are no rows?

Update 1

Using terminal - 0 rows.

query table from the console

Jekis
  • 4,274
  • 2
  • 36
  • 42
  • This is strange. If I use your query in command-line mysql, it works fine. It reports the correct number of rows inserted and 'SELECT...' shows them correctly. If I do it in PHPMyAdmin, I get 'MySQL returned an empty result set' but it does actually insert them. I'd suspect heidisql. – Nick Jul 30 '18 at 07:02
  • @Nick My first test query was using a text file with a couple of rows and it worked (rows were present in the Heidi). – Jekis Jul 30 '18 at 07:05
  • Just tried it again with 1.2 million random words and both methods work as with the small test file. Using MySQL 5.7.22. – Nick Jul 30 '18 at 07:23
  • @Mate There was one query only. `COMMIT;` doesn't help. – Jekis Jul 30 '18 at 07:31
  • @Nick Hm... I did it two times and without any success. – Jekis Jul 30 '18 at 07:32
  • If you try select * from stg LIMIT 10; ? – Mate Jul 30 '18 at 07:45
  • Most likely, you are inserting into the wrong server, the wrong database or the wrong table (e.g. `storage`/`_storage` asin your query/pictures). Try `SELECT @@hostname, database();` and compare the values. Another option is that you are not committing; you already check, but recheck and do a commit (just in case) and do the `select count(*)` directly after the `load data`, so it is certainly in the same transaction (and the same server/database), in case there is one. Also: does the import take significant time? (Just to make sure you are not importing an empty file). – Solarflare Jul 30 '18 at 09:49
  • @Mate, I got empty result. – Jekis Jul 30 '18 at 13:53
  • @Solarflare, It is my localhost, db and table are correct. Import takes about 2 hours. I will try to debug the *commit* version one more time. – Jekis Jul 30 '18 at 13:55

0 Answers0