Tag Info

Hot answers tagged

2

\G is a delimiter exclusive to the mysql client I personally do not use MySQL Workbench, but many others have done the following Mar 12, 2010 : How to get a table creation script in MySQL Workbench? Mar 28, 2012 : \G like vertical output in MySQL Workbench for show engine innodb status But here is the Blog for MySQL Workbench Team ( MySQL Workbench: ...


2

This is a job better done on application layer. Databases are not suited for this. Just do SELECT category_id, set_order FROM your_table ORDER BY category_id DESC; Then write a function that rearranges the array returned by above query. For fun I did it in MySQL, just to let you see, how complicated this can be. So you see it's doable, note though, ...


1

Just create a trigger to run before insert to generate the UUID for the given column. CREATE TRIGGER before_insert_mytable BEFORE INSERT ON mytable FOR EACH ROW SET new.LogID = uuid();


1

The solution appears to be presented to you multiple times in the log file you posted. Please use mysql_upgrade to fix this error. mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or ...


1

The benchmark you cited is using the term "select time" to mean the amount of time that elapses between when the time when the query is sent to the server for execution and the time when the first row is available for retrieval. The "fetch time" is the difference between when the first row is available to the client and the last row has been retrieved by ...


1

InnoDB InnoDB has to lock up because you do not have it configured properly. You have to realize the framework behind the InnoDB Here is a Pictorial Representation of InnoDB from Percona CTO Vadim Tkachenko Looking at your my.cnf, there are many settings you do not have enabled or is underconfigured innodb_buffer_pool_size : 400M is your setting ...


1

QUERY #1 position the user 2 has if I sort by time and server = 1 SET @given_user = 2; SET @given_server = 1; SET @pos = 0; SELECT * FROM ( SELECT (@pos:=@pos+1) pos,User FROM playerstats WHERE server = @given_server ORDER BY time DESC ) A WHERE User = @given_user; QUERY #2 if I sort by the sum of the time of a user SET @pos = ...


1

You may need to try something a little unorthodox service mysql stop Open mysql-bin.index in vi Change the contents to this ./mysql-bin.000313 ./mysql-bin.000314 ./mysql-bin.000315 Save mysql-bin.index service mysql start Login to MySQL and run SHOW BINARY LOGS; SHOW MASTER STATUS; This stunt worked a few times for me. Give it a Try !!!


1

SELECT t1.EmployeeID, t2.EmployeeName FROM Table1 AS t1 JOIN Table2 AS t2 ON t1.EmployeeID = t2.EmployeeID;


1

You don't use single quotes ' to quote identifiers in MySQL. You use a backtick ` or you can use nothing at all, if the identifier isn't a reserved word. 'mid_initials' (255) IS NULL, Should be `mid_initials` VARCHAR(255) DEFAULT NULL, ...although using a VARCHAR(255) seems unnecessarily large.


1

The datatype int doesn't accept a data length. int is just int. So, this should get you going: CREATE TABLE clteacher ( `id` INTEGER AUTO_INCREMENT PRIMARY KEY ,f_name VARCHAR(255) NOT NULL ,mid_initials VARCHAR(255) NULL ,l_name VARCHAR(255) NOT NULL ,saturation VARCHAR(2) );


1

HERE ARE THE FACTS Here are the two INSERTs insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561) insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', ...


1

Condition 1 : You can define a composite primary key such as : CREATE customerItem ( customer_id INT, item_id INT, view_date DATETIME, PRIMARY KEY (customer_id, item_id) ) You can then use INSERT ... ON DUPLICATE KEY UPDATE such as : INSERT INTO customerItem (customer_id,item_id,view_date) VALUES (123,999,"2014-12-22") ON DUPLICATE KEY UPDATE ...


1

I think your approach is reasanoble. Because it would show online data (no need for background processing) and would only use resources from active transactions (customer not seeing anything will not need to have their data reprocessed). I would do it using triggers. You would not have to change your existing code and you could disable/enable this new ...


1

"Does minimizing the size of the database come at a cost of lower performance?" Typically databases are IO limited, unless they are regularly recalculating reporting style queries. (in which case pre-calculated views can be added to again shift cpu to IO.) Minimizing the size of the database typically minimizes the IO needed against the disk, as a higher % ...


1

Why generate a temporary that drops on disconnect ? You would have to reload the table of ids, which requires extra code to manage the emptying and reloading. If you create a subquery that limits to 100 ids at a time, you could join to the cache_table: Just create an inline query for the first 1000 rows, then join SELECT ct.column_names FROM (SELECT id ...


1

You will have to treat the view as a table. The information_schema already does If you run SELECT table_name FROM information_schema.tables WHERE engine IS NULL; you get all the views. Just grant SELECT on the view to the user as follows GRANT SELECT ON `myDatabase`.`fordibenForYouTable` TO 'thisUser'@'localhost' ; Once you do this, you should have ...


1

You have tmp_table_size set to 16G and max_heap_table_size set to 8G. Way Too Big !!! If you have temp tables that exceed 8G, you can do one of three(3) things SUGGESTION #1 Use smaller values. Set tmp_table_size and max_heap_table_size to 16K. That way, the temp table will go to disk faster. BTW Yes, they should be the same value. If you don't set these ...



Only top voted, non community-wiki answers of a minimum length are eligible