It seems that we have managed to insert into our database 2 unicode characters for each of the unicode characters we want, For example, for the unicde char 0x3CBC, we've inserted the unicode equivalents for each of it's components (0xC383 AND 0xC2BC) Can anyone think of a simple solution for fixing this? I've come up with something li...
Hi all, I'm running a MYSQL query in two steps. First, I get a list of ids with one query, and then I retrieve the data for those ids using a second query along the lines of SELECT * FROM data WHERE id in (id1, id2 ...). I know it sounds hacky, but I've done it this way as the queries are very complicated; the first involves lots of geo...
Hi there, I have a large table (several million rows) in a MySQL (version 5.0) database that has data inserted into it at a very frequent rate. There are also less frequent select statements done against the database that only required the latest data (i.e. the newest 10000 rows), is there a way to create a table that would keep a copy...
I have a simple table set up with two columns, each column is a key value. the values stored in each field are varchar(45) representing an email address and a keyword. It is possible that the information collected may duplicate itself as it is related to site browsing data collection. To avoid duplicate entries, I used tried to use INSER...
DELIMITER $$; DROP FUNCTION IF EXISTS tonumeric $$; CREATE FUNCTION tonumeric() returns numeric BEGIN declare num numeric; set num = to_number('12'); return num; END$$ DELIMITER; $$ When I executed this function, I am facing this error. Error Code : 1064 You have an error in your SQL syntax; check the manual that correspon...
Hi, Is there an easy way to extract table DDL information, via a query, using either Ms or My SQL server? (preferably both?) For example, using MySQL Administrator / Navicat for MySql, there is a "DDL" function, which generates the "create table foo (....)" script. Is there any way to get this information from a query itself, such as...
Hi Friends, I have a MySQL table in which i have a time field in INT(10) format. I am storing UnixTime in it. Some of the records have a Zero value and some have a date in it. I want to build a query which shows "No Date" if the value is 0 else show the stored date if there is any date stored in the record on query execution. The table...
mysql_fetch_array will give me an array of a fetched row. What's the best way generate an array from the values of all rows in one column? Edit: Lots of great answers. Thank you everyone who replied! I ended up going with a solution similar to what Gumbo and GSto suggested. I gave the answer points to GSto because he has less points (ju...
Is there a way in MySQL to call a stored procedure from within SQL every x minutes? I want to use this in a session-environment, where the database keeps track of all the sessions and automatically deletes sessions older than x minutes. ...
Hi guys, I'm experiencing a few errors on a rails app, along the lines of: ActiveRecord::StatementInvalid: Mysql::Error: Lost connection to MySQL server during query: SELECT * FROM `actions` WHERE (`foo`.`id` = 16) What appears to be happening is that mysql connection is being closed after a timeout, and rails isn't noticing until i...
Is there a nice simple way to get the amount of columns using perl and mysql? I generate the sql select code so i dont know how many columns are in there. I do have the column section isolated so a regex solution could be simple? -edit- for the comment below. "select " . col . " FROM " #more code col = "a, b, c, d" #how do i find out i...
We have a database with the time related data in it. As you can imagine it growths (and slows down) with time. There is 50% read and 25% inserts and 25% update action on the present (this months) data, 100% read on the older data. The good thing is, the older data also becomes less important. The bad thing is that sometimes we need t...
Is there a way i can do a SELECT and tell it to return 3 columns and a dummy empty column ? I need 4 columns and the 3rd must be '' until the table is somewhere in the database. I am not allowed to add any columns to any tables. ...
I'm solving problem of storing files in my web app. I've already done some applications where files were stored in filesystem (simple upload of a file via PHP) and I wasn't sure how to solve the security issues correctly (I have the webapp on a shared webhosting). Is there any "manual" or book which target these issues in detail? Rece...
I've exported all the old orders out of our old store into a mySQL DB. Anyone know of a simple project or web based app that will let you search all fields with a wildcard? So if I type in sha automatically any results with those letters would popup. This would be similar to dbman but with mysql and some ajax. And preferably free and...
I have been using Linq-to-SQL. What is a good option for working with mySQL? I have been looking at NHibernate, Entity Framework, etc. Some comparisons (pros, cons) would be helpful ...
what is the proper way of closing a tread after running a query against a MySql database from a windows form in C#. is a simple open close enough like this? conn.Open(); //querycode conn.Close(): ...
My problem is that this application is deployed with a lot of different payment options in mind, and those payment options vary from one deployment to the next. My question is, how do I make a set of results become my select statement? SELECT DISTINCT(CONCAT('SUM(IF(pay_type = "',pay_type,'", pay_amt,0)) AS `',pay_type,'`')) as queryFi...
I am trying to decide whether to create a global view table or 1 for each section. for example, Lets say I have products, categories and pages. Do I have 3 tables e.g: CREATE TABLE `tbl_products` ( `p_id` INT NOT NULL , `p_views` INT NOT NULL , INDEX ( `p_id` ) ) ENGINE = MYISAM CREATE TABLE `tbl_categories` ( `c_id` INT NOT NULL , ...
I have heard over and over that you should add indexes to any foreign key you will be doing joins on. I have also heard you should have indexes for fields you will do queries on. Does anyone have a fairly exhaustive list or set of guidelines around when and when not to add indexes? There must be a size of table where it is inefficie...