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
Quite often my Server hangs and MySQL shows the following process list:
Info: LOCK TABLES tRatings WRITE, tUsers WRITE
Info: UPDATE tUsers SET fGender = '1' WHERE fID = 232049
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
Is there a way I can combine the multiple WHERE's into one
Here is what I want to combine.
WHERE content LIKE '%$search_each%'
WHERE title LIKE '%$search_each%'
WHERE summary LIKE '%$search_each%'
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?
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
I have a pretty long SQL query:
SELECT *, content.contentID AS thisID
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
Do I need to define an index for a MySQL table with a single row (settings table) for performance/optimization reasons?
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.
id | url
1 | http://...
2 | http://...
3 | http://...
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
I have the following MySQL query:
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
I have 3 Tables:
initially all users has no features
| id | name |
| 1 | Rex |
| 2 | Job |
| id | name |
| 1 | Eat |
| 2 | Walk |
| 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
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 MAX( `sent` )
WHERE `from_user` = '1' --id of user who is re
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?
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.
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-&
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
members_stats_images a, members b, members_images c, members_images_comments d
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)
I need to replace the Table1's filed values from Table2's values while select query.
I need like this:
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.
I have the following table in place...Please see below
| id | name |
| 1 | mike |
| 2 | john |
| 3 | bill |
| uid | spent |
| 1 | 5.00 |
| 1 | 6.00 |
| 2 | 5.00 |
| 3 | 5.00 |
| 3 | 10.00 |
| id | name | spent | spent |
| 1 | mike | 5.00 | 6.00 |
| 2 | john | 5.00 | null |
| 3 | b
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.
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
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
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
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)
SET name, `type`, family, rank, modelid2, modelid3, modelid4, Health_mod, movementId, IconName, KillCredit1, KillCredit2, type_flags, questItem1, questItem2, questItem3, questItem4, questItem5, q
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
Presently, i am using this query to get some information which i need and is correct
a.AS_No SOURCE, b.AS_No TARGET
a.Update_ID = b.Update_ID
AND a.Path_Index = b.Path_Index - 1
AND a.Update_ID IN (
FROM bgpstorage.announce_update t1
SELECT IP as i, MAX(Update_ID) as maxupdate
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?
<div class="" ng-controller='dishesController' >
<div class="dishPanel" ng
I have this code,
WHERE NOT HEX(description) REGEXP '^([0-7][10-191A-1F])*$'
but when i run it, I get invalid character range from regexp.
Thanks for the help.
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
I have three tables:
answer (question_id, answer_id, user_id)
How can I select the questions that a user didn't answer?
first image of student_detail table,second is image of payment_detail table when i fire the query like
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
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
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.
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
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
Imagine you have 2 tables "article" and "article_manage", with a configuration like this:
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?
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.
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
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)
IF(@r=RAND() < 0.5, a, b),
IF(@r < 0.5, b, a)
WHERE table1.filter = 42;
I expected that this will generate one random number per row, but it actually just generates one rand
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:
ORDER BY CompanyName ASC;
So I am missing the informatio
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.
Ï 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:
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;
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=184.108.40.206,
i'm trying to execute database migration in my cloudways server but it returns me this error:
SQLSTATE[HY000]  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
I want to synchronize HBASE Database as per the update triggers generated by the MySQL with the help of Kafka Streams or Kafka Connect. Any suggestions on its possibility and how is it possible ?
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
I have tried at least 10 different queries but none of them work correctly.
The approach that I used is converting the comma
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.
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
this is my fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=19b6e1298eba9eaa92ba2c726916d5d2
assume i have this table
CREATE TABLE Prime_Table (
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
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:
id(pk), user_id, content, type, space, space_id, date, visibility
id(pk), username, profile_photo, f_name, l_name
id(pk), post_id, user_id
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 页