Tag Info

Hot answers tagged

10

Ok, found about this in the documentation itself. This was a change introduced in mysql 4.1 so that the earlier password lengths of 16 characters and newer password lengths of 40 characters could be simultaneously supported. The Password column was made 41 bytes (chars) long, and the newer passwords would begin with a mandatory * to identify them. From the ...


4

In addition to the password starting with an asterisk, here is algorithm for PASSWORD() SET @plaintextpassword = 'whatever password you want'; SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@plaintextpassword))))); EXAMPLE mysql> SET @plaintextpassword = 'whatever password you want'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT ...


3

Background info... The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks. This leads to an unreadable block for InnoDB. The double-write buffer, and its extra write, makes it possible to recover from a torn page. The hardware needs to ...


3

Ignore the "fragmented" alarm; it's bogus. Ignore the max "possible" memory; it's bogus. Focus on the slow queries; they are causing the high CPU. What are the slow queries? Plan A: Glance at SHOW FULL PROCESSLIST; frequently. You will see one or two queries there most of the time. Plan B: Turn on the slowlog; set long_query_time = 2; and (after a ...


3

This was a fun exercise :) I decided to throw this problem on it's head and instead ask, which days are not available first then get which days are available. You will need a "days" table that list all days. There are a lot of scripts out there to find how to do this dynamically. test is your table in the question and days is this days table. First, get just ...


3

Like many things, the answer to this question is "it depends". In this case, what it depends on is how many transactions each product will get. With low to moderate volumes of transactions, it will be very fast to compute the running total on the fly and you don't have to write lots of code to compute, store and maintain the totals. Index your table! ...


2

As it appears on the manual, use the option --difftype=sql in order to obtain the results as ALTER TABLEs. You have an example on a recent MySQL Performance Blog post: $ mysqldiff --force --difftype=sql \ --server1=root:msandbox@127.0.0.1:21489 \ --server2=root:msandbox@127.0.0.1:21490 \ employees:employees


2

Something like this would give you the total across all three of those days: SELECT COUNT(attendance.AttendanceID) as 'Total Attendance' FROM student INNER JOIN attendance ON attendance.StudentID = student.StudentID AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015') WHERE student.WorkshopID = '101' If you wanted the total separately for ...


2

Here's something to test out. You can't force the order of execution in an OR, but you can in a CASE, so you can short-circuit extra processing if you put the most common scenario at the top of each CASE. EDIT: Since you aren't using any fields from orders (just testing that the order isn't deleted or in need of review), I moved that part to an EXISTS in ...


2

Well, I tried a solution. It works but it is pretty ugly. But it works... SELECT count(*) FROM ( SELECT code, dates.selected_date FROM appartments INNER JOIN (select * from (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 ...


2

In general, this design will do the job without problems, specially that you only handle 400 rows per month, which is relatively small number. However, I have few recommendations to improve the design of the table: price is better to be decimal, unless you want to store the price in "cents". i.e. if the ticket price is $5.99. you either have to store it ...


2

There is essentially no difference between table and db.table. Once the tables (for SELECT or for INSERT) is opened, it is just a table, regardless of what database it is in.


1

Broadly speaking, on a MyISAM Table with a range scan, the process is: Find the first Index result using the BTREE (inside the .MYI file) and access the row result (on the .MYD file) - Handler_read_key Get the next result, using the index (and in the same order), until the value retrieved is larger than the one defined (multiple instances of ...


1

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1. Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko) You cannot recover just a database. You must restore the entire data ...


1

As always, a few minutes after posting a question, I found the answer myself. The issue was SELinux. It was set on enforced; permissive made it work instantly. Now I'll have to figure out a way to make it work with the enforced mode. The following appeared in the audit.log: type=AVC msg=audit(1429781752.260:6738): avc: denied { name_connect } for ...


1

Do a GROUP BY aggregation and see if the aggregated count is greater than 1 SELECT B.Author_id,A.Skill_id,COUNT(1) SkillEntryCount FROM tableB B INNER JOIN tableA A USING (Entry_id) GROUP BY B.Author_id,A.Skill_id HAVING COUNT(1) > 1; or collect all entry_id values for each (Author_id,Skill_id) and see it is has commas SET group_concat_max_len = ...


1

How about the SET datatype. With that, you could represent all combinations of 14 categories in 14 bits (2 bytes).


1

The commandline mysql allows \! to do what you want, but you appear to be in Windows, which does not really allow for such a feature. So, No, you can't do it. For reading INSERT statements (etc) back in there is the source command. Can you turn things inside out? You can get a list of databases, or otherwise create MySQL commands, from mysql ... -e ...


1

The MySQL SET datatype is a disaster - as are any type of array datatypes (supported unfortunately by many RDBMSs). Check out my answer to another question here. As I note, MySQLs SET is a breach of Codd's second rule - no repeating group datatypes. It is also completely non-portable should you wish to change RDBMS. If you wish to store that data this way ...


1

here's my observations. :) 1/ Your posts should only refer to a car_model. There is almost no performance issues to do an additional join given the fact that there won't be a load of car makers. More about that, you are running into an integrity problem : what if you have a problem with an update and found that your post is referring to an 'Audi A3' but is ...


1

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision? No, this adds redundancy to your database and this is something you generally do not want ...


1

There are so many 3rd party tools, most popular are MySQL Enterprise Monitor MONyog for DBAs Percona Toolkit Both MONyog and MySQL Enterprise Monitor shows CPU usage information in Linux systems However, if we tune certain parameters in my.cnf, high MySQL CPU usage may reduce innodb_read_io_threads innodb_write_io_threads innodb_log_buffer_size ...


1

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer. First i made one procedure to update one single row in the column: delimiter // create procedure update_amount_products (in id int) begin update categories set products_amount= (SELECT DISTINCT COUNT( products_to_categories.products_id ) FROM ...


1

The following is probably an issue: [!!] Maximum possible memory usage: 13.4G (347% of installed RAM) Due to limited memory, the database is probably using the swap/page file a lot, as well as significant disk i/o through out. This might cause CPU utilization to remain rather high at all times.


1

Please provide some sizes. For example, how big would BIG be when it is time to remove the rows? How big are the other tables? MEMORY is not necessarily better than InnoDB. It may be slower because of table vs row locking. It may interfere with overall performance because of taking RAM away from the buffer_pool for BIG, thereby slowing down other ...


1

This should do the job. DECLARE @country1 varchar(30) SET @country1 = 'asd' SELECT country_id, country FROM country WHERE country.country LIKE IF(@country = '', '%', @country); The IF statement works like this : IF(condition, ifverifiedcondition, else);


1

I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.) Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE to swap it in. Only ...


1

First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more suitable engines like InnoDB have a higher write penalty (to provide ACID properties) and are optimized for accessing records that are spatially and temporally ...


1

From the MySQL Documentation Aborted_clients : The number of connections that were aborted because the client died without closing the connection properly. Aborted_connects : The number of failed attempts to connect to the MySQL server From the look of those two status variables: Aborted_clients : This should make you look over how your clients are ...


1

Actually, in case of correct indexes existence, you will receive the data calculated in the relatively small time. In this case you don't need to perform the data manipulations relevant for storage of running sums and so on. The indexes will reduce the insert time, but influence will be very small (almost slight). For example, if the clustered index (not ...



Only top voted, non community-wiki answers of a minimum length are eligible