Reputation
13,544
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 18 39
Newest
 Mortarboard
Impact
~972k people reached

Jan
3
revised mysqldump --single-transaction, yet update queries are waiting for the backup
update, minor clarifications, shift speculative stance (heh)
Jan
3
comment MySQL / MariaDB pid ended (cannot start mysql) in CentOS
Also servers.frm, servers.myd, plugin.frm, plugin.myd are probably among the files the log messages indicate aren't found. Several of them are also likely to have a .myi file of the same base name.
Jan
3
comment MySQL / MariaDB pid ended (cannot start mysql) in CentOS
Do you have files called user.frm, user.myd, innodb_table_stats.frm, innodb_table_stats.ibd, innodb_index_stats.frm, innodb_index_stats.ibd somewhere on your system, at this point? If so, where? The lettercase of the extensions may vary, but these files, and several others, should be in /var/lib/mysql/mysql but if they are elsewhere, mention where you found them. You will need to proceed with extreme caution before moving anything around.
Jan
3
comment MySQL / MariaDB pid ended (cannot start mysql) in CentOS
This looks fairly serious. Unfortunately, if you moved the files with the server running, there's only one way to flawlessly recover, and that has to be done using a delicate manual process prior to the server daemon being stopped. The immediate issue, though, is that files that should be in /var/lib/mysql/mysql can't be located.
Jan
3
comment relationships in access
The relationships should reference the primary key of the "ips" table, and the "IP ID" is what should be stored in the referencing columns.
Jan
1
comment error 2013 (hy000) lost connection to mysql server during query while load of mysqldump
Set the value in the [mysql] section of the config, as well? (Not [mysqladmin] though). SELECT @@MAX_ALLOWED_PACKET; to confirm that the server is using these values?
Dec
27
comment MySQL using cpu after cancelling slow query
possible duplicate of How to stop rollback in progress.
Dec
27
comment MySQL using cpu after cancelling slow query
If this was a query that was modifying data, then you now have a runaway rollback on your hands; the server made changes, but kept track of the former data, and is now in the process of permanently reverting everything to the way it was, undoing the changes. For this reason, your best course is to leave it alone. It should eventually finish. Or, you can force it to stop but that's not the necessarily the best or safest course.
Dec
19
comment Why do innodb tables get corrupted so easily?
I wonder if mysqladmin shutdown would be a better shutdown approach than whatever ridiculous thing Windows might be doing... But that doesn't change the fact that you shouldn't need to be shutting down a production server, and when you do, InnoDB should be able to recover, if recovery were needed in the first place, which it shouldn't be...
Dec
18
comment Change MySQL stored procedure 'Database Collation' name
ALTER DATABASE changes the default values. I suspect your "most correct" solution would be to fix this in the dump file, so they are loaded correctly from the start, but an alternative would be to drop and recreate the procedures now that the database itself is as you intended. Does that work if you try it? No other approach comes to mind at the moment.
Dec
18
comment Modify MySQL information_schema (specially create_time) on AWS RDS?
This can't be done on any MySQL server, and is unrelated to RDS. The "tables" in information_schema are not tables at all -- they're internal server data structures represented as tables, for read-only access. Any "why?" is an excellent question from @Phil.
Dec
15
comment With MySQL 5.6, Is there any way to keep a replication slave X seconds behind_the_master?
@ypercube you wouldn't lose the data. It's persisted to the slave's relay log immediately, as standard async replication (as OP has correctly speculated), but the slave sql_thread waits to execute the events until they are MASTER_DELAY seconds in the past. The idea is to provide a recovery window to avoid data loss due to queries that ran on the master at the hands of someone whose fingers are asking to be cut off. The option is poorly named. SLAVE_SQL_DELAY would have been a more intuitive name.
Dec
14
comment How to minimize the InnoDB page evictions in a MySQL full in-memory db?
The buffer pool size is not dynamic, it's fixed, so to say InnoDB has "more" is to miss the point. It's the configured innodb_buffer_pool_size that may be insufficient.
Dec
14
comment How to minimize the InnoDB page evictions in a MySQL full in-memory db?
InnoDB doesn't evict pages unless it needs to load a new page from disk that it doesn't already have... so, apparently you do not have enough memory for your data and indexes and handling of ROW_FORMAT=COMPRESSED operations, if you're using table compression. How did you prove that you had "more than enough memory?"
Dec
10
comment Release MySQL Memory and Performance Tunning
You are tweaking far too many variables with this configuration. If you don't have a specific, objective reason for each of these entries, you should start with the defaults.
Dec
9
comment Why is MySQL 5.5 ignoring this index?
Also, don't select * unless you really need all the columns. This can also modify the query plan.
Dec
9
comment Why is MySQL 5.5 ignoring this index?
ALTER TABLE cba_device_log ADD KEY (device_id, device_time_ms); If there are a large number of device 1067 rows, the optimizer can conclude that the index will be more trouble than it's worth, but a more useful index, as I suggest here, might get more attention.
Dec
3
comment mysql ssl replication - fails while attempting to connect to master
Use the mysql command line client while logged in on the slave server to try connecting to the master using the same cert, key, and credentials. Replication uses an ordinary client connection from slave to master, so this is an effective approach that may tell you something useful.
Nov
27
answered Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?
Nov
20
comment Taking a mysqldump from a specific point in time
To clarify what @ypercube is saying, the servers do have to start with identical data from a fixed point in time, but as long as you know the binlog coordinates of a given dump of server A, and no logs have been purged, you can restore the dump elsewhere and play forward from there using master A's logs, however much later that may be... but identical data is essential.