CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypass’; GRANT ALL ON *.* TO ‘myuser’@’localhost’; CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypass GRANT ALL ON *.* TO ‘myuser’@’%’; flush privileges;
credits to: http://joezack.com/2008/10/20/mysql-capitalize-function/ create a function: CREATE FUNCTION CAP_FIRST (input VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE len INT; DECLARE i INT; SET len = CHAR_LENGTH(input); SET input = LOWER(input); SET i = 0; WHILE (i < len) DO […]
surprisingly, to find the min date per company, which is a super basic query, on mysql you need to do a complicated query. SELECT a.id, a.company, a.email, a.custom_last_email FROM leads a INNER JOIN ( SELECT company, MIN(custom_last_email) mindate FROM […]
update it from the console (will revert upon reboot) from the command line go to the mysql console mysql then edit the sql_mode entry SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”)); SELECT @@sql_mode; SELECT @@GLOBAL.sql_mode; for a persistent change, in mysql […]
This code shows how to delete duplicate rows on pgsql DELETE FROM m_product WHERE sku in( SELECT sku FROM ( SELECT sku, count(*) FROM m_product GROUP BY sku HAVING count(*) > 1 ) a ) AND em_mg_manufacturer = 1000012 […]
UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,’————‘,chr(13)) WHERE em_mg_manufacturer = 1000637 AND value = ‘IMI-YW356A01′ Some additional replace scripts UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,'</li>’,chr(10)) WHERE em_mg_manufacturer = 1000637 UPDATE m_product SET em_mg_longdescription = replace(em_mg_longdescription,'<li>’,”) WHERE em_mg_manufacturer […]
I installed PHPMyadmin to check my help manage my database, eventhough I also use the desktop program MySQL Workbench, I fell sometimes its important to use the webconsole. Recently, upon checking my MySQL Logs (/var/logs/mysql/error.log) I encountered that everyday I […]