Tag Info

New answers tagged

0

If your big tables are innodb, you can use --single-transaction to not lock the table. Other possibility is Percona XtraBackup Or pt-archiver (again Percona)


0

Issue solved - Windows firewall had to be turned ON and then have the ports forwarded. Windows firewall being off was filtering the ports.


0

I have never seen an IP address like that before. Since 303 is an impossible number for an IP address, you should just save yourself the headache and delete both from mysql.user DELETE FROM mysql.user WHERE user='asdfasdf_imac4'; FLUSH PRIVILEGES; Give it a Try !!!


1

You can use prepare statement for this and also need a delimiter for the procedure something as delimiter // create procedure table_create (in tablename varchar(100)) begin set @table_ = concat(tablename, '_meta'); set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;"); prepare stmt from @qry; execute stmt; ...


0

PROBLEM I think your problem stems from a change in sql_mode. sql_mode is blank in MySQL 5.5 It is not blank in MySQL 5.6 SOLUTION Run this command SET GLOBAL sql_mode = ''; Then, add this to my.cnf to keep sql_mode blank should you restart mysqld [mysqld] sql_mode = '' You do not need to restart mysqld since you set it globally GIVE IT A TRY ...


0

I have compile a summary of MySQL limits. Some, related to your question: An InnoDB partition is limited to 64TB (2**46 bytes). A MyISAM table is limited to 2** 56 bytes, but you need to change a setting to get beyond 2**48. A table can have 1024 partitions until some recent release, then 8192. (BIGINT is not relevant in these limits.) Is there alarm? ...


0

Maximum row count is depend on type of field used for primary index. In case of unsigned bigint table can be 18446744073709551615 rows long.


0

It sounds like the column is a composite of two or more items. Creating an index on two or more columns can increase the chance that a value in the key is different from any other value in the index key. This can have its benefits. But as you add more columns than will be used in the where clause, the performance of the index degrades because it becomes ...


0

The mysql.servers table is used only in conjunction with any table that uses the FEDERATED Storage Engine. It's in the MySQL Documentation: FEDERATED tables may be replicated to other slaves, but you must ensure that the slave servers are able to use the user/password combination that is defined in the CONNECTION string (or the row in the mysql.servers ...


2

Well, I figured out that if you use the backticks ("`") around the table name (like mysqldump uses in the comment-separator sections, e.g. `call`), it works as expected. So, the above should look like: -- -- Table structure for table `call` -- DROP TABLE IF EXISTS `call`; CREATE TABLE `call` ( path varchar(255) NOT NULL default '', call_num int(12) ...


0

SUGGESTION #1 Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings: innodb_buffer_pool_instances=2 # If your Server is DualCore innodb_buffer_pool_instances=4 # If your Server is QuadCore innodb_buffer_pool_instances=6 # If your Server is HexaCore innodb_buffer_pool_size=90G I ...


1

I don't have enough reputation to comment on the previous answers. So I thought I'd elaborate a bit. 1) ON DELETE CASCADE means if the parent record is deleted, then any referencing child records are also deleted. ON UPDATE defaults to RESTRICT, which means the UPDATE on the parent record will fail. 2) ON DELETE action defaults to RESTRICT, which means ...


0

In phpMyAdmin you cannot use DELIMITER in the SQL statement, because you have to define the delimiter on the page instead. Remove both DELIMITER lines from the SQL statements and then, just below the TEXTAREA where you write your SQL, there is an input field that looks like this: [ Delimiter ';'] Change the ';' to your new delimiter, ie. '//'. This also ...


0

What is the goal? If the goal is to have a path to recovering the system, then consider using Replication and having a Slave. The Slave will usually be up to the minute with the Master. This is far better than the 93 minutes you are grumbling about.


1

You must understand what the binary logs are: they store all the queries (in STATEMENT format) or row changes (in ROW format) that happened since they were recorded. So, your command: mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname basically executes all insert, updates & deletes that occurred to the server during ...


0

Rick James covered it quite well, I just want to add one thing: [!!] Query cache is disabled [--] Reads / Writes: 94% / 6% - and [1K qps] for me it seems there might be some potential in enabling query cache - but it depends a lot on your queries - if lot of queries contain some dynamic conditions (datetime/timestamp for taking most recent something) or ...


1

Are you using MyISAM? Or InnoDB? Or both? [!!] Query cache is disabled This is "good", not "bad". [!!] Table cache hit rate: 0% (400 open / 78K opened) How many tables to you have? Thousands? Please elaborate on why so many. (This is usually a design flaw.) Meanwhile, increase table_open_cache to, say, 2000. What MySQL version are you ...


0

MyISAM still uses .MYD and .MYD. The only way you can see .frm files only and no other table file would be this: All tables are using InnoDB innodb_file_per_table disabled (all table data would be inside ibdata1 If you can log into MySQL, run this SELECT IFNULL(engine,'total') engine,(data_length+index_length) tblsize FROM information_schema.tables ...


0

Unfortunately, you are not going to get those queries from mysql.slow_log. Why ? There are two options evidently being used log_queries_not_using_indexes log_throttle_queries_not_using_indexes According to MySQL Documentation (The Slow Query Log, Paragraph 9) To include queries that do not use indexes for row lookups in the statements written to the ...


0

You should set optimizer_switch in MariaDB back to the old MySQL 5.5.11 value in my.cnf [mysqld] optimizer-switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on You could login to MariaDB abd set is as well set global optimizer_switch = ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


1

since the the following command use my_database; executed too fast when you try this locally (on the same server), and take a log time when you executed remotely, there is some common causes for that like the connection between the server and client is slow, or there is a delay (lag) on attempting to retrieve and verify reverse DNS, you can try to add the ...


0

Yes this design is poor, just imagine to add another hour. This is a better aproach: Students(**StudentID**, LastName, ...[other Student Details]) Courses(**CouseID**, Description, ...[any other Course Specific Stuff]) or to whatever they attend. If you can be sure that there is and will always be only ONE you can skip this If possible have something ...


3

Run the output of the following query: SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';") FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT LIKE 'mysql';


1

Since you have 200 databases and you do not want to grant one by one. Fastest way to do that would be GRANT SELECT ON *.* TO 'test_user'@'localhost'; FLUSH PRIVILEGES; And then just revoke the privilege in mysql db REVOKE SELECT ON mysql.* FROM 'test_user'@'localhost' ; FLUSH PRIVILEGES; But when I selectively GRANT and then REVOKE on mysql.* . Then it ...


1

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons So there are good reasons to use prepared statements: Save on query parsing Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of using prepared statements: Query cache ...



Top 50 recent answers are included