mysql – need help with the update request

table name: - ** t_ci **
s39 column_name
176

I want to have my table like that

s39 column_name
176 176

I do not know the last name. This column name will be the result of a different query.

SELECT column_name FROM `t_core_properties` WHERE property_name = & # 39; Virtual IP address & # 39;

I have tried this query.

UPDATE t_ci SET s39 = (SELECT column_name FROM `t_core_properties` WHERE property_name = & # 39; Virtual IP address & # 39;)
WHERE blueprint_id = 1234;

this comes as

s39 column_name
s360 176

But this only adds the column_name and not the value to

I've tried using the variables set by the Mysql user

SET @column = (SELECT column_name FROM `t_core_properties` WHERE property_name = & # 39; Virtual IP address & # 39;);
UPDATE t_ci SET s39 = @ column WHERE blueprint_id IN (SELECT ID selected in `t_core_blueprints` WHERE NAME = & # 39; VMWare VCenter & # 39;);

I'm getting the same result as before.

Several possible MySQL property associations in a "floral" arrangement – acceptable design?

I have a feature that has several (7+) potential properties to add via a central hub & # 39; hub. In most cases, only one property is assigned, but it is possible that the user wants to add everything. The hub identifier is used to find an intermediate table that is then linked to the content table if there is additional content for the property.

I guess this is the standard approach, but I am new to database design. Is this at all the reasonable approach to such a scenario?

osx mac – MySQL, Sequel Pro via ssh, access error denied

Before going into details, there are at least some questions that appear address the same problematic behavior, but the answers given to the questions do not apply in my situation, which I will explain. Here are the questions whose answers do not apply:

What is happening

I use Sequel Pro (1.1.2) from a new computer, Mac OS 10.14 (Mojave). I have tried to setup a MySQL connection via ssh on a server with which I work regularly, and the following error message is displayed:

Can not connect to HOSTNAME host because access has been denied.

Double check your username and password and make sure that access from your current location is allowed.

MySQL said: Access denied for the user USERNAME & # 39; @ & # 39; localhost & # 39; (using the password: YES)

As an added detail, I use SSL public authentication rather than password authentication.

What is not the problem

The answers to the questions I mentioned at the beginning do not apply because I always have the exactly the same The configuration of Sequel Pro works successfully on my old Mac (El Capitan, 10.11.4). In other words, it has nothing to do with the configuration of MySQL on the remote server, nor with the permissions granted to the user, nor with the specific configuration details that I provide to Sequel Pro, because I manage to connect to the same server with the same user on the old computer.

The only difference between the two configurations is that I use a new SSL key on the Mojave computer, a keyless key because Keychain no longer stores the secret phrases in the same way. I first tried using the original SSL key (with passphrase), which did not work … but for different reasons.

And yes, the new SSL key is working fine … I can very well connect to the destination server from the Mojave command line with the help of the new key.

In addition, I confirmed that the SSH connection to the remote server was not the problem in itself. I've replaced the "SSH User" field of Sequel Pro with a dummy user that does not exist and I have a different error. :

The SSH tunnel was unable to authenticate with the remote host. Please check your password and make sure you always have access.

This suggests that the problem comes from the MySQL connection once the tunnel established … but with the same configuration producing different results from different computers, it makes no sense that it is a problem on the server .

The problem lies therefore in the way the operating system allows Sequel Pro to create and use an SSH tunnel … is not it?

Why I'm stuck

I do not know how to find a solution to this problem, I do not know (with certainty) where to look to identify the problem.

  • I can not figure out how to get a console log from Sequel Pro before the connection is established, to compare exactly what is going on behind the scenes on the respective computers. But as it is the same version of the software in both cases … it does not seem likely to me that this is where the divergence manifests itself
  • I do not know enough about the SSH tunnel with enough confidence to manually recreate the situation without Sequel Pro.
  • I am new for about 3 days at Mojave, and I do not know / still understand what could have changed between El Capitan and Mojave to introduce new variables

Real Sequel Pro error log on the new computer

… Pro Suite drives me crazy because only rarely provides a "Details" button in the failed login dialog. If I manage to get this option again, I will update this question with the log. Of course, if someone knows how to force Sequel Pro to display the details of the connection attempt and the subsequent error, please let me know and I will.

Any idea / help on this problem will be greatly appreciated.

Or, if there is another awesome and free MySQL client based on Mac that will successfully replace Sequel Pro, I'm open to suggestions …

mysql – Accelerates the LEFT JOIN with the OR operator in the ON clause

I have a query that uses a LEFT JOIN with an OR operator in the ON clause. When you comment to the OR operator, the query runs in ~ 150ms. With the OR operator kept in the query, it takes more than 80 seconds to run. Does anyone know how to speed this up? Details below.

réponses_fournisseur
+ ---- + ------------- + ------------- + ---------- + ----- - +
| id | internal_id | supplier_id | supplier | price |
+ ---- + ------------- + ------------- + ---------- + ----- - +
| 1 | 100 | 100 | poggle | 10 |
| 2 | 101 | 101 | poggle | 15 |
| 3 | 102 | 290 | lello | 12 |
| 4 | 103 | 370 | chugs | 10 |
| .. | ... | ... | ... | ... |
+ ---- + ------------- + ------------- + ---------- + ----- - +

vendor update
+ ---- + ------------- + ------------- + ---------- + ----- --- + -------------- +
| id | internal_id | supplier_id | supplier | status | timestamp |
+ ---- + ------------- + ------------- + ---------- + ----- --- + -------------- +
| 1 | 100 | 100 | poggle | 80 | 2019-01-15 ... |
| 2 | 100 | 100 | poggle | 100 | 2019-01-16 ... |
| 3 | null | 290 | lello | 80 | 2019-01-16 ... |
| 4 | 107 | 107 | poggle | 80 | 2019-01-17 ... |
| 5 | 112 | null | chugs | 100 | 2019-01-17 ... |
| .. | ... | ... | ... | ... | ... |
+ ---- + ------------- + ------------- + ---------- + ----- --- + -------------- +

For "poggle" internal_id and supplier_id will be identical.

TO SELECT
*
FROM supplier_responses sr
JOIN LEFT (SELECT *
FROM provider_updates su
LEFT JOIN (SELECT)
supplier_id supplierId,
coalesce (internal_id, supplier_id) latest_id,
Max (timestamp) last_timestamp
FROM supplier_updates
GROUP BY supplier_id, supplier_id) most recent
ON suLatest.supplierId = su.supplier_id
AND suLatest.latest_timestamp = su.timestamp
AND suLatest.latest_id = coalesce (su.internal_id, supplier_id)) su
ON (sr.supplier_id = su.supplier_id AND sr.internal_id = su.internal_id)
OR (sr.supplier_id = su.supplier_id AND sr.supplier_id = su.supplier_id)
- O a clause ...
ORDER BY su.timestamp_at ASC;

mysql – How to optimize UPDATE with a nested SELECT subquery

I've written a complex UPDATE query, and it works, but it seems threatening. Here is what I try to do:

In each topic, the user 'Bob123 & # 39; has posted anonymously. When you publish anonymously in a topic, you get a unique anonymous index for that topic.

Say I want to merge two topics together. Bob123 has a different anon index in both subjects, so its unique anon index would not be unique. I only have two data elements to work with: $ topic_id, the identifier of the topic in which you are merging, and $ post_id_list, all publishing identifiers that have been merged.

I want to update all anonymous_index entries for each separate poster_id message in this topic. This anonymous_index must be the original index that they had in the subject before the other topic was merged into it.

The first SELECT query first selects the anon indexes of the moved publications.
The outer SELECT query gets the anon index of the first non-merged publication (if it is > 0) of these posters merged into the subject and selects a merged index of the first query.

Then I update it. Where the anon index of these posters in this topic does not match the old index, I update it.

Is there anything simple I miss here? I do not like the fact that I have a subquery in a subquery.

At first, I used Having MIN (anonymous_index) <> MAX (anonymous_index) in the same way AND post_id NOT IN ($ merged_post_list)to select the list of poster identifiers to update and an unmerged index, but returned 0 rows with that. If the merged publication is BEFORE all the original publications (and has a larger anon index), then the minimum index will be the maximum index for that poster. So, doing another subquery corrects that …

$ merged_post_list = implode (& # 39 ;, $ post_id_list);

...

UPDATE ". POSTS_TABLE." AS p
INNER JOIN (SELECT p.post_id, p.anonymous_index AS old_index,
merged.poster_id, merged.anonymous_index AS new_index
FROM ". POSTS_TABLE." AS p,
(SELECT poster_id, anonymous_index
FROM ". POSTS_TABLE."
WHERE post_id IN ($ merged_post_list)
AND topic_id = $ topic_id
AND anonymous_index> 0
) Merged AS
WHERE identifier.post_not IN ($ merged_post_list)
AND p.topic_id = $ topic_id
AND index.anonym> 0
AND p.poster_id = merged.poster_id
GROUP BY merged.poster_id
) AS postdata
SET p.anonymous_index = postdata.old_index
OERE p.topic_id = $ topic_id
AND anonymous_index> 0
AND anonymous_index <> postdata.old_index
AND p.poster_id = postdata.poster_id

post_id is the main index, poster_id and topic_id are also indexes.

Here is an example of behavior:

Before the merger:

| post_id _____ poster_id _____ anonymous_index |
| 11 | 3 | 2 |
| 12 | 22 | 1 |
| 14 | 22 | 1 |
| 15 | 3 | 2 |

After the merger:

| post_id _____ poster_id _____ anonymous_index |
| 10 | 22 | 4 |
| 11 | 3 | 2 |
| 12 | 22 | 1 |
| 13 | 3 | 4 |
| 14 | 22 | 1 |
| 15 | 3 | 2 |
| 16 | 22 | 4 |

After UPDATE (the query above):

| post_id _____ poster_id _____ anonymous_index |
| 10 | 22 | 1 |
| 11 | 3 | 2 |
| 12 | 22 | 1 |
| 13 | 3 | 2 |
| 14 | 22 | 1 |
| 15 | 3 | 2 |
| 16 | 22 | 1 |

banco de dados – Connection to MySQL Workbench with localhost: 81

In my Linux machine, I installed the local server I always use, it's the Bitnami Lamp Stack, but it automatically configures the local host on port 81, so far without any problem, but I can connect my local store in mysql workbench No, I have already searched in many sources and I can not find a solution to connect the local store located on port 81 in the workbench. Someone to help me?

MySQL insert with the value derived from the primary id

J & # 39; would INSERT in a MySQL table, so that one of the columns contains a value derived from the primary id.

For example:
insert into picture_table ('image name', 'folder') values ​​('holiday_123.png', FLOOR (pic_id / 1000)

Where pic_id is the auto_incrementing primary id of the table. It would be even better if it was somehow wired to the table. Can this be done?

The best I've found is
insertthen update using the last insertion ID

mysql – enum date

I have the following request:

INSERT IN DATA (DIARUNIOES) VALUES ('Tuesday')

You give the following error:

Truncate data for column & # 39; dayRegions & # 39; at line 1

This field in the table is:

        `Monday, & # 39; Friday & # 39 ;, & quot; Saturday & # 39; Sunday & # 39;) NOT NULL DEFAULT & # 39; Second & # 39;; & # 39;

Where is the error?

mysql – Is it safe to use pt-online-schema-change in a multi-master environment?

I have 2 MySQL servers with replication based on the lines between them. Both are masters and slaves for each other.

As I understand it, pt-osc creates triggers to intercept changes that are running. But from what I know, triggers are not fired in a row-based replication environment. So, I guess pt-osc is not able to catch the changes made on the second master during the change, is not it?

sql – MySQL query to visualize the number of fields (by its heat) in several columns

J & # 39; uses

  • mysql See 14.14 Distrib 5.7.24, for Linux (x86_64)

The following query returns the language of books by author (author_id)

select author_id, language, count (author_id) as c_languaje_books
books
group by author_id, language;

It is presented in the following format, Spanish row and English row (in this example, there is only one author)

enter the description of the image here

My question is:

  • How do I build the query (only with the books table) if I want to display the result as follows – Spanish column and English column (in this example, there are two authors)

enter the description of the image here

Thanks for the help