Tag Info

New answers tagged

0

When one needs to do something more complex that is available in the syntax, it is often useful to 'stage' the data through a temp table: Perhaps: CREATE TEMPORARY TABLE tmp ( ... DEFAULT 'myfile' ... ); LOAD XML ...; INSERT INTO tab1 SELECT * FROM tmp; or: CREATE TEMPORARY TABLE tmp LIKE tab1; LOAD XML ...; UPDATE tmp SET DEFAULT = 'myfile'; INSERT ...


0

Wrong design. When you need one more phrase, you have to ALTER TABLE, which is a costly operation. Instead... Build a new, 3-column, table PhraseGroups with PRIMARY KEY(languageid, phrasegroup). It will have L*P rows, where L = number of languages and P = number of phrasegroups. phrasegroup would be VARCHAR(44) and contain 'global' or 'forum' or ...


0

You show "create table" with MyISAM engine but the error comes from InnoDB so either you changed it or RDS did. From manual: "Tables created in older versions of MySQL use the Antelope file format, which supports only ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT. In these formats, MySQL stores the first 768 bytes of BLOB, VARCHAR, and TEXT columns ...


0

There are a handful of options that are Dynamic, but for the most part they have to be added to the cnf file, which means you need to restart your live databases if you want to use it.


0

Change the ft_min_word_len value to specify the minimum length of the word to be included in a MyISAM FULLTEXT index. ft_min_word_len The minimum length (default: 4) of the word to be included in a MyISAM FULLTEXT index. Note: FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK. ...


0

All uses of that table will run faster (some will run a lot faster) if you jettison id and make name the PRIMARY KEY. Your UPDATE is currently looking in 2 BTrees, plus updating 2 BTrees. The PK change would change those to 1 and 2. More importantly it would simply the row locking. How big is the table? How much RAM do you have? What is the value of ...


3

You might also find the tee command to be of use. Log all output to an operating system file mytabs.txt. mysql> tee mytabs.txt; Logging to file 'mytabs.txt' Issue your show tables command as below. mysql> show tables; +-----------------+ | Tables_in_sport | +-----------------+ | billy | | player | | seasons | | team ...


1

You will notice in the errors this message InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down So please remove the innodb_force_recovery option from my.cnf


4

You can use the pager (see docs) command in MySQL console or use the --pager startup option (docs again) to redirect the output of your commands to an external executable. To use the pager command and list the tables try this: mysql> pager less mysql> show tables You can also start MySQL to send all your output to an external executable like this ...


0

The timings could be bogus because of caching. You probably have the Query cache on. Try again, but with SELECT SQL_NO_CACHE ...; Recommend you add a new possibility to the ENUM behind this: `attempt_1_result` NOT IN ('complete,incorrect,decline,prospecting') OR `attempt_1_result` IS NULL That way you could avoid the NULL test and the OR. AND ...


1

Get rid of UNIX_TIMESTAMP() everywhere. DATETIME fields can be compared directly. Once you have done that, you will probably notice the missing @ that is causing the error. I think it can be simplified down to only: CREATE TRIGGER ... IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month INSERT INTO ...


0

OK, from what I can tell, Percona Audit Log Plugin, has very little in the way of configuration. I looked at McAfee Audit Plugin, but that doesn't seem to work on Debian MySQL. I also looked at MariaDB Audit Plugin, and that seems to have some level of filtering (DDL or DML). Unfortunately it requires 5.5.42 , and most of my DBs are 5.5.40. Still, on my ...


0

This happened to me recently and this thread has been invaluable for helping me understand what's happening. I'm running a LAMP stack on Ubuntu 14.04 with 1GB Ram. My server kept crashing due to web traffic spikes. For me, fiddling with the mysql config file only extended the amount of time I'd experience another random crash. To test and fix this, I ...


1

MySQL doesn't reduce/reclaim the size of previously deleted data. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space. Source: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html To verify MySQL replication integrity, you may want ...


1

With the changing of the clock times on the Master there might be loss of data for the slave a slave may replay a query it already processed Rather than risk either of these extremes, you should schedule a brief downtime. Here is what you should do: STEP 01: Stop all writes to the Master SET GLOBAL read_only = 1; FLUSH TABLES WITH READ LOCK; STEP ...


0

There is one thing that cannot work with MySQL Replication from 5.5 to 5.6 : DATETIME. Why ? MySQL 5.6 DATETIME has a very different byte format because it accommodates microseconds. When the binlog_format is ROW, or when MIXED switches from STATEMENT to ROW, only a 5.6 Slave can unpack the row change from the relay logs. A slave running MySQL 5.5 cannot ...


5

You can also use BIN() function. You still have to LPAD() though, the higher order 0s are not displayed: LPAD(BIN(hash), 64, '0')


2

After a little more messing around I've found that I can skip the CAST() and just use CONV(), but it still seems as though there should be a better way: select conv(hash, 2, 2) hash from `table`; I also am using LPAD() to get the full 64-characters, which would be nice to avoid if possible (I'm hoping there's a function that infer the length from the ...


0

Do you need for it to run in single transaction (and do you use InnoDB)? From the wording I expect the table is MyISAM and all updates lock entire table. InnoDB does not have that problem as updates and selects can run in parallel. Check if you might change the table to InnoDB - it offers this and ACID If you need/have to use MyISAM, you might "split" the ...


0

That's the way InnoDB works anyway. Take a look at the MVCC (multi-version concurrency control - aka record-versioning or record-shadowing) mechanism which most databases use today. If you are doing a long update and somebody wants to read a record, what the person doing the read will see is the database as it was before the update started. Then, when that ...


1

The ".frm" file holds table definition. You might want to check mysql file-types info. Usually when you move data-base physically do following: Shutdown mysql Copy datafiles to datadir make sure mysql has full permissions on it: chown mysql:mysql datadir/ -R start mysql So, make sure that the tables which are not "visible" have proper permissions for ...


1

It sounds like you lack the SUPER privilege (global permission). When you connect to MySQL, run SHOW GRANTS; If you see more than one line, you do not have SUPER. What does having SUPER give you ? The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill ...


1

PROLOGUE Someone asked the same thing of me in my organization because everyone was using MySQL 5.5. All DB servers was upgraded over the past 8 months to MySQL 5.6. Some client applications were being affected by sql_mode change as well. ROOT CAUSE I just found out why what you did does not work and the workaround is very simple. According to MySQL 5.5 ...


1

The output is only showing the current query in a transaction. What else is in the BEGIN...COMMIT? Or are you running autocommit=0? Or =1? SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction. INSERT grabs exclusive lock(s). It sounds like the SELECT already has a lock that the INSERT desires. Perhaps the SELECT ...


0

Just delete ib_logfileX files and restart mysql service. You changed the innodb-log-file-size var to size more bigger than already set it in your mysql daemon.



Top 50 recent answers are included