Tag Info

New answers tagged

0

select usr.nick_name, count(ans.ans_given) from user usr, question que, answer ans where usr.user_id = ans.user_id and que.question_id = ans.question_id and que.right_ans = ans.ans_given group by usr.user_id That was the solution, but how to show 0 if an user did not answer anything correctly?(thanks szakwani)


0

You design is basic and cover 1 user -> many items 1 item -> many comments 1 user -> many comments The question is would you like the user to be able to comment on the same item multiple of times? if yes, you do have a good design else we would need to work on it.


-1

select usr.nick_name, count(ans.ans_given) from user usr, question que, answer ans where usr.user_id = ans.user_id and que.question_id = ans.question_id and que.right_ans = ans.ans_given; it would help if you could create the DDL in sql fiddle and we could have a working environment. If you chose to vote down, kindly leave a comment.


0

You most likely have a .my.cnf file in your home directory, which is specifying a password. The clue here is that when you did not specify a password (sudo -u root), the error message is still saying that you did.


0

What you are asking for can be done by getting the list of all tables from the database. However, you should use the INFORMATION_SCHEMA database. In your particular case, you can use the INFORMATION_SCHEMA.TABLES table. TABLES IN DESCENDING TABLE_NAME ORDER (Database mydb) MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" ...


0

If the range of fees is guaranteed to be fix - as well as the fees themselves, you might be fine with having them as columns in your product table rather than as rows in additional tables. Otherwise, you will need to adjust the schema and your code in order to add a new type of fee. Once, you have separate tables, maintaining, e.g., periods, a particular fee ...


0

Primary keys must be unique. As long as any particular user/user_id can't become more than a single seller/buyer (each), you will be fine. Whether or not that restriction does hold, depends on your domain. It would, e.g., fail, if a user can run multiple shops with each shop's owner being (considered) a distinct seller. (You might, e.g., want to attach ...


0

It really depends on the read-write split on your data in general. If its read heavy, then you save CPU load in RDS by smartly using query_cache. However, I would never recommend turning it to default to ON, but rather use DEMAND (number 2 in RDS parameter) that way you can use SQL_CACHE in your select statements for particularly cumbersome queries to ...


3

This issue was well dealt with in this post by Yves Trudeau who seems to suggest that it is safe - his conclusion is that Conclusion Like ZFS, ext4 can be transactional and replacing the InnoDB double write buffer with the file system transaction journal yield a 55% increase in performance for write intensive workload. Performance gains are ...


2

The only situation I can think of is reloading a large mysqldump. Why ? Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko) From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to Log Buffer Insert Buffer in ibdata1 Double Write Buffer in ibdata1 .ibd file for each InnoDB table Shutting off the ...


0

This is very simply a case where your server does not have sufficient capacity (often disk I/O, but sometimes memory or CPU) to handle the workload. Poorly-written queries and insufficient or sub-optimal indexes are a common contributor, as are applications that give up too soon and re-send the same query that's already running, and an improperly sized ...


0

Go to your home directory cd ~ Create the file .my.cnf Put those lines in it [client] user = <user> password = ***** host = <host> That's it. If you want to keep that name .mylogin.cnf, you could one of the following: Create symlink of .my.cnf to .mylogin.cnf, then mysql dbname Launch mysql with mysql --defaults-file=~/.mylogin.cnf ...


0

If you are looking for a running total for each row, you have to set up a user variable. The purpose of the user variable is to keep the total value after each row like a spreadsheet cell. PROPOSED CODE SET @rt = 0; SELECT *,(@rt:=@rt+IF(transtype='D',MemTot,-MemTot)) RunningTotal FROM member_trans; SAMPLE DATA I am using the first ten(10) rows from ...


0

Would that work? :) It does work in SQL Server 2012 SELECT t.mem_id, (MAX(CASE WHEN t.transtype = 'D' THEN t.MemTot END) - MAX(CASE WHEN t.transtype = 'C' THEN t.MemTot END)) AS Diff FROM ( SELECT 1 AS mem_id, 'D' AS transtype, 1129.80 AS MemTot UNION ALL SELECT 1, 'C', 750.90 UNION ALL SELECT 2, 'D', 568.60 UNION ALL SELECT 2, ...


0

use following command to set login path first mysql_config_editor set --login-path=lgpath --user=root -p password and then use below command to login mysql mysql --login-path=lgpath Help about mysql login path options can bee seen by mysql_config_editor set --help


0

Visit link for more details I have find solution for this error in xampp on start MySQL service we have face some problem Solution for this Error Plugin 'FEDERATED' is disabled. Just open Task Manager and go in Details tab then find mysqld.exe file stop this task. Let's Re-try start MySQL Service and see it's working.


5

MySQL allows you to do GROUP BY with aliases (Problems with Column Aliases). This would be far better that doing GROUP BY with numbers. Some people still teach it Some have column number in SQL diagrams. One line says: Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as ...


11

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms. The reasons people do it: they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code. The reasons it's bad: it's not ...


0

I have an old post where I discussed an actual product I was evaluating : How to properly secure MySQL database?. The product Gazzang (now Cloudera) would basically encrypt the data and the framework requires using specific keys. Even if you copied the files, the keys not being present in the target system made everything inaccessible just from an OS ...


0

I don't have direct experience with a similar setup, but you probably need to set this option on the master services: --log-slave-updates Should slaves log updates to binlog [false] By default, changes made by appliers are not written to the binlog. As Tungsten watches the binlog to detect changes, the master extractor will not see those changes ...


0

The post you link to only discusses capturing a single scalar value into the variable, not an entire result set. You are trying to capture more than one column and presumably more than one row. MySQL variables can only hold one scalar value. It seems like what you are looking for is a temporary table, but on the other hand, your explanation is difficult ...


1

It isn't possible as SQL Server Native Client is a client library for SQL Server, not for MySQL. If you want to access a MySQL server through ODBC, then you need the MySQL ODBC driver (Connector/ODBC).


0

Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation: A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. Could you move the admin field to a separate table which just holds the current ...


0

As a solution for someone who can't get pv to work or for whom pv tells lies. You can monitor the size of ibdata1 file in /var/lib/mysql which contains the data. This will end up the same size (or thereabouts) of the filesize in your source server. If there are many tables you can also watch them appear one by one in /var/lib/mysql/< database name>.


0

Semisync Replication will switch to async on the fly if needed. I mentioned this before in my old post MySQL Replication : 1 Slave / Multiple Masters In that post I said Having this setup gives you block-level replication within both data centers. If you are using MySQL 5.5, you can have Semisynchronous Replication send SQL to the other DataCenter ...


0

The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses. (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.) I would suggest that the ...


0

According to database normalization technique create a separate for city, state and country. However if you're having only few records in company address table and there is no other tables are need address info, I think there is no point creating multiple tables.


0

Please try: (edited 2014-12-18: replaced Left with INNER join): SELECT `pages_users`.`page_id` , COUNT(`pu2`.`page_id`) AS `count` FROM `pages_users` INNER JOIN `pages_users` AS `pu2` ON `pu2`.`user_id` = `pages_users`.`user_id` AND `pu2`.`page_id` = *PAGE_ID* WHERE `pages_users`.`page_id` != *PAGE_ID* GROUP BY ...


0

COM_SET_OPTION is a special command in the MySQL protocol which is currently used exclusively to enable or disable multi-statement support as detailed here. Some database clients (such as certain versions of the MySQL libraries for PHP) will do this every time a new connection is established.


0

I have an old post from March 2012 that limits a specific user's connections by the hour How can I limit MySQL connections? You also need to remove anonymous users : Cannot drop anonymous user from mysql.user Get rid of test user access : MySQL : Why are there "test" entries in mysql.db? Get rid of all users with no password with DELETE FROM ...


0

You are simply not going to believe this. The root cause of your problem is this: ROOT CAUSE User=mysql max_allowed_packet=500M Your max_allowed_packet is perfectly fine. THE PROBLEM IS User !!! SOLUTION You can either Remove User=mysql from my.cnf Comment User=mysql in my.cnf Change User=mysql to user=mysql in my.cnf and start mysql. GIVE IT A ...


0

Unfortunately you insert nav_date greater than '2014-01-01', so the partition wont accept the date greater than this. Because in this condition you have partitioned only values less than 2013. So alter the table by adding another partition.I think it works better.


0

If you don't like using primary keys that are auto generated, there is another way. Instead of indexing the userID (which is large and slow to index, because it is a string) you can instead index the hash value of userID. You can then do: CREATE TABLE Users ( ID BIGINT , Username VARCHAR(255) ) And you can find the user with: $user = ...


1

I hate precision-based issues. I dealt with one before: Data Truncated for Column You may have do the ALTER TABLE manually CREATE TABLE test_new LIKE test; ALTER TABLE test_new MODIFY foo DOUBLE; INSERT INTO test_new (foo) SELECT CONVERT(foo,DOUBLE) FROM test; RENAME TABLE test TO test_old,test_new TO test; DROP TABLE test_old; or CREATE TABLE test_new ...


0

Maybe this post could be somehow useful for your question - as far as I had experience with till now (even in ERP db we serviced for nearly 5 years) type FLOAT or DOUBLE was almost never used. Instead of it all the fields storing values like cost prices, purchase prices, stock item characteristics, warehouse, production, accountance data - always had fixed ...


2

SUGGESTION #1 Based on the WHERE clause, I highly recommend a compound index ALTER TABLE mytable ADD INDEX (md5,created_at); That way, all parts of the WHERE clause are answered by the index. SUGGESTION #2 Since you are imposing a limit of 200, collect the 200 keys first, then join the keys SELECT B.* FROM ( SELECT id FROM mytable WHERE md5 = ...


1

With innodb_file_per_table configured, the network_type table will exist in a physical file under the DB folder. For example, let's say your table has the following attributes: Table name somedb.network_type datadir is /var/lib/mysql You will find the physical file /var/lib/mysql/somedb/network_type.ibd That file is the home of all data and index pages ...


1

Go for md5(15) because it has better cardinality and ref always beats range in performance. You wrote LIMIT 200 without ordering clause. Just heads up.


0

You could try dbvisualiser, a - in my opinion - nice multi database, multi platform tool for SQL databases, that has a free and a licensed version.


0

Your update is the correct way to insert data. Using INSERT INTO ... SELECT ... requires a source table and is not meant to be used with static values. Using INSERT INTO ... VALUES ... is for inserting static data, such as CCC, into the table. The two are not interchangable and will cause the issues you've been seeing. You can check the documentation on ...


0

OBSERVATIONS According to the MySQL 5.0 Documentation As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate ...


1

Make the sure the datadir in new cnf file is the same as the old one then restart the service and try again.


0

Hope you are accessing using following command from Linux Console mysql -uroot -ptest -hHost_Name then in mysqlprompt use following command to take mysqldump mysql> system mysqldump -uroot -ptest -hhost_name --events --routines --single-transaction --databases sakila >sakila.sql Now while being in windows and you want to take mysqldump of ...


1

You cannot run OS commands in a MySQL CLI nor in MySQL Workbench. Why would you do that (except for trying to hack a machine via an SQL connection)? If you need to run an OS command, open a normal shell window (that still stands). Update: The system command only works on Unix like OSes (http://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html), e.g. Linux ...


0

I also recommend (as mentioned in first reply) to monitor data consistency across all servers binded to your master, becouse for variety of reasons sometimes replicas are not 100% copies. Some nice PHP script, that checks data integrity can be found here: https://github.com/KredytyChwilowki/MySQLReplicaIntegrityCheck It is an open source tool, very ...


0

To find out path to a socket mysqld process has opened you can use lsof: # lsof -U -c mysqld -a COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 18149 mysql 16u unix 0xffff880037a7a700 0t0 126146 /var/lib/mysql/mysql.sock


0

Usually, when mysqld is still running and their is no socket, there is only one thing to do. If you have root@127.0.0.1 defined as a user, you can do a clean shutdown like this mysqladmin -h127.0.0.1 -P 3306 --protocol=tcp -uroot -p shutdown When the propt cvomes back, do a normal startup service mysql start This will recreate the mysql.sock file. I ...


1

I followed @ypercube's suggestion and just rewrote both queries - turns out there wasn't a relationship in the database that was needed to tie two tables together, and when there were multiple relationships on a set of columns MySQL did what @JNK said and was non-deterministic and grouped inconsistently. If you run into something like this, check your table ...


1

First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko) Strictly in terms of InnoDB, there is only lose data on power failure if certain settings as disabled. There are two parts of the InnoDB Storage that, if disabled, can cause data loss on power failure. Double Write Buffer A record of all data and index ...


0

Please try, if you could, this approach: UPDATE ProductCategories, Products SET ProductCategories.categoryid = CASE LEFT(UCASE(LTRIM(Products.productname)), 2) WHEN 'AP' THEN 1 WHEN 'TO' THEN 2 WHEN 'WA' THEN 3 ...



Top 50 recent answers are included