asked on
How to batch load entries in MariaDB database?
I have a MariaDB database for our Open Journal Server. It has a table called "notification_subscription_settings" that contains one line per each type of notification that a user selected.
On the screenshot below, a user put a checkmark in "Enable these types of notification" under "New announcement" and under "An issue has been published", but no checkmark in "Do not send me an email for these types of notifications." This way, they will not only see pop-ups when looking at the journal online BUT ALSO will also automatically receive this notification in email. That is a default behavior of the site that I want to change, so users only get pop-ups, BUT NO emails, unless they go to their profile and select that option themselves.
I want to insert two entries per those users who have not touched their notification settings, placing a checkmark in "Do not send me an email for these types of notifications." It will add two rows (one with a "8" and one with a "268435477") to the database so it looks like this:
setting_id, setting_name, setting_value, user_id, context, setting_type
(469, 'blocked_emailed_notification', '8', 3747, 8, 'int'),
(470, 'blocked_emailed_notification', '268435477', 3747, 8, 'int');
The setting_id needs to start from 471 and get increased by 1 with each new row. The user_id needs to be taken from a list of a few hundred users that I have ready in a text file.
How can I do it in PHPMyAdmin?
DatabasesMySQL Server* MariaDB* SQL phpmyadmin
Last Comment
gr8gonzo
1) As @gr8gonzo, without your full schema no way to clearly answer.
And, almost surely setting_id is an AUTOINCREMENT field, so this column should never be touched.
Instead, you'll find 1x entry with the value you require, then do an UPDATE on all records, to jam the value into setting_type column.
2) That said, the schema looks odd.
The data you provided has many user_id == 4 rows.
Normally (good example is the WordPress meta data tables), you'd have...
user_id + setting_name (unique) + setting_value.
Your data, to me, seems illogical.
Best make sure you're clear about how all this works, before doing a mass database edit.
3) Using phpMyAdmin for this project would be shooting yourself in your foot.
phpMyAdmin has no audit trail, so do a bunch of edits + there's no way to easily track what you've done, to reverse out any problems.
My personally, I'd use the mariadb or mysql command line tools, to record each SQL statement I issued.
4) If I were tasked with this project, I'd backup the production site + restore it to a dev site, so for foo.com clone a copy to dev.foo.com to get all my SQL code working, before issuing the code against the production database.
5) Tip: If you must use some non command line tool, don't use phpMyAdmin, use https://www.adminer.org instead, which is far more secure than phpMyAdmin + is also trivial to update, compared to the dark hole of travesty that is phpMyAdmin updating.
And, almost surely setting_id is an AUTOINCREMENT field, so this column should never be touched.
Instead, you'll find 1x entry with the value you require, then do an UPDATE on all records, to jam the value into setting_type column.
2) That said, the schema looks odd.
The data you provided has many user_id == 4 rows.
Normally (good example is the WordPress meta data tables), you'd have...
user_id + setting_name (unique) + setting_value.
Your data, to me, seems illogical.
Best make sure you're clear about how all this works, before doing a mass database edit.
3) Using phpMyAdmin for this project would be shooting yourself in your foot.
phpMyAdmin has no audit trail, so do a bunch of edits + there's no way to easily track what you've done, to reverse out any problems.
My personally, I'd use the mariadb or mysql command line tools, to record each SQL statement I issued.
4) If I were tasked with this project, I'd backup the production site + restore it to a dev site, so for foo.com clone a copy to dev.foo.com to get all my SQL code working, before issuing the code against the production database.
5) Tip: If you must use some non command line tool, don't use phpMyAdmin, use https://www.adminer.org instead, which is far more secure than phpMyAdmin + is also trivial to update, compared to the dark hole of travesty that is phpMyAdmin updating.
ASKER
Here is the table structure:
The last few rows of data are:
For each selection that is not a default that is made by a user, the system creates an entry with the setting name and a hexadecimal setting value taken from the PKPNotification.inc.php file (I attached that file with binary to hexadecimal conversion). Some user_ids don't have any entry in the notification_subscription_settings table, some might have up to 12, depending on the combination they selected to edit the defaults. ("context" is just the number of the journal to which these users subscribe, so it is the same for everyone).
Below is what it looks like when I exported it for a couple users.
So, I need to start from 472 and it should autoincrement. Attached is the list of 1240 user_ids in a csv file. I would like to add 2480 entries to the notification_subscription_table that would look something like this, with user_id pulled from the user_id.csv file:
"setting_id";"blocked_emailed_notification";"8";"user_id";"8";"int"
"setting_id";"blocked_emailed_notification";"268435477";"user_id";"8";"int"
Additionally, I created a csv file (rows_to_batch_load.csv), attached, that has starts with 472 and creates 2 lines for everyone. Can I run the following command?
LOAD DATA INFILE 'rows_to_batch_load.csv' INTO TABLE notificaiton_subscription_settings FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';
I have created a snapshot of the VM, so I will be able to roll it back, if necessary. Nobody else has access to it, so no risk of creating duplicate setting_ids. And I have no problem running it in command line (I'm using XAMPP on Windows 2019 server). Just trying to figure out what command(s) would do it.
PKPNotification.inc.php-with corresponding decimals from notification_subscription_settings.php
notification_subscription_settings table export.txt
user_id.csv
rows_to_batch_load.csv
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | notification_subscription_settings | index | NULL | PRIMARY | 8 | NULL | 412 |
The last few rows of data are:
For each selection that is not a default that is made by a user, the system creates an entry with the setting name and a hexadecimal setting value taken from the PKPNotification.inc.php file (I attached that file with binary to hexadecimal conversion). Some user_ids don't have any entry in the notification_subscription_settings table, some might have up to 12, depending on the combination they selected to edit the defaults. ("context" is just the number of the journal to which these users subscribe, so it is the same for everyone).
Below is what it looks like when I exported it for a couple users.
"setting_id";"setting_name";"setting_value";"user_id";"context";"setting_type"
"446";"blocked_emailed_notification";"16777217";"3747";"8";"int"
"447";"blocked_emailed_notification";"16777247";"3747";"8";"int"
"448";"blocked_emailed_notification";"16777218";"3747";"8";"int"
"449";"blocked_emailed_notification";"16777219";"3747";"8";"int"
"450";"blocked_emailed_notification";"16777249";"3747";"8";"int"
"451";"blocked_emailed_notification";"16777250";"3747";"8";"int"
"452";"blocked_emailed_notification";"8";"3747";"8";"int"
"453";"blocked_emailed_notification";"16777258";"3747";"8";"int"
"454";"blocked_emailed_notification";"268435477";"3747";"8";"int"
"463";"blocked_notification";"16777217";"3800";"8";"int"
"464";"blocked_notification";"16777247";"3800";"8";"int"
"465";"blocked_notification";"16777218";"3800";"8";"int"
"466";"blocked_notification";"16777219";"3800";"8";"int"
"467";"blocked_notification";"16777249";"3800";"8";"int"
"468";"blocked_notification";"16777250";"3800";"8";"int"
"469";"blocked_notification";"8";"3800";"8";"int"
"470";"blocked_notification";"16777258";"3800";"8";"int"
"471";"blocked_notification";"268435477";"3800";"8";"int"
So, I need to start from 472 and it should autoincrement. Attached is the list of 1240 user_ids in a csv file. I would like to add 2480 entries to the notification_subscription_table that would look something like this, with user_id pulled from the user_id.csv file:
"setting_id";"blocked_emailed_notification";"8";"user_id";"8";"int"
"setting_id";"blocked_emailed_notification";"268435477";"user_id";"8";"int"
Additionally, I created a csv file (rows_to_batch_load.csv), attached, that has starts with 472 and creates 2 lines for everyone. Can I run the following command?
LOAD DATA INFILE 'rows_to_batch_load.csv' INTO TABLE notificaiton_subscription_settings FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n';
I have created a snapshot of the VM, so I will be able to roll it back, if necessary. Nobody else has access to it, so no risk of creating duplicate setting_ids. And I have no problem running it in command line (I'm using XAMPP on Windows 2019 server). Just trying to figure out what command(s) would do it.
PKPNotification.inc.php-with corresponding decimals from notification_subscription_settings.php
notification_subscription_settings table export.txt
user_id.csv
rows_to_batch_load.csv
Okay so your setting_id field is auto-incrementing, which is good. That means you can completely leave out that field in your data file, as if the column doesn't even exist. Because it's auto-incrementing, the database will fill in 472, 473, 474, etc... automatically.
So edit your rows_to_batch_load.csv file and just remove that first field for every row.
Next, you've got a lot of extra quotes going on in that file. You only need one pair of quotes around each value (and honestly in this car, you likely don't even need any quotes at all).
Currently:
You only need quotes for values that have certain characters inside the values. For example, if you had a formatted currency amount like $1,234 then you would use quotes to tell the program that is reading the file that $1,234 is one value and not two values $1 and 234.
However that doesn't seem to be the case with your values so I don't think you need any quotes at all.
Your load data infile query looks good except for a typo in the table name and the lines should be terminated by \n not /n.
So edit your rows_to_batch_load.csv file and just remove that first field for every row.
Next, you've got a lot of extra quotes going on in that file. You only need one pair of quotes around each value (and honestly in this car, you likely don't even need any quotes at all).
Currently:
"""478"",""blocked_emailed_notification"",""8"",""97"",""8"",""int"""
"blocked_emailed_notification","8","97","8","int"
blocked_emailed_notification,8,97,8,int
You only need quotes for values that have certain characters inside the values. For example, if you had a formatted currency amount like $1,234 then you would use quotes to tell the program that is reading the file that $1,234 is one value and not two values $1 and 234.
However that doesn't seem to be the case with your values so I don't think you need any quotes at all.
Your load data infile query looks good except for a typo in the table name and the lines should be terminated by \n not /n.
Side note - you're using bigint as the data type for a lot of these IDs. It's kind of overkill.
An unsigned bigint goes from 0 up to (drum roll):
18,446,744,073,709,551,615
And you're still at 472 for the setting ID.
The catch is that every bigint takes 8 bytes of space. That might not seem like much at the moment but you have 3 of them on each record in your settings table. So every single record uses up 24 bytes of space to store 3 numbers.
So 1000 records takes up 24,000 bytes.
Now, if you used a smaller type, you can get the same results but without wasting space. For example, a normal int holds numbers from 0 to 4 billion, so it's way more than enough for the size you're dealing with right now, but it's only 4 bytes per field. If you used int for all 3 fields, that's only 12 bytes per record instead of 24.
As I said before, it might not seem like a big difference now, but if your application really starts growing and you start getting tables with a hundred million records in it, every single byte is multiplied that many times. At that point, your database might be 400 megabytes if you use int or it might be 800 megabytes if you use bigint.
It can be really good practice to use the right data types at the very beginning so you have room to grow but you're not being overly wasteful.
An unsigned bigint goes from 0 up to (drum roll):
18,446,744,073,709,551,615
And you're still at 472 for the setting ID.
The catch is that every bigint takes 8 bytes of space. That might not seem like much at the moment but you have 3 of them on each record in your settings table. So every single record uses up 24 bytes of space to store 3 numbers.
So 1000 records takes up 24,000 bytes.
Now, if you used a smaller type, you can get the same results but without wasting space. For example, a normal int holds numbers from 0 to 4 billion, so it's way more than enough for the size you're dealing with right now, but it's only 4 bytes per field. If you used int for all 3 fields, that's only 12 bytes per record instead of 24.
As I said before, it might not seem like a big difference now, but if your application really starts growing and you start getting tables with a hundred million records in it, every single byte is multiplied that many times. At that point, your database might be 400 megabytes if you use int or it might be 800 megabytes if you use bigint.
It can be really good practice to use the right data types at the very beginning so you have room to grow but you're not being overly wasteful.
ASKER
Thank you, @gr8gonzo. I get you point regarding the use of bigint, I am just sticking to whatever Open Journal software creators are supplying, and in a small shop as ours we probably won't see much more growth, so I am not preoccupied by it right now.
As to the load, I removed the first (auto-increment) field from each row and removed the quotes. Placed in the mysql/data directory the csv file that has 2480 rows starting with this (no column titles):
Ran:
LOAD DATA INFILE 'rows_to_batch_load.csv' INTO TABLE notification_subscription_settings FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
Now, the rows got added, but the "setting_name" column is missing and instead of "int" I have "0". See below starting with 472. I probably made a typo somewhere, but I am not sure where.
As to the load, I removed the first (auto-increment) field from each row and removed the quotes. Placed in the mysql/data directory the csv file that has 2480 rows starting with this (no column titles):
blocked_emailed_notification,8,44,8,int
blocked_emailed_notification,268435477,44,8,int
blocked_emailed_notification,8,60,8,int
blocked_emailed_notification,268435477,60,8,int
Ran:
LOAD DATA INFILE 'rows_to_batch_load.csv' INTO TABLE notification_subscription_settings FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
Now, the rows got added, but the "setting_name" column is missing and instead of "int" I have "0". See below starting with 472. I probably made a typo somewhere, but I am not sure where.
"setting_id";"setting_name";"setting_value";"user_id";"context";"setting_type"
"470";"blocked_notification";"16777258";"3800";"8";"int"
"471";"blocked_notification";"268435477";"3800";"8";"int"
"472";"8";"44";"8";"0";
"473";"268435477";"44";"8";"0";
"474";"8";"60";"8";"0";
"475";"268435477";"60";"8";"0";
It's because you changed the field delimiter from a comma "," to a semicolon ";" but your query still used the comma:
...FIELDS TERMINATED BY ','...
If you changed your data to use semicolons as a delimiter, you have to update that "FIELDS TERMINATED BY" to match.
...FIELDS TERMINATED BY ','...
If you changed your data to use semicolons as a delimiter, you have to update that "FIELDS TERMINATED BY" to match.
ASKER
Could it be caused by something else, @gr8gonzo? The csv file I loaded has fields separated by a comma:
but when I export the table, then the exported csv has fields separated by ";'.
blocked_emailed_notification,8,44,8,int
LOAD DATA INFILE 'rows_to_batch_load.csv' INTO TABLE notification_subscription_settings FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
but when I export the table, then the exported csv has fields separated by ";'.
ASKER
Could someone help me make LOAD DATA INFILE work? I will be much obliged!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Million thanks, @gr8gonzo! It worked!
After going through the process a couple of times yesterday, I had to delete a bunch of rows, so I found the command to reset the AUTO_INCREMENT to the one it should be:
Now, after running your last command, everything is up and running and the two notification options for which I needed a check mark in "Do not send me an email for these types of notifications" are now checked off for all users!
After going through the process a couple of times yesterday, I had to delete a bunch of rows, so I found the command to reset the AUTO_INCREMENT to the one it should be:
ALTER TABLE notification_subscription_settings AUTO_INCREMENT = 472
Now, after running your last command, everything is up and running and the two notification options for which I needed a check mark in "Do not send me an email for these types of notifications" are now checked off for all users!
Great!
As far bulk-loading goes, if your text file is already in a CSV-type format that matches the table structure, then you can either upload the file to the server and use the LOAD DATA INFILE query to import the data into the table or you can use the phpMyAdmin import feature which is just a wrapper around the same process.
LOAD DATA INFILE - MariaDB Knowledge Base
There are a bunch of other possibilities (e.g. an INSERT query with multiple records, or a SELECT/INSERT combo query,converting the text file to a bunch of queries and just running it as a .sql file though that's the slowest method). So it kind of depends on what your text file looks like, how many records you have, etc... But since you said you want to do it in phpMyAdmin, its import feature is probably what you're looking for.