mysql

Django Tests fail with InternalError: no such savepoint. DB: Postgres, passes on mysql

Interestingly it also works on the shell. [MY code which calls Model.objects.get_or_create(...)] File "/usr/lib/python2.5/site-packages/django/db/models/manager.py", line 123, in get_or_create return self.get_query_set().get_or_create(**kwargs) File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 308, in ge...

How to join 2 tables based on a like in mysql

I have 2 tables Employee and Company_Employee. Employee: ID, FirstName Company_Employee: ID, Company_ID, Employee_ID I want to do a search by First Name. I was thinking my query would look like: select FirstName, ID from Employee where FirstName LIKE '%John%' and ID in (select id from Company_Employee) This query returns no ro...

Lots of queries to pull up user photos and their comments. (CakePHP)

What I'm trying to do is pull up profile info w/ the profile's comments. I get everything as expected. No error returned, array is formatted perfectly. My concern is the queries that are run. It runs one query for each ID to get its photo (the start of the query is noted in comments in the code below). I guess this is the only way to get...

Should a two-to-many data relationship be treated as many-to-many?

I have 2 database tables: Teams and Games. For the purpose of this question, we are dealing with football (soccer) teams and games. Each Game has exactly 2 teams, generally a home team and an away team although occasionally both teams can be neutral. My question is whether I should represent this data relationship using 2 foreign keys...

using LIKE with logical operators

i can't seem to figure out how to combine LIKE with an OR or AND: DELETE * FROM persons WHERE FirstName = 'Abe' AND LastName LIKE '%coln'; Looks like it should owrk to me but I get error 1064 (syntax0 Is there a correct way to do this? ...

Will memcache help with expensive MySQL queries? (Drupal site)

Hello all, this is a simple question. There are some queries on our site that take too many seconds to complete (like 15 seconds!). For anonymous traffic they get a cached .html file, but for logged in users it's too slow. The query looks like this: SELECT node.nid, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_...

Doctrine ORM: Create Table Like Another

Hello, Is there any way, when using Doctrine, to create a table like another? I know in MySQL there is a function to do so: CREATE TABLE user2 LIKE user; and tables user and user2 will be identical. Can this be done in Doctrine? ...

PHP Mysql joins across databases

Lets say I have two separate databases, X and Y, on the same physical server. All of my queries currently run out of X. I find I have one table in Y I would like to be available to X for JOINS. So... now I keep a copy of the one table I need for both X and Y in both X and Y, but the data in Y is constantly changing, so the copy soon b...

How well do ASP.NET and MySQL work together?

What have your experiences been with asp.net and mysql? Is it actually faster than mssql in terms of connection times etc? Or is the library slower? Is it suitable for a large scale type app? ...

Escaping an apostrophe in Java

I'm still somewhat new to Java and trying to insert data into a database. I'm getting an error when inserting a string containing 's so my end result would be to escape the apostrophe. How can I accomplish? ...

Trouble with MySQL 5.1 and RANGE?

We have a pile of old databases in archives that have a column named RANGE in one of the tables. These database could have been created in MySQL 4.1 or 5.0. In MySQL 5.1+, RANGE is apparently a keyword for the query language. So now we have to change our schema for that table or we won't have any data at all. Just to be clear, its not...

Database abstraction layer in C# for MySQL?

I am trying to use MySQL 5 with C#. I downloaded MySQL driver on mysql.com and installed it. I can now connect to MySQL in C# with the following code. string ConString = "SERVER=192.168.10.104;"; ConString += "DATABASE=test;"; ConString += "UID=user;"; ConString += "PASSWORD=password;"; MySqlConnection connection = ...

Multiple values in where with prepared SQL-Statements?

Is there a way to select multiple values with prepared statements in (My-)SQL? I'm trying to select a couple of rows from a table with the IN-keyword, something like: SELECT * FROM table where id IN (1, 2, 3) The "1, 2, 3" should be passed as a parameter of the statement. Is this possible with PHP/PDO or do I have to concaterate...

Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or is it better to do a query like this: SELECT * FROM table1 WHERE ... LIMIT 1 and check to see if any rows were returned? In both queries, ...

Page vs. Extent vs. Segment vs. Tablespace

Mysql's Innodb has concepts of page, extent, segment, tablespace but I don't know how they interact with each other. How are they related? What does it mean a tablespace has 16K of pages? How does this relate to the physical rows and is 'pages' something in the memory? I would assume that Oracle might have the same concept but I'm not s...

PHP - MySQL connection not working: 2002 No such file or directory

I tried to use WordPress, and it would never work. I have apache and mysql running, the accounts and database are all set up, there are no typos. So I tried to make a simple connection: <?php $conn = mysql_connect('localhost', 'USER', 'PASSWORD'); if(!$conn) { echo 'Error: ' . mysql_errno() . ' - ' . mysql_error(); }...

MySQL Query to find customers who have ordered two specific products

I'm having trouble coming up with a query that will find all customers who have purchased both PROD1 and PROD2. Here's a pseudo-query that kind of looks like what I want to do: (obviously this wouldn't work) SELECT COUNT(DISTINCT userid) FROM TRANSACTIONS WHERE product_id = 'prod1' AND product_id = 'prod2' So basically I'm t...

MySQL custom engine: already existing data.

I'm currently exploring if a custom MySQL engine could fulfill my needs. I've been looking at http://forge.mysql.com/wiki/MySQL_Internals_Custom_Engine. One thing I wonder is: can I create an engine using an already existing (binary) file? If yes, I guess(?) there is no "CREATE TABLE..." so, how does MySQL know about this table? EDITED...

How do I make my delete query FULLY work?

My delete.php is sending the selected item to the db as dead as it should, but the blog is still staying on the page that its deleted from. What am I doing wrong? this is my query: on the delete.php: $MyBlog = $_GET['id']; $query = "UPDATE `Blogs` SET `status` = 'dead' WHERE `id` = '".$MyBlog."'"; Like I said, this is marking id dea...

mysql gem looking for an earlier version of libmysqlclient.16.dylib

I just upgraded to Mac OSX Snow Leopard, and recompiled mysql (5.1.39) with the Snow Leopard XCode. I also reinstalled the latest mysql gem. Now, when running rake db:migrate in a rails app, I get dyld: NSLinkModule() error dyld: Library not loaded: /usr/local/mysql/lib/libmysqlclient.15.dylib Referenced from: /usr/local/lib/ruby/...