New answers tagged

0

No. A foreign key cannot use a literal value, nor can it use an expression or a virtual generated column. It must use a column identifier. FOREIGN KEY <name> (<column1>, <column2>, ...) REFERENCES <tablename> (<column1>, <column2>, ...) It looks like you're trying to do polymorphic-associations. The easiest solution to do ...


0

In general, a mapping table does not need its own id. Instead, the PRIMARY KEY should be a composite key with the 2 (or more) column-ids referencing the tables that are being related in a many-to-many relationship. For a 1-to-many relationship, a mapping table is unnecessary. Your "DLB" smells like a "Relation", hence the above comments ...


0

What does it take to "create a purchase order"? If it is something outside MySQL, then the process flow is impossible. Instead, work on gathering the data needed for the purchase order, return that data to your application, which will then "create the purchase order(s)". Try not to use "cursors". They are inefficient, clumsy, ...


0

You don't need self-joins here. You can use a simple GROUP BY/HAVING with conditional aggregation. The patient join is unnecessary. The purpose of the date filters is unclear. You may want to re-check the logic. Ideally, instead of using BETWEEN, you should always use half-open intervals with dates >= AND < SELECT COUNT(DISTINCT p.patient_id) AS ...


0

New problem raised which changed the question life: I've always used to start second instance of MySql 8 with command: mysqld_safe --defaults-file=/etc/mysql/conf.d/game.cnf --sql mode="NO_ENGINE_SUBSTITUTION" And the problem that in Debian command line I can connect to this database with: mysql --socket=/var/run/mysqld/second_server.sock -u root ...


0

Include this: --no-create-info It means: "Do not write CREATE TABLE statements that create each dumped table." -- https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html


0

to ignore multiple tables, you need to exclude all one by one Like mysqldump -u root -p DB_NAME --ignore-table=DB_NAME.table1 --ignore-table=DB_NAME.table3 > database.sql


0

word press has a variable for the database, as long as you have the configuration correct it will apply to all script automatically. So you have no worries about which database you are using // ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define( 'DB_NAME', 'database_name_here' ); ...


0

Improved version of @a1ex07, to consider duplicated scores SELECT id, Name, (SELECT count(*) from (select Score from table_name group by Score) a WHERE a.Score > b.Score) as RNK, Score FROM table_name b; You can replace the nested group select by count(DISTINCT Score) instead of count(*) to simplify the query, but the ...


0

No problem. USE mumer_watches; SELECT ... FROM wp58_posts will refer, unambiguously, to the table mumer_watches.wp58_posts. Ditto for SELECT ... FROM mumer_watches.wp58_posts Assuming this is WordPress, well, that's the way it works.


0

If you disable innodb_stats_on_metadata, InnoDB still updates the stats periodically: On first opening the table after a MySQL Server restart. When the table changes 1/16 of the rows. When the table performs over 2 billion row updates. When a client executes the SQL statement ANALYZE TABLE <tablename>. In addition, if InnoDB persistent stats are ...


0

The best thing you can do is drop the id column as it: Allows absolute junk to be inserted into your table Prevents utilizing MySQL's clustered index to optimize the query Takes up space, requiring more page reads to return the same amount of information The primary key is (Meter,Datetime)1, so we should define it as such. This will: Prevent inconsistent ...


0

First, let's focus on the speed of the temp table t. After that, we can worry about browser.name. (And why t needed to be materialized and auto-key'd.) Probably UNIQUE KEY `mu` (`reference_date`,`browser_id`) was promoted to be the PRIMARY KEY, but I don't know for sure. However, for this type of data, I usually find it optimal to have only PRIMARY KEY(...


1

The default installation is fine for typical usage. If you have extremely heavy usage, then explain what kind of app you have and how much RAM and data. Swapping is bad for performance, but not having swap space when needed is worse. Still, generally a dedicated server with a default configuration is not likely to hit such. A "busy system" would ...


0

Use backtics around the expression (which is the column name in the new table.) ALTER TABLE dist RENAME COLUMN `ST_DISTANCE(POINT(x1,y1),POINT(x2,y2))` TO Values; Note RENAME COLUMN was added in 8.0 and 10.5; if you have an older version, you must use the more verbose CHANGE COLUMN syntax. (Oh, and be cautious about VALUES; it may be a reserved word.)


1

In the trigger, change IF NEW.delivery_date = "" to IF NEW.delivery_date < '1971-01-01' That will catch empty string and '1970-01-01' and any timezone variation on that "zero" date. It will fail if it is already NULL`, but that won't change the action of the Trigger. (In the future, be sure to include any relevant Triggers in the ...


0

I've done some tests and it looks like that restoring a mysqldump backup on the primary using mysql will get written into the binary log correctly and the changes automatically applied to the replicas. i.e. mysql -u root -p < db-backup.sql will work fine!


0

Purging the last binlog on the Primary is risky. The data in the replication stream is sent to the Replica(s) at the same time it is added to the last binlog on the Primary. If the network goes down, the binlog is vital for providing the replication stream to the Replicas when the network comes back up. I like to keep enough binlogs to hold a week's worth of ...


0

Besides that Values is a very bad choice for a column name, as are all reserved words You can give the column a name by creation. like CREATE TABLE dist (`Values` DECIMAL(12,11)) SELECT ST_DISTANCE(POINT(x1,y1),POINT(x2,y2)) as 'values' FROM config;


0

Yes, replicas function fully even though they aren't replicating. "IO" is the communication with the master where new binary logs are fetched. "SQL" is the application of these fetched binary logs on the tables and everything in the server. At the moment you have lost everything beyond the master 'mysql-bin.000001' at 2314. If what is ...


0

{ Related to @nbk's suggestion (in a comment on the question) to use: sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' -i myfilename.sql } I think there are two main options for running Unix commands in Windows: "Windows Subsystem for Linux Documentation (WSL)" — this is provided by Microsoft. According to the "Windows interoperability with ...


2

You have definitely more than one option here. There is another method with grouping and aggregation and it uses conditional counting: SELECT annex_id FROM table GROUP BY annex_id HAVING COUNT(CASE sort_order WHEN 1 THEN 1 END) = 0 ; The conditional counting itself has several ways of expressing in MySQL. Instead of COUNT(CASE sort_order WHEN 1 ...


2

For anyone wondering, as per matigo's instruction the query would look like the following SELECT annex_id FROM table GROUP BY annex_id HAVING MIN(sort_order) > 1


0

Yes, finally found it. Actually, some SQL order programmatically disabled replication using following PHP order : $db->exec('SET sql_log_bin=OFF'). I don't know why it was there and it took us a lot of time to understand where it came from, but we finally did it. I wish nobody experience this, but if it happens, maybe this comment will help


0

Those 3 settings are probably wrong. When autocommit=0, nothing gets stored until a COMMIT is issued. Look in the dump file; it will probably have a lot of settings, plus some huge (multi-row) INSERTs. Big INSERTs like those work best with autocommit=ON. 150GB is an odd RAM size; are you sure? These sound dangerous, even with 150G of RAM: ...


0

several causes can lead to this, but it may be memory. Please check your system logs to see if Kernel is killing the process due to too much memory usage. For example: sudo cat /var/log/syslog | grep -i "killed" Or: dmesg -T | grep -i "killed" Check the memory consumption while you do the process. For example with: watch free -h Can ...


7

Hope you're doing great. I would like to see your schema to make sure all is good. I tried to recreate what I believe you did. Here is what I did: mysql> create database test295895; mysql> use test295895; mysql> create table my_table (my_column JSON); Then I inserted the values you showed: mysql> insert into my_table VALUES('{"my_value"...


0

Judging by your design you are looking for this only if a staff_members then update the services rows that refer to that tutoring entry, that was changed. I added also to remove the old Staff_Memebers as it only updates when that value has changed. DELIMITER $$ CREATE TRIGGER UpdateStaffNum AFTER UPDATE ON collegedb.tutoring FOR EACH ROW BEGIN IF ...


3

The performance differences between those distros are so subtle that any of them could be the best choice for a given workload. The only way to get a definitive answer for your workload is for you to run your own benchmarks. None of them are likely to be a great bottleneck on MySQL, or a great advantage over the others. I'd recommend that you just pick the ...


0

If you are using a case-folding collation (which is the default), LOWER() is unnecessary. You probably need a GROUP BY SELECT COUNT(*), Dine_Type FROM Restaurant WHERE City = 'Cork' GROUP BY Dine_Type; I discussed HAVING AVG(Rating) > Rating in another Q&A. Again missing GROUP BY: SELECT c.Name , COUNT(*) From Customer c JOIN ...


0

create table xy (x int, y int) insert into xy values (1,3), (5,9) set @point_o = POINT( 3,5) select ST_Distance(@point_o, POINT(x,y)),x,y FROM xy ST_Distance(@point_o, POINT(x,y)) | x | y --------------------------------: | -: | -: 2.8284271247461903 | 1 | 3 4.47213595499958 | 5 | 9 ref: fiddle


0

You have everything you need right there in your screenshot. It just needs to be put together: SELECT ST_Distance(POINT(`origin_lat`, `origin_long`), POINT(`dest_lat`, `dest_long`)) as `distance`; This will give you the information you seek 👍🏻


2

A1: One of the inserts fail as they are not done in parallel. A2: As pointed in other answer - there is at least row level lock, and after UPDATE is finished DELETE then removes the row. If DELETE comes first then UPDATE does will not update that record.


1

If LATERAL joins were supported (they are in MySQL 8+): SELECT rrr.name , SUM(rrr.action = 'respond' ) AS total_l2_respond_by , SUM(rrr.action = 'return' ) AS total_l2_return_by , SUM(rrr.action = 'resolved') AS total_l2_resolved_by FROM master_apps_issue AS mai CROSS JOIN LATERAL ( SELECT mai.l2_respond_by, 'respond' WHERE mai....


1

It isn't really possible to do this in one scan: the grouping and filtering are completely different. It seems a full join would be more in order here: select ifnull(resp.l2_respond_by, ret.l2_return_by) as userid, resp.total as total_respondby, ret.total as total_returnby from ( select l2_respond_by, count(l2_respond_by) as total from ...


1

There are going to be some assumptions in this answer. As there is no sample dataset with which to test the results, keep these in mind and be sure to adjust the query to suit your situation: the COUNT() functions are using an id of some sort rather than a user_id, as this makes it much easier to verify accuracy the source table is assumed to contain a ...


0

Copy /lib/systemd/system/mysql.service to /etc/systemd/system/mysql_replica.service. In the copied service file add --defaults-file=/home/user/mysqld_replica.cnf to the ExecStart* directives as the first argument to mysqld. Reload systemd to read this systemctl daemon-reload. Start the service systemctl start mysql_replica.service. Enable on boot with ...


0

As J.D. has alluded, the films_made column should be using COUNT() rather than SUM(). If the query is to show the number of movies released per actor per year, then you’ll want to add release_year into the GROUP_BY. Otherwise, if you are looking for one row per actor, you may want to use MIN() or MAX() with the release_year to show when they appeared in ...


1

There are a few ways to accomplish this. There’s the slowest way: WHERE (page_visited LIKE '%Homepage%') OR (visitor_id LIKE '%Homepage%') There’s the slow and expensive way: WHERE CONCAT(page_visited, '|', visitor_id) LIKE '%Homepage%' Note: The pipe (|) is there to prevent a match being the result of a concatenation. There’s the inefficient way: WHERE ...


0

Use CREATE OR REPLACE to atomically replace the view. create_view_query = """ create or replace view chicago_public as ... So you don't need to enable multi=True for the query.


1

You have multiple queries so you need to use cursor.execute(create_view_query,(), multi=True)


0

Got the same problem and found a convenient solution - at least for my tasks. If you always have the same ORDER BY eg popularity or whatever and don't do frequent updates, eg if it's enough on a daily basis, an idea is to set up a cronjob which resorts the table every night. ALTER TABLE my_table ORDER BY my_sort_column ASC/DESC This way you get the sorted ...


-2

in temp table you can make some index field , for example other table`s name , and make a regular index with integer like id . its best way to speedup your queries .


1

Do you have something like a "data warehouse"? And you are doing "reports? That is, if you are summarizing (COUNT, SUM, etc) the information, then build and maintain "summary table(s)". More discussion: http://mysql.rjweb.org/doc.php/summarytables That will be much faster and can be as up-to-date as necessary. Please provide ...


0

Why track them? If it is to see everything that is happening, use the general log. If it is looking for performance issues, use the slowlog. Neither the general nor slow logs limit output to specific table(s). The Audit plugin, if available, is probably better for the former case. Various "proxy" products could sit between clients and the server; ...


0

It seems that you have 3 ratings: From one user for one restaurant An average for a restaurant An overall average for all restaurants. Give this a try: SELECT Restaurant_ID, Name FROM ( SELECT r.Restaurant_ID, r.Name, ( SELECT AVG(Rating) FROM Review WHERE ...


3

Don't use the @@ sigil when you set the variable. You can use this when you use the value of a variable in an expression, but not when you set it. Example: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.23 | +-----------+ mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@% ...


0

I was able to reduce my SELECT query against INFORMATION_SCHEMA from 12s to 0.001s by adding AND TABLE_SCHEMA='DB_NAME' to the query, on MySQL 5.6. Before (12s): SELECT AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'myTableName'; After (0.001s): SELECT AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '...


0

You can do it using MySQL AUDIT PLUGIN (Requires license) [["filter": { "class": { "name": "table_access", "event": { "name": [ "insert", "update", "delete" ], "abort": { "and": [ { "field": { "name": "table_database.str"...


Top 50 recent answers are included