9,567 reputation
11025
bio website
location Cincinnati, OH
age 43
visits member for 2 years, 2 months
seen 23 hours ago

If you question or otherwise fail to recognize the superiority of the relational model... you're doing it wrong.


Oct
24
reviewed Close “DISTINCT” JOIN in mySQL
Oct
24
revised Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
clarify question title, rework portions of the body for formatting and to remove a portion of the question that wasn't needed (op trying to figure out how to show create table); change some phrasing to make the question more googleable
Oct
24
comment Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
Version Caveat: If you are writing to the federated table, there is a fairly serious bug in MySQL versions prior to 5.5.36, 5.6.16, and 5.7.4, that could crash the local server due to an invalid pointer if an UPDATE ... JOIN joined a federated table to another table and exactly one row matched on the join. If 0 rows or more than 1 row matched, the issue did not occur, because the optimizer came up with a different query plan. This was fixed in the versions listed, above. If you are only reading from the federated side, this issue has no impact.
Oct
24
comment Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
@PythPhytho yes, the lack of FEDERATED support is one of several drawbacks to RDS, a decent platform overall if you can accept the limitations in functionality that are arguably necessary for a "managed" MySQL offering. Here's an extremely valuable reference that will help avoid some potential gotchas with the federated engine. It's an old article, but federated hasn't changed much in "official" MySQL (it was rewritten as "FederatedX" in MariaDB (and Percona?), but many of these principles still hold). onlamp.com/pub/a/databases/2006/08/10/…
Oct
24
comment Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
@PythPhytho yes, you can. I have a daily report runner on a "local" MySQL server that uses several federated connections to an RDS server to fill in some data from a different system. With a FEDERATED table, the remote server (where the data lives) has absolutely no clue that you're using it this way, because the federated storage engine makes an ordinary client connection to the remote system, and it writes and sends ordinary SQL queries to the far end, to get its work done.
Oct
23
comment How to change table with innodb/myisam engine to federated
@tombom the federated storage engine absolutely does support indexes, and in fact, they're critical for good performance... but they work in a very unconventional way. If FEDERATED doesn't know about a useful index for a given query, it won't send a WHERE clause in the remote query, and it will fetch the entire table and scan it locally. It usually makes sense to declare indexes on the federated table that don't even exist on the real table, so that it won't do this.
Oct
23
answered Using concat inside a stored procedure parameters
Oct
23
answered Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
Oct
22
revised MySQLfabric — Manage setup problem
cleanup/readability
Oct
22
comment Returning true or false in a custom function
Note that if (the_nxt(44) = true) will never be true, because the_nxt() returns VARCHAR 'true' instead of the literal value TRUE (which is the integer 1)... and the string 'true' != the integer 1 (true, unquoted). A more correct and less error-prone solution would be for the function to return a TINYINT TRUE or FALSE and test whether the_nxt(44) IS TRUE (not =).
Oct
22
comment Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command
Are you sure you really need to optimize the table weekly? The excess space should level itself out over time if you leave the table alone. What kind of scale/size and what amount of reclaimable space are you dealing with?
Oct
20
comment Dealing with Mysql Replication and Functions
On the other hand, the number of read-only slaves you can deploy is virtually unlimited, since slaves can cascade from other slaves, as well as from the master.
Oct
20
comment Dealing with Mysql Replication and Functions
@jcho360 there's not really a meaningful limit. You'd have to stay below 255 masters to avoid collisions in UUID_SHORT() and below 65,535 masters to avoid problems with auto_increment_offset and _increment but those are all unreasonably large numbers of master servers. Avoiding consistency issues becomes almost exponentially complex as master server count rises. If you need more than 2, look at Galera in MariaDB or Percona server, both MySQL compatible, for synchronous replication.
Oct
20
comment MySQL: #126 - Incorrect key file for table '/tmp/#sql_9dc_1.MYI'; try to repair it
Try observing your free disk space with df -k while the view is opening. If the view needs to build a temporary table while it's opening, you could be running out of free space. The space will be used up, then freed again, as the space consumed by the failed temporary table creation attempt will be freed almost immediately when the error is returned, if this is your problem.
Oct
17
comment Rows are not getting deleted from MySQL Table
On the InnoDB server, SELECT @@global.autocommit;... shows 1 or 0? Is this a new problem, or did you have a working system that stopped working? Can you update or insert from the Federated side?
Oct
17
comment MySQL has gone away with two connections
SET GLOBAL LOG_WARNINGS = 2; and try again? Then check the MySQL error log...
Oct
16
reviewed Close MySQL keeps inserting new rows automatically
Oct
16
reviewed Approve suggested edit on
Oct
16
reviewed Approve suggested edit on
Oct
16
answered Dealing with Mysql Replication and Functions