date:20061016

schem help...

2006-10-16 Thread Payne


Hi,

I need some help. I  got a 161 tables that I am trying to create schem 
on, the problem is I can get any of my boxes to show you know


+---+
|  |
+---+

Here is the command I am doing

my -Ae show tables; | while read a ; do my -Ae desc $a; echo  
myfile.txt ; done


what I get the desc with out the boxes.

Can someone help me? I need the box because I am going to cut these 
tables out so I can map out the relatation to them.


Thanks,

Payne

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

group by problem

2006-10-16 Thread kalin mintchev


 hi all...

 i have an issue with group by and ordering. apparently group by ignores
'order by id DESC'?!

 an example is a table that has an id and a category fields. there are a
few categories under which records can be filed. so what i want is the
latest record from each category by doing something like:

select id, name from table group by category order by id DESC;

this doesn;t work - it shows me the first record under each category - not
the latest as specified by DESC?! something is wrong.

i tried 'distinct' but that 'distincts' on all fields in the query?!?! 
whats the point of distinct if it can not distincts between fields?! in:

select distinct category, id, name from table order by id DESC;

this query distincts on all category, id and name when it should distinct
only on category

how do i do that without temporary tables?

thanks...



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

Re: group by problem

2006-10-16 Thread Chris


kalin mintchev wrote:

 hi all...

 i have an issue with group by and ordering. apparently group by ignores
'order by id DESC'?!

 an example is a table that has an id and a category fields. there are a
few categories under which records can be filed. so what i want is the
latest record from each category by doing something like:

select id, name from table group by category order by id DESC;

this doesn;t work - it shows me the first record under each category - not
the latest as specified by DESC?! something is wrong.

i tried 'distinct' but that 'distincts' on all fields in the query?!?! 
whats the point of distinct if it can not distincts between fields?! in:


select distinct category, id, name from table order by id DESC;

this query distincts on all category, id and name when it should distinct
only on category

how do i do that without temporary tables?


You misunderstand group by  distinct and what they do.

If you have some records:

name - chris
name - mary
name - fred
name - chris

and run something like this:

select name, count(*) from table group by name;

it will return:
chris - 2
mary - 1
fred - 1

*then* if you have an order by mysql will order the results that are 
returned by that query:


select name, count(*) from table group by name order by name desc;

it will return:
mary - 1
fred - 1
chris - 2


Basically you can't do what you want either without temporary tables or 
using a subselect. Subselects are only available in mysql 4.1+ (I think 
- check the docs) so that may or may not be an option.


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

Re: Re: Re: Re: Readind a Dump W/o Expanding It

2006-10-16 Thread Ted Johnson

8080The data field's a text field, not a blob! That's the problem, there are no 
blobs, and I just wanted a second set of eyes on this to confirm what simply 
makes no sense at all! I'd love to know where the dickens those images went!!

Thanks anyway,
Ted

- Original Message 
From: Dan Buettner [EMAIL PROTECTED]
To: Ted Johnson [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Sunday, October 15, 2006 8:45:07 PM
Subject: Re: Re: Re: Re: Readind a Dump W/o Expanding It

Ted - sorry, not sure what this is showing me.  Is the image data
supposed to be in the data field in the Photo table?  It's not
there, or it's corrupt?

Dan

On 10/15/06, Ted Johnson [EMAIL PROTECTED] wrote:

 74
 - Original Message 
 From: Dan Buettner [EMAIL PROTECTED]
 To: Ted Johnson [EMAIL PROTECTED]
 Cc: mysql@lists.mysql.com
 Sent: Sunday, October 15, 2006 9:22:04 AM
 Subject: Re: Re: Re: Readind a Dump W/o Expanding It

 How were they stored in the database?

 You got me on that. I just took over this box. Here's what my queries show:

 server167# mysqlshow -uroot -p tirekingdom
 Enter password:
 Database: tirekingdom
 ++
 |   Tables   |
 ++
 | OrderItem  |
 | Orders |
 | Photo  |
 | PhotosForTire  |
 | PhotosForWheel |
 | Shopper|
 | Tire   |
 | Vehicle|
 | Wheel  |
 | WheelMake  |
 | WheelsForWheelMake |
 ++
 server167# mysqlshow tirekingdom Photo -uroot -p
 Enter password:
 Database: tirekingdom  Table: Photo  Rows: 2160
 +---+-+---+--+-+-+---+-+-+
 | Field | Type| Collation | Null | Key | Default | Extra |
 Privileges  | Comment |
 +---+-+---+--+-+-+---+-+-+
 | oid   | int(11) | NULL  |  | PRI | 0   |   |
 select,insert,update,references | |
 | Data  | mediumtext  | latin1_swedish_ci | YES  | | |   |
 select,insert,update,references | |
 | Type  | varchar(4)  | latin1_swedish_ci | YES  | | |   |
 select,insert,update,references | |
 | Name  | varchar(32) | latin1_swedish_ci | YES  | | |   |
 select,insert,update,references | |
 +---+-+---+--+-+-+---+-+-+
 server167# mysqlshow tirekingdom PhotoForTires -uroot -p
 Enter password:
 mysqlshow: Cannot list columns in db: tirekingdom, table: PhotoForTires:
 Table 'tirekingdom.PhotoForTires' doesn't exist
 server167# mysqlshow -uroot -p tirekingdom PhotosForTire
 Enter password:
 Database: tirekingdom  Table: PhotosForTire  Rows: 1058
 +-+-+---+--+-+-+---+-+-+
 | Field   | Type| Collation | Null | Key | Default | Extra |
 Privileges  | Comment |
 +-+-+---+--+-+-+---+-+-+
 | TireID  | int(11) | NULL  |  | PRI | 0   |   |
 select,insert,update,references | |
 | PhotoID | int(11) | NULL  |  | PRI | 0   |   |
 select,insert,update,references | |
 +-+-+---+--+-+-+---+-+-+
 server167# mysqlshow -uroot -p tirekingdom PhotosForWheel
 Enter password:
 Database: tirekingdom  Table: PhotosForWheel  Rows: 1098
 +-+-+---+--+-+-+---+-+-+
 | Field   | Type| Collation | Null | Key | Default | Extra |
 Privileges  | Comment |
 +-+-+---+--+-+-+---+-+-+
 | WheelID | int(11) | NULL  |  | PRI | 0   |   |
 select,insert,update,references | |
 | PhotoID | int(11) | NULL  |  | PRI | 0   |   |
 select,insert,update,references | |
 +-+-+---+--+-+-+---+-+-+

 So, where's the blob? It isn't in some file in the site, I've checked.

 Which way did you end up restoring the data?

 The first way you recommended. Using vi, I opened the file, set ma at the
 top, searched for the top of the database, entered d`a to delete
 everything above, searched for the bottom of the database, set ma, entered
 G to go to the bottom, then d`a to delete everything else. It was a little
 more complicated than that, but with those steps involved.

 And the dumpfile you have - how has it been handled?  I ask because if it
 contains binary data and was FTP'd from one machine to another the wrong
 way, that could

Re: group by problem

2006-10-16 Thread kalin mintchev



 Basically you can't do what you want either without temporary tables or
 using a subselect. Subselects are only available in mysql 4.1+ (I think
 - check the docs) so that may or may not be an option.

thanks... pardon my ignorance - how would i do that using subselects?






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

Re: group by problem

2006-10-16 Thread Chris


kalin mintchev wrote:

Basically you can't do what you want either without temporary tables or
using a subselect. Subselects are only available in mysql 4.1+ (I think
- check the docs) so that may or may not be an option.


thanks... pardon my ignorance - how would i do that using subselects?


Without knowing your schema this is just a guess but:

select * from records where categoryid=(select categoryid from 
categories order by last_update desc limit 1);


will get the most recently updated category and match it to the 
'records' table.


Actually... That could probably be done as a join:

select * from records r inner join categories c on 
(r.categoryid=c.categoryid) order by c.last_update desc;


but that will get you all records for that category not just the most 
recently updated.



Of course I could be on the completely wrong track because you haven't 
fully described what you're trying to get out and what data you have ;)


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

Deadlock avoidance

2006-10-16 Thread Marcus Bointon

I've seen occasional deadlocks reported, and I'm wondering how I  
should avoid them, or if my approach is just wrong.


I have two client processes running in PHP on different machines  
accessing the same database (all InnoDB on MySQL 5.0.24a). Each has  
an integer daemon_id which is used to grab a chunk of a list of tasks  
which it can take away and process before coming back for more. The  
task grabbing process goes like this (it has failure detection and  
rollback if necessary):


BEGIN;
UPDATE task SET daemon_id = $my_daemon_id where daemon_id = 0 and  
status = 'new' LIMIT 100;

COMMIT;

Then it grabs the tasks it has claimed:

SELECT * FROM task WHERE daemon_id = $my_daemon_id AND status = 'new';

These tasks should never be locked by another daemon because the  
daemon_id doesn't match.


As each process completes its tasks, it issues single updates:

UPDATE task SET status = 'complete' where id = 123;

In theory, this mechanism is intended to allow me to add more client  
processes to increase capacity without them treading on each other.


I don't see where the deadlock can occur, but there is one in the  
initial task grab apparently. If the two processes try to grab the  
same tasks at once, I would expect the later of the two to fail to do  
the update, or simply fail to find tasks in common because the  
transaction is atomic. Have I got this all wrong? Do I need to be  
more explicit about locking during the update? Is there a better way  
of doing this?


Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
[EMAIL PROTECTED] | http://www.synchromedia.co.uk/



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

Re: group by problem

2006-10-16 Thread kalin mintchev



 but that will get you all records for that category not just the most
 recently updated.


that's the main problem, isn't it?

what i'm looking for is the last record for EACH of the categories in the
table. i'm aware of the aformentioned options.

my problem with group by is that ignores the DESC in order by. if i do:

select id from table group by category;

it does the same as if i do:

select id from table group by category order by id desc;

both queries return the FIRST found record for EACH category in ascending
order - the order they were indexed in. i geuss i still don't get why if i
request 'order by id desc' the 'group by' doesn't look 'backwards'.
obviuosly the 'grouping' comes before the 'ordering' in the query - it
probably is executed that way too. why can't it be the other way around
taking in consideration the request of ordering the results in descending
order and then grouping them by category

i guess the answer is - just because...




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

Re: group by problem

2006-10-16 Thread chris smith


On 10/16/06, kalin mintchev [EMAIL PROTECTED] wrote:



 but that will get you all records for that category not just the most
 recently updated.


that's the main problem, isn't it?

what i'm looking for is the last record for EACH of the categories in the
table. i'm aware of the aformentioned options.

my problem with group by is that ignores the DESC in order by. if i do:

select id from table group by category;

it does the same as if i do:

select id from table group by category order by id desc;

both queries return the FIRST found record for EACH category in ascending
order - the order they were indexed in. i geuss i still don't get why if i
request 'order by id desc' the 'group by' doesn't look 'backwards'.
obviuosly the 'grouping' comes before the 'ordering' in the query - it
probably is executed that way too. why can't it be the other way around
taking in consideration the request of ordering the results in descending
order and then grouping them by category


The order by orders the *results* of the group by. It does not
affect what happens inside the group by.

This is standard across all databases - mysql, postgres, sqlite - all of them.

It can't be done the other way because grouping the results together
will affect ordering (think of aggregate functions especially).

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

How to get into mysql command line?

2006-10-16 Thread Cornelia Menzel


Hello!

I have installed MySQL 4.1.21 on my iBook which is running Mac OS X  
(Tiger 10.4.8). I have installed MySQL without any problems, I  
activated PHP and CGI-Scripts in httpd.conf and erverything was fine.  
With 'mysql' I have been able to log in to the mysql command line.


Unfortunately, I thought it would be better to remove the user  
account with no username and set a password to root. I removed the  
user account with no username and everything was fine. Then I tried  
to set a password for root, following the manual:


http://dev.mysql.com/doc/refman/4.1/en/default-privileges.html

shell mysql -u root
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_PASSWORD');
mysql SET PASSWORD FOR 'root'@'cornelia-menzels-ibook-g4.local' =  
PASSWORD('MY_PASSWORD');


Unfortunately, I've got a message, that '0 rows were affected ...'. I  
was surprised and tried it with 'mysqladmin':


shell mysqladmin -u root password MY_PASSWORD
shell mysqladmin -u root -h localhost password MY_PASSWORD

Since then I have not been able to log in to the mysql command line.  
I tried to reset the root password following the manual, but no success:

http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html

The error message I receive is the following:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using  
password: NO)


Is there somebody who can help me with this? I tried dot remove the  
mysql installation. In terminal I removed the /usr/local/mysql  
directory, but when I reinstall, I encounter the same problems. If  
there is somebody who has a hint how to remove the mysql install  
completely, that would be usefull to be able to install mysql again  
and leave it without password insecure. But at least the installation  
would collaborate.


With Linux it was pretty easy setting up a local test server, but  
with Mac OS X I was not succesfull till now. Unbelievable.


Kind regards,
Cornelia


-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com

Re: How to get into mysql command line?

2006-10-16 Thread Dominik Klein


The error message I receive is the following:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using 
password: NO)


This message indicates you tried to login without giving a password.

Add -p to your commandline. It will then prompt you for your password 
and log you in if the password is correct.


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

Re: How to get into mysql command line?

2006-10-16 Thread Cornelia Menzel



Am 16.10.2006 um 13:00 schrieb Dominik Klein:


The error message I receive is the following:
ERROR 1045 (28000): Access denied for user  
'root'@'localhost' (using password: NO)


This message indicates you tried to login without giving a password.

Add -p to your commandline. It will then prompt you for your  
password and log you in if the password is correct.


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




Unfortunately, that is not the reason, why I get this message. I have  
tried 'mysql', 'mysql -uroot', mysql -u root', 'mysql -uroot -p',  
'mysql -uroot -pMY_PASSWORD', but anything fails.


When I am using the password option, the error message is like this:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using  
password: YES)


Any ideas?

-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com

Re: How to get into mysql command line?

2006-10-16 Thread Dominik Klein

Unfortunately, that is not the reason, why I get this message. I have 
tried 'mysql', 'mysql -uroot', mysql -u root', 'mysql -uroot -p', 'mysql 
-uroot -pMY_PASSWORD', but anything fails.


When I am using the password option, the error message is like this:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using 
password: YES)


Any ideas?


Did you restart the server after you set the password? Did you execute 
flush privileges?


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

Re: How to get into mysql command line?

2006-10-16 Thread Cornelia Menzel



Am 16.10.2006 um 13:08 schrieb Dominik Klein:

Unfortunately, that is not the reason, why I get this message. I  
have tried 'mysql', 'mysql -uroot', mysql -u root', 'mysql -uroot - 
p', 'mysql -uroot -pMY_PASSWORD', but anything fails.

When I am using the password option, the error message is like this:
ERROR 1045 (28000): Access denied for user  
'root'@'localhost' (using password: YES)

Any ideas?


Did you restart the server after you set the password? Did you  
execute flush privileges?


Yes, I did, but it did not work. I have no idea, what the problem is.  
And I cannot understand, why I cannot login after having reset the  
root password.


-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com

Re: How to get into mysql command line?

2006-10-16 Thread Anders Karlsson

The error shows that the passwords doesn't match. You just must have 
made some mistake when resetting the password. But this can be fixed:
1) You can start the server with the --skip-grant-tables option that 
disables password checking,  then you log in as root, set the password, 
and the restart the server without the --skip-grant-tables.

or
2) Use the --init-file option to run a file that resets the password. 
Bith methods are described here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
It is a rare occurence, but the problem might also be this:
http://dev.mysql.com/doc/refman/5.0/en/password-too-long.html

Best regards
Anders Karlsson
Cornelia Menzel wrote:


Am 16.10.2006 um 13:08 schrieb Dominik Klein:

Unfortunately, that is not the reason, why I get this message. I 
have tried 'mysql', 'mysql -uroot', mysql -u root', 'mysql -uroot 
-p', 'mysql -uroot -pMY_PASSWORD', but anything fails.

When I am using the password option, the error message is like this:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using 
password: YES)

Any ideas?


Did you restart the server after you set the password? Did you 
execute flush privileges?


Yes, I did, but it did not work. I have no idea, what the problem is. 
And I cannot understand, why I cannot login after having reset the 
root password.


-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com






--
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /  Anders Karlsson ([EMAIL PROTECTED])
 / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/  /_/\_, /___/\___\_\___/ Stockholm
   ___/   www.mysql.com Cellphone: +46 708 608121



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

Re: How to get into mysql command line?

2006-10-16 Thread Cornelia Menzel



Am 16.10.2006 um 13:23 schrieb Anders Karlsson:

The error shows that the passwords doesn't match. You just must  
have made some mistake when resetting the password. But this can be  
fixed:
1) You can start the server with the --skip-grant-tables option  
that disables password checking,  then you log in as root, set the  
password, and the restart the server without the --skip-grant-tables.

or
2) Use the --init-file option to run a file that resets the  
password. Bith methods are described here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
It is a rare occurence, but the problem might also be this:
http://dev.mysql.com/doc/refman/5.0/en/password-too-long.html

Best regards
Anders Karlsson


Thank you Anders, I have tried this already. But I will do it again,  
perhaps I have more luck now.


Thank you.

Best regards,
Cornelia

-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com

Re: How to get into mysql command line?

2006-10-16 Thread Anders Karlsson

Come to think of it, might it be that you are using an old mysql client, 
possibly one that exists as part of an old installation of MySQL or in 
the case of Linux, one that was installed as part of the Linux distro 
installation?
Some of these older versions of MySQL Client does not support the more 
secure password encryption methods used by newer MySQL versions, and you 
will get just the errors that you are getting.
So if you are using Linux, do a which mysql and see what mysql client 
you are using. Also do a mysql --version which will show the command 
line client version. You have to watch for this if the client has 
version 4.0 or earlier, and you have server with version 4.1 or higher, 
then this is surely the problem you are experiencing.

Read more here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

Best regards
Anders Karlsson
Cornelia Menzel wrote:


Am 16.10.2006 um 13:23 schrieb Anders Karlsson:

The error shows that the passwords doesn't match. You just must have 
made some mistake when resetting the password. But this can be fixed:
1) You can start the server with the --skip-grant-tables option that 
disables password checking,  then you log in as root, set the 
password, and the restart the server without the --skip-grant-tables.

or
2) Use the --init-file option to run a file that resets the password. 
Bith methods are described here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
It is a rare occurence, but the problem might also be this:
http://dev.mysql.com/doc/refman/5.0/en/password-too-long.html

Best regards
Anders Karlsson


Thank you Anders, I have tried this already. But I will do it again, 
perhaps I have more luck now.


Thank you.

Best regards,
Cornelia

-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com






--
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /  Anders Karlsson ([EMAIL PROTECTED])
 / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/  /_/\_, /___/\___\_\___/ Stockholm
   ___/   www.mysql.com Cellphone: +46 708 608121



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

Re: schem help...

2006-10-16 Thread Rolando Edwards

The mysql client program never generates the fixed length, pipe layout from the 
shell's command line.
This is only possible at mysql's command line.

1) Try this:

my -Ae show tables; | while read a ; do my -Ae show create table $a; echo 
 
myfile.txt ; done 

2) Try mysqldump in this manner

mysqldump --no-data [database]  myfile.txt

This will export the schema only using 'DROP TABLE IF EXISTS ...'
and 'CREATE TABLE ...' syntax for every table in a given database.
If database is not specified, the schema of every database, and the
'CREATE TABLE ...' for all tables per schema are exported.

3) Try using the INFORMATION_SCHEMA database

Create a SQL script (myschema.sql) using the following SQL command in it

select CONCAT('show create table `',t.table_schema,'`.`',t.table_name,'`;')
from INFORMATION_SCHEMA.tables t where t.table_schema = 'database';

If you are doing all databases, try this:

select CONCAT('show create table `',t.table_schema,'`.`',t.table_name,'`;')
from INFORMATION_SCHEMA.tables t order by t.table_schema,t.table_name;

Then, from the shell, do this:

mysql  myschema.sql  myfile.txt



All of these solutions will generate the SQL for schema creation.
Try these out. I hope they help.

Have a good day.

- Original Message -
From: Payne [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, October 16, 2006 2:20:55 AM GMT-0500 US/Eastern
Subject: schem help...

Hi,

I need some help. I  got a 161 tables that I am trying to create schem 
on, the problem is I can get any of my boxes to show you know

+---+
|  |
+---+

Here is the command I am doing

my -Ae show tables; | while read a ; do my -Ae desc $a; echo  
myfile.txt ; done

what I get the desc with out the boxes.

Can someone help me? I need the box because I am going to cut these 
tables out so I can map out the relatation to them.

Thanks,

Payne

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

Highlight search keywords in Java

2006-10-16 Thread Harini Raghavan


Hello All,

I am using MySQL full-text search capability in a java based search
application. I have a requirement to highlight search keywords in the
results obtained from MySQL database(similar to Google). I found some 
links to do this using a PHP script. Is this possible to do using Java?


Any help/suggestions would be appreciated.

Thanks,
Harini


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

Re: mysql within firewall

2006-10-16 Thread Peter Gershkovich


Firewall issues continue.
Once again if anyone had similar problem please help.

We did various tests and found out the following:
LIMIT 10 , 100, 1000 works fine. On 2000 it breaks. I checked queries  
of different type against the same table and different tables. It  
works the same with intermittent errors even on such simple commands  
as 'show tables' (see below).


show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:30

or

ERROR 2013 (HY000): Lost connection to MySQL server during query

There is absolutely nothing in the log or error log except last query  
run.


The actual size of  queries that break is not that big - 3000  
records. It all works fine when the client is outside the firewall.  
Also it does not matter if the database is on AIX or OS X. We see  
same behavior on both.


We do see bad checksums on TCP dump and (as confirmed by Cisco) their  
firewall sees all the interfaces coming into it as VLAN's , and thus  
does VLAN tagging.  VLAN tagging reduces the MTU size by about 20 to  
30. That is the only difference we can determine so far.


Thanks,
Peter


On Oct 5, 2006, at 2:23 PM, Christian Hammers wrote:


Hello Peter

On 2006-10-05 Peter Gershkovich wrote:

Problem:

When we run a large query (returns 4000 records) on a firewalled
XServe (OS X 10.4) against Mysql database (outside firewall) on an
AIX (Version 5.2) machine the database server intermittently
generates the following errors:

ERROR 2006 (HY000): MySQL server has gone away


If the result is large, check if max_allowed_packet is set to at least
16MB or so in both, the client and the serve (see docs how to  
configure

variables best).

Also, a gone away server normally means a crash of mysqld for which  
some
debugging output is written on stderr by mysqld. In Debian Linux  
this is

sent to syslog, check where your output has gone to.

Third, try experimenting if the same problem occurs if you use  
LIMIT 10
or if the query takes a very long time *before* the output is sent  
to the

client. That helps identifying which limit or timeout you've hit.

A firewall normally only lead to trouble if either the query takes  
say 5min

before the first result row comes and the firewall things that the
connection is timed out or if you have an enormous number of  
simultaneous

connections and some connection tracking table runs full.

bye,

-christian-

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




Peter Gershkovich M.D.
Associate Research Scientist
Senior Manager, Information Technology
Yale University School of Medicine
Department of Pathology
Phone: 203-785-2325
Fax:  203-785-7303

Re: How to get into mysql command line?

2006-10-16 Thread Cornelia Menzel



Am 16.10.2006 um 14:10 schrieb Anders Karlsson:

Come to think of it, might it be that you are using an old mysql  
client, possibly one that exists as part of an old installation of  
MySQL or in the case of Linux, one that was installed as part of  
the Linux distro installation?
Some of these older versions of MySQL Client does not support the  
more secure password encryption methods used by newer MySQL  
versions, and you will get just the errors that you are getting.
So if you are using Linux, do a which mysql and see what mysql  
client you are using. Also do a mysql --version which will show  
the command line client version. You have to watch for this if the  
client has version 4.0 or earlier, and you have server with version  
4.1 or higher, then this is surely the problem you are experiencing.

Read more here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

Best regards
Anders Karlsson


Thank you Anders.

A 'which mysql' in command line gives the following: /usr/local/mysql/ 
bin/mysql
A 'mysql --version' in command line says: mysql  Ver 14.7 Distrib  
4.1.21, for apple-darwin7.9.0 (powerpc) using readline 4.3


I am using the Version 4.1.21 Standard for Mac OS X (PPC)

I have already 'solved' the problem. I have reinstalled the whole  
system. It takes me less than 2 hours reinstalling everything. As I  
am always making backups of all my preference files, it was pretty  
easy. Easier than finding out, what the hell was going wrong with my  
password (at least for me). Unfortunately, right now I do not have so  
much time so I was forced to do so. Thanks to all who tried to help  
me with this.


For now I did not set up a password for my root account. After my bad  
experience with that, I prefer to leave everything as it is.


Anyhow, if somebody has a glue of what was or could have been the  
problem, I am interested to know what it was. What really annoys me,  
is that I have not found what it was.


Best regards,
Cornelia

-
Cornelia Menzel

Spanien / España / Spain

e-Mail: [EMAIL PROTECTED]
http://www.corneliamenzel.com

RE: help with update query

2006-10-16 Thread Jerry Schwartz

You might want to check for an empty string () rather than null. From what
I can tell, HTML forms don't give you NULL values if you leave fields empty,
they return . Unless your programs internally convert empty strings to
NULL, you won't find NULL in your table.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
 Sent: Saturday, October 14, 2006 9:16 PM
 To: Dan Buettner
 Cc: mysql
 Subject: Re: help with update query

 Thanks Dan. This does help.  This a pretty straight-forward
 idea. I could
 even save the results of this query to a text file and
 possibly review it a
 little before running it so I don't acidentally do anything
 funky and I
 could see the impact this would have on the data before
 applying it. I think
 maybe I'll even add a WHERE email_address IS NULL line
 within the UPDATE
 concatenation so I don't overwrite any records that already have an
 email_address. I'll try this. Thanks alot!

 Ferindo

 On 10/14/06, Dan Buettner [EMAIL PROTECTED] wrote:
 
  Ferindo, I had a similar task recently, and the problem you'll run
  into is that you can't select from and update the same
 table at once.
  What I ended up doing was doing a SELECT to build the update queries
  for me.
 
  Something like this:
  SELECT CONCAT(
  UPDATE bowler_score SET email_address = ', email_address, ' ,
  WHERE firstname = ', firstname, ' ,
  AND middlename = ', middlename, ' ,
  AND lastname = ', lastname, ' ,
  AND race = ', race, ' ,
  AND religion = ', religion, ';  )
  FROM bowler_score
  WHERE email_address LIKE [EMAIL PROTECTED]
 
  This finds all the entries where there appears to be a valid email
  address (contains @), and updates all the other records for that
  individual.
 
  Note this is not very efficient, since a LOT of update
 queries will be
  generated, and also that if one person has more than one
 email address
  (a typo perhaps) you will lose all but one address for them.  But it
  should work, and it's pretty easy.
 
  HTH,
  Dan
 
  On 10/13/06, Ferindo Middleton  [EMAIL PROTECTED] wrote:
   I have a table, bowler_score_records, with the following
 columns:  id,
   firstname,  middlename, lastname, race, religion, email_address,
   bowling_score, gamedate
  
   As records get entered to this table, sometimes the users
 forget to
  input
   the email_address but the users always capture the full
 name, race, and
   religion. Assuming that no two individuals (bowlers)
 would happen to
  have
   the same name, race, and religion.
  
   I need to write a query to update the email_address for
 all the records
   where the users forgot to input it based on the idea that records
  carrying
   the same full name, race, and religion are in fact the
 same person,
  hence
   the same email_address.
  
   Based on the schema described above, how would you write it?
  
   --
   Ferindo
  
  
 



 --
 Ferindo Middleton
 Web Application Developer/Database Administrator/IT Infrastructure and
 Integration Management Specialist/Perception Augmentation and Control
 Supplementation Research Specialist for AI
 Wetware-to-Software Interface and
 Design
 -Sleekcollar-





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

RE: change format of date fields during LOAD DATA INFILE?

2006-10-16 Thread Jerry Schwartz

I just tested it with Excel, as it will save the date as seen if you save
the worksheet to a text file. I do this quite a bit, actually, to put
spreadsheet data into MySQL. Often I use Excel macros to construct entire
UPDATE or INSERT statements, and save those into a text file for MySQL to
inhale.

I can't speak for OpenOffice.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
 Sent: Saturday, October 14, 2006 9:40 PM
 To: mysql
 Subject: change format of date fields during LOAD DATA INFILE?

 Is there a way to change the format of date fields MySQL is
 expecting when
 LOADing data from a file? I have no problem with the format
 MySQL saves the
 date but most spreadsheet programs I use don't make it easy
 to export text
 files with date fields in the format -MM-DD even if I
 formated the field
 that way on-screen.

 It would be great if you could tell MySQL on the command line
 to expect
 dates in the format Month/Day/Year or something like that and
 be able to
 interpret that and convert the date to the format it's
 expecting on the fly.

 --
 Ferindo





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

MySQL Swapping problem

2006-10-16 Thread Clyde Lewis

A little confused as to why MySql is swapping when there is more than 
enough real memory still available on the server. I'm having a 
serious problem with a production server running out of swap space 
and in turn causing mysql to crash with the following error message. 
According to the TOP report, there is more than 14G available to the server.


Error:
Version: '4.1.11-max-log'  socket: 
'/export/ctrl/mysqladmin/mysql.sock'  port: 3306  MySQL Community 
Edition - Experimental (GPL)

061013 12:39:47  InnoDB: Error: cannot allocate 73919464 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 595973856 bytes. Operating system errno: 11
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: We keep retrying the allocation for 60 seconds...
061013 12:39:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...

Top on server:
load averages:  0.11,  0.12,  0.13   11:37:55
140 processes: 138 sleeping, 1 stopped, 1 on cpu
CPU states: 99.1% idle,  0.2% user,  0.6% kernel,  0.0% iowait,  0.0% swap
Memory: 32G real, 14G free, 30G swap in use, 145M swap free

   PID USERNAME LWP PRI NICE  SIZE   RES STATETIMECPU COMMAND
  2556 mysql 19  590  954M  735M sleep   23.5H  0.14% mysqld
 20373 mysql 31  590  954M  706M sleep   78:23  0.13% mysqld
 27559 mysql 47  590 1011M  990M sleep   26.6H  0.09% mysqld
  2049 mysql 18  590  943M  701M sleep   16.8H  0.07% mysqld
  2810 mysql 26  590  947M  754M sleep  766:37  0.05% mysqld
  2370 mysql 24  590  963M  825M sleep   31.7H  0.01% mysqld
 29472 mysql  1  590 3144K 2024K cpu/11   0:00  0.01% top
  2432 mysql 20  590  952M  732M sleep  800:57  0.01% mysqld
 22967 mysql 31  590  934M  139M sleep5:02  0.01% mysqld
  2742 mysql 18  590  947M  722M sleep   17.6H  0.00% mysqld
  2494 mysql 20  590  945M  728M sleep  782:58  0.00% mysqld
  2880 mysql 26  590  972M  828M sleep  910:23  0.00% mysqld

Any help would be greatly appreciated.

Thanks in advance,
CL


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

LOCK TABLES

2006-10-16 Thread mdpeters

I am having a devil of a time moving a database from one server to 
another. My database is one that is in production to support the 
mediawiki wiki application. This is a Solaris Sparc 10 system using the 
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.


I execute this:
# mysqldump --user root --password=password horsewiki  horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when 
using LOCK TABLES


I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the 
database to an SQL file. When I attempt to import it on the other 
system, I get this:


Error

There seems to be an error in your SQL query. The MySQL server error 
output below, if there is any, may also help you in diagnosing the problem


ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
   http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en 
dir=ltr

head
   link rel=icon href=./favicon.ico type=image/x-icon /
   link rel=shortcut icon href=./favicon.ico type=image/x-icon /
   titlephpMyAdmin/title
   meta http-equiv=Content-Type content=text/html; charset=utf-8 /
   link rel=stylesheet type=text/css 
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right 
/
   link rel=stylesheet type=text/css 
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /

   script type=text/javascript language=javascript
   // ![CDATA[
   // Updates the title of the frameset if possible (ns4 does not allow 
this)
   if (typeof(parent.document) != 'undefined'  
typeof(parent.document) != 'unknown'

typeof(parent.document.title) == 'string') {
   parent.document.title = 'www.lazarusalliance.com / localhost / 
horsewiki / archive | phpMyAdmin 2.9.0.2';



SQL query:

-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net 
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM -- 
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --



I have not found a good source to understand what the problem might be. 
It does not help that I am such a greenhorn with databases either. I 
would appreciate any assistance.


Michael

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

RE: Re: Re: Re: Readind a Dump W/o Expanding It

2006-10-16 Thread Jerry Schwartz

This may be too obvious, but what is in that text field?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Ted Johnson [mailto:[EMAIL PROTECTED]
 Sent: Monday, October 16, 2006 3:57 AM
 To: Dan Buettner
 Cc: mysql@lists.mysql.com
 Subject: Re: Re: Re: Re: Readind a Dump W/o Expanding It

 8080The data field's a text field, not a blob! That's the
 problem, there are no blobs, and I just wanted a second set
 of eyes on this to confirm what simply makes no sense at all!
 I'd love to know where the dickens those images went!!

 Thanks anyway,
 Ted

 - Original Message 
 From: Dan Buettner [EMAIL PROTECTED]
 To: Ted Johnson [EMAIL PROTECTED]
 Cc: mysql@lists.mysql.com
 Sent: Sunday, October 15, 2006 8:45:07 PM
 Subject: Re: Re: Re: Re: Readind a Dump W/o Expanding It

 Ted - sorry, not sure what this is showing me.  Is the image data
 supposed to be in the data field in the Photo table?  It's not
 there, or it's corrupt?

 Dan


 On 10/15/06, Ted Johnson
 [EMAIL PROTECTED] wrote:
 
  74
  - Original Message 
  From: Dan Buettner [EMAIL PROTECTED]
  To: Ted Johnson [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Sent: Sunday, October 15, 2006 9:22:04 AM
  Subject: Re: Re: Re: Readind a Dump W/o Expanding It
 
  How were they stored in the database?
 
  You got me on that. I just took over this box. Here's what
 my queries show:
 
  server167# mysqlshow -uroot -p tirekingdom
  Enter password:
  Database: tirekingdom
  ++
  |   Tables   |
  ++
  | OrderItem  |
  | Orders |
  | Photo  |
  | PhotosForTire  |
  | PhotosForWheel |
  | Shopper|
  | Tire   |
  | Vehicle|
  | Wheel  |
  | WheelMake  |
  | WheelsForWheelMake |
  ++
  server167# mysqlshow tirekingdom Photo -uroot -p
  Enter password:
  Database: tirekingdom  Table: Photo  Rows: 2160
 
 +---+-+---+--+-+--
 ---+---+-+-+
  | Field | Type| Collation | Null | Key |
 Default | Extra |
  Privileges  | Comment |
 
 +---+-+---+--+-+--
 ---+---+-+-+
  | oid   | int(11) | NULL  |  | PRI | 0
  |   |
  select,insert,update,references | |
  | Data  | mediumtext  | latin1_swedish_ci | YES  | |
  |   |
  select,insert,update,references | |
  | Type  | varchar(4)  | latin1_swedish_ci | YES  | |
  |   |
  select,insert,update,references | |
  | Name  | varchar(32) | latin1_swedish_ci | YES  | |
  |   |
  select,insert,update,references | |
 
 +---+-+---+--+-+--
 ---+---+-+-+
  server167# mysqlshow tirekingdom PhotoForTires -uroot -p
  Enter password:
  mysqlshow: Cannot list columns in db: tirekingdom, table:
 PhotoForTires:
  Table 'tirekingdom.PhotoForTires' doesn't exist
  server167# mysqlshow -uroot -p tirekingdom PhotosForTire
  Enter password:
  Database: tirekingdom  Table: PhotosForTire  Rows: 1058
 
 +-+-+---+--+-+-+--
 -+-+-+
  | Field   | Type| Collation | Null | Key | Default | Extra |
  Privileges  | Comment |
 
 +-+-+---+--+-+-+--
 -+-+-+
  | TireID  | int(11) | NULL  |  | PRI | 0   |   |
  select,insert,update,references | |
  | PhotoID | int(11) | NULL  |  | PRI | 0   |   |
  select,insert,update,references | |
 
 +-+-+---+--+-+-+--
 -+-+-+
  server167# mysqlshow -uroot -p tirekingdom PhotosForWheel
  Enter password:
  Database: tirekingdom  Table: PhotosForWheel  Rows: 1098
 
 +-+-+---+--+-+-+--
 -+-+-+
  | Field   | Type| Collation | Null | Key | Default | Extra |
  Privileges  | Comment |
 
 +-+-+---+--+-+-+--
 -+-+-+
  | WheelID | int(11) | NULL  |  | PRI | 0   |   |
  select,insert,update,references | |
  | PhotoID | int(11) | NULL  |  | PRI | 0   |   |
  select,insert,update,references | |
 
 +-+-+---+--+-+-+--
 -+-+-+
 
  So, where's the blob? It isn't in some file in the site,
 I've checked.
 
  Which way did you end up restoring

Re: change format of date fields during LOAD DATA INFILE?

2006-10-16 Thread Ferindo Middleton


I was using OpenOffice... And I couldn't get it to keep the format
-mm-dd I saw on screen in that format when I went to save it as a text
file I was able to I suppose this should be reported to their developers
as an enhancement.

There's no way to get MySQL to accept dates in a different format when
performing the operation on the command line though?

Ferindo

On 10/16/06, Jerry Schwartz [EMAIL PROTECTED] wrote:


I just tested it with Excel, as it will save the date as seen if you save
the worksheet to a text file. I do this quite a bit, actually, to put
spreadsheet data into MySQL. Often I use Excel macros to construct entire
UPDATE or INSERT statements, and save those into a text file for MySQL to
inhale.

I can't speak for OpenOffice.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Ferindo Middleton [mailto:[EMAIL PROTECTED] ]
 Sent: Saturday, October 14, 2006 9:40 PM
 To: mysql
 Subject: change format of date fields during LOAD DATA INFILE?

 Is there a way to change the format of date fields MySQL is
 expecting when
 LOADing data from a file? I have no problem with the format
 MySQL saves the
 date but most spreadsheet programs I use don't make it easy
 to export text
 files with date fields in the format -MM-DD even if I
 formated the field
 that way on-screen.

 It would be great if you could tell MySQL on the command line
 to expect
 dates in the format Month/Day/Year or something like that and
 be able to
 interpret that and convert the date to the format it's
 expecting on the fly.

 --
 Ferindo

Re: help with update query

2006-10-16 Thread Ferindo Middleton


I agree. I should check for empty strings intead of nulls. The application
doesn't convert them to null and the default value when a user leaves the
field blank on the web page is to save it as an empty string. Thanks.

Ferindo

On 10/16/06, Jerry Schwartz [EMAIL PROTECTED] wrote:


You might want to check for an empty string () rather than null. From
what
I can tell, HTML forms don't give you NULL values if you leave fields
empty,
they return . Unless your programs internally convert empty strings to
NULL, you won't find NULL in your table.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Ferindo Middleton [mailto:[EMAIL PROTECTED]
 Sent: Saturday, October 14, 2006 9:16 PM
 To: Dan Buettner
 Cc: mysql
 Subject: Re: help with update query

 Thanks Dan. This does help.  This a pretty straight-forward
 idea. I could
 even save the results of this query to a text file and
 possibly review it a
 little before running it so I don't acidentally do anything
 funky and I
 could see the impact this would have on the data before
 applying it. I think
 maybe I'll even add a WHERE email_address IS NULL line
 within the UPDATE
 concatenation so I don't overwrite any records that already have an
 email_address. I'll try this. Thanks alot!

 Ferindo

 On 10/14/06, Dan Buettner [EMAIL PROTECTED] wrote:
 
  Ferindo, I had a similar task recently, and the problem you'll run
  into is that you can't select from and update the same
 table at once.
  What I ended up doing was doing a SELECT to build the update queries
  for me.
 
  Something like this:
  SELECT CONCAT(
  UPDATE bowler_score SET email_address = ', email_address, ' ,
  WHERE firstname = ', firstname, ' ,
  AND middlename = ', middlename, ' ,
  AND lastname = ', lastname, ' ,
  AND race = ', race, ' ,
  AND religion = ', religion, ';  )
  FROM bowler_score
  WHERE email_address LIKE [EMAIL PROTECTED]
 
  This finds all the entries where there appears to be a valid email
  address (contains @), and updates all the other records for that
  individual.
 
  Note this is not very efficient, since a LOT of update
 queries will be
  generated, and also that if one person has more than one
 email address
  (a typo perhaps) you will lose all but one address for them.  But it
  should work, and it's pretty easy.
 
  HTH,
  Dan
 
  On 10/13/06, Ferindo Middleton  [EMAIL PROTECTED] wrote:
   I have a table, bowler_score_records, with the following
 columns:  id,
   firstname,  middlename, lastname, race, religion, email_address,
   bowling_score, gamedate
  
   As records get entered to this table, sometimes the users
 forget to
  input
   the email_address but the users always capture the full
 name, race, and
   religion. Assuming that no two individuals (bowlers)
 would happen to
  have
   the same name, race, and religion.
  
   I need to write a query to update the email_address for
 all the records
   where the users forgot to input it based on the idea that records
  carrying
   the same full name, race, and religion are in fact the
 same person,
  hence
   the same email_address.
  
   Based on the schema described above, how would you write it?
  
   --
   Ferindo
  
  
 



 --
 Ferindo Middleton
 Web Application Developer/Database Administrator/IT Infrastructure and
 Integration Management Specialist/Perception Augmentation and Control
 Supplementation Research Specialist for AI
 Wetware-to-Software Interface and
 Design
 -Sleekcollar-








--
Ferindo Middleton
Web Application Developer/Database Administrator/IT Infrastructure and
Integration Management Specialist/Perception Augmentation and Control
Supplementation Research Specialist for AI Wetware-to-Software Interface and
Design
-Sleekcollar-

CREATE TABLE Inv_Id

2006-10-16 Thread Scott Hamm


I'm trying to create a table as follows:

CREATE TABLE Inv_Id (
ID INT(12) AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

How do I make ID to start out as '0001' for UPC barcode assignment?
--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.

Re: LOCK TABLES

2006-10-16 Thread Dan Buettner


Michael, is the 'archive' table present in your database?  e.g., if
you do a 'LIST TABLES', does it show up?  What happens if you do a
SELECT * FROM archive LIMIT 1 ?

I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.

If you don't need the archive table, can you DROP it successfully?

Dan


On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.

I execute this:
# mysqldump --user root --password=password horsewiki  horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES

I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:

Error

There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined' 
typeof(parent.document) != 'unknown'
 typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';


SQL query:

-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: `horsewiki` --


I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.

Michael

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

Sum DISTINCT

2006-10-16 Thread Alvaro Cobo

Dear friends:

I have two tables. In one of those I store information about loans and
the year it was given. The other one stores the families which have
received this loans, with infromation abot the number of members in each
family (male and women).

Each family can receive more than one loan in a year.

The query I need to get is the total of male and women which have got a
loan during an especific year, making a join betwen this two tables.

La consulta que necesito obtener es: Suma de hombres y mujeres de las
familias que han participado de algún crédito durante cada año.

SELECT credito.anio,
Sum(familia.hombres) AS Total_Hombres,
Sum(familia.mujeres) AS Total_Mujeres
FROM credito
LEFT JOIN familia ON credito.id_familia = familia.id_familia
GROUP BY credito.anio;

+--+---+---+
| anio | Total_Hombres | Total_Mujeres |
+--+---+---+
| 2005 |21 |23 |
| 2006 |11 | 9 |
+--+---+---+
(Translation: Hombre=Male; Mujeres=Women)

The problem is that as long as the families can receive more than one
loan in one year (so there are more than one row in the families/loan
row), the rows of the male and women get duplicated so the SUM of those
results it too big (for example, in the upper result table Male might be
12 instead of 21).

So is there a function like a DISTINCT to sum each different family in
this joined tables?.

Thanks a lot and best regards,

Alvaro Cobo

MySQL version 5.0.17
SO: Debian Sarge.

Tabla de ejemplo. Base de datos test.

CREATE TABLE `familia` (
  `id_familia` int(11) NOT NULL auto_increment,
  `nombre_familia` varchar(60) NOT NULL,
  `hombres` int(11) NOT NULL,
  `mujeres` int(11) NOT NULL,
  PRIMARY KEY  (`id_familia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `familia` VALUES (1, 'Perez', 2, 3);
INSERT INTO `familia` VALUES (2, 'Suarez', 5, 3);
INSERT INTO `familia` VALUES (3, 'Sanchez', 6, 6);
INSERT INTO `familia` VALUES (4, 'Montalvo', 4, 5);
INSERT INTO `familia` VALUES (5, 'Cobo', 4, 3);
INSERT INTO `familia` VALUES (6, 'Larrea', 1, 3);


CREATE TABLE `credito` (
  `anio` year(4) NOT NULL,
  `id_credito` int(11) NOT NULL auto_increment,
  `id_familia` int(11) NOT NULL,
  PRIMARY KEY  (`id_credito`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `credito` VALUES (2005, 1, 1);
INSERT INTO `credito` VALUES (2005, 2, 1);
INSERT INTO `credito` VALUES (2005, 3, 1);
INSERT INTO `credito` VALUES (2005, 4, 3);
INSERT INTO `credito` VALUES (2005, 5, 4);
INSERT INTO `credito` VALUES (2005, 6, 2);
INSERT INTO `credito` VALUES (2006, 7, 2);
INSERT INTO `credito` VALUES (2006, 8, 3);




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

Re: CREATE TABLE Inv_Id

2006-10-16 Thread Gerald L. Clark


Scott Hamm wrote:

I'm trying to create a table as follows:

CREATE TABLE Inv_Id (
ID INT(12) AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

How do I make ID to start out as '0001' for UPC barcode assignment?


UPC barcodes are not sequential numbers.
They are composed of a casecode prefix, a manufacturers id, a 
partnumber, and a check digit.


You cannot use an autoincrement number as a UPC.

--
Gerald L. Clark
Supplier Systems Corporation

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

Re: CREATE TABLE Inv_Id

2006-10-16 Thread Dan Buettner


Hmmm.  When the table is first created, the id number should default
to starting at 1.  Once the table has had entries added and deleted
you can run an ALTER TABLE to reset it to 1, provided it is empty I
think.

There's no way to store an integer left-padded with zeros like that, however.

What you can do is left pad it with zeros whenever you select it:
select LPAD(id,12,0) from Inv_Id order by id;

HTH,
Dan

On 10/16/06, Scott Hamm [EMAIL PROTECTED] wrote:

I'm trying to create a table as follows:

CREATE TABLE Inv_Id (
ID INT(12) AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

How do I make ID to start out as '0001' for UPC barcode assignment?
--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.





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

Re: CREATE TABLE Inv_Id

2006-10-16 Thread Scott Hamm


Alright, I'll abandon UPC, I'm trying to serialize all components i.e. video
cards, keyboards, sound cards, etc and when I process these info into
database I would like for it to give me the image of bar code with number on
bottom and small enough to put on the metal side of components i.e. ethernet
card, sound card and so on. Here is what I found so far:

http://www.ashberg.de/php-barcode/index.php?code=01234567890scale=2bar=UPC

The reason why I select UPC is because it is almost impossible to become
unreadable compared to other encodes.

Any better suggestions?

On 10/16/06, Gerald L. Clark [EMAIL PROTECTED] wrote:


Scott Hamm wrote:
 I'm trying to create a table as follows:

 CREATE TABLE Inv_Id (
 ID INT(12) AUTO_INCREMENT PRIMARY KEY,
 MID INT NOT NULL,
 FOREIGN (MID) REFERENCES 'Model' (ID)
 );

 How do I make ID to start out as '0001' for UPC barcode
assignment?

UPC barcodes are not sequential numbers.
They are composed of a casecode prefix, a manufacturers id, a
partnumber, and a check digit.

You cannot use an autoincrement number as a UPC.

--
Gerald L. Clark
Supplier Systems Corporation





--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.

Re: CREATE TABLE Inv_Id

2006-10-16 Thread Rolando Edwards

You may want to simulate autoincrementing
by means of Triggers

You may need to make ID a 12 character string first.

Then create a trigger something like this:

CREATE TRIGGER addingInv BEFORE INSERT ON Inv_Id
FOR EACH ROW SET @ID= '...'

You will have to also add what Gerald mentioned (casecode prefix,
a manufacturers id, a partnumber, and a check digit) to the table definition.

- Original Message -
From: Gerald L. Clark [EMAIL PROTECTED]
To: Scott Hamm [EMAIL PROTECTED]
Cc: Mysql  mysql@lists.mysql.com
Sent: Monday, October 16, 2006 2:31:47 PM GMT-0500 US/Eastern
Subject: Re: CREATE TABLE Inv_Id

Scott Hamm wrote:
 I'm trying to create a table as follows:
 
 CREATE TABLE Inv_Id (
 ID INT(12) AUTO_INCREMENT PRIMARY KEY,
 MID INT NOT NULL,
 FOREIGN (MID) REFERENCES 'Model' (ID)
 );
 
 How do I make ID to start out as '0001' for UPC barcode assignment?

UPC barcodes are not sequential numbers.
They are composed of a casecode prefix, a manufacturers id, a 
partnumber, and a check digit.

You cannot use an autoincrement number as a UPC.

-- 
Gerald L. Clark
Supplier Systems Corporation

-- 
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: CREATE TABLE Inv_Id

2006-10-16 Thread Gabriel PREDA


I would try:

CREATE TABLE Inv_Id (
ID INT(12) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

Note the UNSIGNED and ZEROFILL flags !

-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Gabriel PREDA
Senior Web Developer

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

Re: CREATE TABLE Inv_Id

2006-10-16 Thread Scott Hamm


Thanks!



On 10/16/06, Gabriel PREDA [EMAIL PROTECTED] wrote:


I would try:

CREATE TABLE Inv_Id (
ID INT(12) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

Note the UNSIGNED and ZEROFILL flags !

-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Gabriel PREDA
Senior Web Developer





--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.

User Table - exposed

2006-10-16 Thread Michael DePhillips


Hello All,

Cyber-security scrutiny has prompted this scenario. 

I like to have an anonymous login for my slave machines, with read only 
access, of course.  There are quite a few databases that I would like 
them to read from, so I using a wild card granting them read 
privileges to all dbs/tables.  This, however, means that the mysql.USER 
table is exposed, a big no-no.  Is there a way I can EXCLUDE viewing the 
USER table (or the entire mysql db) without explicitly indicating all 
the db that they are allowed to view.


Sorry if this is obvious, but I didn't see it in the documentation.

Thanks,
Michael

--
Michael DePhillips 
www.star.bnl.gov



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

list of cols that I need to index

2006-10-16 Thread Ahmad Al-Twaijiry


Hi

is there anyway or command to run it against a production table to see
if there is any column that I should think about indexing it

remember this is a production database, so I can't run it in debug
mode and I don't have a root access to the database (I'm just a
developer).



Thanks

--

Ahmad Fahad AlTwaijiry

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

Re: list of cols that I need to index

2006-10-16 Thread Dan Buettner


Ahmad -

It's not always a cut-and-dried thing; performance tuning involves a
lot of factors, and with living databases should be an ongoing thing.

Check out the section on optimization at
http://dev.mysql.com/doc/refman/5.0/en/optimize-overview.html
and/or Jeremy Zawodny's excellent book, High Performance MySQL

what may be of specific interest to you right away is the EXPLAIN
function, showing you how MySQL will execute your queries:
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Dan



On 10/16/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote:

Hi

is there anyway or command to run it against a production table to see
if there is any column that I should think about indexing it

remember this is a production database, so I can't run it in debug
mode and I don't have a root access to the database (I'm just a
developer).



Thanks

--

Ahmad Fahad AlTwaijiry

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

install mysql 5.0.26 with php-5.1.6 - issues

2006-10-16 Thread Serban Ghita

./configure --prefix=/usr/local/php --with-apxs2=/usr/local/apache/bin/apxs 
--with-mysql=/usr/local/mysql --enable-module=so --enable-cli 
--with-zlib-dir=/usr/include 

[...] 

checking for MySQL support... yes 
checking for specified location of the MySQL UNIX socket... no 
checking for MySQL UNIX socket location... no 
checking for mysql_close in -lmysqlclient... no 
checking for mysql_errno in -lmysqlclient... no 
configure: error: mysql configure failed. Please check config.log for more 
information. 
[EMAIL PROTECTED]:/home/serban/kits/php-5.1.6# 

cut from config.log-- 

int main() { 
mysql_close() 
; return 0; } 
configure:58773: checking for mysql_error in -lmysqlclient 
configure:58792: gcc -o conftest -I/usr/include -g -O2 -Wl,-rpath,/usr/local/m$ 
/usr/local/mysql/lib/libmysqlclient.a(client.o): In function 
`mysql_real_connec$ 
client.c:(.text+0x15f6): undefined reference to `_intel_fast_memcpy' 
/usr/local/mysql/lib/libmysqlclient.a(client.o): In function `cli_read_rows': 
client.c:(.text+0x23d4): undefined reference to `_intel_fast_memcpy' 

[] 

inflate.c:(.text+0x1e5d): undefined reference to `_intel_fast_memcpy' 
inflate.c:(.text+0x1e8d): undefined reference to `_intel_fast_memcpy' 
inflate.c:(.text+0x1ecd): undefined reference to `_intel_fast_memcpy' 
/usr/local/mysql/lib/libz.a(inflate.o):inflate.c:(.text+0x1ff3): more 
undefined$ 
/usr/local/mysql/lib/libz.a(gzio.o): In function `gzseek': 
gzio.c:(.text+0x1270): undefined reference to `_intel_fast_memset' 
collect2: ld returned 1 exit status 
configure: failed program was: 
#line 58781 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; } 

cut from config.log-- 


im in the middle of php install, when it stops and tells me to look into 
config.log. in there i can only see some stuff about a file located in /lib/ 

do i need any other libraries installed? this never happened when i previously 
installed lamp with older versions. 

i've installed mysql-dfsg-5.0-5.0.22.orig in case that helps 

thanks

--
Serban Gh. Ghita
Project Manager

VERASYS Intl.
Headoffice World Trade Center
10, Montreal Sq., Entrance F, 1st Floor,
011469, Bucharest, ROMANIA
GSM: +40(0)788 282910
Tel: +40(0)21-2109111
Fax: +40(0)21-2109011
E-mail: [EMAIL PROTECTED]
E-mail: [EMAIL PROTECTED]
www.itpromo.ro
www.verasys.com

Re: LOCK TABLES

2006-10-16 Thread mdpeters


As you can see, it is clearly showing up but I cannot do anything with it.

mysql show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:


Michael, is the 'archive' table present in your database?  e.g., if
you do a 'LIST TABLES', does it show up?  What happens if you do a
SELECT * FROM archive LIMIT 1 ?

I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.

If you don't need the archive table, can you DROP it successfully?

Dan


On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:


I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is 
horsewiki.


I execute this:
# mysqldump --user root --password=password horsewiki  horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES

I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:

Error

There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the 
problem


ERROR: Unknown Punctuation String @ 494
STR: /
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico 
type=image/x-icon /

titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; 
charset=utf-8 /

link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right 


/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined' 
typeof(parent.document) != 'unknown'
 typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';


SQL query:

-- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
-- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database: 
`horsewiki` --



I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.

Michael

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

Re: LOCK TABLES

2006-10-16 Thread Dan Buettner


Try looking in the filesystem for the file(s) called archive in the
database directory, and move them somewhere else (or if you know you
don't need them, delete them).

If it is/was a MyISAM table, or just an errant file, this should work.
If it is/was InnoDB, this will be trickier ...

Dan


On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

As you can see, it is clearly showing up but I cannot do anything with it.

mysql show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:

 Michael, is the 'archive' table present in your database?  e.g., if
 you do a 'LIST TABLES', does it show up?  What happens if you do a
 SELECT * FROM archive LIMIT 1 ?

 I'd hazard a guess that you may have a table definition file with no
 actual table data files, if you're on MyISAM tables.

 If you don't need the archive table, can you DROP it successfully?

 Dan


 On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

 I am having a devil of a time moving a database from one server to
 another. My database is one that is in production to support the
 mediawiki wiki application. This is a Solaris Sparc 10 system using the
 mysql-max-5.0.20a-solaris10-sparc version. My database name is
 horsewiki.

 I execute this:
 # mysqldump --user root --password=password horsewiki  horsewiki.sql
 and get this:
 mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
 using LOCK TABLES

 I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
 database to an SQL file. When I attempt to import it on the other
 system, I get this:
 
 Error

 There seems to be an error in your SQL query. The MySQL server error
 output below, if there is any, may also help you in diagnosing the
 problem

 ERROR: Unknown Punctuation String @ 494
 STR: /
 SQL: -- phpMyAdmin SQL Dump
 -- version 2.9.0.2
 -- http://www.phpmyadmin.net
 --
 -- Host: localhost
 -- Generation Time: Oct 16, 2006 at 10:00 AM
 -- Server version: 5.0.20
 -- PHP Version: 5.1.6
 --
 -- Database: `horsewiki`
 --
 !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
 http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
 html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
 dir=ltr
 head
 link rel=icon href=./favicon.ico type=image/x-icon /
 link rel=shortcut icon href=./favicon.ico
 type=image/x-icon /
 titlephpMyAdmin/title
 meta http-equiv=Content-Type content=text/html;
 charset=utf-8 /
 link rel=stylesheet type=text/css
 
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right

 /
 link rel=stylesheet type=text/css
 href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
 script type=text/javascript language=javascript
 // ![CDATA[
 // Updates the title of the frameset if possible (ns4 does not allow
 this)
 if (typeof(parent.document) != 'undefined' 
 typeof(parent.document) != 'unknown'
  typeof(parent.document.title) == 'string') {
 parent.document.title = 'www.lazarusalliance.com / localhost /
 horsewiki / archive | phpMyAdmin 2.9.0.2';


 SQL query:

 -- phpMyAdmin SQL Dump -- version 2.9.0.2 -- http://www.phpmyadmin.net
 -- -- Host: localhost -- Generation Time: Oct 16, 2006 at 10:00 AM --
 Server version: 5.0.20 -- PHP Version: 5.1.6 -- -- Database:
 `horsewiki` --
 

 I have not found a good source

Re: LOCK TABLES

2006-10-16 Thread mdpeters

I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. 
It moves past archive and onto another table. I did this 6 times, each 
time moving the next one it complained about until I stopped and put 
them all back.


Dan Buettner wrote:


Try looking in the filesystem for the file(s) called archive in the
database directory, and move them somewhere else (or if you know you
don't need them, delete them).

If it is/was a MyISAM table, or just an errant file, this should work.
If it is/was InnoDB, this will be trickier ...

Dan


On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

As you can see, it is clearly showing up but I cannot do anything 
with it.


mysql show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:

 Michael, is the 'archive' table present in your database?  e.g., if
 you do a 'LIST TABLES', does it show up?  What happens if you do a
 SELECT * FROM archive LIMIT 1 ?

 I'd hazard a guess that you may have a table definition file with no
 actual table data files, if you're on MyISAM tables.

 If you don't need the archive table, can you DROP it successfully?

 Dan


 On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

 I am having a devil of a time moving a database from one server to
 another. My database is one that is in production to support the
 mediawiki wiki application. This is a Solaris Sparc 10 system 
using the

 mysql-max-5.0.20a-solaris10-sparc version. My database name is
 horsewiki.

 I execute this:
 # mysqldump --user root --password=password horsewiki  horsewiki.sql
 and get this:
 mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't 
exist when

 using LOCK TABLES

 I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
 database to an SQL file. When I attempt to import it on the other
 system, I get this:
 
 Error

 There seems to be an error in your SQL query. The MySQL server error
 output below, if there is any, may also help you in diagnosing the
 problem

 ERROR: Unknown Punctuation String @ 494
 STR: /
 SQL: -- phpMyAdmin SQL Dump
 -- version 2.9.0.2
 -- http://www.phpmyadmin.net
 --
 -- Host: localhost
 -- Generation Time: Oct 16, 2006 at 10:00 AM
 -- Server version: 5.0.20
 -- PHP Version: 5.1.6
 --
 -- Database: `horsewiki`
 --
 !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
 http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
 html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
 dir=ltr
 head
 link rel=icon href=./favicon.ico type=image/x-icon /
 link rel=shortcut icon href=./favicon.ico
 type=image/x-icon /
 titlephpMyAdmin/title
 meta http-equiv=Content-Type content=text/html;
 charset=utf-8 /
 link rel=stylesheet type=text/css
 
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right 



 /
 link rel=stylesheet type=text/css
 href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 
media=print /

 script type=text/javascript language=javascript
 // ![CDATA[
 // Updates the title of the frameset if possible (ns4 does not 
allow

 this)
 if (typeof(parent.document) != 'undefined' 
 typeof(parent.document) != 'unknown'
  typeof(parent.document.title) == 'string') {
 parent.document.title = 'www.lazarusalliance.com / 
localhost /

 horsewiki / archive | phpMyAdmin 2.9.0.2';


 SQL

Re: Re: LOCK TABLES

2006-10-16 Thread Dan Buettner


Hmmm, sounds like something's pretty abnormal here.  Any idea what may
have been done here?

I wonder if you could step around this with a call to mysqldump that
doesn't explicitly lock tables ... what is the commad you're running
again?

Dan


On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

I tried mv archive.frm .archive.frm first. Then I ran mysqldump again.
It moves past archive and onto another table. I did this 6 times, each
time moving the next one it complained about until I stopped and put
them all back.

Dan Buettner wrote:

 Try looking in the filesystem for the file(s) called archive in the
 database directory, and move them somewhere else (or if you know you
 don't need them, delete them).

 If it is/was a MyISAM table, or just an errant file, this should work.
 If it is/was InnoDB, this will be trickier ...

 Dan


 On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:

 As you can see, it is clearly showing up but I cannot do anything
 with it.

 mysql show tables;
 +-+
 | Tables_in_horsewiki |
 +-+
 | archive |
 | categorylinks   |
 | externallinks   |
 | hitcounter  |
 | horse_archive   |
 | horse_categorylinks |
 | horse_externallinks |
 | horse_hitcounter|
 | horse_image |
 | horse_imagelinks|
 | horse_interwiki |
 | horse_ipblocks  |
 | horse_job   |
 | horse_logging   |
 | horse_math  |
 | horse_objectcache   |
 | horse_oldimage  |
 | horse_page  |
 | horse_pagelinks |
 | horse_querycache|
 | horse_recentchanges |
 | horse_revision  |
 | horse_searchindex   |
 | horse_site_stats|
 | horse_templatelinks |
 | horse_text  |
 | horse_trackbacks|
 | horse_transcache|
 | horse_user  |
 | horse_user_groups   |
 | horse_user_newtalk  |
 | horse_validate  |
 | horse_watchlist |
 | image   |
 | imagelinks  |
 | interwiki   |
 | ipblocks|
 | job |
 | logging |
 | math|
 | objectcache |
 | oldimage|
 | page|
 | pagelinks   |
 | querycache  |
 | recentchanges   |
 | revision|
 | searchindex |
 | site_stats  |
 | templatelinks   |
 | text|
 | trackbacks  |
 | transcache  |
 | user|
 | user_groups |
 | user_newtalk|
 | validate|
 | watchlist   |
 +-+
 58 rows in set (0.00 sec)

 mysql SELECT * FROM archive LIMIT 1;
 ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

 mysql drop table archive;
 ERROR 1051 (42S02): Unknown table 'archive'




 Dan Buettner wrote:

  Michael, is the 'archive' table present in your database?  e.g., if
  you do a 'LIST TABLES', does it show up?  What happens if you do a
  SELECT * FROM archive LIMIT 1 ?
 
  I'd hazard a guess that you may have a table definition file with no
  actual table data files, if you're on MyISAM tables.
 
  If you don't need the archive table, can you DROP it successfully?
 
  Dan
 
 
  On 10/16/06, mdpeters [EMAIL PROTECTED] wrote:
 
  I am having a devil of a time moving a database from one server to
  another. My database is one that is in production to support the
  mediawiki wiki application. This is a Solaris Sparc 10 system
 using the
  mysql-max-5.0.20a-solaris10-sparc version. My database name is
  horsewiki.
 
  I execute this:
  # mysqldump --user root --password=password horsewiki  horsewiki.sql
  and get this:
  mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't
 exist when
  using LOCK TABLES
 
  I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
  database to an SQL file. When I attempt to import it on the other
  system, I get this:
  
  Error
 
  There seems to be an error in your SQL query. The MySQL server error
  output below, if there is any, may also help you in diagnosing the
  problem
 
  ERROR: Unknown Punctuation String @ 494
  STR: /
  SQL: -- phpMyAdmin SQL Dump
  -- version 2.9.0.2
  -- http://www.phpmyadmin.net
  --
  -- Host: localhost
  -- Generation Time: Oct 16, 2006 at 10:00 AM
  -- Server version: 5.0.20
  -- PHP Version: 5.1.6
  --
  -- Database: `horsewiki`
  --
  !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
  http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
  html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
  dir=ltr
  head
  link rel=icon href=./favicon.ico type=image/x-icon /
  link rel=shortcut icon href=./favicon.ico
  type=image/x-icon /
  titlephpMyAdmin/title
  meta http-equiv=Content-Type content=text/html;
  charset=utf-8 /
  link rel=stylesheet type=text/css
 
 
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right

 
  /
  link rel=stylesheet type=text/css
  href=./css/print.css?token=7c73a56802fc2ee8b4239fe721
 media=print

mysql5 onHPUX - no entry for terminal type

2006-10-16 Thread Ian Collins


Hi,
I have just upgraded an HPUX 11.00 server to MySQL 5.0.26-pro.
Since the upgrade, the readline seems to be broken. (It was OK on 4.1.18).

We see the problem when we run,

mysql -A -u user -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34782 to server version: 5.0.26-pro

No entry for terminal type xterm;
using dumb terminal settings.
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql

It doesn't matter what I set my TERM to.
It works for everything else (e.g., vi, emacs, more)

Any ideas?

(It may sound trivial but we have automated test suites that break 
because of this).


Regards,
Ian Collins.


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

Re: result set on prepared statements

2006-10-16 Thread Visolve DB Team


Hi,

Hope this link will be useful:
http://mirrors.sunsite.dk/mysql/doc/refman/5.1/en/c-api-prepared-statement-functions.html

Thanks
ViSolve DB Team
- Original Message - 
From: Roland Volkmann [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Sunday, October 15, 2006 5:59 PM
Subject: result set on prepared statements



Hello all,

using MySQL C API function |mysql_query() with a query producing a
result set, I have to fetch *all* records, if I use
||mysql_use_result() to avoid a client side cursor. So it's written in
the manual section |22.2.3.70.

But if I want to use prepared statements with MySQL C API functions 
|mysql_stmt_prepare(), ||mysql_stmt_execute() and
||mysql_stmt_fetch(), I can't find anything in the manual, whether I
also have to fetch *all* records, when not using
||mysql_stmt_store_result() (I don't want to use client side cursor).
MySQL Version is 5.0.26 on Windows 32 Bit.

Any Information is welcome.


With best regards,

Roland.
|

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

47 matches

Mail list logo