mysql

How to import a mysql dump while renaming some tables/columns and not importing others at all?

I'm importing a legacy db to a new version of our program, and I'm wondering if there's a way to not import some columns/tables from the dump, and rename other tables/columns as i import them? I'm aware I could edit the dump file in theory, but that seems like a hack, and so far none of my editors can handle opening the 1.3 gb file (Yes...

Using mysql innodb as a queue?

I have two jobs pulling from a mysql table. They both want to get a row, do some work on it and update that row with the results, however I don't want them selecting the same row to update. Whats the best way using mysql/innodb engine to lock a row in the select so that it will be unavailable to other threads but allowing them to select ...

MySQL - Need to search URL table for URLs containing a specified word

I have a table of URLs, and I need to find any URLs that contain a keyword, such as "fogbugz" or "amazon". If the keyword appears anywhere in the URL, I'd like it to be a result. Right now, I'm trying: SELECT url FROM url WHERE url LIKE '%keyword%' Which is predictably slow, even when url is indexed. I'm open to alternative metho...

MySQL Database doesn't exist, queries are automatically shunted to the correct db?

This is quite odd, but I have a script that was until now calling queries on a db that existed only on another server, and is now on my new server with a different name. However, the "host" was always localhost. The user connected to the non-existent DB on localhost, and somehow connected to the existing database instead? Can someone p...

Benefits, etc of using mySQL over SQLite (RoR)

I'm building a web application right now for my company and for whatever reason, can't get mySQL working on my Mac w/ my Ruby install (OSX 10.5). SQLite works fine though, so would it be a problem to use SQLite for now so I can get to work on this and then just change up my database.yml file to point to a mySQL database when I deploy (a...

Select records for MySQL only once based on a column value

Hi all, I have a table that stores transaction information. Each transaction is has a unique (auto incremented) id column, a column with the customer's id number, a column called bill_paid which indicates if the transaction has been paid for by the customer with a yes or no, and a few other columns which hold other information not relev...

Why do I get this error in MySQL?

import MySQLdb import random db = MySQLdb.connect (host = "localhost", user = "python-test", passwd = "python", db = "python-test") cursor = db.cursor() var = .3 sql = "INSERT INTO RandomInt (RAND) VALUES (var)" # RandomInt is the name of the table and Rand is the Column Name cursor.execute(sql) db.c...

Rails Error - No Such File to Load -- mysql

I've been fighting with a Rails install on my Mac for some time. The error I'm getting (in my development log) says: Status: 500 Internal Server Error no such file to load -- mysql Here's some info: I can successfully rake db:migrate my application I've installed the MySQL gem and it appears in the gem list: mysql (2.8.1) I have Pass...

PHP won't return data from function

Hi all, I'm a bit of a PHP newbie so go easy on me :) I am trying to return a category name for a searched entry, after determining whether the category is the lowest child. My sql statement returns multiple duplicate entries (for each category and sub category that it is in - i.e. if an entry is in Cars > Parts > Engine > Crankshafts...

Python's mysqldb obscure documentation

There are many escape functions in the Python module mysqldb whose documentation I don't understand, and my efforts at looking them up have revealed nothing. >>> print _mysql.escape.__doc__ escape(obj, dict) -- escape any special characters in object obj using mapping dict to provide quoting functions for each type. Returns a SQL litera...

Should I avoid COUNT all together in InnoDB?

Right now, I'm debating whether or not to use COUNT(id) or "count" columns. I heard that InnoDB COUNT is very slow without a WHERE clause because it needs to lock the table and do a full index scan. Is that the same behavior when using a WHERE clause? For example, if I have a table with 1 million records. Doing a COUNT without a WHERE c...

Sorting MySQL results for diversity

I have a table called Classes which stores information on College classes. It has the columns Department, Course, and Section. I currently have an autocomplete where users can enter classes. For example if they type "ECON" it shows ECON-E 201 12345, etc. The autocomplete query is LIMIT'ed to 10. Now, the problem with my autocomplete...

When to sanitize PHP & MySQL code before being stored in the database or when its being displayed?

Okay I was wondering when should I sanitize my code, when I add store it in the database or when I have it displayed on my web page or both? I ask this question because I sanitize my code before it gets stored in the database but I never sanitize when its displayed for the user. Here is an example of how I sanitize my code before its ...

give the job to MySQL or to PHP?

I have some several codes in PHP to do jobs that MySQL could do. such as sorting, merging each data from different MySQL tables, etc... Lately, I found out that I can do all these stuffs with one MySQL query. I am wondering is it better to give the MySQL capable jobs to MySQL or to PHP. efficiencies, speed, etc.. Thank You, ...

Simple MYSQL Group By and Join

Table users id - Primary Key, AI username - varchar (50) Table logins login_time - DateTime member_id - int (11) This SQL selects mid and max login_time and sorts DESC SELECT member_id, max(login_time) as time FROM logins GROUP BY member_id ORDER BY time DESC Question How do you select and join username onto member_id fo...

jQuery post a serialized form then inserting into mysql via php?

Hello, I'm trying to post a serialized form to a sumbit.php file, in turn, will then insert into a MySQL database; however, the last input which is hidden, is not getting inserted into the database, though the rest are. Here's some snippet examples of what I've got thus far which is not working: HTML <form method="post" ...

JDBC, INSERT MULTIPLE records obtained from query, into other table

I need to insert multiple records into mysql database at once. What I am doing is SELECT on one table, from my application with Prepared statement. What I must do is to insert those results into one other temporary table. String sqlquery = "select * from table1 where arg1 = ?"; PreparedStatement statement; statement = con.prepareStatem...

What's the difference between table-based relations and column-based relations in RDBMS?

Hi, I have used column-based relations a lot in my projects like: CREATE TABLE `user` ( id INT AUTO_INCREMENT PRIMARY KEY, usergroup INT ); CREATE TABLE `usergroup` ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); however, at work it seems some people do it using table-based relations like this: CREATE TABLE `user` ( id INT...

Saving credit card information in MySQL database?

I want to allow my customer users to enter their credit card information so that I can charge them every month. I wonder how one should save this information? Should it be saved in the MySQL database ("user" table) or is this kind of information too sensitive and need to be stored in another place? I have no experience of this and wou...

How can i store unicode in mysql?

Hello, How do i store unicode in free edition of MySql? There doesn't seem to be nvarchar type as in Sql Server. Is unicode not supported in MySql? I tried using text but that too is not working. Thanks in advance:) ...