Tag Info

New answers tagged

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

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

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

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

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 ...


0

You can execute the following command to check if my.cnf is used or note by executing the following command ps -ef |grep mysql or strace ./mysqld the first command (ps -ef |grep mysql) will show you the active mysql service ID and the default cnf file location, the other one will show you only the location of the cnf file. As for the location my the cnf ...


0

As your my.cnf is not on standard location, check ps -ef | grep mysql for --defaults-file option. Below command will give you default file options: mysqld --verbose --help | grep -A 1 "Default options" (There are many similar questions previously answered.)


1

This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions. This has been explained well in documentation: When you create a stored function, you must declare either that it is deterministic or that it does not modify data. ...


0

When you have disable innodb, dont' forget to remove all innodb-related tables from Dbs. For example "mysql" database has 5 of them: root@ustimen.co$ mysql mysql -e 'show table status' | grep Unknown | awk '{print $1}' innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info


0

I believe I have found the solution, and that is to convert the Procedure to a Function. DELIMITER $$ CREATE FUNCTION fn_get_item_type_id( item_name varchar(45) ) RETURNS INT DETERMINISTIC RETURN (SELECT CASE WHEN LEFT(`item_name`,6) = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = ...


0

For some reason, one of the views is lost. You should re-install common_schema. Or maybe you don't have all proper access rights - in that case, just check your rights.


1

Perhaps you should use --single-transaction mysqldump --single-transaction -u root -p common_schema > common_schema_bkup.sql Why ? According to the MySQL Documentation on mysqldump, you need the following rights: mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK ...


0

The only way I have found to do this so far is by using: On the Master B: SET SQL_LOG_BIN=0; ALTER DEFINER=user@localhost EVENT e_test ENABLE; SET SQL_LOG_BIN=1; This works, but as soon as I alter the Event on Master A, Master B goes back to Slaveside_disabled and I have to repeat this process. So not perfect by any means, as it relies on us remembering ...


1

I used JOIN and LEFT JOIN to modified your query: SELECT u.*, p.*, f.approved FROM test.default_user AS u JOIN test.default_profile AS p ON (u.id>1 AND p.user_id = u.id) LEFT JOIN test.default_friend AS f ON ((f.user_id = u.id AND f.friend_id = 1) OR (f.friend_id = u.id AND f.user_id = 1)) WHERE u.email LIKE '%some_string%' OR u.username ...


3

oNare's answer shows the principle way to achieve this. (He gets a +1 !!!) He set the variable @names as follows: SET @names="'oNare','Another'"; Then, his algorithm worked perfectly. SUGGESTION Instead of putting single quotes in @names, do it without the single quotes. Do the delimiting before processing the query with this SET ...


3

You can't pass a User-defined variable (UDF) through a IN clause, but you could make a Dynamic SQL Statement like this: Example table: CREATE TABLE user ( id int(11) NOT NULL, fName varchar(45) DEFAULT NULL, lName varchar(45) DEFAULT NULL, pwd varchar(45) DEFAULT NULL, rol varchar(45) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT ...


0

Depending on the hardware you're moving from/to (or not moving), you could just take the data & config files, and copy them across to the new system. If the new system is set up the same for storage/memory, this might just work, but may need some tweaking of config to make it happen. If you're changing to hardware where you can't just copy the files, ...


1

That error is nonsequiter with regards to MySQL. This would especially be true if your Windows server crashed for reasons other than MySQL. There are ways to fix IRQL_NOT_LESS_OR_EQUAL. However, if you are reloading a mysqldump and it is the only reason Windows crashes, you may have to accommodate Windows until you fix the IRQL_NOT_LESS_OR_EQUAL error by ...


0

You could load the mysql schema into the 5.6 instance and run mysql_upgrade. I have a much cleaner method in mind: You could dump the grants as pure SQL from the MySQL 5.5. instance and then run it in the MySQL 5.6 instance. You can do one of the following: Extract the grants as SQL using pt-show-grants. Run my personal emulation of pt-show-grants: ...


0

The EXPLAIN took so long because it evaluated the subquery. Perhaps the subquery took so long because of lack of INDEX(field, id). When deleting a large chunk of a table, it is often faster to copy everything that you want to keep into a new table, then use RENAME to swap tables. Or, you could do the deletes in chunks of 100-1000, walking through the ...


0

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field. I have a much better method. DROP TABLE IF EXISTS keys_to_keep; CREATE TABLE keys_to_keep ( id INT NOT NULL, PRIMARY KEY (id) ); INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field; CREATE TABLE mytable_new LIKE ...


1

(!!! Make a backup first before trying the following !!!) Step 1 : Setting in your ini file: [mysqld] innodb_force_recovery = 1 (Change your mysql port from 3306 to something else if you don't want clients to reconnect during your recovery) https://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html Step 2: Increase the number carefully until ...


1

It appears the OP is conflating how the data should be stored in the database with how the data will be displayed and/or entered when taking classroom attendance. These are not necessarily the same thing. Display should not drive storage. The UI could display the data the way the OP shows above in a classroom attendance form and have a middle layer that ...


4

What catches my eye is the Query recorded in the general log 150629 9:17:14 76946 Query UPDATE `api_dictionaryitem` SET `name` = '', `group_id` = 4, `count` = 2248844, `last_occurence` = NULL WHERE `api_dictionaryitem`.`id` = 8 when you got signal 11 09:17:14 UTC - mysqld got signal 11 ; I would suspect you hit some weird bug when running the ...



Top 50 recent answers are included