Re: What is a good benchmark?
i got 0.96 on a dual XEON 2G 1G ram , its a dell box > On Tue, Jul 29, 2003 at 01:41:00AM -0400, Asif Iqbal wrote: >> Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any >> suggestion on how to improve it ? > > Get a faster CPU. > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 4.0.13: up 19 days, processed 575,681,006 queries (346/sec. avg) > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is a good benchmark?
Now I am running these processes for my mysql serever /bin/sh /usr/local/mysql/bin/safe_mysqld --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/webrt.pid /usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/bin/mysqld --defaults-extra-file=/usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc/data/my.cnf --basedir=/usr/local/mysql-standard-4.0.13-sun-solaris2.8-sparc --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/webrt.pid Now on the same logic if I run 4 pair ( the above two processes ) will it imporve my mysql query performance done through my DBIx::SeacrhBuilder ? Thanks On Tue, 29 Jul 2003, Dan Nelson wrote: > In the last episode (Jul 29), Asif Iqbal said: > > Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any > > suggestion on how to improve it ? > > Run it 4 times simultaneously in separate windows. You got 4 CPUs, use > them :) All that "benchmark" query does is tell you how fast one cpu > is. > > -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlMax vs mysql
When installing the mysql4 binary it works fine. When installing the mysql max4 binary I get the following message when I try to run mysqld_safe. I am running linux redhat 9.1. Would anyone know why this is happening? 030728 23:54:57 mysqld started 030728 23:54:58 bdb: /usr/local/mysql/data/log.01: Permission denied 030728 23:54:58 bdb: PANIC: Permission denied 030728 23:54:58 Can't init databases 030728 23:54:58 Aborting 030728 23:54:58 /usr/local/mysql/bin/mysqld: Shutdown Complete 030728 23:54:58 mysqld ended If mysql max is hopeless, how would you propose one could get around to using transactions and stored procedures since mysql alone doesn't do it. Cheers. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is a good benchmark?
In the last episode (Jul 29), Asif Iqbal said: > Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any > suggestion on how to improve it ? Run it 4 times simultaneously in separate windows. You got 4 CPUs, use them :) All that "benchmark" query does is tell you how fast one cpu is. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is a good benchmark?
On Tue, Jul 29, 2003 at 01:41:00AM -0400, Asif Iqbal wrote: > Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any suggestion on > how to improve it ? Get a faster CPU. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 575,681,006 queries (346/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation problem Mysql 4.0.13 on Red Hat linux 6.2
i am unable to install mysql 4.0 on my Rh. 6.2 box. >i have glib 1.2.6. When try to install the mysql it >gives the message "Mysql-server-version no.cannot be >installed" > >May i know what are the minimum requirment for >installtion of mysql 4.0 on 6.2. > >Is it possible to install the mysql 4.0 on 6.2 > >thanks in advance > >sankalap - Original Message - From: "sanjay gupta" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, July 29, 2003 11:22 AM Subject: installation problem Mysql 4.0.13 on Red Hat linux 6.2 > Dear All , > >I am facing problem in installing the mysql 4.0.13 on my > linux 6.2 machine . I have pentium III server. When i give the command > rpm -ivt MySQL-server-4.0.13-0.i386.rpm it give the message " Only one > major mode may be specified " > > I tried lot to install the mysql ver 4 on my machine but could not succeed . > please help > > Thanks in advance. > > sanjay > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installation problem Mysql 4.0.13 on Red Hat linux 6.2
Dear All , I am facing problem in installing the mysql 4.0.13 on my linux 6.2 machine . I have pentium III server. When i give the command rpm -ivt MySQL-server-4.0.13-0.i386.rpm it give the message " Only one major mode may be specified " I tried lot to install the mysql ver 4 on my machine but could not succeed . please help Thanks in advance. sanjay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is a good benchmark?
Solaris SPARC 420R 4 * 450 MHz, 4GB - 2.93 secs ... Yikes.. Any suggestion on
how to improve it ?
On Wed, 23 Jul 2003, John May wrote:
> Xserve 1ghz - 1.08 sec
>
> G3 333mhz - 2.78 sec
>
> - John
>
>
> >On my p4 2gig
> >
> >mysql> SELECT BENCHMARK(100,ENCODE("hello","goodbye"));
> >+--+
> >| BENCHMARK(100,ENCODE("hello","goodbye")) |
> >+--+
> >|0 |
> >+--+
> >1 row in set (0.86 sec)
> >
> >-Original Message-
> >From: Jake Johnson [mailto:[EMAIL PROTECTED]
> >Sent: Wednesday, July 23, 2003 8:34 AM
> >To: Mysql
> >Subject: What is a good benchmark?
> >
> >I ran this benchmark on my pIII 500 and was wondering what everyone else
> >was getting?
> >
> >mysql> SELECT BENCHMARK(100,ENCODE("hello","goodbye"));
> >
> >+--+
> >| BENCHMARK(100,ENCODE("hello","goodbye")) |
> >+--+
> >|0 |
> >+--+
> >1 row in set (2.59 sec)
> >
> >
> >Regards,
> >Jake Johnson
> >[EMAIL PROTECTED]
>
>
--
Asif Iqbal
http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08
There's no place like 127.0.0.1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
On Wed, Jul 23, 2003 at 04:47:35PM -0400, Joe Gainey wrote: > > Currently we have a web based application that is mostly reads (4:1 > r/w). It is using a single MySQL database server. Is there any way to > have two database servers in a master/master configuration such that > writes to either database server are replicated to eachother. Basically > even though we have a 4:1 ration of read/write the writes happen often > enought that when the database goes down the app stops working. I know > how to get this working in Oracle (insert big laugh here) but Oracle is > cost prohibitive. Any pointers? Any suggestions? If this is available > in the latest version that would be great. You can do it, yes. But beware that MySQL has no provisions for conflict resolution. So using auto-increment fields with primary keys (for example) can be a problem because of the inherent race condition. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 575,245,290 queries (346/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dbase calculations
Thanks - Original Message - From: "Adam Nelson" <[EMAIL PROTECTED]> To: "'Andy Jackman'" <[EMAIL PROTECTED]>; "'MySQL'" <[EMAIL PROTECTED]> Sent: Tuesday, July 29, 2003 12:26 AM Subject: RE: dbase calculations > I believe views in Oracle (SQL Server? Sybase?) can do this if you need > it. > > > -Original Message- > > From: Andy Jackman [mailto:[EMAIL PROTECTED] > > Sent: Monday, July 28, 2003 12:36 PM > > To: MySQL > > Subject: Re: dbase calculations > > > > > > Kalle, > > The usual way to do this is to create the table with the 2 real fields > > and then use a query to 'create' the sum field at run time. > > For example > > assume you have this table: > > > > create table my_table ( > > field_1 int(9), > > field_2 int(9) > > ); > > > > then you can write this query: > > SELECT field_1, field_2, (field_1 + field_2) AS my_sum FROM my_table; > > > > This print 3 'fields', the third one is called my_sum and contains the > > sum of the other two (the AS keyword gives a field a name). > > > > Hope this helps, > > Andy. > > > > > > > > > > Kalle Saarinen wrote: > > > > > > Hello > > > > > > I'm rather new when it comes to databases and I was hoping > > that someone > > > could help me out! I was just wondering is it possible to > > make a field in > > > MySQL dbase wich is a total of two other fields. > > > > > > ie. > > > > > > field_XX is a sum of field_1 and field2 > > > > > > Thanks > > > > > > -Kalle > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Category System schema
Does anyone know the best schema to achieve a multi-level category system? I need a person to be able to add categories on the fly, but then also specify sub categories and even possibly sub-categories of sub-cats. I have found two possible ways inside one table with using parentID, groupID, etc. But the issue I am now running into is that I have to sort on groupID to get the items to "group" correctly when showing in a HTML drop down list. Which means I can't sort alphabetically. If I do the order is not right...using Lasso 6 with some looping code to indent the subs in the list. On top of that I would also like the ability to assign a priority field for listing in that order as well. There has to be some kind of solution to this that I don't see. Any ideas? Thanks! -- <---> Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] <---> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know the maxmimum length of data in a perticular column?
Great. That does it. Thanks. Thanks. Thanks. Karam --- Dan Nelson <[EMAIL PROTECTED]> wrote: > In the last episode (Jul 28), Karam Chand said: > > Greetings > > > > Is there any query to know the maximum string > length > > of data in every column of the table? > > > > For eg, if I have data - > > > > 1,[EMAIL PROTECTED],2000-12-12 > > 20,[EMAIL PROTECTED],NULL > > ... > > > > It will return me > > > > 2,15,10 > > SELECT MAX(LENGTH(field1)) AS f1len, > MAX(LENGTH(field2)) AS f2len, > MAX(LENGTH(field3)) AS f3len FROM mytable; > > -- > Dan Nelson > [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicating records
Hi Karam, I would suggest to CREATE A TEMP TABLE with SELECT DISTINCT ... query in your table, then do TRUNCATE TABLE to your existing table.. You can just add indexes before you insert back the records from the TEMP TABLE... -- Richard Bornay ST Assembly Test Services Test Product Engineering Test Data Management Group 6824-1367 Karam Chand <[EMAIL PROTECTED]>29/07/2003 01:13 AM To: Venelin Arnaoudov <[EMAIL PROTECTED]>, [EMAIL PROTECTED], [EMAIL PROTECTED] cc: [EMAIL PROTECTED], (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: Re: Deleting duplicating records Great. So in that case I need to create a new table with similar structure with a additional UNIQUE index on email. Karam --- Venelin Arnaoudov <[EMAIL PROTECTED]> wrote: > I would copy all the records (_email_, max(version)) > to a new table, > drop the old one and then rename the new one > > Regards, > Venelin > > Karam Chand wrote: > > >Well that is OK if I have only one email. > > > >What if if I have thousands of users duplicated... > > > >Do I need to write SQL query 1000 times > > > >Karam > >--- Jeff McKeon <[EMAIL PROTECTED]> wrote: > > > > > >>How bout > >> > >>Delete from tablename where email like > >>[EMAIL PROTECTED] && ID <> 1 > >> > >>Jeff > >> > >> > >>>-Original Message- > >>>From: Karam Chand > [mailto:[EMAIL PROTECTED] > >>> > >>> > >>>Sent: Monday, July 28, 2003 10:33 AM > >>>To: [EMAIL PROTECTED] > >>>Subject: Deleting duplicating records > >>> > >>> > >>>Greetings > >>> > >>>I manage a website wherein i keep track of the > >>> > >>> > >>people > >> > >> > >>>email who have downloaded my software and the > >>> > >>> > >>version > >> > >> > >>>number. > >>> > >>>the structure is like - > >>> > >>>id int auto_increment primary key, > >>>email char, > >>>version > >>> > >>>now the same person can download different > version > >>>therfore my table has data like this - > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>2,[EMAIL PROTECTED],2.0 > >>>3,[EMAIL PROTECTED],3.0 > >>> > >>>Now I want to delete all the records wherein all > >>> > >>> > >>rows > >> > >> > >>>with duplicate email addresses are deleted so > that > >>> > >>> > >>i > >> > >> > >>>have data like > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>... > >>> > >>>What should be the query? Thanks in advance. > >>> > >>>Karam > >>> > >>>__ > >>>Do you Yahoo!? > >>>Yahoo! SiteBuilder - Free, easy-to-use web site > >>> > >>> > >>design > >> > >> > >>>software http://sitebuilder.yahoo.com > >>> > >>>-- > >>>MySQL General Mailing List > >>>For list archives: http://lists.mysql.com/mysql > >>>To unsubscribe: > >>>http://lists.mysql.com/mysql?> > >>> > >>> > >>[EMAIL PROTECTED] > >> > >> > >>> > >>> > >>-- > >>MySQL General Mailing List > >>For list archives: http://lists.mysql.com/mysql > >>To unsubscribe: > >> > >> > >> > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > >__ > >Do you Yahoo!? > >Yahoo! SiteBuilder - Free, easy-to-use web site > design software > >http://sitebuilder.yahoo.com > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: MySQL error code 1064:K@N@!:
I suspect that this is some kind of issue with privileges. However, the account I am using has been given 'ALL' privileges to the database I am trying to insert to. Am I save to assume that ALL includes insert privileges? --- Begin Message --- I cannot seem to find this in the MYSQL Reference. Are there other places I should check? The message text in my log started out with 'You have...'. But, it was truncated and I could not see the rest. It occurred on a submitted insert statement originating in an application. The insert statement works ok directly on the MySQL server. The ODBC connector performs its function in the application without any errors. Thanks in advance for any direction you can give. --- End Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can anyone stop this guy?:K@N@!:
Hi. Can anyone stop this guy and this message? I'm getting a ton of them. I might just direct his email to my delete bin. Thanks, Trina -Original Message- x-sender: Info [mailto:[EMAIL PROTECTED] From: Info [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 10:01 PM x-receiver: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Help with DELETE and a subquery Subject: Re: Help with DELETE and a subquery:[EMAIL PROTECTED]@!: Estoy tomando el sol q -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.51 Reference Manual needed:K@N@!:
Thanks Ken, I've found it. Great. Regards, Venelin Ken Menzel wrote: Hi Venelin, If you have the source code go into the ./Docs directory of the source tree and read the Makefile on how to build the format you want (PDF, HTML etc) if that is the machine you built on there may already be the HTML format document there. Hope this helps, Ken - Original Message - From: "Venelin Arnaoudov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, July 25, 2003 6:41 AM Subject: MySQL 3.23.51 Reference Manual needed Hi, I am looking for the Reference Manual for MySQL 3.23.51 (or the latest of the prior ones). Can anyone help me? Kindest regards, Venelin Arnaoudov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to find the closest result:K@N@!:
Hi, I'm working on a PHP based website that loads custom pricing for users where they logon. I'm trying to write a query to find the custom price of an item. Here is the table with all the prices in it. mysql> DESCRIBE item_price; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | majver | int(10) unsigned | | PRI | 0 | | | minver | int(10) unsigned | | PRI | 0 | | | iid| int(10) unsigned | | PRI | 0 | | | price | float(6,2) | | | 0.00| | ++--+--+-+-+---+ 4 rows in set (0.00 sec) majver and minver identify which price schedule to use. Each customer has a different majver minver combination. iid is the key to the products table. price is the price of the item. The price schedule where majver=0 and minver=0 is called list price, and is the only schedule that has a price for every single item. All the other combinations of majver and minver make up an sub-list of items that sell at the same discount level. If there is no price for a majver, minver combo, an attempt is made to use the price where minver=0, otherwise list price is used. To get the price of one item, I would do the following. mysql> SELECT * FROM item_price WHERE iid=3 AND majver IN (0,1) AND minver IN (0,10) ORDER BY majver DESC, minver DESC; +++-++ | majver | minver | iid | price | +++-++ | 1 | 10 | 3 | 98.81 | <= first choice | 1 | 0 | 3 | 91.21 | <= second choice | 0 | 0 | 3 | 152.02 | <= last choice +++-++ 3 rows in set (0.00 sec) Sorting the table puts the most relevent price first. I grab this row and ignore the rest. This type of query should never have more than 3 rows since the tree structure of item_price is only 3 levels deep. Most of the time I want to get information about more than one item at once. However this complicates things. I only want 1 row for each iid, specifically the row with the highest majver and minver for a given iid. EXAMPLES: mysql> SELECT * FROM item_price WHERE iid IN (3,4) AND majver IN (0,1) AND minver IN (0,10) ORDER BY majver DESC, minver DESC; +++-++ | majver | minver | iid | price | +++-++ | 1 | 10 | 3 | 98.81 | | 1 | 0 | 3 | 91.21 | <= Need to eliminate these rows | 0 | 0 | 3 | 152.02 | <= | 0 | 0 | 4 | 49.29 | +++-++ 4 rows in set (0.00 sec) mysql> SELECT * FROM item_price WHERE iid IN (3,5) AND majver IN (0,1) AND minver IN (0,10) ORDER BY majver DESC, minver DESC; +++-++ | majver | minver | iid | price | +++-++ | 1 | 10 | 3 | 98.81 | | 1 | 10 | 5 | 89.77 | | 1 | 0 | 3 | 91.21 | <= Need to eliminate these rows | 1 | 0 | 5 | 82.87 | <= | 0 | 0 | 3 | 152.02 | <= | 0 | 0 | 5 | 138.11 | <= +++-++ 6 rows in set (0.00 sec) mysql> Is it possible to get a result set with 1 row for each iid specified, and the price where the majver and minver are the largest combination for each iid? I would like to be able to do this with one query. Currently I've had to look up each price individually, but a page that loads prices for 100+ items creates a lot of overhead in running separate queries. The webserver and mysql server are over 3000 miles apart, so bandwidth is kind of an issue, but processing power on the database server is not. Any help would be appreciated. Brian Newsham Krackeler Scientific Inc. [EMAIL PROTECTED] 518-462-4281 ext. 121 518-462-6011
Selecting unique values:K@N@!:
I have two tables as below: CREATE TABLE domain_types ( type_id INT(4) NOT NULL AUTO_INCREMENT, name VARCHAR(10) UNIQUE NOT NULL, description VARCHAR(75), PRIMARY KEY(type_id) ) TYPE=INNODB COMMENT="Types of domains we store"; CREATE TABLE domains ( domain_id INT(6) NOT NULL AUTO_INCREMENT, domain VARCHAR(50) UNIQUE NOT NULL, type_id INT(4) NOT NULL, PRIMARY KEY(domain_id) ) TYPE=INNODB COMMENT="Domains"; I can get the below result easily: mysql> select distinct t.name,d.domain from domain_types t, domains d WHERE t.type_id=d.type_id ORDER BY t.type_id; +--++ | name | domain | +--++ | hostdom1 | abc123.com | | hostdom1 | abc124.com | | hostdom1 | abc125.com | | hostdom2 | abc127.com | | hostdom2 | abc126.com | | hostdom3 | abc128.com | | hostdom4 | abc129.com | | hostdom4 | abc130.com | +--++ 8 rows in set (0.01 sec) But what I really need is a result like +--++ | name | domain | +--++ | hostdom1 | abc123.com | | hostdom2 | abc127.com | | hostdom3 | abc128.com | | hostdom4 | abc129.com | +--++ where only the t.name and d.domain pair with the highest domain_id for each type_id are given. Is there a way to do this without resulting to seperate SQL queries for each entry in the domain_types table? I'm using MySQL 4.0.14 on FreeBSD 5.1. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL error code 1064:K@N@!:
I have managed to successfully run the following MySQL statement directly on the server hoasting MySQL: insert tblMealCounts set ProgramCode = '140', yymmdd = '030725', MealCode = '2', MealCount = '11'; And, I can view the table afterwards and see the record ok. when I run it from an application - ng. Could there be a problem with my ODBC driver? Here is its description: MySQL ODBC 3.51 Driver DSN. It is running on an old WinNT box with SP6a applied, and accessing a MySQL server running on a virtual RedHat v7.2 server. I have been able to do any selects I want ok. This is my first attempt to insert. >>> David Precious <[EMAIL PROTECTED]> 07/25/03 11:54 AM >>> On Friday 25 July 2003 1:05 pm, Kenneth Illingsworth wrote: > I cannot seem to find this in the MYSQL Reference. Are there other places I > should check? It's a syntax error. A quick Google (http://www.google.com/search?q=mysql+error+1064) would have found it for you. > The message text in my log started out with 'You have...'. > But, it was truncated and I could not see the rest. It would have been saying "You have an error in your SQL syntax near > It occurred on a > submitted insert statement originating in an application. The insert > statement works ok directly on the MySQL server. It would appear that the application is generating the INSERT statement incorrectly. HTH! David Precious http://www.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
Richard Bolen wrote: This works! I was then wondering how to get the total number of all jobs that this condition is true for? Just include count(distinct j.jobid) in the SELECT list. Bruce select j.* FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* all selected columns */ HAVING min(abs(s.status - 1)) > 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SLEEP command in mysql
Hi all, Is there a 'sleep' command in mysql? I'm trying to take a backup of MySQL instance from a ksh script, and I want to lock all the tables before I take a back of all datafiles. So, once I issue the lock tables command and exit the session to take backup of the OS files, the session (and there by the lock) is lost. So, I'm trying to issue a lock command from one script and let it sleep till my backup is completed. Any help is appreciated. Thankx in advance, SB
Re: fulltext indexing and query speeds?
Hi! On Jul 28, Niels Larsen wrote: > Greetings, > > Do anyone know where to find a description of how fulltext indexing > and query speeds depend on data volume? I have 30-40 gb of text > distributed across 30-40 million entries, a medium size database I > suppose. But I have not even been able to test a query yet, because > indexing is on its second day. Which I think cant be right. I will come > up with the details if anyone asks, but how long is indexing supposed > to take on different amounts of data? if there is a way to predict how > long it will take, then that might work; then we may simply buy a > machine do nothing but indexing .. assuming the "boolean mode" > queries finish in "interactive time", ie seconds, not minutes. I use v. > 4.0.12, but will happily upgrade if indexing becomes faster. About "indexing is on its second day"... How is it done ? You insert data into the table with FULLTEXT index ? Or you add an index to existing table (with ALTER TABLE or alike) ? What does SHOW PROCESSLIST shows ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Select with join query question
This works! I was then wondering how to get the total number of all jobs that this condition is true for? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 5:26 PM To: Bruce Feist Cc: Richard Bolen; [EMAIL PROTECTED] Subject: Re: Re: Select with join query question [snip] > Rich's solution, which I edited out, was a good one. But, if you > really > want to do it with a single JOIN, try this: > > select j.* > FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* > all selected columns */ HAVING min(abs(s.status - 1)) > 0 > > I leave it as an exercise to the reader to figure out why this works > (if > it does -- I haven't tested it!). If status = 1 is the lowest possible > value for status, you can simplify this a bit. A quick test seems to show it works. Though it doesn't pick up the case where status IS NULL, which occurs when there's a job but no matching submission. One disadvantage to your method: it requires computing a formula for each tuple, which slows things down (in principle; not sure it really matters in practice). > > Bruce Feist > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please HELP Romanian charset Collate in MySQL
Hi! On Jul 28, Primaria Falticeni wrote: > Hello, > > Simply I made a table with romanian characters "aAsStT". I tried to > sort it, in fact to order it in a query. > > How can I do this? Please give me an example at how can I change the > latin2.conf in a romanian one to fairly sort the chars for me. I must do > change the behaviour and I don't know how. check http://www.mysql.com/doc/en/Localisation.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why the query is not using index?
Karam,
If you look closely you'll see that it is not using the index in either of
your examples. Each query claims to scan all 33914 rows in the table, which
seems like the correct behavior. MySQL does not have an index built on the
substrings, so it has to generate each substring before it can check if that
value is less than 500.
-Rob
-Original Message-
From: Karam Chand [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 3:51 PM
To: [EMAIL PROTECTED]
Subject: Why the query is not using index?
Greetings
Having a table with the following structure -
+-+---+---+--+-+-+--
--+
| Field | Type | Collation | Null |
Key | Default | Extra
|
+-+---+---+--+-+-+--
--+
| email | char(50) | latin1_swedish_ci | YES |
MUL | NULL|
|
| is_sent | enum('Y','N') | latin1_swedish_ci | YES |
| NULL|
|
| id | int(10) | binary| |
PRI | NULL| auto_incr
ement |
+-+---+---+--+-+-+--
--+
If I issue a command like this -
explain select substring(id,1,4) from email_table
where substring(id,1,4) < 300 limit 500;
The result returned is -
++-+-+---+---+-+
-+--
+---+--+
| id | select_type | table | type |
possible_keys | key | key_len | r
ef | rows | Extra|
++-+-+---+---+-+
-+--
+---+--+
| 1 | SIMPLE | email_table | index | NULL
| PRIMARY | 4 | N
ULL | 33914 | Using where; Using index |
++-+-+---+---+-+
-+--
+---+--+
This means it is using the index.
But, if issue a command like -
explain select email from email_table where
substring(id,1,4) < 300 limit 500;
++-+-+--+---+--+-+--
+---+-+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra |
++-+-+--+---+--+-+--
+---+-+
| 1 | SIMPLE | email_table | ALL | NULL
| NULL |NULL | NULL
| 33914 | Using where |
++-+-+--+---+--+-+--
+---+-+
the KEY column is NULL i.e. it is not using Index?
Why the query is not using index?
Thanks in advance.
Karam
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.013 - How to set innodb_fast_shutdown?
Eddy,
thank you for the bug report. I have now fixed it to 4.0.15 so that you can
set
innodb_fast_shutdown=0
Regards,
Heikki
- Original Message -
From: "Eddy Muljono" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, July 28, 2003 5:26 AM
Subject: Re: MySQL 4.013 - How to set innodb_fast_shutdown?
> I have tried it, the variable "innodb_fast_shutdown" still ON.
> Below is my.ini (in C:\winnt):
>
> [mysqld]
> basedir=C:/mysql
> #bind-address=19.195.2.117
> datadir=C:/mysql/data
> default-table-type=innodb
> set-variable=innodb_fast_shutdown=0
> set-variable=innodb_buffer_pool_size=18M
> set-variable=innodb_log_buffer_size=3M
> set-variable=key_buffer=12M
> #language=C:/mysql/share/your language directory
> #slow query log#=
> #tmpdir#=
> #port=3306
> [WinMySQLadmin]
> Server=C:/mysql/bin/mysqld-nt.exe
>
> How can I set Innodb_fast_shutdown to OFF ?
> Thanks.
>
> [EMAIL PROTECTED] ("Heikki Tuuri") wrote in message
news:<[EMAIL PROTECTED]>...
> > Eddy,
> >
> > - Original Message -
> > From: "Eddy Muljono" <[EMAIL PROTECTED]>
> > Newsgroups: mailing.database.mysql
> > Sent: Friday, July 25, 2003 11:48 AM
> > Subject: MySQL 4.013 - How to set innodb_fast_shutdown?
> >
> >
> > > I am using MySQL 4.013 (Win2000 Prof).
> > > How to set innodb_fast_shutdown to OFF in My.ini ?
> > > I have try to set it OFF but it didn't change ( I check it using Show
> > > Status Variables).
> >
> > please try setting it 0.
> >
> > > Thanks.
> >
> > Regards,
> >
> > Heikki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dbase calculations
I believe views in Oracle (SQL Server? Sybase?) can do this if you need it. > -Original Message- > From: Andy Jackman [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 12:36 PM > To: MySQL > Subject: Re: dbase calculations > > > Kalle, > The usual way to do this is to create the table with the 2 real fields > and then use a query to 'create' the sum field at run time. > For example > assume you have this table: > > create table my_table ( > field_1 int(9), > field_2 int(9) > ); > > then you can write this query: > SELECT field_1, field_2, (field_1 + field_2) AS my_sum FROM my_table; > > This print 3 'fields', the third one is called my_sum and contains the > sum of the other two (the AS keyword gives a field a name). > > Hope this helps, > Andy. > > > > > Kalle Saarinen wrote: > > > > Hello > > > > I'm rather new when it comes to databases and I was hoping > that someone > > could help me out! I was just wondering is it possible to > make a field in > > MySQL dbase wich is a total of two other fields. > > > > ie. > > > > field_XX is a sum of field_1 and field2 > > > > Thanks > > > > -Kalle > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Select with join query question
[snip] > Rich's solution, which I edited out, was a good one. But, if you really > want to do it with a single JOIN, try this: > > select j.* > FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid > GROUP BY /* all selected columns */ > HAVING min(abs(s.status - 1)) > 0 > > I leave it as an exercise to the reader to figure out why this works (if > it does -- I haven't tested it!). If status = 1 is the lowest possible > value for status, you can simplify this a bit. A quick test seems to show it works. Though it doesn't pick up the case where status IS NULL, which occurs when there's a job but no matching submission. One disadvantage to your method: it requires computing a formula for each tuple, which slows things down (in principle; not sure it really matters in practice). > > Bruce Feist > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT command question
Hi there, If you go to the MySQL manual (chapter 4.3.1) you'll see that GRANT ALL... does not include the granting of privileges to others. So you must use something like: GRANT ALL [...] WITH GRANT OPTION; The manual has also downloadable versions. Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net > -Original Message- > From: Charles Cantrell [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 11:55 PM > To: '[EMAIL PROTECTED]' > Subject: GRANT command question > > > I have recently set up mySQL on a Mandrake release of Linux (Version 7 of > Mandrake, I believe), using the binary 4.0.13 standard release. > > The set up and start up all were normal, as far as I could tell, with no > warnings or error messages. > > In nearly all respects, the database appears to be running as expected. I > have the book "PHP and mySQL Web Development" by Luke Welling and Laura > Thomson, and have been working through the examples there. > > The question I have is about the GRANT command, and the apparent results > there. I don't know if my problem is one of understanding what the results > should be, or if I have another problem. > > I am attempting to set up an administrative user, that has the same > privileges as root. I have been able to set up a user that appears to have > all privileges, with the exception of GRANT privileges to other > users. This > user can create new database tables, insert records, modify them, delete > them and so on. > > But, when this user tries to GRANT privileges on any database, > even ones it > has created, an 'access denied' error is generated. > > I am obviously missing some part of the process, but I am not sure what it > is. Can you explain the piece I am missing? I would appreciate it > very much. > Thanks. > > Charles Cantrell > > PS: I submitted a much more detailed message showing the GRANT > table set up > and so on, but it was rejected by the filters. If this information would > help someone answer my question, let me know and I can send it directly, > maybe. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicating records
Try this: Delete your_table t Where t.version > min(t.version) Group by t.email Lin -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 11:27 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Deleting duplicating records Well that is OK if I have only one email. What if if I have thousands of users duplicated... Do I need to write SQL query 1000 times Karam --- Jeff McKeon <[EMAIL PROTECTED]> wrote: > How bout > > Delete from tablename where email like > [EMAIL PROTECTED] && ID <> 1 > > Jeff > > -Original Message- > > From: Karam Chand [mailto:[EMAIL PROTECTED] > > > Sent: Monday, July 28, 2003 10:33 AM > > To: [EMAIL PROTECTED] > > Subject: Deleting duplicating records > > > > > > Greetings > > > > I manage a website wherein i keep track of the > people > > email who have downloaded my software and the > version > > number. > > > > the structure is like - > > > > id int auto_increment primary key, > > email char, > > version > > > > now the same person can download different version > > therfore my table has data like this - > > > > 1,[EMAIL PROTECTED],1.0 > > 2,[EMAIL PROTECTED],2.0 > > 3,[EMAIL PROTECTED],3.0 > > > > Now I want to delete all the records wherein all > rows > > with duplicate email addresses are deleted so that > i > > have data like > > > > 1,[EMAIL PROTECTED],1.0 > > ... > > > > What should be the query? Thanks in advance. > > > > Karam > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site > design > > software http://sitebuilder.yahoo.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> > [EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design decision
Lin, thanks for your input. Indeed I forgot to mention there is a many-to-many relation between users and groups. I'm inclined though to use Solution 3. My main concern with 2 and 3 was not to exceed the column allocated space for the concatenated string, when it grows with the number of users in a group. I calculated the space requirement for storing a string resulted by 1 milion user IDs each separated by a character. It's taken about 8 milion bytes to store the whole string. A MediumText gives me 16 MB so I think I'm pretty much covered. As I said my main requirements would be speed when a Select is performed. So I prefer to do some additional logic in perl and to retrieve faster results in one Select returning only one row. Hope I'm not wrong ;-) Thanks again, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net > -Original Message- > From: Lin Yu [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 9:33 PM > To: [EMAIL PROTECTED]; 'mysqllist' > Subject: RE: Design decision > > > Lian, > > Between your design solutions (1) and (3), you need to decide, > from the logical > business requirement, whether the nature of the relationship > between user and > group is one-to-many (a group may have many users, and each user > may belong to > exactly one group) or many-to-many (a group may have many users, > and each user > may belong to multiple groups). For the former, use Solution (3), for the > latter, use Solution (1). Granted, Solution (3) is a subset of > Solution (1), but > requires more resources which might be a waste if you only need > represent a > one-to-many relationship. > > Your solution (2) has no restriction on the granularity of the > relationship > i.e., it can support both; it all depends on your implementation > outside SQL, > thus is not really a DB schematic means. In this case, the relationship is > actually interpreted and maintained by your application program, > not by DBMS. > > In making a choice between Solution (2) and the other two you > need to consider > the performance difference and code maintenance. > > Best regards, > > Lin > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 10:22 AM > To: mysqllist > Subject: Design decision > > Hi everyone, > > Just wanted your expert opinion on the following: > > I'm implementing an authorization system with user/group > permissions stored > in a database. I have a Users table and a Group table, identical in > structure: > mysql> desc users; > mysql> desc groups; > +---+-+ > | Field | Type| > +---+-+ > | id| int(11) | > | name | varchar(30) | > +---+-+ > > Now, my question is "How to store BEST the relations between users and > groups?". > > Solution 1. I use a separate table with this structure: > mysql> desc users2groups; > +-+-+ > | Field | Type| > +-+-+ > | idUser | int(11) | > | idGroup | int(11) | > +-+-+ > and I add one record for each user <--> group mapping. So a SELECT will > return potentially many rows for one group or one user. > > Solution 2. I construct and maintain a string separated by colons (let's > say) for each group. So in the users2groups I'd have for example: > | idGroup | idUser | > | 123 | 2:3:4:8:9:10 | > > Similary, since I need also user-to-group lookups I construct a string for > the "group membership of a user" so I can have in the same table: > | idGroup | idUser | > | 123 | 2:3:4:8:9:10 | > | 123:456 | 4| > > Solution 3. Similary to Solution 2 but using the initial tables extended > with one more field to accomodate the membership constructed string like: > +---+-+ > | Field | Type| > +---+-+ > | id| int(11) | > | name | varchar(30) | > | member_of | text| > +---+-+ > > In Solution 1 I have multiple rows returned. In solution 2,3 I have only > one. > Solution 1 is scalable however Solution 2,3 can reach (potentially) the > limits of the column specification (unlikely though). > > Assuming I'm interested in maximum speed at the authorization moment (and > not at administrative moment), and that I'll have a big number of > users and > groups, and I access the database via Perl (so no problem to > construct/deconstruct strings), what do you think is the best solution? > > Thank you for your time, > > Lian Sebe, M.Sc. > Freelance Analyst-Programmer > www.programEz.net > > "I'm not mad. I've been in bad mood for the last 30 years..." > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT command question
I have recently set up mySQL on a Mandrake release of Linux (Version 7 of Mandrake, I believe), using the binary 4.0.13 standard release. The set up and start up all were normal, as far as I could tell, with no warnings or error messages. In nearly all respects, the database appears to be running as expected. I have the book "PHP and mySQL Web Development" by Luke Welling and Laura Thomson, and have been working through the examples there. The question I have is about the GRANT command, and the apparent results there. I don't know if my problem is one of understanding what the results should be, or if I have another problem. I am attempting to set up an administrative user, that has the same privileges as root. I have been able to set up a user that appears to have all privileges, with the exception of GRANT privileges to other users. This user can create new database tables, insert records, modify them, delete them and so on. But, when this user tries to GRANT privileges on any database, even ones it has created, an 'access denied' error is generated. I am obviously missing some part of the process, but I am not sure what it is. Can you explain the piece I am missing? I would appreciate it very much. Thanks. Charles Cantrell PS: I submitted a much more detailed message showing the GRANT table set up and so on, but it was rejected by the filters. If this information would help someone answer my question, let me know and I can send it directly, maybe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with foreign key constraint when updating
Jeremiah,
please send me a dump of the whole table structure in that database. No need
to send the actual data. Just CREATE TABLEs.
Also send me an UNEDITED capture of a mysql session where an insert fails in
that that error, and which shows that the table really exists in the
database. Below it is possible the table really did not exist at the time
you got the error. Can you repeat the error manually by trying an insert?
Regards,
Heikki
- Original Message -
From: "Jeremiah Jacks" <[EMAIL PROTECTED]>
To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 12:08 AM
Subject: RE: Error with foreign key constraint when updating
Oh sorry... I was trying to disguise my db name with "mydb", heh. Oh well...
There is only one db.. It is tamiyausa.
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 11:57 AM
To: [EMAIL PROTECTED]
Subject: Re: Error with foreign key constraint when updating
Jeremiah,
you are inserting in the database 'tamiyausa' to the table
'product_access_level'.
> But the parent table mydb/product does not currently exist!
It complains there is no table 'product' in database 'mydb'.
Can you do
mysql> use tamiyausa
mysql> SHOW CREATE TABLE product_access_level;
mysql> use mydb
mysql> SHOW CREATE TABLE product;
?
I am suspecting you printed these from wrong databases.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL
technical support from https://order.mysql.com/
- Original Message -
From: ""Jeremiah Jacks"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, July 28, 2003 9:11 PM
Subject: Error with foreign key constraint when updating
>
> I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using
> = the pre-compiled binaries.
>
> I have a database with INNODB tables.
> When I insert a row into one of the child tables, I get the following
> = MySQL
> error:
>
> INSERT INTO product_access_level (product_id,access_level_id) VALUES
> ('10201','2') [nativecode=3D1216 ** Cannot add or update a child row:
> a foreign key constraint fails]
>
> I was not getting this error before with the previous version of
> MySQL(3.23.57) that I had installed.
> Below is the output of the latest foreign key error from 'SHOW INNODB
> STATUS':
> I am not sure what the problem is here. In the INNODB STATUS it says =
> that my product table doesn't exist??
> Below the status are my table structures. Any input would be helpful.
> Thanks!
>
>
> LATEST FOREIGN KEY ERROR
>
> 030728 13:15:03 Transaction:
> TRANSACTION 0 554436, ACTIVE 0 sec, process no 22745, OS thread id =
> 864270 insert
> ing, thread declared inside InnoDB 500
> 1 lock struct(s), heap size 320
> MySQL thread id 203, query id 11471 localhost root update
> INSERT INTO product_access_level (product_id,access_level_id) VALUES
> ('10201','2
> ')
> Foreign key constraint fails for table tamiyausa/product_access_level:
> ,
> CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` =
> (`id`)
> ON D
> ELETE CASCADE
> Trying to add to index PRIMARY tuple:
> 0: len 5; hex 3130323031; asc 10201;; 1: len 4; hex 0002; asc =
> ;; 2:
> len
> 6; hex 000875c4; asc u.;; 3: len 7; hex 68338b; asc
> h3.;;
> But the parent table mydb/product does not currently exist!
> -=
> ---
> -
>
>
> CREATE TABLE `product_access_level` (
> `product_id` varchar(10) NOT NULL default '', `access_level_id`
> int(10) unsigned NOT NULL default '0', PRIMARY KEY
> (`product_id`,`access_level_id`), KEY `idx_product_id` (`product_id`),
> KEY `idx_access_level_id` (`access_level_id`),
> CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` =
> (`id`) ON
> DELETE CASCADE,
> CONSTRAINT `0_282` FOREIGN KEY (`access_level_id`) REFERENCES =
> `access_level`
> (access_level_id`) ON DELETE CASCADE) TYPE=3DInnoDB
>
> CREATE TABLE `product` (
> `id` varchar(10) NOT NULL default '',
> `name` varchar(100) NOT NULL default '',
> `category_id` int(10) unsigned default NULL,
> `retail_value` float unsigned default NULL,
> `dealer_price` float unsigned default NULL, `minimum_purchase` int(10)
> unsigned default NULL, `case_quantity` int(10) unsigned default NULL,
> `status_id` char(2) default NULL,
> `description` text,
> PRIMARY KEY (`id`),
> KEY `idx_category_id` (`category_id`),
> KEY `idx_status_id` (`status_id`),
> CONSTRAINT `0_274` FOREIGN KEY (`status_id`) REFERENCES `product_status`
> (`id`) ON DELETE SET NULL,
> CONSTRAINT `0_34` FOREIGN KEY (`category_id`) REFERENCES `category`
> (`category_id`) ON DELETE SET NULL) TYPE=3DInnoDB
>
> CREATE TABLE `access_level` (
> `access_level_id` int(10) unsigned NOT NULL default '0',
> `access_level_name` varchar(25) NOT NULL default '', PRIMARY KEY
> (`access_level_id`))
Testing Blocking
My posts have been blocked. Testing simple email. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rights to create table, select, then drop table..
Hi there, I tested your setup and wors fine for me. I issued this commands as mysql's root: grant usage on *.* to [EMAIL PROTECTED] identified by 'nelu'; grant select,insert,update,create,drop on test.* to nelu@'%'; Then I logged in as nelu with: mysql -u nelu -p test create table test1 (id int, nume text); insert into test1 values (1,"cico"); select * from test1; +--+--+ | id | nume | +--+--+ |1 | cico | +--+--+ drop table test1; So it's clearly working. Therefore I recommend you to do a select * from mysql.user; to see if you have other "rules" that cancel the good ones somehow. In addition, this is from manual and maybe useful for future security: "You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself." HTH, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net > -Original Message- > From: Jeff McKeon [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 10:43 PM > To: [EMAIL PROTECTED] > Subject: FW: rights to create table, select, then drop table.. > > > Nobody has any ideas on this one? > > Jeff > > -Original Message- > From: Jeff McKeon > Sent: Friday, July 25, 2003 3:23 PM > To: [EMAIL PROTECTED] > Subject: rights to create table, select, then drop table.. > > > I have a need to get data from the db that requires me to > > 1) do a select and create a new table with the results > 2) run a query against that new table > 3) drop the new table > > I have a script on my server that does this using the root account that > has all on *.* for the db. It works fine. > > I now want to get these results on a web page. > I want to create a new db user for my .php web page to use to connect to > the db that only has the needed priviledges on that specific db to get > the job done. > > what priviledges do I need to give that user? > > currently I have the following but the user can't even log into the db > from the command line.. > > mysql> show grants for user; > +--- > -+ > | Grants for [EMAIL PROTECTED] | > +--- > -+ > | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD > '6fe4c0ab2cf30ae3' | > | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' | > +--- > -+ > 2 rows in set (0.00 sec) > > when I do a "show grants for user", what should I see to allow what I > want? > > Thanks, > > Jeff McKeon > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
I was wondering if you were going to come back with that. Your going to need to using grouping then. Something like this should do it: SELECT *,SUM(s.status) AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS s ON j.job_id=s.job_id WHERE ActiveJob<1 OR ActiveJob IS NULL GROUP BY j.job_id I'm not sure what type of data is in your status field, so I'm not sure if SUM is the right thing you are looking. The above query should give you all jobs without any Sumission records and those that have matching submission records but whose status ends up to be zero. You may not need to check for NULL values, I forget if MySQL considers NULL less than 1 or anything else. I don't think it does. On Monday, July 28, 2003, at 04:12 PM, Richard Bolen wrote: I think this gets me all the Jobs that have no submissions but I'm really looking for any job that doesn't have a submission with a status of 1. That means I need Jobs that don't have submissions plus jobs with submissions with exclusively non-1 statuses. The problem is when a job has more than one submission associated with it (and at least one submission has a non-1 status). -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
[EMAIL PROTECTED] wrote: From: "Richard Bolen" <[EMAIL PROTECTED]> Date: 2003/07/28 Mon PM 01:37:27 CDT I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. I haven't been able to think of a clever way to do it using only JOINs. Rich's solution, which I edited out, was a good one. But, if you really want to do it with a single JOIN, try this: select j.* FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* all selected columns */ HAVING min(abs(s.status - 1)) > 0 I leave it as an exercise to the reader to figure out why this works (if it does -- I haven't tested it!). If status = 1 is the lowest possible value for status, you can simplify this a bit. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext indexing and query speeds?
Greetings, Do anyone know where to find a description of how fulltext indexing and query speeds depend on data volume? I have 30-40 gb of text distributed across 30-40 million entries, a medium size database I suppose. But I have not even been able to test a query yet, because indexing is on its second day. Which I think cant be right. I will come up with the details if anyone asks, but how long is indexing supposed to take on different amounts of data? if there is a way to predict how long it will take, then that might work; then we may simply buy a machine do nothing but indexing .. assuming the "boolean mode" queries finish in "interactive time", ie seconds, not minutes. I use v. 4.0.12, but will happily upgrade if indexing becomes faster. Niels L Niels Larsen, Associate Professor Bioinformatics Research Center (BIRC) Aarhus University Hoegh Guldbergsgade 10 DK 8000 Aarhus C Denmark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
> > From: "Richard Bolen" <[EMAIL PROTECTED]> > Date: 2003/07/28 Mon PM 01:37:27 CDT > To: <[EMAIL PROTECTED]> > Subject: Select with join query question > > I'm trying to write a select query that involves 2 tables. One table > (Submissions) has a one to many relationship with the other table > (Jobs). I'm trying to find all the records in Jobs that do NOT have a > corresponding record in Submissions with a status of 1. > > The problem I'm having is that when there is more than one record in > Submissions that match a record in Jobs and one Submissions record has a > status of 1 and one record doesn't, my query matches the one with status > != 1 and returns the record for Jobs (even though it has a record in > Submissions with a status of 1 also). I haven't been able to think of a clever way to do it using only JOINs. Moreover, there are probably clever/elegant solutions using subselects (that is, nested queries), but your version of MySQL doesn't have those. Here's how I would do it. (Sorry if my notation deviates from yours a little): CREATE TEMPORARY TABLE tmp SELECT jobs.id AS jobs_id FROM jobs LEFT JOIN sub ON jobId=jobs.id WHERE status=1; SELECT jobs.id FROM jobs LEFT JOIN tmp ON jobs.id=jobs_id WHERE jobs_id IS NULL; > > I've tried a variety of queries including left outer joins and more > simple join relationships. I'm using MySQL 3.23.47 on Windows. > > Here's an example query: > > select j.job_id from jobs j left outer join submissions s on (j.job_id = > s.job_id) where s.status_id != 1 group by j.job_id > > Any help is greatly appreciated. > > Rich > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select with join query question
I think this gets me all the Jobs that have no submissions but I'm really looking for any job that doesn't have a submission with a status of 1. That means I need Jobs that don't have submissions plus jobs with submissions with exclusively non-1 statuses. The problem is when a job has more than one submission associated with it (and at least one submission has a non-1 status). Something like this should work. You want to do a left join on Jobs so you don't filter out those without submission matches. The resulting left join will have a value of NULL for any fields joined from Submissions that don't have a match in Jobs. Just include at least on field from Submissions and test for null on that field. SELECT *,s.status AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS s ON j.job_id=s.job_id WHERE s.status IS NULL On Monday, July 28, 2003, at 02:37 PM, Richard Bolen wrote: > I'm trying to write a select query that involves 2 tables. One table > (Submissions) has a one to many relationship with the other table > (Jobs). I'm trying to find all the records in Jobs that do NOT have a > corresponding record in Submissions with a status of 1. > > The problem I'm having is that when there is more than one record in > Submissions that match a record in Jobs and one Submissions record has > a status of 1 and one record doesn't, my query matches the one with > status > != 1 and returns the record for Jobs (even though it has a record in > Submissions with a status of 1 also). > > I've tried a variety of queries including left outer joins and more > simple join relationships. I'm using MySQL 3.23.47 on Windows. > > Here's an example query: > > select j.job_id from jobs j left outer join submissions s on (j.job_id > = > s.job_id) where s.status_id != 1 group by j.job_id > > Any help is greatly appreciated. > > Rich > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know the maxmimum length of data in a perticular column?
In the last episode (Jul 28), Karam Chand said: > Greetings > > Is there any query to know the maximum string length > of data in every column of the table? > > For eg, if I have data - > > 1,[EMAIL PROTECTED],2000-12-12 > 20,[EMAIL PROTECTED],NULL > ... > > It will return me > > 2,15,10 SELECT MAX(LENGTH(field1)) AS f1len, MAX(LENGTH(field2)) AS f2len, MAX(LENGTH(field3)) AS f3len FROM mytable; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to know the maxmimum length of data in a perticular column?
Greetings Is there any query to know the maximum string length of data in every column of the table? For eg, if I have data - 1,[EMAIL PROTECTED],2000-12-12 20,[EMAIL PROTECTED],NULL ... It will return me 2,15,10 I hope I make myself clear. Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with foreign key constraint when updating
Jeremiah,
you are inserting in the database 'tamiyausa' to the table
'product_access_level'.
> But the parent table mydb/product does not currently exist!
It complains there is no table 'product' in database 'mydb'.
Can you do
mysql> use tamiyausa
mysql> SHOW CREATE TABLE product_access_level;
mysql> use mydb
mysql> SHOW CREATE TABLE product;
?
I am suspecting you printed these from wrong databases.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: ""Jeremiah Jacks"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, July 28, 2003 9:11 PM
Subject: Error with foreign key constraint when updating
>
> I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using =
> the
> pre-compiled binaries.
>
> I have a database with INNODB tables.
> When I insert a row into one of the child tables, I get the following =
> MySQL
> error:
>
> INSERT INTO product_access_level (product_id,access_level_id) VALUES
> ('10201','2') [nativecode=3D1216 ** Cannot add or update a child row: a
> foreign key constraint fails]
>
> I was not getting this error before with the previous version of
> MySQL(3.23.57) that I had installed.
> Below is the output of the latest foreign key error from 'SHOW INNODB
> STATUS':
> I am not sure what the problem is here. In the INNODB STATUS it says =
> that my
> product table doesn't exist??
> Below the status are my table structures. Any input would be helpful.
> Thanks!
>
>
> LATEST FOREIGN KEY ERROR
>
> 030728 13:15:03 Transaction:
> TRANSACTION 0 554436, ACTIVE 0 sec, process no 22745, OS thread id =
> 864270
> insert
> ing, thread declared inside InnoDB 500
> 1 lock struct(s), heap size 320
> MySQL thread id 203, query id 11471 localhost root update
> INSERT INTO product_access_level (product_id,access_level_id) VALUES
> ('10201','2
> ')
> Foreign key constraint fails for table tamiyausa/product_access_level:
> ,
> CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` =
> (`id`)
> ON D
> ELETE CASCADE
> Trying to add to index PRIMARY tuple:
> 0: len 5; hex 3130323031; asc 10201;; 1: len 4; hex 0002; asc =
> ;; 2:
> len
> 6; hex 000875c4; asc u.;; 3: len 7; hex 68338b; asc
> h3.;;
> But the parent table mydb/product does not currently exist!
> -=
> ---
> -
>
>
> CREATE TABLE `product_access_level` (
> `product_id` varchar(10) NOT NULL default '',
> `access_level_id` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`product_id`,`access_level_id`),
> KEY `idx_product_id` (`product_id`),
> KEY `idx_access_level_id` (`access_level_id`),
> CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` =
> (`id`) ON
> DELETE CASCADE,
> CONSTRAINT `0_282` FOREIGN KEY (`access_level_id`) REFERENCES =
> `access_level`
> (access_level_id`) ON DELETE CASCADE) TYPE=3DInnoDB
>
> CREATE TABLE `product` (
> `id` varchar(10) NOT NULL default '',
> `name` varchar(100) NOT NULL default '',
> `category_id` int(10) unsigned default NULL,
> `retail_value` float unsigned default NULL,
> `dealer_price` float unsigned default NULL,
> `minimum_purchase` int(10) unsigned default NULL,
> `case_quantity` int(10) unsigned default NULL,
> `status_id` char(2) default NULL,
> `description` text,
> PRIMARY KEY (`id`),
> KEY `idx_category_id` (`category_id`),
> KEY `idx_status_id` (`status_id`),
> CONSTRAINT `0_274` FOREIGN KEY (`status_id`) REFERENCES `product_status`
> (`id`) ON DELETE SET NULL,
> CONSTRAINT `0_34` FOREIGN KEY (`category_id`) REFERENCES `category`
> (`category_id`) ON DELETE SET NULL) TYPE=3DInnoDB
>
> CREATE TABLE `access_level` (
> `access_level_id` int(10) unsigned NOT NULL default '0',
> `access_level_name` varchar(25) NOT NULL default '',
> PRIMARY KEY (`access_level_id`)) TYPE=3DInnoDB
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why the query is not using index?
Greetings
Having a table with the following structure -
+-+---+---+--+-+-+--
--+
| Field | Type | Collation | Null |
Key | Default | Extra
|
+-+---+---+--+-+-+--
--+
| email | char(50) | latin1_swedish_ci | YES |
MUL | NULL|
|
| is_sent | enum('Y','N') | latin1_swedish_ci | YES |
| NULL|
|
| id | int(10) | binary| |
PRI | NULL| auto_incr
ement |
+-+---+---+--+-+-+--
--+
If I issue a command like this -
explain select substring(id,1,4) from email_table
where substring(id,1,4) < 300 limit 500;
The result returned is -
++-+-+---+---+-+-+--
+---+--+
| id | select_type | table | type |
possible_keys | key | key_len | r
ef | rows | Extra|
++-+-+---+---+-+-+--
+---+--+
| 1 | SIMPLE | email_table | index | NULL
| PRIMARY | 4 | N
ULL | 33914 | Using where; Using index |
++-+-+---+---+-+-+--
+---+--+
This means it is using the index.
But, if issue a command like -
explain select email from email_table where
substring(id,1,4) < 300 limit 500;
++-+-+--+---+--+-+--
+---+-+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra |
++-+-+--+---+--+-+--
+---+-+
| 1 | SIMPLE | email_table | ALL | NULL
| NULL |NULL | NULL
| 33914 | Using where |
++-+-+--+---+--+-+--
+---+-+
the KEY column is NULL i.e. it is not using Index?
Why the query is not using index?
Thanks in advance.
Karam
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
Something like this should work. You want to do a left join on Jobs so you don't filter out those without submission matches. The resulting left join will have a value of NULL for any fields joined from Submissions that don't have a match in Jobs. Just include at least on field from Submissions and test for null on that field. SELECT *,s.status AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS s ON j.job_id=s.job_id WHERE s.status IS NULL On Monday, July 28, 2003, at 02:37 PM, Richard Bolen wrote: I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status != 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = s.job_id) where s.status_id != 1 group by j.job_id Any help is greatly appreciated. Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: rights to create table, select, then drop table..
Nobody has any ideas on this one? Jeff -Original Message- From: Jeff McKeon Sent: Friday, July 25, 2003 3:23 PM To: [EMAIL PROTECTED] Subject: rights to create table, select, then drop table.. I have a need to get data from the db that requires me to 1) do a select and create a new table with the results 2) run a query against that new table 3) drop the new table I have a script on my server that does this using the root account that has all on *.* for the db. It works fine. I now want to get these results on a web page. I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done. what priviledges do I need to give that user? currently I have the following but the user can't even log into the db from the command line.. mysql> show grants for user; +--- -+ | Grants for [EMAIL PROTECTED] | +--- -+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '6fe4c0ab2cf30ae3' | | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' | +--- -+ 2 rows in set (0.00 sec) when I do a "show grants for user", what should I see to allow what I want? Thanks, Jeff McKeon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bottleneck
How do I debug my live mysql query on a specific database to find the query time, memory usage, etc. ? Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicating records
There a bit of discussion like this in the user comments of the manual: http://www.mysql.com/doc/en/example-Maximum-row.html - Andy Venelin Arnaoudov wrote: > > I would copy all the records (_email_, max(version)) to a new table, > drop the old one and then rename the new one > > Regards, > Venelin > > Karam Chand wrote: > > >Well that is OK if I have only one email. > > > >What if if I have thousands of users duplicated... > > > >Do I need to write SQL query 1000 times > > > >Karam > >--- Jeff McKeon <[EMAIL PROTECTED]> wrote: > > > > > >>How bout > >> > >>Delete from tablename where email like > >>[EMAIL PROTECTED] && ID <> 1 > >> > >>Jeff > >> > >> > >>>-Original Message- > >>>From: Karam Chand [mailto:[EMAIL PROTECTED] > >>> > >>> > >>>Sent: Monday, July 28, 2003 10:33 AM > >>>To: [EMAIL PROTECTED] > >>>Subject: Deleting duplicating records > >>> > >>> > >>>Greetings > >>> > >>>I manage a website wherein i keep track of the > >>> > >>> > >>people > >> > >> > >>>email who have downloaded my software and the > >>> > >>> > >>version > >> > >> > >>>number. > >>> > >>>the structure is like - > >>> > >>>id int auto_increment primary key, > >>>email char, > >>>version > >>> > >>>now the same person can download different version > >>>therfore my table has data like this - > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>2,[EMAIL PROTECTED],2.0 > >>>3,[EMAIL PROTECTED],3.0 > >>> > >>>Now I want to delete all the records wherein all > >>> > >>> > >>rows > >> > >> > >>>with duplicate email addresses are deleted so that > >>> > >>> > >>i > >> > >> > >>>have data like > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>... > >>> > >>>What should be the query? Thanks in advance. > >>> > >>>Karam > >>> > >>>__ > >>>Do you Yahoo!? > >>>Yahoo! SiteBuilder - Free, easy-to-use web site > >>> > >>> > >>design > >> > >> > >>>software http://sitebuilder.yahoo.com > >>> > >>>-- > >>>MySQL General Mailing List > >>>For list archives: http://lists.mysql.com/mysql > >>>To unsubscribe: > >>>http://lists.mysql.com/mysql?> > >>> > >>> > >>[EMAIL PROTECTED] > >> > >> > >>> > >>> > >>-- > >>MySQL General Mailing List > >>For list archives: http://lists.mysql.com/mysql > >>To unsubscribe: > >> > >> > >> > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > >__ > >Do you Yahoo!? > >Yahoo! SiteBuilder - Free, easy-to-use web site design software > >http://sitebuilder.yahoo.com > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please HELP Romanian charset Collate in MySQL
Hello, Simply I made a table with romanian characters "aAîÎâÂsStT". I tried to sort it, in fact to order it in a query. I settled the default_charset to some charsets: latin1(default)(latin2 win1250(central european)) ÎteIbur Ibur Îte ItoIto The correct result must be: Ibur, Ito, Îte in ascending order. How can I do this? Please give me an example at how can I change the latin2.conf in a romanian one to fairly sort the chars for me. I must do change the behaviour and I don't know how. Thanks Anticipated, Iulian Teodosiu Economist/Analyst Programmer Primaria Falticeni Falticeni (town), jud. Suceava Romania, Europe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select with join query question
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status != 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = s.job_id) where s.status_id != 1 group by j.job_id Any help is greatly appreciated. Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design decision
Lian, Between your design solutions (1) and (3), you need to decide, from the logical business requirement, whether the nature of the relationship between user and group is one-to-many (a group may have many users, and each user may belong to exactly one group) or many-to-many (a group may have many users, and each user may belong to multiple groups). For the former, use Solution (3), for the latter, use Solution (1). Granted, Solution (3) is a subset of Solution (1), but requires more resources which might be a waste if you only need represent a one-to-many relationship. Your solution (2) has no restriction on the granularity of the relationship i.e., it can support both; it all depends on your implementation outside SQL, thus is not really a DB schematic means. In this case, the relationship is actually interpreted and maintained by your application program, not by DBMS. In making a choice between Solution (2) and the other two you need to consider the performance difference and code maintenance. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:22 AM To: mysqllist Subject: Design decision Hi everyone, Just wanted your expert opinion on the following: I'm implementing an authorization system with user/group permissions stored in a database. I have a Users table and a Group table, identical in structure: mysql> desc users; mysql> desc groups; +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | +---+-+ Now, my question is "How to store BEST the relations between users and groups?". Solution 1. I use a separate table with this structure: mysql> desc users2groups; +-+-+ | Field | Type| +-+-+ | idUser | int(11) | | idGroup | int(11) | +-+-+ and I add one record for each user <--> group mapping. So a SELECT will return potentially many rows for one group or one user. Solution 2. I construct and maintain a string separated by colons (let's say) for each group. So in the users2groups I'd have for example: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | Similary, since I need also user-to-group lookups I construct a string for the "group membership of a user" so I can have in the same table: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | | 123:456 | 4| Solution 3. Similary to Solution 2 but using the initial tables extended with one more field to accomodate the membership constructed string like: +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | | member_of | text| +---+-+ In Solution 1 I have multiple rows returned. In solution 2,3 I have only one. Solution 1 is scalable however Solution 2,3 can reach (potentially) the limits of the column specification (unlikely though). Assuming I'm interested in maximum speed at the authorization moment (and not at administrative moment), and that I'll have a big number of users and groups, and I access the database via Perl (so no problem to construct/deconstruct strings), what do you think is the best solution? Thank you for your time, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net "I'm not mad. I've been in bad mood for the last 30 years..." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
Well, the bad news is that I did not get any positive responses (that I
can recall) from the list. But I did a little digging. suppossedly in
the latest release you can do A->B->C->D->A. Something to do with the
server name encoded in the blog. I'm not much of a dba but in todays ID
sweatshops they are trying to squeeze me for all I got. My official
answer to the higher ups was "I know this can be done in Oracle" but at
thier licensing costs there's no way. If I get anything concrete going
I'll drop a note to the list.
/Joe
Gaspar Bakos wrote:
Hi, Joe,
I have exactly the same scenario. Did you get any valuable response you
could share with me? I haven't seen any on the list.
In fact, my case is slightly more complicated; I have "N" computers, all
having their local databases, and have an additional computer, which I
call the "central" one, having a "central" copy of the database. I'd like
to sync all the N+1 databases continuously, so they are identical.
A---C---B
|
D
That is, if I change anything on any PC's DB (e.g. "A"), it replicates
itself to the central DB ("C"), and then migrates to "B" and "D". This
means that the A<->C connection is such that "A" is a master and "C" is
a slave, and the C<->B is such that C is a master and B is a slave. On
the other hand, if I change something on another local DB than "A",
e.g. "B" or "C" itself, I'd like this to migrate to "A", i.e. this case
the A<->C connection is such that "A" is the slave.
Altogether, I'd need a continuous master-master replication between all
DBs and "C". Similar to a RAID-1 array, just in the world of databases,
and over TCP. Maybe there is a problem with my concept, and this solution
of pushing for reliability will eventually cause chaos.
The Mysql manual does not mention master-master replication:
"Starting in Version 3.23.15, MySQL supports one-way replication
internally. One server acts as the master, while the other acts as the
slave."
Cheers,
Gaspar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with foreign key constraint when updating
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using the
pre-compiled binaries.
I have a database with INNODB tables.
When I insert a row into one of the child tables, I get the following MySQL
error:
INSERT INTO product_access_level (product_id,access_level_id) VALUES
('10201','2') [nativecode=1216 ** Cannot add or update a child row: a
foreign key constraint fails]
I was not getting this error before with the previous version of
MySQL(3.23.57) that I had installed.
Below is the output of the latest foreign key error from 'SHOW INNODB
STATUS':
I am not sure what the problem is here. In the INNODB STATUS it says that my
product table doesn't exist??
Below the status are my table structures. Any input would be helpful.
Thanks!
LATEST FOREIGN KEY ERROR
030728 13:15:03 Transaction:
TRANSACTION 0 554436, ACTIVE 0 sec, process no 22745, OS thread id 864270
insert
ing, thread declared inside InnoDB 500
1 lock struct(s), heap size 320
MySQL thread id 203, query id 11471 localhost root update
INSERT INTO product_access_level (product_id,access_level_id) VALUES
('10201','2
')
Foreign key constraint fails for table tamiyausa/product_access_level:
,
CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
ON D
ELETE CASCADE
Trying to add to index PRIMARY tuple:
0: len 5; hex 3130323031; asc 10201;; 1: len 4; hex 0002; asc ;; 2:
len
6; hex 000875c4; asc u.;; 3: len 7; hex 68338b; asc
h3.;;
But the parent table mydb/product does not currently exist!
-
CREATE TABLE `product_access_level` (
`product_id` varchar(10) NOT NULL default '',
`access_level_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`product_id`,`access_level_id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_access_level_id` (`access_level_id`),
CONSTRAINT `0_281` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON
DELETE CASCADE,
CONSTRAINT `0_282` FOREIGN KEY (`access_level_id`) REFERENCES `access_level`
(access_level_id`) ON DELETE CASCADE) TYPE=InnoDB
CREATE TABLE `product` (
`id` varchar(10) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`category_id` int(10) unsigned default NULL,
`retail_value` float unsigned default NULL,
`dealer_price` float unsigned default NULL,
`minimum_purchase` int(10) unsigned default NULL,
`case_quantity` int(10) unsigned default NULL,
`status_id` char(2) default NULL,
`description` text,
PRIMARY KEY (`id`),
KEY `idx_category_id` (`category_id`),
KEY `idx_status_id` (`status_id`),
CONSTRAINT `0_274` FOREIGN KEY (`status_id`) REFERENCES `product_status`
(`id`) ON DELETE SET NULL,
CONSTRAINT `0_34` FOREIGN KEY (`category_id`) REFERENCES `category`
(`category_id`) ON DELETE SET NULL) TYPE=InnoDB
CREATE TABLE `access_level` (
`access_level_id` int(10) unsigned NOT NULL default '0',
`access_level_name` varchar(25) NOT NULL default '',
PRIMARY KEY (`access_level_id`)) TYPE=InnoDB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Referential integrity, FULLTEXT and table types
At 16:20 +0100 28-07-2003, Tom Gazzini wrote: I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order to support referential integrity. If I create the child tables as INNODB tables, will referential integrity still work with the MyISAM parent table? Many thanks, Tom If You can do a join between InnoDB & MyIsam You can put your text in one table and other data in another table, then link the tables with ids. I'm not sure You can mix InnoDB & MyIsam tables in a join. Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Search WHERE SUM
[snip] SELECT id FROM sales WHERE SUM( totalsales) <= '2' GROUP BY id; [/snip] SELECT id, SUM(totalsales) AS TotalSales FROM sales GROUP BY id HAVING TotalSales <= '2' HTH! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Search WHERE SUM
Easy: SELECT id FROM sales GROUP BY id HAVING SUM( totalsales) <= 2 ; That will perform the grouping, by ID, then filter and display only calculated rows with a sum less than 2. > -Original Message- > From: Yoed Anis [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 10:22 AM > To: [EMAIL PROTECTED] > Subject: Search WHERE SUM > > > Hello, > > What would be the proper way of generating a query like the following; > > SELECT id FROM sales WHERE SUM( totalsales) <= '2' GROUP BY id; > > Assuming the table looks like this; > Sales: > > Id| totalsales| monthyear > 1 | 100 | 2003-09 > 1 | 1 | 2003-08 > 1 | 2000 | 2003-07 > 2 | 3 | 2003-05 > 3 | 1 | 2003-06 > 3 | 1 | 2003-05 > > I want the query to return id 1 and 2 as the sum of their > totalsales is smaller then 2. > > > Thanks for your help, > Best, > Yoed > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search WHERE SUM
Hello, What would be the proper way of generating a query like the following; SELECT id FROM sales WHERE SUM( totalsales) <= '2' GROUP BY id; Assuming the table looks like this; Sales: Id| totalsales| monthyear 1 | 100 | 2003-09 1 | 1 | 2003-08 1 | 2000| 2003-07 2 | 3 | 2003-05 3 | 1 | 2003-06 3 | 1 | 2003-05 I want the query to return id 1 and 2 as the sum of their totalsales is smaller then 2. Thanks for your help, Best, Yoed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Atomicity of a SELECT/UPDATE
Andy Jackman said:
> Hi,
> I want to get any one of a number of rows that matches some criteria and
> update it so that it is marked as 'mine'. How can I do this safely?
>
> Given something like this table:
>
> create table tbl_new (
> t_value varchar(16) primary key not null,
> dt_used datetime,
> l_pid int(9)
> );
>
> I want to get any t_value WHERE dt_used is null and then set dt_used to
> prevent anyone else getting the same t_value.
>
> If I use locking (but I'm using C and I don't see any locking functions)
> (nor a START TRANSACTION?) I could:
>
> lock the tables,
> select t_value from tbl_new where dt_used is null;
> update tbl_new set dt_used = now() where t_value = 'whatever';
> unlock the tables;
>
> Without locking I could do something like this: (assume my-pid is unique
> between all users of this application at any one moment)
> while (1)
> {
> select t_value from tbl_new where dt_used is null;
> update tbl_new set dt_used = now(), l_pid = where t_value =
> '' and l_pid is null;
> select l_pid from tbl_new where t_value = '';
> if (l_pid == )
> break;
> // Else someone grabbed that record before us, go round and do it again
> }
>
> This sounds long winded to me. Anyone got a better suggestion?
> Thanks,
> Andy.
You almost have it. Look up 'Lock Tables' in the manual.
Unlike most database engines, mysql allows various types of tables. The
defaults is myisam which does not support transactions; however, InnoDB
does. Please see docs for details.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicating records
Great. So in that case I need to create a new table with similar structure with a additional UNIQUE index on email. Karam --- Venelin Arnaoudov <[EMAIL PROTECTED]> wrote: > I would copy all the records (_email_, max(version)) > to a new table, > drop the old one and then rename the new one > > Regards, > Venelin > > Karam Chand wrote: > > >Well that is OK if I have only one email. > > > >What if if I have thousands of users duplicated... > > > >Do I need to write SQL query 1000 times > > > >Karam > >--- Jeff McKeon <[EMAIL PROTECTED]> wrote: > > > > > >>How bout > >> > >>Delete from tablename where email like > >>[EMAIL PROTECTED] && ID <> 1 > >> > >>Jeff > >> > >> > >>>-Original Message- > >>>From: Karam Chand > [mailto:[EMAIL PROTECTED] > >>> > >>> > >>>Sent: Monday, July 28, 2003 10:33 AM > >>>To: [EMAIL PROTECTED] > >>>Subject: Deleting duplicating records > >>> > >>> > >>>Greetings > >>> > >>>I manage a website wherein i keep track of the > >>> > >>> > >>people > >> > >> > >>>email who have downloaded my software and the > >>> > >>> > >>version > >> > >> > >>>number. > >>> > >>>the structure is like - > >>> > >>>id int auto_increment primary key, > >>>email char, > >>>version > >>> > >>>now the same person can download different > version > >>>therfore my table has data like this - > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>2,[EMAIL PROTECTED],2.0 > >>>3,[EMAIL PROTECTED],3.0 > >>> > >>>Now I want to delete all the records wherein all > >>> > >>> > >>rows > >> > >> > >>>with duplicate email addresses are deleted so > that > >>> > >>> > >>i > >> > >> > >>>have data like > >>> > >>>1,[EMAIL PROTECTED],1.0 > >>>... > >>> > >>>What should be the query? Thanks in advance. > >>> > >>>Karam > >>> > >>>__ > >>>Do you Yahoo!? > >>>Yahoo! SiteBuilder - Free, easy-to-use web site > >>> > >>> > >>design > >> > >> > >>>software http://sitebuilder.yahoo.com > >>> > >>>-- > >>>MySQL General Mailing List > >>>For list archives: http://lists.mysql.com/mysql > >>>To unsubscribe: > >>>http://lists.mysql.com/mysql?> > >>> > >>> > >>[EMAIL PROTECTED] > >> > >> > >>> > >>> > >>-- > >>MySQL General Mailing List > >>For list archives: http://lists.mysql.com/mysql > >>To unsubscribe: > >> > >> > >> > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > >__ > >Do you Yahoo!? > >Yahoo! SiteBuilder - Free, easy-to-use web site > design software > >http://sitebuilder.yahoo.com > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Atomicity of a SELECT/UPDATE
Hi,
I want to get any one of a number of rows that matches some criteria and
update it so that it is marked as 'mine'. How can I do this safely?
Given something like this table:
create table tbl_new (
t_value varchar(16) primary key not null,
dt_used datetime,
l_pid int(9)
);
I want to get any t_value WHERE dt_used is null and then set dt_used to
prevent anyone else getting the same t_value.
If I use locking (but I'm using C and I don't see any locking functions)
(nor a START TRANSACTION?) I could:
lock the tables,
select t_value from tbl_new where dt_used is null;
update tbl_new set dt_used = now() where t_value = 'whatever';
unlock the tables;
Without locking I could do something like this: (assume my-pid is unique
between all users of this application at any one moment)
while (1)
{
select t_value from tbl_new where dt_used is null;
update tbl_new set dt_used = now(), l_pid = where t_value =
'' and l_pid is null;
select l_pid from tbl_new where t_value = '';
if (l_pid == )
break;
// Else someone grabbed that record before us, go round and do it again
}
This sounds long winded to me. Anyone got a better suggestion?
Thanks,
Andy.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aborted Connects
Hi, i've been trying to run mysqld (safe_mysqld and mysqld_safe) with -W and -- warnings and --log-warnings however, no matter how i try, mysql does NOT write information about Aborted Connects in .err file. Have tried that on Linux and Unix, on 4th (4.0) version and 3rd version, it just don't work. Help pls. wbr, ingus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dbase calculations
Kalle, The usual way to do this is to create the table with the 2 real fields and then use a query to 'create' the sum field at run time. For example assume you have this table: create table my_table ( field_1 int(9), field_2 int(9) ); then you can write this query: SELECT field_1, field_2, (field_1 + field_2) AS my_sum FROM my_table; This print 3 'fields', the third one is called my_sum and contains the sum of the other two (the AS keyword gives a field a name). Hope this helps, Andy. Kalle Saarinen wrote: > > Hello > > I'm rather new when it comes to databases and I was hoping that someone > could help me out! I was just wondering is it possible to make a field in > MySQL dbase wich is a total of two other fields. > > ie. > > field_XX is a sum of field_1 and field2 > > Thanks > > -Kalle > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14: Manual
Not to be too picky, but the version I downloaded didn't have a table of contents in
the .pdf file.
The older version did. It was very handy for quickly narrowing down a topic and
getting close to what you were interested in...
>>> Sergei Golubchik <[EMAIL PROTECTED]> 07/28/03 10:55AM >>>
Hi!
On Jul 28, Thomas Spahni wrote:
> Hi,
>
> in some strange way the version number did not propagate into the
> manual for version 4.0.14. It starts like this:
>
>
> This is the Reference Manual for the `MySQL Database System'. This
> version refers to the {No value for `mysqlversion'} version of `MySQL
> Server' but it is also applicable for ...
>
>
> Regards,
> Thomas Spahni
Oops!
Sorry for this :(
It's fixed - and won't happen again.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please Note
The information in this E-mail message is legally privileged
and confidential information intended only for the use of the
individual(s) named above. If you, the reader of this message,
are not the intended recipient, you are hereby notified that
you should not further disseminate, distribute, or forward this
E-mail message. If you have received this E-mail in error,
please notify the sender. Thank you
*
RE: Referential integrity, FULLTEXT and table types
> I need to have a table that supports FULLTEXT searches. This > implies that this table should be a MyISAM table. > > However, I also require that this table act as a parent for > child tables in order to support referential integrity. If I > create the child tables as INNODB tables, will referential > integrity still work with the MyISAM parent table? > Hello Tom, Currently you can't use InnoDB tables and Full-Text search, also you can't use MyISAM (which support Full-Text) with foreign keys (it's planned to implement foreign keys in MyISAM tables in MySQL 5.0). So my suggestion: use InnoDB & MyISAM together -> maybe it isn't "referential safe" but what can we do... Good luck, Marek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup problem - disaster waiting to happen
H M Kunzmann wrote:
>
> I use mysqldump to dump my databases to file.
> I then write these files to tape.
>
> I was doing a test restore to a test server this weekend and found that
> for my largest database, I cannot restore from this file.
>
> I use mysql < backup.script
>
> It runs for a long time and creates most of the tables, but eventually
> comes up with a syntax error and stops processing the file.
>
> I have two questions:
> How do I get around this ? The error message is:
>
> ERROR 1064 at line 78631: You have an error in your SQL syntax. Check the manual
> that c
> om:vml\"\r\nxmlns:o=\"u
>
> This data is xml data stored in one of the fields. If mysqldump created
> the syntax surely it should process back in correctly ? There's no way I
> can edit 2GB of incorrect entries in order to correct them.
>
> Secondly, how can I make the restore more fault tolerant ? If one call
> fails to continue with the next one ?
>
> Thank
> Ciao
> Herbert
> --
> Herbert Michael Kunzmann
> Binary Chaos Magician
Herbert,
It might be better if you do a per table export instead of whole
database export. If you still have files that are too large to easily
edit, use a utility like split to break them up. Below is the script we
use to backup all of our tables except for 100_PATS and 400_PATS as
those tables are dropped and reloaded everynight anyway.
Hope this helps!
walt
#!/bin/bash
cd /var/lib/mysql/NEA/
FILES=`ls *.frm`
for file in $FILES; do
LEN=${#file}
STRIP=$((LEN -4))
table=`expr substr $file 1 $STRIP`
if [ $table != "100_PATS" ] && [ $table != "400_PATS" ]; then
/usr/bin/mysqldump -qt -u nea NEA $table -r /opt/db_dump/$table
fi
done
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14: Manual
Hi!
On Jul 28, Thomas Spahni wrote:
> Hi,
>
> in some strange way the version number did not propagate into the
> manual for version 4.0.14. It starts like this:
>
>
> This is the Reference Manual for the `MySQL Database System'. This
> version refers to the {No value for `mysqlversion'} version of `MySQL
> Server' but it is also applicable for ...
>
>
> Regards,
> Thomas Spahni
Oops!
Sorry for this :(
It's fixed - and won't happen again.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TRUNCATE problem ...
hi, I have this table: CREATE TABLE `category_tree` ( `category_tree_id` BIGINT NOT NULL AUTO_INCREMENT, `parent_id`BIGINT, `mandant_id` SMALLINT NOT NULL, `partner_id` SMALLINT NOT NULL, `class_id` VARCHAR ( 32) NOT NULL, `position` SMALLINT NOT NULL, `path` VARCHAR (255), `description` VARCHAR (255) NOT NULL, # PRIMARY KEY (`category_tree_id`), INDEX (`parent_id`), INDEX (`mandant_id`), INDEX (`partner_id`), FOREIGN KEY (`parent_id`) REFERENCES `category_tree` (`category_tree_id`), FOREIGN KEY (`mandant_id`) REFERENCES `mandant` (`mandant_id`), FOREIGN KEY (`partner_id`) REFERENCES `partner` (`partner_id`) ) TYPE=InnoDB; After call: TRUNCATE TABLE `category_tree`; I get this error message: TRUNCATE TABLE `category_tree` Cannot delete or update a parent row: a foreign key constraint fails While there is an foreign key on each self. Is this a Bug in MySQL? Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remote client freeze mysqld...
Hi all, I have install Mysql on a redhat 8 server. Works perfectly locally but when trying to access remotely with the Mysql Control Center(on Win2k), client freezes and so Mysqld. Must do a killall mysqld to release the client. Any ideas? Francois -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting duplicating records
I would copy all the records (_email_, max(version)) to a new table, drop the old one and then rename the new one Regards, Venelin Karam Chand wrote: Well that is OK if I have only one email. What if if I have thousands of users duplicated... Do I need to write SQL query 1000 times Karam --- Jeff McKeon <[EMAIL PROTECTED]> wrote: How bout Delete from tablename where email like [EMAIL PROTECTED] && ID <> 1 Jeff -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 10:33 AM To: [EMAIL PROTECTED] Subject: Deleting duplicating records Greetings I manage a website wherein i keep track of the people email who have downloaded my software and the version number. the structure is like - id int auto_increment primary key, email char, version now the same person can download different version therfore my table has data like this - 1,[EMAIL PROTECTED],1.0 2,[EMAIL PROTECTED],2.0 3,[EMAIL PROTECTED],3.0 Now I want to delete all the records wherein all rows with duplicate email addresses are deleted so that i have data like 1,[EMAIL PROTECTED],1.0 ... What should be the query? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?> [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master/Master Asynchronous replication
Hi, Joe,
I have exactly the same scenario. Did you get any valuable response you
could share with me? I haven't seen any on the list.
In fact, my case is slightly more complicated; I have "N" computers, all
having their local databases, and have an additional computer, which I
call the "central" one, having a "central" copy of the database. I'd like
to sync all the N+1 databases continuously, so they are identical.
A---C---B
|
D
That is, if I change anything on any PC's DB (e.g. "A"), it replicates
itself to the central DB ("C"), and then migrates to "B" and "D". This
means that the A<->C connection is such that "A" is a master and "C" is
a slave, and the C<->B is such that C is a master and B is a slave. On
the other hand, if I change something on another local DB than "A",
e.g. "B" or "C" itself, I'd like this to migrate to "A", i.e. this case
the A<->C connection is such that "A" is the slave.
Altogether, I'd need a continuous master-master replication between all
DBs and "C". Similar to a RAID-1 array, just in the world of databases,
and over TCP. Maybe there is a problem with my concept, and this solution
of pushing for reliability will eventually cause chaos.
The Mysql manual does not mention master-master replication:
"Starting in Version 3.23.15, MySQL supports one-way replication
internally. One server acts as the master, while the other acts as the
slave."
Cheers,
Gaspar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql queries with numbers
Do it in MySQL if you can. "Use the force" ;-) Besides MIN() and MAX() there are also statistical functions implemented as: AVG(), STDDEV() etc. See the manual for all functions. Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net > -Original Message- > From: Taylor Lewick [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 6:00 PM > To: [EMAIL PROTECTED] > Subject: mysql queries with numbers > > > Question for everyone, I have a database with about 30-60 days > worth of information. Much of it numerical. > Can and should I use SQL to run quereis that will return me the > high/low and average for the time frame, as well as standard deviation, > or should I just get all of the info into an array via perl and > let it do the crunching? > > Thanks, > Taylor > > > Please Note > The information in this E-mail message is legally privileged > and confidential information intended only for the use of the > individual(s) named above. If you, the reader of this message, > are not the intended recipient, you are hereby notified that > you should not further disseminate, distribute, or forward this > E-mail message. If you have received this E-mail in error, > please notify the sender. Thank you > * > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicating records
Well that is OK if I have only one email. What if if I have thousands of users duplicated... Do I need to write SQL query 1000 times Karam --- Jeff McKeon <[EMAIL PROTECTED]> wrote: > How bout > > Delete from tablename where email like > [EMAIL PROTECTED] && ID <> 1 > > Jeff > > -Original Message- > > From: Karam Chand [mailto:[EMAIL PROTECTED] > > > Sent: Monday, July 28, 2003 10:33 AM > > To: [EMAIL PROTECTED] > > Subject: Deleting duplicating records > > > > > > Greetings > > > > I manage a website wherein i keep track of the > people > > email who have downloaded my software and the > version > > number. > > > > the structure is like - > > > > id int auto_increment primary key, > > email char, > > version > > > > now the same person can download different version > > therfore my table has data like this - > > > > 1,[EMAIL PROTECTED],1.0 > > 2,[EMAIL PROTECTED],2.0 > > 3,[EMAIL PROTECTED],3.0 > > > > Now I want to delete all the records wherein all > rows > > with duplicate email addresses are deleted so that > i > > have data like > > > > 1,[EMAIL PROTECTED],1.0 > > ... > > > > What should be the query? Thanks in advance. > > > > Karam > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site > design > > software http://sitebuilder.yahoo.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> > [EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please URGENTLY Romanian charset Collate in MySQL
Hello, Simply I made a table with romanian characters "aAîÎâÂsStT". I tried to sort it, in fact to order it in a query. I settled the default_charset to some charsets: latin1(default)(latin2(iso-8859-2 I think) the same like win1250(central european)) SteSbur Sbur Ste StoSto The correct result must be: Sbur, Sto, Ste in ascending order. How can I do this? Please give me an example at how can I change the latin2.conf in a romanian one to fairly sort the chars for me. I must do change the behaviour and I don't know how. Thanks Anticipated, Iulian Teodosiu Economis/Analyst Programmer Primaria Falticeni Falticeni (town), jud. Suceava Romania, Europe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Referential integrity, FULLTEXT and table types
I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order to support referential integrity. If I create the child tables as INNODB tables, will referential integrity still work with the MyISAM parent table? Many thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleting duplicating records
How bout Delete from tablename where email like [EMAIL PROTECTED] && ID <> 1 Jeff > -Original Message- > From: Karam Chand [mailto:[EMAIL PROTECTED] > Sent: Monday, July 28, 2003 10:33 AM > To: [EMAIL PROTECTED] > Subject: Deleting duplicating records > > > Greetings > > I manage a website wherein i keep track of the people > email who have downloaded my software and the version > number. > > the structure is like - > > id int auto_increment primary key, > email char, > version > > now the same person can download different version > therfore my table has data like this - > > 1,[EMAIL PROTECTED],1.0 > 2,[EMAIL PROTECTED],2.0 > 3,[EMAIL PROTECTED],3.0 > > Now I want to delete all the records wherein all rows > with duplicate email addresses are deleted so that i > have data like > > 1,[EMAIL PROTECTED],1.0 > ... > > What should be the query? Thanks in advance. > > Karam > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design > software http://sitebuilder.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql queries with numbers
Question for everyone, I have a database with about 30-60 days worth of information. Much of it numerical. Can and should I use SQL to run quereis that will return me the high/low and average for the time frame, as well as standard deviation, or should I just get all of the info into an array via perl and let it do the crunching? Thanks, Taylor Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you *
Backup problem - disaster waiting to happen
I use mysqldump to dump my databases to file. I then write these files to tape. I was doing a test restore to a test server this weekend and found that for my largest database, I cannot restore from this file. I use mysql < backup.script It runs for a long time and creates most of the tables, but eventually comes up with a syntax error and stops processing the file. I have two questions: How do I get around this ? The error message is: ERROR 1064 at line 78631: You have an error in your SQL syntax. Check the manual that c om:vml\"\r\nxmlns:o=\"u This data is xml data stored in one of the fields. If mysqldump created the syntax surely it should process back in correctly ? There's no way I can edit 2GB of incorrect entries in order to correct them. Secondly, how can I make the restore more fault tolerant ? If one call fails to continue with the next one ? Thank Ciao Herbert -- Herbert Michael Kunzmann Binary Chaos Magician signature.asc Description: This is a digitally signed message part
Deleting duplicating records
Greetings I manage a website wherein i keep track of the people email who have downloaded my software and the version number. the structure is like - id int auto_increment primary key, email char, version now the same person can download different version therfore my table has data like this - 1,[EMAIL PROTECTED],1.0 2,[EMAIL PROTECTED],2.0 3,[EMAIL PROTECTED],3.0 Now I want to delete all the records wherein all rows with duplicate email addresses are deleted so that i have data like 1,[EMAIL PROTECTED],1.0 ... What should be the query? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design decision
Hi everyone, Just wanted your expert opinion on the following: I'm implementing an authorization system with user/group permissions stored in a database. I have a Users table and a Group table, identical in structure: mysql> desc users; mysql> desc groups; +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | +---+-+ Now, my question is "How to store BEST the relations between users and groups?". Solution 1. I use a separate table with this structure: mysql> desc users2groups; +-+-+ | Field | Type| +-+-+ | idUser | int(11) | | idGroup | int(11) | +-+-+ and I add one record for each user <--> group mapping. So a SELECT will return potentially many rows for one group or one user. Solution 2. I construct and maintain a string separated by colons (let's say) for each group. So in the users2groups I'd have for example: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | Similary, since I need also user-to-group lookups I construct a string for the "group membership of a user" so I can have in the same table: | idGroup | idUser | | 123 | 2:3:4:8:9:10 | | 123:456 | 4| Solution 3. Similary to Solution 2 but using the initial tables extended with one more field to accomodate the membership constructed string like: +---+-+ | Field | Type| +---+-+ | id| int(11) | | name | varchar(30) | | member_of | text| +---+-+ In Solution 1 I have multiple rows returned. In solution 2,3 I have only one. Solution 1 is scalable however Solution 2,3 can reach (potentially) the limits of the column specification (unlikely though). Assuming I'm interested in maximum speed at the authorization moment (and not at administrative moment), and that I'll have a big number of users and groups, and I access the database via Perl (so no problem to construct/deconstruct strings), what do you think is the best solution? Thank you for your time, Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net "I'm not mad. I've been in bad mood for the last 30 years..." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.14: Manual
Hi,
in some strange way the version number did not propagate into the
manual for version 4.0.14. It starts like this:
This is the Reference Manual for the `MySQL Database System'. This
version refers to the {No value for `mysqlversion'} version of `MySQL
Server' but it is also applicable for ...
Regards,
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE tables still gamma?
The manual page for MERGE tables states the code is in gamma since 3.23.25. But it also says you can only SELECT, DELETE, and UPDATE, which isn't true since version 4.0.something. Can anyone verify if the MERGE tables is still in gamma? We occassionally have diskspace issues on our system, and I'd love to split our invoice table into years and use pack on the old years and replace the original table with a MERGE. -- Mike Miller Business Analyst & Applications Developer BMG Canada Inc. Tel: 416-586-1646 Fax: 416-586-0454 EMail: [EMAIL PROTECTED]
RE: schema and Catalog under MySQL 3.23.55
Schemas and user defined partitions are not utilized in MySQL as they are in Oracle. If you are attempting to design your architecture according to schemas you could visualize each database as an user defined schema. CREATE DATABASE company_00; -Original Message- From: Morten Gulbrandsen [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 8:38 AM To: [EMAIL PROTECTED] Subject: schema and Catalog under MySQL 3.23.55 Hi programmers, I try to investigate some of the basics behind schemas and cataloges, Which is part of SQL2 Language this is the error message I get: C:\mysql\bin>mysql -u administrator -h localhost -p Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 246 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql> use company; Database changed mysql> create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql> create schema company_00 authorization administrator; === it seems to me that the sql statements schema and catalog is not working in my version of mysql. What can be done in order to remedy this ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
schema and Catalog under MySQL 3.23.55
Hi programmers, I try to investigate some of the basics behind schemas and cataloges, Which is part of SQL2 Language this is the error message I get: C:\mysql\bin>mysql -u administrator -h localhost -p Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 246 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql> use company; Database changed mysql> create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql> create schema company_00 authorization administrator; === it seems to me that the sql statements schema and catalog is not working in my version of mysql. What can be done in order to remedy this ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to retrieve integer in C prog?
Use atoi();
Andy Jackman wrote:
Hi,
I'm new to mySql. I've got a table with an integer field defined like
this:
l_start_wait int(9) not null
I inserted a value into the table using mysql command line client with:
Insert into tbl_wait_list values(12345678);
When I use mysql_fetch_row() to retrieve the column values I am
surprised to see
that mysql_fetch_lengths() returns a length of 8 rather than 4 and
row[0] seems to
point to bytes containing ascii characters "12345678" rather than being
a pointer
to an int.
I've searched the manual and google but apart from occasional references
to blob
data most examples seem to assume that all data is ascii.
What I'd like to be able to do is something like this: int i =
(cast)row[0];
Please tell me what I'm doing wrong - or is this a limitation?
Thanks,
Andy.
Here's my code fragment if it helps:
int checkWaitList(MYSQL *aDb)
{
// aDb connection is already open
MYSQL_RES *rsResult;
MYSQL_ROW row;
char pszSql[256];
int lStartTime;
unsigned long *lengths;
strcpy(pszSql, "SELECT l_start_wait FROM tbl_wait_list");
mysql_query(aDb,pszSql);
rsResult = mysql_use_result(aDb);
if (row = mysql_fetch_row(rsResult)) {
lengths = mysql_fetch_lengths(rsResult);
sprintf(pszSql, ">>%d, %12.12s<<\r\n", lengths[0], row[0]);
// Prints >>8, 12345678<<
return -1;
}
else {
return 0;
}
}
mysql_query(aDb,pszSql);
rsResult = mysql_use_result(aDb);
if (row = mysql_fetch_row(rsResult)) {
lengths = mysql_fetch_lengths(rsResult);
// Found a row
tsOut("Found: " );
tsOut("\r\n");
//lStartTime = *(int *)row[1];
sprintf(pszSql, ">>%ud, %12.12s<<\r\n", lengths[0], row[0]);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL shutdown error...
Mysqld is the server, and can be started automatically.
Mysql is a client program. It makes no sense to start it automatically.
-{ Rene Brehmer }- wrote:
Hi gang
Not sure if this is more for the RedHat list than here, but let's try...
I've put MySQL on a RedHat by using the RPM ... haven't yet figured
out if it actually works... but I had to make the thing start
automatically on my own ... so I put mysqld and mysql on the startup
list for runlevel 3, which is what I run at...
But when it shifts to runlevel 0 or 6, it saysStopping MySQL ..
[FAILED]everytime ... now is that essential, or did I do something
wrong???
I basically added mysql and mysqld as startup on level 1-5, and on the
kill list for level 0 and 6...
Rene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up lookups on a table
That's a fairly simple query you are running and should run pretty quicker, obviously."ps auwx" isn't going to tell you enough about what is going on to troubleshoot effectively. The cause of a slowdown on a system is always either: CPU, Memory, I/O, or Network. "top" might be a better choice. Before anything else you should check you MySQL settings and adjust them. If you see disk I/O jump on your slow query, then MySQL may not be getting enough RAM and you should either adjust you settings file or add RAM. Ideally MySQL will have enough RAM to cache all the tables. Are more rows returned on a slow query than a fast one? You don't have a LIMIT set in your query, are you really always going to use every row that is returned on every query? Are you using all the fields on every query? You have a "text" field which could contain lots of data. Remember, all of the data that results from the query must be read from the database and transferred to the calling process. If a query results in 10MB of data, MySQL may have the matching records in 1-2 seconds, but it's going to take a from seconds to transfer that much data over a network. If this table is going to be huge (or already is) you may want to break off you text field into a separate table and then modify the current table to use char instead of varchar. This will result in fixed length records for your main query database and will speed things up. If you are worried about table locking, which I don't think is your problem, then you can switch to InnoDB table types. On Sunday, July 27, 2003, at 12:00 AM, Bennett Haselton wrote: I have a MySQL query running inside a CGI script on my site that, at random intervals, seems to take 10-20 seconds to complete instead of less than 1 second. I spent so much time trying to track this down that I wrote a script which runs once a minute on the site, which (a) captures the output of "ps auwx" (listing all processes) so I can see if that has anything to do with the slowdown; (b) times how long it takes to run the query, and; (c) times how long it takes to run a similar query on a much smaller table. (Part (c) is so that I can separate out whether it's the size of the table in part (b) that's making the difference, or the time taken to do something that's common to both queries, like getting a database handle.) 90% of the time, the large-table query takes less than 1 second, but 10% of the time, it takes 15-20 seconds. (The small-table query always takes less than 1 second.) I looked at the output of "ps auwx" to see if there seemed to be a relationship between the %CPU time used by other processes, or the number of other running processes, and the speed of the query, but there didn't seem to be. So, my questions are: (a) What is the usual cause of this type of problem? (b) The query I'm running is: SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id; on a table whose description is: ++--+--+-+- ++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+- ++ | ID | int(10) unsigned | | PRI | NULL| auto_increment | | news_feed_owner_userid | int(10) unsigned | YES | MUL | NULL| | | URL| varchar(255) | YES | | NULL| | | title | varchar(255) | YES | | NULL| | | description| text | YES | | NULL| | | date_and_time | datetime | YES | | NULL| | | news_site_name | varchar(255) | YES | | NULL| | ++--+--+-+- ++ (if it wraps, widen message window to see it all). Since I've already defined an index on news_feed_owner_userid, is there anything else I can do to make this kind of query run faster on this table? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dbase calculations
Hello I'm rather new when it comes to databases and I was hoping that someone could help me out! I was just wondering is it possible to make a field in MySQL dbase wich is a total of two other fields. ie. field_XX is a sum of field_1 and field2 Thanks -Kalle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL ODBC Help
Something you may want to try. Look in this file C:\WINNT\system32\drivers\etc\hosts and make sure this entry is in there 127.0.0.1 localhost Also make sure a ";" or "#" is not in front of the line. If the line does not exist at all then create it. Hope that helps, Bill -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 6:01 AM To: jackiesu Cc: [EMAIL PROTECTED] Subject: Re: MySQL ODBC Help Hi Jackie, I am sorry haven't searched for any documents myself, but I can imagine that Google will list them up when you search for " OpenOffice MySQL Windows Setup". Best regards Nils Valentin Tokyo/Japan 2003年 7月 28日 月曜日 17:07、jackiesu さんは書きました: > Thank you very much for your help, but I don't run on a Linux system. Is > there any tutorials that you know of for Windows NT/XP? Thanks again =) > - Original Message - > From: "Nils Valentin" <[EMAIL PROTECTED]> > To: "jackiesu" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Saturday, July 26, 2003 6:17 PM > Subject: Re: MySQL ODBC Help > > > Hi Jackie, > > Having the odbc driver installed , doesnt mean that it is set up yet. > > You probably have just finished 25% of the complete setup. > > There are some good documents describing the setup: > > http://www.linuxworld.com/story/32629.htm > http://www.linuxworld.com/story/32634.htm > http://www.unixodbc.org/doc/OOoMySQL.pdf > > 1)Read them ALL. > 2) Shake the gained knowledge and apply only the useful stuff ;-) > 3) Enjoy your ODBC application. > > The whole procedure might take 30 minutes up to easily 1 day depending on > your > skill level and driver versions (incompatibilities etc. ) ;-). > > Good Luck !! > > Best regards > > Nils Valentin > Tokyo/Japan > > 2003年 7月 26日 土曜日 23:22、jackiesu さんは書きました: > > I've installed Apache 2, PHP 4 and I've tried to install MySQL. But when > > I open the MySQL ODBC Driver and click Test Data Source it says > > [MySQL][ODBC > > > 3.51 Driver] Can't connect to MySQL server on 'localhost' (10061), can > > anyone help? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf not existing - Thx :)
Thx for the answer, found it myself too: Am Montag, 28. Juli 2003 14:22 schrieben Sie: > Build the file and put it in your C:\>.. You are not given a my.cnf > file if you build from source, if you had used the binary file, it came > with about 4 of the files and you just pick one and modify it and then > save it in your C drive Ok, i work on Linux, so there is no C:\. But i found a my-sample.cnf in the mysql-directory/share/. Modified it and copied to /etc, works fine. thx, Bernd -- One OS to rule them all, one OS to find them. One OS to bring them all, and in the darkness bind them In the land of Redmond, where the shadows lie. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
I saw a macro for access that will produce a script that will recreate your tables and the data in them. But it only worked in older versions of access. Sorry I can't be more help. bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
From MySQL to Outlook
Hi, I was wondering whether someone knew of the options available for getting customer details from a MySQL db into Outlook? Also whether it was possible to sync Outlook, so it reads it's Address book straight from the MySQL db? Any suggestions would be greatly appreciated, Many thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Depreciated the update log? No!!! Please don't do that.
Hi! On Jul 28, Daniel Kasak wrote: > http://www.mysql.com/doc/en/News-5.0.x.html > > The change log says it's "no longer supported". Does that mean that it > will be there, but there are no guarantees that it's accurate, or that > it's being removed completely? > > The update log is very handy when doing development work - especially > when I screw something up completely, and I have to restore from last > night's backup and edit the update log to remove the mistake I made, > then run the ammeded update log through the mysql client to apply > today's updates. > > It's also just cool to watch when nothing else is happening. > > Anyone know why it's being depreciated? Because you can do all the same (and more) with binary log. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf not existing?
Hello List, this is my first posting here so plz do not flame me too much if my question marks me as a total noob. My problem is, i play around a bit with mysql (4.0.13) and want to test the Replication. The Documentation at http://www.mysql.com says i have to do the following: Stop databases - done. Build tarball of data and bring it on slave - done. Modify my.cnf - ??? Here is my problem. There is no my.cnf existing on my machine. Maybe its called different with the newer version? Or can it be considered empty and i have to build a new one? But if i have to, in which directory would i do that? Maybe someone can give me a kick in the right direction. Bernd -- One OS to rule them all, one OS to find them. One OS to bring them all, and in the darkness bind them In the land of Redmond, where the shadows lie. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ODBC Help
Hi Jackie, I am sorry haven't searched for any documents myself, but I can imagine that Google will list them up when you search for " OpenOffice MySQL Windows Setup". Best regards Nils Valentin Tokyo/Japan 2003年 7月 28日 月曜日 17:07、jackiesu さんは書きました: > Thank you very much for your help, but I don't run on a Linux system. Is > there any tutorials that you know of for Windows NT/XP? Thanks again =) > - Original Message - > From: "Nils Valentin" <[EMAIL PROTECTED]> > To: "jackiesu" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Saturday, July 26, 2003 6:17 PM > Subject: Re: MySQL ODBC Help > > > Hi Jackie, > > Having the odbc driver installed , doesnt mean that it is set up yet. > > You probably have just finished 25% of the complete setup. > > There are some good documents describing the setup: > > http://www.linuxworld.com/story/32629.htm > http://www.linuxworld.com/story/32634.htm > http://www.unixodbc.org/doc/OOoMySQL.pdf > > 1)Read them ALL. > 2) Shake the gained knowledge and apply only the useful stuff ;-) > 3) Enjoy your ODBC application. > > The whole procedure might take 30 minutes up to easily 1 day depending on > your > skill level and driver versions (incompatibilities etc. ) ;-). > > Good Luck !! > > Best regards > > Nils Valentin > Tokyo/Japan > > 2003年 7月 26日 土曜日 23:22、jackiesu さんは書きました: > > I've installed Apache 2, PHP 4 and I've tried to install MySQL. But when > > I open the MySQL ODBC Driver and click Test Data Source it says > > [MySQL][ODBC > > > 3.51 Driver] Can't connect to MySQL server on 'localhost' (10061), can > > anyone help? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
Robert Morgan wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. Depends how big the whole thing is as well as how good the current design is. Since it is running in a hospital I hope the Access approach was propperly done as if Access were a real RDBMS. ;) (My own project is a mess that grew (in the sense of cancer) out of a single Excel table and now sucks to maintain any further.) If possible I'd go with spambox's approach and write those table designs myself. The data-migration isn't that hard afterwards. The big issue is the table and index design. Generally you could use one of those Windows thingies that you can download at mysql.com but I don't know how they cope with foreign-keys and that you'll HAVE TO HAVE a TIMESTAMP collumn in every mysql-table with content that will be edited later, even when there is none in the access-table. I'm pretty sure a click-and-fly migration from Access to MySQL where you just start some magical tool and everything is done, is wishful dreaming. SpamBox wrote: I would not go about it this way though. I would create the tables in MySQL - paying close attention to field types, RIGHT And perhaps there are some NOT NULLs or FOREIGN KEYs and INNDEX/UNIQUEs to be seeded in the new design. Automatic migration would move shortcomings of the old design to mysql without bringing you to notice them. and then link the MySQL tables to Access via the MyODBC driver, and then use queries to dump the data from the Access tables into the MySQL tables. That gives a head ache if AUTO_INCREMENTS were used as foreign-keys. Which of course they shouldn't have done in the first place. Which of course *I* shouldn't have done, too ! ... live and learn. > Alternatively, you could export the data to a CSV file and use 'load data infile' on the MySQL server to import the data. Though ... there might lurk a number of fields in those CSVs that break consistency in the new design. At least my Access-DB has a lot of collumns that should have been set to NOT NULL but I didn't bother to switch it on in Access table-design. And then there wer dangling foreign-keys that lost their parent or became NULL sometime while Access looked away or crashed or where I skrewed up. Who uses transactions in Access anyway ? =8-O The new handwritten CREATE TABLES have a lot more consistency checks in them and so CSV import runs on a landmine now and then. Take your new design scrips and check the consistency while the data sits still in Access. But linking the tables in Access is easier And a lot slower. OK, that doesn't bother in a one time data migration move but while I was at it skribbling away on those table scripts I had to keep up with the live data so regular reimports where neccessary. One can create dynamically block-INSERT commands. Those are way faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication stopps
Hi, I set up a two-way replication server, it works very well, but after some hours (24-48) the replication stopps, the log-error only says: 030725 14:03:53 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 030726 9:36:40 Slave I/O thread exiting, read up to log 'log-bin.002', position 283 Does anybody know how to prevent this accidentally stopping? Moritz
Re: PhpMyadmin
Hi, > I want to install phpMyAdmin on my windows 2000 box. Whare can I find the > PhpMyAdmin software to download. http://www.phpmyadmin.net/ Google would have found that for you in seconds, it's the first result for a search for "phpmyadmin". With all due respect, you really should try a little harder to find the answer yourself first before posting to a list about it. HTH David P -- David Precious http://www.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: importing Access databases
there is an app called sqlyog it imports access directly into mysql -Original Message- From: SpamBox [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:13 AM To: Robert Morgan; mysqllist Subject: Re: importing Access databases Robert Morgan wrote: >Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. >Any help appreciated. > >Running Redhat 9 and the MYsql version that comes bundled with it. > > > There is 'mdbtools' on sourceforge, which allows you to view data in an MDB file and export it. I would not go about it this way though. I would create the tables in MySQL - paying close attention to field types, and then link the MySQL tables to Access via the MyODBC driver, and then use queries to dump the data from the Access tables into the MySQL tables. Alternatively, you could export the data to a CSV file and use 'load data infile' on the MySQL server to import the data. But linking the tables in Access is easier... Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PhpMyadmin
Hi, I want to install phpMyAdmin on my windows 2000 box. Whare can I find the PhpMyAdmin software to download. Thanks
Re: Backup problem - disaster waiting to happen
Hi Herbert, the -f/--force option lets you continue with the next command Thats my $0.0.2. Best regards Nils Valentin Tokyo/Japan 2003年 7月 28日 月曜日 17:57、H M Kunzmann さんは書きました: > I use mysqldump to dump my databases to file. > I then write these files to tape. > > I was doing a test restore to a test server this weekend and found that > for my largest database, I cannot restore from this file. > > I use mysql < backup.script. > > It runs for a long time and creates most of the tables, but eventually > comes up with a syntax error and stops processing the file. > > I have two questions: > How do I get around this ? The error message is: > > .ERROR 1064 at line 78631: You have an error in your SQL syn > s:v=\"urn:schemas-microsoft-com:vml\"\r\nxmlns:o=\"u > > This data is xml data stored in one of the fields. If mysqldump created > the syntax surely it should process back in correctly ? There's no way I > can edit 2GB of incorrect entries in order to correct them. > > Secondly, how can I make the restore more fault tolerant ? If one call > fails to continue with the next one ? > > Thank > Ciao > Herbert -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
