Tag Info

New answers tagged

0

Here is an overview of methods to generate random unique ids in mysql, by Rick James: http://forums.mysql.com/read.php?24,425424,425491 The overview is quite comprehensive, gives 5 different strategies, lists pros and cons for every one.


0

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons So there are good reasons to use prepared statements: Save on query parsing Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of using prepared statements: Query cache ...


1

I would suggest normalizing data and splitting the table into the following two tables: CREATE TABLE `file_data` ( `data_id` bigint(20) NOT NULL AUTO_INCREMENT, `hash` char(40) DEFAULT NULL, `size` bigint(20) unsigned DEFAULT '0', PRIMARY KEY (`data_id`), UNIQUE (`hash`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `file_names` ( ...


0

Please try the following. If you need most recent date for every song over all meeting instances: select song_id, max(meeting_date) from song_instances si join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id group by song_id If you're interested only in particular meeting (meeting_id = 4): select song_id, max(meeting_date) ...


0

We can use MySQL CREATE TABLE AS statement to create a TABLE_B from existing TABLE_A by copying the existing table's columns CREATE TABLE TABLE_B AS SELECT id, name, SUM(cost) AS 'cost', organization FROM TABLE_A GROUP BY name, organization;


0

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way: SELECT o.* -- Change this to only the columns needed. FROM objects AS o -- If there are data needed from other tables, -- join here. WHERE ...


1

I've been having the exact same issues on my OpenVZ VPS's recently. Both running Debian 7 (official OpenVZ template), both with MySQL 5.6.23 from the Dotdeb repository. Random InnoDB corrupted tables, with the dump in the error log showing strange data that is definitely not part of any of my own databases, nor any files stored in my container. Both started ...


0

As long as you have a date field that marks the time the score changed, you can perform a versioned query to return the score that was in effect at any given time. Here is a recent answer that describes the query.


0

Sounds like a single table in a single database. It sounds like you need three columns: account, date, score. The PRIMARY KEY might be (account, date). 10K rows is "small" for a table.


2

There is essentially no difference between table and db.table. Once the tables (for SELECT or for INSERT) is opened, it is just a table, regardless of what database it is in.


0

Yeah, what the heck is tmp_user? This smells like an attack. And I suspect it was successful. He probably already got in -- all he had to do was to try different flavors of the INSERT based on different versions. The one successful insert would suffice. Then he RENAMEs tables, does damage, rename tables back, DROPs evidence of his naughtiness, and ...


0

Be sure skip_name_resolve is OFF. GRANT access for the users from their hosts. Remove non-root grants for localhost (when you want to really force them to convert). I assume you have user(s) that are GRANTed access only to the application database(s).


0

Consider turning product_id_CSV into IN ( '...', '...', ... ) and then batching the work -- instead of looping.


0

A different approach to the question/answer: What is cached when you run the query? If what you "need" is cached in RAM, there is essentially no difference is speed. You "need" m "rows" from the index; these will be blocked and consecutive (as @jynus discusses). m rows of data. Because the rows were inserted essentially in t order, they will also be ...


1

If we assume you have the following databases: Master (DB0); Slave 1 (DB1); Slave 2 (DB2); Slave 3 (DB3); The only way for a 'slave' (e.g DB1) to update the 'master' (DB0) is if the 'slave1' (DB1) is also acting as a master to the 'master' (DB0). (commonly known as Master to Master Replication) You can check if this is the case by connecting to your ...


0

I realized one thing: I can easily obtain the ID of the combination by other means, so what I need most now is to obtain the array of categories, which is best done by the SET approach suggested by Rick James. However, I don't want to delete this my answer, because it best answers the question as written (especially the EDIT in the question). It's below. ...


1

In addition to what Derek says, kill 119634; -- Since it is in Sleep mode, this will disconnect the client. That, in turn, terminates the runaway transaction. look in your code for autocommit=0. Don't do transactions that way; it leads to the problem you are experiencing. (No, I can't be sure this is the cause this time.)


0

Check under /var/log/mysql/ like your my.cnf says. SHOW VARIABLES LIKE '%log%'; -- to see if the settings really took effect. Is there a /etc/my.cnf file? Look at the arguments on mysqld that is running to see if they override anything. Did you restart mariadb?


1

You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open. If you are running in the default REPEATABLE READ transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE ...


0

I finally figured that joining these tables would not bring a solution. I solved it doing this: SELECT S.schaal, W.wnr, W.wnaam, W.salaris FROM Werknemer W, S_schaal S WHERE W.salaris >= S.ondergrens && W.salaris <= S.bovengrens;


0

The functionality you are looking for goes under the name UNPIVOT. I'd suggest you have a good search on that subject and post a new question if you have further, specific question.


1

Broadly speaking, on a MyISAM Table with a range scan, the process is: Find the first Index result using the BTREE (inside the .MYI file) and access the row result (on the .MYD file) - Handler_read_key Get the next result, using the index (and in the same order), until the value retrieved is larger than the one defined (multiple instances of ...


0

OP said: "However I was looking for a "just click and click" solution if you know what I mean. " You can also right click the table you are interested in via the schema inspector tab on the left, and select the option: "Select Rows - Limit 1,000" for a quick view, obviously limited to 1,000 results.


0

MySQL security hasn't been every a particular strong point of the server software. That is why most people recommend not expose the MySQL port outside the internal network/firewall. Like any other software, there has been records of vulnerabilities found. Having said that, MySQL has a basic protection against port-scan attacks, as it maintains internally a ...


1

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1. Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko) You cannot recover just a database. You must restore the entire data ...


0

This answer speeded up everything a lot: http://stackoverflow.com/questions/2167522/innodb-takes-over-an-hour-to-import-600mb-file-myisam-in-a-few-minutes I simply SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; at the beginning, and COMMIT; SET unique_checks=1; SET foreign_key_checks=1; at the end. Now it took 3 minutes. ...


0

I will delete this answer. Just comment, or paste the error message, What is not working for you ? As I see nothing wrong. I am using innodb_version | 5.5.41 mysql> create table skill ( id bigint(20), description varchar(255)DEFAULT NULL, name varchar(255), primary key(id) ); Query OK, 0 rows affected (0.13 sec) mysql> desc skill; ...


0

Do you really think that you are going to need millions of skills? I would do CREATE TABLE skill ( id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, description VARCHAR(255) DEFAULT NULL ); And as MySQLRockstar says - what's wrong with this? CREATE TABLE skills ( id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, ...


1

As always, a few minutes after posting a question, I found the answer myself. The issue was SELinux. It was set on enforced; permissive made it work instantly. Now I'll have to figure out a way to make it work with the enforced mode. The following appeared in the audit.log: type=AVC msg=audit(1429781752.260:6738): avc: denied { name_connect } for ...


0

Just my 2 cents: You could set up a MySQL master-slave replication, the master being inside the internal network and the slave on the external site. You could also do a nightly mysqldump of the wiki, sftp it to the external site via a cron job and restore it there, but it looks like an ugly and clunky solution to me. Perhaps the simplest thing would be to ...


0

This shouldn't be right, should it? It's right. Databases (not just MySQL) generally ensure that sequences will only ever increase (once a transaction is committed), but not that they will be without gaps. For instance, TFM says: You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have ...


2

In general, this design will do the job without problems, specially that you only handle 400 rows per month, which is relatively small number. However, I have few recommendations to improve the design of the table: price is better to be decimal, unless you want to store the price in "cents". i.e. if the ticket price is $5.99. you either have to store it ...


0

I have spent 5 days to understand why the variables have_openssl was always set to disable. The reason is that the server can't read the certificates (I don't know why) in every directory I've tried to put that. The only directory that work for me is the one that contain the file my.ini ( C:\programData\mySql ). The solution is to copy the certificates in ...


3

Background info... The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks. This leads to an unreadable block for InnoDB. The double-write buffer, and its extra write, makes it possible to recover from a torn page. The hardware needs to ...


1

Do a GROUP BY aggregation and see if the aggregated count is greater than 1 SELECT B.Author_id,A.Skill_id,COUNT(1) SkillEntryCount FROM tableB B INNER JOIN tableA A USING (Entry_id) GROUP BY B.Author_id,A.Skill_id HAVING COUNT(1) > 1; or collect all entry_id values for each (Author_id,Skill_id) and see it is has commas SET group_concat_max_len = ...


1

How about the SET datatype. With that, you could represent all combinations of 14 categories in 14 bits (2 bytes).


0

You cannot move an InnoDB table in that manner. The problem is that there is info in ibdata1, even if the table has its own "tablespace" (.ibd). With 5.6, and if you created the table as "file per table", you can use "transportable tablespaces". See online docs for details.


1

The commandline mysql allows \! to do what you want, but you appear to be in Windows, which does not really allow for such a feature. So, No, you can't do it. For reading INSERT statements (etc) back in there is the source command. Can you turn things inside out? You can get a list of databases, or otherwise create MySQL commands, from mysql ... -e ...


1

The MySQL SET datatype is a disaster - as are any type of array datatypes (supported unfortunately by many RDBMSs). Check out my answer to another question here. As I note, MySQLs SET is a breach of Codd's second rule - no repeating group datatypes. It is also completely non-portable should you wish to change RDBMS. If you wish to store that data this way ...


0

From the manual: With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP and ...


1

here's my observations. :) 1/ Your posts should only refer to a car_model. There is almost no performance issues to do an additional join given the fact that there won't be a load of car makers. More about that, you are running into an integrity problem : what if you have a problem with an update and found that your post is referring to an 'Audi A3' but is ...


1

Initially I have thought to only have reference to "car_model" in "posts" table. Cause technically I could access the car make via the "car_model" table. But then decided that for search purposes it would be easier to have both keys in this table, correct decision? No, this adds redundancy to your database and this is something you generally do not want ...


0

Something like this should work : select nm_linha from linhas where cidades_origem like CONCAT('%', (subquery_goes_here) ,'%') For example : SELECT * FROM films WHERE titre LIKE CONCAT('%', (SELECT 'fella'), '%') +---------+------------+ | id | titre | +---------+------------+ | 1 | Goodfellas | +---------+------------+ 1 row in set ...


3

This was a fun exercise :) I decided to throw this problem on it's head and instead ask, which days are not available first then get which days are available. You will need a "days" table that list all days. There are a lot of scripts out there to find how to do this dynamically. test is your table in the question and days is this days table. First, get just ...


2

Well, I tried a solution. It works but it is pretty ugly. But it works... SELECT count(*) FROM ( SELECT code, dates.selected_date FROM appartments INNER JOIN (select * from (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 ...


4

In addition to the password starting with an asterisk, here is algorithm for PASSWORD() SET @plaintextpassword = 'whatever password you want'; SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@plaintextpassword))))); EXAMPLE mysql> SET @plaintextpassword = 'whatever password you want'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT ...


0

Check the folowing First check if another instance is runnig in the same machine ps ax | grep mysql Then, check if your file my.cnf (probably in /etc/mysql/ folder) is correctly configured with [mysqld] datadir = /var/lib/mysql/ socket = /var/lib/mysql/mysql.sock Next, you have changed the default data directory, did you gave the right ...


0

I had the same issue the other day. Try this, it should work! Right click on MySQL Notifier -> Actions -> Manage Monitored Items Highlight the MySQL56 entry and click the delete button Click the add button -> windows service Scroll down and look for MySQL56 Highlight it and click ok


10

Ok, found about this in the documentation itself. This was a change introduced in mysql 4.1 so that the earlier password lengths of 16 characters and newer password lengths of 40 characters could be simultaneously supported. The Password column was made 41 bytes (chars) long, and the newer passwords would begin with a mandatory * to identify them. From the ...


0

If filter 110M is exactly a subset of filter 126M, then appending more AND with WHERE would have done the job. $sql1 = "SELECT ..... WHERE ..."; $sql2 = $sql1 . "AND column-name = ...."; $sql3 = $sql2 . "AND column-name = ...."; If that is complex to accomplish try to CREATE VIEW of the previous SELECT statement and the next SELECT statement should ...



Top 50 recent answers are included