mysql

Thinking Sphinx configuration fails - cannot connect to mysql through socket, despite socket existing!

When I run rake ts:conf, I get "Can't connect to local MySQL server through socket '/var/run/mysqld/mysql.sock' (2)" This socket file does exist. ...

How to Keep a MySQL Connection Open in Bash

I have a bash script that calls MySQL several times. Instead of having to reconnect to MySQL, is there a way to keep the connection open? Ideally, the connection would close if the script exits early. I'm thinking named pipes would work but they would stay open. Here's a quick pseudo-example of what I hope to find: openMySQL exec...

mysql select parent id of the records that match all values from array

Hi there, I have a problem with the sql query. I have a joint table which stores records with reference id of the record in the parent table and id of the record from the other table. Now - to make it a bit clearer: Table 1: "products_properties" : 'id', 'product', 'price' Table 2: "products_properties_options" : 'product_property',...

MySQL column drop taking a long time, but not in isolation

I'm dropping a column from a large InnoDB (actually Percona XtraDB 5.1.47) table of 136 million rows taking about 35Gb on disk. To test the process, I copy the entire table (using create table... select), which only takes 14 minutes. I then drop the column, which only takes 6 minutes. If I do exactly the same operation on the original ta...

How to get rid of "Error 1329: No data - zero rows fetched, selected, or processed"

Hey guys I have a stored procedure which does not need to return any values. It runs smoothly and without any problem. However, it outputs an error message (Error: No data - zero rows fetched, selected, or processed) after finishing its run. How can I get rid of this error message? (It is a simplified version of the procedure so, please ...

using generated column in a MySQL where clause

If I have a query, such as: select column1, ( select count(*) from table2 ) as some_count from table1 where column1 = 'foo' I can use column1 in the where clause, but if I try to add and some_count > 0 then I get an error that some_count doesn't exist. How can I use that in a where clause? ...

Email Notification on new MySQL database entry.

I've been creating a blog using tutorials around the web and I have a working comments system but what I would like is if when the user adds a comment that I get an email. I'd really love if you could explain how exactly I could go about implementing a notification. Any help is greatly appreciated. Current Form: <form id="commentform" ...

What is the best way of searching mysql table field for the ending of a fiels?

I have been told that searching in a MySQL database with LIKE '%wordend' is bad because it takes very long time. What is the best way of searching on the end of a field? Should I make an extra field where field that needs to be searched backwards is stored backwards, seems like an ok idea to me since it will give the benefit of indexin...

Optimize a rankings page using PHP and MySQL

I could really use some help optimizing a table on my website that is used to display rankings. I have been reading a lot on how to optimize queries and how to properly use indexes but even after implementing changes I thought would work, little improvement can be seen. My quick fix has been simply to use only the top 100,000 rankings (...

mysql - order by frequency with multiple columns

I have a table with 5 columns: tag 1 tag 2 tag 3 tag 4 tag 5 If I want to show results ordered by the popularity(frequency) of those tags, what kind of query would i use? ...

Change srid in mysql

Is it possible to change the srid of a column of geometry type? I just want to create a view of geometry type data from the raw latlon data and use it in the geoserver. However after using the pointfromtext function, the type of data I generate is geometry rather than point and geoserver would treat it as an feature typ of byte array whi...

Querying a subquery

I'm trying to do something like this: SELECT t1.*, t2.* FROM (SELECT * FROM USER) AS t1, (SELECT * FROM t1) AS t2 It doesn't work in MySQL. Is something like the above possible? My other option is to take the last subsquery and "wrap" it around the main query. Ideas? ...

How to JOIN a table in MySQL with a table in Oracle

Now, I'm pretty sure that it's not possible, but I want to 1) Confirm this, and 2) Hear alternatives. My solution today was to run the MySQL query, then use its result to construct a "NOT IN ('value1', 'value2', 'value3')" string, which I tacked onto the Oracle query before running it. Rather cumbersome, but it worked, and it's fast eno...

4 queries on the same table, a better way?

Currently I am doing one query, with 3 sub queries, all queries are on the same table, all queries have different where clauses I have thought about doing a group by however this would destroy the SUM() here is the query SELECT SUM(club) AS club, (SELECT COUNT(id) FROM action_6_members WHERE SUBSTR(CODE, 1, 1) = '9') AS 5pts, (SELE...

MySQL order question.

Is there a way I can order a group of users by first name and if a user didn't enter a first name order that user by last name or if they didn't enter a last name order them by middle name? ...

Local dev environment, php & mysql work, but not together.

I've spent the whole day trying to set up development environment using: PHP Version 5.3.3 apache 2.2 mySQL Server 5.1 Windows 7 The localhost renders the php fine, and I can work with mySQL via the command line client, but whenever I try to interact with mySQL through PHP I get a 30 second "waiting for localhost" and then a blank w...

jdbc - java - method query

I am using JDBC to query a MySQL database for specific records from two tables. My MySQL query sample is this: SELECT r.name , r.network , r.namestring , i.name , r.rid , i.id , d.dtime , d.ifInOctets FROM router AS r INNER JOIN interface AS i ON r.rid = i.rid INNER JOIN 1278993600_1_60 AS d ON i.id = d.id AND dtime BETWE...

Export list of pretty permalinks and post title

Looking for a way to export a list of pretty permalinks in wordpress with the corresponding post title. Looking for the actual permalink structure defined not the shortlink. I suppose if I have to I'll use a short link, but I prefer the full permalink. Any suggestions would be helpful. Thanks ...

Why is PHP/MySQL inserting my Chinese characters differently?

Gday All, I have a baffling problem whilst trying to insert some chinese characters into my MySQL database from PHP using mysqlnd. I have a form that accepts some details, eg Internal Name, External Name, Shot Name, etc... I enter "语言测试" (Language Testing) into all three fields in the form. I am submitting my information using an inn...

Primary Key - VARBINARY or BLOB or VARCHAR for UUID primary key

I am using UUID as the primary key in one of the tables. What are the pros-cons of having this field as a varchar/varbinary/blob? DB: MYSQL. TIA ...