New to Dates - Plain English Please

I haven't worked with dates yet and wondered if someone could give me an overview in plain English. At the moment, I'm working on a table with a column of dates in this format: March 2, 2003 July 7, 2004 If I understand the Manual, the correct format for these dates in a MySQL table would be li

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_u

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

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

Re: How do you deal with URL's?

Sergio Salvi wrote: "Doing the way you're suggesting would create some problems: - data redundancy (http://www.alabama.gov 2 Washington http://access.wa.gov and so on OK, I see. But I assume you mean

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: > > http://www.alabama.gov/";>Alabama > http://access.wa.gov/";>Washington > > If I display this on a webpage, I'll get the names of > the states, linked to their home pag

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 | +-

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 < Using <> is kind of tricky, since they're invisible when I'm working in de

How do you deal with URL's?

Suppose I have a field with the names of states, linked to their home pages: http://www.alabama.gov/";>Alabama http://access.wa.gov/";>Washington 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 displa

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 hitti

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

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 environ

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 record

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 v

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:

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 row

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 m

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 r

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 t

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 lib

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 cyc

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/

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?

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

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 hel

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 expect

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

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 r

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

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 arc

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 > 30

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

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

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} Thank you very much in advance > > Tony -- Brian Reichert

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 alre

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 alphabet

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. > >

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,

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/mysq

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 t

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

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 dr

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

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 woul

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 be

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)

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 adv

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: ---

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 a

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 Bles

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

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://

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

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. Be

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 distri

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). T

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]

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 speci

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.

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

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-ha

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.

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]

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 pr