mysql

Best cross-platform/SQL GUI?

I use SQL Server 2005, MySql, and Posgresql regularly. I'm tired of using Microsoft's SQL tool for SQL Server, phpMyAdmin for MySql, and pgAdmin for Postgres. Is there a decent cross-platform GUI that I can use for all of these database servers? I don't care about the actual database administration part, I realize that each vendor's to...

How can I set the max number of mysql processes or threads?

ps axuw| grep mysql indicates only mysql process but if I run htop I can see 10 rows each one of them with a separate PID. So I wonder if they are threads or processes that for some reason I cannot see using ps. Would it make any sense to try to limit them to 2 on my development machine where I don't need concurrent access of many clien...

Insert variables and arrays in different database tables using PHP and MySQLi

Hi, I'm learning PHP and MySQL and I'm creating a movies website for learning purposes. I want to insert variables and arrays containing movie info into a database but in different tables. This is what I have right now: include('inc/connection.php'); $conn = dbConnect(); // create SQL $sql = 'INSERT INTO movies (movieid, title, year,...

Unable to import python-mysqldb

I installed python-mysqldb using sudo apt-get install python-mysqldb I have tried to use it unsuccessfully. The following commands do not work in Python prompt: import python-mysqldb Or import mysqldb How can I solve this problem? ...

How do you make a threshold or limit mysql table?

Hi, I have a table which i would like only to have the 20 most recent entries, because the table is adding a row every .50 seconds. How would I accomplish this in querying to the database(mysql). Should i add a new row and delete the oldest whenever i want to push a new value in? ...

Problem with CHARSET in MySQL create table

Hi this is my code and it returns "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUE, chapter VARCHAR(100) CHARACTER SET utf8 COLLA' at line 2" CREATE TABLE IF NOT EXISTS texts ( id SERIAL DEFAULT VALUE, ...

Why does MySQL replication use five certificates?

Hello everyone! I'm attempting to set up MySQL replication with SSL encryption, and while I'm beginning to close in on the solution, there's one aspect of the process that I can't wrap my brain around relating to the way MySQL uses SSL. According to the documentation on MySQL 5.0 (setting up SSL for client/server and setting up SSL fo...

Search and replace a term in a MySQL database

I recently added a new project to our issue tracker, which is Redmine. When creating a project, you give it a name and an identifier (which are often the same). There is a note when creating the project that you cannot change the identifier once it has been set. For this reason I was very careful to choose a generic identifier. Unfortuna...

MySQL Indices and Order By Clause

Say I have a simple query like this: SELECT * FROM topics ORDER BY last_post_id DESC In accordance to MySQL's Order-By Optimization Docs, an index on last_post_id should be utilized. But EXPLAINing the query tells the contrary: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE topic_info ALL NULL NULL NUL...

Best Resource for mysql + python 2.6 programming

I need a great resource for interacting with MySql (version 5.0.45) with Python2.6. I'm using cherrypy, mako, the standard library, and nothing else. The resources can be blogs, howtos, books (online of offline), whatever. Additional information: The python mysql module, MySQLdb, is compatible with Python DB-API 2.0 . See http://sour...

SELECT * FROM table WHERE x IN (...a few hundred ints...)

Table has about 8 million rows. There is a non-unique index for X. Showing indexes, it shows that in the table there is a non-unique index on key name X with "seq_in_index" of 1, collation A, cardinality 7850780, sub_part NULL, packed NULL, index_type BTREE. Still, this query can take 5 seconds to run. The list of ints comes from anoth...

problem constructing a mysql query(most recent per item)

I have a website where users can comment on photos. I have a table of comments in this format: +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(12) | NO | | NULL | | | id ...

Group By and displaying entries under date

Here's my data: id date 1 2009-01-01 10:15:23 2 2009-01-01 13:21:29 3 2009-01-02 01:03:13 4 2009-01-03 12:20:19 5 2009-01-03 13:01:06 What I'd like to do is group by each date and then list the id numbers below each date. Should I be getting the distinct date values and then cycling through them to get the entries that fa...

Inserting mm/dd/yyyy format dates in MySQL

How can I insert an MM/DD/YYYY format date into an MySQL database using PHP? ...

What language should I use in conjunction with MySQL

I have no database experience and want to get started with MySQL. I have often seen it paired with Php and other various languages. Which language should I use in conjunction with MySQL? I am most familiar with Java currently, but I am very willing to learn a new language that works well with MySQL. ...

setting utf8 with mysql through php

I have the following very simple code, which retrieves utf8 formatetd data, such as containing umlauts from a mysql database, which may or may not be set as utf8. If I use either of the commented out approaches to ensure that utf8 data is returned, the data will NOT be returned as utf8, however if I leave them off, the data will be displ...

Testing a SQL query in PHP

I have the following simple php code snippet, which will, when called, delete a relevant article from a database. The result is passed to a javacsript function, which will update the page via AJAX. I would like to return the string "false" if the query fails, as I am attempt below. if($cmd=="deleterec"){ $deleteQuery = "DELETE FROM AUCT...

SQL Query to find holes between min_numbers and max_number in a table

Quick question for the SQL gurus. I have a table which contains, amongst others, two columns - min_number and max_number I have been trying unsuccessfully to write a query which finds the first hole of n size between the min and max numbers Example min max 1. 100 200 2. 250 300 3. 330 400 If I want to find a h...

Adding Image to a database in Java

I am trying to add an image to a BLOB field in a mysql database. The image is going to be less then 100kb in size. However I am running into problems and was wondering what would be a better way to add this data to the database? com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'Data' at row 1 PreparedStatem...

Are there any good tutorials on linking an iPhone app to a database?

I have built an iPhone app that needs to pull data from a server. First I need to figure out what kind of server I will need. I'm a bit familiar with MySQL but was wondering if anyone had better suggestions on a backend. My app will have two tables that need to be populated by data residing on a server. Also, a user can submit data to th...