New answers tagged

0

I stumble upon the error while connecting hosted mysql on amazon ec2 via ssh. I came to realize you have to edit the following file. /etc/ssh/sshd_config Change the following: AllowTcpForwading from no to yes Also check if the path to your ssh is right on this file in /ect/ssh/sshd_config. AuthorizedKeysFile /home/user/.sshkey Then reboot your system ...


0

There are three ways that you can do this - the final method of choice may well depend on the structure of your data - you've only provided one sample example - the NULLs may well vary from record group (grouped by code and des) to different record group. In order to answer your question, I did the following (a fiddle is available here): Create and ...


0

To compliment Alex's answer above, you could also save and restore the default variables rather then just blindly setting them to 1 after. In most cases, setting them back to 1 is what you want, but just in case, here's a way to set them back to the way they were before the import took place: PRE Import: SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0; ...


1

Unclear. What does drink_no signify? You should not try to maintain a "running count" of how many drinks a person has consumed on a given day. Instead, simply store that they have consumed another drink and let your queries do the "adding up": select drinker , odate , count( odate ) total from table1 group by drinker, odate order by drinker, odate ; ...


0

When the writes are single sector, and OS guarantees that single sector writes are atomic. At the time being, it is just Windows with innodb-page-size=4K, and 4K sector size disk. Nothing definitive was ever said about Linux single sector writes being atomic, although I presume it could be, in some circumstances, and depend on the file system.


0

After a little more research, I figured out the solution to this "gaps and islands" problem: SELECT t.tag, begin AS start_date, MAX(end) AS end_date FROM ( SELECT a.* CASE WHEN (DATEDIFF(start_date, @prev_date) = 1 AND DATEDIFF(end_date, start_date) = 1) THEN @begin := @begin ELSE @begin := start_date END AS begin, ...


2

Another solution is one table per quantity. +-----------+------------+-----------+ | device ID | date | quantity1 | +-----------+------------+-----------+ | A | 2020/06/03 | 10 | +-----------+------------+-----------+ +-----------+------------+-----------+ | device ID | date | quantity2 | +-----------+------------+-----------+ | ...


0

Use a prepared statement DELIMITER $$ CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12)) BEGIN DECLARE `_rollback` BOOL DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1; DECLARE _refID INT DEFAULT 0; START TRANSACTION; SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp; INSERT INTO filenamedt (...


1

You could use the window function row_number, like so: select id, collection, name from ( select id, collection, name, row_number() over (partition by collection order by id) rn from stac_item ) t where rn < 3 order by id


0

SELECT w.* , IF(@coll = @coll:=w.collection, @cnt:=@cnt+1, @cnt:=1) AS counter FROM stac_item AS w WHERE @cnt := 1 ORDER BY w.collection ASC, w.name ASC HAVING counter < 3 This query can be very slow on a huge tables


2

If I understand correctly, you have hundreds of measuring devices, and you're contemplating having a table for each one, or one table for all -- is that correct? And for the one-table solution, you're thinking of a design something like this: +-----------+------------+-----------+-----------+-----------+-----------+-----------+ | device ID | date | ...


-2

You have some problems in your code in mysql. You can't use column manes with spaces, at least not without backticks like you see in the table departments. The referenced columns need an index, your table employee has a primary key, but because it is a double column you can not use it. in emploee and department your code also fell short in a parenthesis ...


0

From MySQL documentation: MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically ...


1

You use a subselect query as basis for t1 and t2 Like SELECT t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1) FROM (SELECT * FROM TagMap WHERE TagID IN (SELECT TagID FROM Tags WHERE Articles > 50)) AS t1 JOIN (SELECT * ...


1

In SQL Server, I would store this as an int, and use CRYPT_GEN_RANDOM(4) to generate cryptographically random numbers for each row. Here's an example: DECLARE @c int; DECLARE @c_base64 varchar(8); DECLARE @c_converted int; --get the cryptographically random integer SELECT @c = CONVERT(int, crypt_gen_random(4)); --convert it into a base-64 string DECLARE ...


0

Remove these for a start, they are almost certainly counterproductive: table_open_cache = 256 join_buffer_size = 1M thread_concurrency = 16 There is also a conspicuous absence of innodb_buffer_pool_size being set. Configuration isn't going to be the root cause of your problem, though - you need to identify what queries are eating all of your CPU and ...


0

I posted it in the MySQL bug database and got an answer: Adding log-error = /var/log/mysql/mysqld.log in the [mysqld] section in the file my.cnf creates mysql.log in the directory I want to have. However, I still don't understand why it remains empty when the above setting isn't mentioned.


0

Testing out $tompave's Comment; it seems to work: First, create and populate: mysql> CREATE TABLE test_uuid ( -> my_uuid BINARY(16) -> ); Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO test_uuid (my_uuid) VALUES (UUID_TO_BIN(UUID())); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test_uuid (...


0

Assuming that (date, lead_id) is defined as UNIQUE in each table you may use SELECT date, lead_in, SUM(quote), SUM(sale), SUM(welcome) FROM ( SELECT date, lead_in, quote, 0 AS sale, 0 AS welcome FROM quotes UNION ALL SELECT date, lead_in, 0, sale, 0 FROM sales UNION ALL SELECT date, lead_in, 0, 0, welcome FROM welcomes ) totally ...


1

SELECT t1.run_name, hardware_used, COUNT(*) times_used FROM hardware t1 JOIN hardware t2 USING (hardware_used) WHERE t1.run_name >= t2.run_name GROUP BY t1.run_name, hardware_used ORDER BY t1.run_name; fiddle


0

Try gathering the updated statistics with analyze table tasks; and see if show table status reflects the value from select count(*) from tasks. The select count(*) should always return the exact value while table statistics (shown in show table status) might not be up to date.


1

For a closer analysis we would need the table definitions for both tables and indexes as well as the row counts, but I think the problem is that you use key2 for joining and mysql can then not use it for filtering of the datetime (since the first part of the index, the key2, is different for the rows in the join result). Can you try to create an index on ...


0

if you using specific table name you can use query like this SELECT table_name FROM information_schema.tables WHERE table_name in ('TABLES','SCHEMATA') AND TABLE_SCHEMA = 'INFORMATION_SCHEMA'


0

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' AND TABLE_NAME IN ('TABLES', 'SCHEMATA'); or, for example (6 tables will be shown) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' AND ( TABLE_NAME LIKE 'TABLE%' OR TABLE_NAME LIKE 'SCHEMA%' );


0

If you have an INDEX on a column (or an INDEX starting with that column), then this expression is very fast: EXISTS ( SELECT 1 FROM tbl WHERE col IS NOT NULL ) Depending on what else you do with the million rows, it may be better to have a News table, a Dot table, etc.


1

The syntax for CAST() is CAST(expr AS type [ARRAY]). You got the syntax right but you were unable to do the cast because TINYINT is not among the list of permitted values for 'type' in MySQL. Casting to INT works in MariaDB but it doesn't in MySQL. MySQL only allow any one of the following to be used as value for 'type': - BINARY. - CHAR - DATE - DATETIME - ...


0

You will insert by chunks, using mysqlsh util.importTable. What is your version of MySQL? Your file is delimited by |,"? what is value for your variables: innodb_buffer_pool_size innodb_buffer_pool_instances innodb_write_io_threads innodb_reads_io_threads


0

Finally was the auto commit as mustaccio told me in a comment: Looks like your "sleep connections" are not committing their transactions and thus keeping locks. Revise your application logic.


1

What you want is called an UPDATE JOIN First you must create a query that has the MAX(b_foo) for every a_uid: SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid; Making this a subquery, you can perform the UPDATE JOIN as follows: UPDATE A INNER JOIN (SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C ON A.id = C.a_uid SET A.foo = C.max_b_foo;...


0

Whenever your see mysql> ' '> the way to escape this cleanly is to type a single quote followed by \c and hit <enter>: '>'\c mysql> Then, you can type SQL commands from scratch.


1

How about this : select t1.key from table1 t1 inner join table1 t2 on t1.key = t2.key || 'P' where t1.key like '%P' order by t1.key ; Querying t1 gives you a list of all of the rows with keys that end in 'P'. The join to t2 is based on rows where t2's key, plus a trailing 'P', matches the key in t1 (or, alternatively, the original key ...


1

I notice that when I accidentally put ' in it ... the console seems to stuck there It's not "stuck" at all. It's patiently waiting for you to supply the closing quote for the string (or date) literal that you started when you typed in the opening quote! It even goes as far as to give you a "hint" that it's doing so - notice the different prompt it ...


0

SELECT id, MAX(CASE WHEN `day` = 1 THEN item END) day_1, MAX(CASE WHEN `day` = 2 THEN item END) day_2, MAX(CASE WHEN `day` = 3 THEN item END) day_3, MAX(CASE WHEN `day` = 4 THEN item END) day_4 FROM source_table GROUP BY id


2

Note: Try to install MySQL same version, if your crashed MySQL version was 5.6, please install MySQL 5.6. (brew install mysql@5.6). If following process fails try the same things with MariaDB. (In my case, by MariaDB, I get back all database) Process 1 Before start, must stop MySQL or MariaDB. Use this command (brew services stop mysql), For MariaDB brew ...


0

Your description looks messy. Expression from your SELECT statement will be evaluated for each row in received result set. I didn't understand why do you need to join boxscores table. Actually your original query should return more than one row. If you want to find the number of yellowcards for team_id = 2 you can use such query: SELECT SUM(IF(e.hometeam_id ...


0

Thanks to Akina's comment using CONCAT in the where clause worked. WHERE key_ LIKE CONCAT('sometext_', _TAG, '_sometext_', _ITEM_NAME)


3

SELECT t1.TagID AS TagID1 ,t2.TagID AS TagID2 ,COUNT(1) FROM TagMap AS t1 JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID GROUP BY t1.TagID, t2.TagID UPDATED: index on ArticleID may help to improve query performance


0

This worked for me: Clear the mysql data directory at /var/lib/mysql assuming that you have backups or you don't really need that data anymore. Then sudo service mysqld start


0

Partitioning rarely helps with performance. http://mysql.rjweb.org/doc.php/partitionmaint Adding an index to a partitioned table probably takes longer than to the equivalent non-partitioned table -- it will block access throughout the action. There have been several important changes to Oracle's MySQL; Aurora may not have picked them up yet. (I don't ...


0

I guess I would argue that it did "a right thing". Note: You have two UNIQUE keys. On the surface, IODKU should check both keys in order to find the up to two rows to modify. However, since IODKU is "either update (first priority) or insert (if row does not exist)" then let's consider this algorithm: Check all UNIQUE keys other than AUTO_INCREMENT. ...


3

It seems you need in simple SELECT *, SUM(debit_balance - credit_balance) OVER (PARTITION BY account_no ORDER BY id) balance FROM acc_account_transactions fiddle


1

Your query was only missing the ability to reset @running_balance. You can only reset @running_balance when the row has a different account_no from the previous row. With this in mind, I give you (drum roll please) ... PROPOSED QUERY SELECT account_no,debits,credits,balance FROM (SELECT account_no ,COALESCE(debit_balance) as debits ,COALESCE(...


0

Try this: /* WITH T (LOCATORCODE, COUNTRYCODE, SERVICEENDDATE) AS ( VALUES ('003', '067', '12/31/9999') , ('003', '082', '07/31/2013') , ('011', '017', '05/31/2020') , ('011', '034', '05/31/2020') ) */ SELECT * FROM ( SELECT T.*, MAX(CASE SERVICEENDDATE WHEN '12/31/9999' THEN 1 ELSE 0 END) OVER (PARTITION BY LOCATORCODE) IS_VALID FROM T ) WHERE IS_VALID =...


1

1 IN (a,b) won't scale. It will do a full table scan. This will make use of indexes: ( SELECT id, receiver_id FROM messages WHERE sender_id = 1 LIMIT 3 ) UNION ALL ( SELECT id, sender_id FROM messages WHERE receiver_id = 1 LIMIT 3 ) LIMIT 3 together with both INDEX(sender_id, receiver_id, id), INDEX(receiver_id, sender_id, id) Now, assuming you ...


0

SELECT locatorCode FROM source_table GROUP BY locatorCode HAVING NOT SUM(serviceEndDate = '9999-12-31')


0

AWS limits the IOP size to a maximum of 16KB, so whatever IOPS you buy, you will get throughput capped at 16KB times that number. So if you buy 1,000 IOPS on EBS, this will max out at 16MB/s. If your application does random I/O using smaller blocks that aren't adjacent, e.g. 8KB, you will get maximum throughout of 8MB/s from the same EBS volume. ...


1

WITH cte1 AS ( SELECT UDF1(0, a, b, c, d) AS Foo1, UDF1(1, a, b, c, d) AS Foo2, UDF1(2, a, b, c, d) AS Foo3, UDF1(3, a, b, c, d) AS Foo4, x, y, z FROM MyTable ), cte2 AS ( SELECT Foo1, Foo2, Foo3, Foo4, x, y, z, UDF2(Foo1, Foo2, Foo3, Foo4) AS Foo5, ...


0

I can give you a more concrete example as to what was actually deprecated. Seven years ago, I wrote the answer to Get the rank of a user in a score table I make use of variables to find out the rank of a score table and assign the same value for rank in the event that more than one user has the same score (i.e. in case there are ties): SET @rnk=0; SET @...


2

Short answer: It's 2020, the case for spinning rust is super-weak. Long answer: It depends on how much memory you have and what your read:write ratio is. If the workload is mostly reads, and you have enough buffer pool to cache all of the even remotely hot data, disk speed won't matter as much because once the buffer pool is warmed up, not much data will ...


1

Database schema and data CREATE TABLE `Students` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(32) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Students` (`ID`, `Name`) VALUES (1, 'Ashley'), (2, 'Samantha'), (3, 'Julia'), (4, 'Scarlet'); CREATE TABLE `Friends` ( `ID` int(10) unsigned NOT NULL ...


Top 50 recent answers are included