Tag Info

New answers tagged

0

Read Joel Spolsky's "Joel on Software" - revelation to me when I read it, software development as it should be done. Bug tracking - he recommends a system for even one person. Source control - set up git (or Mercury) or Subversion on a server. Stuff on hiring also. Also, if I were you, I'd read Eric Sink's "On The Business of Software" - much of it is ...


1

Use the typical trick of sum(IF the column is the right one, use it, if not read 0: SELECT sum(IF(variable_name = 'Com_select', variable_value, 0)) as `Total reads`, sum(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes` FROM information_schema.GLOBAL_STATUS;


1

You have 2 temporary tables being created. It is highly likely that the big difference comes from the fact that MEMORY, the engine used by MySQL to create temporary tables in, well, memory does not support TEXT/BLOB data types (independently of max_heap_table_size), so they are forced to be created on disk, having a great slow down . Profiling your query as ...


1

Substitute $n with the desired number: SELECT c1.id FROM consecutive c1 WHERE $n = (SELECT count(*) FROM consecutive c2 WHERE c2.id BETWEEN c1.id AND (c1.id + $n - 1) ); This does a full table scan for each number, combined with a range scan. It finds all possible combinations (returning the first figure in ...


0

first of all, please have a look at this recent response about multi-master replication, where I remark how likely is this setup to create problems and some alternatives. This was probably a direct or indirect cause of your desync. Bidirectional sync is supported in pt-table-sync, but not in a replication environment, and only between 2 servers. You must ...


1

This is a question more for stackoverflow than for here, but it is great that you asked here first because we would tell you that do not need to handle that on PHP code. MySQL has a syntax called INSERT ... ON DUPLICATE KEY UPDATE that does exactly what you want, in only 1 query instead of 3. There is also REPLACE, although it is slightly different (REPLACE ...


2

First step: SELECT testname, entry, MAX(`value`) as max_value FROM your_table GROUP BY testname, entry Second step: SELECT MAX(max_value), MIN(max_value), AVG(max_value) FROM ( /*here we insert the query from the first step*/ SELECT testname, entry, MAX(`value`) as max_value /*rename the column so we can conveniently access it from the outer query*/ ...


1

There is a bug on 5.6.10 version, fixed on 5.6.11 Microsoft Windows: In Shared Memory mode, the MySQL Server could crash when receiving requests from multiple threads. (Bug #13934876) http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-11.html


1

select b.*, s.service_name from branches b, branch_services bs, services s where b.branch_id=bs.branch_id and bs.service_id=s.service_id


1

I have a single MySQL query that do all the heavy lifting SELECT TimeDisplay FROM ( SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'', IF(dy=1,'1 day ',CONCAT(dy,' days '))), IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours '))), IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))), IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),' ',' ...


1

Let's say the table is called mydb.mytable This query SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1; can dramatically be improved if you index the table. There is one of two techniques you can try when indexing the table Technique #1 ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5); Within the ...


0

I was going to write a long post telling you that there are basically 3 ways to implement polymorphic associations, but someone (@Bill Karwin) has already done it better in a more compact way: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32 Your original solution is the first one presented on the slides, the one you propose is the 3rd ...


0

You noted on the Percona discussion group that you found the culprit to be the thread pool. Then Laurynas Biveinis replied: Jul 16 (7 days ago) Ah. Your my.cnf has thread_pool_size = 2 which seems too low and possibly explains the poor performance of thread pool. If you want to use it, make sure to tune its settings, ...


1

Doh! @jynus was exactly correct. Here is the fix: $eventid = intval(mysqli_real_escape_string($dbconnect, $_POST['eventid'])); $sql="DELETE FROM Events WHERE EventID='$eventid' LIMIT 1"; if (!mysqli_query($dbconnect,$sql)) { die('Error: ' . mysqli_error($dbconnect)); } echo mysqli_affected_rows() . " Record(s) Dropped";


1

Well, that is handled in memory and transactionally stored on disk on the transaction log (by default, the ib_logfile0 and i_blogfile1. But if you just want to know its value, you can check SHOW ENGINE INNODB STATUS or simply: mysql> SHOW GLOBAL STATUS like '%lsn%'; +----------------------------+-------------+ | Variable_name | Value | ...


3

I highly recommend that you do not run GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'; This user has the SHUTDOWN privilege, which can allow the user shutdown mysql remotely with mysqladmin -hIP_of_DB Server -uusername -p shutdown You also do not want the SUPER privilege given remotely to just anyone. Why ? The SUPER privilege enables an account to ...


1

MariaDB had the wonderful idea of jumping from 5.5 to 10.x, making some applications to break. If you look at the source code for MHA, the place where you are getting your error, you can read: # The following servers can not be master: # - dead servers # - Set no_master in conf files (i.e. DR servers) # - log_bin is disabled # - Major version is not the ...


0

You can create a username and password also provide grant access to them. so that all clients can access the mysql from their local by using the username and password provided by you. CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON * . * TO 'username'@'%';


1

Set lower_case_table_names=1 on the Linux server before importing, this will convert all databases to lower case and new queries will use lower case, too. You can see it here. Your problem arises from the fact that the default value on Mac is 2 (names are not case sensitive when queried, but they are when stored) but 0 on Unix/Linux (names are always case ...


0

When you run a query in MySQL Workbench you get actually 2 times in the action output window. The headeing also shows what they are for: There's the execution time on the server (which is probably your ~16s) and the, usually much larger, fetch time, to transport the data from the server to the client. With millions of records that can easily take minutes ...


0

Please try the below query, REPAIR TABLE 'nagios_servicechecks' Try the above query in terminal or check the below link to repair table or databases via phpmyadmin http://php.about.com/od/mysqladministration/qt/repair_mysql.htm


1

GOT IT! I had to explicitly set my PHP variable types to integer after normalizing them from the Http POST function... settype($foo, "integer"); ...and then use them in the MySQL query.


0

Maybe this: $sql = "INSERT INTO Taskers (Taskers.MemberID, Taskers.EventID) VALUES (".$memberid.",".$eventid.")";


1

Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive. However, depending on the variable innodb_autoinc_lock_mode, other non-trivial inserts like insert... on duplicate ...


0

I have dealt with Circular Replication with three nodes before. Here are my posts: May 07, 2012 : Setting Circular Replication in mysql (From Scratch) Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology? (Takes a Master with Two Slaves and converts the topology to Three-Node Circular Replication) I remember a client I ...


0

The 16.04 seconds is a timer on the client that measures the amount of time that passed between the call and the execution of the query (if you want to be more specific than that, the wall clock time between calling start_timer() and mysql_end_timer(), something that can lead to hilarious results like this one I got). The reason that you think that it took ...


0

I have executed the very same query on 5.6.14 and got a positive result: alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE; Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 So I am sure that this kind of operation can be done online. You are probably experiencing some of the ...


0

The answer to this is very straightforward. The two slaves must have the same server_id. I wrote about this 2 years ago (Screwed up replication by sharing server ids). In that post, I quoted Baron Schwartz's blog Pop quiz: how can one slave break another slave. The quick-and-dirty solution ? Change the second slave's server_id. For example, if the master's ...


0

InnoDB tables, unlike MyISAM*, cannot be "just copied away", as part of its data dictionary (and potentially other structures the table is depending on, like the merge buffer) are located in memory (if the server is running) and in the common/main tablespace, a.k.a. that large file called ibdata1. If you are using Percona Server >=5.1 or MySQL >= 5.6, there ...


0

I agree with previous answer from Jynus. MariaDB is pretty straightforward to install and there are many how to topics to do inplace replacements of MySQL (You dont need to worry much, all the utilities and commands you know are the same) For example, from MariaDB site itself MariaDB Upgrade. You can also simulate a MariaDB/Percona/MySQL configuration with ...


2

Suppose you have mydb.mytb and you want to create mydb.mytbcopy I have five(5) approaches to doing this copy APPROACH #1 In the mysql client, run the following USE mydb CREATE TABLE mytbcopy LIKE mytb; INSERT INTO mytbcopy SELECT * FROM mytb; APPROACH #2 MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" mysql ...


1

There are multiple reason why you wouldn't want to do multimaster replication in a circular fashion, but most of them can be summarized in one: You will have 3 different single points of failure. Check this article (which is exactly what you asked for, but probably not what you want). Standard replication is very prone to drifts in data, as it is ...


0

You have not uninstalled MySQL-server-5.6.19-1.rhel5.x86_64 and MySQL-server, maybe others. Please do not remove manually package files, as that would only make thing more problematic. I quote a similar answer I wrote some time ago: Search for mysql-related packages that are now installed: rpm -qa | grep -i mysql Delete any packages like mysql, ...


0

Your current settings over-commit memory. Try echo 1 > /proc/sys/vm/overcommit_memory or lower mysql memory usage


1

Sounds like you just need to do an INNER JOIN. And it also sounds like you don't know what one is? Recommend you read up: http://www.mysqltutorial.org/mysql-inner-join.aspx In your case it would be a simple: SELECT EventID, CONCAT(m.Title, " ", m.LastName) AS Member FROM Events e INNER JOIN Tasks t ON e.EventID = t.EventID INNER JOIN Members m ON ...


0

Is this what you are looking for? select * FROM Comment cm left join [Delete] del on del.[Target]=cm.Id where cm.[Target] = '".$_GET[''pp'']."' and Type = 'comment' and del.[Target] is null and deleted=0


0

I have had experience with something similar (I believe), a scientific database that could have thousands of different field types and new ones coming along all the time. Here is how we solved it, similar to what it appears you are attempting. The problem is (as someone suggested) reconstructing the rows into a sensible format and displaying in an ...


1

In the where clause, trade the Ic alias with IFNULL(c.new_ic,c.old_ic). SELECT `c`.`fullname` AS `fullname`, IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`, `bla`.`loan_no` AS `Loan_No`, `bla`.`total_loan_amt` AS `Total_Loan_Amt`, `bla`.`monthly_installment` AS `Month_Inst_Amt`, `bla`.`disb_date` AS `disb_date` FROM (`customers` ...


2

You are better off dropping and recreating. Why ? ANALYSIS Let's take the hypothetical example from your question CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ); This is literally a pure Cartesian Product. Let's say tableA has 2,000 rows and tableB has 5,000. A Cartesian product would result in result_table having1,000,000 rows. Now ...


3

Try using MySQL's built-in ExtractValue function, which does all the XML heavy lifting for you: SELECT id, name, ExtractValue(elements, '/elements/video/url') url FROM jos_zoo_item; Ref: http://dev.mysql.com/doc/refman/5.6/en/xml-functions.html


1

You be able to delete it without issues. Why ? Look for the file relay-log.index and display it # cat relay-log.index You will see a list of relay logs MySQL Replication needs and rotates. If relay.000724 is not in relay.index, you can delete relay.000724 without breaking anything. Why is is still around ? It could be anything. Between setting up ...


0

You should look here and here. It's better for permanent settings like you want to set up your my.cnf file to do what you want by default - then you don't run the risk of forgetting to run your SET statements on startup. You might also want to peruse the docco (I've pointed you to 5.6, but you can choose based on your own server version).


1

The --single-transaction option of mysqldump does not do FLUSH TABLES WITH READ LOCK;. It causes mysqldump to setup a repeatable read transaction for all tables being dumped. From your question, you stated that the mysqldump's SELECT for the db_external_notification table is holding up hundreds of INSERT command to that same table. Why is this happening ? ...


0

You have multiple values in the DATE_ADDED field for each distinct pair of ORDERS_ID/VALUE results you see returned when you execute the query with only two columns selected.


1

It seems you want to keep you PHP code simple and make the SQL code more complicated, while it should be otherwise. Example: lets say that - user A chooses: "color = 'red', size = 'Medium', category = any" - user B chooses "color = any, size = any, category = 'Tshirts'" The conditions could be respectively as simple as: -- userA WHERE color = $color AND ...


2

Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines: CREATE TABLE Category ( Category_ID INT NOT NULL AUTO_INCREMENT primary key, Parent_Category_ID INT NOT NULL REFERENCES Category(Category_ID), Description varchar(255) not null ) ...


1

Check out the manual here. However, if you're transferring between 32/64 bit, then you'll have to use mysqldump, see here which gives you a text-based dump of the SQL statements you'll have to run to install your database. The commands for mysqldump vary according to whether you're using MyISAM or InnoDB, so just be aware of that. On Linux, there are more ...


0

I looked at this again, and I figured out how to get the answer, but it's a horrible hack (DDL and DML below - just changed values from my last answer). The reason that I changed some values was that on running my queries with my orignal data, there are 2 results returned for ticket 3 by the second query which is incorrect. This was because there was a match ...


0

You do not provide much information about your system or program, but here are very general pieces of advise to improve the writing performance of your system: Number one reason why MySQL can be slow is because it has inappropriate queries: are tables indexed correctly? Do you have a fast and flexible table design? Are you using memory efficiently? ...


3

Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ... If you feel intimidated by SQL, an ORM can help ...



Top 50 recent answers are included