DELETE FROM yourTable
WHERE id NOT IN (SELECT MIN(id)
FROM yourTable
GROUP BY anotherColumn)
If you want to remove older rows, use the following:
DELETE FROM yourTable
WHERE id NOT IN (SELECT MAX(id)
FROM yourTable
GROUP BY anotherColumn)
UPDATE `customer_entity`
SET `password_hash` = CONCAT(SHA2('SaltPASSWORD', 256), ':SALT:1')
WHERE `entity_id` = 7615;
Please note that if SPJ9WIp7 is the salt and User’s password is 7qLzX33ETxYE, the following will be your query:
UPDATE `customer_entity`
SET `password_hash` = CONCAT(SHA2('SPJ9WIp77qLzX33ETxYE', 256), ':SPJ9WIp7:1')
WHERE `entity_id` = 7615;
7615 is the customer id
Click on export, and choose “custom” export method.
Lets say you want to export first 20 tables only.
Open chrome or browser console [ctrl+shift+j], enter the following:
$("input[name='table_data[]']").slice(0,20).click();
For next 20, use 20,50 as arguments in slice function used above.
The customizer data is stored in table wp_options under option_name theme_mods_THEME-NAME
you can view all such settings via the following query:
SELECT * FROM `wp_options` WHERE `option_name` LIKE "theme_mods_%"
In some cases, you just want to use parent theme’s settings in newly added child theme. If that is your case too, use the following queries to utilize and close those settings for your child theme also.
update `wp_options` set `option_name`="theme_mods_YOURCHILDTHEME_backup" where `option_name`="theme_mods_YOURCHILDTHEME";
insert into `wp_options` (`option_name`,`option_value`,`autoload`) select "theme_mods_YOURCHILDTHEME",`option_value`,"yes" from `wp_options` where `option_name`="theme_mods_PARENTTHEME";
The following one is a bit risky one. If you understand what is going on here, go ahead.
delete from `wp_options` where `option_name`="theme_mods_YOURCHILDTHEME";
insert into `wp_options` (`option_name`,`option_value`,`autoload`) select "theme_mods_YOURCHILDTHEME",`option_value`,"yes" from `wp_options` where `option_name`="theme_mods_PARENTTHEME";
SELECT * from wp_usermeta where `meta_key` like 'edit_%_per_page'
So probably you chose “Number of items per page” as 1000 or 200 and now the page doesnt load anymore because your database ran out of memory.
If that is the case, run the above query on phpmysql or any other way, edit the numbers and fixed!
This is related to new MariaDb upgrade from 10.1.41 to 10.1.42 and also for servers which were updated from 10.2.27 to 10.2.28
Go to solution ( Special Thanks to @Valetia )
You will see errors in following formats/messages:
Log files (/var/lib/mysql/$hostname.err) will have or can have any of these errors:
/etc/init.d/mysql start
Starting MySQL/etc/init.d/mysql: line 159: kill: (10704) – No such process
[FAILED]
yum downgrade MariaDB-* -y
whmapi1 update_updateconf RPMUP=manual UPDATES=manual
(run as root #)
This will downgrade MariaDb installation to previous version
Line 2 will cancel automatic update on WHM/CPanel installations
If you are not using WHM, use line 1 only OR you can downgrade the repository individually too
yum downgrade MariaDB-server MariaDB-common MariaDB-shared MariaDB-client MariaDB-compat MariaDB-devel
Short Answer:
Not possible via Magento2 Admin Backend.
Solution:
You need to update eav_attribute table and edit information about backend_source, frontend_input etc.
Simple query for that is:
UPDATE `eav_attribute` SET `backend_model`="Magento\\\Eav\\\Model\\\Entity\\\Attribute\\\Backend\\\ArrayBackend", `backend_type`="varchar", `frontend_input`="multiselect", `source_model`=NULL WHERE `attribute_id`=YOUR_ATTRIBUTE_ID_INTEGER LIMIT 1
Replace YOUR_ATTRIBUTE_ID_INTEGER with your attribute_id like 355
SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
Will not work via phpmyadmin or any script. this has to be run in cmd/terminal
Quick Command
wget https://gist.githubusercontent.com/harshvardhanmalpani/e670a8de7aa81673364dd48f125cb9ac/raw/ce04b319bf1594d148d3346e1474119fe1cd1b3f/flushdb.sql
mysql -hYOUR_DATABASE_HOSTNAME -uYOUR_DATABASE_USER -p YOUR_DATABASE_NAME < flushdb.sql
Source:
https://gist.github.com/harshvardhanmalpani/e670a8de7aa81673364dd48f125cb9ac
Let me explain the problem scenario: Let us assume we have a table with 4 columns in it out of which 1 is PRIMARY column and rest 3 contain some data.
What we want to do is to reverse the primary key IDs for that data keeping the rest of data intact. It is like shifting the first row to end and moving last row to starting.
| id | name | anything | |
| 1 | jacob | whatever | how to reverse |
| 3 | nathan | whosoeverisnathan | ids in mysql table column |
| 4 | jagmohan | idontknowjagmohan | without breaking |
| 8 | monica | iamsexy | primary key constraint |
| 9 | batman | idontexistinrealworld | i am batman |
| id | name | anything | |
| 9 | jacob | whatever | how to reverse |
| 8 | nathan | whosoeverisnathan | ids in mysql table column |
| 4 | jagmohan | idontknowjagmohan | without breaking |
| 3 | monica | iamsexy | primary key constraint |
| 1 | batman | idontexistinrealworld | i am batman |
So, this seems simple to reverse an array if isolated by key “id“. But you have to understand that this column is the primary key. So if you run a command to change id for “jacob” to “9“. It will give you error: “Duplicate entry for id 9“
So here is my proposed solution, I start with pair of first and last row and then swap them. Then swap second and second last row. and So on…
If total rows are odd, we will be left with 1 row which does not need correction because it will already be the middle row.
If total rows are even, we would swap middle two rows too.
Here is my solution in PHP
$possible_group_id="3";
$ai=12720; //can be any high int which does not exist in column `id` yet
$q="select id,email from table_name where `possible_group_id`=$possible_group_id order by id asc";
$r=mysqli_query($f,$q);
$row_collection=[];
$ct=[];
while($row=mysqli_fetch_row($r))
{
$row_collection[]=$row;
$ct[]=$row[0];
}
$pt=array_reverse($ct);
$size=count($row_collection);
for($i=0;$i<$size;$i++)
{
$row_collection[$i][2]=$pt[$i];
}
echo "start transaction;<br>";
foreach($row_collection as $k=>$v)
{
if($k < floor($size/2)){
echo 'UPDATE `table_name` SET `id`='.$ai.' WHERE `email`="'.$row_collection[$size-$k-1][1].'";<br>';
echo 'UPDATE `table_name` SET `id`='.$v[2].' WHERE `email`="'.$v[1].'";<br>';
echo 'UPDATE `table_name` SET `id`='.$row_collection[$k][0].' WHERE `email`="'.$row_collection[$size-$k-1][1].'";<br>';}
else break;
}
echo "commit;<br>";
It would output this:
start transaction;
UPDATE `table_name` SET `id`=12720 WHERE `email`="idontexistinrealworld ";
UPDATE `table_name` SET `id`=9 WHERE `email`="whatever";
UPDATE `table_name` SET `id`=1 WHERE `email`="idontexistinrealworld ";
UPDATE `table_name` SET `id`=12720 WHERE `email`="iamsexy";
UPDATE `table_name` SET `id`=8 WHERE `email`="whosoeverisnathan";
UPDATE `table_name` SET `id`=3 WHERE `email`="iamsexy";
commit;