Re: Problem With Creating Table

[Chris W]

Caroline Jen wrote:

Hi, I tried to create a table.  This table has
seventeen fields.  My create table syntax gets too
long and I was only able to specify 5 fields at the
mysql prompt in the DOS window (DOS does not accept a
command beyond certain length).  How do I put the rest
12 fields in the table I just created?
mysql>CREATE TABLE message_thread (thread_id INTEGER
NOT NULL AUTO_INCREMENT PRIMARY KEY, message_receiver
VARCHAR(79) NOT NULL, message_sender VARCHAR(79) NOT
NULL, article_title VARCHAR(255) NOT NULL,
last_post_member_name VARCHAR(79) NOT NULL);
You don't have to put it all on one line.  Just put whatever you want on 
the first line hit enter and put more on the next line. until it is 
 done.  The prompt will change from mysql> to -> which is an indicator 
that mysql is waiting for more input.  Bellow is an example from a 
create table I did recently.

The second section of Chapter 3 (MySQL Tutorial) of the manual tells all 
about how the prompt works. 
http://www.mysql.com/doc/en/Entering_queries.html

mysql> CREATE TABLE UserTest (
->  UserKey INT NOT NULL AUTO_INCREMENT,
->  UserID  CHAR(16) NOT NULL UNIQUE ,
->  PasswordCHAR(16),
->  Email   VARCHAR(128),
->  NameVARCHAR(20),
->  ShowEmail   BOOL,
->  PRIMARY KEY (UserKey)
-> );
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

revoke and set password syntax

[Chris W]

I am having trouble revoking the rights of the anonymous user or setting 
a password for that user.  Can some one help.  I can just do an update 
or delete but I want to do it with revoke and set password.

Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem With Creating Table

[Caroline Jen]

Hi, I tried to create a table.  This table has
seventeen fields.  My create table syntax gets too
long and I was only able to specify 5 fields at the
mysql prompt in the DOS window (DOS does not accept a
command beyond certain length).  How do I put the rest
12 fields in the table I just created?

mysql>CREATE TABLE message_thread (thread_id INTEGER
NOT NULL AUTO_INCREMENT PRIMARY KEY, message_receiver
VARCHAR(79) NOT NULL, message_sender VARCHAR(79) NOT
NULL, article_title VARCHAR(255) NOT NULL,
last_post_member_name VARCHAR(79) NOT NULL);

__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Hi, I sent you a greeting from AOL E-Cards

[[EMAIL PROTECTED]]

To view your greeting, choose from the options below.

Click on the following link:
http://www.pickup.aol.egreetings.com/view.pd?i=6375961&m=5319&rr=y&source=aole999

OR

Copy and paste the above link into your web browser's "address" window.

Thank you for using AOL E-Cards!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

special characters as field values

[Chris W]

I am storing data from an html form and there could be any character in 
there, including a % and an _.  I'm using mysql_real_escape_string in 
php to escape special characters, but it says it doesn't escape the % 
and _.  I was also reading something about escaping the -- .  If I want 
all of these characters and character sequences to be allowable field 
values do I need to do more than what mysql_real_escape_string will do 
for me?  I am enclosing all values in ' like the following...

$query = "INSERT INTO user Values (NULL, '$UserID', '$Password', 
'$Email', '$FName', '$LName', ";
$query .= "'$StreetAddress', '$AddressLine2', '$City', '$State', '$ZIP')";
$result = mysql_query($query);

Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: C Programing: mysql_free_result

[Peter Burden]

Rocar Peças wrote:

Dear Friends,

I´m programming in C, Linux envirnment.

The SELECT´s results are being brought to the client by using mysql_store_result. And the result sets are being released by mysql_free_result.

The problem is: as mysql_free_result is called, the system memory is NOT actually released. 

For example: I can monitor the program size in memory with "top" Linux utility (Conectiva 7.0 distribution). As the program is loaded into the memory, its size is, say, 1900Kb. Then, a select with a ruge result set is called and uploaded to the client. At this time, the program size in memory is 6000Kb. Then mysql_free_result is called. The program size in memory does not change. Isn´t it supposed to shrink back to 1900Kb?
 



   In general I wouldn't expect the memory to shrink, what actually 
happens depends on the
   behaviour of the underlying memory allocation routines. AFAIK the 
MySQL code "wraps"
   the standard alloc library routines on whatever platform you're 
using. MySQL doesn't do
   it's own memory management so whatever memory behaviour you see is 
that of the standard
   libraries on your system.

   Of course on a modern OS, the reported memory size of a process is 
usually the "top" address,
   this can be significantly larger than the actual memory used by the 
process.

Thanks, folks!!!

Leandro M Neves,
Rocar Pcs Ltd, IT Manager
Sete Lagoas/MG - BRAZIL
 



--
From Peter Burden, [EMAIL PROTECTED]
http://www.scit.wlv.ac.uk/~jphb



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FULLTEXT Search and Hyphens

[tk]

Sorry about this,

> The only way to search for kk-4835 is to do the
> search
> in Boolean mode and put things in quotes ->
> "kk-4835"
> So your query would look like this:
> 
> > SELECT * FROM ms_items
> > where MATCH (it_mnfgID, it_title, it_descrip)
> > AGAINST ('"kk-4835"' IN BOOLEAN MODE )

Put things in quotes not question marks. 
TK

__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FULLTEXT Search and Hyphens

[tk]

Hello, 

Fulltext section of mysql manual: "MySQL uses a very
simple parser to split text into words. A "word" is
any sequence of characters consisting of letters,
digits, " ' ", and  " _ ". Any "word" that is present
in the stopword list or is just too short is ignored."

Thus a hyphen would be viewed as a non-word character
and kk-4835 would be split in kk and 4835. And since
the default min. word length is 4, kk would not be
index but 4835 would be. For more info:
http://www.mysql.com/doc/en/Fulltext_Search.html

The only way to search for kk-4835 is to do the search
in Boolean mode and put things in quotes -> “kk-4835”
So your query would look like this:

> SELECT * FROM ms_items
> where MATCH (it_mnfgID, it_title, it_descrip)
> AGAINST ('”kk-4835”' IN BOOLEAN MODE )

and 

> I want to be able to type in "KK-4835", "KK4835", or
> even "KK 4835)

To be able to search for the variations you described
above I would just parse the search and insert a
hyphen where it is missing and then perform the same
search above.

Hope this helps,
TK


__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Subtracting date fields

[Scott Haneda]

on 01/31/2004 11:57 AM, Kenneth Letendre at [EMAIL PROTECTED]
wrote:

> Hello,
> 
>  I'm trying to get the difference (in days) between dates stored in two
> date fields.
>  My query:
> 
> SELECT id,(firstdate- postdate) AS diff FROM calendar
> 
>  This works fine if the two dates are in the same month, but not
> otherwise.  MySQL appears to be treating the two dates as base-10 integers
> rather than dates.  E.g.:
> 
> 2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) = 8876
> 
>  How do I get MySQL to treat these date fields as date fields in this case?

Take a look at 
for the DATEDIFF() function.
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How Do I Do This In Version 3.23.56?

[David Mohorn]

Have you tried to execute this statement manually thru their PHPAdmin?

Have you checked the case of your SQL?

I recently migrated a bunch of PHP/MySQL code from a Linux to Windows
then back to Linux and some of my SQL did not work because the case of
the table names had been converted to part upper/lower, or all upper,
etc.

I'd double check all your case to ensure everything is identical.



-Original Message-
From: John Boshier [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 31, 2003 12:36 PM
To: [EMAIL PROTECTED]
Subject: How Do I Do This In Version 3.23.56?


I have spent all day working on a PHP/MySQL system offline using MySQL
version 4.0.15-nt. One particular SQL statement that works perfectly
wouldn't work when I uploaded it to my web site. It seems that my ISP is
using version 3.23.56, and I have written a SQL statement that will only
work on 4.04 or later.

Can someone suggest how I can rewrite the following to work on the old
version please?

UPDATE contacts, working SET contacts.lastactivity = working.date  WHERE
contacts.rcd = working.rcd

I have asked my ISP if they have plans to upgrade, but need to get this
system working urgently.

John


-- 
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: Subtracting date fields

[stairwaymail-mysql]

Kenneth,

try

SELECT id, DATEDIFF(firstdate, postdate) AS diff FROM
calendar

RTFM!

hope that helps, dan

-Original Message-
From: Kenneth Letendre
[mailto:[EMAIL PROTECTED]
Sent: Saturday, January 31, 2004 1:51 PM
To: [EMAIL PROTECTED]
Subject: Subtracting date fields


Hello,

   I'm trying to get the difference (in days) between
dates stored in two 
date fields.
   My query:

SELECT id,(firstdate- postdate) AS diff FROM calendar

   This works fine if the two dates are in the same
month, but not 
otherwise.  MySQL appears to be treating the two dates
as base-10 integers 
rather than dates.  E.g.:

2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) =
8876

   How do I get MySQL to treat these date fields as
date fields in this case?


Thanks,

Kenneth


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

Subtracting date fields

[Kenneth Letendre]

Hello,

  I'm trying to get the difference (in days) between dates stored in two 
date fields.
  My query:

SELECT id,(firstdate- postdate) AS diff FROM calendar

  This works fine if the two dates are in the same month, but not 
otherwise.  MySQL appears to be treating the two dates as base-10 integers 
rather than dates.  E.g.:

2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) = 8876

  How do I get MySQL to treat these date fields as date fields in this case?

Thanks,

Kenneth 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Subtracting date fields

[Kenneth Letendre]

Hello,

  I'm trying to get the difference (in days) between dates stored in two 
date fields.
  My query:

SELECT id,(firstdate- postdate) AS diff FROM calendar

  This works fine if the two dates are in the same month, but not 
otherwise.  MySQL appears to be treating the two dates as base-10 integers 
rather than dates.  E.g.:

2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) = 8876

  How do I get MySQL to treat these date fields as date fields in this case?

Thanks,

Kenneth

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

simplify a query?

[kbbabb]

Hi everyone,

If, using MySQL, I have to select 'playerID' from soccer.players based on
lname='smith', fname='john', and dob='1988-12-02' and THEN update
soccer.teams 'coachID' equal to the result of the select statement, can I do
it all in one query?  Or must I let my PHP script run a select, return the
result, and then use that in the update statement?

The scenario is this: I've just created the player record in soccer.players
(it's an adult league, so the players can also be the coach) and need to
grab that playerID to insert into the coachID section of soccer.teams.
Here's the SQL used to create it all if you're wondering about my table structures:

CREATE DATABASE soccer;
USE soccer;
CREATE TABLE `divisions` (
  `divisionID` int(11) NOT NULL auto_increment,
  `division_name` varchar(15) NOT NULL default '',
  UNIQUE KEY `divisionID` (`divisionID`)
) TYPE=MyISAM COMMENT='league division names with identifier'
AUTO_INCREMENT=1 ;

CREATE TABLE `teams` (
  `teamID` int(11) NOT NULL auto_increment,
  `team_name` varchar(20) NOT NULL default '',
  `coachID` int(11) NOT NULL default '0',
  `divisionID` int(11) NOT NULL default '0',
  `paid` enum('y','n') NOT NULL default 'n',
  `pmnt_notes` text,
  `win` int(2) NOT NULL default '0',
  `loss` int(2) NOT NULL default '0',
  `tie` int(2) NOT NULL default '0',
  `goals_for` int(2) NOT NULL default '0',
  `goals_against` int(2) NOT NULL default '0',
  PRIMARY KEY  (`teamID`)
) TYPE=MyISAM COMMENT='Team registration and statistical info'
AUTO_INCREMENT=1 ;

CREATE TABLE `players` (
  `playerID` int(11) NOT NULL auto_increment,
  `lname` varchar(20) NOT NULL default '',
  `fname` varchar(20) NOT NULL default '',
  `teamID` int(11) NOT NULL default '0',
  `dob` date NOT NULL default '-00-00',
  `address` varchar(40) NOT NULL default '',
  `telephone` int(9) NOT NULL default '2147483647',
  `email` varchar(40) NOT NULL default '',
  `sex` enum('m','f') NOT NULL default 'm',
  `yellow` int(2) NOT NULL default '0',
  `red` int(2) NOT NULL default '0',
  `disc_notes` text,
  `goals_scored` int(2) NOT NULL default '0',
  `own_goals` int(2) NOT NULL default '0',
  PRIMARY KEY  (`playerID`)
) TYPE=MyISAM COMMENT='player registration and statistical info'
AUTO_INCREMENT=1 ;

Thanks,

Kirk




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How Do I Do This In Version 3.23.56?

[John Boshier]

I have spent all day working on a PHP/MySQL system offline using MySQL
version 4.0.15-nt. One particular SQL statement that works perfectly
wouldn't work when I uploaded it to my web site. It seems that my ISP
is using version 3.23.56, and I have written a SQL statement that will
only work on 4.04 or later.

Can someone suggest how I can rewrite the following to work on the old
version please?

UPDATE contacts, working SET contacts.lastactivity = working.date  WHERE contacts.rcd 
= working.rcd

I have asked my ISP if they have plans to upgrade, but need to get
this system working urgently.

John


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FULLTEXT Search and Hyphens

[Dan Nelson]

In the last episode (Dec 31), michael elston said:
> I am having some trouble with fulltext search when searching a Table 
> for parts numbers which contain HYPHENS ( - ) and i am 90% sure that is 
> where the problem is.
> 
> My query is:
> SELECT * FROM ms_items
> where MATCH (it_mnfgID, it_title, it_descrip) AGAINST ('*kk-4835*' IN 
> BOOLEAN MODE )
> 
> What i want to be able to accomplish is say your are searching for a 
> "KK-4835"
> 
> I want to be able to type in "KK-4835", "KK4835", or even "KK 4835) and 
> all return the same part. as it stands right now,  searching for even 
> just "kk-" returns nothing  but searching for "4835" will return items 
> with 4835.

Make sure you have changed ft_min_word_len to 2; I bet "KK" never got
indexed at all.

Searching for KK4835 probably won't work, since that's one word and
"KK-4835" gets indexed as "KK" and "4835".  You may have to parse your
search string and split the words up before calling mysql. 
 
-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

C Programing: mysql_free_result

[Rocar Peças]

Dear Friends,

I´m programming in C, Linux envirnment.

The SELECT´s results are being brought to the client by using mysql_store_result. And 
the result sets are being released by mysql_free_result.

The problem is: as mysql_free_result is called, the system memory is NOT actually 
released. 

For example: I can monitor the program size in memory with "top" Linux utility 
(Conectiva 7.0 distribution). As the program is loaded into the memory, its size is, 
say, 1900Kb. Then, a select with a ruge result set is called and uploaded to the 
client. At this time, the program size in memory is 6000Kb. Then mysql_free_result is 
called. The program size in memory does not change. Isn´t it supposed to shrink back 
to 1900Kb?

Thanks, folks!!!

Leandro M Neves,
Rocar Pcs Ltd, IT Manager
Sete Lagoas/MG - BRAZIL

Problem with DELETE/INSERT - even simpler example!

[Richard S. Huntrods]

I've distilled the problem I'm having with DELETE/INSERT to an even 
simpler test case.

Here's the SQL - it can be used with any database, not just the special 
"expdb" I created. I run this script followed by running "mysqlcheck 
expdb".

#
USE expdb;
DROP TABLE IF EXISTS appraised;
CREATE TABLE appraised (
 idNumber int(11) NOT NULL default '0',
 indexNo int(11) NOT NULL default '0',
 experience int(11) default NULL,
 lastused int(11) default NULL,
 competence int(11) default NULL,
 status int(11) default NULL,
 appraiser int(11) default NULL,
 comments text,
 PRIMARY KEY  (idNumber,indexNo)
) TYPE=MyISAM;
PRINT;
DELETE FROM appraised;
INSERT INTO appraised VALUES (1,1,1,1,1,1,1,'aaa1');
SELECT * FROM appraised;
DELETE FROM appraised;
#
If you run this with the INSERT commented out, mysqlcheck reports OK. If 
you don't comment out the INSERT, mysqlcheck reports:

C:\mysql-4.0.15\bin>mysqlcheck expdb
expdb.appraised
warning  : Table is marked as crashed
warning  : Size of indexfile is: 2048  Should be: 1024
warning  : Size of datafile is: 160   Should be: 0
error: Record-count is not ok; is 4   Should be: 0
warning  : Found 4 partsShould be: 0 parts
error: Corrupt
This happens every single time. You cannot use the -autorepair function, 
as it reports "appraised.MYD" cannot be opened.

What is going on?

Thanks,

-Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FULLTEXT Search and Hyphens

[michael elston]

I am having some trouble with fulltext search when searching a Table 
for parts numbers which contain HYPHENS ( - ) and i am 90% sure that is 
where the problem is.

My query is:
SELECT * FROM ms_items
where MATCH (it_mnfgID, it_title, it_descrip) AGAINST ('*kk-4835*' IN 
BOOLEAN MODE )

What i want to be able to accomplish is say your are searching for a 
"KK-4835"

I want to be able to type in "KK-4835", "KK4835", or even "KK 4835) and 
all return the same part. as it stands right now,  searching for even 
just "kk-" returns nothing  but searching for "4835" will return items 
with 4835.

is this a known problem with MySQL or is there a way around it? I am 
half tempted to create another column for keywords.

thanks for any help

-me

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Is it True?

[Hans van Harten]

Dennis Strickland wrote:
> Just started using MYSQL.  And in doing some reading about MYSQL I
> read that MYSQL does not support nested Selects.Is this true?
Current documentation reveals nested selects aka subqueries aka derived
tables aka anonimous views are available as of version 4.1, first alpha
release dated April 3rd 2003:

http://www.mysql.com/doc/en/Rewriting_subqueries.html
http://www.mysql.com/doc/en/Unnamed_views.html

Hans


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Is it True?

[Roger Baklund]

* Dennis Strickland 
> Just started using MYSQL.  And in doing some reading about MYSQL I read
> that MYSQL does not support nested Selects.Is this true?

Depends on your definition...

The most used version (3.23) does not support nested selects.

The current version (4.0) does not support nested selects.

Version 4.1 (Alpha status) and later however _does_ support nested selets:

http://www.mysql.com/doc/en/Subqueries.html >

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Lock Wait Timeout

[Randy Chrismon]

>If you run the app again, does it give errors on the same record again?
>If you move the record in question up or down in the sequence of
>inserts, does it still give errors on that record?

Don't know about the commit. I have set autocommit to 250 but that is
connection by connection and I'm using two different connections. So,
it is possible. On the other hand, both the child and the parent
record already existed in the database -- this is an update rather
than an insert. And, yes, when I re ran the application, it failed on
the same record. I can't move the sequence because I'm actually
exporting data from a collection of unprocessed documents. 
My most recent effort, currently under way, is to change the
autocommit of the parent table connecttion to 1, then see if it
clears
up. I'm still quite uncertain however, since the application
processed
26,000 records before failing on the one. There is nothing
particularly unique or unusual about the one record if failed on
except that it was one of the newer documents. 
Thanks for the thoughts.
Randy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Anger Managment and MySql

[walt]

TriKster Abacus wrote:
> 
> I am trying to get some kind of friggin Quake 3 stat generator to work
> on my Quake 3 servers that I have running.
> Currently I am running a program called Q3Log, which is java based, but
> it seems to have stopped working for some reason. (no log to show
> errors). Anyhow, now my only option is to run a MySql / php backend stat
> generator.
> 


TriKster,
The "mysql.sock" you refer to is what is known in the *nix world as a
named pipe. It is how applications can communicate with each other
without using network sockets. When you get the 

"Can't connect to local MySQL server through socket
'/var/run/mysql/mysql.sock'"

That usually means that mysql is not running. Did you try 
`ps -eaf |grep -i mysql` to see if mysql was running? If it was running,
then you know that the mysql client is not looking in the correct place.
If you do a "man msql" you'll see that you can add a "-S" flag to
specify the socket to use.

The fact that mysql doesn't appear to start can be caused by many things
that aren't really related to mysql. You said you did a "chmod 755" on
the mysql directory. Did you do that as root. If so, how do expect the
"mysql user" to write anything to that directory? 

The first thing you need to do is get mysql running. Try to start mysql
and if you get a "mysqld ended" then oviously it's not running and how
are you going to connect to a database that's not running? Try su -
mysql and then `chmod 755 /var/lib/mysql`. Then try to start mysql again
as the "OS" root user.

walt

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Is it True?

[Jay Blanchard]

[snip]
Just started using MYSQL.  And in doing some reading about MYSQL I read
that MYSQL does not support nested Selects.Is this true?
[/snip]

You mean SUB selects? See

http://www.mysql.com/doc/en/Subqueries.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

French characters ok with mysql in Windows, nok with mysql in Linux

[Willy Dockx]

Hello,

 

I have  made a website using jboss, Hibernate, mysql 4.0.16 and
mysql-connector-java-3.0.9. 

 

In development this is installed on Windows2000 and everything works fine, also
when the user inputs characters like é, à, è, …

 

In production this is installed on Linux (RedHat 8). There also everything works
fine, except for the strange characters é, à, è.

 

I suppose the reason is that RedHat uses UTF-8 as encoding.

 

I’ve tried to put ‘useUnicode=true&characterEncoding=UTF-8’ in the driver
connection url, but this doesn’t help.

 

Can anyone help me on this problem?

 

Greetings,

 

Willy Dockx

 

Is it True?

[Dennis Strickland]

Just started using MYSQL.  And in doing some reading about MYSQL I read
that MYSQL does not support nested Selects.Is this true?

Happy new year

[Director General: NEFACOMP]

Just to wish you a prosperous new Year.
My wishes go to the special guys of the MySQL and related software development team[s].
May the coming year bring you happiness and more innovative and productive ideas. 
May your families be the happiest on this earth! 
May this year protect you against homosexuality!


Thanks,
__
NZEYIMANA Emery Fabrice
NEFA Computing Services, Inc.
P.O. Box 5078 Kigali
Office Phone: +250-51 11 06
Office Fax: +250-50 15 19
Mobile: +250-08517768
Email: [EMAIL PROTECTED]
http://www.nefacomp.net/

RE: MySQL on RAID server????

[Mechain Marc]

You can afterwards the installation specify the path to the data in the "my.cnf" 
config file.

Marc.

-Message d'origine-
De : Kirti S. Bajwa [mailto:[EMAIL PROTECTED]
Envoyé : mercredi 31 décembre 2003 01:08
À : [EMAIL PROTECTED]
Objet : MySQL on RAID server


Hello List:

Here comes a newbie's newbie!!

I want to install MySQL 5.0.0 on a machine with RAID. It has the following
hardware/Software configuration:

DISTRO: RH9
1-Disk which has all the software installed.
2-Disks, which are RAID1 format with one folder "/data"

I plan to use the RAID for data only. I am trying to install Source
5.0.0-alpha version of MySQL. I am not sure how to go about installing. I
have two chain of thought:

(1) Install MySQL at "/usr/local" as suggested in the Source installation
directive, but install the MySQL data on the RAID disk ("/data" folder) -or-

(2) Install the entire MySQL package on the RAID disk.

If my explanation needs better explanation, I will be more than happy to do
it. I prefer the (1) method but I have no idea how I can install just data
on the RAID disk? I think the "./configure" statement needs additional
directive, but what?

I have reviewed the information on the MySQL web site under Source
installation page. There are 4-5 notes from people. Please shoe me the way.

Thanks in advance.

Kirti

-- 
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: COBOL Syntax of calling MySQL's C API

[Patrick Sherrill]

BTW the error also indicates your client is trying to connect to the
localhost i.e. the same machine you are connecting from. Try using the IP
address of the server instead of the servername.  This should also be a
string (eg. "123.123.123.123").

You most likely have a parameter/data type problem.  The parameters being
passed from your COBOL program, are not getting to the 'C' routine as the
correct data types or parameters (remember NULL is not zero).

Pat...


- Original Message - 
From: "Arunachalam" <[EMAIL PROTECTED]>
To: "Patrick Sherrill" <[EMAIL PROTECTED]>
Sent: Wednesday, December 31, 2003 4:22 AM
Subject: Re: COBOL Syntax of calling MySQL's C API


> hi,
>
> >From COBOL I have tried to Connect to MySQL using the C API functions
given by MySQL. Finally I
> struck up with the error during runtime as "Can't connect to MySQL server
on localhost (10061)"
>
> I have a doubt Is, we have to start explicitly MySQL in the server
machine?  i.e., mysqld.
>
> If so with out start the mysqld in the server I can able to connect to the
server using MySQLCC,
> is an utility tool provided by MySQL and MySQLExplorer is a free utility
tool provided by
> ToolMagic softwares. More than this I can able to connect to MySQL server
and fetch the data using
> simple C++ program using C API of MySQL. I believe, no one do start the
MySQL in my server.
>
> I am running my application in Windows2000 machine (client), myserver is
at Linux machine
> (server).
>
> So what I can to do?.
>
> Arun.
>
>  --- Patrick Sherrill <[EMAIL PROTECTED]> wrote: > Being COBOL
illiterate, I may not be able to
> help.
> >
> > Two questions come to mind in reviewing the parameters you are passing.
> >
> > Are the parameters by reference passed as char-like pointers and the
> > parameters by content passed as integers? If they are, then the only
issue I
> > see as a possibility is that 'fMySQLSocket'  should be passed as NULL
not 0.
> >
> > Also make sure mysqld is running on 'myserver'.
> >
> > I hope this helps...
> >
> > Pat...
> >
> >
> > BTW replies to the list usually yield better results.
> > 
> >
> > - Original Message - 
> > From: "Arunachalam" <[EMAIL PROTECTED]>
> > To: "Patrick Sherrill" <[EMAIL PROTECTED]>
> > Sent: Tuesday, December 30, 2003 8:44 AM
> > Subject: COBOL Syntax of calling MySQL's C API
> >
> >
> > > Hello Patrick,
> > >
> > > The actual calling routines in C is;
> > >
> > > MYSQL *mysql_real_connect(MYSQL *mysql,
> > > const char *host,
> > > const char *user,
> > > const char *passwd,
> > > const char *db,
> > > unsigned int port,
> > > const char *unix_socket,
> > > unsigned long client_flag)
> > >
> > > I have declared the variables with values are;
> > >
> > > 05 C-cMySQLHostName  pic  x(9).
> > >   88 fMySQLHostName value 'myserver'.
> > > 05 C-cMySQLUserIdpic  x(4).
> > >   88 fMySQLUserId   value 'arun'.
> > > 05 C-cMySQLPassword  pic  x(4).
> > >   88 fMySQLPassword value 'arun'.
> > > 05 C-cMySQLDBNamepic  x(6).
> > >   88 fMySQLDBName   value 'MyDBMS'.
> > > 05 C-cMySQLPort  pic  x(4).
> > >   88 fMySQLPort value '3306'.
> > > 05 C-cMySQLSocketpic  x(8).
> > >   88 fMySQLSocket   value '0'.
> > > 05 C-cMySQLFlag  pic  x(8).
> > >   88 fMySQLFlag value '0'.
> > >
> > >
> > > It's equivalent COBOL coding for the C coding syntax is;
> > >
> > > CALL "C_mysql_real_connect"
> > > using by reference E-ptrSQLConnect
> > > by reference C-cMySQLHostName
> > > by reference C-cMySQLUserId
> > > by reference C-cMySQLPassword
> > > by reference C-cMySQLDBName
> > > by content C-cMySQLPort
> > > by reference C-cMySQLSocket
> > > by content C-cMySQLFlag
> > > giving E-ptrSQLEnv
> > >
> > > I have tested that before reaching this Calling portion all the
variables
> > hold the values what I
> > > have set earlier. but after execution it stores the error as it's
value
> > (i.e., "Can't connect to
> > > MySQL server on localhost (10061) ") and the pointer variable
> > E_ptrSQLConnect hold value 0.
> > >
> > > Before invoking this Call I have invoked the "C_mysql_init" funtion
and
> > get the pointer value in
> > > E_ptrSQLConnect.
> > >
> > > I could't get where is the possibility of error occurence exist... :(
> > >
> > > Arun.
> > >
> > >
> > >  --- Patrick Sherrill <[EMAIL PROTECTED]> wrote: > What is the
syntax of
> > your call to connect to
> > > the MySQL server?
> > > > It seems as though no server parameters are being passed, thereby
> > defaulting
> > > > to the local machine which apparently has no  mysql server running.
> > > >
> > > > Pat...
> > > >
> > > > CocoNet Corporation
> > > > SW Florida's First ISP
> > > >
> > > >
> > > >
> > > > - Original Message - 
> > > > From: "Arunachalam" <[EMAIL PROTECTED]>
> > > > To: <[EMAIL PROTECTED]>
> > > > Sent: 

Re: COBOL Syntax of calling MySQL's C API

[Patrick Sherrill]

Arun,

Please reply to the list so all may help/benefit from your question.

The mysql server program/daemon must be running on the server prior to any
client access.
If you are able to access the mysql server from any client than the server
is running.

For example if your linux server's resolvable domain name is
'mylinuxbox.generic.com', then mysqld must be running on
mylinuxbox.generic.com before any mysql client can connect.

If you can connect from another client program, but not your COBOL program,
then you need to look at how your COBOL program treats the data types passed
to your 'C' call.  As I mention previously, after confirming data types
being passed you need to pass NULL, not '0' as the unix_socket.  NULL is not
zero.

Normally the error you are reporting indicates the server is not running or
not accepting connections.

Check your parameters and data types for mysql_real_connect and ensure the
server you are attempting to connect to is in fact running and accepting
connections.

Also don't forget to reply to the list.

Pat...


- Original Message - 
From: "Arunachalam" <[EMAIL PROTECTED]>
To: "Patrick Sherrill" <[EMAIL PROTECTED]>
Sent: Wednesday, December 31, 2003 4:22 AM
Subject: Re: COBOL Syntax of calling MySQL's C API


> hi,
>
> >From COBOL I have tried to Connect to MySQL using the C API functions
given by MySQL. Finally I
> struck up with the error during runtime as "Can't connect to MySQL server
on localhost (10061)"
>
> I have a doubt Is, we have to start explicitly MySQL in the server
machine?  i.e., mysqld.
>
> If so with out start the mysqld in the server I can able to connect to the
server using MySQLCC,
> is an utility tool provided by MySQL and MySQLExplorer is a free utility
tool provided by
> ToolMagic softwares. More than this I can able to connect to MySQL server
and fetch the data using
> simple C++ program using C API of MySQL. I believe, no one do start the
MySQL in my server.
>
> I am running my application in Windows2000 machine (client), myserver is
at Linux machine
> (server).
>
> So what I can to do?.
>
> Arun.
>
>  --- Patrick Sherrill <[EMAIL PROTECTED]> wrote: > Being COBOL
illiterate, I may not be able to
> help.
> >
> > Two questions come to mind in reviewing the parameters you are passing.
> >
> > Are the parameters by reference passed as char-like pointers and the
> > parameters by content passed as integers? If they are, then the only
issue I
> > see as a possibility is that 'fMySQLSocket'  should be passed as NULL
not 0.
> >
> > Also make sure mysqld is running on 'myserver'.
> >
> > I hope this helps...
> >
> > Pat...
> >
> >
> > BTW replies to the list usually yield better results.
> > 
> >
> > - Original Message - 
> > From: "Arunachalam" <[EMAIL PROTECTED]>
> > To: "Patrick Sherrill" <[EMAIL PROTECTED]>
> > Sent: Tuesday, December 30, 2003 8:44 AM
> > Subject: COBOL Syntax of calling MySQL's C API
> >
> >
> > > Hello Patrick,
> > >
> > > The actual calling routines in C is;
> > >
> > > MYSQL *mysql_real_connect(MYSQL *mysql,
> > > const char *host,
> > > const char *user,
> > > const char *passwd,
> > > const char *db,
> > > unsigned int port,
> > > const char *unix_socket,
> > > unsigned long client_flag)
> > >
> > > I have declared the variables with values are;
> > >
> > > 05 C-cMySQLHostName  pic  x(9).
> > >   88 fMySQLHostName value 'myserver'.
> > > 05 C-cMySQLUserIdpic  x(4).
> > >   88 fMySQLUserId   value 'arun'.
> > > 05 C-cMySQLPassword  pic  x(4).
> > >   88 fMySQLPassword value 'arun'.
> > > 05 C-cMySQLDBNamepic  x(6).
> > >   88 fMySQLDBName   value 'MyDBMS'.
> > > 05 C-cMySQLPort  pic  x(4).
> > >   88 fMySQLPort value '3306'.
> > > 05 C-cMySQLSocketpic  x(8).
> > >   88 fMySQLSocket   value '0'.
> > > 05 C-cMySQLFlag  pic  x(8).
> > >   88 fMySQLFlag value '0'.
> > >
> > >
> > > It's equivalent COBOL coding for the C coding syntax is;
> > >
> > > CALL "C_mysql_real_connect"
> > > using by reference E-ptrSQLConnect
> > > by reference C-cMySQLHostName
> > > by reference C-cMySQLUserId
> > > by reference C-cMySQLPassword
> > > by reference C-cMySQLDBName
> > > by content C-cMySQLPort
> > > by reference C-cMySQLSocket
> > > by content C-cMySQLFlag
> > > giving E-ptrSQLEnv
> > >
> > > I have tested that before reaching this Calling portion all the
variables
> > hold the values what I
> > > have set earlier. but after execution it stores the error as it's
value
> > (i.e., "Can't connect to
> > > MySQL server on localhost (10061) ") and the pointer variable
> > E_ptrSQLConnect hold value 0.
> > >
> > > Before invoking this Call I have invoked the "C_mysql_init" funtion
and
> > get the pointer value in
> > > E_ptrSQLConnect.
> > >
> > > I could't get where is the possibility of error occ

Re: Create Tables In MySQL

[Martijn Tonies]

Hi Caroline,


> Hi, I have several simple questions regarding creating
> tables in the MySQL.
>
> 1. For a variable of Java primitive int type, I should
> use INT or INTEGER? Do I have to specify the length of
> the field?
> CREATE TABLE message_thread( thread_id INT or INTEGER
> NOT NULL AUTO_INCREMENT PRIMARY KEY);

There's no difference in INT or INTEGER - see
http://www.mysql.com/doc/en/Column_types.html

> 2. A field in my table is for storing articles.
> Articles could be very long in variable length. I
> should use VARCHAR or TEXT? What about the length of
> the field?

VARCHAR has a maximum length of 255 characters, although I
think this is increased in a later MySQL version. If you want to
store "unlimited" amounts of text, use a blob (or blob like) column
type, eg: MEDIUMTEXT or LONGTEXT
See http://www.mysql.com/doc/en/Column_types.html for details.

> 3. For a variable of the Timestamp type, do I specify
> CREATE TABLE message_thread( thread_creation_date
> TIMESTAMP NOT NULL );
> What about the length of the field?

To store date/time values, use the DATETIME type. See
http://www.mysql.com/doc/en/DATETIME.html

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb defragmentation question

[Franky Van Liedekerke]

the problem is that, if it happens again, I get a file of 900 MB, which 
gets kinda big ... In order to rectify the situation after that, I'll 
need to dump all innodb tables, drop them and reinsert them. This would 
takes hours, and in the meantime the application running on top of it 
would be down ...

Anyway, here's the output of "show table status" and "show innodb 
status" for the corresponding tables. Maybe you can tell me how to check 
if defrag is even needed?

| history | InnoDB | Fixed  | 6132057 | 50 |   
310378496 |NULL |0 | 0 |   NULL 
| NULL| NULL| NULL   
|| InnoDB free: 8192 kB
| users_groups| InnoDB | Fixed  |   0 |  0 
|   16384 |NULL |0 | 0 |   
NULL | NULL| NULL| NULL   
|| InnoDB free: 8192 kB |
| usrgrp  | InnoDB | Dynamic|   7 |   2340 
|   16384 |NULL |16384 | 0 
|  8 | NULL| NULL| 
NULL   || InnoDB free: 8192 kB

And the "show innodb status":
=
031231  9:31:03 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 23 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 269898, signal count 269639
Mutex spin waits 164682, rounds 1645099, OS waits 81402
RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679

TRANSACTIONS

Trx id counter 0 7556158
Purge done for trx's n:o < 0 2341232 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 150765
MySQL thread id 150754, query id 42886888 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 7556157, not started, OS thread id 55
MySQL thread id 44, query id 42886887 localhost root

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 144, node heap has 1 buffer(s)
0.96 hash searches/s, 3.35 non-hash searches/s
---
LOG
---
Log sequence number 0 395581663
Log flushed up to   0 395581663
Last checkpoint at  0 395580831
0 pending log writes, 0 pending chkp writes
6292464 log i/o's done, 4.00 log i/o's/second
--
--
BUFFER POOL AND MEMORY
--
Total memory allocated 17823008; in additional pool allocated 719232
Buffer pool size   512
Free buffers   0
Database pages 511
Modified db pages  14
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 110330, created 17200, written 6223975
0.00 reads/s, 0.00 creates/s, 4.30 writes/s
Buffer pool hit rate 1000 / 1000
--
ROW OPERATIONS
--
0 queries inside InnoDB, 0 queries in queue
Main thread id 10, state: sleeping
Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877
3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s

END OF INNODB MONITOR OUTPUT



F.

Heikki Tuuri wrote:

Franky,

since MySQL performs

ALTER TABLE ... TYPE = InnoDB;

by totally rebuilding the table, it is very normal that the space usage
temporarily doubles in ibdata files.
But if it doubles also after an immediate SECOND rebuild, then that must be
a bug. If you can reproduce that phenomenon, please send me the following
before and after each step:
1) SHOW TABLE STATUS;
2) SHOW INNODB STATUS;
3) ls -l in the datadir,
4) and what the following prints to the .err log:
CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB;

DROP TABLE innodb_table_monitor;
Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete
the old .ibd file after the rebuild, and the disk space is freed to the OS.
Thus, multiple tablespaces help in your problem.
Note also that the big transaction which builds the new table will also use
some 20 bytes per row in the undo logs in the system tablespace, that is,
the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The
space in undo logs is freed within the ibdata files, and can be u