Re: mysqld not starting
Matt Brei [EMAIL PROTECTED] wrote: Last night I upgraded from 3.23 to 4.0.20 and when I try to start mysqld it fails. The only entries I have in my error log are: 040620 23:44:19 mysqld started 040620 23:44:19 mysqld ended I know this isnt' enough info to troubleshoot the problem. How can I set the log level to give me more detail so I can figure out why the service keeps dying? Any help would be greatly appreciated. Check error log file for error message. -- 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]
Merge tables and Fulltext?
Hello, Is there any plan to support fulltext indexes in Merge Tables? Thank You. Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-connect is working very slow
Hello, this weekend, one of our caching nameservers failed. Since then, all mysql-connects were running very slow. I removed the failing nameserver in /etc/resolv.conf. Later, I was setting up another caching nameserver instead and inserted it in /etc/resolv.conf. I rebooted the mysql-machine. Nothing helped so far. What does mysql take so long to connect? I guess it's some sort of lookup, but I don't know what in detail. Since there's no failing nameserver anymore, I don't understand, why it is still taking so long. Is it performing ident lookups also? How can I check which nameserver mysql is using? I also tried --skip-name-resolve in the mysqld-startup, but this also didn't help. But connecting from localhost is fast as always. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-connect is working very slow (2)
Hello, regarding --skip-name-resolve: I read, that values in the Host-column of the mysql-authorization must be localhost or ip-addresses to work. Since everyone can connect from everywhere, I used 'localhost' and '%'. Is % treated the same? Is the ip-address-rule obligatory or just a should-have? Is --skip-name-resolve treated as if wouldn't be there if the values in Host aren't of type ip-address? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text search problem
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I guess
the problem is that MySql sees May May as being a date or something
and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search problem
Pieter,
I think FTS minimum WORD size is 4 characters - you may to be searching
with 3 on 'May May'.
Not having ever used FTS; I believe you can adjust it to count 3-character
words by changing the configuration, but I'm not sure where - and it would
then need re-indexing, if I'm not mistaken.
Hope that helps
Terry
--Original Message-
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I
guess the problem is that MySql sees May May as being a date or
something and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UTF-8 With MySQL 4.0.18 and PHP 2.5.5
MySQL List, At my request, my virtual hosting service has upgraded to MySQL 4.0.18 so that I can have better UTF-8 character support. Full Unicode support is important to me as I do bilingual Japanese/English sites. My understanding is that in 4.0.18 I can specify that a TEXT field is specifically in UTF-8, or other encodings if I desire. However, usually I use phpMyAdmin to manage my database, and in the version that I'm running - 2.5.5, it doesn't seem to have any special selectors or anything to indicate the character encoding of any one field or column. My question is, do I have to step outside of phpMyAdmin to ensure that the correct encoding is being used? If I do so, will that affect future database management from within phpMyAdmin? Any advice would be much appreciated. -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
disconnecting from server
Hi, I am using the command ./mysqld_safe --user=mysql to connect to the server but how does one disconnect? Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance splitting data
I have to deal with pretty big data, such a table (say BIG_TABLE) with 16.000.000 of rows. The table has just one primary index (a simple int). I thought that splitting data among multiple tables would have boosted performance, so I put my data in 16 different tables (everything is on one disk). The first table had IDs from 1 to 1.500.000, the second the range 151-300 and so on. When I made performance tests, I found that I didn't get any performance gain. So I tried (just for test purpose) to create a table of 10 rows (say SMALL_TABLE) with some data (IDs from 1.000.000 to 1.100.000) from my big table BIG_TABLE. Tests were like follows: (pseudocode) for 10 times read a column from BIG_TABLE where ID=random number between 1.000.000 and 1.100.000 for 10 times read a column from SMALL_TABLE where ID=random number between 1.000.000 and 1.100.000 The results are the same! There is no noticeable difference between a table with 16M rows and a table with 100K rows! Is that because my IDs are simple int from 1 to n (without gaps)? I've tried to do 10 inserts in both tables, and BIG_TABLE takes 45 seconds while SMALL_TABLE takes 43 secs (not such a big difference for a table 160 times smaller!) The only big performance gain I got is from a SUM on a big range, because MySql in this case does a full scan on the SMALL_TABLE which is faster than an access by index on the BIG_TABLE. Am I doing something wrong? Isn't splitting supposed to improve performance? Or it is true only if you can split data on multiple disks? Thank you Leonardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search problem
Hi Pieter,
That's because may is a stopword in MySQL's full-text indexing, by
default (like can, the, etc). You can define your own stopword file
with the ft_stopword_file variable. And you can find the default, built-in
list of stopwords in the file myisam/ft_static.c of the source
distribution.
Hope that helps.
(Oh, also what Terry said in his reply!)
Matt
- Original Message -
From: Pieter Botha
Sent: Monday, June 21, 2004 3:54 AM
Subject: Full text search problem
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I guess
the problem is that MySql sees May May as being a date or something
and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Professional certification
Hi Brian, On Fri, 2004-06-18 at 19:48, Brian Mansell wrote: I took the certification exam this morning and passed. Congratulations! When should I expect to receive the certificate (and other items) in the mail? It usually takes 4-6 weeks for the certificate to arrive at your address. Best regards, / Carsten -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Office: +45 56 36 16 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: disconnecting from server
exit; ? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 21, 2004 10:12 AM Subject: disconnecting from server Hi, I am using the command ./mysqld_safe --user=mysql to connect to the server but how does one disconnect? Thanks in advance -- 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: disconnecting from server
[EMAIL PROTECTED] wrote: I am using the command ./mysqld_safe --user=3Dmysql to connect to the= server but how does one disconnect? With the above command you start the MySQL server, not connect. To stop MySQL server use command: shell mysqladmin shutdown http://dev.mysql.com/doc/mysql/en/mysqladmin.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]
Distinct records based on one field
I got a little problem. I want to show distinct results based on only one field of the record, field1. Here's an example. I want to show records from Table1 and the number in field1 must not show more than once. If my table is this: Table1 field1 field2 field3 field4 5 Five Five One 4 Four Four Bla 5 Five Five Blah 3 Three Three Bluh 5 Five Five Bla bla 6 Six Six Bl.a 8 Eight Eight B.La 9 Nine Nine Lba 3 Three Three LAb I want to output this: Output field1 field2 field3 field4 5 Five Five Bla bla 6 Six Six Bl.a 8 Eight Eight B.La 9 Nine Nine Lba 3 Three Three LAb Is there a way to do that? without making another table everytime? I mean, by an sql command? Thanks in advance. PhistucK
RE: Full text search problem
Good morning. Not knowing too much about PHP it looks like you are
searching for
`name`,`colour`,`gender`,`breed`,`location`,`description`
Where there is a whitespace in the name.
Could you use,
$query_results = sprintf(SELECT * FROM dogslost WHERE
`name` LIKE '% %', $crit_results);
God Bless
Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.
GOD BLESS AMERICA!
To God Be The Glory!
-Original Message-
From: Terry Riley [mailto:[EMAIL PROTECTED]
Sent: Monday, June 21, 2004 5:08 AM
To: [EMAIL PROTECTED]
Subject: Re: Full text search problem
Pieter,
I think FTS minimum WORD size is 4 characters - you may to be searching
with 3 on 'May May'.
Not having ever used FTS; I believe you can adjust it to count 3-character
words by changing the configuration, but I'm not sure where - and it would
then need re-indexing, if I'm not mistaken.
Hope that helps
Terry
--Original Message-
Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called May May which doesnt show up in the
search results. A dog called Doggy Doggy does show up however. I
guess the problem is that MySql sees May May as being a date or
something and doesnt do a text compare.
Here is my query, from php.
$query_results = sprintf(SELECT * FROM dogslost WHERE MATCH
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
('%s' IN BOOLEAN MODE), $crit_results);
any ideas?
Regards
Pieter
--
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]
database size
Hello. I have quite big database - around 15Gb(~ 180 million of rows). My problem is that after deleting 70million of rows(with 'delete from' statement) database file size didn't decrease - it's still 15Gb. Can anybody give me advice, what i'm doing wrong, why db size is the same after deleting around 1/3 of all rows. Thanks in advance Best regards, Ruslan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
failure to connect using a c program
Hi,
I am trying to execute a c program which extracts data from a mysql database.
I have installed mysql and created the desired database called one
The program uses the following if statment to try to connect to the database
and flag if connection is not successful:
---
if (!mysql_real_connect(mysql,localhost,root,XXX,one,0,NULL,0))
{
printf(\nError connecting to database\n\n);
printf(Error: %s\n\n,mysql_error(mysql));
exit(1);
}
where root is the username and XXX denotes the place wher I filled in my
password. The username and password used are the same as ones I use to successfully
connect to the database by starting up the client.
However, when I try to execute the code the program terminates at this point
and prints out the following:
Error connecting to database
Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.
sock' (2)
I understand that it is very difficult to spot the problem without viewing
the entire code but can anyone hazard a guess to why this is happening?
Does the c program need to be in any specific mysql directory?
note: if I try to connect to the server from the command line, while the
MySQL server is not started, using ./mysql -u root -pXXX it will return
the error:
Can't connect to local MySQL server through socket '/tmp/mysql.sock'
Does the fact that the location of mysql.sock is different in each case?
Thanks in advance,
Cathal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database size
Ruslan Spivak [EMAIL PROTECTED] wrote on 21/06/2004 12:22:04: I have quite big database - around 15Gb(~ 180 million of rows). My problem is that after deleting 70million of rows(with 'delete from' statement) database file size didn't decrease - it's still 15Gb. Can anybody give me advice, what i'm doing wrong, why db size is the same after deleting around 1/3 of all rows. Thanks in advance I am assuming you are using MyISAM table type. MySQL does not normally compress the files when deleting records because that would mean shuffling up all the data in the file, which would be a very slow operation. Instead, it leaves holes which are filled in when new records are added. If you want to reclaim the space, you must OPTIMIZE the tables, which rebuilds them in a new file and discards the old. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Distinct Records Based On One Field - fixed
I'm posting it again coz I did it all wrong. so here it is again: I got a little problem. I want to show distinct results based on only one field of the record, field1. Here's an example. I want to show records from Table1 Table2 and each number in field1\tfield1 must be output only once (unique), the last record inserted with that number. If my tables are: Table1: field1 field2 field3 1 5Five Five 2 4Four Four 3 3Three Three 4 6Six Six 5 8Eight Eight 6 9Nine Nine Table2: tfield1 tfield2 1 5One 2 4Bla 3 5Blah 4 3Bluh 5 5Bla bla 5 6Bl.a 7 8B.La 8 9Lba 9 3LAb I want to output this: Output field1 field2 field3 tfield2 1 4Four Four Bla 2 5FiveFive Bla bla 3 6Six Six Bl.a 4 8Eight Eight B.La 5 9Nine Nine Lba 6 3Three Three LAb Is there a way to do that? without making another table everytime I insert records to the second? I mean, by an sql command? Thanks in advance. PhistucK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fastest way to load a master table removing duplicates - Not Answered
Paul, These opinions reflect my own experience, your mileage may vary. I the fastest way would depend on your source data. For most data dumps I use LOAD DATA INFILE as it will read both delimited and fixed width text. When applied to a table with NO indexes (indices?) on it I will get the best results translating data files into a MySQL database. Now, how to detect dupes and not break your primary keys. There are several ways of checking the integrity of your source data. you ask: If inserted records have a duplicate SSN I don't want to insert those but put them in a duplicate table or flag them and Insert the row and ignore duplicate using insert into master ( .) Those are similar in what you are trying to do. Let's look at flagging or re-logging duplicate source rows first. It would be very useful to apply some artificial uniqueness constraint to your random source data unless you are absolutely certain that you can identify a primary key. I typically do this (when I need to) by importing the data into a table with an auto_incrementing ID field. That way I can easily identify each and every row in my source data. Using this field it is rather simple to collect which rows duplicate themselved in the columns you need to have unique. SELECT column list FROM import_table GROUP BY column list having count(1) 1 column list represents the list of columns in which you do not want data duplicated. To copy those to another table takes two steps:CREATE a list of your duplicated primary key columns, then INSERT the duplicated rows into the other table CREATE TEMPORARY TABLE tmpDupes SELECT column list FROM import_table GROUP BY column list having count(1) 1 INSERT duplicated_table (destination column list) SELECT source column list FROM import_table INNER JOIN tmpDupes ON ... AND ... AND ... one comparison per column in tmpDupes against the same column in import_table DROP TABLE tmpDupes To merge your imported data with your existing data in such a way as to NOT break your existing primary keys, you can use the INSERT with the IGNORE keyword. I will sometimes add keys to my import table (after it is loaded) to speed up this processing. I drop them again before the next batch import. To flag your import data, you will have to ALTER TABLE ... ADD a new column to it then run an update query. I suggest you create a temp table for your duplicate keys (like I did above) so that you can JOIN that list back into your import data to quickly update the new flag field for only those rows that are duplicated. I am sorry I could not be more specific but you did ask a VERY generic question. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Paul Chu [EMAIL PROTECTED]To: [EMAIL PROTECTED] net cc: Fax to: 06/18/2004 10:02 Subject: RE: Fastest way to load a master table removing duplicates - PMNot Answered Appreciate any help at all Thanks, Paul -Original Message- From: Paul Chu [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 10:16 AM To: [EMAIL PROTECTED] Subject: Fastest way to load a master table removing duplicates Hi all, I want to load a Master table millions of rows for other sources with a unique index on e.g. ssn social sec number. If inserted records have a duplicate SSN I don't want to insert those but put them in a duplicate table or flag them. . The primary key will be an auto-increment field. There will be other indexes such as zipcode.. What is the fastest way to load these rows and remove duplicates ? Assume I load the rows to be inserted into another table. 1. Check if the ssn already exists before inserting the row ? 2. Insert the row and ignore duplicate using insert into master ( .) select .. From loaddata I have lots of files with data that can be saved to load tables and then inserted into the master table. Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Full Text Index on Large Tables - Not Answered
Why are you putting a list of all skills into one field? That's not a very relational method of storing your data and you will have a much harder time trying to extract skills from a text list of fields than if you used a very standard database practice called normalization Personally I would create 3 tables: one to contain a list of all people, one that contains a list of all skills, and a third that will contain an entry for each skill a person has (using either the primary key or a unique key from both tables) CREATE TABLE Person ( ID int auto_increment primary key, Name varchar(20), other columns KEY (name) ) CREATE TABLE Skill ( ID int auto_increment primary key, SkillName varchar(50) KEY (SkillName) ) CREATE TABLE PERSON_SKILL ( Person_ID int, Skill_ID PRIMARY KEY(Person_ID, Skill_ID) ) The primary key on the PERSON_SKILL table prevents the same person from being assigned the same skill more than once. Now, you can use text and numerical indices and not full-text indices and table scans to find your skills list. This is MUCH faster!!! To get a list of all skills for a person: SELECT p.Name, s.SkillName FROM Person p INNER JOIN PERSON_SKILL ps ON ps.Person_ID = p.ID INNER JOIN Skill s ON s.ID = ps.SkillID WHERE p.Name = 'somename here' To get a list of people with a skill: SELECT p.Name, s.SkillName FROM Person p INNER JOIN PERSON_SKILL ps ON ps.Person_ID = p.ID INNER JOIN Skill s ON s.ID = ps.SkillID WHERE s.SkillName = 'some skill here' Regards, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Paul Chu [EMAIL PROTECTED]To: 'Paul Chu' [EMAIL PROTECTED], [EMAIL PROTECTED] net cc: Fax to: 06/18/2004 10:02 Subject: RE: Full Text Index on Large Tables - Not Answered PM Appreciate any help at all Thanks, Paul -Original Message- From: Paul Chu [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 10:16 AM To: [EMAIL PROTECTED] Subject: Full Text Index on Large Tables Hi, If I have a table with 100 - 200 million rows and I want to search For records with specific characteristics. Ex. Skills varchar(300) Skill id's 10 15 Accounting finance etc. Is it advisable to created a field with skill ids and then use the Skills column in a full text index Thanks for your help, Paul -- 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: Returning where COUNT 5
With a join and group by I think you have HAVING. Hi all how to do this in MySQL? Returning only records with COUNT 5? SELECT `groups`.`groupsDescr`, `roles`.`roles_Agroup`, `roles`.`rolesDescr`, COUNT(`roles`.`rolesDescr`) AS TOTAL FROM `roles` INNER JOIN `groups_roles` ON (`roles`.`rolesID` = `groups_roles`.`fkrolesID`) INNER JOIN `groups` ON (`groups_roles`.`fkgroupsID` = `groups`.`groupsID`) GROUP BY `roles`.`rolesDescr` WHERE COUNT(`roles`.`rolesDescr`) GT 5 MySQL 4.0.12 give an error... maybe because i'm using WHERE COUNT? Thanx for your time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't drop database
Hi I am running Suse Linux 8.2 and MySQL 3.23.55 A funny thing happens: % mysqladmin drop base Database base dropped % mysqladmin create base Can't create database 'base'. Database exists. And if I log in to MySQL, there it is - undropped! So why is mysqladmin telling me the database is dropped when it isn't? Thanks in advance! Mick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to randomnly select just 1 record from the table?
Hi all I need to select randomnly just one record from the table. how can i do? please help me out. Thanks in advance binay
Re: Distinct Records Based On One Field - fixed
I think you still did it wrong, assuming the first column is IDs, but no matter. How do you determine which row from Table2 is the last record inserted with that number.? Michael PhistucK wrote: I'm posting it again coz I did it all wrong. so here it is again: I got a little problem. I want to show distinct results based on only one field of the record, field1. Here's an example. I want to show records from Table1 Table2 and each number in field1\tfield1 must be output only once (unique), the last record inserted with that number. If my tables are: Table1: field1 field2 field3 1 5Five Five 2 4Four Four 3 3Three Three 4 6Six Six 5 8Eight Eight 6 9Nine Nine Table2: tfield1 tfield2 1 5One 2 4Bla 3 5Blah 4 3Bluh 5 5Bla bla 5 6Bl.a 7 8B.La 8 9Lba 9 3LAb I want to output this: Output field1 field2 field3 tfield2 1 4Four Four Bla 2 5FiveFive Bla bla 3 6Six Six Bl.a 4 8Eight Eight B.La 5 9Nine Nine Lba 6 3Three Three LAb Is there a way to do that? without making another table everytime I insert records to the second? I mean, by an sql command? Thanks in advance. PhistucK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to randomnly select just 1 record from the table?
Binay [EMAIL PROTECTED] wrote on 21/06/2004 15:03:34: I need to select randomnly just one record from the table. how can i do? please help me out. SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1 ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't drop database
When you DROP DATABASE 'base', mysql drops all its tables then drops the db. At the filesystem, this means deleting all the table files from the 'base' subdirectory of mysql's data directory, then deleting the 'base directory itself. If 'base' contains a file that isn't a table file, however, that file won't be deleted by mysql, which causes the attempt to delete the directory to fail. Since directory 'base' still exists, mysql sees it as db 'base' still exists. So check in 'base' for any leftover files and either delete them or move them somewhere else, then try again. Michael michael watson (IAH-C) wrote: Hi I am running Suse Linux 8.2 and MySQL 3.23.55 A funny thing happens: % mysqladmin drop base Database base dropped % mysqladmin create base Can't create database 'base'. Database exists. And if I log in to MySQL, there it is - undropped! So why is mysqladmin telling me the database is dropped when it isn't? Thanks in advance! Mick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to randomnly select just 1 record from the table?
well i have only 4 records in the table. When i use below mentioned query it always return the same record irrespective of time delay. Now wht can i do? Thanks Binay Binay [EMAIL PROTECTED] wrote on 21/06/2004 15:03:34: I need to select randomnly just one record from the table. how can i do? please help me out. SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1 ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL backup not backing up all tables
I am having trouble with a nightly backup of our MySQL database. Using the mysqldump command, we dump our entire database to a backup directory. But consistently the backup file contains only 33 of the 88 tables in the database. The 33 getting backed up are the first 33 of the 88 as sorted alphabetically by table name. Here are the relevant lines in the backup script: mysqldump --opt ACA /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.all.ACA mysqldump mysql /home/sites/home/users/admin/dump/ACA/`date --date '1 day ago' +%m%d`.mysql I'm a newbie with MySQL, so I don't know what other info to provide. I appreciate your help. Danny S. * Adventure Cycling Association Inspiring people of all ages to travel by bicycle. 800/755-2453, 406/721-1776 www.adventurecycling.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create DB help
Hello,
I am new with using mySQL.
I have a little problem
I am starting a new web site on which I wanna use the same DB I am using on another
site (that was created by somebody else). Instead of starting from scratch, I would
like to use a copy of the DB we have, since we know it is already good and tested.
I have an .sql file for the database structure.
I found this:
http://www.jsw4.net/info/list-archives/mysql/02-wk49/msg00049.html
I believe it is exactly what I need.
What I need to knwo now... could you please tell me WHERE I need to try:
mysql -u{username} -p {databasename}filename.sql
Thank you very much in advance
Tony
Re: Create DB help
On Mon, Jun 21, 2004 at 12:01:06PM -0400, Tony Martino wrote:
I believe it is exactly what I need.
What I need to knwo now... could you please tell me WHERE I need to try:
mysql -u{username} -p {databasename}filename.sql
This assumes a few things:
- that the database (as opposed to the tables in the database) has
already been created.
- that '{username}' has the priviledges of writing to said database
('create table', and so forth.
As to 'where': from the command line. 'mysql' is the name of the
command-line client for MySQL.
Unless I'm misunderstandig your question...
Thank you very much in advance
Tony
--
Brian Reichert [EMAIL PROTECTED]
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA BSD admin/developer at large
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pocket PC, Flash, Linux server, Apache, MySQL, PHP
Hi, I am researching possible solutions for a simple database application delivered to a Pocket Windows PDA (PocketPC). The database will be a subset of a larger company-wide database held on a Linux server using MySQL and PHP. The PDA database will be used by a small number of people - probably no more than 10 initially - and they will make only a small number of edits to the database, fewer than 5 daily under normal circumstances. The server and database already exist, so no configuration changes are possible, but I can make changes to the database if required. I'd love data replication with the server to be automatic if possible, but if I have to build something to handle that then so be it. For the sake of a nice clean, updatable interface, and to allow for some other multimedia facilities which we can consider unrelated to this database, the interface will be Macromedia Flash. I am looking for the least painful method to enable the above. I have encountered numerous suggestions including - Wrapping the Flash interface inside a eVC wrapper, eVB, e.Net (C#) ... - Using a local Access database, synchronising that with a server copy and then synchronising the server copy of the access database with the main MySQL application. - Saving a record of local updates to text/xml file then passing the saved data to the server for processing on synchronisation. If user A makes updates, a record of those updates is then saved to a special table for users B, C, D ... so that when they synchronise they can download and apply the same edits. . but an automatic replication would be far more elegant. So far all of this is speculation. Obviously I have to make a choice as to what methods I will apply and what technologies to employ. Has anyone here done anything similar to above? Can you share comments, suggestions, tools used? TIA Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why CPU is high while disks are idle in a table scan???
I'm doing some tests on a 16.000.000 rows table. Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed that disks are at 0.1%, while cpu arrives up to 80%. How is that possible? My HDs are IDE. MySql has around 30Mb of ram, I thought it should read a lot from disk. Even doing lots of queries by key I get only high cpu usage, not disk reads. Is that normal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why CPU is high while disks are idle in a table scan???
On Mon, Jun 21, 2004 at 06:57:51PM +0200, Leonardo Francalanci wrote: I'm doing some tests on a 16.000.000 rows table. Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed that disks are at 0.1%, while cpu arrives up to 80%. How is that possible? My HDs are IDE. MySql has around 30Mb of ram, I thought it should read a lot from disk. Even doing lots of queries by key I get only high cpu usage, not disk reads. Is that normal? Hard to say, but in a table scan the CPU does have a lot of work to do. It needs to do about 16,000,000 comparisons (based on your info). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: why CPU is high while disks are idle in a table scan???
Hard to say, but in a table scan the CPU does have a lot of work to do. It needs to do about 16,000,000 comparisons (based on your info). Why comparison? It's a sum... And the table is not small: 272,000,000 bytes! And disk is very low (almost 0%) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why CPU is high while disks are idle in a table scan???
If all your queries are on columns that are indexed (query by key), then all your data for your query may actually fit into cache. If you are only pulling data from a key field, then MySQL doesn't need to actually access the whole table, just the index. You usually see high disk usage when you are accessing a lot of data, which is not necessarily the same as accessing a lot of rows. On Jun 21, 2004, at 12:57 PM, Leonardo Francalanci wrote: I'm doing some tests on a 16.000.000 rows table. Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed that disks are at 0.1%, while cpu arrives up to 80%. How is that possible? My HDs are IDE. MySql has around 30Mb of ram, I thought it should read a lot from disk. Even doing lots of queries by key I get only high cpu usage, not disk reads. Is that normal? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: why CPU is high while disks are idle in a table scan???
Sorry, now it runs as espected (but I don't know what happened, maybe doing other queries I emptied out the cache) Thank you anyway for your replies. And: could somebody answer my previous question? I wrote: I have to deal with pretty big data, such a table (say BIG_TABLE) with 16.000.000 of rows. The table has just one primary index (a simple int). I thought that splitting data among multiple tables would have boosted performance, so I put my data in 16 different tables (everything is on one disk). The first table had IDs from 1 to 1.500.000, the second the range 151-300 and so on. When I made performance tests, I found that I didn't get any performance gain. So I tried (just for test purpose) to create a table of 10 rows (say SMALL_TABLE) with some data (IDs from 1.000.000 to 1.100.000) from my big table BIG_TABLE. Tests were like follows: (pseudocode) for 10 times read a column from BIG_TABLE where ID=random number between 1.000.000 and 1.100.000 for 10 times read a column from SMALL_TABLE where ID=random number between 1.000.000 and 1.100.000 The results are the same! There is no noticeable difference between a table with 16M rows and a table with 100K rows! Is that because my IDs are simple int from 1 to n (without gaps)? I've tried to do 10 inserts in both tables, and BIG_TABLE takes 45 seconds while SMALL_TABLE takes 43 secs (not such a big difference for a table 160 times smaller!) The only big performance gain I got is from a SUM on a big range, because MySql in this case does a full scan on the SMALL_TABLE which is faster than an access by index on the BIG_TABLE. Am I doing something wrong? Isn't splitting supposed to improve performance? Or it is true only if you can split data on multiple disks? Thank you Leonardo -- 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: R: why CPU is high while disks are idle in a table scan???
On Mon, Jun 21, 2004 at 07:10:59PM +0200, Leonardo Francalanci wrote: Hard to say, but in a table scan the CPU does have a lot of work to do. It needs to do about 16,000,000 comparisons (based on your info). Why comparison? It's a sum... Oh. You didn't say thta. :-) And the table is not small: 272,000,000 bytes! And disk is very low (almost 0%) How much RAM does your machine have? If it's nearly all cached, you'll be CPU bound rather than disk bound. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: R: why CPU is high while disks are idle in a table scan???
Oh. You didn't say thta. :-) Well, I wrote Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed but don't worry ;) How much RAM does your machine have? If it's nearly all cached, you'll be CPU bound rather than disk bound. 1Gb. I'm using Solaris 8. The SUM() works as expected now (disks works a lot), but accessing by key is very cpu-intense and still 0% work on disk. To sum up: table scans are very disk-intense (which make sense), accessing by key leaves disk on idle (I see that it should not work as with a table scan, but at least a little!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
thread_stack and out of memory issues...
Hi! My Config: Running Redhat 9.0 Linux 2.4.20-8bigmem SMP 8GB mem 250GB raid array My server: MySQL 4.1.2-alpha-standard We are running into strange memory related errors with our system. 1) Warning: Asked for 262144 thread stack, but got 126876 On start up, we get the above warning. How can I help MySQL get the stack space that it expects? I changed thead_stack in my.cnf to 256k (from 192k) just to test the param out and it had no effect. 2) Out of Memory; check if mysqld or some other process uses all available memory -and- Out of memory (needed 56 bytes) These errors are usually generated after MySQL dies. These usually are produced after a large insert of a let's say 100,000 records from our c client app using: INSERT DELAYED INTO kneejoint VALUES (),(),(), etc... Our C client inserter application works well, so I am leaning toward our server/distro configuration as the problem. I'm inserting the records using our client because we are receiving real-time stress/strain data from a remote input source. By the way, max_allowed_packet is large enough, so that is not the problem. 3) Can't create new thread (errno 11) I am assuming that the server can't start a new client thread because of memory issues. I have monitored the system and it doesn't look like any other app is using much memory at all. In fact, it looks like most mem is cached when examined using top. I haven't been able to interpret any of these error properly, so any help would be appreciated!
Re: Where to start for performance issues?
Performance for anything, database or otherwise, comes down to finding where the bottleneck is. Thankfully, there are only a limited number of places the bottleneck can be: RAM, CPU, Disk I/O, Network. Sounds easy, you have a 1 in 4 chance of picking the right one without doing any analysis. Of course, it's not that easy. If your my.cnf file is not configured optimally, MySQL may be loading up the RAM it is allowed to use (perhaps showing a CPU spike), but then starting swapping out to disk (temp files) when it hits it's RAM allocation. Since this is MySQL hitting a RAM allocation limit and not the system running out of RAM, you won't see the OS indicating page outs, which would indicate not enough RAM. Usually, the two parameters that help the most in the my.cnf file is the key_buffer and sort_buffer_size. If your complaints are coming from Forum and Picture Gallery, your bottleneck may be in disk I/O. I always try to put the OS on it's own disk like you, but if I only have two disks, I'll mirror the two disks to increase read speeds. Although it's then double important that you system has enough RAM since your writes will be slower (think slow memory paging). Of course, if you want to live dangerously, which you are just about doing anyway with no mirroring, you can stripe your disks and get better read and write speeds. But first play with you my.cnf parameters to see if things speed up. Also, read the manual: http://dev.mysql.com/doc/mysql/en/Optimising_the_Server.html On Jun 21, 2004, at 12:10 AM, Eric Wagar wrote: I have a Sun Netra T1 with 512MB of ram and 2 x 9G drives. (One is exclusively the MySQL 3.23.56 files, and the other is OS, Apache, etc.) I am also using the default medium (I think) my.cnf. Currently, the complaints are coming from a php Forum and a php picture Gallery. From the OS side, I don't see any problems, along with the Apache side. So, I am leaning towards an improper MySQL configuration. Where do I go to get more information on where to start looking for performance issues for the DB? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: R: why CPU is high while disks are idle in a table scan???
On Mon, Jun 21, 2004 at 07:37:50PM +0200, Leonardo Francalanci wrote: Oh. You didn't say thta. :-) Well, I wrote Doing a simple SELECT SUM(MYFIELD) FROM MYTABLE I noticed but don't worry ;) Oh. Damned Mondays. I never liked 'em. :-( How much RAM does your machine have? If it's nearly all cached, you'll be CPU bound rather than disk bound. 1Gb. I'm using Solaris 8. The SUM() works as expected now (disks works a lot), but accessing by key is very cpu-intense and still 0% work on disk. To sum up: table scans are very disk-intense (which make sense), accessing by key leaves disk on idle (I see that it should not work as with a table scan, but at least a little!) Hmm. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBD::mysql problem
I am unable to install DBD::mysql in my environment shown below RedHat Linux 9 perl v5.8.0 DBI-1.42 mysqld Ver 4.0.18-standard After unzipping and untarring the DBD-mysql-2.9003.tar.gz ( obtained from CPAN ), in the DBD-mysql-2.9003 directory I tried the following mkdir /tmp/mysql-static cp /usr/lib/mysql/*.a /tmp/mysql-static perl Makefile.PL --libs=-L/tmp/mysql-static -lmysqlclient make It fails. When I looked at the Makefile created by 'perl Makefile.PL ..' command, I find there are lines that cause make to fail Mostly they are constants that are not properly quoted. Is there a proper tar.gz file that I can use in my ( RH Linux 9) environment stated above that someone was able to successfully install? Is it possible I am trying to use a version of DBD with wrong versions of Linux, perl and MySql individually or when used together as above ? Any advice or help will be very much appreciated. Thanks Raj Kairam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: why CPU is high while disks are idle in a table scan???
There are 3 major factors determining your performance: 1) the speed of the CPU, 2) the size if your RAM, and 3) the data transfer speed of your disks. Since most computers do not let you change your CPU on the fly I will assume that for some process A it will require the same number of clock cycles to execute whether your data is in memory or on your disk. Being a constant, we will leave it out for a moment. RAM is much faster than disk for storage and retrieval. Thus, if you have more RAM, you will generally have better performance. That leave DISK I/O as the factor you are trying to change in order to improve your performance, right? Let's think through several scenarios and figure out why what you did made only a small difference to your performance in the tests you ran. TEST 1) Random read of 10 rows Whenever the database engine has to find a value in a database it has only two choices. It can find a value in an index or it can perform a table scan to find the value. Since I assume you used your primary key as your search value for both tables then both lookups were resolved by index search. MySQL uses binary tree indexes. That means for any search in an index of size X you will have to perform roughly N=(log-base-2( X) ) comparisons to find your value. For an index containing 1.5 Million items it would take (log-base-2(1.5e6)) or about 21 comparisons to locate your value in the index. For an index that contains 16 million we get the number N=(log-base-2(1.6e7)) or about 24 comparisons to find your number. How much longer did you think the search engine would need to do those 3 extra comparisons? What takes the longest is transferring all of the data for the index into RAM from the disk then moving the read heads to the correct location to retrieve your record. The actual lookup is blazingly fast. 16 million integers (assuming 32 bits per integer + 8 bytes for record pointers) is only 192 MB so the entire list fits nicely into most server's memory. TEST 2) Insert 10 rows of new data This performance will vary by database engine you are using (MyISAM or InnoDB) as does the storage method. Suffice it to say that by the time the disk has finished physically writing 10 rows of data to the media, the CPU has already updated your index and is waiting on a chance to commit its cache to disk. Both engines can grow the end of their indices and data storage on disk efficiently so dumping new data into a singly-indexed table is fast. Since all of the data was sent to the end of the dataspace, there was no need to split data pages to insert rows mid-space. What can kill INSERT performance is if there are several indices on a table. Each new record will have to be added to each index causing a nearly linear slowdown in INSERT performance. What splitting your data into smaller tables WILL help with is: 1) you are more fault tolerant. The failure of one 10% block of your data will not affect in the availability of the other 90%. 2) you can move parts of your data to other disks. Using multiple I/O channels should be faster than a single channel. 3) it will be easier to backup and archive your database. You can move parts of your data to near-line storage if it is old and not frequently needed. 4) You can apply different indexing methods to your most active data blocks than you need for your older records, improving your response time. You save total disk space by adding indexes to just the most active portions of your data. 5) If your application is smart enough, you can split your data over several servers. This would be a SERIOUS performance increase. 6) Write-locking a table only locks up a portion of your data (MyISAM). This improves concurrency and consistency. Negative things about splitting your data: 1) You will have to code more MERGE tables, UNION queries, and table JOINS depending your circumstances. This is a negative due to the increased development time and maintenance complexity. 2) Certain aggregate queries will be much less efficient or unavailable. 3) Requires an external partitioning processes to decide which table should receive new data. Depending on your application, you may be able to gain significant increases in performance by splitting your tables. I hope I was able to shed some light on why I think your tests didn't show much difference between the two storage schemes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Leonardo Francalanci To: Mysql [EMAIL PROTECTED] [EMAIL PROTECTED]cc: tel.ie
Intel Compiler
I am trying to use my binary Intel installation to compile PHP (which seems
to be doing a _very_ simple program). So in a statically linked
installation these references are defined in the embedded C library, but if
one is linking it with another application, it isn't.
Possibly the intel C library would need to be distributed with the binary
application for the sake of linking with other applications? Thoughts?
To quote the Intel C++ Compiler release notes:
The Intel C++ Compiler uses two routines _intel_fast_memcpy and
_intel_fast_memset to perform memcpy and memset operations that are not
macro expanded to __builtin_memcpy and __builtin_memset in the source code.
These are found in libirc. If you use the gcc compiler to link your
application or if you directly call the linker, ld, you might find these
unresolved symbols. For this reason, Intel recomends using the Intel C++
Compiler for linking, using the same compiler options used during the
compilation phase. However, if you see these as undefined externals, either
add -lirc to your link line, or change your includes so that memcpy and
memset will be macro expanded to the builtin forms and recompile. The Intel
C++ Compiler for IA-32 based applications calls a routine intel_proc_init
from the main routine of any program to ensure that the processor is
correctly set up. This routine is also found in libirc. These routines used
further entry points from glibc, so -lirc needs to be placed before -lc on
your command line.
usr/local/mysql/lib/libmysqlclient.a(libmysql.o): In function `read_rows':
libmysql.o(.text+0xe60): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(libmysql.o): In function
`mysql_real_connect.':
libmysql.o(.text+0x3044): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_malloc.o): In function `my_memdup':
my_malloc.o(.text+0x31): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_malloc.o): In function `my_strdup':
my_malloc.o(.text+0x100): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_malloc.o): In function
`my_strdup_with_length':
my_malloc.o(.text+0x132): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_alloc.o)(.text+0x327): more
undefined references to `_intel_fast_memcpy' follow
collect2: ld returned 1 exit status
configure: failed program was:
#line 56453 configure
#include confdefs.h
/* Override any gcc2 internal prototype to avoid an error. */
/* We use char because int might match the return type of a gcc2
builtin and then its argument prototype would still apply. */
char mysql_error();
int main() {
mysql_error()
; return 0; }
-Mike
_
Add photos to your messages with MSN Premium. Get 2 months FREE*
http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication on same machine...
I have MySQL 4.1 installed via rpm. I just installed a new hd because I want another MySQL installation on the same machine but acting as a replication server. At the same time, I want to try out MaxDB on this new installation. Does that mean that I can't set up a master/slave replication with the hdb and hda? Or can I go ahead and have a different config with the new instance and simply follow the instructions for setting up replication as normal? Also, I have the source rpm, I figure I can tweak it to install into my new location which is /mysql2 . Is that all I need to do make sure that it won't interfere with the current install? How about command such as mysqladmin and mysql, etc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
opposite query
I have: select name from a, b where a.type='X' and a.id=b.id; I want a query to return all the rows that were NOT found by the above query. I can't simply do: select name from a, b where a.type!='X' and a.id=b.id; because there is more than one row in b for each type!='X' but there is only one row in b for each type='X' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication on same machine...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all! Jonathan Villa wrote: | I have MySQL 4.1 installed via rpm. I just installed a new hd because I | want another MySQL installation on the same machine but acting as a | replication server. - --8 It won't matter as long as you keep all you mysql installation-config-instances in separated locations. Also make sure they'll be listening differents ports and using differents my.cnf's. | | At the same time, I want to try out MaxDB on this new installation. Does - ---8 MaxDB uses some other port/set-of-config files than MySQL server. There should not be a problem with this. | that mean that I can't set up a master/slave replication with the hdb and | hda? Or can I go ahead and have a different config with the new instance | and simply follow the instructions for setting up replication as normal? YEAP! =) as long as they are in _SEPARATE_ configs | | Also, I have the source rpm, I figure I can tweak it to install into my It could be _WAY_ easier to just do a compile/install the old way, just change the prefix option of the configure script to poit to differents locations: --prefix=/opt/mysql-1 --prefix=/opt/mysql-2 ... and so on | new location which is /mysql2 . Is that all I need to do make sure that | it won't interfere with the current install? How about command such as | mysqladmin and mysql, etc | | They don't even care as long as you tell them the right ip/port. =) SO... if performace is not a problem and is just for testing, go ahead! Best Regards! - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| - --- .- Este mensaje está digitalmente firmado para garantizar ~ su origen .- El intercambio de llaves públicas se realiza a petición ~ de las partes interesadas via e-mail - --- .- This message has been digitally signed .- Public Key (PGP or GPG) available upon request -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA1z6c8WJSBCrOXJ4RAhVgAJ0YoXk23mIYiH8kjyBKkvAbu8IJJgCfSgkc oowua4XLtl/ge2qkPs/omDM= =yx9d -END PGP SIGNATURE-
Re: opposite query
I don't understand. You want every other record except .what? By flipping the equality the way you did, you should see all of the records where a.id = b.id (regardless of what the b.type value is) where a.type was not 'X' which is one reasonably valid solution to the statement all of the rows NOT found by the above query The more details you give us the better we can help you. If you can give us your SHOW CREATE TABLE statements and possibly some sample data, we should be able to get at exactly the data you want to see. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Bob Lockie [EMAIL PROTECTED]To: MySQL Mailing List [EMAIL PROTECTED] ca cc: Fax to: 06/21/2004 04:04 Subject: opposite query PM I have: select name from a, b where a.type='X' and a.id=b.id; I want a query to return all the rows that were NOT found by the above query. I can't simply do: select name from a, b where a.type!='X' and a.id=b.id; because there is more than one row in b for each type!='X' but there is only one row in b for each type='X' -- 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: opposite query
The opposite of the query would be a.type!='X' and there is no related record in table b. Not sure if that is what you what, but this is what the query would look like: select name from a left join b on a.id=b.id where b.id is null and a.type!='X' On Jun 21, 2004, at 4:04 PM, Bob Lockie wrote: I have: select name from a, b where a.type='X' and a.id=b.id; I want a query to return all the rows that were NOT found by the above query. I can't simply do: select name from a, b where a.type!='X' and a.id=b.id; because there is more than one row in b for each type!='X' but there is only one row in b for each type='X' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: opposite query
On 06/21/2004 04:26 PM [EMAIL PROTECTED] spoke: I don't understand. You want every other record except .what? By flipping the equality the way you did, you should see all of the records where a.id = b.id (regardless of what the b.type value is) where a.type was not 'X' which is one reasonably valid solution to the statement all of the rows NOT found by the above query The more details you give us the better we can help you. If you can give us your SHOW CREATE TABLE statements and possibly some sample data, we should be able to get at exactly the data you want to see. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine drop table a; create table a ( id int auto_increment, name varchar(20), primary key (id) ); drop table b; create table b ( id int auto_increment, a_id int not null, name char(20), type char, primary key (id) ); insert into a (name) values (row1); insert into b (a_id, name, type) values(1, 1b, X); insert into b (a_id, name, type) values(1, 2b, Y); insert into b (a_id, name, type) values(1, 3b, Y); insert into b (a_id, name, type) values(1, 4b, Z); insert into a (name) values (row2); insert into b (a_id, name, type) values(2, 1a, X); insert into b (a_id, name, type) values(2, 2a, Y); insert into b (a_id, name, type) values(2, 3a, Y); insert into b (a_id, name, type) values(2, 4a, Z); insert into a (name) values (row3); insert into b (a_id, name, type) values(3, 2a, Y); insert into b (a_id, name, type) values(3, 3a, Y); insert into b (a_id, name, type) values(3, 4a, Z); select a.name from a, b where b.type='X' and a.id=b.a_id; returns row1 and row1 from a. Now I need a select that will return row3 but not rows 1 or 2. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: opposite query
On 06/21/2004 05:02 PM Brent Baisley spoke: The opposite of the query would be a.type!='X' and there is no related record in table b. Not sure if that is what you what It isn't what I want because there could be other a.type other than 'X'. Oops, that should be b.type I need to return the a records that have no b records of type 'X' but there may be b records of other types. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding replica server in a mixed MyISAM/InnoDB multi-master env?
On Thu, Jun 10, 2004 at 12:04:52PM -0400, Tabor J. Wells [EMAIL PROTECTED] is thought to have said: I have a fairly complicated and large MySQL installation that I need to add a new slave server to and I'm uncertain about the best way to get this done with the least risk to my live environment and least downtime of my existing masters. I'm hoping that someone here can suggest the best way for me to do this. Here's the setup: ServerA (Redhat 9, MySQL 4.0.16) Replicates 4 dbs from ServerB Replicates 2 dbs to ServerB ServerB (RedHat 9, MySQL 4.0.16) Replicates 4 dbs to ServerA Replicates 2 dbs from ServerA Has 19 other dbs I need to add ServerC which would contain replicas of all 23 dbs (which is about 60gb of data total). ServerC is a RedHat 7.3 server and would be running MySQL 4.0.20. The problem is that those dbs contain a mixture of InnoDB and MyISAM tables How do I get a consistent snapshot of those dbs and get the replication set up? Is it safe to just shutdown ServerB, copy all of the mysql data dir (including the ibdata files) to ServerC, restart ServerB, and then change the my.cnf of ServerC to set a new server-id, master-host, replicate-do-db entries and start ServerC? Is there anything else that needs to be done to ensure the consistency of the data and that the replication will work as expected? If this isn't the best way to set up replication for an already-running server with a mixture of InnoDB and MyISAM tables, what is? For the sake of the list archives and anyone else that needs to do this in the future, the method I suggested above does in fact work just fine and doesn't require the use of mysqldump --opt as Egor suggested. It took about 5.5 hrs to rsync -ac the mysql data directory from ServerB to ServerC over a 100M connection while ServerB's mysql instance was shut down. -- Tabor J. Wells [EMAIL PROTECTED] Fsck It! Just another victim of the ambient morality -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert data if not duplicate based on order
Hi,
I have a table that has many rows. I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.
Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert. If a match occurs I will skip the insert
command.
This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.
I know this is bad syntax, but this is what I imagine
a insert query to look like:
INSERT INTO $table (datax, datay, dataz)
VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20
In this example, 'z' is a datetime field and 'x' is a
varchar. I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.
Is this possible to do with one INSERT command? I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.
Any suggestions would be appreciated,
Thanks,
Grant
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert data if not duplicate based on order
I don't think its possible in one query. One thing you can do is lock
the table when you select the 20 rows and determine whether to do the
insert. Then unlock when done. This avoids the concurrency issue you
are having, but it may cause unacceptable perfomance if you have a lot
of queries hitting this table. Maybe someone else has a better
solution.
John A. McCaskey
-Original Message-
From: Grant Giddens [mailto:[EMAIL PROTECTED]
Sent: Monday, June 21, 2004 4:59 PM
To: [EMAIL PROTECTED]
Subject: Insert data if not duplicate based on order
Hi,
I have a table that has many rows. I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.
Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert. If a match occurs I will skip the insert
command.
This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.
I know this is bad syntax, but this is what I imagine
a insert query to look like:
INSERT INTO $table (datax, datay, dataz)
VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20
In this example, 'z' is a datetime field and 'x' is a
varchar. I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.
Is this possible to do with one INSERT command? I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.
Any suggestions would be appreciated,
Thanks,
Grant
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you deal with URL's?
Suppose I have a field with the names of states, linked to their home pages: a href=http://www.alabama.gov/;Alabama/a a href=http://access.wa.gov/;Washington/a If I display this on a webpage, I'll get the names of the states, linked to their home pages. But is there a simple strategy that will let me to display the names UNLINKED on another page, or do I have to create a second field that lists simple state names, with no URL's? Thanks. __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you deal with URL's?
And here's a follow up question... When you want to designate URL's or other html tags in a database table, do you use the same tag containers you use in your html - and - or do (or can) you use the equivalents, like lt; Using is kind of tricky, since they're invisible when I'm working in design view. I make most of my tables on a webpage, preview them in a browser, then copy them into a spreadsheet, so that pretty much rules out invisible elements. __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you deal with URL's?
Personally, I'd split that into 2 fields. I think that's a better way
to model the data unless there's something I don't know.
Otherwise, try this:
mysql select * from url;
+---+
| url |
+---+
| a href=http://www.alabama.gov;Alabama/a |
| a href=http://access.wa.gov;Washington/a |
+---+
2 rows in set (0.00 sec)
mysql select substring(url,locate('',url)+2,
char_length(url)-locate('',url
)-5) as state from url;
++
| state |
++
| Alabama|
| Washington |
++
2 rows in set (0.00 sec)
mysql
What I had to to was to have mysql take the string:
a href=http://www.alabama.gov;Alabama/a
and give me the parts between and /a. First, I had to find the
position of and then add 2 to it. The substring function in mysql
takes the parameters string, starting_position, and length. Using
locate, I got the starting postion and added 2 to it. For length, I had
to use locate again; locating gives me the position of the in .
Subtracting 5 gives me the right length after discounting the /a and
the 2 positions I'm off from .
Someone more experienced that I can tell you if there's a more effecient
way. My inclination would be that for best results, you should split
the field in two and build your webpage like this:
a href=$URL$STATE/a
Hope this helps.
bob
David Blomstrom wrote:
Suppose I have a field with the names of states,
linked to their home pages:
a href=http://www.alabama.gov/;Alabama/a
a href=http://access.wa.gov/;Washington/a
If I display this on a webpage, I'll get the names of
the states, linked to their home pages. But is there a
simple strategy that will let me to display the names
UNLINKED on another page, or do I have to create a
second field that lists simple state names, with no
URL's?
Thanks.
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you deal with URL's?
On Mon, 21 Jun 2004, David Blomstrom wrote: Suppose I have a field with the names of states, linked to their home pages: a href=http://www.alabama.gov/;Alabama/a a href=http://access.wa.gov/;Washington/a If I display this on a webpage, I'll get the names of the states, linked to their home pages. But is there a simple strategy that will let me to display the names UNLINKED on another page, or do I have to create a second field that lists simple state names, with no URL's? Doing the way you're suggesting would create some problems: - data redundancy (a href=http:// and /a on every field) - storing two different information on a single field (state name and URL) - the MySQL index would be filled with at least 8 bytes of useless data (a href=), wasting more disk space - no database-friendly way to search for states or even to sort them by name Separate data from how it's displayed. I mean, create a table called states with the fields state_id, state_name and state_url. Put the data in the according field: state_id state_name state_url 1 Alabama http://www.alabama.gov 2 Washington http://access.wa.gov ...and so on Then you can easily list only state names (ordered by state_name, for example), not showing the URL. []s, Salvi. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you deal with URL's?
Sergio Salvi wrote:
Doing the way you're suggesting would create some
problems:
- data redundancy (a href=http:// and /a on every
field)
- storing two different information on a single field
(state name and
URL)
- the MySQL index would be filled with at least 8
bytes of useless data
(a href=), wasting more disk space
- no database-friendly way to search for states or
even to sort them by
name
Separate data from how it's displayed. I mean, create
a table called
states with the fields state_id, state_name and
state_url. Put
the data in the according field:
state_id state_name state_url
1 Alabama http://www.alabama.gov
2 Washington http://access.wa.gov
and so on
OK, I see. But I assume you mean http://www, rather
than http without the beginning tag - or do you insert
that with PHP? It just occurred to me that I probably
don't need to put /a in the database, because I
probably add that with PHP somehow.
--- Bob Ramsey [EMAIL PROTECTED] wrote:
Personally, I'd split that into 2 fields. I think
that's a better way
to model the data unless there's something I don't
know.
Otherwise, try this:
mysql select * from url;
+---+
| url |
+---+
| a href=http://www.alabama.gov;Alabama/a |
| a href=http://access.wa.gov;Washington/a |
+---+
2 rows in set (0.00 sec)
mysql select substring(url,locate('',url)+2,
char_length(url)-locate('',url
)-5) as state from url;
++
| state |
++
| Alabama|
| Washington |
++
2 rows in set (0.00 sec)
mysql
What I had to to was to have mysql take the string:
a href=http://www.alabama.gov;Alabama/a
and give me the parts between and /a. First, I
had to find the
position of and then add 2 to it. The substring
function in mysql
takes the parameters string, starting_position, and
length. Using
locate, I got the starting postion and added 2 to
it. For length, I had
to use locate again; locating gives me the
position of the in .
Subtracting 5 gives me the right length after
discounting the /a and
the 2 positions I'm off from .
Someone more experienced that I can tell you if
there's a more effecient
way. My inclination would be that for best results,
you should split
the field in two and build your webpage like this:
a href=$URL$STATE/a
Hope this helps.
I think I understand. Thanks.
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: opposite query
Bob Lockie wrote: On 06/21/2004 04:26 PM [EMAIL PROTECTED] spoke: I don't understand. You want every other record except .what? By flipping the equality the way you did, you should see all of the records where a.id = b.id (regardless of what the b.type value is) where a.type was not 'X' which is one reasonably valid solution to the statement all of the rows NOT found by the above query The more details you give us the better we can help you. If you can give us your SHOW CREATE TABLE statements and possibly some sample data, we should be able to get at exactly the data you want to see. Respectfully, Shawn Green drop table a; create table a ( id int auto_increment, name varchar(20), primary key (id) ); drop table b; create table b ( id int auto_increment, a_id int not null, name char(20), type char, primary key (id) ); insert into a (name) values (row1); insert into b (a_id, name, type) values(1, 1b, X); insert into b (a_id, name, type) values(1, 2b, Y); insert into b (a_id, name, type) values(1, 3b, Y); insert into b (a_id, name, type) values(1, 4b, Z); insert into a (name) values (row2); insert into b (a_id, name, type) values(2, 1a, X); insert into b (a_id, name, type) values(2, 2a, Y); insert into b (a_id, name, type) values(2, 3a, Y); insert into b (a_id, name, type) values(2, 4a, Z); insert into a (name) values (row3); insert into b (a_id, name, type) values(3, 2a, Y); insert into b (a_id, name, type) values(3, 3a, Y); insert into b (a_id, name, type) values(3, 4a, Z); select a.name from a, b where b.type='X' and a.id=b.a_id; returns row1 and row1 from a. Now I need a select that will return row3 but not rows 1 or 2. In another message, Bob Lockie wrote: I need to return the a records that have no b records of type 'X' but there may be b records of other types. SELECT a.name FROM a LEFT JOIN b ON a.id=b.a_id AND b.type='X' WHERE b.a_id IS NULL; +--+ | name | +--+ | row3 | +--+ 1 row in set (0.01 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld dead but subsys locked
I have a dedicated server, which went down without any reason. I am runing a couple of phpBB databases there. Now when I try to access the phpBB from browser, I get the following message. Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) Using Shell, I logged in ran the command 'service mysqld status' it showed: 'mysql dead but subsys locked' tried to 'restart' it, Stopping - [FAILED], Starting - [OK] Tried to reboot the server a couple of times, but no gains, the problem still stands there. Searched the net for possible solutions, and I have tried the following solutions till now without any outcome. 1- Try chowning /var/lib/mysql to chown -R mysql.mysql /var/lib/mysql 2- You may want also to remove the lock file from '/var/lock/subsys/' which is 'mysqld'.After this, restart the mysql daemon, and run mysql. I tried and removed mysql file there many times, without any outcome. 3- killall -9 mysqld --- may take a couple of attempts then... /etc/rc.d/init.d/mysqld restart mysql should turn over and run fine again. Tried it and got : mysqld: no processes killed - Can anyone help me as what to do next? Thanks in advance for any help as I am desperately looking for some solution. Thanks, Sara.
Re: How do you deal with URL's?
On Mon, 21 Jun 2004, David Blomstrom wrote:
Sergio Salvi wrote:
[...]
Separate data from how it's displayed. I mean, create
a table called
states with the fields state_id, state_name and
state_url. Put
the data in the according field:
state_id state_name state_url
1 Alabama http://www.alabama.gov
2 Washington http://access.wa.gov
and so on
OK, I see. But I assume you mean http://www, rather
than http without the beginning tag - or do you insert
that with PHP? It just occurred to me that I probably
don't need to put /a in the database, because I
probably add that with PHP somehow.
I would add the a href HTML tag in the application and store only the
URL in the database. Just do not remove the http://; and assume in your
application that every URL starts with that because some of them may be
https://;. If you are space usage paranoid (hehe), you could create a
enum(y,n) field called use_ssl and remove the http://; from every
URL, setting the use_ssl field to y when the URL is a https:// one.
Do not assume you'll always display the URLs in an HTML output. What if
you decide to send it by plain-text mail? Or output in XML to export it to
another system?
HTH.
[]s,
SAlvi
--- Bob Ramsey [EMAIL PROTECTED] wrote:
Personally, I'd split that into 2 fields. I think
that's a better way
to model the data unless there's something I don't
know.
Otherwise, try this:
mysql select * from url;
+---+
| url |
+---+
| a href=http://www.alabama.gov;Alabama/a |
| a href=http://access.wa.gov;Washington/a |
+---+
2 rows in set (0.00 sec)
mysql select substring(url,locate('',url)+2,
char_length(url)-locate('',url
)-5) as state from url;
++
| state |
++
| Alabama|
| Washington |
++
2 rows in set (0.00 sec)
mysql
What I had to to was to have mysql take the string:
a href=http://www.alabama.gov;Alabama/a
and give me the parts between and /a. First, I
had to find the
position of and then add 2 to it. The substring
function in mysql
takes the parameters string, starting_position, and
length. Using
locate, I got the starting postion and added 2 to
it. For length, I had
to use locate again; locating gives me the
position of the in .
Subtracting 5 gives me the right length after
discounting the /a and
the 2 positions I'm off from .
Someone more experienced that I can tell you if
there's a more effecient
way. My inclination would be that for best results,
you should split
the field in two and build your webpage like this:
a href=$URL$STATE/a
Hope this helps.
I think I understand. Thanks.
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
