Tag Info

New answers tagged

1

Install percona-toolkit. Then you can use pt-online-schema-change. I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, ...


0

If your auto-increments do have a meaning outside of an arbitrary indentifier, maybe they shouldn't be auto_increments, but simple int or bigint unsigned. You can drop the auto_increment particle and just insert max(id)+1, but that would create you all sorts of concurrency problems (that the auto_increment solves for you automatically). You could store the ...


0

This kind of sounds like poor flushing behavior, ideally you'd be seeing consistent IO not stretches of 90 and 25. What is the adaptive flushing method in use (or is it off)? keep_average? Is flushing neighbor pages enabled? Sometimes this helps, sometimes this really isn't efficient to do. Generally, its good on spinning disks so probably not the issue ...


2

I've written this in SQL Server so you may have to translate to MySQL but this should work for you. CREATE TABLE #ExcludeList (ExcludeVal varchar(50)) CREATE TABLE #VideoList (VideoName varchar(500)) INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1') INSERT INTO #VideoList VALUES ('This is a prob1'), ('This is clean'),('This is ...


0

Here are some disadvantages For you MyISAM users out there, concurrent inserts are prohibited. Data changes cannot be read unless you use mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose. BLOB data is written "as is" rather than the SQL that created the BLOB For more information, see the MySQL Documentation


0

Cleanest way is to add little more storage, start, and then issue purge binary logs to name_of_binlog_XXX;. If you can temporarily move some files off of the volume that will work, too. Or, with little risk, move an unused MyISAM files (frm, MYD, MYI, etc) from unused DB, start, purge, and copy it back. If this is not an option, you're looking at manually ...


0

Before deciding on value for max_connections I suggest that you look at WHY it reached the max_connection condition. A bunch of sleeping/idle connection due to loose coding is very common cause. Check if you have proper value for wait_timeout and adjust if this is your symptom. Besides that, I would check for INSERT/UPDATE query that holds table locks for ...


0

You can skip table name prefix in the status column, You can have all the tables with same column name and same datatype, that will make your naming uniform through out the tables in the database I have seen Database with 2000 of tables, and most of them have column status with same datatype. TINYINT in our case. Let say a query. SELECT ...


0

I think you're mixing up Java (other language?) with MySQL's stored procedure language. Look here and here. Check out this by Guy Harrison, the guy who wrote this book.


0

This could become confusing - but it's not difficult to make it work if you really wanted to. Say you had two tables, TASK and PROJECT. If both tables have fields called status and id, you can still access them without conflict by referring to them as either TASK.status or PROJECT.status. E.g: SELECT TASK.id, TASK.status, PROJECT.id FROM TASK, PROJECT WHERE ...


0

You can use the below comment to check the connections: SELECT count(*) FROM INFORMATION_SCHEMA.PROCESSLIST; SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST order by DB; For MySql you can have the below value to optimize the performance innodb_log_file_size = 512M max_connections=2000 max_allowed_packet=64M wait_timeout=604800 key_buffer = 384M table_cache ...


0

If you move your datadir, you not only need to give the new datadir permissions, but you need to insure all parent directories have permission. I moved my datadir to a hard drive, mounted in Ubuntu as: /media/*user*/Data/ and my datadir was Databases. I had to set permissions to 771 to each of the media, user and Data directories: sudo chmod 771 *DIR* ...


1

You literally do not need a third party tool. STEP 01 Find out the path to the data by running mysql> SHOW GLOBAL VARIABLES LIKE 'datadir'; For this example, let's suppose it is C:\Program Files\MySQL\MySQL 5.6\data STEP 02 Pick a destination folder to copy the data directory For this example, let's pick D:\MySQLBackup STEP 03 Create batch file ...


1

As you say, temporary tables are only visible within the session, and materialized views are not available in MySQL. There are several options (including the ones you have mentioned). I will name your original query as SELECT ...: The WITH Oracle keyword is equivalent to CREATE TEMPORARY TABLE: CREATE TEMPORARY TABLE savedquery AS SELECT ...; -- you ...


0

If you want to hunt down what key combinations are preventing the unique key from working, run this SELECT add1, city, state,COUNT(1) rowcount FROM mytable GROUP BY add1, city, state HAVING COUNT(1) > 1; To see the actual rows, run this SELECT A.* FROM mytable A INNER JOIN (SELECT add1, city, state,COUNT(1) rowcount FROM mytable GROUP BY add1, city, ...


2

I might be guessing on this one, but I think the key distribution must be insane. POSSIBILITY #1 Please run this SELECT * FROM ( SELECT IFNULL(user_id,-1) user_id,COUNT(1) prod_count FROM products GROUP BY IFNULL(user_id,-1) ) A ORDER BY prod_count DESC; If you can send the output to a text file, look for user_id 12 and compare its prod_count to ...


0

got it working but there might be better ways CREATE PROCEDURE simple6(IN name VARCHAR(20)) BEGIN SET @sql_text = concat('SELECT endtime INTO @endtime1 FROM ',name,'big order by endtime DESC LIMIT 1'); PREPARE stmt FROM @sql_text; EXECUTE stmt; if @endtime1 IS NULL then SET @sql_text2=concat('INSERT INTO ',name,'big (starttime,endtime) SELECT ...


3

Because the value is incremented outside the scope of the transaction. This is the way you want it to work, so that separate transactions can happily insert rows or roll back without waiting for each other. If you care about gaps or want different behavior, stop using the built in and roll your own...


0

This line set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1); isn't returning any rows at all, therefore @URLID retains the value set in the preceding line i.e. -1.


1

This is related to a bug with new installs of mysql on debian wheezy whereby the error.log file is owned by root.root, as per this: http://tipstricks.itmatrix.eu/?p=1352 I fixed it with chown mysql.adm /var/log/mysql/error.log


0

According to the MySQL Documentation on --skip-name-resolve Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost. See Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”. Depending on the network configuration ...


4

There's no UNIQUE key on your table, only a PRIMARY KEY on an AUTO_INCREMENT column, which never trigger the ON DUPLICATE KEY because, as you probably already know, you're passing id = NULL. Change this: KEY `unique_index` (`user_id`,`list_type`,`s_object_id`,`group_id`) to this: UNIQUE INDEX `unique_index` ...



Top 50 recent answers are included