Referring to columns by ordinal

Hi all, I've searched all over the place for a solution to this, and I hope you can help. I'm trying to write a very simple database handling script. The SQL statement I'm trying to do is SELECT * FROM tablename WHERE the_first_column = a_number; All my tables have the primary key in the first colu

Can someone please explain this?

During some training on SQL recently, the following occurred. No one can explain it. The training used five tables A, B, C , D and E, each with a single int column. Table A has column a with values 1, 2, 3, 4, 5 Table B has column b with values 1, 2, 3, 4 Table C has column c with values 1, 2,

InnoDB deadlock problem

Hi, I've got a deadlock problem using InnoDB tables in MySQL 4.1.5 for win32. I have two tables, 'jobs' and 'results', where 'results' has a foreign key 'id_job' that references the primary key of 'jobs'. There may be more than one result for any given job. Both tables have a single auto_incre

Re: InnoDB deadlock problem

On Wed, 6 Oct 2004, David Edwards wrote: > I've got a deadlock problem using InnoDB tables (...) > Transaction 1: > START TRANSACTION; > DELETE FROM results WHERE id_job = 25920; > INSERT INTO results(result,id_job) VALUES (31.461937,25920); > COMMIT; > > Transaction 2: > START TRANSACTION; > DE

Re: InnoDB deadlock problem

Hi Tobias, Thanks for your reply. Unfortunately I couldn't see from the manual why I was getting the deadlock - transaction 2 already has a lock on the index it is waiting for. The difference seems to be 'insert intention' - I'm not sure what different types of exclusive lock there are and how

Re: Long Running Queries

How do you do to benchmark a query ? >I wish we had thought to have had him benchmark a query before and after he >added an index. It would be interesting to see the difference in actual time >that an index can make on a table with 450,000 records. > >On Wed, 2004-10-06 at 15:31, Jason Williard w

Re: Referring to columns by ordinal

- Original Message - From: "Paul Hanlon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 07, 2004 4:55 AM Subject: Referring to columns by ordinal > Hi all, > I've searched all over the place for a solution to this, and I hope you can > help. I'm trying to write a ve

Re: Delete duplicate entry

Batara Kesuma wrote: Hi, I have a table that looks like: CREATE TABLE `message_inbox` ( `member_id` mediumint(8) unsigned NOT NULL default '0', `message_id` int(10) unsigned NOT NULL default '0', `new` enum('y','n','replied') NOT NULL default 'y', `datetime` datetime default NULL, KEY `idx_1`

RE: Referring to columns by ordinal

The programming solution is work out the column name in your script, ie do "describe tablename" in your script, look for the column name marked as "PRI" in the key column, then insert this column name in the select statement. Andy > -Original Message- > From: Rhino [mailto:[EMAIL PROTECT

JOIN in the same table

Hi, I have a situation like this: Table People = people_ID people_name people_friend_ID people_friends_ID is the people_ID from another record. Is there a way to make a SELECT that returns people_name and people_friend_name? Perhaps I´d get this with sub-selects but I´m using MySQL-4.0

Re: JOIN in the same table

Ronan Lucio wrote: Hi, I have a situation like this: Table People = people_ID people_name people_friend_ID people_friends_ID is the people_ID from another record. Is there a way to make a SELECT that returns people_name and people_friend_name? SELECT p1.people_name,p2.people_name FROM Peopl

RE: [OT] Email heaaders and threading (was Re: update MySQL)

> -Original Message- > From: Michael Stassen [mailto:[EMAIL PROTECTED] > This tells the recipient's email client that your message is > a reply, not a new message, despite your efforts to change the subject and > recipients. Many email clients use that header to decide which thread a me

Re: Can someone please explain this?

It's a bug: Depending on which bug report you look at, this result is either because mysql treats this as a nested join, or because mysql does not support nested joins. Frankly, I find the explanations make no

Re: JOIN in the same table

This should work. No sub-selects needed here. select a.people_name, b.people_name as people_friend_name from people a, people b where a.people_friend_ID=b.people_ID Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL:http://www.iMikalsen.com On 7 Oct 2004 at 10:34, Ronan Lucio wrote: > Hi, > > I

**newbie question** renaming a database

using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in order to do something

Re: Backup problem

At 12:27 AM 10/7/2004, you wrote: This is what I need to do, what is the best approach for doing this? I need to take backup of few tables in mysql, then I need to drop that entire DB, recreate it, (and sth else that's not relevant here) and then restore these backed up tables. This backup and rest

Re: **newbie question** renaming a database

Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shutdown and restart the mysql database in orde

Re: **newbie question** renaming a database

At 11:16 -0500 10/7/04, gerald_clark wrote: Carolina wrote: using mysql version 4.0.13 - **total newbie here**... something as simple as renaming a database...i've not found anything relating to that on the mysql site or anywhere else. what i have found is that in order to do this, i'd have to shut

Re: Referring to columns by ordinal

Many thanks for the quick response. All good suggestions. I also noticed that in PHP there is a mysql_fetch_field function which returns a collection of properties about a given field, including it's name and whether it is a primary key, so with a little rejigging of my code, I'll use this. I want

Question about using select...where f in (xxx)

I have a quick SQL question that I hope someone can answer, I have two tables Events { e_id int(11), e_name varchar(200) }; Bookings { customer varchar(200), event_list varchar(200) }; Event_list is a string of comma separated values, eg "1,2,4" where the number corresponds to Events.e_id Sa

Re: Can someone please explain this?

Michael Stassen wrote: It's a bug: Depending on which bug report you look at, this result is either because mysql treats this as a nested join, or because mysql does not support nested joins. Frankly, I find t

Re: Question about using select...where f in (xxx)

I would strongly recommend refactoring as the string transformation you mention cannot be accomplished without external scripting assistance. change your Bookings table to be (customer varchar(200), event_Id int(11)) This way, each booking becomes one row in the table. Five bookings = five rows

Re: Question about using select...where f in (xxx)

I second this. The way you are storing multiple data items in a single column is very bad database design and fails the test for even the first normal form. You are going to get very poor performance and usability out of this table design. I know you said you inherited the data so its probably n

Re: Can someone please explain this?

>From 5.0.1, I get ... e c b 1 1 1 2 2 2 3 3 3 NULLNULL4 PB - Original Message - From: Ian Sales To: [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 4:21 AM Subject: Can someone please explain this? During some train

optimizing InnoDB tables

The documentation is not clear on this point. Here is a quote: 'For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It was also the case for InnoDB tables before MySQL 4.1.3; starting from this version it is mapped to ALTER TABLE.' What is meant by its being mapped to ALTER TABL

how can this query be optimized?

hello, i was wondering if anyone could help me to optimize a query i use when gathering search results? this is easily the most complicated query i've written (and likely a walk in the park for most of you) and because of this, i'm afraid it's a bit slow. SELECT COUNT(p.id) FROM products AS p

Storing SQL in a column? - looking for advice

Greetings, The project I'm working on involves extracting data from one database and storing it in another. With the goal of creating a generic mechanism (perl script) to perform the extraction on a periodic basis, I set up a table mapping destination table and column to the source of the valu

Re: how can this query be optimized?

You set up your situation very well but for one small item. Please allow me to kindly introduce you to the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Post the results from using that on your query and we will be good to go :-) Shawn Green Database Administrator Unimin C

RE: how can this query be optimized?

[EMAIL PROTECTED] on Thursday, October 07, 2004 12:30 PM said: > You set up your situation very well but for one small item. Please > allow me to kindly introduce you to the EXPLAIN command > > http://dev.mysql.com/doc/mysql/en/EXPLAIN.html hey! that looks lik

RE: how can this query be optimized?

Can you see where the column "possible_keys" is NULL for every table? That means that there are NO (none, zilch, nada, zero) indexes that can be used to save your database engine from the trouble of doing a full table scan for EACH AND EVERY MATCH in your query. I don't even see where you define

MySQL v ASP problem

I have searched high and low and I know that you guys can help out ( as you have helped me before ). First, I will list table descriptions, then the problem, then I will list the code, and finally the permissions. The Descriptions: 2 tables - both MyISAM. "Table 1" has 9 fields,

shoud this query fail?

Consider these three queries, the first fails with an error, the second succeeds and third also succeeds. The only difference the set of records available in the database to match the query. In the case that fails, the picture record exists but the batchflow record does not exist. In fact, NO b

RE: how can this query be optimized?

[EMAIL PROTECTED] on Thursday, October 07, 2004 1:34 PM said: > Can you see where the column "possible_keys" is NULL for every table? > That means that there are NO (none, zilch, nada, zero) indexes that > can be used to save your database engine from the trouble of

Re: ResultSet NotUpdatabelProblem

Its my impression that prepared statements are buggy with innodb tables. i've recently filed a bug, at heikki's request, where some buffer on the mysql server periodically flushes itself or otherwise is erased, with the result that the sql executed by a prepared statement is not what you think

Multiple Databases or One?

I'm working on several websites that will be driven primarily by two databases - Geography and Animals. The Geography database will feature information about nations, provinces and states, such as capitals, population, etc. The Animals database features lots of taxonomic tables (orders, families, s

Resetting the password error

While trying to login to the server I got the following errors: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\>c:\mysql\bin\mysql -root -p Enter password: * ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) C:\>c:\mysql\bin\m

Re: Resetting the password error

Michael FayeC SQL wrote: While trying to login to the server I got the following errors: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\>c:\mysql\bin\mysql -root -p Enter password: * ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YE

Re: MySQL v ASP problem

J.R., The difference in the way the two statements function is in your code. In the first example, you set the cursor type (rs.Open sql, conn,3,3) as updatable. In the second example, you set the cursor to the default type (non-updatable: rs.Open sql, conn). I haven't used ASP extensively rece

Re: Delete duplicate entry

gerald_clark wrote: Batara Kesuma wrote: Hi, I have a table that looks like: CREATE TABLE `message_inbox` ( `member_id` mediumint(8) unsigned NOT NULL default '0', `message_id` int(10) unsigned NOT NULL default '0', `new` enum('y','n','replied') NOT NULL default 'y', `datetime` datetime default

MySQL Databases in Subdirectories?

Is it possible to create a database in a lower-level subdirectory of MySQL's data directory? We have almost 100,000 sites, and we would like to have a separate database for each site. However, it's very impractical from a filesystem maintenance standpoint to have 100,000 subdirectories of MyS

Re: Delete duplicate entry

Hi, I think the query has to be considering the count rather than the sum. the query can be like this: select pkfield1[,pkfield2[,pkfield3[,]]], count(1) from group by pkfield1[,pkfield2[,pkfield3[,]]] having count(1) > 1 Here u can add n-number of feilds which u want make PK. Reg, El

A small, very small sucess story

I know this is off topic, but I have been a lurker for awhile, quietly listening or reading, and getting to learn more and more about this wonderful tool. I am a true mysql newbie, in fact a database newbie. However I decided I wanted to learn something new and differnet then my normal job as a Wi

Re: Delete duplicate entry

Eldo Skaria wrote: Hi, I think the query has to be considering the count rather than the sum. the query can be like this: select pkfield1[,pkfield2[,pkfield3[,]]], count(1) from group by pkfield1[,pkfield2[,pkfield3[,]]] having count(1) > 1 Here u can add n-number of feilds which u want ma

user variables and regexp

User variables do not work with REGEXP under MySQL 4.0.21 & 4.1.5. Is this a bug or a feature? -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.c

Re: user variables and regexp

At 3:12 +0200 10/8/04, Przemyslaw Popielarski wrote: User variables do not work with REGEXP under MySQL 4.0.21 & 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what "do not work" means. Can you be more specific? mysql> s

Re: user variables and regexp

Paul DuBois <[EMAIL PROTECTED]> wrote: >> User variables do not work with REGEXP under MySQL 4.0.21 & 4.1.5. >> Is this a bug or a feature? > > It's difficult to provide an answer to this because you're providing > no information about what "do not work" means. Can you be more > specific? Sure.

Re: user variables and regexp

At 3:47 +0200 10/8/04, Przemyslaw Popielarski wrote: Paul DuBois <[EMAIL PROTECTED]> wrote: User variables do not work with REGEXP under MySQL 4.0.21 & 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what "do not work"

Re: user variables and regexp

Paul DuBois <[EMAIL PROTECTED]> wrote: >> SELECT >>@a:=FIRMLEGALZIPCODE >> FROM tCustomers >> WHERE @a REGEXP "[0-9]" >> -> Empty set (0.03 sec) > > You're expecting the value to be selected first so that you then can > test it with the WHERE clause later. Of course you're right. Thanks. Accor

Comparing Tables

I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reason, table2 has ~45

Re: user variables and regexp

At 4:01 +0200 10/8/04, Przemyslaw Popielarski wrote: Paul DuBois <[EMAIL PROTECTED]> wrote: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP "[0-9]" -> Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later.

data with dynamic schema stored in a column as a property list.

Hi, instead of xml, i stored arbitrary data of the form (the actual usage of such mechanism is for more fancy stuff, say, dynamic configuration, otherwise this is really not necessary) { name = "Fn, Ln"; // string value gender = F;

Re: Comparing Tables

On Thu, 7 Oct 2004 19:22:23 -0700 "Jason Williard" <[EMAIL PROTECTED]> wrote: > I know that it is possible to do this, though I don't know how. > > I have 2 tables containing information about trouble tickets. One of the tables > (table2) contains information about every ticket ever received,

data with dynamic schema stored in a column as a property list.

Hi, instead of xml, i stored arbitrary data of the form (the actual usage of such mechanism is for more fancy stuff, say, dynamic configuration, otherwise this is really not necessary) { name = "Fn, Ln"; // string value gender = F;

Re: Comparing Tables

Jason Williard wrote: I know that it is possible to do this, though I don't know how. I have 2 tables containing information about trouble tickets. One of the tables (table2) contains information about every ticket ever received, including tickets that were deleted from the system. For this reaso

2003 server problem

I am having trouble intalling versions 4.02 and 4.1 onto server 2003. The issues are; 1. my.ini is not being written 2. WinMYSQLAmin is causing the following error; "Access violation at address 0040289D in module 'winmysqladmin.exe'. read of address ." 3. MySQL can can only be stared onc

Re: MySQL Databases in Subdirectories?

How does that help? The database itself should be allowed to organize everything. Ruben On Thu, Oct 07, 2004 at 04:57:39PM -0700, Justin Smith wrote: > Is it possible to create a database in a lower-level subdirectory of > MySQL's data directory? We have almost 100,000 sites, and we would li

Re: Comparing Tables

see IS NOT On Thu, Oct 07, 2004 at 07:22:23PM -0700, Jason Williard wrote: > I know that it is possible to do this, though I don't know how. > > I have 2 tables containing information about trouble tickets. One of the tables > (table2) contains information about every ticket ever received, in