Tag Info

New answers tagged

0

It is not because there is an index that it is always used. The database decides if it useful to use the index. ThestoreTitle is 'only' used for ordering. Reading through an index need more disk reads. With a size of 282 characters you will not have many keys in a block. Perhaps the number of rows that are returned are not enough for MySQL to use the index.


0

I just fixed the problem: apt-get remove php5-mysql apt-get install php5-mysqlnd I had to reinstall phpmyadmin, the errors disappeared now.


0

I think your PM meant this CREATE VIEW `debit_credit_view` AS SELECT ledger_head_id, SUM(debit) as debit, SUM(credit) as credit FROM debit_credit GROUP BY ledger_head_id


0

If your password has a # in it then you need to surround your password with quotes. If your password is #53wdf@##$454 $ mysql_config_editor set --user=<user> --password --host=<host> Enter password: "#53wdf@##$454"


1

You shouldn't put those lines at the very bottom of the file. Look for the [mysqld] group header in my.cnf and put those lines under it [mysqld] query_cache_type = 1 query_cache_size = 4096M query_cache_limit = 2M query_cache_strip_comments =1 Those line cannot go under any other group header. Then, restart mysqld.


0

When inserting utf8 strings, an invalid byte combination will cause truncation. You have two different functions; I assume one blindly left a D7 at then end, leading to truncation; and the other stripped it off?


-4

Here is the code if someone else get stuck: <?php $query = "SELECT SUM(quantity) FROM dabase123 WHERE program = 'windows xp'"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)){ echo $row['SUM(quantity)']; } ?>


0

You have 3 different instances of tables aliased as pm. Your "minor" change changed from using one instance to another.


1

I'm surprised that this query actually works. You're using the same aliases three times. You can access the outer tables in a subquery, you know? That's one of the reasons why aliases are actually there, to distinguish tables when used multiple times. First I would recommend, that you're using the join syntax introduced with the ANSI SQL standard from 1992 ...


0

Looks like in the name of alises you also seem to ignoring the join wp_postmeta as pm, wp_terms AS t! So my guess is non-existence of index is causing the delay, but you can confirm by posting explain plan and/or table definitions. SELECT DISTINCT pm.meta_value FROM wp_postmeta as pm, wp_terms AS t WHERE meta_key='company' AND pm.meta_value=t.term_id ...


2

I try to make an hypothesis about the second question: Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction? Transactions are managed by Spring. So it would be possible that before running the select spring has raised a start ...


1

It's more readable if you use a derived table. No need for 4 SELECT clauses, just 2. Plus, it's proper SQL, not some fancy feature that MySQL or Access has added: SELECT InvoiceNumber, CalcField1, CalcField2, (CalcField1 - CalcField2) / CalcField2 AS Percentage FROM ( SELECT InvoiceNumber, Field1 + Field2 AS ...


1

Yes you can, simply add replicate-ignore-table in your my.cnf . please refer following link for mysql documentation : https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-ignore-table


2

You can create a temp table, insert your parameters there and then join it: create temporary table tmp (x int); insert into tmp(x) values (10027),(11535); select x.* from activities as x join tmp as y on y.x = x.actor_id join tmp as z on z.x = x.original_actor_id;


0

You have a choice of many tools - possibly including one that I wrote. First, check out this post on stackoverflow which has treated this subject in detail. Note that MySQL has its own tools which may be of more interest to you than the commercial ones? There's mysqldbcompare straight from the horse's mouth - it's part of the MySQL utilities collection. ...


1

Better yet: where orders.created >= '2015-07-01' and orders.created < '2015-07-01' + INTERVAL 1 MONTH; This works for DATE, DATETIME, DATETIME(6), leap year, etc. (Notice the <, not <=.)


1

in 5.6.12: (Well, not really an answer, but more info.) mysql> insert into se108833 (product_id,product_id_nn) values ('',''); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message ...


1

The output is only showing the current query in a transaction. What else is in the BEGIN...COMMIT? Or are you running autocommit=0? Or =1? SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction. INSERT grabs exclusive lock(s). It sounds like the SELECT already has a lock that the INSERT desires. Perhaps the SELECT ...


0

This is because the user does not have execute grant global, to fix it GRANT EXECUTE ON *.* TO user_name;


2

When you store a password you have to store encrypt(password,salt) and salt for this user. To check if input from user is the right password you comapre encrypt(input,salt) with the stored encrypted string. Chanck the section about 8.6.2 What Is Salt? in Practical UNIX & Internet Security, by Simson Garfinkel & Gene Spafford from O'Reilly.


0

NOTE : This is all reads and writes for all databases in the MySQL Instance You should create a temp database for collecting stats SET sql_log_bin = 0; DROP DATABASE IF EXISTS mystats; CREATE DATABASE mystats; Then, every hour run this SET sql_log_bin = 0; USE mystats DROP TABLE IF EXISTS rwstats; CREATE TABLE rwstats ( id INT NOT NULL ...


0

If you have binary logging enabled, you can analyse your binlogs to identify types and number of statements executed. mysqlbinlog your_binlog | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | ...


2

In your first query you do ...orders.created <= '2015-07-31'; which is actually orders.created <= '2015-07-31 00:00:00'; since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like orders.created <= '2015-07-31 23:59:59';


1

Ofcourse a physical files restore will be quicker than loading a dump. So, if you have full physical backup / snapshot of mysql data-directory - you can go fast! (Below answer assumes that you have physical backup available.) Tables are MyISAM: Stop mysql Copy database folder to mysql datadir Correct permissions Start mysql Tables ...


1

So, rephrasing your requirement a little, you want to delete every row whose pos has the value of OF and for which another row in the same table exists with the same plyrID, year and teamID, and with pos of RF, LF or CF. That sounded almost SQL, you would only need to correct some "grammar": DELETE FROM MyTable WHERE pos = 'OF' AND EXISTS ( ...


1

If the four digits you need to extract are either exactly at the end of the string or are followed only by spaces, it would probably be easiest to extract them using the RIGHT() function after removing the trailing spaces using the RTRIM() function: SELECT RIGHT(RTRIM(YourColumnName), 4) AS SomeCode FROM YourTableName WHERE ... ;


0

It is a socket file used for inter-process communication, you have to stop the local MySQL server and make sure the mysql.sock doesn't exist. Then you have to re-create the socket file to send the connection to the correct destination (in your case, it is Amazon RDS). There are many ways to create a socket file, for example you can use socat, and the ...


0

Seconds_Behind_Master is the difference between the timestamps a statement was most recently downloaded by the IO_THREAD and when it was most recently executed. I'd highly recommend pt-heartbeat. pt-heartbeat is a two-part MySQL and PostgreSQL replication delay monitoring system that measures delay by looking at actual replicated data. This avoids ...


0

Just delete ib_logfileX files and restart mysql service. You changed the innodb-log-file-size var to size more bigger than already set it in your mysql daemon.


1

When this DML be written to iblog files ? -- "as needed" during the hour When the DML be written to binary log files ? -- Never. Information for the binlog is gathered, but not written until COMMIT. Will the related data pages on disc be modified during 10:00 AM to 11:00 AM -- There may be modifications to the pages. They will be invisible to the user. ...


2

You can use the technique described in: http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/ to mimic: row_number() over (partition by ... order by ...) In your case that would be something like: SELECT user_id, comment, row_number FROM ( SELECT @row_number:=CASE WHEN @user_id=user_id ...


1

Go to folder C:\wamp\bin\mysql\MYSQL_VERSION\bin Hit shift + right-click in that folder and select "Open command window here" from the menu that pops up. This will open a command window in the path you are already in, so C:\wamp\bin\mysql\MYSQL_VERSION\bin Enter any of the MySQL commands you like with any of the executables you find in that folder. To ...


0

Go to folder C:\wamp\bin\mysql\MYSQL_VERSION\bin Hit shift + right-click in that folder and select "Open command window here" from the menu that pops up. This will open a command window in the path you are already in, so C:\wamp\bin\mysql\MYSQL_VERSION\bin Enter any of the MySQL commands you like with any of the executables you find in that folder. For ...


1

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


0

You can also use options like symmetricDS which can help in synchronizing two databases. With this you will be able to select the tables which needs to be synchronized that way you can save internet bandwidth. This would be also suit in the scenario when there is lack of connectivity between two locations.


0

If you want to DELETE all the information in table1.*: DELETE table1.* FROM test.table1 JOIN test.table2 ON (table2.id = table1.id) WHERE table1.price < (table2.price*2); This will DELETE all rows from table1 that match with the WHERE criteria. If you want to DELETE all the rows that match against the WHERE criteria in table2, just replace DELETE ...


1

I have seen this since about 2002. Ignore it; it will go away. I have failed to find out what causes it, and it so elusive that I could not get anyone to look at as a "bug". I do not know if this will have any impact on innobackupex.


-1

Maybe you look for LPAD and CONCAT: mysql> SELECT * FROM test.tbl; +---+---+---+ | U | C | R | +---+---+---+ | 1 | 1 | 4 | | 1 | 2 | 1 | | 1 | 2 | 2 | | 1 | 2 | 3 | | 1 | 2 | 5 | +---+---+---+ 5 rows in set (0.00 sec) mysql> SELECT -> U, -> CONCAT(C,LPAD(R,2,0)) AS C, -> R -> FROM test.tbl; +---+-----+---+ | U | C ...


1

With MyISAM, you can get an AUTO_INCREMENT that starts over with each new value. CREATE TABLE foo ( C INT UNSIGNED NOT NULL, N INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(C,N) ) ENGINE=MyISAM; INSERT INTO foo (C) SELECT C FROM your_table; Then... SELECT 100*C + N FROM foo gives you the desired numbers. (If you need U and R, add ...


0

It used to be that a high transaction rate in InnoDB would stall periodically. The TPS would drop to zero for a few seconds, and there was nothing that anyone could do about it. Percona came along, figured out what was causing it and put out a version of MySQL that fixed the problem. (It had something to do with the dirty pages in the buffer_pool getting ...


0

RAID-5 controller with a battery-backed write cache is excellent. Writes appear (to MySQL) to be instantaneous. When comparing RAID-5 to -10, you need to decide whether you are comparing N drives to N drives or comparing M GB to M GB. N drives: RAID-10 scatters its reads across N drives; So does RAID-5. Writes hit 2 drives in either case, but the ...


0

If the GROUP BY necessary? Try removing it; that will eliminate a significant performance hit. (Or it will give you far more rows than you expect.) In t, this should help: INDEX(published, zip) -- in that order Since both of the 'columns' in ORDER BY priority DESC, rreviews DESC are computed, there is no way to avoid finding all the possible rows, then ...


0

Try ::1 and localhost instead of 127.0.0.1.


1

I think NOW() is what should be stored into a TIMESTAMP. As it is stored, the time is modified according to the timezone, and UTC is stored in the table. Upon retrieving, the reverse modification is performed. (This, of course, assumes the system has the 'correct' timezone established. See SHOW VARIABLES LIKE '%zone%';.) That way, if you have users ...


0

At 250/hour, all possible techniques are plenty fast. Anyway, I will throw my 2 cents in... Multi-row INSERT of 100 rows will run 10 times as fast as 100 individual INSERTs. Multi-row INSERT may lead to gaps in AUTO_INCREMENTs. In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use. (REPLACE them all dups ...


0

Saying "Sending data" merely points out how useless the Profiler often is. This index on a might change the EXPLAIN (eg, start with a instead of b) and speed up things: INDEX(`type`, `deleted`) -- in either order If you need further discussion, please provide SHOW CREATE TABLE and some clues of about how big each table is. Why is b used? Would you get ...


0

Stare at this: / ( SUM(ua_expenses_by_school.Amount_USD) * 100 , 1 + SUM(ua_money_allocation.Value_USD) )


0

BOTTOM OF THE BUG REPORT YOU REFERENCED [21 Jan 16:44] David Moss Thanks for your feedback. This was fixed in version 5.7.3, and the following text was added to the 5.7.3 release notes: Previous versions of mysqlbinlog did not correctly accept the ssl-ca option in an option file. This fix ensures that this option can be correctly used. In earlier ...


4

That SQL dialect is from Oracle but this answer will be for MySQL. If datum is DATE select count (*),datum date from tx_feeder where datum between (CURDATE() - INTERVAL 31 DAY) and (CURDATE() - INTERVAL 1 DAY) group by datum; If datum is DATETIME or TIMESTAMP select count (*),DATE(datum) date from tx_feeder where datum between (CURDATE() - INTERVAL 31 ...


2

XtraBackup logs the most recent LSN not only in xtrabackup_info which you'll have to extract, but to STDERR, too: 150731 16:33:28 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 150731 16:33:28 innobackupex: Waiting for log copying to finish xtrabackup: The latest check point (for incremental): '1948062848' xtrabackup: Stopping log ...



Top 50 recent answers are included