New answers tagged

0

You can also try purge binary logs before '2016-04-29 08:00:00'. I sometimes find one works when the other doesn't for some reason. Failing that just delete them with rm like you said. It's not technically correct, but it shouldn't be a problem (I did this frequently in the past before I learned about the PURGE commands, and never had any problems). Of ...


0

maybe kill mysqld process is not the best way to deal with this problem now I simulate your error "InnoDB: Unable to lock ./ibdata1, error: 11" 1.delete PID file while mysqld is running [root@linux tmp]# service mysql status SUCCESS! MySQL running (9320) [root@linux tmp]# rm -rf /data/mysql/data/lawrence.pid 2.when you restart or shutdown MySQL ...


0

Personally, I'd be allocating more RAM to MySQL itself rather than the RAM disk, and then putting the tmp store on physical media. MySQL works much better when it has more memory allocated to things such as buffers and the innodb cache. Also make sure your max_heap_size and tmp_table_size variables are set to as high as they can be (64Mb), so that the ...


0

One of the "select into" statements is returning more than one result. Those statements can only return a single row.


0

Temp is used to store temporary on-disk tables for queries which go over the in-memory tmp tables limit. When MySQL needs to insert to a table and there is not enough space, the query stops - You get disk-full warning and you can either free some space or kill the offending query. In your case one runaway query would probably stop all queries using on-disk ...


1

a) .. engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index .. - Only InnoDB uses clustering by PK, MyISAM uses HEAP tables (no clustering) and the primary key is just another index with all indexes using pointer/offset to the heap to find the right row. b) sequential vs ...


0

Here are my thoughts on this, 1. Via DB Side, If you don't want to mesh the deployment aspects with your application then this is best one. The system admin people will take care of these burden from you. They will setup the master-slave setup and your application will seamlessly access the db. 2. Spring - Hibernate In this way you can use interceptors ...


1

Do you have 4 or more cores/processors machine? Current MySQL query cache implementation does not scale well and there is a mutex contention problem on systems with more thread (the query might spend more time waiting to check the query cache than it would need to be executed fresh if the relevant data are already in other buffers which allow parallel ...


1

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent. MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5. With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some ...


0

Did you make any changes in my.cnf file recently? Most of the time MySQL fails to start due to any incorrect changes made in its conf file. Next option is to check the ownership and permission of /var/lib/mysql. It should be owned by MySQL user and not the root user.


2

You'll speed-up the query significantly if you create an index (ideally a unique index) on property_ad_id and created_at, and another on just created_at: CREATE INDEX ON property_ads_history (property_ad_id, created_at); CREATE INDEX ON property_ads_history (created_at); # if not already there! Note that the condition t1.price > -1 OR t1.price <> ...


3

ORDER BY in the sub query is what's making it slow. ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do. And you don't even need to order by all of it. What you need is only to filter out the minimum, so instead of: ORDER BY ...


0

Such a tool would be almost completely worthless in practical use. max_connections has nothing to do with performance. It's a safety mechanism to prevent a runaway application from making so many connections that the server becomes overwhelmed. The value makes absolutely no difference unless you are using that many connections. It is also a value that ...


1

First backup the database. Sometimes character conversions can give unexpected results. I have had good success using these instructions: SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') INTO OUTFILE '/tmp/altertablesstatements.sql' FROM information_schema.tables WHERE ...


1

By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature. Definer and invoker security contexts differ as follows: A stored program or view that executes in definer security context executes with the privileges of the account named ...


0

SELECT * FROM animals WHERE FIND_IN_SET(name,(SELECT @animal_names)); See the FIND_IN_SET documentation


0

Build a Stored Routine; in it use CONCAT to construct the SELECT with the @variable stitched in; prepare and execute. Alternatively, if you are using an application language such as PHP, do the stitching in that language.


1

Simply make these changes: call mysql.insertTables (`Debbie`, `Hopkins`, NULL, NULL, NULL, -- Changed 1, 123456789, 0, NULL, NULL, NULL, 12211985, 12345678, 1) NULL tells the INSERT to invoke the DEFAULTs.


0

On the JSON question... Look at the SELECTs you will be performing. If any of them need to search (WHERE) or sort (ORDER BY) on a particular field, then expose it as a column, probably with an INDEX. Otherwise, consider throwing it into a JSON column. At one extreme, all the info could be in a single table with (id, src_ip, dst_ip, timestamp, json). ...


0

When declaring HEX strings such as UUIDs, or when declaring other strings with limited character set needs, such as country_code, postal_code, zip_code, etc, declare them to be CHARACTER SET ascii. It can be applied just to the column, letting the rest of your columns be utf8mb4 or whatever. And be consistent between tables so that JOIN works efficient. ...


1

Let's do the math. 6500 characters, even in CHARACTER SET utf8mb4 takes no more than 26000 bytes. TEXT has a limit of 64K bytes and needs a hidden 2-byte length field. LONGTEXT has a 4-byte length field. Let's say (for the 'math') that the average row length, including this text, is 3000 bytes. Math... Savings of switching from LONGTEXT to TEXT: 2 ...


1

You can misuse ~/.my.cnf for being able to change the mysql-root-password. The trick is to have a task "Set root password"(nr.1), which will set the password. Afterwards, you have a task, which creates a ~/.my.cnf with the correct credentials (nr.2). On a new system, ~/.my.cnf is not present. Task nr.1 will create mysql-root-user with given credentials. On ...


2

The CASCADE clause works in the opposite direction. From the parent to the child table. When you delete a row in the parent table, the related rows in the child table are deleted as well.


-2

I have been using mysqltuner.pl from mysqltuner.com for years. It's not automatic, it analyses and makes recommendations. Good for part time dba's like me.


0

Im not sure about a script but you can build your own based on MySQL recommendations and good practices shared by experts here: InnoDB Buffer Pool Hit Rate How much memory do I need for innodb buffer pool? Optimal Number of MySQL InnoDB Buffer Pool Instances Making sense of INNODB buffer pool stats How to clear/flush mysql innodb buffer pool? MySQL ...


0

You could do: SELECT * FROM animals WHERE CONCAT(',', @animal_names, ',') LIKE CONCAT('%,\'', name, '\',%'); It won't perform fast, but it'll work.


2

You can use variables: delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT @fn:=`first_name`, @ln:=`last_name`, ...


0

Example pivot tracking table CREATE TABLE IF NOT EXISTS `mail_track` ( `ID` int(10) NOT NULL auto_increment, MemberID mediumint(7) default NULL, `MessageTitle` varchar(20) default NULL, `Type` varchar(6) default NULL, `URLID` varchar(15) default NULL, `DateTime` datetime default NULL, `IP` varchar(15) default NULL, PRIMARY KEY (`ID`), KEY `DateTime` ...


0

You can use Galera for multi-master replication. If you could move individual clients to specific nodes you will not run into locking issues. It also has automatic recover of failed nodes. http://galeracluster.com/


0

Use https://github.com/alestic/ec2-consistent-snapshot of your EBS volume using --mysql-master-status-file FILE option. Create a new volume from that snapshot and attach to new instance or another instance you can use to copy to a remote location if needed.. The db server will only be locked for a couple seconds. I just performed this operation on a server ...


1

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables ...


0

Check for high traffic users using MyISAM or ARIA table engine. I run CloudLinux cpanel boxes and even with CPU throttling a single high volume user with lots of writes to MyISAM table can lock the whole server up.


0

Federated tables are not transactional by nature, so you don't want to put a trigger on it. Alternative would be to put a trigger to post to another local table. See: Federated tables and triggers


0

Verify that you tables are using the InnoDB engine and not the MyISAM or ARIA engine. Concurrent writes on non InnoDB tables can cause huge CPU spikes from attempts to get full table locks.


0

Sounds like your PHP code is converting the string to a integer and chopping off the decimal place. Possibly in the prepare statement. You could try binding the param as a string, or possibly a cast as a (float). Example PHP: $number='2.90'; echo (int)$number; Will output 2 $number='2.90'; echo (float)$number; Will output 2.9 I would just leave ...


1

What you are looking for is in fact possible. What you are looking to do (like many businesses) is build an automatic failover which is often called a hot standby. A hot standby is just a backup slave server that can be switched to either automatically or manually when you need to do maintenance or if your master server fails. MySQL has some great ...


0

Here is my thought: Create a table with all the fields that exist in the csv file. Create a trigger (After insert on the table created in step 1) that calls your procedure that you already have, or let the trigger itself insert new values to the corresponding tables. Load the CSV file to the table you created in step 1 The other tables will be populated ...


1

You are using >= which is "greater than or equal to". <= is "less than or equal to". A trick to remember this is to think of the < or > as a mouth that is "eating" the larger number. 12 > 6: twelve is greater than six. 5 < 18: five is less than eighteen. Or just look at the < or > symbol and realize which side is bigger than the ...


2

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows. SELECT * FROM animals WHERE name IN(SELECT @animal_names); is translated to SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\''); MySQL variable cannot hold table resultset so ...


3

From comments: DISTINCT is very similar to GROUP BY <all selected columns> and getting rid of the temporary table may be impossible when joining many tables as the server needs it to check uniqueness of the returned rows. Covering indexes (Using index) are quite useful when you need to get the top performance. I prefer IN() instead of multiple ORs ...


0

I've made some tests and manage to find some interesting results. You can use the casting operation to solve the problem: SET totalTaxRate = CAST(totalTax * 100 / amount AS DECIMAL(10,3)); You can use the truncate operation to solve the problem: SET totalTaxRate = TRUNCATE(totalTax * 100 / amount, 3); Somehow, using a select after changing the value of ...


1

Unfortunately, all you can do is what said in my earlier post SET GLOBAL innodb_fast_shutdown = 0; Shutdown mysqld delete ibtmp1 Start MySQL DB ALTERNATIVE Attach another volume to the VM and map ibtmp1 to that other volume using innodb_temp_data_file_path (As of MySQL 5.7.1) innodb_tmpdir (As of MySQL 5.7.11) Please these docs carefully. Also, try ...


1

If overlaps are only partial, (i.e., a range may partially overlap another, but no range is a subset of another range), I think the following query will do what you want: SELECT t1.office_type_id, t1.state_id, t1.district_id, t1.office_class, t1.term_end, MIN(t2.term_begin) next_begin FROM terms t1 JOIN terms t2 ON ...


2

Not sure what you are using for this, but apparently it is looking for a very specific answer. Your answer will work, but it looks like they are expecting WHERE City <> 'Paris' This would also be valid: WHERE City != 'Paris' but you would probably get the same error.


0

If OS is Red Hat, it is possible that SELinux is stopping MariaDB from creating its log files. Run getenforce as root. If output is Enforcing then run setenforce 0. Output from getenforce should now be Permissive. MariaDB should start now.


1

EXPLAIN tells you the difference. If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big. Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact: use ...


1

Indexing can have several purposes: - Access your data faster and to accelerate the execution time of your queries - Define the degree of uniqueness of a given column: Should every field be unique? Are duplicates allowed? When you send a request to your MySQL server, it is first assigned to the "parser" SQL which aims to verify the syntax of your request is ...


0

I do not think it will be worthwhile to change the type. You may save a byte or two for the length field of each column value if using TEXT or MEDIUMTEXT, but that will be insignificant compared to the size of your data. VARCHAR and TEXT columns are handled the same way in InnoDB, so there is no reason to switch to VARCHAR either.


1

The keyword DEFAULT is context sensitive within an INSERT statement only (some systems may support it as well within UPDATE). You can't use DEFAULT within the context of calling a stored procedure. Also, MySQL does not yet support default values for stored procedure parameters either. An alternative can be: delimiter // CREATE PROCEDURE mysql.insertTables ...


4

The point of "default" values is that they are the default values. You Don't need to list them in the insert query at all. They'll be inserted automatically. To pass CURRENT_TIMESTAMP as a parameter, you probably need to use the equivalent pseudo-function, CURRENT_TIMESTAMP()... which is the same as NOW() Stop and consider DEFAULT CURRENT_TIMESTAMP ON ...



Top 50 recent answers are included