New answers tagged

0

See if this meets your specs: SELECT SUM(turned_on), SUM(turned_off) FROM ( SELECT ( @prev <= 5 AND EngineOil_P > 5 ) turned_on, ( @prev >= 15 AND EngineOil_P < 15 ) turned_off, @prev := EngineOil_P FROM ( SELECT @prev := 0 ) init JOIN tbl ORDER BY ...


0

Have two instances of MySQL on that one server. Each has its own data directory, its own port (3307 is typically used for the second one), each has its own my.cnf and logs. But, since they share the RAM, you need to cut back many tunables relating to RAM. Why? What do you gain? Certainly not HA. You may be able to better use lots of Cores. But you are ...


1

If I understand your intent, I might write the query something like this: SET @City = 'London,Lyon,Kln'; SET @sql = concat('SELECT * FROM Invoices WHERE City IN (',@City,')'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


0

max_connections and ulimit and SSDs have virtually no relationship to each other. ulimit is an upper bound (unnecessarily low at 1024) on the number of files that can be open in any 'process'. (mysqld is a 'process'.) It does relate to table_open_cache, which is now set dynamically based on ulimit. (In older versions, it was not automatically set.) ...


0

Query 1: INDEX(person_type, person_id) -- in that order INDEX(person_type, person_id, full_name) -- to be "covering" Query 2 ("covering" is not practical because of *): INDEX(person_type, full_name) -- in that order Query 3: Before making suggestions here, please explain why you have a LIMIT without an ORDER BY. See also my cookbook on making ...


0

I believe the key here lies in comparing the previous for every new row with the current value. I don't know MySQL syntax but perhaps this pseudo code helps convey the logic I'm thinking of: if (previous_value < 15 and current_value >= 15) { set start_count += 1 } if (previous_value > 5 and current_Value <= 5) { set stop_count += 1 } I'm not sure I ...


1

Check and log/dump the insert/update queries - this looks to me as if the php code somehow recodes your data. The 00f1 is the hexadecimal representation of the character ñ and in the form \u00f1 it might be used as an special/escape sequence (for example in JSON).


-1

You have to change fields/tables encoding to utf8_spanish_ci or utf8_spanish2_ci so you can save spanish characters.


1

I can only speculate what the people meant. Maybe it's related to how MySQL computes index statistics depending on n. The only case that comes to my mind is whether n is higher than 127 or not. As you may know InnoDB stores a string with its length. The length is stored in so called offset fields. The offset can be either one or two bytes. In REDUNDANT ...


1

Dump output is raw SQL statements, numbers and everything else is represented as text so the size is in no way relevant (one int is 4 bytes, but as text 1 is probably 1 byte and maximal int value (signed) = 2147483647 is 10 bytes and minimal int value = -2147483648 is 11 bytes). I suppose those are all ibdataX, *.ibd, *.myi, *.myd, ...


0

TL;DR -- explain how db's work Long version: You can't show that removing a FK can not cause corruption, because that is not actually true -- theoretically it could. What you can show, is that it doesn't cause corruption and explain why the chance of it causing corruption is more theoretical than actual. I would start with why the chance of it actually ...


0

If the SSD you are talking is "Solid State Drive", it will only act as an ordinary drive with faster read and write. That is the reason why servers are being migrated to SSD drives for critical parts of it. Increasing the max_connection will also increase CPU and Memory Usage. I won't recommend a limit of 3000. About the issue with kernel limit of open ...


-1

You cannot block or remove the root user. This is the administrator user for your MySQL. The best way is to password protect the user Root and allow it to login using the local computer (127.0.0.1) Some of the tables can be a temporary table which will be generated on the fly and deleted on a system specified rule.


-1

Please see the following documentation for why the auto_increment_offset should not be set as described above: http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_increment 0 -> 1 1 -> 1 2 -> 2


2

InnoDB will only lock the row. But it will eventually have to update all the indexes; however this is mostly deferred. Was something else going on while the INSERT was taking 36 seconds? Perhaps a SELECT ... FOR UPDATE? Or even a SELECT can block the INSERT in certain circumstances. Next time it happens, quickly do SHOW FULL PROCESSLIST to see what else ...


1

Let's think out of the box. Instead of having a "snapshot", let's have a "log". What you currently have is "current" state of things; adding a "log" would provide the "history", from which could be derived the 'lost' info. One way to implement the log is to have a TRIGGER on INSERT or UPDATE of the table, and have the trigger write to the log file. This ...


2

Here's one approach: create table users ( username ... not null primary key , <additional attributes> ) engine = innodb; create table rooms ( room_name ... not null primary key , creator ... not null , <additional attributes> , constraint ... foreign key (creator) references users (username) ) engine = innodb; create table conversations ( ...


1

I don't know PHP or MySql but the answer is definitely not creating new tables for each room. I'd have one table for rooms and one for messages and have the messages refer to the rooms (via foreign key). You probably want to consider archiving and/or purging as the data will probably grow quickly. Look into MySql's ability to partition tables and if there ...


1

That slave info is in RAM. If you are running MySQL 5.6, run RESET SLAVE ALL; If you are running MySQL 5.5, restart MySQL If you are running MySQL 5.0/5.1, run the following: STOP SLAVE; CHANGE MASTER TO master_host=''; These are the ways to make the SHOW SLAVE STATUS\G disappear from view GIVE IT A TRY !!!


-1

Maybe you could filter the start_date with MAX or MIN and group by member, group, status, and start_date


1

This is not going to be efficient but I doubt there's any really efficient way to perfrom this in MySQL: select t.* from temp1 as t where not ( t.status <=> ( select p.status from temp1 as p where p.member_id = t.member_id and p.groupie_id = t.groupie_id and p.start_date < ...


2

This works on consecutive Start_date for each partition (on Member): SELECT `Member`, `Group`, `Status`, `Start_date`, `End Date` FROM ( SELECT @row := CASE WHEN @status=Status AND @member=Member AND @group = `Group` THEN @row + 1 ELSE 1 END as row , @member:=Member as Member , @group:=`Group` as `Group` , @status:=Status ...


2

Probably refers to "fresh installation". The workaround is painful... Dump all the data. Stop mysqld. Blow away ibdata1 and iblogs. Change the setting in my.cnf. Restart. (This should feel like a 'fresh' install.) Reload the data. Do you have some indication that changing that setting will help you significantly? If so, please explain. It is a very ...


1

When dumping, use the option to not write the CREATE DATABASE statement. When reloading, specify a new, empty, database on the mysql command. That will put the imported data in a different database. You can then compare them, or do whatever. However, if you plan to move them into the old db name, you would have to do DROP TABLE + RENAME TABLE one table ...


0

If you use BEFORE INSERT instead of AFTER INSERT in the update_referral trigger, you might be able to avoid the loop. You could directly update chezmoi_user_details inside update_referral instructions, all in one trigger. You can find explanations here: http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html


-1

Solution for PostgreSQL: We can avoid using rules - Stop slave daemons - Create ON DELETE and ON UPDATE DO INSTEAD NOTHING CREATE RULE void_delete AS ON DELETE TO reptest DO INSTEAD NOTHING - rule Apply RULE on table ALTER TABLE reptest ENABLE REPLICA RULE void_delete; Now, you can notice below that DELETE has no impact on Slave Node ...


0

This sound like a (probably) badly written wordpress plugin. If you have installed some plugins, especially if these are not rated or newest release, try to deactivate it one by one and do a test for each deactivation (if you want first backup the database and httpd wordpress dir, but normally on the deactivation process this is not required) Also check ...


1

The correctness of a database design is specific to the requirements that you gather prior to building it. In simple terms, one size does not fit all and in many situations you will find more than one solution to your problem. The skill is in determining which solution best fits the requirements that you have. There will be occasions when the solution you ...


1

Try the below: Add below 2 lines in my.cnf skip-grant-tables skip-networking Restart mysql service service mysqld restart Note: if you don't have a service, you can do from /etc/init.d/ Connect mysql: mysql Update root password: UPDATE mysql.user SET PASSWORD=PASSWORD('MyNewPass') WHERE USER='root'; flush privileges"; exit; Remove newly added ...


1

MyISAM! Shame. Switch to InnoDB. Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc. But do make sure it is the 'appropriate' character set: VARCHAR(21) CHARACTER SET ascii If it is case sensitive, then add on COLLATE ascii_bin. Yes, there is a memory and disk penalty. But there are tradeoffs with speed, simplicity, etc. ...


0

SHOW VARIABLES LIKE 'tmpdir'; -- You could change that (and restart) to immediately workaround the problem. But that is not a long-term solution. Complex queries with inadequate indexes are likely to be the cause. Set long_query_time=1 and turn on the slowlog; wait a day; run pt-query-digest or mysqldumpslow -s t to see the 'worst' queries. Focus on ...


0

Use a GROUP_CONCAT to make them the same line and searchable. Say you have a three way table. that has products, categories and a link table. First show all products with their categories as one table: SELECT * FROM products LEFT JOIN category_relations ON products.id = category_relations.prod_id LEFT JOIN categories ON category_relations.cat_id = ...


1

So, you want to import the new data without overwriting and no duplicate keys, right? I'm no expert, but as I see it, you can either: Remove the current data and import the whole old DB, Use --ignore or --replace on mysqlimport - either won't duplicate rows (they'll either --replace existing rows or --ignore duplicates), mysqldump without creating the ...


0

With mysql 5.7 you can consider moving your undo logs to separate files: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_logs This will also give you the option to truncate those logs when you like: https://dev.mysql.com/doc/refman/5.7/en/truncate-undo-tablespace.html In my.cnf this setting should do the trick: ...


0

There are many restrictions on PARTITIONs. But also, there are many reasons why they don't provide any benefit. What do you hope to gain? HASH partitioning does not provide any performance benefit, unless you are doing "table scans" now. Vérace's suggestion is a way that would pass the syntax issues, but, again, not likely to have performance benefit. ...


0

GROUP BY Name HAVING COUNT(*) >= 2 You may need to do something like this: WHERE ... AND ( SELECT COUNT(*) FROM tbl WHERE Name = ... GROUP BY Name HAVING COUNT(*) >= 2 ) For checking for exactly a certain list: WHERE Category IN ('clothes', 'hats') GROUP BY Name, Category ...


-3

I'm so surprised no one just posted 'mysql -v' as an option. If it gets stuck, the output will stop.


0

There seems to be an anomaly or bug when a timestamp column has a default value of CURRENT_TIMESTAMP() - with the parenthesis. 5.7 documentation says it is synonymous with CURRENT_TIMESTAMP but workbench must disagree - sort of. Changing the definition in workbench by removing the parenthesis provides a message that nothing changed. However changing to ...


0

From here, below is an example that range partitions by YEAR - change as appropriate. Here is the part of the manual that explains how to alter tables and add partitions. You should be able to take it from here. CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', ...


1

Do you want to remove the anon users? You can just use DROP USER to remove them. They shouldn't have any special privileges, as stated in the MySQL Docs and should not be a major problem as they're only in the localhost. If, even removing, you still can login or something, check out this other question.


5

This query: Uses ANSI JOIN (LEFT, INNER, ...) Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables Counts values for totals and counts ids for unique counts Query: SELECT s.ip , COUNT(DISTINCT s.id) , COUNT(DISTINCT ...


0

I have tried all thing you guys suggested. Thanks for that, it was useful but did not solve my problem. Indexes suggested by jkavalik helped a little bit, but the query was still too slow (45 seconds~). The problem was that there was 2 large tables - visits and calls. And I had to join them twice in the query. It was taking long time. After few days of ...


1

Seems you've found a bug in migration wizard, I follow your description and I was able to repeat issue on my machine. Can you please fill bug report at bugs.mysql.com? Appreciate. We'll do our best to fix that isse. As workaround you can try this way - use Data Export to export only structure of your database. Run exported stricpt on target server. Then run ...


1

The answer is simple... MySQL WorkBench! Enables migrations from the following systems. SQL Server, Access, PostgreSQL, SyBase, etc... Manage migration projects Source & Target selection Object migration Version upgrades Here's the link... http://www.mysql.com/products/workbench/migrate/ Price is good too! ;-) -Tony


1

You are working too hard. Plan A -- RENAME TABLE Assuming the production database is really readonly, you don't need FKs, just the indexes they generate. (And you may not need all of them.) So, before testing in Staging, turn off FK checks and drop all the FKs. Then consider dropping any excess indexes. Then test. Since there is no RENAME DATABASE, ...


2

If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY. I specifically suggest that order because I assume this query is frequently used? SELECT COUNT(*) FROM tbl WHERE post_id = ? AND visible; By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing ...


0

Ok, after some research, here's the answers I found. Q1: Is it possible to detect the user that executed the query in MySQL? It is possible to detect with which user by using: select user(); http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user Beware: In triggers and events current_user() returns the user who defined the object ...


2

You should consider the balance between how often your data will be accessed vs how often your data will be updated. Adding another condition on your SELECT just to display your content may well create more load than deleting rows in the infrequent occasion that a user un-likes. Note that you will need an index on visible to efficiently query against it.


0

Firstly, I would not set an end date on an active record as this will help you locate it when querying. If you need to return a date to the client then you can use CURDATE() in your SELECT statement. As you need to UPDATE the previous record and then INSERT a new record (or the other way around whichever you prefer) this should be done in a single unit of ...


-2

@JulienVavasseur is correct in that you need to format your queries using Ansi92 join syntax. If nothing else, it will make it easier for us to read and help you with your questions. However, looking at the problem, it seems as easy as adding a Distinct to the count of session.id to reduce it from 7882 to 752. When you ask for a count, you're going to get ...



Top 50 recent answers are included