Tag Info

New answers tagged

0

You can use Geohashing (Geohasing) You have to create a geohash for every POI in your table. And then based on the precision that you choose you can use LEFT(your_geohash_column,'c1','c3','c4','c5','c6',c7','c8') Where the c's are the 8 neighbors from the center calculated by your desired precision. Keep in mind that you are limiting the results with the ...


0

I resolved: SELECT COUNT(achou) as total FROM ( SELECT h.acctstarttime, h.acctstoptime, h.radacctid, h.calledstationid, ( SELECT calledstationid FROM radius.radacct_historical as sub WHERE sub.callingstationid = '54:9F:13:27:31:7F' AND sub.acctstarttime < h.acctstarttime ORDER BY sub.acctstarttime DESC ...


0

I'll make an assumption first. A real world person could have more than one user in your system? If a real world person (persons table) can have (and should have) only one user, why do you allow a one to many relationship between those two tables? Or I've misunderstood your model or I would create in the Logical Data Model only one table for users AND ...


1

Assuming you always have pair , the following should give you desired result: SELECT a.event_id ,a.pair,a.versus, a.score as score1, b.score as score2 FROM table_name a INNER JOIN table_name b on (a.event_id = b.event_id and b.pair =a.versus and b.versus=a.pair and a.pair<b.pair) Side notes. 1. It makes sense to have a surrogate primary key column in ...


0

Have you considered using either mydumper or myloader? In tests that they have ran, it appears to have a significant effect on processing time. You can find more information here (https://www.percona.com/). Or is this a giant CSV type file you are trying to import?


1

If it's MyISAM - no chance to recover w/o a backup. If InnoDB - it depends. InnoDB flags a record as deleted and keeps it in a page for a while. When a tree is rebalanced the deleted records are purged. So whether you can undelete records depends on how much writes were done to the table after the delete. Open InnoDB tablespace in a hexeditor and try to ...


1

You can try this: SELECT m.vlanID, v.vlan_name, m.interfaceName, count(1) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType FROM macs m INNER JOIN network_devices n ON n.nd_ip_address = m.ipAddr INNER JOIN vlans v ON m.vlanID = v.vlan_id INNER JOIN ...


0

This command may not have run properly on slave while configuring its master CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx', MASTER_USER='replication', MASTER_PASSWORD='xxxxxxxxxxx', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.00xxxx', MASTER_LOG_POS=xxx


0

See the MySQL documentation or a Step by step replication setup blog if you need to review. If these doesn't help please share the output of show slave status\G (as such it appears connected) and also output of my.cnf of master/slave. Mainly you need to worry about server-ids, replication filters (replicate-to-db/table etc)


1

Based on the explain image above, the query is doing a table scan. (possible key and key are Null). Rows_examined: 1,088,312,551 ( 1 billion rows examined, that's quite a lot, look like a cross join to me). The second query (not exist) might not be right as it will exclude all data FROM bot_sessions_statistics WHERE date_active >= date(NOW()) compare to ...


0

I would break these queries and have result stored in temporary tables. I would use a stored proc. Have sub-queries like this among many tables will create constrain and potentially some lockings. Also using > on a column in query will for Mysql to do a scan on that column (even if it is indexed). Avoid >,>= or < <= (use between).


0

Master Master should be possible in DR too. Basically what you really have is a Master - Slave with Slave ready to become a master upon failover. So in Location 1: Master Active <--> Macter Passive Location 2: Master Active <--> Macter Passive Master Active in Location 1 should replicate to Master Active in Location 2. Make sure they all have bin ...


0

I think you're looking at something like this: Select A.Max_id, harddrives.id As id1, harddrives.serial_number, brands.name, harddrives.size, location.name As name1, encryption_type.name As name2, resource.name As name3, backups.start_time, backups.end_time FROM ( Select backups.resource_id, Max(backups.id) As Max_id FROM ...


0

Change the ft_min_word_len value to specify the minimum length of the word to be included in a MyISAM FULLTEXT index. ft_min_word_len The minimum length (default: 4) of the word to be included in a MyISAM FULLTEXT index. Note: FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK. ...


1

The first thing I would do is fix the network issue. I would also set up pt-heartbeat to monitor Mysql slave delay. Why do you need to purge the binlog on Master? If you miss the position of a binlog to purge you could affect all your slaves. Mysql allows to set up binlog retentions. If you are running out of space, because your binlog are too large, I ...


0

No way I think. But 1) You could just stop purging if any of the slaves are unreachable(ssh or mysql) from the server where you are running this purging script 2) If the connectivity issue is only with MySQL and not SSH, you could get the positions from master.info files of the slaves Actually I follow this method


1

The easiest method is the following Go to each Slave and run SHOW SLAVE STATUS\G Look for Relay_Master_Log_File on Each Slave Whichever Slave has the oldest Relay_Master_Log_File is the one you purge to on the Master Why Relay_Master_Log_File ? First Look at SHOW SLAVE STATUS\G mysql> show slave status\G *************************** 1. row ...


1

You should rather monitor your slaves for replication lag or if it's actually catching-up / connected with master or not! You may simply write a shell script to look at io_thread and sql_thread values and alert if they're NO. There are already many scripts available for reference. You can use Percona Monitoring Tools's pmp-check-mysql-replication-delay / ...


1

Active-Passive master-master is a good setup but I have seen SUPER (humans)users writing on slave without setting sql_log_bin. (Though super_read_only in 5.7 will change things around this.) Anyways, following is possible and works. ProdActiveMaster<---->ProdPassiveMaster | | | \/ DRmaster----->DRSlave So you know now that another master-master ...


1

I am not sure when but sooner or later we're going to see deprecation of MyISAM. InnoDB is performing well and it does have fulltext as well (though performance is a bit of something being worked over there.) InnoDB is even a default storage engine in latest MySQL versions. If you really really need performance from FULLTEXT go with MyISAM (Note ...


1

Have you tested or would it be possible to drop indexes on the destination DB table(s) where you are inserting into, insert those into smaller batched chunks (optimal as indicated above), and then rebuild the indexes on the destination table(s) once all inserts are complete? May be something easy enough to test to confirm.


3

Following @ypercube 's (justified) critique of my first pass at an answer, this is the (far more thorough) second go! To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. Note, the Cust_Order table now has a DATETIME (MySQL) or TIMESTAMP (PostgreSQL) column. I then loaded these tables with data (see end of post). ...


1

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not. At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows. I guess your query will at least require ...


0

harddrives.id As id1 Update it to (Max)harddrives.id As id1 I think that should work.


7

As stated in comments, I would store the current or effective version of a BlogStory in its respective table and keep all of its previous versions (or past states) in a separate BlogStoryVersion table. In this way, you may find this post helpful since it presents a comparable method for a similar scenario. Business rules In accordance with my ...


1

Few points here: "Cartesian product" is not simple :) In your specific query, and based on the index you have, the time will highly depend on the distribution of the data. Try this query: SELECT COUNT(*) FROM table WHERE column2=''; I guess the result is not a small number. UNIQUE entry (column2, column3, column4, column5); is not healthy. The index is ...


1

I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user. The model would look like this: This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the ...


1

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action. That is, one simple write can snowball into the mess you are seeing. Changing to InnoDB is likely to avoid the ...


0

pt-table-checksum and its friends are very good at validating that the data is (or is not) the same.


3

If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking. myISAM is faster in a number of read-only use cases but: The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time ...


0

If you read that main page carefully, you would noticed a few things If I wasn't the first then it's a tie with Baron Schwartz, creator of Maatkit. In 2008 he hired me at Percona where we worked together until 2012. (I still work at Percona.) We transformed and greatly expanded Maatkit into Percona Toolkit which is now arguably the world's most popular ...


1

If I understand then this is what you want: SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name FROM macs m, network_devices n, vlans v WHERE n.nd_ip_address = m.ipAddr AND m.vlanID = v.vlan_id GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name ORDER BY m.vlanID


0

you could add an empty field at the end of each line, and remove the last column of your table. Otherwise check for all the line terminations (\r\n vs. \n), or the presence of \n in your contents... It should work at the end, good luck !


1

Definitely, in a Relational Data Management System, you SHOULD use relationships between your entities. A simple example query where you will be stuck by using the first option : Select all users and role name with roles 1 or 11 You will have to perform character to character searches and comparisons that are much less efficient than relational ...


0

1 Stop the slave on the first master mysql> STOP SLAVE; If the second master becomes a read only then the first master should no longer replicate from it. 2 Change the 2nd master's configuration to read only. mysql> SET GLOBAL READ_ONLY=1; Add read_only=1 to my.cnf for the next restart Read Only variable 3 Unlock the tables on the second ...


2

There's probably something more elegant than this, but since you are doing a kind of pivot (transforming from columns to rows, i.e. metadata to data) it is never going to be pretty. Assuming a table like: create table t ( ts timestamp not null , EngOilP_sd smallint not null , CompOilLVL_sd smallint not null ) engine = innodb; insert into t (ts, EngOilP_sd, ...


1

Before doing any changes and attempts, take complete backup of your databases. If you have physical backup of your database: Restore your database in a temporary instance. Identify the point from binary logs where the changes were made and you want to ignore them. Extract DMLs after that point till latest to SQL file. Run the SQL against the new mysql ...


0

A skipped id can happen in several situation. Keep in mind that an INSERT generally starts by allocating all the ids that it may need. Here's a few examples where AUTO_INCREMENT ids are 'burned': BEGIN; INSERT; ROLLBACK; INSERT IGNORE -- and some of them are ignored due to dup key REPLACE -- DELETE, then INSERT The one I dislike is: SET autocommit = ...


2

Load your data in another database. Run LEFT JOINs one way, then the other way, to see what was deleted/new. Run a JOIN to see if the rows that exist in both are the same; fix if necessary. Run a multi-table DELETE to get rid of the the appropriate rows. Run INSERT...SELECT..LEFT JOIN to copy the missing rows in


0

A common pattern for avoiding dups goes something like this: SELECT ... FROM ( SELECT ... SUM(...) FROM w ... GROUP BY ... ) AS x JOIN y ON ... WHERE ... ORDER BY ... This assumes table w has multiple entries that you want to see only once in the result. Hence the GROUP BY to get one copy of each, plus any aggregates you desire. Then you ...


1

Downgrade to 6.2; 6.3 has the bug you describe.


2

The short answer: Can't tell. Not enough info. The long answer... If your data is growing at 10%/month, it will be about a year before it is 64/24 times as big. So if you grow the RAM and the buffer_pool by 64/24, you are somewhat likely to have the same cache performance of the buffer_pool. After only one year. The 99% utilized doesn't really say ...


0

DreamFactory works just fine with either SQL or NoSQL, and the APi will actually be the same to GET or PUT data. The fields you have above make sense for either SQL or NoSQL. The only difference is that you need to create schema for the SQL table you want to use. Here is a tutorial that is very similar to your use case: ...


0

There are several options, but here are two based on two options for how your end-users connect. First, if your users connect as themselves. For example, if you have a user "annamaria" then in your trigger you can identify the user with the CURRENT_USER() function. Record CURRENT_USER() into your auditing table with each modification and you can track ...


1

Depends on what version you are. You can install the audit_plugin: INSTALL PLUGIN server_audit SONAME 'server_audit'; You can choose two outputs, file or syslog. The output format will be: 20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries, 20140901 ...


1

Since the alias column is nullable, if you add a unique constraint on the composite (last_name, first_name, birth_date, alias), there will still be duplicates allowed, with the same values in the first 3 columns and NULL in the alias. The constraint is skipped / accepted when at least one value is null. MySQL documentation on CREATE TABLE is not very clear ...


0

Your relation (story_id, version_id, editor_id, author_id, timestamp, title, content , coverimg) is not in 3rd normal form. For every version of your story the author_id is the same. So you need two relations to overcome this (story_id, author_id) (story_id, version_id, editor_id, timestamp, title, content , coverimg) The key of the first relation ...


1

I use SQLyog to have a backup of the database,which would help me to rewrite the data into the database by importing the backed-up data. And not only that i can also import the data by executing a sql script containing the table syntax and data.


0

So basically after some more research my conclusion is that parallel queries are just not possible with MySQL at all. The closest it looks like you can get is by using shard-query, but I believe this can only execute parallel queries against a single database when using partitioning, and partitioning does not support foreign keys. Foreign keys are a massive ...


0

There is no CREATE TRIGGER IF NOT EXISTS There is DROP TRIGGER IF EXISTS You must execute the trigger's creation as follows: USE xxx_admin DELIMITER $$ DROP TRIGGER IF EXISTS test_AFTER_UPDATE $$ CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE` AFTER UPDATE ON `test` FOR EACH ROW BEGIN INSERT INTO auditTest select *, ...



Top 50 recent answers are included