Hot answers tagged

3

Do not use (m,n) on the end of FLOAT or DOUBLE. That causes a rounding (at the bottom) or a truncation (at the top). If you want (m,n), you probably should use DECIMAL(m,n). FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38. DOUBLE stores 53 bits (about 16 ...


3

This is not really database administration. The way you do it cannot be done in most programming languages too. Calculate the discount as 95% of the drink price: 2.75*0.95


2

If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead. So for your query it should look like: SELECT DISTINCT CompanyId, Company FROM User_Hierarchy_Mapping WHERE UserId = ? That way you should get a parameter named Parameter1. If you have more than one question mark your ...


2

It looks like you had a couple of errors. Try CREATE FUNCTION IS_ROLEMEMBER (Rolename VARCHAR(255), Username VARCHAR(255)) RETURNS BINARY READS SQL DATA RETURN ( SELECT (FLOOR(RAND() * 10) % 2) ); The main errors I saw were: @Rolename and @Username - no need to use @ RETURNS BINARY AS - no need for AS I included the READS SQL DATA line as this ...


2

If you're looking for an easy-to-use, open-sourced tool, checkout etlalchemy. You can carry out your SQL Server to MySQL database migration with 4 lines of Python code: To Install: pip install etlalchemy (On El Capitan you may have to run pip install --ignore-installed etlalchemy) To Run: from etlalchemy import ETLAlchemySource, ETLAlchemyTarget ...


2

Would i benefit in this case from the myisam table engine? No you won't. What could i do to further optimize the query or the settings? Materialize the query. MySQL does not have built-in means to do that easily (similar to indexed views in SQL Server or materialized views in Oracle) so you'll have to put some effort into it. Create a table like ...


2

There was a bug in galera_recovery.sh script. https://jira.mariadb.org/browse/MDEV-10396


2

Indexes with the columns in the order given: actions: INDEX(user_id_to, user_id_from) -- 'covers' first query actions: INDEX(user_id_to, counter) -- may subsume ORDER BY and LIMIT in 3nd query Either index will be useful for 2nd query. DROP KEY user_id_to (user_id_to) as redundant when you add my two suggestions. For various reasons, it may be better to ...


1

I have resolved such issues multiple times by increasing the value of max allowed packet while restoring the databases. So something like: mysql --max-allowed-packet =[1G or 2G] -u root -p < dump.sql should work. Though there can be another reasons for this error. Still you can start debugging with above at least.


1

Floating point numbers are not always stored as you would wish, due to the way CPUs deal with floating point numbers. If you're always storing numbers that have 2 decimal points, store it as an integer and add the decimal point in the presentation layer.


1

Your question hits a 'gray area'; there is not an overwhelmingly 'right' way to design the schema. When the question is asked about city + state + country, I say: Have one Locations table that contains all 3 columns. And use country_code CHAR(2) CHARACTER SET ascii. I also say "normalize, but don't over-normalize". There are 'religious wars' fought over ...


1

Yes. You should also say UNSIGNED: TINYINT UNSIGNED. Range 0..255; 1 byte. Actually, it is such a tiny optimization that I don't bother mentioning it. Think about using the smallest datatypes when you first CREATE the table; it is messier to make the change later. SMALLINT UNSIGNED: 0..65535, 2 bytes MEDIUMINT UNSIGNED: 0..16M, 3 bytes INT UNSIGNED: ...


1

There are 3 choices. VARCHAR is one of them; probably the 'worst'. You suggested a second one -- an extra table. The third is: ENUM('one_option', 'another', ...). It will take only 1 byte (like TINYINT). You can directly use the strings that you are currently using with VARCHAR. If you ever need to add another option, ALTER TABLE will be 'instant' if ...


1

Just found this question while looking for answers on a related topic. What I've done is to set up the MySQL Fabric controller as a pair of servers with multi-master replication and a daemon that looks for a heartbeat on the first server's mysqlfabric process and restarts it, starting the second server's mysqlfabric in case the first refuses to restart. Why ...


1

No, you don't need to prep every backup. You only need to create the full backup on Sunday and then create the incrementals from Monday to Saturday. Full: innobackupex --user=USER --password=PASSWORD /path/to/backup/dir/ Incr: innobackupex --incremental /path/to/inc/mon --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD innobackupex --...


1

Since I happened to be researching this myself, here's a summary of what I found. According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further ...


1

Perhaps you can try server_uuid Other have addressed using it Beware of MySQL 5.6 server UUID when cloning slaves Resolving Error: master and slave have equal MySQL server UUIDs Some may resort to copying the auto.cnf to another server, which would then have the same generated server_uuid. You would have to code something that would scan a network on ...


1

This is a common issue as you scale to some point. There could be many causes; let's first search for the cause; then the solution. long_query_time=0 turn on the slowlog run for a few hours turn off the slowlog (or raise the cutoff to 1) run pt-query-digest to find the 'worst' couple of queries. Those queries may be zillions of 10ms queries, or it may be ...


1

NO! Do not attempt to have the same files accessed by different instances of MySQL. The code has now way of coordinating access between them. (If it did, it would slow MySQL down to a crawl.)


1

Always outer join them, then just use a CASE statement to pick out the info from the relevant table: SELECT ref_id, case when p.property_id is null then p2.property_id else p.property_id end FROM invoices i left outer join properties p on i.ref_id = p.property_id left outer join properties2 p2 on i.ref_id = p2.property_id;


1

Not addressing the question, but addressing 2 bugs in your schema. FLOAT(m,n) takes the decimal input, rounds to n decimal places in decimal, then converts to binary with another rounding error. FLOAT (with or without (m,n)) can hold only about 7 digits of precision. Hence, both numbers in your example lose precision at the low end (.559 --> .500, etc.) ...


1

FROM ( SELECT ... ) JOIN ( SELECT ... ) does not optimize well. Think of a better way to write the query. If that fails, put one of the subqueries into a TEMPORARY TABLE and add an index to it. Consider using the datatype DATE, not INT, for dates. OR is a performance killer (because it prevents use of an index). Consider other ways to deal with IS ...


1

You should revise your table structure. You are trying to put too many different things into one table. What you could do is have multiple tables like so: Table: site Has an ID and all other details about the site! Table: Devices Has an ID and all other details about the device. Table: measurements Has an ID and all other details about the measurement....


1

First, add these indexes: ALTER TABLE cat_01 ADD INDEX `ShopBuyDates` (`shop_id`,`buy_date`) ALTER TABLE orders ADD INDEX `OrderSales` (`order_id`,`sales`) Then try this query, and report the results. Make sure to run it at least twice, and discard the first result's performance, to flush out the effects of populating the cache. SELECT 'cat_01' as ...


1

This is age-old scenario I have dealt with before. Would you believe there are times when the checksum of table when dumped and reload on another server simply does not match the checksum of the source DB, regardless how the checksum was evaluated and no matter how you correct it ? Three(3) things can alter the checksum values of tables between source and ...


1

Run this query: UPDATE tablename SET textname = "" WHERE textname IS NULL after running that you can change the table structure so it does not allow null values anymore. I recommend checking the code to make sure it doesn't check for null instead of empty strings before changing the way the dB behaves


1

This seems to be an on-going issue Views are messy to handle with Dynamic SQL Earliest Bug was Cannot create VIEWs in prepared statements from 11 years ago. There was a patch put in to address it. Another bug report, Prepared-Statement fails when MySQL-Server under load, states that error 1615 is not a bug when the underlying tables are busy. (Really ?) ...


1

Do not see the problem. When somebody enters the email address a second time then this happens. Nothing to do with replication. This is what the unique key is used for. If this error would break the replication then I do not understand how replication could ever work. Every user could easily break the replication by, in this case, entering an email address ...


1

You have not decompressed the Xtrabackup files if they still have the .qp extension. Try using this inside the current folder: sudo innobackeupex --decompress --parallel=4 where 4 stands for the number of threads you would like to use. Also, for clean-up afterwards: sudo find ${DEST_DIR}/ -name "*.qp" -delete


1

Yes and no. The Query cache will not see, much less keep, the partial result. The page you referenced is talking only about the Query cache. The QC is populated only after the complete result is built. This gives a significant boost for a repeat of the identical query. (Similar queries are not helped any by the QC.) InnoDB's buffer_pool (and the ...



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