mysql – Page 2 – Make Me Engineer

In MySQL SERVER 8.0 the PASSWORD function not working

June 14, 2023 by Tarik

If you need a replacement hash to match the password() function, use this: SHA1(UNHEX(SHA1())); E.g. mysql> SELECT PASSWORD(‘mypass’); +——————————————-+ | PASSWORD(‘mypass’) | +——————————————-+ | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +——————————————-+ and replacement that gives the same answer in version 8: mysql> SELECT CONCAT(‘*’, UPPER(SHA1(UNHEX(SHA1(‘mypass’))))); +————————————————-+ | CONCAT(‘*’, UPPER(SHA1(UNHEX(SHA1(‘mypass’))))) | +————————————————-+ | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +————————————————-+

Use A Union Or A Join – What Is Faster [closed]

June 14, 2023 by Tarik

Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table. The Join will go through each row of both tables, finding matches in the other table therefore needing a lot more processing due to searching … Read more

MySQL bulk INSERT or UPDATE

June 14, 2023 by Tarik

You can insert/update multiple rows using INSERT … ON DUPLICATE KEY UPDATE. The documentation has the following example: INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); Or am I misunderstanding your question?

What are the benefits of only_full_group_by mode?

June 14, 2023 by Tarik

only_full_group_by = on tells MySQL engine: Do not apply GROUP BY when you have doubt about what results to show and throw an error. Only apply it if Group By specifically tells you what to do. i.e. when the Group By is full and perfect! only_full_group_by = off tells MySQL engine: always apply GROUP BY … Read more

How to SELECT by MAX(date)?

June 14, 2023 by Tarik

This should do it: SELECT report_id, computer_id, date_entered FROM reports AS a WHERE date_entered = ( SELECT MAX(date_entered) FROM reports AS b WHERE a.report_id = b.report_id AND a.computer_id = b.computer_id )

PDO were rows affected during execute statement

June 14, 2023 by Tarik

Try $q->rowCount(). Prepared statements will return the number of affected rows via that method.

Errno 121, duplicate key on write or update?

June 14, 2023 by Tarik

This is likely because you have named at least one constraint with the same identifier as a column: /* You already have a column named `restaurant` in this table, but are naming the FK CONSTRAINT `restaurant` also… */ CONSTRAINT `restaurant` FOREIGN KEY (`restaurant` ) REFERENCES `mydb`.`restaurants` (`id` ) ON DELETE NO ACTION ON UPDATE NO … Read more

PID error on mysql.server start?

June 14, 2023 by Tarik

I ran into this same problem when installing via homebrew. Make sure you run these commands (which are listed during install but easy to miss): unset TMPDIR mysql_install_db

Error Code: 1822. Failed to add the foreign key constaint. Missing index for constraint

June 14, 2023 by Tarik

create_user INT UNSIGNED ZEROFILL cannot reference id INT, because these count as different data types for purposes of foreign key reference. Make them the same data type. The only data type difference that is permitted between columns in a foreign key relationship is length of a varchar. For example, VARCHAR(10) can reference VARCHAR(20) or vice-versa. … Read more

MySql SELECT union for different columns? [duplicate]

June 14, 2023 by Tarik

If you have different fields that have different meaning too, you can’t and shouldn’t return them in the same position. You can however ‘fill in the blanks’ by adding null to your fields, like this: select id, name, date, null as userid, ‘A’ as recordtype from table1 union all select id, name, null /*as date*/, … Read more