How access data between databases in mysql?

I'm working in a project that is divided into multiple modules. Each module have it's own independent database in mysql, but now, the modules need to obtain data between them. For example we're going to develop a new "admin" module, and every other modules need to access the data in the "admin" database. I know that I can make a query like select * from admin.table to obtain data from other database, but each module (and the new "admin" module) are created in CakePHP. I think one possible sol

Weird Locks Problem in MySQL

Quite often my Server hangs and MySQL shows the following process list: Process 1: Time: 24 User: Worker Status: Locked Info: LOCK TABLES tRatings WRITE, tUsers WRITE Process 2: Time: 24 User: Worker Status: Updating Info: UPDATE tUsers SET fGender = '1' WHERE fID = 232049 Process 3: Time: 24 User: Worker Status: Locked Info: LOCK TABLES tClients READ, tUsers uA READ, tUsers aB READ, tNexts READ All tables are InnoDB and I use the explicit LOCK TABLES to prevent InnoDB Deadlocks from happeni

MySQL WHERE and Like Question

Is there a way I can combine the multiple WHERE's into one Here is what I want to combine. SELECT * FROM question WHERE content LIKE '%$search_each%' WHERE title LIKE '%$search_each%' WHERE summary LIKE '%$search_each%'

Using mysql innodb as a queue?

I have two jobs pulling from a mysql table. They both want to get a row, do some work on it and update that row with the results, however I don't want them selecting the same row to update. Whats the best way using mysql/innodb engine to lock a row in the select so that it will be unavailable to other threads but allowing them to select other records? is that possible? thanks!

Mysql start fails with Operating System error 13

I have XAMPP on my Ubuntu Lucid system and everything worked fine. But there seems to be some problem now and mysql wouldn't start. I had tried to recover a few Drupal databases and hence copied the raw files to /opt/lampp/var/mysql folder like all other database folders. And, I guess that could have caused the problem. I am pasting the last few lines of the error log. Someone please help me out. 100814 15:08:29 mysqld_safe mysqld from pid file /opt/lampp/var/mysql/jitendra-laptop.pid ende

MySQL - AND query on joined table

I have a pretty long SQL query: SELECT *, content.contentID AS thisID FROM content LEFT JOIN link_keywords ON content.contentID = link_keywords.contentID LEFT JOIN link_countries ON content.contentID = link_countries.contentID LEFT JOIN link_sections ON content.contentID = link_sections.contentID WHERE status = '1' AND typeID = '1' GROUP BY contentID ORDER BY creationDate DESC I get back a set of results, what I would like to do is display all results where sectionID (which comes from the lin

Fetch column if row exists using MySQL?

I have two tables photo and sale. photo has two columns id and url. sale has two columns photoID and status. The thing is, a photo(row) in photo table doesn't have to have a record in sale table so we do not know if a photo is on sale or not. i.e. photo id | url 1 | http://... 2 | http://... 3 | http://... sale photoID | status 1 | 'sold' 3 | 'pending' As you can see, photo having id 2 doesn't have a record in sale table. What I want to do is to pull all photos

How to optimise this MySQL query

I have the following MySQL query: SELECT k.strNaam FROM klant AS k INNER JOIN project AS p ON p.iKlantID = k.iKlantID LEFT JOIN klant_factuurregels AS kfr ON kfr.iJobID = p.iProjectID WHERE p.iFactuurID = '49' OR kfr.iFactuurID = '49' This query takes more than 0.5 seconds to load, but I am using it in a while loop so that's very inefficient. Thanks in advance Edit the MySQL query with the EXPLAIN command prepended: id | select_type | table | type | possible_keys | key | key_le

Mysql Complex Query with related tables - Optimal Solution

I have 3 Tables: User Feature User_has_Feature: initially all users has no features Example data: User: | id | name | | 1 | Rex | | 2 | Job | Feature: | id | name | | 1 | Eat | | 2 | Walk | User_has_Feature: | id | user_id | feature_id | have_feature | | 1 | 1 | 1 | true | | 2 | 1 | 1 | true | | 3 | 2 | 2 | true | | 4 | 2 | 2 | false | The questions are: ¿How to get only the rec

Mysql Query returns additional rows

I have this kind of table for simple chat: messages table structure +----+---------+-----------+---------+------+------+ | id | to_user | from_user | message | read | sent | +----+---------+-----------+---------+------+------+ And i need to get list of each conversation which looks like that Username ---- Message ---- Date I am using this query to do it: SELECT * FROM `messages` WHERE `sent` IN ( SELECT MAX( `sent` ) FROM `messages` WHERE `from_user` = '1' --id of user who is re

Autoincrement in mysql without unique ids

I have a table with main-ids and user-ids. Each user-id has a set of their own unique main-ids, but multiple user-ids can have the same main-id. Is there anyway to increment a main-id for a specific user without having to do 2 queries?

Mysql Grails Shiro plugin create read only tables

Why grails shiro plugin create read only tables in mysql for roles and permissions? i create controllers and actions and add them into permissions for registered role in BootStrap file. but every time when i want to add new action into permissions, I must delete the tables and recreate them via rerun the app. how I can edit permissions table manually or maybe I must not create roles and permissions in BootStrap for prod.

Mysql How to convert this prepare statement to use placeholders in Wordpress $wpdb?

I have a perfectly working wpdb prepare statement before Wordpress 3.5. This is my line: $post_id = $wpdb->get_var($wpdb->prepare( "SELECT a.post_id FROM $metatable AS a JOIN $metatable AS b ON a.post_id = b.post_id WHERE a.meta_value = '$valuex1' AND b.meta_value = '$valuex2'")); Now with Wordpress 3.5, this returns a warning since this is not fully sanitized. I have modified this to use placeholders as advised by Wordpress: $post_id = $wpdb->get_var($wpdb-&

mysql union optimization

QUERY 1 ( SELECT a.image_name, a.member_id, b.username, c.image_alt , c.img_id, a.total_views, c.island_id, c.personal, c.date_upload, COUNT(d.img_id) AS numComm FROM members_stats_images a, members b, members_images c, members_images_comments d WHERE c.island_id='$island_id' AND c.personal='N' AND c.approved='Y' AND c.is_other<>'Y' AND c.member_id=b.member_id AND a.member_id=b.member_id AND a.image_name=c.image_name AND d.img_id=c.

Mysql Having Foreign key constrain while deleting rows using inner join

I am trying to delete all the rows from all three tables which has the Movie_ID = 29 I even tried writing this DELETE a FROM..... I am having an error : Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (sp_movie.movie_actor, CONSTRAINT2FOREIGN KEY (Movie_ID) REFERENCESmovie_db(Movie_ID) ON DELETE NO ACTION ON UPDATE NO ACTION)

Mysql replace field from another table's field

I need to replace the Table1's filed values from Table2's values while select query. Eg: Table1: Org Permission -------------------------------------- Company1 1,3,7 Company2 1,3,8 Table2: Permission Permission -------------------------------------- 1 Read 3 Write 7 Execute 8 Delete I need like this: Org Permission -----------------------------------

Mysql phpmyadmin limit privileges for a specific database

I want my user to have extended privileges on all databases at the exception of one single database where the user should only have select statement right. I have tried to limit to select only using database-specific option, but it has no effect and the user can still update the database. I suspect the extended privileges override my database-specific option. I don't want to manage database-specific privileges for all databases as I have four users and 100+ databases.

Mysql Inner-Join and group by not showing what I want

I have the following table in place...Please see below table users: ------------- | id | name | ------------- | 1 | mike | | 2 | john | | 3 | bill | ------------- table trans: --------------- | uid | spent | --------------- | 1 | 5.00 | | 1 | 6.00 | | 2 | 5.00 | | 3 | 5.00 | | 3 | 10.00 | --------------- expected output: ---------------------------- | id | name | spent | spent | ---------------------------- | 1 | mike | 5.00 | 6.00 | | 2 | john | 5.00 | null | | 3 | b

table level locking with mysql innoDB

I have 2 tables of type innoDB. I need to lock table 1 until I select a row from table1 and insert a new row in table 2 with the last inserted id of table 1. I face difficulties here as my script might run every second in different sessions. So the records are added to table 1 while inserting to table 2.

Mysql Comparison in where clauses between two subqueries

Is there any way, using Mysql, to compare the result of two subqueries in where clause in an insert statement ? Here is an example of what I'm trying to do. insert into table1 (field1, field2) values (....) where (select sum(x) from table2 where ..) <= (select sum(y) from table3 where ..) In this example, we assume that x and y are numeric values. Thanks you for your help

Can a primary key also be a foreign key in mysql?

Let's imagine a scenario where users have pages which have articles (none or many) in them. I would have these three tables: user,page,article. In this case user table would have primary key username, and page would have two primary keys username and page_name (page name would be fe. "news" which is unique name in user's pages). Now as I said article has two primary keys of which username is a primary key but also refers to user table. Is it possible that in page table I set username as a primar

Mysql Producer Consumer setup: How to handle Database Connections?

I'm building my first single-producer/single-consumer app in which the consumer takes items off the queue and stores them in a MySQL database. Previously, when it was a single thread app, I would open a connection to the DB, send the query, close the connection, and repeat every time new info came in. With a producer-consumer setup, what is the better way to handle the DB connection? Should I open it once before starting the consumer loop (I can't see a problem with this, but I'm sure that one

Mysql Sql Syntax error regarding copy data from different databases

i have a syntax error in this code, but i can't see where since i think that the code is right... i want to copy some column in the database named "w" (table creaturecache) and replace that data with the columns in the "world" database (table creature_template) CODE: UPDATE world.creature_template SET name, `type`, family, rank, modelid2, modelid3, modelid4, Health_mod, movementId, IconName, KillCredit1, KillCredit2, type_flags, questItem1, questItem2, questItem3, questItem4, questItem5, q

Mysql SQL Query Condition With Sum

I have three tables namely the class_record, class_violation, violation. The table class_record has these columns and data: Class Violation CR No. | Class ID 000-000 | A30-000 000-001 | A30-000 The table class_violation has these columns and data: Class Violation CR No. | Violation ID 000-000 | 2 000-000 | 1 000-001

Mysql How to get this additional data with Existing Query

Presently, i am using this query to get some information which i need and is correct SELECT DISTINCT a.AS_No SOURCE, b.AS_No TARGET FROM as_path a, as_path b WHERE a.Update_ID = b.Update_ID AND a.Path_Index = b.Path_Index - 1 AND a.Update_ID IN ( SELECT Update_ID FROM ( SELECT * FROM bgpstorage.announce_update t1 JOIN ( SELECT IP as i, MAX(Update_ID) as maxupdate

Mysql Refresh $scope automatically

I am making an Angular Recipe App which retrieves data from a MySQL database and shows it on the screen. I have a function to "like" the recipe and it just plusses a count to the total likes. But everytime I like it, I have to refresh the page. I would like the scope to refresh it self. I have tried to use the push() function and the apply(), but I cant get it to work. Could someone help me please? Dishes.html <div class="" ng-controller='dishesController' > <div class="dishPanel" ng

Uploading Excel to MySQL - trouble with enum field

I am using SQLYog Ultimate. I have a table with an enum field that can get one of 3 numbres - 0,1,2 I made 0 a default number (wrote it as default in the table) I am creating a table from an excel file and then insert the table content to my table. The problem is only with the enum field: If i leave this column empty, then it stays empty and doesnt get the default value If i enter "0" as number in excel, then it stays empty when i insert to my table If i enter "0" as text in excel, then it s

mysql query works with some particular records only not with all of the data

first image of student_detail table,second is image of payment_detail table when i fire the query like SELECT `student_detail`.`id`, `student_detail`.`first_name`, `student_detail`.`last_name`, `student_detail`.`course`, `payment_detail`.`id`, `student_id`, `inst_paid_date`, `next_inst_date`, `paid_installment`, `next_installment_amount` FROM `student_detail`,`payment_detail` WHERE MONTH(`next_inst_date`)=MONTH(now()) AND `inst_paid_date`<`next_inst_date` AND `stud

Mysql Select rows from a column within the first 45 minutes of the past 1 hour

I'm trying to select rows from a column that were submitted within the first 45 minutes of the last 1 hour plus the last 15 minutes of the hour before that. Basically, it is to query form submissions through a cron job that gives people who might be submitting the form (which has multiple parts) as the cron job is running to have 15 minutes or so to be able to fill out the form before sending their information while it is incomplete. Currently, this works for selecting rows within the last hour

Mysql Summation of a formatted column in SQL

Example table: ID Calls 1 10 2 20 3 15 4 11 5 09 Output Needed: ID Calls Percent 1 10 15.38 2 20 30.77 3 15 23.08 4 11 16.92 5 09 13.85 The "Calls" column is already a formatted one. format(count(id),0) as Calls. That is the values are found by using formulas. Now I want to make a 3rd column which will show the % value of Calls. The SUM (Calls) cannot be used as "Calls" is a manual provided name. How should it be done using MySQL

Mysql Export rows with foreign keys to another database

I'm working with msyql and symfony2. I have a database with 15 tables. I want to export all data starting from a row in the main table, go to each table I have and select all data related to that and move it to another database (which is not empty) and also not loose foreign key relations. I will try to explain it with a very basic example. screenshot here So I want to select the user with id 1 and from that to select all threads connected to that user and from those threads I want to select

How do I exclude records with mysql if?

I have a query that loops through a table of events. We're only interested in the first tests taken that day if that first test passed. So, for example, if we have: s/n test_result first test taken and passed DX123 Terminated 2016-06-16 09:33:38 DX124 Passed 2016-06-16 10:12:01 DX125 Failed 2016-06-16 08:27:48 DX126 Passed 2016-06-16 08:28:16 DX127 Passed 2016-06-16 08:25:02 Then what we want is only those first tests that passed. If DX125 went on to pass

Mysql Select columns by related table and property

Imagine you have 2 tables "article" and "article_manage", with a configuration like this: article: id title private (boolean) article_manage: id article_id user_id I want for each user list all articles where "private" is false or if the user can manage the article. What is the mysql query to return that?

MySQL Update with Delete sub query

I want to do the following Delete all the records from a table called survey and then set the status of deleted reference numbers to zero(0) which is in other table. The query which I am using is below Update ref_numbers rn SET rn.status = 0 where ref_no IN (DELETE FROM survey WHERE id < 302) But this query is not working, I have also tired to modify the delete query like DELETE ref_no FROM survey...... but still it's not working. I think I am missing something which i don't know. An

Mysql SQL: Get an article for each category

There are two tables article and category. nid | title | status ---+-------------+------- 1 | abc | 1 2 | ggg | 1 3 | kkk | 0 4 | rrr | 1 5 | fff | 1 6 | ggg | 1 Where status = 1 is published. cid | nid ---+------------- 1 | 1 2 | 2 2 | 3 3 | 4 1 | 5 2 | 6 Now I want to get a one nid for each cid, no double occurrence of ci

Mysql Randomize order of two columns per row in a query

I want to copy rows from one MySQL table to another while randomizing the order of two fields, i.e. I want to take rows with the fields id,a,b from table1 and insert them into table2, but swap a and b on random rows. This is what I tried: INSERT INTO table2 (id, a, b) SELECT id, IF(@r=RAND() < 0.5, a, b), IF(@r < 0.5, b, a) FROM table1 WHERE table1.filter = 42; I expected that this will generate one random number per row, but it actually just generates one rand

Mysql Table name as a value in column

I got an exercise: Find names of companies in ascending order. The list should include both supplier names and customer names. There should also be information whether this company is supplier or customer. It should look something like this: CompanyName A, Supplier CompanytName B, Customer CompanyName C, Customer What I have been able to do so far: SELECT CompanyName FROM suppliers UNION SELECT CompanyName FROM customers ORDER BY CompanyName ASC; So I am missing the informatio

Confusing mysql select results with LIKE and multiple zeros (%00%)

I have created a search query to search a table for a string in multple fields. It works for every string except if the string contains only one or multiple zeros eg: %000% returns rows with 2 or less zeros. The search fields: `par_partno` varchar(20) utf8_general_ci `par_group_id` varchar(16) utf8_bin `par_desc` text utf8_general_ci `par_details` text utf8_general_ci I already tried to cast par_partno to char CAST(par_partno AS CHAR) but the result is the same. The query: select `par_id`

How to join two selects from the same table in mysql

Ï need to do a join of two selects of the same table (top and bottom rows), but the inner join returns an empty set. I do not understand how this is possible since I am doing the join on a new column which is the same for both tables. Here are the select sub-queries for the bottom-row and top-row tables: bottom rows: select @n := @n + 1 row_number, st.* from (select @n:=0) init, strmrs st where timestamp between 1514764860 and 1517443140 order by timestamp desc, username limit 10; which giv

How to fix MySql.Data.MySqlClient Error in visual studio?

I've Imported MySql.Data.MySqlClient but the Visual Studio wont recognize it. I already installed MySql Connector and MySql for Visual Studio but I reinstall and it fixes the problem. However, when I start my program in visual studio the problem will pop up again and I don't know how to fix this. Sorry for my bad English. When I start the program this error will pop: Warning 1 The primary reference "MySql.Data, Version=8.0.18.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d, pro

Mysql Cloudways SQLSTATE[HY000] [1045] Access denied for 'root'@'localhost' (using password: YES) (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE')

i'm trying to execute database migration in my cloudways server but it returns me this error: SQLSTATE[HY000] [1045] Access denied for 'root'@'localhost' (using password: YES) (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE'). image error here I've been deploied my laravel project on cloudways from gitlab repository and i've also edited by cloudways terminal the file .env and changed the value DB_DATABASE with the name of my database and DB_USERNAME & DB_PASSWORD with my database

MySQL select random row with each different value - comma separated input

I have a comma separated string : input_str : 'gold,silver,gold,broze,silver' I have one table cards which has a column color. Cards table has hundreds of rows for each of the colors. Now, with a MySQL query, I want to select 1 random row from cards with these colors So, result would be id color 6251 gold 7251 silver 827127 gold 234 bronze 27816 silver I have tried at least 10 different queries but none of them work correctly. The approach that I used is converting the comma

Capitalise the first letter of each word in Mysql

I got a question asking- Write a Query to print the word 'beautiful vintage', but capitalising the first letter in each word, in mysql. I googled and got suggestion from stack overflow using sub string combination with upper So I tried : Select CONCAT(UPPER(SUBSTRING('beautiful vintage',1,1)),LOWER(SUBSTRING('beautiful vintage',2))) as dd; o/p ---> Beautiful vintage (where v is not getting converted) Yet I could only capitalize the first word and couldn't the second word. Please he

How can I get the latest date from two columns in MySQL Workbench?

I have a table look like this: ID B C date01 date02 11 xxx xxxx 2020-05-01 2020-05-02 11 xxx xxxx 2020-05-01 2020-05-03 11 xxx xxxx 2020-05-01 2020-05-01 11 xxx xxxx 2020-02-03 2020-02-08 11 xxx xxxx 2020-02-03 2020-02-31 22 xxx xxxx 2020-05-01 2020-05-02 22 xxx xxxx 2020-05-01 2020-05-03 22 xxx xxxx 2020-05-01 2020-05-01 22 xxx xxxx 2020-02-03 2020-02-08 22 xxx xxxx 2020-02-03 2020-02-31 I want

how to find out case when in select mysql 5.7

this is my fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=19b6e1298eba9eaa92ba2c726916d5d2 assume i have this table CREATE TABLE Prime_Table ( user_Id varchar(100), join_date date, platform varchar(100), country varchar(30) ); INSERT INTO Prime_Table VALUES ('895A8F53-C61C-471A-B934-CC2795286E19', '2020-01-01','IOS', 'United States'), ('2B4C47D7-0DDC-4007-B1CF-4E3C802E5D5D', '2020-01-02', 'IOS', 'United States'), ('5309F361-DBF9-4AF9-8B6B-79F377C3647C', '2020-01-02', 'IOS', 'United

Mysql Is it normal to perform INNER and LEFT JOIN with 4 tables?

I'm building a community website, I've made a timeline system (just like Facebook). People can post text, or create and post an event as a post (post type will be 'event'). I have the following tables: Posts id(pk), user_id, content, type, space, space_id, date, visibility Users id(pk), username, profile_photo, f_name, l_name Likes id(pk), post_id, user_id Events id(pk), title, visibility, event_type I noticed that fetching 5 posts is taking 700ms, because I was getting the posts in one query, t

  1    2   3   4   5   6  ... 下一页 最后一页 共 1251 页