Hot answers tagged

2

Nothing wrong with your query. I already tested in my mariadb database version 10.2.25 and it works Please check your mysql version. If you want to use mysql, please upgrade to mysql version 8 If you want to switch to mariadb, make sure version minimal 10.2


2

Yes and no. Yes in that the PK column(s) are tacked onto each UNIQUE index and the INDEX implicitly generated by each FOREIGN KEY. No in that the PK column(s) are not part of the uniqueness constraint implied by UNIQUE. For example: PRIMARY KEY(id), UNIQUE(x) is like PRIMARY KEY(id), INDEX(x,id) and a uniqueness constraint on only `(x)`. FOREIGN KEY(y)...


2

I don't know about MySQL but Postgres never locks the whole table when running DML statements - regardless of the number of affected rows. Additionally writers never block readers, so even if you update all rows in a table, other transactions will still be able to SELECT from that table - obviously seeing the old values until your DML statement is ...


2

This information, along with other user properties, is found in the user table in the mysql schema (database). The value 'Y' would indicate an expired password select password_expired from mysql.user where user = 'whatever'


2

Use INSERT IGNORE and have a unique key on the date. The first insert to run for the day will succeed; the rest will be "ignored".


1

As it stands this query: select ename from employee where eno in( select eno from works_on where pno =(select * from project)) Will return a 116 Error in SQL Server. Msg 116, Level 16, State 1. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Due to having a subquery with SELECT *, in the ...


1

Figured this out if anyone comes across this and wants the solution: SELECT color, TRIM(TRAILING ',' FROM GROUP_CONCAT(REPEAT(CONCAT(length,','),qty) SEPARATOR '') ) FROM buyer_advert GROUP BY color This makes a nice array like I wanted.


1

The three ways I can see this being handled is to either: 1) Have an audit table that tracks any changes to the questions\forms tables. This would store the records being updated before they are changed, along with a date. 2) If you foresee the questions\forms being changed often, it might be better to store the actual questions in the response table, only ...


1

> function get_santri_certificate($detail_id) > { $q = $this->db->query > ( > "select detail.*, student.name from detail > inner join detail on detail.detail_student_id = student.name > where detail.detail_student_id=".$student_id > ); > return $q->row(); > } This is PHP code, but it looks to me ...


1

As for MySQL - it depends on the engine used ... https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a ...


1

8M is a very old default for MySQL. Yes, it is laughable. It is much too small for any instance today. Either that my.ini was left over from some previous installation, or it is worth a bug report. If you have at least 4GB of RAM, set innodb_buffer_pool_size to about 70% of RAM.


1

DepartmentID in Employees is referencing to a table that doesn't exist as a result it raises error.Always create tables in the order of dependency.Swap the order of table creation i.e push Department table ahead of Employees table and foreign key must be same data type of referencing key. DROP TABLE IF EXISTS Trans; DROP TABLE IF EXISTS Location; DROP TABLE ...


1

MySQL 8.0 Reference Manual / ... / Setting Account Resource Limits (valid for 5.5+ at least). The server stores resource limits for an account in the user table row corresponding to the account. The max_questions, max_updates, and max_connections columns store the per-hour limits, and the max_user_connections column stores the MAX_USER_CONNECTIONS ...


1

It seems you need: CREATE DEFINER=`root`@`localhost` PROCEDURE `Migrate`() BEGIN START TRANSACTION; INSERT INTO new_customers(firstname, lastname) SELECT firstname, lastname FROM customers; INSERT INTO new_addresses(customer_id, address_line_1, address_line_2) SELECT nc.customer_id, a.address_line_1, ...


1

If you can tolerate a little downtime you can make a snapshot and share the snapshot with the new account. This is a simple and cheap approach though you will be down for the time it takes to make the snapshot, share and then restore it and then point your clients to the new instance. For a solution with much less downtime, if you are using RDS you can ...


1

You have actually 3 possibilities to run the queries: Run everything that's in the editor. This is like running an external script. Workbench opens a result tab for each statement that returns a result set. Use the flash icon or cmd/ctrl+shift+enter to execute (cmd on macOS, otherwise ctrl). Select a single statement or multiple statements and press the ...


Only top voted, non community-wiki answers of a minimum length are eligible