Column Permissions?

Hello, I am wondering if it is possible to limit privileges for a user account based on information stored within specific columns. For example, I plan to include a `username` column on a table with the hope of having each user log into the database using their own credentials and only be able

Re: Table scan in join on primary keys??

If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another an

Re: Table scan in join on primary keys??

If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another an

Subquery Oddity? Or where is the error I'm missing?

While working tonight on some query work I came across the following situation. Bare with me as I build up all the pieces of the final two queries, then notice how the subqueried version fails, but the substituted version doesn't. What am I missing in here: mysql> SELECT parent_path FROM catego

I want to get schema infomation in MySQL DB for schema.

Hi. ORACLE DBMS have a information for table or column in database dictionary, such as, USER_TAB_COLUMNS, USER_TABLES. Don't have any data base dictionary in MySQL, as ORACLE? thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lis

Re: Log data transfer amount?

Anyone have any ideas on this one? : I've scoured the MySQL manuals... does anyone know if there's any way to log the amount of data that individual queries produce? Eg: like bytes transferred in a web server log? - John -- --- J

Table scan in join on primary keys??

I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do

RE: Help with a query using multiple LEFT JOINS

If you mean that you want to get a row even if tbl2 does not have a matching row for dcode, then move the conditions into the ON clause. Example based off of what you had: SELECT FROM tbl1 as d LEFT JOIN tbl2 as r ON (d.dcode=r.dcode AND r.mcode='AB' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,

"missing" my.cnf file?

I have inhertited this old Solaris x86 system with MYSQL installed on it. mdb:/tmp 15:38:08 [539]# uname -a SunOS mdb.hitbox.com 5.7 Generic_106542-24 i86pc i386 i86pc I would rate myself as an advanced novice WRT MYSQL To a large deree this system is running OK. However, it does seem to be smac

Help with a query using multiple LEFT JOINS

I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as

SOLVED: SQL syntax error: help a noob

On 31 Jan 2005, at 11:39 pm, Tom Crimmins wrote: I think datediff only takes two arguments and you have three listed. Nailed it! Thanks, Tom. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL syntax error: help a noob

I think datediff only takes two arguments and you have three listed. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Chris Kavanagh Sent: Monday, January 31, 2005 5:33 PM To: mysql@lists.mysql.com Subject: Re: SQL syntax error: help a noob Thank

Re: Mysql + PHP -> Search

I just answered my own question actually! - snip from php.net - MySQL 4.0 supports a fabulous new feature that allows you to get the number of rows that would have been returned if the query did not have a LIMIT clause. To use it, you need to add SQL_CALC_FOUND_ROWS to the query, e.g. $sq

Re: SQL syntax error: help a noob

Thanks very much for the replies, guys. My version is "4.1.7-max". The error message I get is: -- Error Executing Database Query. Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use nea

MySQL + PHP -> Search Engine question!

Hi All - I'm building a search engine and what I would like to do is run a search and get the number of results, but still use the LIMIT command so I am not returning a ton of rows all at once. Is this the best way to go about searching? Thanks, Matt Babineau Criticalcode w: http://www.critical

RE: SQL syntax error: help a noob

[snip] My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GET

Re: SQL syntax error: help a noob

Chris Kavanagh wrote: My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF

SQL syntax error: help a noob

Dear list, My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_t

Re: Changing own password on 3.x

Jesse Sheidlower wrote: I'm asking on behalf of someone; I don't have access to the machine in question: How do you change your own password in MySQL 3.23.58, without access to the mysql table? The user in question discovered that he was only able to change the password from the particular machine

Changing own password on 3.x

I'm asking on behalf of someone; I don't have access to the machine in question: How do you change your own password in MySQL 3.23.58, without access to the mysql table? The user in question discovered that he was only able to change the password from the particular machine he was logged in from

Re: disappearing data

Sheryl Canter wrote: I've got a weird problem that's driving me nuts. I'm updating a set of scripts for a customer database that supports sending out software registration codes in real time. I've had this working for some time now, but I'm having the most frustrating problem. Data I've inserted si

Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No

Hello. I've looked through the bug database, and the only thing that I've found was an already-closed bug: http://bugs.mysql.com/bug.php?id=6148 Check that your server passes rpl_relayspace.test. Go to the mysql-test directory and execute: ./mysql-test-run t/rpl_relayspace.test

Re: ANNOUNCE: ShellSQL 0.7

Forgive me replying to my own post There is a bug in the MySQL engine of this suite (shmysql). This has been fixed in ShellSQL-0.7.1 at the below mentioned places. If you have downloaded 0.7 you should download 0.7.1 and re-install (only shmysql is effected here, all the other programs are

Re: bash powered MySQL Queries

On Mon, Jan 31, 2005 at 06:57:58PM +, Edward Macnaghten wrote: > 1 - The output is not cluttered with headers, and a means exist to > easily separate fields when there is more than one column or row in the > query. The mysql --batch option should take care of that for you. -Jason Martin --

Re: bash powered MySQL Queries

Thank you all for your replies. I think that ShellSQL is really the thing I am looking for. With kind regards Andy On Sun January 30 2005 23:50, Andy wrote: > Hi all > > I just wanted to know what would be the easiest way to retrieve simple data > from a MySQL database from a bash script. >

Re: bash powered MySQL Queries

Forgive me for blowing my own trumpet here. The advantage with ShellSQL over this method is 1 - The output is not cluttered with headers, and a means exist to easily separate fields when there is more than one column or row in the query. 2 - The connection is persistant, whereas running "mys

Re: How-to copy a column

>I have a table (table1) which has 4 columns, I want to copy all the >contents of col1 into col2. >Col3 is the primary unique key, so the copy has to keep the data matched >with col3. Sounds like what you want to do is update table1 set col2 = col1; which will copy the contents of

Re: bash powered MySQL Queries

On Sun, 30 Jan 2005, Andy wrote: > Hi all > > I just wanted to know what would be the easiest way to retrieve simple data > from a MySQL database from a bash script. I do this a lot - just construct the query and dump it into a file from within the script, eg: echo "select * from widgets

Re: bash powered MySQL Queries

>I just wanted to know what would be the easiest way to retrieve simple data >from a MySQL database from a bash script. Easiest way I've used to do it is: mysql

Re: UNION

Ha! Thx Michael, your first suggestion (reversing the order of the UNION terms) worked like a charm. greetz, Stijn Michael Stassen wrote: Stijn Verholen wrote: Jigal van Hemert wrote: Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, st

Re: UNION

Stijn Verholen wrote: Jigal van Hemert wrote: Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs

RE: Need a query to get the difference of two tables

Hi, You are absolutely right as per the test. I am confused. Infact, I had run a limit on the query. So, I got the result in the first two records. So, i was able to get a single ID which I was looking at. My original requirement was a bit more complicated than the one I posted. I had to check 1

Re: UNION

Jigal van Hemert wrote: Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkooppri

Re: How-to copy a column

Hi! Am Mo, den 31.01.2005 schrieb Syed Ali um 17:14: > Does anyone know the easiest way to copy a column in mysql? > > I have a table (table1) which has 4 columns, I want to copy all the > contents of col1 into col2. > Col3 is the primary unique key, so the copy has to keep the data matched > wi

RE: How-to copy a column

[snip] Does anyone know the easiest way to copy a column in mysql? I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. [/snip] UPDATE table1 SET col2=col1; --- Tom Cr

How-to copy a column

Does anyone know the easiest way to copy a column in mysql? I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. I guess one possible way is to do a select and output to

Re: UNION (was: WHERE ... IN () )

> Here is my solution: > (SELECT ID, referentie, postcode, gemeente, > kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS > hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, > referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, > verkoopprijs, doel AS

Re: WHERE ... IN ()

Jay, thx for your speedy reply. MySQL does indeed know the "WHERE IN" syntax (bad coding on my part). I have another question. There are two tables in the db from which I want to gather information. These tables are identical, except for two fields that have different names, the same types (but ot

RE: WHERE ... IN ()

[snip] ANSI SQL has the WHERE key IN (value1, value2, ...) MySQL doesn't seem to support this. Is this a faulty assumption, or am i stuck with WHERE key = value1 OR key = value2 ... [/snip] It is a faulty assumption, see http://dev.mysql.com/doc/mysql/en/comparison-operators.html SELECT stuff F

RE: disappearing data - please help!

> -Original Message- > From: Sheryl (Permutations Software) [mailto:[EMAIL PROTECTED] > Sent: maandag 31 januari 2005 14:44 > To: 'leegold'; mysql@lists.mysql.com > Subject: RE: disappearing data - please help! > > > Par down the PHP or what ever script you're using to the minimum > > ie.

WHERE ... IN ()

Greetings, list ! ANSI SQL has the WHERE key IN (value1, value2, ...) MySQL doesn't seem to support this. Is this a faulty assumption, or am i stuck with WHERE key = value1 OR key = value2 ... TIA, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

Re: Mysqldump unusable, bugged?

matt_lists wrote: Found the problem now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' the restore barfs on this not sure how to remove this, looking at options now, it only puts this on a couple tables, not all o

Re: Need a query to get the difference of two tables

Harish wrote: Hi All, This query worked for me. SELECT a.address FROM a LEFT JOIN b ON a.id != b.iid AND b.message='y' Strange... that query should give far too many and wrong rows as a result, and it would take a long time to run on a big dataset... you are joining each row in table a with

RE: Need a query to get the difference of two tables

Hi All, Sorry, I got a autoreply from [EMAIL PROTECTED] I was not able to understand the autoreply and thought that the mail bouced. Please ignore my duplicate mail. Thanks again, Harish -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 6:15

Re: Mysqldump unusable, bugged?

Found the problem now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' the restore barfs on this not sure how to remove this, looking at options now, it only puts this on a couple tables, not all of them -- MySQL G

RE: disappearing data - please help!

Lee wrote: > Are you escaping the data you insert? If you have apostrophies eg. "Sheryl's Website", you could have problem if you do not escape special chars. See if it happens with just "aa" (without the quotes) in all the fields in the record...then try unescaped words with apostrophies. I'

RE: Need a query to get the difference of two tables

Hi All, This query worked for me. SELECT a.address FROM a LEFT JOIN b ON a.id != b.iid AND b.message='y' Thanks, Harish -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 12:23 AM To: Roger Baklund Cc: mysql; Harish Subject: Re: Nee

Re: Need a query to get the difference of two tables

Michael Stassen wrote: Roger Baklund wrote: [...] select a.address from a left join b on b.iid=a.id where b.iid is null; That's not equivalent, because it leaves out a condition. I think it should be SELECT a.address FROM a LEFT JOIN b ON a.id = b.iid AND b.message='y' WHERE b.iid

Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No

Hi, My problem still goes on... After having had the problem 2 more times within 1 day, I decided to re-do the replication (copy the whole database onto the slave with rsync and reset master and slave). That only lasted for little more than 1 day and I ended up with the same error: Could not pa

Sub query Help

Hello I have written a report generator in PHP that handles creating and paginating the results from queries into a nice, user-friendly, HTML format. Because it's a generic report generator, it has some quirks about it, but I have managed to modify queries enough so that it works in almost all ca

Re: Mysqldump unusable, bugged?

matt_lists wrote: I've seen this quite a few times, but never been able to reproduce it properly. I'm assuming you're running on Windows, correct? I'd search your dump-file for "DATA DIRECTORY" the problem on Windows is that it uses \ instead of / in the path names there, which makes it use it a

MySQL Administrator not working correctly 2nd Request

OK I have a Fedora Core 3 Box running MySQL-server-4.1.8-0, when I try to manage the users on that box using the windows version of MySQL administrator version 1.0.19 I get errors. When I add a new user it tells me: error while storing user information If I look at the mysql log file is shows t

Re: Mysqldump unusable, bugged?

I've seen this quite a few times, but never been able to reproduce it properly. I'm assuming you're running on Windows, correct? I'd search your dump-file for "DATA DIRECTORY" the problem on Windows is that it uses \ instead of / in the path names there, which makes it use it as an escape characte

Re: Corrupted auto_increment?

Hello. Not enough information to make a conlusion. For example this can be a bug: http://bugs.mysql.com/bug.php?id=6784 Which is fixed in 4.1.8. Send us information about exact version of MySQL that you use, an output of SHOW CREATE TABLE on your tables, a config file, a version of oper

Re: Problem with query and password

Hello. Looks like passwords in your table are stored in old short format. Use old_password() instead of password(). See: http://dev.mysql.com/doc/mysql/en/password-hashing.html Lancer Emotion 16 <[EMAIL PROTECTED]> wrote: > Hello everbody,i have a problem with mysql and i wish you could

client_test fails on Solaris 9 x86

>Description: Running mysql-test fails test client_test with segv. Several other tests fail after this and it may be due to the segv. here is the output from the test: client_test[ fail ] Errors are (from /opt/csw/mysql4/mysql-test/var/log/mysqltest-tim

Re: utf8 x latin

I think you should use: latin1_general_ci and on columns with spanish you should use latin1_spanish_ci. English, French, Portuguese not having a separate collation I believe that they are included into latin1_general_ci ? Please observe the last part from a collation name: case sensitive (_cs) .

RE: Need a query to get the difference of two tables

Hi All, This query worked for me. SELECT a.address FROM a LEFT JOIN b ON a.id != b.iid AND b.message='y' Thanks, Harish -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 12:23 AM To: Roger Baklund Cc: mysql; Harish Subject: Re: Need