Tag Info

Hot answers tagged

3

Firstly, find out your constraint name thus: select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'My_Table'; And then remove the constraint by the following means: ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint; Refs: 1 & 2. ...


3

First, get rid of the subquery. It is not needed and it can interfere with optimization. Second, you don't need a left join, because the having clause is requiring matches. SELECT p .*, IF(sum(c.course_id = 2) > 0, 'Yes', 'No') as cTrained, IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as aTrained FROM person p JOIN ...


2

If your traffic is very light, you could enable the general log which logs everything and use that to debug. WARNING this can adversely affect performance - test first at an off-peak time. Following your comment on my comment, please find below what I have in my own my.cnf. As I said, be aware that this is a dev machine and not open to the web. Just keep a ...


2

In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following: set @@session.max_sp_recursion_depth=255; delimiter $$ create procedure get_bu_tree ( in parent int ) begin create table if not exists ...


2

Lets say that the media tables has 1K rows and the emails one has 100K rows. Then this will insert into the emails_media_xref all possible combinations, 1K x 100K = 100M, that is a hundred million rows. Warning, this may take some time: INSERT INTO emails_media_xref (emails_id, media_id) SELECT emails.id, media.id FROM emails CROSS JOIN ...


2

Decided to just remove and reinstall it!


2

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use ...


2

Disclaimer: If you have important data on your server, backup before you perform any of these operations. I did not exhaustively test my solution. To answer this, I did the following on Windows and Linux (example shown is for Linux) Logged on - selected from user table. Deleted all users except the root one with password. Logged out, then logged back in ...


2

You need conditional aggregation using sum(case): select cat_id, sum(case when status = 1 then 1 else 0 end) as published, -- only count status 1 sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0 count(*) as totals from tab group by cat_id Edit: My crystal ball was apparently broken, but now it's fixed :-) If ...


2

Try this it will work INSERT INTO phppos_categories (name) SELECT DISTINCT category FROM phppos_items UNION SELECT DISTINCT category FROM phppos_item_kits


1

You should be restoring to a completely empty folder - you need to remove all the content (files and folders) from the target folder before doing the restore. Xtrabackup contains the entire system, not just a particular database. As always, test the restore on your test environment before attempting on production! And remember to shut down MySQL before ...


1

I experimented with this and found something very interesting. Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino. If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put ...


1

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions. DELIMITER $$ DROP PROCEDURE IF EXISTS Events_insertion$$ CREATE PROCEDURE Events_insertion() BEGIN DECLARE i INT; DECLARE dTime DATETIME; SET i = 1; SET dTime = NOW(); ...


1

If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick. (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version). /* add the Users_ID column to the table */ ALTER TABLE dbo.Content ADD Users_ID INT NULL CONSTRAINT FK_Content_Users_ID FOREIGN KEY ...


1

I answered an old question like this Feb 25, 2014 : Questions since MySQL Startup In that post, I described how everything MySQL executes to retrieve info is a question. I also mentioned how a billion questions is no big deal (See my ServerFault post 1 billion mysql queries in 24 days? Can something be wrong?) Here is the difference Queries : The number ...


1

It doesn't work because the syntax is defined as INSERT .. SELECT i.e. there has to be a valid SELECT in the overall statement. For your "DOESN'T WORK" example, if you comment out the INSERT you're left with an invalid statement. This will not run: --INSERT INTO phppos_categories (name) (SELECT DISTINCT category FROM phppos_items as items UNION SELECT ...


1

The problem you have is that Ansible is trying to use the same root password to login as you want to change it to: - name: Set root user password mysql_user: name=root host="{{ item }}" password="{{ mysql_root_password }}" check_implicit_admin=yes login_user="{{ mysql_user }}" ...


1

The nested set model makes this kind of queries, for unlimited levels, easy to write: SELECT * FROM sr_projects WHERE name LIKE '%x%' UNION ALL SELECT p.* -- the parents FROM sr_projects AS p JOIN ( SELECT lft FROM sr_projects WHERE name LIKE '%x%' ) AS x ON p.lft < x.lft AND x.lft < p.rght ...


1

If you can provide sqlfiddle with data sample that would help a lot. But so far test this query please: SELECT p . *, c.cTrained, c.aTrained FROM (`person` p) INNER JOIN (SELECT `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`, IF(sum(c.course_id = 2) > 0, 'Yes','No') as `cTrained`, IF(sum(c.course_id = 4) ...


1

This is a very common question, and unfortunately the answer is that we've fallen into one of the most serious deficiencies that MySQL has. In MSSQL, we could use recursive CTEs to accomplish this type of result. Just about every other RDBMS has some implementation that will allow for recursive querying. Unfortunately, in order to accomplish this cleanly ...


1

If you are using MySQL 5.6, InnoDB is already the default. Please do not use storage-engine because it is deprecated and probably explains the error. You should use default-storage-engine or default_storage_engine in my.cnf instead [mysqld] default-storage-engine = InnoDB GIVE IT A TRY !!!



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