Re: Finding a point inside a polygon
Thanks, but according to the documentation the Contains() function is the same as the MBRContains() function which only tests if the point is inside the minimum bounding rectangle of the polygon, not the actual polygon. See the following: http://dev.mysql.com/doc/refman/5.0/en/functions-that-test-spatial-relationships-between-geometries.html From the manual: "Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions." On 7/21/06, ViSolve DB Team <[EMAIL PROTECTED]> wrote: Hello Mark, You can locate a point, whether inside or outside a polygon area using the query below: SELECT contains(geomfromtext(@poly),geomfromtext(@p)); where @poly,@p are variables. Eg: set @p='Point(3 2)'; set @poly= 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1)) '; If the return value of the select statement is 0 -> Outside the polygon 1 -> Inside the polygon Thanks, ViSolve MySQL Support Team. - Original Message - From: "Mark Maunder" <[EMAIL PROTECTED]> To: Sent: Friday, July 21, 2006 4:40 AM Subject: Finding a point inside a polygon > I'd like to test whether a point is truly inside a polygon, not just > insude the minimum bounding rectangle. Is there a way to do this in > MySQL with the spatial extensions? I love mysql but I'm forced to > consider migrating to postgresql (ugh!) because it has built in > support for testing spatial relationships between polygons and points. > > Thanks. > > Mark. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Mark Maunder <[EMAIL PROTECTED]> http://www.markmaunder.com/ +1-206-6978723 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a point inside a polygon
Hello Mark, You can locate a point, whether inside or outside a polygon area using the query below: SELECT contains(geomfromtext(@poly),geomfromtext(@p)); where @poly,@p are variables. Eg: set @p='Point(3 2)'; set @poly= 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1)) '; If the return value of the select statement is 0 -> Outside the polygon 1 -> Inside the polygon Thanks, ViSolve MySQL Support Team. - Original Message - From: "Mark Maunder" <[EMAIL PROTECTED]> To: Sent: Friday, July 21, 2006 4:40 AM Subject: Finding a point inside a polygon I'd like to test whether a point is truly inside a polygon, not just insude the minimum bounding rectangle. Is there a way to do this in MySQL with the spatial extensions? I love mysql but I'm forced to consider migrating to postgresql (ugh!) because it has built in support for testing spatial relationships between polygons and points. Thanks. Mark. -- 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: udf configuration resources
Your udf is executing in a threaded environment so you have the option of creating a datastrucutre to store options in that will persist across udf executions. However since you won't be deallocating this structure ever it's essentially leaked memory. What are you doing with udfs that you want to store configuration for? I have a feeling udfs are not he right solution. On 7/19/06, Yong Lee <[EMAIL PROTECTED]> wrote: Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong. -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT and Asian languages
Hi Peter,
Definitely using OR will slow up the performance of FULL TEXT searching.
Instead of using OR, you can try using UNION statement.
Hope this will be a fix for your issue.
Thanks,
ViSolve MySQL Support Team.
- Original Message -
From: "Peter Lauri" <[EMAIL PROTECTED]>
To: "'JC'" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, July 20, 2006 4:21 PM
Subject: RE: FULL TEXT and Asian languages
That is what I am doing right now, but it is not that fast if this system
would grow, and also it is not "ranking" the searches.
Right now I do something like this:
$searchwords = explode(" ", $searchstring);
foreach($searchwords AS $value) {
$Query.= "OR lajlaj LIKE '%$value%'"
}
If there are many search words, the OR will grow a bit, and OR are not
that
fast as I read somewhere.
/Peter
-Original Message-
From: JC [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 20, 2006 10:46 PM
To: Peter Lauri
Cc: mysql@lists.mysql.com
Subject: Re: FULL TEXT and Asian languages
don't know about indexing, but try to search: LIKE '%sentences%'
JC
On Thu, 20 Jul 2006, Peter Lauri wrote:
Best group member,
I have a problem. I was going to use FULL TEXT search for my Thai client.
It
is working smooth with English text and wordings, the indexing and search
works fine.
The problem with Thai text is that words are not separated with a white
space as in English and other languages. I think this screws up the
indexing, and complete sentences are classed as a word. Assume Thai
characters:
Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch.
I want to search for "sentence", but can not. How can this be done? And
will
the indexing ever work?
Best regards,
Peter Lauri
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.2/393 - Release Date: 7/19/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "CHARACTER SET COLLATE NULL" error with mySQL 4.0.27
Hello Thomas You have an error in your SQL syntax. Please try this : CREATE TABLE test.user ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(user_id), email text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci', firstname text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'); The error is due to the AUTO_INCREMENT column, which is not defined as PRIMARY KEY and the CHARACTER SET column, which is not syntatically defined. Also to list the MySQL support Character Set try: Show Character Set; Thanks ViSolve MySQL Support Team. - Original Message - From: "thomas Armstrong" <[EMAIL PROTECTED]> To: Sent: Thursday, July 20, 2006 2:41 PM Subject: "CHARACTER SET COLLATE NULL" error with mySQL 4.0.27 Hola. With mySQL 4.0.27 I'm trying to create this table -- CREATE TABLE `test`.`user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `email` TEXT CHARACTER SET COLLATE NULL , `firstname` TEXT CHARACTER SET COLLATE NOT NULL , ) -- but I get this error: -- #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET COLLATE NULL, `firstname` TEXT CHARACTER SET -- Does anybody know which the right way is for this mySQL version? I'm using mySQL-Workbench to design the tables, and that's the code I get? Thank you very much. -- 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: How to query on part of a date column?
At 06:20 PM 7/20/2006, Martin Jespersen wrote:
As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)
Correct. But how many people want to create more work for themselves? A
show of hands please!
Mike
mos wrote:
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious
tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a
SELECT, but can't seem to use it in a WHERE clause at all. There has to
be something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character "_".
You are only creating problems for yourself if you leave the blanks in
the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to grant replication slave/client to class c
I'm attempting to take a brand new mysql server build on gentoo and set up replication. I'm using GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@"192.168.1.0/255.255.255.0" IDENTIFIED BY 'secret'; I've tried various combinations of removing the quotes, tried not using a password, and various other things, yet when I SHOW GRANTS FOR repl; I get nothing. If I do the same, and use % for all hosts, it works, and it shows up in the grants. I tried to limit it to one IP, it wouldn't show up, tried %.domain.com and that wouldn't work either. The only way I could get anything at all to show up when SHOW GRANTS FOR repl; was performed is to use GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@"%" IDENTIFIED BY 'secret'; Now, what I'll eventually be setting up is a master-master replication (basically only for failover using keepalived), so if anyone has any experience with that, I'd be much appreciated. These are completely empty freshly built gentoo/mysql installs, so a "from the ground up" reply is appreciated. Thanks, Michael
Finding a point inside a polygon
I'd like to test whether a point is truly inside a polygon, not just insude the minimum bounding rectangle. Is there a way to do this in MySQL with the spatial extensions? I love mysql but I'm forced to consider migrating to postgresql (ugh!) because it has built in support for testing spatial relationships between polygons and points. Thanks. Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database back up
/var/lib/mysql/ is a common location. If you're on unix try the following command: find /var -name "mysql" On 7/20/06, Martin Jespersen <[EMAIL PROTECTED]> wrote: You can usually find the database files under the "var" subdirectory under your installation, unless another datadir was specified at compiletime. ;) Martin Joko Siswanto wrote: > Dear All > > if myqsl service can't start, where can i found the file and back up it? > [under windows and linux] > > Thanks, > Joko Siswanto > -- 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: How to query on part of a date column?
As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)
mos wrote:
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each
person has a registration date, kept in a standard date field. How do
I select for people who registered in a particular month or year? The
obvious tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a
SELECT, but can't seem to use it in a WHERE clause at all. There has
to be something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character "_".
You are only creating problems for yourself if you leave the blanks in
the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database back up
You can usually find the database files under the "var" subdirectory under your installation, unless another datadir was specified at compiletime. ;) Martin Joko Siswanto wrote: Dear All if myqsl service can't start, where can i found the file and back up it? [under windows and linux] Thanks, Joko Siswanto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
effects of aquiring exclusive lock on subsequent reads inside same TX?
Hi, I'm running into lost of DB deadlocks would really appreciate answers to the questions below which will help me diagnose the problem. I am running MySQL 5 InnoDB, SERIALIZABLE mode. Let's say we have: 1.TX1 START //assume autocommit is off 2.TX1 read on TableA 3.TX1 update on TableA 4.TX1 read on TableB 5.TX1 COMMIT Is the following correct? -In step2, TX1 obtains a SHARED lock. Is it applied to all rows in TableA or only those returned by select statement? -In step3, TX1 obtains an EXCLUSIVE lock. Does that lock out all of TableA or only those rows that are being updated? -In step4, does TX1 apply SHARED or EXCLUSIVE lock? is the lock applied to all of TableB or only to rows returned by the select statement? In general what is the effect (w/r/t locking) on selects performed after a write operation within the same TX? thanks a lot! -nikita -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: retrieve value from an external file?
At 02:48 PM 7/20/2006, you wrote: Hello everyone, Without scripting, can this be done from mysql> command line? The 'publications' field is mediumtext in table 'research'. Since 'publication' is pretty long, I want to save it in a file on the file system. But I don't know how, or if that's even possible, to grab the content from the file and use it in the following update statement? mysql>update research set publications='lng text in a file' where uid=319; Any ideas? Thanks in advance, Bing Bing, You could create a temporary table with a single text field and then use "Load Data Infile ..." to put the text data into the temporary table. Once it is there you can use the temporary table to update your research table. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: retrieve value from an external file?
On Thursday 20 July 2006 12:48 pm, Bing Du wrote: > Hello everyone, > mysql>update research set publications='lng text in a file' where > uid=319; You already have the data in a storage system ( a file on the filesystem ). That said, use what you have, store the location to the file, then grab that and use it to open the file and get the contents, using that for whatever purpose you may have. -- Chris White PHP Programmer/DBlarg Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
retrieve value from an external file?
Hello everyone, Without scripting, can this be done from mysql> command line? The 'publications' field is mediumtext in table 'research'. Since 'publication' is pretty long, I want to save it in a file on the file system. But I don't know how, or if that's even possible, to grab the content from the file and use it in the following update statement? mysql>update research set publications='lng text in a file' where uid=319; Any ideas? Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious
tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character "_". You
are only creating problems for yourself if you leave the blanks in the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to disable foreign_key_checks when using mysqlimport?
Try: ALTER TABLE `tbl_name` DISABLE KEYS; -- now insert in the TXT file ALTER TABLE `tbl_name` ENABLE KEYS; I think this is what you were looking for ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
Barry, I think you've got too many quotes in your SQL - the db is
trying to find the year from the string 'date paid'. You want to use
it as a column name, so drop the quotes:
Select * from Capclave2005reg
Where Year(Date Paid) = 2004;
If you've really got a space in your column name, try enclosing it in
backticks instead of quotes - ` instead of ', as in
Select * from Capclave2005reg
Where Year(`Date Paid`) = 2004;
Regards,
Dan
On 7/20/06, Barry Newton <[EMAIL PROTECTED]> wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
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]
How to query on part of a date column?
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database back up
Joko Siswanto wrote: Dear All if myqsl service can't start, where can i found the file and back up it? [under windows and linux] Thanks, Joko Siswanto What file are you looking for? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database back up
Dear All if myqsl service can't start, where can i found the file and back up it? [under windows and linux] Thanks, Joko Siswanto
RE: FULL TEXT and Asian languages
Why can the Thai and Chinese not use regular sentences and word delimiter :) So I have to stick to my LIKE thing, just to erase the FULL TEXT index I assume. -Original Message- From: Neculai Macarie [mailto:[EMAIL PROTECTED] Sent: Thursday, July 20, 2006 10:50 PM To: mysql@lists.mysql.com Subject: Re: FULL TEXT and Asian languages Peter Lauri wrote: > Best group member, > > I have a problem. I was going to use FULL TEXT search for my Thai > client. It is working smooth with English text and wordings, the > indexing and search works fine. "The FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example, ' ' (space), ',' (comma), and '.' (period). If words are not separated by delimiters (as in, for example, Chinese), the FULLTEXT parser cannot determine where a word begins or ends. To be able to add words or other indexed terms in such languages to a FULLTEXT index, you must preprocess them so that they are separated by some arbitrary delimiter such as '"'." http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -- -- 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: FULL TEXT and Asian languages
That is what I am doing right now, but it is not that fast if this system
would grow, and also it is not "ranking" the searches.
Right now I do something like this:
$searchwords = explode(" ", $searchstring);
foreach($searchwords AS $value) {
$Query.= "OR lajlaj LIKE '%$value%'"
}
If there are many search words, the OR will grow a bit, and OR are not that
fast as I read somewhere.
/Peter
-Original Message-
From: JC [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 20, 2006 10:46 PM
To: Peter Lauri
Cc: mysql@lists.mysql.com
Subject: Re: FULL TEXT and Asian languages
don't know about indexing, but try to search: LIKE '%sentences%'
JC
On Thu, 20 Jul 2006, Peter Lauri wrote:
> Best group member,
>
> I have a problem. I was going to use FULL TEXT search for my Thai client.
It
> is working smooth with English text and wordings, the indexing and search
> works fine.
>
> The problem with Thai text is that words are not separated with a white
> space as in English and other languages. I think this screws up the
> indexing, and complete sentences are classed as a word. Assume Thai
> characters:
>
> Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch.
>
> I want to search for "sentence", but can not. How can this be done? And
will
> the indexing ever work?
>
> Best regards,
>
> Peter Lauri
>
>
>
>
>
>
>
>
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT and Asian languages
Peter Lauri wrote: > Best group member, > > I have a problem. I was going to use FULL TEXT search for my Thai > client. It is working smooth with English text and wordings, the > indexing and search works fine. "The FULLTEXT parser determines where words start and end by looking for certain delimiter characters; for example, ' ' (space), ',' (comma), and '.' (period). If words are not separated by delimiters (as in, for example, Chinese), the FULLTEXT parser cannot determine where a word begins or ends. To be able to add words or other indexed terms in such languages to a FULLTEXT index, you must preprocess them so that they are separated by some arbitrary delimiter such as '"'." http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL TEXT and Asian languages
don't know about indexing, but try to search: LIKE '%sentences%' JC On Thu, 20 Jul 2006, Peter Lauri wrote: > Best group member, > > I have a problem. I was going to use FULL TEXT search for my Thai client. It > is working smooth with English text and wordings, the indexing and search > works fine. > > The problem with Thai text is that words are not separated with a white > space as in English and other languages. I think this screws up the > indexing, and complete sentences are classed as a word. Assume Thai > characters: > > Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. > > I want to search for "sentence", but can not. How can this be done? And will > the indexing ever work? > > Best regards, > > Peter Lauri > > > > > > > > -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL TEXT and Asian languages
Best group member, I have a problem. I was going to use FULL TEXT search for my Thai client. It is working smooth with English text and wordings, the indexing and search works fine. The problem with Thai text is that words are not separated with a white space as in English and other languages. I think this screws up the indexing, and complete sentences are classed as a word. Assume Thai characters: Thisisasentenceinthai. ButIcannotsearchforsentenceinthatsearch. I want to search for "sentence", but can not. How can this be done? And will the indexing ever work? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Images Table
On Thursday 20 July 2006 07:19 am, Nicholas Vettese wrote: > img_idINTNOT NULL auto_increment PRIMARY KEY, > user_id INTNOT NULL, > location VARCHAR(255) NOT NULL, > imgtype VARCHAR(20) NOT NULL, > img_tag VARCHAR(50) NOT NULL, > img_rate VARCHAR(20) NOT NULL, good > img_commentsVARCHAR(50) NOT NULL, bzzt, bad! Note here comment(s). You have a 1 to many relationship, as one image can have many comments. What you need to do is have a separate table for that: CREATE TABLE image_comments ( id SERIAL PRIMARY KEY, comment TEXT NOT NULL, image_id INT NOT NULL, CONSTRAINT `foreign_images_comments` FOREIGN KEY `foreign_images_comments` (`image_id`) REFERENCES `[image-table]` `img_id` ON DELETE CASCADE ); then: SELECT comment FROM image_comments WHERE image_id = [image_id_here]; Why? Think about how you'd need to add comments. You'd have 2 choices: 1) Shove all the comments in one column (get ready for a HUGE table) 2) Create a separate image row for each comment (lots of duplicate data, bad!) > KEY (img_id); Not needed, you already declared it a key above (PRIMARY KEY) > Thanks for any help. > > nick -- Chris White PHP Programmer/DBoo Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access violation in QueryBrowser
Ok, MysqlCC is deprecated. I do not like this decision but... it is the thruth... I'm trying MysqlBrowser. It start connection with my server but it writes in the status bar: Access violation at address 005BC7B4 in module 'MySQLQueryBrowser.exe' Readof address 0 What does it means? It takes 98% of CPU time!! Thanks, Vittorio PS: WinXP Professional ans also server runs on the same PC on version 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to disable foreign_key_checks when using mysqlimport?
On Thursday 20 July 2006 04:10 am, Dominik Klein wrote: > Hello > > When I re-insert dumped data with "mysql < file.sql", I can simply put > "set foreign_key_checks=0;" at the beginning of the file and this works > fine. So do it that way > So if there's any other well-known solution for per-table dumpfiles, let > me know. I'm not too keen on writing something myself right now. mysqldump -u [user] -h [host] -p [database] [table] > fixme.sql mysql -u [user] -h [host] -p [database] < fixme.sql and for the paranoid, you can always use the ever efficient test database to test things first: mysql -u [user] -h [host] -p test < fixme.sql > Thanks for your help. > Dominik -- Chris White PHP Programmer/DBlankRounds Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MysqlCC
Use "old-passwords" on the server or upgrade your client library. Regards Dominik [localhost] ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client You have something similar about mysql client last week but what about MysqlCC? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Images Table
I am looking to create a database that holds the location of images on my server. I was wondering if anyone could help me understand if I am doing it correctly, and if I either need to add, change or remove something from the current table code. img_idINTNOT NULL auto_increment PRIMARY KEY, user_id INTNOT NULL, location VARCHAR(255) NOT NULL, imgtype VARCHAR(20) NOT NULL, img_tag VARCHAR(50) NOT NULL, img_rate VARCHAR(20) NOT NULL, img_commentsVARCHAR(50) NOT NULL, KEY (img_id); Thanks for any help. nick
Invalid syntax with STD() function when more than one field is used in select query
Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1" The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MysqlCC
Hi, i used MysqlCC for a long time with mysql 4.0 on my remote server... Now i've installed mysql 5.0 on my PC and i'd like to use MysqlCC but it doesn't work. It responds: [localhost] ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client You have something similar about mysql client last week but what about MysqlCC? Thanks for your answers... Vittorio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.sock deleted
On 20 Jul 2006 at 16:56, Vitaliy Okulov wrote: > , Deniss. > > 20 2006 ., 15:59:10: > > > Hi All > > > While I am doing portupgrade -vr mysql on freebsd server > > > than mysql socket"/tmp/mysql.sock" is invisible on, so mysql cant be start > > > How I can create mysql socket. > > How I can start mysql > > > thanks > > MySQL server will create this socket after start. Hi, MySQL will create the socket when it starts but it may not be in the /tmp folder. I have found that some programs expect the socket to be in /tmp whilst others expect it to be in the mysql directory. I find best solution to this problem is to start mysql, then create a symbolic link in the /tmp directory to the socket: ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock This works on my systems, you may have to adjust to your own environment. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.sock deleted
Здравствуйте, Deniss. Вы писали 20 июля 2006 г., 15:59:10: > Hi All > While I am doing portupgrade -vr mysql on freebsd server > than mysql socket"/tmp/mysql.sock" is invisible on, so mysql cant be start > How I can create mysql socket. > How I can start mysql > thanks MySQL server will create this socket after start. -- С уважением, Vitaliy mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql.sock deleted
Hi All While I am doing portupgrade -vr mysql on freebsd server than mysql socket"/tmp/mysql.sock" is invisible on, so mysql cant be start How I can create mysql socket. How I can start mysql thanks
replication from InnoDB to MyISAM
anyone know if there's any known issue with replication from InnoDB tables to MyISAM tables? I just switched a slave (mysql) to replicate from a different master that uses InnoDB and now I'm seeing weird problems on the slave. Table corruption, apps that can't connect etc. thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to disable foreign_key_checks when using mysqlimport?
Hello When I re-insert dumped data with "mysql < file.sql", I can simply put "set foreign_key_checks=0;" at the beginning of the file and this works fine. How can I achieve this when inserting a text file that is read with mysqlimport? I tried to put the mentioned sql-statement in the txt-file, but this does not affect anything. Background: One of my developers accidently dropped a table yesterday which had to be re-created from a dump. I was sort of lucky as the table was in a not too large database, so I could just open the dumpfile of that database and get the lines I needed to recreate the table and data. If I imagine this happened on a larger database which results in several-GB-size dumpfiles, it would have taken MUCH longer to get that table back. So now I'm playing with "mysqldump --tab" which gives nice per-table data and structure files. So if there's any other well-known solution for per-table dumpfiles, let me know. I'm not too keen on writing something myself right now. Thanks for your help. Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with synchronisation master -> slave
Hi Thomas I try to configure replication from master to one slave for a database with various table types (InnoDB, MyISAM) without stopping the master. Therefore I make a mysqldump on the slave from master with this options: mysqldump -h master-db \ -udummyuser \ -pdummypass \ -v \ --all-databases \ --disable-keys \ --quick \ --single-transaction \ Single-transaction only affects innodb tables. As single-transaction sets --skip-lock-tables automatically, MyISAM-tables are not locked during the dump. I think this is why you get the "row exists already" problem. I'd suggest dumping innodb data and myisam data separatly. One mysqldump WITH single-transaction (for innodb) and one without. This leaves the problem of data being inserted in the meantime, as master position will vary. No idea at hand right now :( --master-data \ > backup.file an read the dump to the slave with: mysql \ -u dummyuser \ -pdummypass \ < backup.file When I take a look to the backup.file, I see a line like this: CHANGE MASTER TO MASTER_LOG_FILE='webdb1-bin.000170', MASTER_LOG_POS=151635461; caused by the option "--master-data". But when I start the slave, there are soon the error message, that the replication process will insert new row to a table, where this row exists already. Next try was, to delete all data in slave and then start replication from the master from the beginning on (master-bin.01), because we haven't deleted any binlogs on master. After some time there appears an error message, that there was an unsuccessful insert to an table, which doesn't exist. Also the schema/catalog for this new table doesn't exist. The question is now, why the creation of the new schema/catalog and the table wasn't logged in the binlogs, so that they are not created via replication before some inserts/updates are processed on them. Maybe someone disabled log-bin temporarily when creating the db/table (for tests or whatever) and forgot about this when inserting data later. Are the binlogs not consistent? By the way, the "problem tables" are of type MyISAM. "Problem tables" = binlogged, but non-existent tables? or "Problem tables" = "row exists problem"-tables? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"CHARACTER SET COLLATE NULL" error with mySQL 4.0.27
Hola. With mySQL 4.0.27 I'm trying to create this table -- CREATE TABLE `test`.`user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `email` TEXT CHARACTER SET COLLATE NULL , `firstname` TEXT CHARACTER SET COLLATE NOT NULL , ) -- but I get this error: -- #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET COLLATE NULL, `firstname` TEXT CHARACTER SET -- Does anybody know which the right way is for this mySQL version? I'm using mySQL-Workbench to design the tables, and that's the code I get? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld-nt 100% CPU Utilization?
Can you do a "show processlist" from the MySQL client? This might help you to figure out if it is a specific query that's gumming up the works. Douglas Sims [EMAIL PROTECTED] On Jul 19, 2006, at 6:35 PM, Robinson, Eric wrote: Our MySQL-based medical application has been running fine for 3 years. Last week, mysqld-nt started jumping up to 100% CPU and staying there until someone restarts the service. mysqlcheck found no errors in the database. I dumped it to disk and read it back in to create a fresh copy of the database, but it is still happening. Any ideas? -- Eric Robinson Disclaimer - July 19, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- 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: mysqld-nt 100% CPU Utilization?
Robinson, Eric wrote: Our MySQL-based medical application has been running fine for 3 years. Last week, mysqld-nt started jumping up to 100% CPU and staying there until someone restarts the service. mysqlcheck found no errors in the database. I dumped it to disk and read it back in to create a fresh copy of the database, but it is still happening. Any ideas? Do you have query logging enabled? Anything unusual showing up in there? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
