Tag Info

Hot answers tagged

5

To counter the points directly: Drupal doesn't use them and gets along fine without them, so why should we? Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively ...


4

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS. Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your ...


3

Have you tried REPAIR TABLE; or myisamchk --recover tbl_name http://dev.mysql.com/doc/refman/5.1/en/repair-table.html --EDIT-- As mentioned in the comments under your question, convert your tables to InnoDB tables which are transactional. This should help prevent corruptions in the future.


2

With innodb_flush_log_at_trx_commit = 0 there is not a guaranteed data loss (such an option would be nonsense), but a non-guarantee that data will not be lost in the last innodb_flush_log_at_timeout seconds (between fsyncs). To test how much data you are losing, you need to write several times per second and then kill mysqld just after some of those writes ...


2

You could enable binary logging. If you have not done so already. To find out if binary logging is enabled, run this: SHOW MASTER STATUS; If you get back, Empty set, it is not enabled. If you have not, please add this to my.cnf under the [mysqld] section [mysqld] log-bin = mysql-bin Then, restart mysql. From that point forward, you could look for ...


2

Shown RDS statistics are too global and broad to provide an answer to the question "Who is to blame for the increased load?". There are serveral ways to profile your queries, but as you are using RDS with MySQL 5.6, I would recommend you to use PERFORMANCE_SCHEMA to do it easily, as you won't need external software (some of which is not fully ...


2

The .mysql_history file is only for commands in the mysql client, and only for the current operating system user. I don't know of any way to log this anywhere but in .mysql_history. The mysqld.log can be written to the local syslog daemon, so it ends up in /var/log/messages (or wherever you send syslog). You enable this with the following entries in ...


2

Yes, that (1.4.4) is the latest version of the utilities as of 2014-08-20 (although the version 1.5.1 is probably going to be released very soon). Since approximately when MySQL 5.6 was released, the MySQL utilities have had an independent download from MySQL workbench (so they can be easily installed on text-only servers) and they have its own numbering ...


2

Instead of using NULLIF, you could have your CASE expressions return NULLs instead of zeros: CASE WHEN HOUR(dateTime) = 0 THEN totalNum ELSE NULL END AS hour0 or just omit the ELSE clause altogether (which means the same as specifying ELSE NULL): CASE WHEN HOUR(dateTime) = 0 THEN totalNum END AS hour0 That way AVG(hour0) would work as ...


2

The particular EAN to render would be determined by the database. Assuming your tablename is august15, then using the sample data below +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | +----+-------+---------+---------------+ | 2 | 102 | ...


2

Are you working with datetimes? Notice that If I ran it now I'd get for instance: August, 28 2014 13:57:36+0000 Which means that if my dates are at midnight I'd get the same with < and <= You should probably cast this into DATE if you are to work with dates


2

below should help you : CREATE TABLE dbo.Trading ([Ticker] int, [iOrder] int, [Price] int, [Amount] int) ; INSERT INTO dbo.Trading ([Ticker], [iOrder], [Price], [Amount]) VALUES (1, 1, 90, 180), (1, 1, 89.5, 179), (1, -1, 91, 91), (2, 1, 5, 20), (2, -1, 4, 16), (2, -1, 3, 9) ; SELECT Ticker ,sum(CASE ...


2

It's really kinda hard to work with your problem, but try the code below. All I did was to introduce a few brackets. SELECT var1, var2, var3 FROM tablename WHERE ( (var_a = '$var_a' AND var_b = '$var_b') AND (( var1 = '$var1' AND var2 = '$var2') OR ( var1 = '$var2' AND var2 = '$var1' )) ); if this doesn't work, then produce a sample data and ...


1

I would rewrite the query as following SELECT DISTINCT id FROM ( SELECT id from patient WHERE company_id=1 AND name_last LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_first LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_remote LIKE 'peter%' ) A; I would also change the indexes as follows ALTER ...


1

If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%. Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. ...


1

You use mysqldump. Step 1. Dump your original 32 bit database. mysqldump my32bitdb [options...] > mydb.sql Step 2. Copy the file mydb.sql to your new server Step 3. Load the 32 bit database mysql -u root -p my64bitdb < C:\path\to\mydb.sql With 10GB of data, it shouldn't take much more than ~ 1hr. For reference, see here and here.


1

I had written an interesting alternative of implementing your own manual hash indexes for your table, and then I made maths and realised your constraints: Having into memory 3 bigints will cost you 500*10^6*(8*8*8)/(1024*1024*1024) = 11.17GB that you do not have. RDS is simply not adequate for you anymore, as it is not flexible enough to try some ...


1

These two are actions to be performed, respectively, when the referenced record on the parent table changes its id and when it gets deleted. If you execute: UPDATE parent SET id = -1 WHERE id = 1; And there is at least one record on child with parent_id = 1, 1) will fail; in cases 2) and 3), all records with parent_id = 1 are updated to parent_id = -1. ...


1

1) means that if the parent is deleted, the child is also deleted (not a good idea IMHO - you should keep track of all data that's ever been in a database), although this can be done using triggers. You could also SET NULL. 2) means that if the parent primary key is changed, the child value will also change to reflect that - again IMHO, not a great idea. If ...


1

I found the problem. The slave was trying to connect to port 7 instead of port 3306 for some reason. I had to manually specify that as part of CHANGE MASTER TO command and add MASTER_PORT=3306 and it works perfectly now.


1

Please tell me you are not (and not even thinking) of using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-( It's not very clear from your post (correct me if I'm ...


1

after i discovered in this lovely site this link gives me an answer. http://stackoverflow.com/questions/5340883/mysql-avg-to-ignore-zero SELECT AVG(NULLIF(field ,0)) from table it works like a charm.


1

Maybe you can group it by the most recent Monday of the Date SELECT COUNT(1) Transactions,WEEK(monday) Week,YEAR(monday) Year ( SELECT (Date - INTERVAL WEEKDAY(Date) DAY) monday FROM mytable WHERE Date >= MAKEDATE(YEAR(NOW()),1) ) A GROUP BY YEAR(monday),WEEK(monday); NOTE: The WHERE clause in the subquery collects everything from the first of ...


1

I would argue for 1 row per data event (so 1440 rows per day) with one static column per data point. This will be easiest to query against any of the fields.


1

If it is the "world" database downloaded from here, it is in latin1 encoding. Use that for importing. UTF-8 (utf8 or utf8mb4 in MySQL) has become the de facto standard nowadays for operating systems and global comunications (except in some countries and specific areas of knowledge), and utf8 is now the default encoding in MySQL (since 5.5). latin1 was the 1 ...


1

Removing foreign keys does not damage data because you are doing DDL to the indexes. Once you do that, data integrity (even for existing data) going down the road needs its integrity tested. EXAMPLE create table parent ( id int not null auto_increment, ... primary key (id) ); create table child ( id int not null auto_increment, fk_id ...


1

Actually I guess the answer is in your boss's words: 3.He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them. FKs removal does not change data, yet, if someone runs queries against the DB, like your boss did, ...


1

Yes, you must set it when using gtid, otherwise mysql won't start, providing you the following error: [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates The slave requires its own executed binary logs in order to auto-position itself in the case of a stop (for example, if it crashes) or a master change ...


1

I actually managed to do it with engine=FEDERATED to get the syncing done. No need to replicate and all info is up to date. Site specific fields are captured in a seperate table.


1

Your entities are based on hours, not days, so you should go with one row per hour, just like you did, not a full day in a row. I did not understand if when you say data between two timestamps, if timestamp refers to the "inserted" column, or if it refers to the date and times on the data file. If it is the latter one, then having a datetime column for your ...



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