In need of a dummy select statement ...
Hi, I am a newbie to mysql db. I need a select statement which will just execute and doesn't return any results. Also, it should not depend on any table which is specific to a database. For example, Oracle has a table named 'tab' which will be present in all databases so my dummy select statement looks like 'select tname from tab where 0=1'. Could this functionality be achieved in mysql ? Is there any table in mysql which is similar to Oracle's 'tab' ? Thanks, Srinivas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interested in MySQL Training in Malaysia or Singapore?
Dear MySQL lovers, I have approached MySQL for courses / certification in the Malaysia and Singapore region, and just want to know if there are people out there who would be interested to attend such training. They would require a minimum of 8-10 attendees. If you're interested...reply. Thanks! Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp
Mike Blezien <[EMAIL PROTECTED]> wrote: > > what is the best way to convert a TIMESTAMP value to a value > similar to a DATETIME value ?? > If you mean TIMESTAMP column type, you can just change column type with ALTER TABLE statement. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In need of a dummy select statement ...
Hi, > I am a newbie to mysql db. I need a select statement which will just > execute and doesn't > return any results. Also, it should not depend on any table which is > specific to a database. > For example, Oracle has a table named 'tab' which will be present in all > databases so my > dummy select statement looks like 'select tname from tab where 0=1'. > Could this > functionality be achieved in mysql ? Is there any table in mysql which > is similar to Oracle's > 'tab' ? Not by default, but hey: you can always create one. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specs for a dedicated MySQL server
Hi >I cannot see the need for transactions etc. Why not? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.1 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Fri, 5 Dec 2003, Boehn, Gunnar von wrote: > > Yes, we plan to offer Linux/PPC binaries in the near future - we're > > currently working on purchasing a system for that. > > If you have some harddisk space left, then you can install Linux on one > of your MACs. I know, but this is not really an option for such a virtual company as ours - I have never physically worked on any of my build hosts as they are located in Sweden, while I work from Germany. So I am a bit hesitant to have to rely on remote reboots. In addition to that it would complicate our build process, as we currently build all binaries on all platforms in parallel. > > I have not decided which Linux Distribution to use, > > though - maybe Yellowdog? > > My recommendation would be, as always, Debian. :-) > But thats a metter of taste. > > You can install nearly every distro on PPC. > Good examples are: YellowDog, Mandrake, Red Hat, Suse, Gentoo, Debian OK, thanks for the info! > > Unfortunately my favourite Linux distribution (SuSE) > > does not support the PPC platform anymore :( > > SUSE has very good PPC support. > IBM even recommands SUSE and RedHat together with their big POWER-machines. > For PPC you need to use the SUSE Server version. Which unfortunately is only available when you pay big bucks for it. I have to check, if there are other ways :) > I have tried Yellow Dog on one of my MACs. > = the install was a piece of cake. OK, good to know. > On my Pegasos ( www.pegasosppc.com ) I'm very happy with Debian. > The Pegasos is a really nice, geeky machine. > With a Pegasos you could release binaries for MorphOS as well. :-) Thanks, will look into that as well! Bye, LenZ - -- Lenz Grimmer <[EMAIL PROTECTED]> Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/1GhESVDhKrJykfIRAiFLAJ9ayLdikzyt04SZwMfSTqxVFvYgSwCfZrwI ykpj1C6otDkAOOfmoqR29Kc= =N91f -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
OK, I will give you more details.
MySQL ver. : 4.0.16
CPU : 2xCelleron 1000 & 1GB RAM
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Last results that I sent are not correct because I forgot to include one
more join, artists.artistid = cds.artistid, bad oversight I know
These are the new results :
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna'IN BOOLEAN MODE) AND
MATCH (cds.title)AGAINST ('"music mix 2001"'IN BOOLEAN MODE)
Time for second SQL : < 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( artists.name ) AGAINST ( 'madonna' ) AND
MATCH ( cds.title ) AGAINST ( 'music' ) AND
MATCH ( cds.title ) AGAINST ( 'mix' ) AND
MATCH ( cds.title ) AGAINST ( '2001' )
One more thing that I noticed in last SQL, when I change, in FROM
clause, positions of tables like this : FROM artists, tracks, cds,
instead FROM artists, cds, tracks I get time of 1.9 sec. instead < 1
sec. ?
Regards
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 00:02
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Hi!
On Nov 27, Uros Kotnik wrote:
> Executing this SQL, takes ~5 sec.
>
> select artists.name, cds.title, tracks.title from artists, tracks, cds
> where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
> and MATCH (artists.name) AGAINST ('madonna')
> and MATCH (cds.title) AGAINST ('music')
> and MATCH (cds.title) AGAINST ('mix')
> and MATCH (cds.title) AGAINST ('2001')
> limit 1001
>
> and this, ~40 sec.
>
> select artists.name, cds.title, tracks.title from artists, tracks, cds
> where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
> and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
> and MATCH (cds.title) AGAINST ('"music mix 2001"' IN BOOLEAN MODE)
> limit 1001
>
> Same result but the speed difference is quite a different, why is that
?
What does EXPLAIN show for both queries ?
And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite.
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: Speed difference between boolean full-text searches and full-text searches
OK, I will give you more details.
MySQL ver. : 4.0.16
CPU : 2xCelleron 1000 & 1GB RAM
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Last results that I sent are not correct because I forgot to include one
more join, artists.artistid = cds.artistid, bad oversight I know
These are the new results :
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna'IN BOOLEAN MODE) AND
MATCH (cds.title)AGAINST ('"music mix 2001"'IN BOOLEAN MODE)
Time for second SQL : < 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( artists.name ) AGAINST ( 'madonna' ) AND
MATCH ( cds.title ) AGAINST ( 'music' ) AND
MATCH ( cds.title ) AGAINST ( 'mix' ) AND
MATCH ( cds.title ) AGAINST ( '2001' )
One more thing that I noticed in last SQL, when I change, in FROM
clause, positions of tables like this : FROM artists, tracks, cds,
instead FROM artists, cds, tracks I get time of 1.9 sec. instead < 1
sec. ?
Regards
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 00:02
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Hi!
On Nov 27, Uros Kotnik wrote:
> Executing this SQL, takes ~5 sec.
>
> select artists.name, cds.title, tracks.title from artists, tracks, cds
> where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
> and MATCH (artists.name) AGAINST ('madonna')
> and MATCH (cds.title) AGAINST ('music')
> and MATCH (cds.title) AGAINST ('mix')
> and MATCH (cds.title) AGAINST ('2001')
> limit 1001
>
> and this, ~40 sec.
>
> select artists.name, cds.title, tracks.title from artists, tracks, cds
> where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
> and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
> and MATCH (cds.title) AGAINST ('"music mix 2001"' IN BOOLEAN MODE)
> limit 1001
>
> Same result but the speed difference is quite a different, why is that
?
What does EXPLAIN show for both queries ?
And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite.
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: Speed difference between boolean full-text searches and full-text searches
Uros Kotnik wrote:
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna'IN BOOLEAN MODE) AND
MATCH (cds.title)AGAINST ('"music mix 2001"'IN BOOLEAN MODE)
In this case, it cannot resolve the query JUST using indexes.
After finding all records in the index where artists.name matches
madonna and title contains all the words "music", "mix", "2001",
then it must retrieve each record, and examine the title field to
see if the three words are found together in the phrase.
In your other example, it only needs to use the fulltext indexes
to know which records satisfy your query, resulting in MUCH
faster query time.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Licence question
Does this mean that I must make all of my web sites non-commercial? (since I use mySQL as my main DBMS) This was sarcastic, not real question aiming the next sentence MySQL> "You need a license if you sell a product designed specifically for use with MySQL or that requires the MySQL server to function at all. This is true whether or not you provide MySQL for your client as part of your product distribution." If I get GPLicensed mySQL I can distribute it (according to GPL) with my commercial (even non GPL) product or with anything I like if I don't merge it with my project or similar which is mentioned in GPL. Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed difference between boolean full-text searches and full-text searches
It makes sense, but Sergei G. said :
"And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite."
I guess that for my DB I can't expect satisfied "in boolena mode" times
?
But also when searching without "in boolean mode" and include search
criteria from TRACKS table, 13,841,930 rows , like "AND MATCH (
tracks.title) AGAINST ('remix')"
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
-Original Message-
From: Chuck Gadd [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 13:17
To: Uros Kotnik; [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Uros Kotnik wrote:
> Time for first SQL : 21 sec.
> SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
> WHERE artists.artistid = cds.artistid AND artists.artistid =
> tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
> AGAINST ('madonna'IN BOOLEAN MODE) AND
> MATCH (cds.title)AGAINST ('"music mix 2001"'IN BOOLEAN MODE)
In this case, it cannot resolve the query JUST using indexes.
After finding all records in the index where artists.name matches
madonna and title contains all the words "music", "mix", "2001",
then it must retrieve each record, and examine the title field to
see if the three words are found together in the phrase.
In your other example, it only needs to use the fulltext indexes
to know which records satisfy your query, resulting in MUCH
faster query time.
--
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: Export in XML
[snip] > Well Todd, it is a little more complex than this. How much do > you know about XML? In fact, exporting is quite simple. Just give > mysqldump --xml --password=[password] \ [-u username] \ databasename \ [tables] A try. Of course, substitute the correct values and stuff. Will give you pretty neat output. [/snip] That is very cool, but what if I need to export according to a specific DTD (perhaps shared data)? I guess that I'll have to perform some sort of data information transformation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting white spaces
Hi,
I have a table witch has a column of type VARCHAR(10) BINARY. When I tried to insert
white spaces in it (insert into mytable (mycolumn) values('')), an empty String is
stored. Why?
Lemasson Sylvain
Ingénieur développeur
[EMAIL PROTECTED]
tel: 01.48.63.27.27
MAK-SYSTEM SERVICES
Groupe MAK-SYSTEM
Paris Nord 2 - 13, rue de la Perdrix
BP 50035-95946 Roissy CDG Cédex
FRANCE
Can I use TAB delimiters for importing CSV
Am I able to specify to somebody who is dumping from Oracle to use TAB as the delimiter? Does MySQL enable me to specify during import that the CSV is a TAB delimited file? What do most of you use that would not cause problems with text heavy dumps? Double quotes and comas etc are used through out the data in the db. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I use TAB delimiters for importing CSV
On Monday 08 December 2003 13:06, Matthew Stuart wrote: > Am I able to specify to somebody who is dumping from Oracle to use TAB > as the delimiter? Does MySQL enable me to specify during import that > the CSV is a TAB delimited file? Read The Fine Manual :) http://www.mysql.com/doc/en/mysqlimport.html 4.9.9 mysqlimport, Importing Data from Text Files -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: inserting white spaces
[snip]
I have a table witch has a column of type VARCHAR(10) BINARY. When I
tried to insert white spaces in it (insert into mytable (mycolumn)
values('')), an empty String is stored. Why?
[/snip]
Because (' ') is an empty string
see
http://www.mysql.com/doc/en/String_syntax.html
http://www.mysql.com/doc/en/String_comparison_functions.html
http://www.mysql.com/doc/en/String_functions.html
You probably want lpad, rpad, or space
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
primary key type is BLOB
I try to add a primary key on a BLOB column and I have got an error (ERROR 1170: BLOB column 'value5' used in key specification without a key length). May be the answer is in the documentation but I did not find it. So how could I make my BLOB column a primary key? Lemasson Sylvain Ingénieur développeur [EMAIL PROTECTED] tel: 01.48.63.27.27 MAK-SYSTEM SERVICES Groupe MAK-SYSTEM Paris Nord 2 - 13, rue de la Perdrix BP 50035-95946 Roissy CDG Cédex FRANCE
Find duplicates query
I'm trying to search a table for duplicate entries. A record is a dup if fields Fee, Fie, Foe are equal in two records. Would this query be correct to search the table for duplicates? Select Fee,Fie,Foe >From TableFoo Group by Fee,Fie,Foe Having Count(*) > 1; Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
manipulating count group by statement
Hello everyone, I have a mysql table tha holds a variety of ip address. I am trying to work out a select statement that will give me a count(*) grouped by the first 2 octets of the ip. if the ip table contains: 10.102.1.1 192.168.123.34 192.168.123.4 192.168.123.43 192.168.123.3 192.168.0.27 192.168.0.212 10.102.1.54 10.102.1.75 10.102.1.62 10.102.1.12 10.102.1.10 10.102.1.111 how can my: SELECT ip, COUNT(*) from tbl_MasterIp group by ip; be changed so that I get back ipCOUNT(*) 10.102.1 20 192.168.12330 192.168.0 10 or whatever the ip counts should be. I have about 5 different octets within this table. I am using mysql 3.* Thanks for any help... Sincerely, Chad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Export in XML
On maandag 8 december 2003 14:08 Jay Blanchard told the butterflies: > [snip] > > Well Todd, it is a little more complex than this. How much do you > > know about XML? > > In fact, exporting is quite simple. Just give > > > mysqldump --xml --password=[password] \ > [-u username] \ > databasename \ > [tables] > > A try. Of course, substitute the correct values and stuff. > Will give you pretty neat output. > [/snip] > > That is very cool, but what if I need to export according to > a specific DTD (perhaps shared data)? I guess that I'll have > to perform some sort of data information transformation. Well yeah, if you want that, then you'll have to do some data transformation. But in that case I think you'll just have to grab to some very cool XML tool. I'd set up some XSLT document and then transform the MySQL output to the specific DTD with that. It might be cool if you could specify the XSLT to MySQL, that would make it a powerfull XML tool as well. Not sure tough if that's the purpose of this particular DBMS. :P, Wouter (let's drink to the birth and health of the future queen of The Netherlands) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: manipulating count group by statement
Hi, I tried this and it seems to work: SELECT SUBSTRING_INDEX(INET_NTOA(ip), '.',3) AS addr, COUNT(*) AS addrsum FROM ips GROUP BY addr; Perhaps you don't have your IP adresses stored as integers but instead as text. Then the following query is for you! Your query: SELECT ip, COUNT(*) from tbl_MasterIp group by ip; Would then be something like this: SELECT SUBSTRING_INDEX(ip, '.', 3) AS ip, COUNT(*) AS addrsum FROM tbl_MasterIp Group By ip; Good luck, Mike On Monday 08 December 2003 15.23, chad kellerman wrote: > Hello everyone, > >I have a mysql table tha holds a variety of ip address. I am trying > to work out a select statement that will give me a count(*) grouped by > the first 2 octets of the ip. > > if the ip table contains: > > 10.102.1.1 > 192.168.123.34 > 192.168.123.4 > 192.168.123.43 > 192.168.123.3 > 192.168.0.27 > 192.168.0.212 > 10.102.1.54 > 10.102.1.75 > 10.102.1.62 > 10.102.1.12 > 10.102.1.10 > 10.102.1.111 > > how can my: > SELECT ip, COUNT(*) from tbl_MasterIp group by ip; > > be changed so that I get back > >ipCOUNT(*) > 10.102.1 20 > 192.168.12330 > 192.168.0 10 > > or whatever the ip counts should be. I have about 5 different octets > within this table. > > I am using mysql 3.* > > Thanks for any help... > > > > Sincerely, > Chad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key type is BLOB
Hi, >May be the answer is in the documentation but I did not find it. So >how could I make my BLOB column a primary key? My guess is you cannot. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: view warnings?
Bryan Harris <[EMAIL PROTECTED]> wrote: > > I'm pretty new to MySQL, but I already like it. Kudos to the developers! > > I recently did a mysqlimport on a few data files, and it came back with over > 4000 warnings. How can I actually see what the warnings were? I'm still > not sure what it was upset about... You can't. >From 4.1.1 you can see warning if you use LOAD DATA INFILE statement: http://www.mysql.com/doc/en/SHOW_WARNINGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key type is BLOB
"Lemasson Sylvain" <[EMAIL PROTECTED]> wrote: > I try to add a primary key on a BLOB column and I have got an error (ERROR 1170: > BLOB column 'value5' used in key specification without a key length). > May be the answer is in the documentation but I did not find it. So how could I make > my BLOB column a primary key? You can create index on the prefix of the column: http://www.mysql.com/doc/en/Indexes.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key type is BLOB
Lemasson, When you refer to a column as a primary key, I tend to think that this key will be your main "relationship" point to the data in your table. Now, when you combine the primary key concept with a BLOB (binary large object), it seems to me that you are almost defeating the purpose of a primary key (you'd be making the index basically useless). I have never heard of anybody making a BLOB a primary key, so I could not comment on whether or not it is actually do-able or possible with MySQL. If it is, it shouldn't be. I would recommend you focus your design on creating inter-table relationships based around smaller keys that index well, such as integers and keep the BLOBs in a separate table, away from your frequently read data. -- R. - Original Message - From: "Lemasson Sylvain" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 08, 2003 8:23 AM Subject: primary key type is BLOB I try to add a primary key on a BLOB column and I have got an error (ERROR 1170: BLOB column 'value5' used in key specification without a key length). May be the answer is in the documentation but I did not find it. So how could I make my BLOB column a primary key? Lemasson Sylvain Ingénieur développeur [EMAIL PROTECTED] tel: 01.48.63.27.27 MAK-SYSTEM SERVICES Groupe MAK-SYSTEM Paris Nord 2 - 13, rue de la Perdrix BP 50035-95946 Roissy CDG Cédex FRANCE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>= not working?
I've got an entry in a table where the value is 875. If I run a query on that table with the clause AND sqaurefeet >= '$squarefeet' and $squarefeet has a value of say 1000 the row with the squarefeer value of 875 will appear. The column is of type varchar(10) and using the binary flag. What am I doing wrong? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: >= not working?
On 8 Dec 2003 at 11:30, Ed Curtis wrote: > I've got an entry in a table where the value is 875. If I run a query > on that table with the clause AND sqaurefeet >= '$squarefeet' and > $squarefeet has a value of say 1000 the row with the squarefeer value > of 875 will appear. The column is of type varchar(10) and using the > binary flag. If the column is VARCHAR, then the comparison is correct. The string '875' is greater lexically (alphabetically) than the string '1000'. If you want the comparison to be numeric, you should use an integer (or other numeric type) for the column. Failing that, you have to cast the value into a numeric type before comparing them. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: >= not working?
You have a few ways to do this. What's happening here is that you do a comparison in a string context, which means that it will sort according to the ascii values, and 1 comes before 8. To sort the way you want you need to specify to MySQL that you want to do it in a numeric context. You have two simple ways to do this. Add 0 to the string number to force it into numeric mode. AND squarefeet >= '$squarefeet' + 0 use the CAST() function, it's described more at: http://www.mysql.com/doc/en/Cast_Functions.html On Mon, 8 Dec 2003, Ed Curtis wrote: > > I've got an entry in a table where the value is 875. If I run a query on > that table with the clause AND sqaurefeet >= '$squarefeet' and $squarefeet > has a value of say 1000 the row with the squarefeer value of 875 will > appear. The column is of type varchar(10) and using the binary flag. > > What am I doing wrong? > > Thanks, > > Ed > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: >= not working?
Thanks for the sugestions from all. I just switched the column type to integer. Thanks again, Ed On Mon, 8 Dec 2003, Tobias Asplund wrote: > > You have a few ways to do this. > What's happening here is that you do a comparison in a string context, > which means that it will sort according to the ascii values, and 1 comes > before 8. > To sort the way you want you need to specify to MySQL that you want to do > it in a numeric context. > You have two simple ways to do this. > > Add 0 to the string number to force it into numeric mode. > > AND squarefeet >= '$squarefeet' + 0 > > use the CAST() function, it's described more at: > http://www.mysql.com/doc/en/Cast_Functions.html > > > On Mon, 8 Dec 2003, Ed Curtis wrote: > > > > > I've got an entry in a table where the value is 875. If I run a query on > > that table with the clause AND sqaurefeet >= '$squarefeet' and $squarefeet > > has a value of say 1000 the row with the squarefeer value of 875 will > > appear. The column is of type varchar(10) and using the binary flag. > > > > What am I doing wrong? > > > > Thanks, > > > > Ed > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication...How often is the data updated to the slave ?
If i have done all the steps to the replication, How often is the data updated to the slave ?. Can i set the time ? Regards Spectrum. _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.latam.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[REPOST] Openssl support not activated?
I compiled MySQL 4.1.1 --with-openssl --with-vio and when I look at the variables with mysqladmin, "has_openssl" is set to "NO". I've got OpenSSL 0.9.7c installed. I'm seeing this on both Solaris and Debian hosts. I'm not seeing any errors during the configure or compile phases. What could have happened to cause this, and how can I fix it? Thanks. -Greg G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Range
Hello, been trying to figure out the correct SQL query to get percentage from a table that list a Min, and Max., price range and the percentage associated to the range. Here is the current data in the table: 1 0.00 4999.00 13.0 2 5000.00 .00 12.5 3 1.00 14999.00 12.0 4 15000.00 1.00 11.0 5 2.00 9.00 10.0 Table structure: commisid smallint(3) NOT NULL auto_increment, min decimal(6,2) NOT NULL default '0.00', max decimal(6,2) NOT NULL default '0.00', percentage decimal(2,1) NOT NULL default '0.0', PRIMARY KEY (commisid) So if I wanted to get the percentage for a price of 500.00 I would like to query the data to find where the 500.00 is equal or greater then the 'min' and less or equal to the 'max' giving me the percentage value. I have tried 'select percentage from table_name where min >= 500.00 and max <= max; but that renders no results is there a query to accomplish this or should I setup my table structure differently. ?? TIA -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repost [Fwd: Query Range]
Sorry.. The query should be: select percentage from table_name where min >= 500.00 and 500.00 <= max; database. Original Message Subject: Query Range Date: Mon, 08 Dec 2003 11:23:28 -0600 From: Mike Blezien <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Organization: Thunder Rain Internet Publishing To: MySQL List <[EMAIL PROTECTED]> Hello, been trying to figure out the correct SQL query to get percentage from a table that list a Min, and Max., price range and the percentage associated to the range. Here is the current data in the table: 1 0.00 4999.00 13.0 2 5000.00 .00 12.5 3 1.00 14999.00 12.0 4 15000.00 1.00 11.0 5 2.00 9.00 10.0 Table structure: commisid smallint(3) NOT NULL auto_increment, min decimal(6,2) NOT NULL default '0.00', max decimal(6,2) NOT NULL default '0.00', percentage decimal(2,1) NOT NULL default '0.0', PRIMARY KEY (commisid) So if I wanted to get the percentage for a price of 500.00 I would like to query the data to find where the 500.00 is equal or greater then the 'min' and less or equal to the 'max' giving me the percentage value. I have tried 'select percentage from table_name where min >= 500.00 and max <= max; but that renders no results is there a query to accomplish this or should I setup my table structure differently. ?? TIA -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Avarage monthly import
Hello people. I have a table structured like this +--+---+ | Field| Type | +--+---+ | dt_imp | date | | imp | decimal(5,2) | +--+---+ If I want to get the total per month I can do a query like this select date_format(a.dt_imp,'%Y/%m') "date", SUM(a.imp) from sp a group by date_format(a.dt_imp, '%Y/%m') order by 1 with a result like this +-++ | ... |... | | 2002/02 | 238.30 | | 2002/03 |1385.95 | | 2002/04 | 475.30 | | 2002/05 | 171.10 | | ... |... | +-++ now, I would like to get the avarage monthly import, so I could use the AVG() function. Unfortunly I didn't understand how to use it in my case. I've tried something like select avg(subqry1) from sp or select * from subqry1 where suqry1 is the query precedently written; but I didn't succed in this. How can I do to solve my problem? thanks everybody. G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repost [Fwd: Query Range]
You have the first inequality backwards. You want: SELECT percentage FROM table_name WHERE min <= 500.00 AND max >= 500.00 Michael Mike Blezien wrote: Sorry.. The query should be: select percentage from table_name where min >= 500.00 and 500.00 <= max; database. Original Message Subject: Query Range Date: Mon, 08 Dec 2003 11:23:28 -0600 From: Mike Blezien <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Organization: Thunder Rain Internet Publishing To: MySQL List <[EMAIL PROTECTED]> Hello, been trying to figure out the correct SQL query to get percentage from a table that list a Min, and Max., price range and the percentage associated to the range. Here is the current data in the table: 1 0.00 4999.00 13.0 2 5000.00 .00 12.5 3 1.00 14999.00 12.0 4 15000.00 1.00 11.0 5 2.00 9.00 10.0 Table structure: commisid smallint(3) NOT NULL auto_increment, min decimal(6,2) NOT NULL default '0.00', max decimal(6,2) NOT NULL default '0.00', percentage decimal(2,1) NOT NULL default '0.0', PRIMARY KEY (commisid) So if I wanted to get the percentage for a price of 500.00 I would like to query the data to find where the 500.00 is equal or greater then the 'min' and less or equal to the 'max' giving me the percentage value. I have tried 'select percentage from table_name where min >= 500.00 and max <= max; but that renders no results is there a query to accomplish this or should I setup my table structure differently. ?? TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specs for a dedicated MySQL server
There's actually a MySQL Database Appliance out there. Have you looked at that yet? http://pogolinux.com/mysql/index.html You could run MySQL on any number of different machines. Intel, Athlon, PowerPC, etc. Linux, FreeBSD, Windows, Mac OS X. - Gabriel On Dec 7, 2003, at 4:41 PM, Defryn, Guy wrote: Hi there, I have been asked to investigate a solution to offer MySQL to our staff. I am not sure how many people will make use of it at this stage. Most people would most likely use it as a backend for message boards And to store information on whatever they are doing. I am sure it will be for web purposes only. I cannot see the need for transactions etc. My employer usually goes for HP DL380 G3 servers. Any tips on redundancy, backup etc? Also, Is there anyone who uses Windows on a Mysql production server? I want to use FreeBSD or Linux but that might not be up to me. What License do I need? Basically we are an ISP for our departments Cheers GUY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting a postgres database to MySQL
Hi, I've been using postgres for a while but have today taken the plunge and installed MySQL 4.0.16 on my Linux box. It's all now working perfectly. Is there a simple way of copying a postgres database to MySQL? I was hoping that postgres's backup 'pg_dump' utility (which dumps a database into a text file in such a format that it can be easily recreated) might be compatible with MySQL's backup and restore facilities. Any advice or web pointers would be greatly appreciated. --Note that this is for my own personal use and not for a company so I'm not looking for expensive commercial software to do this. Thanks and regards, ..matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting a postgres database to MySQL
i think you can because the dump file is a sql file and it is universal for both exception that mysql hasn't subselects. If you havent subselects it should work Matthew Stanfield <[EMAIL PROTECTED]> wrote:Hi, I've been using postgres for a while but have today taken the plunge and installed MySQL 4.0.16 on my Linux box. It's all now working perfectly. Is there a simple way of copying a postgres database to MySQL? I was hoping that postgres's backup 'pg_dump' utility (which dumps a database into a text file in such a format that it can be easily recreated) might be compatible with MySQL's backup and restore facilities. Any advice or web pointers would be greatly appreciated. --Note that this is for my own personal use and not for a company so I'm not looking for expensive commercial software to do this. Thanks and regards, ..matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Comparing strings containing possible quotes
Hi Ya, I have a typical select as follows SELECT * FROM ytbl_development AS t1 WHERE (t1.txtDevName LIKE '%#form.searchtext#%') However users a complaining that if they search for for a name that contains a quote eg a development named King's Reach it is not found. A) Now it simply won't match King's Reach whether I include the quote or not. WHY?? (Have the quotes been converted ie to URL Encoding)) B) Now I can filter any quotes from form.searchtext easy enough, but how do I filter them the Left Hand Side eg from txtDevName?) I look forward to seeing how you solve this. zzapper -- vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?" http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specs for a dedicated MySQL server
I'm all for using what works and do not try to avoid MS (as long as it fits the requirements). However, I found out that MySQL does not work very well under load under Windows 2000. It is considerably slower than a similar configured Linux system. It may be how MySQL AB compiled the Windows binaries (I use the official binaries for both Windows and Linux). I do have one or two Win32 MySQL installations because we did not want to setup a dedicated server. They are use for small things here and there. It all depends on application! :) -will Real-time Chat: irc.freenode.net -> #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: "Defryn, Guy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, December 07, 2003 1:41 PM Subject: Specs for a dedicated MySQL server Hi there, [snip] Also, Is there anyone who uses Windows on a Mysql production server? I want to use FreeBSD or Linux but that might not be up to me. [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Specs for a dedicated MySQL server
This looks like a flash server. I am not sure if we will have enough Mysql use to justify this. Does anyone have an idea for what kind of use (amount of db etc) that I would use this kind of server? Guy -Original Message- From: Gabriel Ricard [mailto:[EMAIL PROTECTED] Sent: Tuesday, 9 December 2003 7:32 a.m. To: Defryn, Guy Cc: [EMAIL PROTECTED] Subject: Re: Specs for a dedicated MySQL server There's actually a MySQL Database Appliance out there. Have you looked at that yet? http://pogolinux.com/mysql/index.html You could run MySQL on any number of different machines. Intel, Athlon, PowerPC, etc. Linux, FreeBSD, Windows, Mac OS X. - Gabriel On Dec 7, 2003, at 4:41 PM, Defryn, Guy wrote: > Hi there, > > > > I have been asked to investigate a solution to offer MySQL to our > staff. > I am not sure how many people will make use of it at this stage. > Most people would most likely use it as a backend for message boards > > And to store information on whatever they are doing. I am sure it will > be for web purposes only. > > I cannot see the need for transactions etc. > > > > My employer usually goes for HP DL380 G3 servers. Any tips on > redundancy, backup etc? > > Also, Is there anyone who uses Windows on a Mysql production server? I > want to use FreeBSD or Linux but that might not be up to me. > > What License do I need? Basically we are an ISP for our departments > > > > Cheers > > > > GUY > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Login Problems
Hello, I am experiencing a strange problem with a MySQL server on a FreeBSD machine here in college. The problem began after I de-installed the 4.1 ALPHA versions of both the client and the server, removed the /var/db/mysql/ directory so the install would start afresh and installed the 4.0.6 Production release, again of both the client and the server. But after I did that users can now only connect if their passwords are null. As soon as a user sets a password they receive he following message: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have tried reinstalling only to find the same problem. Can anyone please help with this as is it hindering the development of our new web site. Thank you in advance. Regards, Conor Quigley. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
Have you flushed priv's recently? -Original Message- From: Schrodinger [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 1:02 PM To: MySQL General Lists Subject: Login Problems Hello, I am experiencing a strange problem with a MySQL server on a FreeBSD machine here in college. The problem began after I de-installed the 4.1 ALPHA versions of both the client and the server, removed the /var/db/mysql/ directory so the install would start afresh and installed the 4.0.6 Production release, again of both the client and the server. But after I did that users can now only connect if their passwords are null. As soon as a user sets a password they receive he following message: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have tried reinstalling only to find the same problem. Can anyone please help with this as is it hindering the development of our new web site. Thank you in advance. Regards, Conor Quigley. -- 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]
Fulltext index not being used
Fellow Mysqlians,
Can anyone tell me why mysql refuses to use the fulltext index
in the second query?
Query 1;
mysql> explain select straight_join distinct i.ID from INTEREST i use index
(COMMENT)
inner join PERSON p on p.ID=i.PERSON_ID where match(i.COMMENT)
against('+todo ' in boolean mode);
+---+--++-+-+---
--+--+--+
| table | type | possible_keys | key | key_len | ref
| rows | Extra|
+---+--++-+-+---
--+--+--+
| i | fulltext | COMMENT| COMMENT | 0 |
|1 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx | PRIMARY | 40 |
i.PERSON_ID |1 | Using index; Distinct|
+---+--++-+-+---
--+--+--+
2 rows in set (0.00 sec)
Query2
mysql> explain select straight_join distinct i.ID from INTEREST i use index
(COMMENT) inner join PERSON p on p.ID=i.PERSON_ID inner join TURBINE_USER t
on p.ID = t.LOGIN_NAME where match(i.COMMENT) against('+todo ' in boolean
mode);
+---+++---+-
+-++--+
| table | type | possible_keys | key |
key_len | ref | rows | Extra|
+---+++---+-
+-++--+
| i | ALL| NULL | NULL |
NULL | NULL| 511322 | Using where; Using temporary |
| p | eq_ref | PRIMARY,person_id_base_idx | PRIMARY |
40 | i.PERSON_ID | 1 | Using index; Distinct|
| t | ref| turbine_user_name_idx | turbine_user_name_idx |
40 | p.ID| 1 | Using index; Distinct|
The only difference between the 2 queries is the extra join. But with the
straight_join and use index I don't understand why the fulltext was
abandoned.
Table info
INTEREST
| INTEREST | CREATE TABLE `INTEREST` (
`ID` varchar(40) NOT NULL default '',
`PERSON_ID` varchar(40) NOT NULL default '',
`COMMENT` mediumtext,
`DATE_CREATED` timestamp(14) NOT NULL,
`INTEREST_ID` varchar(40) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `PERSON_ID` (`PERSON_ID`,`INTEREST_ID`),
KEY `interest_person_id_idx` (`PERSON_ID`),
KEY `interest_person_id_two_idx` (`PERSON_ID`,`COMMENT`(200)),
FULLTEXT KEY `COMMENT` (`COMMENT`)
) TYPE=MyISAM |
TURBINE_USER
TURBINE_USER | CREATE TABLE `TURBINE_USER` (
`USER_ID` int(11) NOT NULL auto_increment,
`LOGIN_NAME` varchar(40) NOT NULL default '',
`PASSWORD_VALUE` varchar(32) NOT NULL default '',
`FIRST_NAME` varchar(99) NOT NULL default '',
`LAST_NAME` varchar(99) NOT NULL default '',
`EMAIL` varchar(99) default NULL,
`CONFIRM_VALUE` varchar(99) default NULL,
`MODIFIED` timestamp(14) NOT NULL,
`CREATED` timestamp(14) NOT NULL,
`LAST_LOGIN` timestamp(14) NOT NULL,
`OBJECTDATA` mediumblob,
PRIMARY KEY (`USER_ID`),
UNIQUE KEY `EMAIL` (`EMAIL`),
KEY `turbine_user_name_idx` (`LOGIN_NAME`)
) TYPE=MyISAM |
PERSON
KEY `person_id_base_idx` (`ID`),
KEY `person_email_idx` (`EMAIL`),
KEY `person_zip_idx` (`ZIP`),
KEY `person_last_name_idx` (`LAST_NAME`),
KEY `person_first_name_idx` (`FIRST_NAME`),
KEY `person_generation_idx` (`GENERATION`),
KEY `person_birthday_idx` (`BIRTHDAY`),
KEY `person_user_created_idx` (`USER_CREATED`)
) TYPE=MyISAM |
Many Thanks,
Trevor
RE: Login Problems
No I haven't. How do I do that? -Original Message- From: Brian Duke [mailto:[EMAIL PROTECTED] Sent: 08 December 2003 20:07 To: 'Schrodinger'; 'MySQL General Lists' Subject: RE: Login Problems Have you flushed priv's recently? -Original Message- From: Schrodinger [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 1:02 PM To: MySQL General Lists Subject: Login Problems Hello, I am experiencing a strange problem with a MySQL server on a FreeBSD machine here in college. The problem began after I de-installed the 4.1 ALPHA versions of both the client and the server, removed the /var/db/mysql/ directory so the install would start afresh and installed the 4.0.6 Production release, again of both the client and the server. But after I did that users can now only connect if their passwords are null. As soon as a user sets a password they receive he following message: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have tried reinstalling only to find the same problem. Can anyone please help with this as is it hindering the development of our new web site. Thank you in advance. Regards, Conor Quigley. -- 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: Specs for a dedicated MySQL server
There is one server that we have and that I can possible use Dual Pentium 1.2 ghz CPU 1 gig of Ram 18 gb SCSI 0+1 Raid What performance can I expect from this machine. What usage would it be able to handle? cheers -Original Message- From: Defryn, Guy Sent: Tuesday, 9 December 2003 9:01 a.m. To: [EMAIL PROTECTED] Subject: RE: Specs for a dedicated MySQL server This looks like a flash server. I am not sure if we will have enough Mysql use to justify this. Does anyone have an idea for what kind of use (amount of db etc) that I would use this kind of server? Guy -Original Message- From: Gabriel Ricard [mailto:[EMAIL PROTECTED] Sent: Tuesday, 9 December 2003 7:32 a.m. To: Defryn, Guy Cc: [EMAIL PROTECTED] Subject: Re: Specs for a dedicated MySQL server There's actually a MySQL Database Appliance out there. Have you looked at that yet? http://pogolinux.com/mysql/index.html You could run MySQL on any number of different machines. Intel, Athlon, PowerPC, etc. Linux, FreeBSD, Windows, Mac OS X. - Gabriel On Dec 7, 2003, at 4:41 PM, Defryn, Guy wrote: > Hi there, > > > > I have been asked to investigate a solution to offer MySQL to our > staff. > I am not sure how many people will make use of it at this stage. > Most people would most likely use it as a backend for message boards > > And to store information on whatever they are doing. I am sure it will > be for web purposes only. > > I cannot see the need for transactions etc. > > > > My employer usually goes for HP DL380 G3 servers. Any tips on > redundancy, backup etc? > > Also, Is there anyone who uses Windows on a Mysql production server? I > want to use FreeBSD or Linux but that might not be up to me. > > What License do I need? Basically we are an ISP for our departments > > > > Cheers > > > > GUY > -- 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]
?? .tcshrc On OS X ??
I had tips from some people here to install the complete MySQL from Server Logistics on my Mac OS X 10.2 and so I did (I also installed Apache 2 and PHP from them). Now I'm on Step one, but got stuck here. (I always get stuck while trying this MySQL). In the pdf-file that came along with the installation it says: shell> echo $SHELL This will return the path to the shell you are using. In most cases it will return either "/bin/tcsh" (tcsh shell) or "/bin/bash" (bash shell). If you are using the tcsh shell, you will have to modify the file ".tcshrc" located within your home directory. If this file does not exist, then you will have to create a new one. After opening the file in your text editor, add the following line at the bottom of the file: setenv PATH "$PATH":/Library/MySQL/bin Save the file. But when I try to find this .tcshrc file (whisch is the one showing up when I write that line in my Terminal. I can't find it (not by using Sherlock or when looking). I guess it should be in MyComputer / Library / MySQL / bin. But it isn't. So I wrote the line they explain in the end of the text above and try to save it as .tcshrc, but my computer tells me I can't use a name with a dot in the beginning. So I tried to take that away and then drop the file from the desktop to the bin folder, but then it tells m I'm not allowed to change that folder?! What am I supposed to do??? I'm really a newbie here... and it seems I'll always be... I've tried this sooo many times the last week now. I'm about to give up soon. :-( _ Hitta rätt köpare på MSN Köp & Sälj http://www.msn.se/koposalj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed difference between boolean full-text searches and full-text searches
Uros Kotnik wrote:
It makes sense, but Sergei G. said :
"And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite."
I guess that for my DB I can't expect satisfied "in boolena mode" times
?
But also when searching without "in boolean mode" and include search
criteria from TRACKS table, 13,841,930 rows , like "AND MATCH (
tracks.title) AGAINST ('remix')"
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the "special" characters, like
+-*"". I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna' IN BOOLEAN MODE) AND
MATCH (cds.title) AGAINST ('+music +mix +2001"'IN BOOLEAN MODE)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if function?
Is there a way to write an if statement in mysql to affect the following?... if field a=0,b=0,c=0 then update d='complete' where the table consists of... a int(1), b int(1), c int(1), d char(8) |a |b |c |d | |1 |0 |0 |null | |1 |1 |0 |null | |0 |0 |0 |null | resulting with the third recode updated to show 'complete'? TIA Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if function?
UPDATE table SET d='complete' WHERE a=0 AND b=0 and c=0; -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 3:58 PM To: MySQL List Subject: if function? Is there a way to write an if statement in mysql to affect the following?... if field a=0,b=0,c=0 then update d='complete' where the table consists of... a int(1), b int(1), c int(1), d char(8) |a |b |c |d | |1 |0 |0 |null | |1 |1 |0 |null | |0 |0 |0 |null | resulting with the third recode updated to show 'complete'? TIA Larry -- 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: Find duplicates query
If you do: Select concat(Fee,'some_dilimiter',Fie,'some_dilimiter',Foe), count(*) as wibble, Fee,Fie,Foe >From TableFoo Group by 1 order by 2 desc; If you want to do a "having wibble > 1" then that'll eliminate all non-duplicates from the results. You should get a nice list of duplicates ordered by how many dupes there are.. Cheers, A -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Monday 08 December 2003 14:20 To: [EMAIL PROTECTED] Subject: Find duplicates query I'm trying to search a table for duplicate entries. A record is a dup if fields Fee, Fie, Foe are equal in two records. Would this query be correct to search the table for duplicates? Select Fee,Fie,Foe >From TableFoo Group by Fee,Fie,Foe Having Count(*) > 1; Thanks, Jeff -- 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: In need of a dummy select statement ...
Thanks for the reply. As you said, we can always create one if it doesn't exist. How about using show command ? Can we execute 'show tables' command from a program using MyODBC ? If yes, can we give a pattern to 'like' which doesn't match anything. i.e. show tables like 'something which will not match anything'. Is this possible ? Thanks, Srinivas. Martijn Tonies wrote: Hi, I am a newbie to mysql db. I need a select statement which will just execute and doesn't return any results. Also, it should not depend on any table which is specific to a database. For example, Oracle has a table named 'tab' which will be present in all databases so my dummy select statement looks like 'select tname from tab where 0=1'. Could this functionality be achieved in mysql ? Is there any table in mysql which is similar to Oracle's 'tab' ? Not by default, but hey: you can always create one. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com
RE: Avarage monthly import
Are you using MySQL? OK, if you are then first simplify your query: select date_format(dt_imp,'%Y/%m') as date, SUM(imp) from sp group by 1 order by 1 Then add the AVG column which will work ok with the "group by" : select date_format(dt_imp,'%Y/%m') as date, SUM(imp), AVG(imp) from sp group by 1 order by 1 Is this what you were after? (if not, let me know and I'll try again..) Cheers, Andrew -Original Message- From: getting_out [mailto:[EMAIL PROTECTED] Sent: Monday 08 December 2003 17:21 To: MySQL ML Subject: Avarage monthly import Hello people. I have a table structured like this +--+---+ | Field| Type | +--+---+ | dt_imp | date | | imp | decimal(5,2) | +--+---+ If I want to get the total per month I can do a query like this select date_format(a.dt_imp,'%Y/%m') "date", SUM(a.imp) from sp a group by date_format(a.dt_imp, '%Y/%m') order by 1 with a result like this +-++ | ... |... | | 2002/02 | 238.30 | | 2002/03 |1385.95 | | 2002/04 | 475.30 | | 2002/05 | 171.10 | | ... |... | +-++ now, I would like to get the avarage monthly import, so I could use the AVG() function. Unfortunly I didn't understand how to use it in my case. I've tried something like select avg(subqry1) from sp or select * from subqry1 where suqry1 is the query precedently written; but I didn't succed in this. How can I do to solve my problem? thanks everybody. G. -- 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: ?? .tcshrc On OS X ??
Your shell, in Terminal, is tcsh. So, when you entered `echo $SHELL`, the response you got was /bin/tcsh. The file .tcshrc is a plain text file where you can customize your shell (Terminal) environment. It belongs in your home directory, as stated in the directions, not in MyComputer/Library/MySQL/bin. It won't exist until you create it, which is why you can't find it. Try this in the shell (Terminal): cd ~ echo 'setenv PATH "$PATH":/Library/MySQL/bin' >>.tcshrc The first command changes your current working directory to your home directory, the second line prints the line you need and appends the output to the .tcshrc file. The ">>" does the redirection/appending. The PATH environment variable tells the shell (Terminal) where to look for executable programs. The line you are appending adds the location of the mysql programs to that list. Also, the .tcshrc is read by tcsh at startup, so adding this line won't affect the shell (Terminal) session you are in at that moment. You can update the PATH in the current session with the command setenv PATH "$PATH":/Library/MySQL/bin Michael Lost Idols wrote: I had tips from some people here to install the complete MySQL from Server Logistics on my Mac OS X 10.2 and so I did (I also installed Apache 2 and PHP from them). Now I'm on Step one, but got stuck here. (I always get stuck while trying this MySQL). In the pdf-file that came along with the installation it says: shell> echo $SHELL This will return the path to the shell you are using. In most cases it will return either "/bin/tcsh" (tcsh shell) or "/bin/bash" (bash shell). If you are using the tcsh shell, you will have to modify the file ".tcshrc" located within your home directory. If this file does not exist, then you will have to create a new one. After opening the file in your text editor, add the following line at the bottom of the file: setenv PATH "$PATH":/Library/MySQL/bin Save the file. But when I try to find this .tcshrc file (whisch is the one showing up when I write that line in my Terminal. I can't find it (not by using Sherlock or when looking). I guess it should be in MyComputer / Library / MySQL / bin. But it isn't. So I wrote the line they explain in the end of the text above and try to save it as .tcshrc, but my computer tells me I can't use a name with a dot in the beginning. So I tried to take that away and then drop the file from the desktop to the bin folder, but then it tells m I'm not allowed to change that folder?! What am I supposed to do??? I'm really a newbie here... and it seems I'll always be... I've tried this sooo many times the last week now. I'm about to give up soon. :-( _ Hitta rätt köpare på MSN Köp & Sälj http://www.msn.se/koposalj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cross database queries
hello guys, im just wondering if there are any limitations in cross database queries like: SELECT one.* FROM db1.one, db2.two; are there any differences in joining tables from within various databases to joinin tables from within the same database? the background of my question is that various users which all have their own database need to share common data. so i wanted to create one common database which they all have access to. i did some tests and all succeeded. but want to be sure before heading production status. thanks alot for your help. kind regards, heri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exportation
Hi, I have a question concerning the portability of the files of the database. Is there a way to export the content of a database from PC (win or *NIX) to Mac ? Great thanks -- Mélet Pierre-Etienne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exportation
use mysqldump to dump all your databases. man mysqldump should help further. regards, /H On Mon, 08 Dec 2003 22:49:30 +0100 Pierre-Etienne Mélet <[EMAIL PROTECTED]> wrote: > Hi, > > I have a question concerning the portability of the files of the > database. Is there a way to export the content of a database from PC > (win or *NIX) to Mac ? > > Great thanks > > -- > Mélet Pierre-Etienne > > > > > -- > 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: Exportation
Pierre-Etienne Mélet wrote: Hi, I have a question concerning the portability of the files of the database. Is there a way to export the content of a database from PC (win or *NIX) to Mac ? Great thanks Use 'mysqldump' to dump your database(s), and then import them into your new computer with the mysql client: mysql < my_dump_file.sql -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In need of a dummy select statement ...
On 08-Dec-2003 Srinivas B.S.S wrote: > Hi, > > I am a newbie to mysql db. I need a select statement which will just > execute and doesn't > return any results. Also, it should not depend on any table which is > specific to a database. > For example, Oracle has a table named 'tab' which will be present in all > databases so my > dummy select statement looks like 'select tname from tab where 0=1'. > Could this > functionality be achieved in mysql ? Is there any table in mysql which > is similar to Oracle's > 'tab' ? > Does it have to be a SELECT? Or just a NO-OP? SET @nothing=0; -or- DO 0; - or - SHOW TABLES LIKE 'asdfzxcv1234'; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No testing server running (OS X + DW MX)
Setting up MySQL and PHP on my Mac OS X 10.2 Just made it work... well, at least I now have a databse that I created and a table with two things inserted. I can also see them when I do a SELECT... So, since I know it's working, I started my DW MX to try to work from there, but I just get an error. I've been setting up the database with all the info and when I want to select from the list of databases I just get the following message: HTTP Error Code 404 File Not Found. Here are some possible reasons for the problem: 1) There is no testing server running on the server machine. What's wrong? Any clues here in this list? Staffan PS. I'm a newbie, so please write in newbie language ;-) _ Hitta rätt köpare på MSN Köp & Sälj http://www.msn.se/koposalj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with VB6 and CR8
hi guys,i need some help, the problem is when i try to execute a report from vb6 the report says it cant open the server, im using mysql 4.1, myodbc 3.51, cr8 and vb6, the strange thing is when i open the report directly from CR it works!! =) but when i call it from vb6 it not works =( i have the root account with password i think its the problem because if i erase the password the reports works, what is wrong in my configuration? or how can i send the password and user to the report in excution time? im using the ocx from crystal reports i hope you can help me. Thanks a lot!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disk Configuration
I have another question regarding specs for a server. I have talked to a DBA who has no mySQL knowledge but did work with Oracle, etc He said I should do some more investigation on disk configuration and how mysql benefits from Separating log files, data files, etc. All feedback welcome!
RV: Problem with VB6 and CR8
hi!! im again i found the aswer to my problem in this site http://www.necrug.org/Crystal-Training/crystal-forum-message.asp?site=&topic id=2244 for all who also have the same problem =) -Mensaje original- De: Rick [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 08 de Diciembre de 2003 16:38 Para: [EMAIL PROTECTED] Asunto: Problem with VB6 and CR8 hi guys,i need some help, the problem is when i try to execute a report from vb6 the report says it cant open the server, im using mysql 4.1, myodbc 3.51, cr8 and vb6, the strange thing is when i open the report directly from CR it works!! =) but when i call it from vb6 it not works =( i have the root account with password i think its the problem because if i erase the password the reports works, what is wrong in my configuration? or how can i send the password and user to the report in excution time? im using the ocx from crystal reports i hope you can help me. Thanks a lot!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication/binary log
We are running MySQL 3.23 in production, and have replication setup in the following manner: There are two machines (m1 and m2). Replication is setup in a circular way. Both machines are master and slave, more specifically, m1 is master to m2 and m2 is master to m1. I checked today and saw that one of the machines had a bunch of binary log files (see below). I read in the MySQL documentation that you can delete the logs by issuing a RESET MASTER command. I am wonder how this will affect replication. Is this going to break replication in any way? Is it safe to simply delete the binary log files manually ? (Id prefer to do this). Any input would be helpful. Thank you. MySQL data directory: total 4388640 drwx--2 mysqlmysql4096 Aug 17 19:24 mysql drwx--2 mysqlmysql4096 Aug 17 19:46 test -rw-rw1 mysqlmysql 445 Aug 17 20:42 mw01-bin.001 drwxr-xr-x8 root root 4096 Aug 17 23:51 .. -rw-rw1 mysqlmysql 111 Aug 24 04:02 mw01-bin.003 -rw-rw1 mysqlmysql 308 Aug 24 04:02 mw01-bin.002 -rw-rw1 mysqlmysql 111 Aug 31 04:02 mw01-bin.005 -rw-rw1 mysqlmysql 244491 Aug 31 04:02 mw01-bin.004 -rw-rw1 mysqlmysql 111 Sep 7 04:02 mw01-bin.007 -rw-rw1 mysqlmysql 28177 Sep 7 04:02 mw01-bin.006 -rw-rw1 mysqlmysql7947 Sep 13 23:59 mw01-bin.008 -rw-rw1 mysqlmysql 111 Sep 14 04:02 mw01-bin.010 -rw-rw1 mysqlmysql3513 Sep 14 04:02 mw01-bin.009 -rw-rw1 mysqlmysql 111 Sep 21 04:02 mw01-bin.012 -rw-rw1 mysqlmysql30791885 Sep 21 04:02 mw01-bin.011 -rw-rw1 mysqlmysql 111 Sep 28 04:02 mw01-bin.014 -rw-rw1 mysqlmysql111270867 Sep 28 04:02 mw01-bin.013 -rw-rw1 mysqlmysql12105202 Sep 28 19:18 mw01-bin.015 -rw-rw1 mysqlmysql 111 Oct 5 04:02 mw01-bin.017 -rw-rw1 mysqlmysql38094517 Oct 5 04:02 mw01-bin.016 -rw-rw1 mysqlmysql 111 Oct 12 04:02 mw01-bin.019 -rw-rw1 mysqlmysql276605852 Oct 12 04:02 mw01-bin.018 -rw-rw1 mysqlmysql61917421 Oct 12 23:48 mw01-bin.020 -rw-rw1 mysqlmysql 111 Oct 19 04:02 mw01-bin.022 -rw-rw1 mysqlmysql101760652 Oct 19 04:02 mw01-bin.021 -rw-rw1 mysqlmysql 111 Oct 26 04:02 mw01-bin.024 -rw-rw1 mysqlmysql579578833 Oct 26 04:02 mw01-bin.023 -rw-rw1 mysqlmysql 479 Nov 2 04:02 mw01-bin.026 -rw-rw1 mysqlmysql844900359 Nov 2 04:02 mw01-bin.025 -rw-rw1 mysqlmysql 111 Nov 9 04:02 mw01-bin.028 -rw-rw1 mysqlmysql869670836 Nov 9 04:02 mw01-bin.027 drwx--2 mysqlmysql4096 Nov 10 21:15 Viper -rw-rw1 mysqlmysql 111 Nov 16 04:02 mw01-bin.030 -rw-rw1 mysqlmysql700865150 Nov 16 04:02 mw01-bin.029 -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.032 -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.031 -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.034 -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.033 -rw-rw1 mysqlmysql 86 Dec 7 01:23 mw01-bin.035 srwxrwxrwx1 mysqlmysql 0 Dec 7 01:25 mysql.sock -rw-rw1 mysqlmysql 570 Dec 7 01:30 mw01-bin.index -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.037 -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.036 drwxr-xr-x5 mysqlmysql4096 Dec 7 01:30 . -rw-rw1 mysqlmysql 63 Dec 7 01:49 master.info -rw-rw1 mysqlmysql861518654 Dec 8 17:53 mw01-bin.038 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New to joins - this simple one doesn't work.
.
I'm new to MySQL joins with PHP and I can't get this to work - and I don't
understand *why* it won't work.
Table 1 - Countries - has fields: country
Table 2 - Properties - has fields: area, reference_number
Table 3 - Images - has fields: image_filename, reference_number
The first page goes -
$Query="SELECT country FROM countries";
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array($Result))
{
print("$Row[country]");
}
The visitor, wanting to see an area, clicks on a particular country's link
and arrives in the country.php page, which contains -
$id = $HTTP_GET_VARS["country"];.
$Query="SELECT properties.area, images.image_filename FROM properties,
images WHERE properties.reference_number=images.reference_number ";
This works, but it delivers the images of every country. To call the images
from just one country, I try -
$Query="SELECT properties.area, images.image_filename FROM properties,
images WHERE properties.reference_number=images.reference_number AND
properties.area=$id";
but it doesn't work - I get the usual "...not a valid MySQL result resource"
I've played around with it, but I confess myself beat. I expect the
answer's terribly simple - but then, as everyone keeps telling me, so am I.
If you can help, thanks in advance.
Iain.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] > $Query="SELECT properties.area, images.image_filename FROM > properties, > images WHERE properties.reference_number=images.reference_number AND > properties.area=$id"; > > but it doesn't work - I get the usual "...not a valid MySQL > result resource" > > I've played around with it, but I confess myself beat. I expect the > answer's terribly simple - but then, as everyone keeps > telling me, so am I. > > If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query="SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'"; HTH! -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication/InnoDB errors
Greetings, I am having a difficult time understanding my replication error. I have two mysql installs both 3.23.54. I executed the following for my replication setup. On master: - stop mysql - add the following to my.cnf: [mysqld] log-bin server-id=1 - tar up data dir, copy to slave - start db - show master status; On slave: - stop mysql - add the following to my.cnf [mysqld] server-id=2 - untar data file - start db - connect, change master to. - slave start; At this point I get: 031208 18:10:24 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'xx-bin.001' at position 73 And it appears that its working, but if I attempt to use the db I get: Didn't find any fields in table 'blah' 031208 18:15:16 InnoDB error: Cannot find table db/blah from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. And if I try to select I get the same error as above plus: ERROR 1016: Can't open file: 'blah.InnoDB'. (errno: 1) But my master does not have any files name *.InnoDB in it's data dir, so why does the master believe the table is of the InnoDB type? Could someone please shed some light on what I have done wrong here. Thank you in advance, Andrew Hall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
. > $Query="SELECT properties.area, images.image_filename FROM > properties, > images WHERE properties.reference_number=images.reference_number AND > properties.area=$id"; > > but it doesn't work - I get the usual "...not a valid MySQL > result resource" > If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query="SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'"; HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for "Spain" and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Iain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
Hi Iain, Did you alter the tables since your original message? In the original message, you didn't have a "caption" column in the properties table. It might be easiest if you showed us your current table structures and partial data, like this: Countries +--+---+ | Field| Type | +--+---+ | id | integer | | country | varchar(20) | +--+---+ Partial data in Countries table +-+---+ | id| country | +-+---+ | 1 | United Kingdom| | 2 | Spain | | ... | | +-+---+ Properties ...etc... Also, I suggest you have an id (or reference_number) field in the countries table. I have found that if you don't, and you need to make a change to a country name, it can become a big mess. Bob -Original Message- From: fatblokeonbike [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 4:39 PM To: Mike Johnson Cc: [EMAIL PROTECTED] Subject: RE: New to joins - this simple one doesn't work. .. > > $Query="SELECT properties.area, images.image_filename FROM > > properties, > > images WHERE properties.reference_number=images.reference_number AND > > properties.area=$id"; > > > > but it doesn't work - I get the usual "...not a valid MySQL > > result resource" > > If you can help, thanks in advance. > >If $id is a string and not an int, then you need to single- or >double-quote it. > >Try this: > >$Query="SELECT properties.area, images.image_filename FROM properties, >images WHERE properties.reference_number=images.reference_number AND >properties.area='$id'"; > >HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for "Spain" and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Iain. -- 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]
mysqlhotcopy as a replication scheme
I've got an application that uses a fairly large (~50MM rows, ~1GB of disk) table of read-only data. The table changes maybe once a month, but when it changes, (almost) EVERY row in the table changes. The app needs to be replicated into several datacenters worldwide using relatively slow backend links. For this reason and others (I need to be able to control when each datacenter picks up updates, etc.) native MySQL replication isn't attractive. I'm considering building a scheme where I insert the data into a table once and ship around a gzipped mysqldump and load it into each datacenter -- this is easy, uses less bandwidth, is easy to control via cron and fits well into the rest of our infrastructure. Then I found mysqlhotcopy. Neato! I've tested, and this seems to work: 1) use mysqlhotcopy to copy the table on the "replication master" 2) gzip the table/index/data files and ship them someplace remote 3) (on the slave) unzip them 4) LOCK TABLES foo WRITE 5) FLUSH TABLE foo 6) copy the unzipped data files over the running mysql data files for the single table I'm intersted in. There's clearly a problem here if the machine crashes during this step, but it can be worked out to just 3 calls to rename(2), which is atomic on a POSIX fs, so that's less an issue than it could be. 7) FLUSH TABLE foo 8) profit! It looks like table foo now contains the new data. It takes a LOT less time than reinserting all the data into the table. Other than "you should really use mysql native replication", does anyone have any comments on whether this is likely to be reliable, or why it's a bad idea? I'm using 3.23.49 (Debian stable); Is FLUSH TABLE likely to change in future versions in a way that will break this? -- thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is mySQL right for me??
Hi All, I want to create a small database that allows a user to populate a report by choosing many different parameters whcih are linked to a variety of images and text entries. The front end would be HTML/ASP based and would feature several choices that the user makes to define what is included in the final report or form. The output would then be printed and forgotten about. The next user could then come along and create their own custom page from the options available. Is mySQL capable of creating such as system? I can't imagine it would be complicated, rather just a collection of queries that are then arranged on the output page according to preset positioning instructions. Regards Greg MAIL IS CONFIDENTIAL. If you have received this e-mail in error, please notify us by return e-mail and delete the document. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. Barwon Health is not liable for the proper and complete transmission of the information contained in this communication or for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-Table UPDATE Emulation
Hopefully someone can help me out with this - I have two tables in mySQL: comments and diary I want to populate a new field in comments with the current values of diary - but only if they meet certain criteria. Now, the problem is this - my webserver is using an older version of mySQL that doesn't support UPDATEs from multiple tables (that's not possible until v4.0.4) and unfortunately, them updating the install isn't a possibility. I know if it was v4.0.4+ I could juse use: UPDATE comments,diary SET comments.diary_date=diary.date WHERE comments.refid=diary.ID I also don't think I can use sub-selects in queries either. Btw - I'm currently running v3.23.58. Thanks for any help! -M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is mySQL right for me??
Hello Greg, I think mySQL would work great for this application. Using PHP would be another language to keep in mind when developing your project, but it seems like it should be well within the capablities of mySQL. Jeff Homan JCS Web Hosting http://www.jcswebhosting.com On Tue, 9 Dec 2003 14:21:25 +1100 , you wrote: >Hi All, > >I want to create a small database that allows a user to populate a report by >choosing many different parameters whcih are linked to a variety of images >and text entries. The front end would be HTML/ASP based and would feature >several choices that the user makes to define what is included in the final >report or form. The output would then be printed and forgotten about. The >next user could then come along and create their own custom page from the >options available. > >Is mySQL capable of creating such as system? I can't imagine it would be >complicated, rather just a collection of queries that are then arranged on >the output page according to preset positioning instructions. > >Regards >Greg >MAIL IS CONFIDENTIAL. If you have received this e-mail in error, >please notify us by return e-mail and delete the document. If you are not >the intended recipient you are hereby notified that any disclosure, copying, >distribution or taking any action in reliance on the contents of this >information is strictly prohibited and may be unlawful. Barwon Health is >not liable for the proper and complete transmission of the information >contained in this communication or for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting white spaces
Hello Lemasson.
Monday, December 8, 2003, 8:16:18 PM, you wrote:
LS> I have a table witch has a column of type VARCHAR(10)
LS> BINARY. When I tried to insert white spaces in it (insert into
LS> mytable (mycolumn) values('')), an empty String is stored. Why?
This is a documented behavior:
Values in VARCHAR columns are variable-length strings. You can declare
a VARCHAR column to be any length between 1 and 255, just as for CHAR
columns. However, in contrast to CHAR, VARCHAR values are stored using
only as many characters as are needed, plus one byte to record the
length. Values are not padded; instead, trailing spaces are removed
when values are stored. (This space removal differs from the SQL-99
specification.)
[taken from 6.2.3.1 The CHAR and VARCHAR Types of MySQL Reference Manual]
--
See you,
Andrey.
[ [EMAIL PROTECTED] | ICQ# 114087545 | 2:5090/[EMAIL PROTECTED] ]
...The best way to hold a man is in your arms.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In need of a dummy select statement ...
Thanks for the reply. It just have to be no-op and it need not necessarily be a select statement. Any one of the options given by you will work for me if they are executable from a program written using MyODBC. Thanks, Srinivas. Don Read wrote: On 08-Dec-2003 Srinivas B.S.S wrote: Hi, I am a newbie to mysql db. I need a select statement which will just execute and doesn't return any results. Also, it should not depend on any table which is specific to a database. For example, Oracle has a table named 'tab' which will be present in all databases so my dummy select statement looks like 'select tname from tab where 0=1'. Could this functionality be achieved in mysql ? Is there any table in mysql which is similar to Oracle's 'tab' ? Does it have to be a SELECT? Or just a NO-OP? SET @nothing=0; -or- DO 0; - or - SHOW TABLES LIKE 'asdfzxcv1234'; Regards,
RE: In need of a dummy select statement ...
You need not select a table in MySQL as you do in Oracle. Select 2 + 2; Is valid in MySQL and should return 4. It at least lets you know if MySQL is functional. By mysqladmin ping will do that too. Steve Cersosimo [EMAIL PROTECTED] Bellsouth Internet Services "When all is said and done, more is said than done" -Original Message- From: Srinivas B.S.S [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 1:22 AM To: Don Read Cc: [EMAIL PROTECTED] Subject: Re: In need of a dummy select statement ... Thanks for the reply. It just have to be no-op and it need not necessarily be a select statement. Any one of the options given by you will work for me if they are executable from a program written using MyODBC. Thanks, Srinivas. Don Read wrote: >On 08-Dec-2003 Srinivas B.S.S wrote: > > >>Hi, >> >>I am a newbie to mysql db. I need a select statement which will just >>execute and doesn't >>return any results. Also, it should not depend on any table which is >>specific to a database. >>For example, Oracle has a table named 'tab' which will be present in all >>databases so my >>dummy select statement looks like 'select tname from tab where 0=1'. >>Could this >>functionality be achieved in mysql ? Is there any table in mysql which >>is similar to Oracle's >>'tab' ? >> >> >> > >Does it have to be a SELECT? Or just a NO-OP? > >SET @nothing=0; > > -or- > >DO 0; > > - or - > >SHOW TABLES LIKE 'asdfzxcv1234'; > > >Regards, > > * "The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential, proprietary, and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all computers." 113 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql user
Hi all, Can any one tell me the command to list all mysql users ??? Thanks in advance Binay
