Re: cannot connect to the mysql server.thank you.

[Ehrwin Mina]

çéè wrote:
dear sir,
nice to meet you.
now i have installed the  MySQL-Server&Client(4[1].0.15).but i cannot 
connect mysql server when i make a change.
what is the wrong with what  i do?l
can i get your help?
thank you very much.
   


 

In what platform did you install it?
thanks,
Ehrwin Mina
Chikka Asia Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Performance

[Marcin Lewandowski]

Jocelyn Fournier napisał(a):
Hi,
What about using another forum ?
phpbb2 is well known to be far for what could be called "optimized" :)
I hate phpbb, but currently we can't change it :(
--
Marcin Lewandowski
[  mailto:[EMAIL PROTECTED]  gg:188068 jid:[EMAIL PROTECTED]  ]
[   http://www.nosoftwarepatents.com/pl/m/intro/index.html   ]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Question on Composite Index

[ManojW]

Dear Shawn,
First off, apologies for the delay in reply to this email. 

Secondly, thanks a lot for a very illuminating dicussion on composite keys 
and the way MySQL handles them. Reading through the whole discussion, I have a 
minor question is popping up in my heads...it is as follows:

If I have a table with composite key fld1,fld2,fld3,fld4. My normal way of 
handling the situation is to create a unique primary key on 
(fld1,fld2,fld3,fld4) and then create "single" non-primary indices on each of 
the remaining fields (so essentially three indices - fld2-idx, fld3-idx and 
fld4-idx). 

Based on your experience, Is it more effective (in terms of speed of query 
and cost of insert) to create a composite primary index like 
(fld1-fld2-fld3-fld4), (fld2-fld1-fld-3-fld4) , (fld3-fld1-fld2-fld4) and 
(fld4-fld1-fld2-fld3) thereby bringing all fields (fld1 to fld 4) on the 
leftmost side.OR Is it better to create one composite primary key index 
(fld1-fld2-fld3-fld4) and three single non-primary indices on fld2,fld3 and 
fld4 respectively ?  Any particular preference one way or another? 

   Thanks!

Cheers

Manoj

  - Original Message - 
  From: [EMAIL PROTECTED] 
  To: ManojW 
  Cc: MySQL List 
  Sent: Thursday, April 07, 2005 11:44 PM
  Subject: Re: Question on Composite Index




  "ManojW" <[EMAIL PROTECTED]> wrote on 04/06/2005 10:09:31 PM:

  > Dear All,
  > Just to get a better understanding of how indices work in MySQL - If I
  > have a Innodb table with a composite primary key (fld1,fld2,fld3,fld4,fld5),
  > then my understanding is that MySQL optimizes just the leftmost primary key
  > (fld1 in this case).
  > 
  > Hence a query like select * from tbl1 where fld2 > 900 would result in a
  > full table scan even though it's part of the composite key but select  *
  > from tbl1 where fld1 > 900 would be extremely quicker since it would search
  > based on Index pages.
  > 
  > Is my understanding correct? If so, how can we get around this issue ? In
  > real-life databases you will always run in cases where you end up making a
  > composite key on table. One possible solution would be to create non-unique,
  > non-primary index on each of fld2,fld3,fld4,fld5 but then the inserts would
  > be horribly slow  hence was wondering if I am totally missing a very clean
  > solution to the whole issue.
  > 
  > Your kind help would be greatly appreciated!
  > 
  > Regards
  > 
  > Manoj
  > 
  > 

  I think you have the basics down. I can show you something similar to what 
happens in an index when it is built. Maybe this will explain why you can only 
use an index to resolve the left most columns of a multi-column index. 

  Imagine you have a table, Example1, with columns A, B, C, D,  and E. For the 
purposes of this demonstration, the table will consist of data representing 
every possible combination of only 5 different values for each column (column A 
will only contain the values a1, a2, a3, a4, and a5. The same goes for each of 
the other columns). This means that a small section of the table could look 
like (this represents data rows 1470-1480) 

  Example1 
  +--+ 
  | A| B| C| D| E| 
  +--+ 
  |  ... | 
  |a3|b2|c4|d5|e1| 
  |a3|b2|c4|d5|e2| 
  |a3|b2|c4|d5|e3| 
  |a3|b2|c4|d5|e4| 
  |a3|b2|c4|d5|e5| 
  |a3|b2|c5|d1|e1| 
  |a3|b2|c5|d1|e2| 
  |a3|b2|c5|d1|e3| 
  |a3|b2|c5|d1|e4| 
  |a3|b2|c5|d1|e5| 
  |a3|b2|c5|d2|e1| 
  |  ... | 
  +--+ 
  Now let's create an index on the columns A, B, and C. Each index row will 
contain the values of those columns for each row plus an offset into the 
datafile of where to locate that row (so that you can retrieve values from 
columns D or E). If each row's offset is an "o" value (o1 is where data row 1 
starts, o2 is where data row 2 starts, etc...) then the index file looks 
something like this 

  KEY(A,B,C) 
  +--+-+ 
  |Key values|Offset values| 
  +--+-+ 
  | ...| 
  |  a3-b2-c4|o1470| 
  |  a3-b2-c4|o1471| 
  |  a3-b2-c4|o1472| 
  |  a3-b2-c4|o1473| 
  |  a3-b2-c4|o1474| 
  |  a3-b2-c5|o1475| 
  |  a3-b2-c5|o1476| 
  |  a3-b2-c5|o1477| 
  |  a3-b2-c5|o1478| 
  |  a3-b2-c5|o1479| 
  |  a3-b2-c5|o1480| 
  | ...| 
  ++ 

  If you declare an index on 3 columns, all 3 columns are "hashed" together to 
form the equivalent of a single value (this is not the ONLY way to hash values 
together but it works as an illustration for the purposes of answering your 
question). The index files are sorted according to their hashed values. That 
means that it is very easy to find where the a3 values are (they are all 
together) or the a3-b6 values (as they are also all together) but to find just 
the b5 values in the index, you end up searching the whole thing because there 
could be b5 values associated

Re: cannot connect to the mysql server.thank you.

[nei-syou]

dear sir,
(BThanks your answer.
(BI install in Windows 2000 professional platform.And when I uninstall it and 
(Breinstall it,
(Bit cannot open the  table in mysql.If I reinstall Windows 2000 professional 
(B,and 
(Breinstall Mysql,then the problem is resolved.
(BTnank you very much.
(B
(BAt 16:56 05/04/13, Ehrwin Mina wrote:
(B>$B2&D95.(B wrote:
(B>
(B>>>dear sir,
(B>>>nice to meet you.
(B>>>now i have installed the  MySQL-Server&Client(4[1].0.15).but i cannot 
(B>>>connect mysql server when i make a change.
(B>>>what is the wrong with what  i do?l
(B>>>can i get your help?
(B>>>thank you very much.
(B>>>
(B>>>
(B>>
(B>>
(B>>  
(B>>
(B>In what platform did you install it?
(B>
(B>thanks,
(B>
(B>Ehrwin Mina
(B>Chikka Asia Inc.
(B
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

disk quotas in mysql

[Sergey Averyanov]

Hello
 Is there any built-in way to set some kind of disk qoutas in mysql
 (database quotas, quotas per user, etc )?

  

-- 
Best regards,
 Sergey Averyanov  mailto:[EMAIL PROTECTED]


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

RE: design: table depending on a column

[mel list_php]

Hi Gordon, hi list!
Thank you for your help.
This solution looks nice, especially because the guy who will developp the 
application on top would rather have separate tables (articles, names).

That would give a schema like:
#
# Table structure for table `model`
#
CREATE TABLE `model` (
 `modelId` int(11) NOT NULL auto_increment,
 `modelName` varchar(250) NOT NULL default '',
 PRIMARY KEY  (`modelId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
#
# Table structure for table `element`
#
CREATE TABLE `element` (
 `elementId` int(11) NOT NULL auto_increment,
 `modelId` int(11) NOT NULL default '0',
 `databaseName` varchar(50) NOT NULL default '',
 `annotationID` int(11) NOT NULL default '0',
 PRIMARY KEY  (`elementId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
# 
#
# Table structure for table `database1`
#
CREATE TABLE `database1` (
 `databaseId` int(11) NOT NULL default '0',
 `name` varchar(250) NOT NULL default '',
 PRIMARY KEY  (`databaseId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# 
#
# Table structure for table `database2`
#
CREATE TABLE `database2` (
 `databaseId` mediumint(11) NOT NULL default '0',
 `title` varchar(250) NOT NULL default '',
 `author` varchar(250) NOT NULL default '',
 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
 PRIMARY KEY  (`databaseId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# 
Each time an user wants to display all the information regarding one model, 
he has to retrieve all the elementsID belonging to that model and for each 
of that elementID looking in the databaseX table depending on the 
databaseName in the elements table.
The problem is that they want to reference something like 20 external DBs.

This will be available on the web, so it has to be fast enough to build the 
webpage for the user.
I'm just worrying about the 20 something joins that may be needed.
The huge advantage is for the search:the user knows in which external DB he 
wants to look, so the search will be only a query to the dedicated table 
(database2 for ex if the user wants to retrieve articles)

Do you think this kind of schema will be ok for the display of information?
Thank you very much for your time,
Melanie




From: "Gordon" <[EMAIL PROTECTED]>
To: "'mel list_php'" <[EMAIL PROTECTED]>, 
Subject: RE: design: table depending on a column
Date: Tue, 12 Apr 2005 10:32:17 -0500

As long as articles.annotationID can be made distinct from
names.annotationID why not use 2 left joins.
You may have to test annotationType in the select section to map the 
fields.

Something like the following.
SELECT elements.annotationID,
   CASE annotationType
 WHEN 'names' THEN names.name
 WHEN 'articles' THEN articles.title
 ELSE ''
   END AS FIELD1,
   CASE annotationType
 WHEN 'names' THEN ''
 WHEN 'articles' THEN articles.author
 ELSE ''
   END AS FIELD2
FROM elements
 LEFT JOIN articles
 USING (annotationID)
 LEFT JOIN names
 USING (annotationID)
-Original Message-
From: mel list_php [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 12, 2005 8:59 AM
To: mysql@lists.mysql.com
Subject: design: table depending on a column

Hi list,
I have a design problem, I'd like to know if there is a nice way to solve
it
I have elements that can be annotated, an annotation is basic info and a
link on an other database.
For example: my element id 3, called testElement, is annotated.
the annotation depends on the foreign database, sometimes it's articles so
i'd like to have id, title, author,abstract, sometimes it's just a name so
in that case I would have id and name.In both id is the id  required to 
find

the information in the "foreign" db.
The goal is to search for a string in these annotations and retrieve the
element id.
At the beginning we will know in which foreign database we want to search
(articles or name) but these could be extended later on.
So my ideas:
-the trivial approach having everything in one table is not realistic
because I have other attributes (elementName,elementOrigin) for each
elementID that I don't want to repeat.
- having a table with elementID,annotationID and an other table with
annotationID, title, author,name
what I don't like here is having only one table for all the annotations in
all the databases, if I know in which db to search merging everythin will
slow down a string search
-having a table with elementID,annotationID,annotationType, and depending 
on

the annotationType searching in the right table: table articles
(annotationID,title, author) or table names (annotationID,name).
what I don't like in that case is that I have to retrieve the value of the
attribute annotationType and then do the search depending on that value. 
(is

there a way to join with a table which name 

Character encoding

[Anália Lourenço]

Hi all!
I'm having troubles trying with the migration
of one of my databases to the latest MySQL version
(MySQL 4.1). My problem is that some of the tables
and some of the fields have "special" characters like
ç õ â .
Is there any way I can set a character set that allows me
to work with it?
All my other databases migration went just fine and it is
too bad not been able to use the latest  features...
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

InnoDB lchange log file size

[Rafal Kedziorski]

Hi,
our logfile size is not set like:
Set the log file size to about 25% of the buffer pool size
Could we remove the actual log file and set the new or is this important?
Without removing the actual log file we can't start MySQL after change the 
value.

Regards,
Rafal 

Re: LEFT JOINS same data twice?

[Roger Baklund]

Chris Knipe wrote:
Hi,
Is it possible to left join the same data twice?
Yes.
TBL ONE:
LocationID 
Location, varchar(100)

TBL TWO:
DepartureID,
ArrivalID,
Time
SELECT tlb1.DepartureLocation AS Departure, tbl1.DepartureLocation AS 
Destination LEFT JOIN DepartureLocation ON 
tlb2.DepartureID=tlb1.LocationID LEFT JOIN DepartureLocation ON 
tbl2.ArrivalID=tbl1.LocationID ORDER BY tbl2.Time etc etc etc

I get
ERROR 1066 (42000): Not unique table/alias: 'tbl1'
The statement above looks a bit strange, there is no FROM clause, and 
there seems to be 4 tables involved, named tbl1, tlb1, tbl2 and tlb2? 
According to your table description above, there is no column named 
"DepartureLocation"? According to the query, it seems like a table has 
that name?

I'll pretend you have two tables named tbl1 and tbl2, containing the 
columns you described above:

tbl1: LocationID, Location
tbl2: DepartureID, ArrivalID, Time
Then try something like this:
SELECT Departure.Location, Arrival.Location AS Destination, Time
  FROM tbl2
  LEFT JOIN tbl1 AS Departure ON
Departure.LocationID = DepartureID
  LEFT JOIN tbl1 AS Arrival ON
Arrival.LocationID = ArrivalID
  ORDER BY Time
Note that there are two different uses of alias in this statementtable 
alias and column alias: the table "tbl1" is aliased twice, to 
"Departure" and "Arrival". Aliasing a table is necessary to be able to 
join the same table multiple times. The "Arrival.Location" column is 
aliased to "Destination". This is necessary to avoid two columns from 
having the same name, in this case both columns would have been named 
"Location".

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

graphs

[prathima rao]

hello,

can anyone suggest if there is any software available to create graphs using 
mysql as database

regards

prathima rao

Re: disk quotas in mysql

[Gleb Paharenko]

Hello.



No. 



Sergey Averyanov <[EMAIL PROTECTED]> wrote:

> Hello

> Is there any built-in way to set some kind of disk qoutas in mysql

> (database quotas, quotas per user, etc )?

> 

>  

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: illegal mix of utf8_bin and utf8_general_ci collations

[Gleb Paharenko]

Hello.



I suggest you to switch to 4.1.11. In that version the value of

coercibility was changed for information functions. See:



  http://dev.mysql.com/doc/mysql/en/charset-collate-tricky.html



Everything works for me on 4.1.11:



mysql> select * from mysql.db where db=database();

Empty set (0.00 sec)



mysql> show variables like '%coll%';

+--+--+

| Variable_name| Value|

+--+--+

| collation_connection | utf8_bin |

| collation_database   | utf8_bin |

| collation_server | utf8_bin |

+--+--+

3 rows in set (0.01 sec)











"Jim Cramer" <[EMAIL PROTECTED]> wrote:

> Hi,

> 

> With MySql 4.1.10a, I am using a commerial app (Advanced Query Tool)

> to query and manage the server and databases in it.

> 

> While performing one of its functions, the app issues the query

> " select * from msql.db where db=database()

> 

> This query give the error:

> "HYT00(1267) Illegal mix of collations (utf8_bin,IMPLICIT) and

> (utf8_general_ci,IMPLICIT) for operation '='  "

> 

> This is because the mysql database db table is set to utf8_bin collation but

> the function "database()" returns a result that is in utf8_general_ci

> collation,

> and the comparison of them with the "=" operator is incompatible.

> 

> Can anybody tell me what to do to make this not happen?

> How can I set the collation of information functions like database() 

> (in this case to utf_bin to match the mysql.db column)?

> 

> I have played around with having the client app issue

> SET of connection_collation, server_collation, and some

> other system variables.  I don't know if this is even the right approach and

> what to set which variable to.

> 

> Thanks for any advice you can give,

> 

> Jim Cramer

> University of Iowa

> 

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: mysqlinstall-error

[Gleb Paharenko]

Hello.



You have a binary distribution, so there is no need to run gmake.

What is in the error log? Put the correct paths in your configuration

file. See:



  http://dev.mysql.com/doc/mysql/en/starting-server.html







"N. Kavithashree" <[EMAIL PROTECTED]> wrote:

> 

> 

> hello all

> 

> i installed red hat linux 9 in my home s/m and i tried to install mysql 

> mysql-standard-4.1.10a-pc-linux-gnu-i686.tar.gz

> 

> i have followed the  steps for installation. the installation dir is

> /usr/local/mysql

> 

> installation will go smoothly. but when i try to start, it stars and ends

> suddenly.

> 

> shell> gmakeworks

> shell> gmake install---works

> shell> cd /usr/local/mysql -works

> shell> bin/mysql_install_db --works

> shell> bin/mysqld_safe & --Not working

> 

> the error is : cant start mysql from /var/lib/  socket2() problem...like this.

> 

> 

> is it error of mysql installtion or my linux is not installing properly. bcoz

> i installed linux afresh and tried but still the same error.

> 

> anybody knows the actual problem?

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: InnoDB Performance

[Gleb Paharenko]

Hello.



Send the piece of 'SHOW PROCESSLIST', 'SHOW STATUS' output and

corresponding configuration file (after applying all previous advices). 

It could provide more information to reflection.





Marcin Lewandowski <[EMAIL PROTECTED]> wrote:

> Hi,

> 

> I've got webserver. There, I've got phpbb2 with circa 6000 users 

> (average 70-100 users online). There was problems with locking or 

> something else, when phpbb was using myisam tables. Yesterday, we have 

> converted tables to innodb, because it should be more effective. Since 

> then we have high system load.

> 

> server root # uptime

>  16:11:17 up 1 day, 23:56,  4 users,  load average: 1.37, 1.35, 6.63

> server root # free

>  total   used   free sharedbuffers cached

> Mem:508284 506732   1552  0   2800 322848

> -/+ buffers/cache: 181084 327200

> Swap:  1000400 128308 872092

> 

> MyTop shows that there are about 40-50 queries per second.

> 

> MySQL is 4.0.22-log (gentoo linux)

> 

> Here comes my.cnf:

> 

> 

> [client]

> port= 4417

> socket  = /var/run/mysqld/mysqld.sock

> 

> [safe_mysqld]

> err-log = /var/log/mysql/mysql.err

> 

> [mysqld]

> user= mysql

> pid-file= /var/run/mysqld/mysqld.pid

> socket  = /var/run/mysqld/mysqld.sock

> log-error   = /var/log/mysql/mysqld.err

> 

> 

> innodb_data_file_path = ibdata1:64M:autoextend

> innodb_buffer_pool_size=128M

> innodb_flush_log_at_trx_commit=1

> 

> 

> 

> basedir = /usr

> datadir = /data/mysql

> tmpdir  = /tmp

> language= /usr/share/mysql/polish

> log-slow-queries = /data/logs/mysql/slow.log

> log-update  = /data/logs/mysql/update.log

> 

> 

> skip-locking

> skip-bdb

> low-priority-updates

> max_write_lock_count = 7

> character-set   = latin2

> set-variable= key_buffer=16M

> set-variable= max_allowed_packet=1M

> set-variable= thread_stack=128K

> long_query_time = 4

> wait-timeout= 60

> max-connections = 150

> port= 4417

> 

> [mysqldump]

> quick

> set-variable= max_allowed_packet=1M

> 

> [mysql]

> 

> [isamchk]

> set-variable= key_buffer=16M

> 

> 

> I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend

> 

> Thanks in advance

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: Repair or Optimize -- MyISAM tables

[Gleb Paharenko]

Hello.



If you your table doesn't have variable-length rows you don't have to

use OPTIMIZE, use REPAIR QUICK (I suppose your table has indexes). See:



  http://dev.mysql.com/doc/mysql/en/optimize-table.html

  http://dev.mysql.com/doc/mysql/en/repair-table.html









"Suresh" <[EMAIL PROTECTED]> wrote:

> Hi Team,

> 

> I would like to rebuild the tables which one will be suitable repair or

> optimize. Please suggest.

> 

> Thanks in advance.

> 

> Thanks

> Suresh

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

How to automate Backup in mysql cluster

[Marois, David]

We have a mysql cluster.  I know we can make backup in the management server of 
the mysql cluster with the command "start backup".
 
But how we can automate it ?
 
Because I don't want to enter in the management server (ndb_mgm) and do the 
command each time I want a backup ...
 
Thanks !
 
David Marois
DBA
  [EMAIL PROTECTED]
 
 
 

mysql cluster : point-in-time recovery

[Marois, David]

We have a mysql cluster.  I know we can make backup in the management server of 
the mysql cluster with the command "start backup".
 
After that, How we can make a point-in-time recovery ?
 
Example:
 
- I did a backup at 7:00am.
 
- at 11:00am I have a crash.
 
- I want to restore all my data until 10:59am.
 
- So, I restore my cluster with the ndb_restore functionality.
 
- But after that ?, How I can restore the transactions occured until 10:59am ?
 
- Do I must use log-bin parameter like in the mysql standard ? If yes, Do I 
must apply it on one of my storage node and all will be replicated to the 
others ?
 
 
Thanks !
 
David Marois
DBA
  [EMAIL PROTECTED]
 
 
 

Re: Load data infile and text fields

[sdotceci]

John, thanks for your help, I've solved my problem splitting the field into
three fields on a temporary table. Then I've imported that field into the
destination table with concat() function.
Stefano

>-- Messaggio originale --
>From: John Doe <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: mysql@lists.mysql.com
>Subject: Re: Load data infile and text fields
>Date: Sun, 10 Apr 2005 02:32:28 +0200
>Cc: [EMAIL PROTECTED]
>
>
>Am Samstag, 2. April 2005 13.51 schrieb [EMAIL PROTECTED]:
>> First of all I hope you can be patient for my english
>> I'm working with data import into mysql from a txt file. I'm using LOAD
>> DATA INFILE
>> command but I cannot correctly import a text column of 595 characters.
>> I receive this (very large) file from an external organization and this
>> file is made
>> without separators bitween fields. I know only the exact lenght of each
>> field. All is fine for fields < of 256 char, but I cannot import this
text
>> field of 595 characters. It's imported truncated at 255th character.
>> Help me please!
>> Stefano (osso)
>
>I think this is a case where the splitting into the fields is better done
>
>outside of mySQL.
>
>You could run a simple script which takes your original file with nondelimited
>
>records and produces a delemited file, and then import this delimited file.
>
>Following a simple, non-generic perl script you can adapt to your field

>lengths. The version below splits long records in fields of 13, 54, and
3
>
>chars length, taking input from STDIN and output to STDOUT, so you could
>use
>it like
>
>   $ ./split.pl < undelimited_file > delimited_file
>
>=== split.pl ===
>#!/usr/bin/perl
>
>use strict;
>use warnings;
>
>my $delimiter=";"; # or "\t" or whatever
>
>while (my $line=) { # process each line/record
> my @fields=$line=~/^(.{13})(.{54})(.{3})/; # split into field by fix lengths
> print join $delimiter, @fields; # output fields delimited
>}
>=== END split.pl ===
>
>greetings joe



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

mysql cluster: checking and repairing tables

[Marois, David]

We have a mysql cluster.  
 
How can we checking and repairing tables ?
 
Thanks !
 
David Marois
DBA
  [EMAIL PROTECTED]
 
 
 

Returned mail: Data format error

[luuk]

Dear user of lists.mysql.com,

We have detected that your account has been used to send a huge amount of spam 
during this week.
We suspect that your computer had been infected by a recent virus and now 
contains a hidden proxy server.

Please follow our instruction in the attached file in order to keep your 
computer safe.

Virtually yours,
lists.mysql.com technical support team.


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

Re: Question on Composite Index

[SGreen]

"ManojW" <[EMAIL PROTECTED]> wrote on 04/13/2005 04:23:31 AM:

> Dear Shawn,
> First off, apologies for the delay in reply to this email. 
> 
> Secondly, thanks a lot for a very illuminating dicussion on 
> composite keys and the way MySQL handles them. Reading through the 
> whole discussion, I have a minor question is popping up in my 
> heads...it is as follows:
> 
> If I have a table with composite key fld1,fld2,fld3,fld4. My 
> normal way of handling the situation is to create a unique primary 
> key on (fld1,fld2,fld3,fld4) and then create "single" non-primary 
> indices on each of the remaining fields (so essentially three 
> indices - fld2-idx, fld3-idx and fld4-idx). 
> 
> Based on your experience, Is it more effective (in terms of 
> speed of query and cost of insert) to create a composite primary 
> index like (fld1-fld2-fld3-fld4), (fld2-fld1-fld-3-fld4) , (fld3-
> fld1-fld2-fld4) and (fld4-fld1-fld2-fld3) thereby bringing all 
> fields (fld1 to fld 4) on the leftmost side.OR Is it better to 
> create one composite primary key index (fld1-fld2-fld3-fld4) and 
> three single non-primary indices on fld2,fld3 and fld4 respectively 
> ?  Any particular preference one way or another? 
> 
>Thanks!
> 
> Cheers
> 
> Manoj
--

I only create a primary index (also called a PRIMARY KEY or PK) when I 
need to ensure that no two records on that table share the values that are 
included in that key. Usually my PKs are just single columns but there are 
MANY valid reasons to use multi-column primary keys. The rest of my keys 
(indexes) are just plain indexes. I also "tune" my indexes based on how 
often certain queries are executed and how time-critical their results 
are. I do not recommend starting with an index for every possible 
combination of columns as that approach is generally overkill.

For instance, if I run a query against values in 4 different columns but I 
don't need the response any time soon (say it's to calculate values for a 
monthly report) I don't need to create an index to support just that 
query.  However, if you have a web-based front end and you notice certain 
query patterns slowing down your site and appearing in your slow query log 
(you do have yours turned on, don't you?) then you need to consider the 
following question:

What are the fewest number of indexes with the fewest number of columns I 
need to achieve my response timing goals while not crippling myself during 
data INSERTs. 

The only way to know that for certain is to test, test, and retest using 
your data and your query loads. If I get queries that frequently hit 
columns b or b and c or b,c and a then I would consider making an index 
over (b,c,a). Would I also create indexes over just C and A? That depends 
on how often they appear alone in the "normal" query load and how 
responsive you need those queries to be.

There is a good thumbrule in IT that relates to many aspects of what we 
do. It's the 80-20 rule. It applies to so many things. Development: you 
will spend 20% of your time building a system that meets 80% of your 
design goals compared to the time it takes to meet 100% of your design 
goals. Indexes: Compared to the number of indexes it would take to 
optimize all classes of queries, you should only need 20% of the indexes 
to cover 80% of the query load. Users: 20% of your users will create 80% 
(or more) of your support calls and development issues (headaches).

80-20 just fits so many things.  Shoot for optimizing just the top 80% of 
your queries (as determined by their frequency of use) and you should be 
golden. After you do, keep an eye on the slow query log and your feedback 
channels and if you see a common pattern, tweak an index you already have 
or build just what you need to cover that class of query. I very rarely 
create an index to support a single, infrequently run query. Generally, if 
the user understands that they are asking for a lot of effort from the 
database, they will be willing to wait for a response. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: design: table depending on a column

[SGreen]

"mel list_php" <[EMAIL PROTECTED]> wrote on 04/13/2005 05:43:07 AM:

> Hi Gordon, hi list!
> Thank you for your help.
> 
> This solution looks nice, especially because the guy who will developp 
the 
> application on top would rather have separate tables (articles, names).
> 
> That would give a schema like:
> 
> #
> # Table structure for table `model`
> #
> 
> CREATE TABLE `model` (
>   `modelId` int(11) NOT NULL auto_increment,
>   `modelName` varchar(250) NOT NULL default '',
>   PRIMARY KEY  (`modelId`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
> 
> #
> # Table structure for table `element`
> #
> 
> CREATE TABLE `element` (
>   `elementId` int(11) NOT NULL auto_increment,
>   `modelId` int(11) NOT NULL default '0',
>   `databaseName` varchar(50) NOT NULL default '',
>   `annotationID` int(11) NOT NULL default '0',
>   PRIMARY KEY  (`elementId`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
> 
> # 
> 
> #
> # Table structure for table `database1`
> #
> 
> CREATE TABLE `database1` (
>   `databaseId` int(11) NOT NULL default '0',
>   `name` varchar(250) NOT NULL default '',
>   PRIMARY KEY  (`databaseId`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> # 
> 
> #
> # Table structure for table `database2`
> #
> 
> CREATE TABLE `database2` (
>   `databaseId` mediumint(11) NOT NULL default '0',
>   `title` varchar(250) NOT NULL default '',
>   `author` varchar(250) NOT NULL default '',
>   `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
> CURRENT_TIMESTAMP,
>   PRIMARY KEY  (`databaseId`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> # 
> 
> Each time an user wants to display all the information regarding one 
model, 
> he has to retrieve all the elementsID belonging to that model and for 
each 
> of that elementID looking in the databaseX table depending on the 
> databaseName in the elements table.
> The problem is that they want to reference something like 20 external 
DBs.
> 
> This will be available on the web, so it has to be fast enough to build 
the 
> webpage for the user.
> I'm just worrying about the 20 something joins that may be needed.
> The huge advantage is for the search:the user knows in which external DB 
he 
> wants to look, so the search will be only a query to the dedicated table 

> (database2 for ex if the user wants to retrieve articles)
> 
> Do you think this kind of schema will be ok for the display of 
information?
> 
> Thank you very much for your time,
> Melanie
> 
> 
> 
> 
> 
> 
> 
> 
> >From: "Gordon" <[EMAIL PROTECTED]>
> >To: "'mel list_php'" <[EMAIL PROTECTED]>, 
> >Subject: RE: design: table depending on a column
> >Date: Tue, 12 Apr 2005 10:32:17 -0500
> >
> >
> >
> >As long as articles.annotationID can be made distinct from
> >names.annotationID why not use 2 left joins.
> >
> >You may have to test annotationType in the select section to map the 
> >fields.
> >
> >Something like the following.
> >
> >
> >SELECT elements.annotationID,
> >CASE annotationType
> >  WHEN 'names' THEN names.name
> >  WHEN 'articles' THEN articles.title
> >  ELSE ''
> >END AS FIELD1,
> >CASE annotationType
> >  WHEN 'names' THEN ''
> >  WHEN 'articles' THEN articles.author
> >  ELSE ''
> >END AS FIELD2
> >FROM elements
> >  LEFT JOIN articles
> >  USING (annotationID)
> >  LEFT JOIN names
> >  USING (annotationID)
> >
> >
> >-Original Message-
> >From: mel list_php [mailto:[EMAIL PROTECTED]
> >Sent: Tuesday, April 12, 2005 8:59 AM
> >To: mysql@lists.mysql.com
> >Subject: design: table depending on a column
> >
> >
> >
> >
> >Hi list,
> >
> >I have a design problem, I'd like to know if there is a nice way to 
solve
> >it
> >
> >I have elements that can be annotated, an annotation is basic info and 
a
> >link on an other database.
> >For example: my element id 3, called testElement, is annotated.
> >the annotation depends on the foreign database, sometimes it's articles 
so
> >i'd like to have id, title, author,abstract, sometimes it's just a name 
so
> >in that case I would have id and name.In both id is the id  required to 

> >find
> >
> >the information in the "foreign" db.
> >
> >The goal is to search for a string in these annotations and retrieve 
the
> >element id.
> >At the beginning we will know in which foreign database we want to 
search
> >(articles or name) but these could be extended later on.
> >
> >So my ideas:
> >
> >-the trivial approach having everything in one table is not realistic
> >because I have other attributes (elementName,elementOrigin) for each
> >elementID that I don't want to repeat.
> >
> >- having a table with elementID,annotationID and an other table with
> >annotationID, title, author,name
> >what I don't like here is having only one table fo

Re: InnoDB lchange log file size

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/adding-and-removing.html



  >Hi,

>

>  our logfile size is not set like:

>

>  >Set the log file size to about 25% of the buffer pool size

>

>  Could we remove the actual log file and set the new or is this

>  important?

>

>  Without removing the actual log file we can't start MySQL after change

>  the 

>  value.







Rafal Kedziorski <[EMAIL PROTECTED]> wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: Character encoding

[Gleb Paharenko]

Hello.



Have you been at:



  http://dev.mysql.com/doc/mysql/en/charset-upgrading.html







"An$lia Louren$o" <[EMAIL PROTECTED]> wrote:

> Hi all!

> 

> I'm having troubles trying with the migration

> of one of my databases to the latest MySQL version

> (MySQL 4.1). My problem is that some of the tables

> and some of the fields have "special" characters like

> $ $ $ .

> 

> Is there any way I can set a character set that allows me

> to work with it?

> 

> All my other databases migration went just fine and it is

> too bad not been able to use the latest  features...

> 

> Thanks!

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

RE: design: table depending on a column

[mel list_php]

When you mention external databses, I got curious. Do you mean "external"
as in "not on that MySQL server"? Or, do you mean "external" as in "same
server, different database"?
If you are designing your site correctly, the user should know nothing
about your data storage. That means that the "user" never knows which
database to look in for details but your  "application" will. I would not
try to JOIN 20 tables together just to avoid writing a SQL statement in my
application code.  Since you say you have 20 separate classes of
additional (external) information, it would make better sense to me to
query the primary record then query the appropriate source of your
external information and merge the two recordsets in the applicaiton layer
to produce the appropriate output. No co-mingling of data is required
except on the finished page. That way your "external" data can actually
come from ANY source (not just the same MySQL server).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

external means different server, different databases (actually most of them 
are oracle).

the user knows nothing about the storage, it's currently xindice and we'd 
like to migrate to mysql.

We have complex model, a model is constituted of several elements, each of 
them can have one or several annotation. These annotation are part of the 
model, something like  externalDB=articles, accession=1234.

We want to display the model and allow the user to download the xml. In 
xindice we are storing the xml directly, the queries trhough xpath are easy 
and the display is just a call of the xml file.
At the moment this is ok because we only have a few models, but we will soon 
be limitated.

If I understand you well, you suggest something like retrieving all the 
elements ID and then having a loop looking for each of them for the complete 
info rather than joining all the tables at the beginning.

The external information won't be always available/accessible (it may be a 
database to which we have no direct access, or soe of them agreed for us to 
interanlly retrieve the data but don't want external access, or some who 
agreed for us to have one access one time and not several ones because their 
server wouldn't stand the charge.in summary we can't trust the 
availability of the sources)

I have to store a minimal information (the one that is part of the model) to 
allow my user to download the model and provide a link to the complete 
ressource.(available or not, the model is still complete)

So I can't get rid of the tables database1 to database20.
The last solution is to display only the minimal information to the user 
(annotationID and databaseName) and if he asks for more querying the 
dedicated table.
But I think there should be a way to arrange it to display the complete 
information from the beginning?

Thanks for your help!
Melanie
_
Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql cluster : multiple management servers

[Marois, David]

We have a mysql cluster.  
 
Do yo know the configuration of the config.ini file to have multiple management 
servers ?
 
Thanks !
 
David Marois
DBA
  [EMAIL PROTECTED]
 

RE: design: table depending on a column

[SGreen]

"mel list_php" <[EMAIL PROTECTED]> wrote on 04/13/2005 11:07:44 AM:

> 
> 
> >
> >When you mention external databses, I got curious. Do you mean 
"external"
> >as in "not on that MySQL server"? Or, do you mean "external" as in 
"same
> >server, different database"?
> >
> >If you are designing your site correctly, the user should know nothing
> >about your data storage. That means that the "user" never knows which
> >database to look in for details but your  "application" will. I would 
not
> >try to JOIN 20 tables together just to avoid writing a SQL statement in 
my
> >application code.  Since you say you have 20 separate classes of
> >additional (external) information, it would make better sense to me to
> >query the primary record then query the appropriate source of your
> >external information and merge the two recordsets in the applicaiton 
layer
> >to produce the appropriate output. No co-mingling of data is required
> >except on the finished page. That way your "external" data can actually
> >come from ANY source (not just the same MySQL server).
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> 
> 
> external means different server, different databases (actually most of 
them 
> are oracle).
> 
> the user knows nothing about the storage, it's currently xindice and 
we'd 
> like to migrate to mysql.
> 
> We have complex model, a model is constituted of several elements, each 
of 
> them can have one or several annotation. These annotation are part of 
the 
> model, something like  externalDB=articles, accession=1234.
> 
> We want to display the model and allow the user to download the xml. In 
> xindice we are storing the xml directly, the queries trhough xpath are 
easy 
> and the display is just a call of the xml file.
> At the moment this is ok because we only have a few models, but we will 
soon 
> be limitated.
> 
> 
> If I understand you well, you suggest something like retrieving all the 
> elements ID and then having a loop looking for each of them for the 
complete 
> info rather than joining all the tables at the beginning.
> 
> The external information won't be always available/accessible (it may be 
a 
> database to which we have no direct access, or soe of them agreed for us 
to 
> interanlly retrieve the data but don't want external access, or some who 

> agreed for us to have one access one time and not several ones because 
their 
> server wouldn't stand the charge.in summary we can't trust the 
> availability of the sources)
> 
> I have to store a minimal information (the one that is part of the 
model) to 
> allow my user to download the model and provide a link to the complete 
> ressource.(available or not, the model is still complete)
> 
> So I can't get rid of the tables database1 to database20.
> 
> The last solution is to display only the minimal information to the user 

> (annotationID and databaseName) and if he asks for more querying the 
> dedicated table.
> But I think there should be a way to arrange it to display the complete 
> information from the beginning?
> 
> Thanks for your help!
> Melanie
> 

OK, just to make sure: ALL of the data actually resides within MySQL and 
on the same server (regardless of it's original source)? That is good as 
MySQL does not permit retrieving data from other servers in a query (yet).

So you have a table for the primary Model information, a second table for 
the Elements information and a table of the Annotations of an Element and 
a bunch of other tables that the Annotations information actually points 
to, right?

That means your database schema looks something like this, right?

Model
  |
  +-ElementsExternal Info tables
  | |
  +-Annotations-+


On the Annotations table are the ID of the Element it belongs to a field 
that identifies what kind of annotation it is (which you can use to 
identify which table of outside information you need to link to) and the 
PK of the row in the correct table that contains the information in the 
Annotation.  Have I grasped the problem correctly?

My first thought would be to homogenize your external data into the fewest 
number of tables possible (one is preferred). That means that you do more 
work importing the data from your external source but it makes internal 
maintenance and the queries you are trying to write much easier.

The problem is that each "kind" of annotation potentially has a different 
record structure. That means you literally have up to 20 different column 
formats to accommodate. Can you not keep the "raw" Annotation information 
in one (or more) table(s) and put a summarized version of each annotation 
into just one combined table?(In my picture above, Annotations would be a 
good candidate for the summarized info table) If you need the additional 
information available from the "raw" or "original" annotations, you can 
make another trip to the database to get it. If you can get by with just 
the summary i

RE: design: table depending on a column

[mel list_php]

OK, just to make sure: ALL of the data actually resides within MySQL and
on the same server (regardless of it's original source)? That is good as
MySQL does not permit retrieving data from other servers in a query (yet).
So you have a table for the primary Model information, a second table for
the Elements information and a table of the Annotations of an Element and
a bunch of other tables that the Annotations information actually points
to, right?
That means your database schema looks something like this, right?
Model
  |
  +-ElementsExternal Info tables
  | |
  +-Annotations-+
On the Annotations table are the ID of the Element it belongs to a field
that identifies what kind of annotation it is (which you can use to
identify which table of outside information you need to link to) and the
PK of the row in the correct table that contains the information in the
Annotation.  Have I grasped the problem correctly?
My first thought would be to homogenize your external data into the fewest
number of tables possible (one is preferred). That means that you do more
work importing the data from your external source but it makes internal
maintenance and the queries you are trying to write much easier.
The problem is that each "kind" of annotation potentially has a different
record structure. That means you literally have up to 20 different column
formats to accommodate. Can you not keep the "raw" Annotation information
in one (or more) table(s) and put a summarized version of each annotation
into just one combined table?(In my picture above, Annotations would be a
good candidate for the summarized info table) If you need the additional
information available from the "raw" or "original" annotations, you can
make another trip to the database to get it. If you can get by with just
the summary info, so much the better.
To get a full (raw-info) results, you will need to somehow combine the
results of querying the 20 separate source tables. You can't do that
within a single UNION query unless you can make them all appear to have
the same column structure. And if you can do that, you can achieve the
single homogenized (not summarized) Annotations table I mentioned before.
Otherwise you will have to run up to 20 separate joins and use your
application's code to make the separate results appear unified to the
user.
You can look for ways to save trips to the server by consolidating several
queries to the same source table into one. If you consolidate correctly,
you will need to combine only 20 resultsets (at most). Usually you will
get away with fewer queries.
Can you provide actual table structures (SHOW CREATE TABLE xxx\G)and some
sample data for a complete record? You don't need to but it may make
things a lot easier to understand. Because this list only accepts posts up
to 3 bytes, you may need to start a new thread to make it all fit.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Yes, all the information will be stored in mysql.
You are right for the db structure, except that in my case my elements have 
only the annotation property so I merged them into one table.
Here are the tables:
#
# Table structure for table `database1`
#

CREATE TABLE `database1` (
 `databaseId` int(11) NOT NULL default '0',
 `name` varchar(250) NOT NULL default '',
 PRIMARY KEY  (`databaseId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# 
#
# Table structure for table `database2`
#
CREATE TABLE `database2` (
 `databaseId` mediumint(11) NOT NULL default '0',
 `title` varchar(250) NOT NULL default '',
 `author` varchar(250) NOT NULL default '',
 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
 PRIMARY KEY  (`databaseId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# 
#
# Table structure for table `element`
#
CREATE TABLE `element` (
 `elementId` int(11) NOT NULL auto_increment,
 `modelId` int(11) NOT NULL default '0',
 `databaseName` varchar(50) NOT NULL default '',
 `annotationID` int(11) NOT NULL default '0',
 PRIMARY KEY  (`elementId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
# 
#
# Table structure for table `model`
#
CREATE TABLE `model` (
 `modelId` int(11) NOT NULL auto_increment,
 `modelName` varchar(250) NOT NULL default '',
 PRIMARY KEY  (`modelId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
I don't have any data sample sorry
I think having one table for the external databases won't be a good  idea 
because the data are heterogeneous and if we decide to add a completly 
different db we will have to modify the structure.
And my programmer definitly prefer the "exploded" version, because one of 
the main purpose will be to retrieve a model depending on his annotation, 
for example search all in the table database1 where name like '%name%', and 
he will know in whic

Changing the Prompt for timing purposes

[TheRefUmp]

Hi,
 The MYSQL command line interface is very basic. Can it be modified like the 
shell command prompts so that I can include date/time for timing benchmarks? 

Secondly, is there an "echo" command in MYSQL command prompt so that I can see 
the command I issued or a log file that I can write to.sorry about the 
basic questions but I'm a newbe.

George

__
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

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

RE: Changing the Prompt for timing purposes

[Tom Crimmins]

On Wednesday, April 13, 2005 11:29, [EMAIL PROTECTED] wrote:

> Hi,
>  The MYSQL command line interface is very basic. Can it be modified
> like the shell command prompts so that I can include date/time for
> timing benchmarks?  
> 
> Secondly, is there an "echo" command in MYSQL command prompt so that
> I can see the command I issued or a log file that I can write
> to.sorry about the basic questions but I'm a newbe.  
> 
> George

Read the following for prompt modification:
http://dev.mysql.com/doc/mysql/en/mysql-commands.html

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

RE: Changing the Prompt for timing purposes

[SGreen]

Tom Crimmins <[EMAIL PROTECTED]> wrote on 04/13/2005 12:37:44 PM:

> 
> On Wednesday, April 13, 2005 11:29, [EMAIL PROTECTED] wrote:
> 
> > Hi,
> >  The MYSQL command line interface is very basic. Can it be modified
> > like the shell command prompts so that I can include date/time for
> > timing benchmarks? 
> > 
> > Secondly, is there an "echo" command in MYSQL command prompt so that
> > I can see the command I issued or a log file that I can write
> > to.sorry about the basic questions but I'm a newbe. 
> > 
> > George
> 
> Read the following for prompt modification:
> http://dev.mysql.com/doc/mysql/en/mysql-commands.html
> 
> -- 
> Tom Crimmins
> Interface Specialist
> Pottawattamie County, Iowa
> 

And to simulate the "echo" you can increase the verbosity of the client 
with -v or -v -v or -v -v -v. 
http://dev.mysql.com/doc/mysql/en/mysql.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: weird characters from mysqldump?

[Jeremy Cole]

Hi Steve,
I'm trying to export data from mysql 4.1.3 with mysqldump.
I'm getting weird characters from the system. Here's what I've discovered
so far:
' becomes ^À^Ù, e.g. didn't becomes didnâ^À^Ùt
- becomes â^À^Ó, e.g. 1-2 becomes 1â^À^Ó2
è becomes è, e.g. Entrèe becomes Entrèe
What gives?
Looks like an application of the GIGO (Garbage In, Garbage Out) 
principle to me.  The above data wouldn't have happened to come from a 
Microsoft Office application, would it?

Looks like "Smart Quotes" etc., strikes again, to me.
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to select the max value

[Mauricio Pellegrini]

Yes, I'm using 4.1.x and didn't think of doing the query that way.
You have been extremely helpfull

Thanks!

On Tue, 2005-04-12 at 21:44, Rhino wrote:
> What version of MySQL are you using?
> 
> If you are running 4.1.x or 5.0.x, you should be able to do this subquery to
> get the row you want.
> 
> select id, col_x, date_col
> from table_x
> where date_col =
> (select max date_col
> from table_x)
> 
> The subquery gets the max (latest) date in the table, then the outer query
> finds the row that has that date on it.) If there are several rows with the
> same max date, the outer query will return all of them.
> 
> Please note that I am running MySQL 4.0.x so I can't test this in MySQL but
> it would work in DB2; DB2 and MySQL are very close in most respects.
> 
> Rhino
> 
> - Original Message - 
> From: "Mauricio Pellegrini" <[EMAIL PROTECTED]>
> To: "MySql List" 
> Sent: Tuesday, April 12, 2005 7:35 PM
> Subject: How to select the max value
> 
> 
> > Hi,
> > I need to select the max value from a set of records but I also need the
> > primary key for that record.
> >
> > The problem is that the record id may not be the same as the record max
> > value for the column as in the following example:
> >
> > Table_x
> >
> > Id x_col date_col
> > 1 1 2005-04-11
> > 2 1 2005-03-10
> > 3 1 2005-04-12
> > 4 1 2001-01-01
> >
> > with
> > SELECT id, x_col, max(date_col)
> > FROM table_x
> > GROUP BY x_col
> >
> > I would probably get the following result
> >
> > Id x_col date_col
> > 4 1 2005-04-12
> >
> > and what I would like to get is
> >
> > Id x_col date_col
> > 3 1 2005-04-12
> >
> > Is there a way to do that ?
> >
> > Thanks in advance
> > Mauricio
> >
> >
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> > -- 
> > No virus found in this incoming message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
> >
> >
> 
> 
> 
> -- 
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
> 


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

Newbie: Help with Match without using a subQuery

[Graham Anderson]

Hi
I am trying to set up my Match statements to filter the result of the 
main query [which works]

If 'chris' does not exist in the first MATCH statement [AND MATCH 
(media.name, media.product)], then the results get a bit screwed up :(  
If the first match statement finds something, then the query works 
fine

What would be a better way to structure this...without using a subquery 
as I am on MYSQL 3.23

SELECT media.id, media.product AS product, media.name AS name, 
Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id AS 
mediatype, mediaType.name, media.product, media.path
FROM media, artist, mediaType
WHERE media.artist_id = artist.id
AND media.mediaType_id = mediaType.id

AND MATCH (
media.name, media.product
)
AGAINST (
'chris'
)
OR MATCH (
artist.fname, artist.lname
)
AGAINST (
'chris'
)
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Newbie: Help with Match without using a subQuery

[SGreen]

Graham Anderson <[EMAIL PROTECTED]> wrote on 04/13/2005 01:46:35 PM:

> Hi
> I am trying to set up my Match statements to filter the result of the 
> main query [which works]
> 
> If 'chris' does not exist in the first MATCH statement [AND MATCH 
> (media.name, media.product)], then the results get a bit screwed up :( 
> If the first match statement finds something, then the query works 
> fine
> 
> What would be a better way to structure this...without using a subquery 
> as I am on MYSQL 3.23
> 
> 
> SELECT media.id, media.product AS product, media.name AS name, 
> Concat_WS( ' ', artist.fname, artist.lname ) AS artist, mediaType.id AS 
> mediatype, mediaType.name, media.product, media.path
> FROM media, artist, mediaType
> WHERE media.artist_id = artist.id
> AND media.mediaType_id = mediaType.id
> 
> AND MATCH (
> media.name, media.product
> )
> AGAINST (
> 'chris'
> )
> OR MATCH (
> artist.fname, artist.lname
> )
> AGAINST (
> 'chris'
> )
> ORDER BY product, media.name, artist ASC
> LIMIT 0 , 30
> 
> 
it's a parentheses issue. Your query looks like this in the where clause

WHERE artist_ID AND mediaType_ID AND first match OR second match

Which gets evaluated like

WHERE (artist_ID AND mediaType_ID AND first match) OR second match.

Any record that matched your second match condition also satisfied your 
WHERE clause. Because you wanted to match on artist_Id and mediaType_ID 
plus one of the match conditions, you needed to put a set of parentheses 
around BOTH of your match conditions so that your WHERE clause looked 
like:

WHERE artist_ID AND mediaType_ID AND (first match OR second match)

Here is an updated version of your original query (I also changed your 
implicit inner joins to explicit ones (it's a pet peeve)):

SELECT media.id, media.product AS product
, media.name AS name
, Concat_WS( ' ', artist.fname, artist.lname ) AS artist
, mediaType.id AS mediatype
, mediaType.name
, media.product
, media.path
FROM media
INNER JOIN mediaType
ON media.mediaType_id = mediaType.id
INNER JOIN artist
ON media.artist_id = artist.id
WHERE MATCH (media.name, media.product)
AGAINST ('chris')
OR MATCH (artist.fname, artist.lname)
AGAINST ('chris')
ORDER BY product, media.name, artist ASC
LIMIT 0 , 30


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

MySQL Crash Diagnosis

[Jason Johnson]

I am running MySQL 4.1.8 on Windows 2000. Sporadically, the service 
will stop. It does not seem to be in relationship with load on the 
service itself, or the box. At seemingly random intervals, the service 
will go kaput for no readily apparent reason.

My question to you isn't "hey, what's wrong?" but more about how I go 
diagnosing the problem. Are there any tools, utilities, logs I should 
be inspecting that I may not be aware of?

Any help would be much appreciated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: graphs

[Daniel Kasak]

prathima rao wrote:

>hello,
>
>can anyone suggest if there is any software available to create graphs using 
>mysql as database
>
>regards
>
>prathima rao
>  
>
My favourite has always been JpGraph - http://www.aditus.nu/jpgraph/ -
it's a PHP library. You can use it on a web server or on a stand-alone
PHP installation. It's not MySQL-specific, but there are some tutorials
around demonstrating some graphs based on data from MySQL.

I'm sure there are Perl libraries that also do graphing, but I've never
been bothered to research to much - they'd have to be good to surpass
JpGraph.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

MyISAM and Dirty Reads

[Homam S.A.]

Is there a way to use dirty reads (that acquire no
read locks on the table) with MyISAM tables?

I want to avoid having the read requests queuing up
while the table is updated, and I can tolerate a small
margin of inconsistency for the sake of throughput.

So far I found only information about scheduling cues
(e.g. LOW_PRIORITY, DELAYED, etc) and using table
handlers, but even with these cue, you still have the
potential of queuing up reads or starving updates.

For example, in MS SQL Server, you can either give a
per-query lock hint or use a read-uncommitted
transaction isolation level.

MyISAM doesn't have a SET TRANSACTION READ UNCOMMITTED
equivalent to InnoDB, and the SELECT statement doesn't
have lock hints like (NOLOCK).

So is there a way to allow reads to go through when
the table is updated, or allow updates to proceed
without waiting for prending reads to finish up?

Thanks,

Homam


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

Help with a tricky/impossible query...

[Andrew Braithwaite]

Hi,

I need some help with a tricky query.  Before anyone asks, I cannot bring
this functionality back to the application layer (as much as I'd like to).

Here's what I need to do...

create table wibble(
seq int(3) auto_increment primary key,
x int(5),
y int(5)
);

insert into wibble set x=5, y=10;
insert into wibble set x=1, y=3;
insert into wibble set x=17, y=22;

mysql> select * from wibble;
+-+--+--+
| seq | x| y|
+-+--+--+
|   1 |5 |   10 |
|   2 |1 |3 |
|   3 |   17 |   22 |
+-+--+--+
3 rows in set (0.09 sec)

So I want to run a query to explode the x/y ranges by seq.

The required output is:

mysql> select some clever things from wibble where some clever stuff happens
here;
+-+--+
| seq | z|
+-+--+
|   1 |1 |
|   1 |2 |
|   1 |3 |
|   1 |4 |
|   1 |5 |
|   2 |1 |
|   2 |2 |
|   2 |3 |
|   3 |   17 |
|   3 |   18 |
|   3 |   19 |
|   3 |   20 |
|   3 |   21 |
|   3 |   22 |
+-+--+
14 rows in set (0.17 sec)

Can anyone help me to achieve this result?

Thanks,

Andrew

SQL, Query




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

Re: Help with a tricky/impossible query...

[Andrew Braithwaite]

I should mention that I'm constrained to version 4.0.n so no sub queries for
me!

Andrew


On 14/4/05 1:11 am, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I need some help with a tricky query.  Before anyone asks, I cannot bring
> this functionality back to the application layer (as much as I'd like to).
> 
> Here's what I need to do...
> 
> create table wibble(
> seq int(3) auto_increment primary key,
> x int(5),
> y int(5)
> );
> 
> insert into wibble set x=5, y=10;
> insert into wibble set x=1, y=3;
> insert into wibble set x=17, y=22;
> 
> mysql> select * from wibble;
> +-+--+--+
> | seq | x| y|
> +-+--+--+
> |   1 |5 |   10 |
> |   2 |1 |3 |
> |   3 |   17 |   22 |
> +-+--+--+
> 3 rows in set (0.09 sec)
> 
> So I want to run a query to explode the x/y ranges by seq.
> 
> The required output is:
> 
> mysql> select some clever things from wibble where some clever stuff happens
> here;
> +-+--+
> | seq | z|
> +-+--+
> |   1 |1 |
> |   1 |2 |
> |   1 |3 |
> |   1 |4 |
> |   1 |5 |
> |   2 |1 |
> |   2 |2 |
> |   2 |3 |
> |   3 |   17 |
> |   3 |   18 |
> |   3 |   19 |
> |   3 |   20 |
> |   3 |   21 |
> |   3 |   22 |
> +-+--+
> 14 rows in set (0.17 sec)
> 
> Can anyone help me to achieve this result?
> 
> Thanks,
> 
> Andrew
> 
> SQL, Query
> 
> 
> 



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

Re: Changing the Prompt for timing purposes

[Andrew Braithwaite]

When you say shell, do you mean DOS or UNIX?

If it's the latter then you may do this for the logfile:

sh-2.05b# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.24-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> ?

For the complete MySQL Manual online visit:
   http://www.mysql.com/documentation

For info on technical support from MySQL developers visit:
   http://www.mysql.com/support

For info on MySQL books, utilities, consultants, etc. visit:
   http://www.mysql.com/portal

List of all MySQL commands:
   (Commands must appear first on line and end with ';')

help(\h)Display this help.
?   (\?)Synonym for `help'.
clear   (\c)Clear command.
connect (\r)Reconnect to the server. Optional arguments are db and host.
edit(\e)Edit command with $EDITOR.
ego (\G)Send command to mysql server, display result vertically.
exit(\q)Exit mysql. Same as quit.
go  (\g)Send command to mysql server.
nopager (\n)Disable pager, print to stdout.
notee   (\t)Don't write into outfile.
pager   (\P)Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)Print current command.
prompt  (\R)Change your mysql prompt.
quit(\q)Quit mysql.
rehash  (\#)Rebuild completion hash.
source  (\.)Execute a SQL script file. Takes a file name as an argument.
status  (\s)Get status information from the server.
system  (\!)Execute a system shell command.
tee (\T)Set outfile [to_outfile]. Append everything into given
outfile.
use (\u)Use another database. Takes database name as argument.

Connection id: 2  (Can be used with mysqladmin kill)

mysql> \T wibble.txt
Logging to file 'wibble.txt'
mysql> show databases;
+--+
| Database |
+--+
| andrew   |
| mysql|
| phpdb|
| test |
+--+
4 rows in set (0.00 sec)

mysql> Bye
sh-2.05b# ll
total 112
drwxr-xr-x  21 root   wheel   714B 14 Apr 02:07 .
drwxr-xr-x  11 root   wheel   374B 12 Apr 00:44 ..
-rw-r--r--   1 root   wheel18K  5 Mar 04:37 COPYING
-rw-r--r--   1 root   wheel 5K  5 Mar 04:37 EXCEPTIONS-CLIENT
-rw-r--r--   1 root   wheel 8K  5 Mar 04:37 INSTALL-BINARY
-rw-r--r--   1 root   wheel 1K  5 Mar 00:38 README
drwxr-xr-x  50 root   wheel 1K 20 Mar 13:06 bin
-rwxr-xr-x   1 root   wheel   773B  5 Mar 04:50 configure
drwxr-x---  11 mysql  wheel   374B 12 Apr 01:27 data
drwxr-xr-x   7 root   wheel   238B 20 Mar 13:06 docs
drwxr-xr-x  53 root   wheel 1K 20 Mar 13:06 include
drwxr-xr-x  10 root   wheel   340B 20 Mar 13:06 lib
drwxr-xr-x   3 root   wheel   102B 20 Mar 13:06 man
drwxr-xr-x  10 root   wheel   340B 20 Mar 13:06 mysql-test
drwxr-xr-x   3 root   wheel   102B 20 Mar 13:06 scripts
drwxr-xr-x   3 root   wheel   102B 20 Mar 13:06 share
drwxr-xr-x  31 root   wheel 1K 20 Mar 13:06 sql-bench
-rwxr-xr-x   1 root   wheel88B 20 Mar 13:13 start
drwxr-xr-x  13 root   wheel   442B 20 Mar 13:06 support-files
drwxr-xr-x  21 root   wheel   714B 20 Mar 13:06 tests
-rw-r--r--   1 root   wheel   160B 14 Apr 02:08 wibble.txt
sh-2.05b# cat wibble.txt
mysql> show databases;
+--+
| Database |
+--+
| andrew   |
| mysql|
| phpdb|
| test |
+--+
4 rows in set (0.00 sec)

mysql> sh-2.05b# 


Hope this helps,

Andrew 


On 13/4/05 5:29 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

> Hi,
>  The MYSQL command line interface is very basic. Can it be modified like the
> shell command prompts so that I can include date/time for timing benchmarks?
> 
> Secondly, is there an "echo" command in MYSQL command prompt so that I can see
> the command I issued or a log file that I can write to.sorry about the
> basic questions but I'm a newbe.
> 
> George
> 
> __
> Switch to Netscape Internet Service.
> As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register
> 
> Netscape. Just the Net You Need.
> 
> New! Netscape Toolbar for Internet Explorer
> Search from anywhere on the Web and block those annoying pop-ups.
> Download now at http://channels.netscape.com/ns/search/install.jsp



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

Unix time as year in select query -nooby

[[EMAIL PROTECTED]]

MySql vers 4.0.20
A table "noticeboard" has three test entries.
A unix time from date("U") is stored in the field "published" of type, 
bigint20.

I am not able to workout how to select  the year.  Assuming that the 
value from date("U") can be treated as a unixtime value, my latest 
unsuccessful effort is 

$yearslist = mysql_query("SELECT published, 
FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM noticeboard") 
or die("Cannot get list of years." . mysql_error());

$counttotal = mysql_num_rows($yearslist);
echo "Rows found $counttotal";
while ($myrow = mysql_fetch_array($yearslist)) {
if (ISSET($myrow['published'])){$published=$myrow['published'];}
$yearpub=date("Y",$published);
if (ISSET($myrow['year'])){
$year=$myrow['year'];}
else {echo "Year is not set";}
echo "Year $year but value was $published.  Year value should be 
$yearpub";

}
The result is
Rows found 3
Year 1969 but value was 1083923875. Year value should be 2004
Year 1969 but value was 1113300220. Year value should be 2005
Year 1969 but value was 1113351870. Year value should be 2005
Louise

RE: Unix time as year in select query -nooby

[Tom Crimmins]

On Wednesday, April 13, 2005 20:17, [EMAIL PROTECTED] wrote:

> MySql vers 4.0.20
> 
> A table "noticeboard" has three test entries.
> A unix time from date("U") is stored in the field "published" of
> type, bigint20. 
> 
> I am not able to workout how to select  the year.  Assuming that the
> value from date("U") can be treated as a unixtime value, my latest
> unsuccessful effort is   
> 
> $yearslist = mysql_query("SELECT published,
> FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM
> noticeboard") or die("Cannot get list of years." .
> mysql_error());  
> 
> $counttotal = mysql_num_rows($yearslist); echo "Rows found
> $counttotal"; 
> 
> while ($myrow = mysql_fetch_array($yearslist)) {
> 
> if (ISSET($myrow['published'])){$published=$myrow['published'];}
> 
> $yearpub=date("Y",$published);
> 
> if (ISSET($myrow['year'])){
> $year=$myrow['year'];}
> 
> else {echo "Year is not set";}
> 
> echo "Year $year but value was $published.  Year value should be
> $yearpub"; 
> 
> }
> 
> The result is
> 
> Rows found 3
> Year 1969 but value was 1083923875. Year value should be 2004 Year
> 1969 but value was 1113300220. Year value should be 2005 Year 1969
> but value was 1113351870. Year value should be 2005  
> 
> 
> Louise

My guess is that you are passing what is already a unix timestamp to 
the function unix_timestamp, and since that is an invalid datetime it 
returns zero which then causes from_unixtime to return 1969 (when epoch 
time started in your time zone).

Try using FROM_UNIXTIME(published,'%Y')

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

Re: Unix time as year in select query -nooby

[boclair]

Tom Crimmins wrote:
On Wednesday, April 13, 2005 20:17, [EMAIL PROTECTED] wrote:
 

MySql vers 4.0.20
A table "noticeboard" has three test entries.
A unix time from date("U") is stored in the field "published" of
type, bigint20. 

I am not able to workout how to select  the year.  Assuming that the
value from date("U") can be treated as a unixtime value, my latest
unsuccessful effort is   

$yearslist = mysql_query("SELECT published,
FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM
noticeboard") or die("Cannot get list of years." .
mysql_error());  
   

My guess is that you are passing what is already a unix timestamp to 
the function unix_timestamp, and since that is an invalid datetime it 
returns zero which then causes from_unixtime to return 1969 (when epoch 
time started in your time zone).

Try using FROM_UNIXTIME(published,'%Y')
 

I saw my mistake as soon as I posted and it was as you say.  Many thanks
Louise
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: weird characters from mysqldump?

[Steve Lefevre]

Actually, it came from Mysql 4.1.1 (not 4.1.3 as I mentioned earlier). 
When I do select queries, the data displays just fine, but when I dump, 
I get this garbage.

Jeremy Cole wrote:
Hi Steve,
I'm trying to export data from mysql 4.1.3 with mysqldump.
I'm getting weird characters from the system. Here's what I've 
discovered
so far:

' becomes ^À^Ù, e.g. didn't becomes didnâ^À^Ùt
- becomes â^À^Ó, e.g. 1-2 becomes 1â^À^Ó2
è becomes è, e.g. Entrèe becomes Entrèe
What gives?

Looks like an application of the GIGO (Garbage In, Garbage Out) 
principle to me.  The above data wouldn't have happened to come from a 
Microsoft Office application, would it?

Looks like "Smart Quotes" etc., strikes again, to me.
Regards,
Jeremy

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

Re: backup databases one to another

[Karam Chand]

This option involves two steps. First you have to
export complete data and then import it again.

You can do this more efficiently by using a sync tool
like Webyog (www.webyog.com). It will sync any two
MySQL databases with changes only done to modified
rows/columns.

YOu can probably mail their support people 

Regards
Karam

--- Gleb Paharenko <[EMAIL PROTECTED]> wrote:
> Hello.
> 
> 
> Something like:
> 
>   mysqldump -h 192.168.1.1 -uroot -p -A >backup.sql
> 
> See:
>   http://dev.mysql.com/doc/mysql/en/mysqldump.html
> 
> 
> "Abdul Aziz" <[EMAIL PROTECTED]> wrote:
> > 
> > 
> > Dear All,
> > 
> > 
> > I wish to backup all databases to my
> server(192.168.1.1) from my host(192.
> > 168.1.5) with (mysqldump),how can we possible,plz
> tell me command or Script
> > with brief description.
> > 
> > Thanks in advance
> > aaziz
> > 
> > 
> > 
> > 
> 
> 
> -- 
> For technical support contracts, goto
> https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET
> http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__  
> [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
><___/   www.mysql.com
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 



__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

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

Re: backup databases one to another

[Forbiddenweb Archive]

http://dev.mysql.com/doc/mysql/en/replication.html
I would look into the replication features of MySQL.  They can be used 
to create a backup copy of a database.  The slave database server will 
only get the changes from the Master as needed.

Karam Chand wrote:
This option involves two steps. First you have to
export complete data and then import it again.
You can do this more efficiently by using a sync tool
like Webyog (www.webyog.com). It will sync any two
MySQL databases with changes only done to modified
rows/columns.
YOu can probably mail their support people 

Regards
Karam
--- Gleb Paharenko <[EMAIL PROTECTED]> wrote:
 

Hello.
Something like:
 mysqldump -h 192.168.1.1 -uroot -p -A >backup.sql
See:
 http://dev.mysql.com/doc/mysql/en/mysqldump.html
"Abdul Aziz" <[EMAIL PROTECTED]> wrote:
   

Dear All,
I wish to backup all databases to my
 

server(192.168.1.1) from my host(192.
   

168.1.5) with (mysqldump),how can we possible,plz
 

tell me command or Script
   

with brief description.
Thanks in advance
aaziz

 

--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET
http://www.ensita.net/
  __  ___ ___   __
 /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__  
[EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
  <___/   www.mysql.com


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

   

http://lists.mysql.com/[EMAIL PROTECTED]
 

   


		
__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/

 


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

book advice

[David Bailey]

Hi,

I'm a newbie and looking for a book to help me learn mysql. I have come
across a book called "Beginning MySQL" by Robert Sheldon and Geoff Moes.

Can anyone recommend this book? Or, if not, what book can you recommend for
a newbie.

David


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

Problems installing MySQL 4.1 under Fedora Core 3

[C.F. Scheidecker Antunes]

I have a Pentium III with Fedora Core 3 notebook and I need to install 
MySQL 4.1 on it.
There was no mysql installed before so I've downloaded the packages and 
did what I usually do to install
the packages. They install great. But when I try to run MySQL I have 
problems as it does not run.
It tries for a while and then quits.

I am using the 4.1.11-0.i386 version.
I have checked the persmissions under /var/lib/mysql and they were user 
mysql group root. So I've changed to
the MySQL group by using chgrp -R mysql /var/lib/mysql

I've created an /etc/my.cnf file by copying the standard 
/usr/share/doc/packages/MySQL-server/my-medium.cnf

It still does not work.
My /var/log/messages file shows the following:
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:  
denied  { append } for  pid=5693 exe=/usr/sbin/mysqld 
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099 
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:  
denied  { append } for  pid=5693 exe=/usr/sbin/mysqld 
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099 
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.267:0): avc:  
denied  { write } for  pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5 
ino=653096 scontext=root:system_r:mysqld_t 
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.269:0): avc:  
denied  { write } for  pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5 
ino=653096 scontext=root:system_r:mysqld_t 
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:54:10 presario2700 lsb_log_message:  failed

I have no idea what might be wrong.
Can anyone help me out?
Thanks in advance!

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

Re: Re : Problems installing MySQL 4.1 under Fedora Core 3

[C.F. Scheidecker Antunes]

It only shows this:
050413  23:53:35 mysqld started
050413  23:53:35 mysqld ended
Nothing conclusive.
prasanna a wrote:
Hi
please cat the below error file and see 
/var/lib/mysql/presario2700.err

		
__ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search. 
http://info.mail.yahoo.com/mail_250

 

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

Problems installing MySQL 4.1 under Fedora Core 3

[C.F. Scheidecker Antunes]

I have a Pentium III with Fedora Core 3 notebook and I need to install 
MySQL 4.1 on it.
There was no mysql installed before so I've downloaded the packages and 
did what I usually do to install
the packages. They install great. But when I try to run MySQL I have 
problems as it does not run.
It tries for a while and then quits.

I am using the 4.1.11-0.i386 version.
I have checked the persmissions under /var/lib/mysql and they were user 
mysql group root. So I've changed to
the MySQL group by using chgrp -R mysql /var/lib/mysql

I've created an /etc/my.cnf file by copying the standard 
/usr/share/doc/packages/MySQL-server/my-medium.cnf

It still does not work.
My /var/log/messages file shows the following:
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:  
denied  { append } for  pid=5693 exe=/usr/sbin/mysqld 
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099 
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:  
denied  { append } for  pid=5693 exe=/usr/sbin/mysqld 
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099 
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.267:0): avc:  
denied  { write } for  pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5 
ino=653096 scontext=root:system_r:mysqld_t 
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.269:0): avc:  
denied  { write } for  pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5 
ino=653096 scontext=root:system_r:mysqld_t 
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:54:10 presario2700 lsb_log_message:  failed

I have no idea what might be wrong.
Can anyone help me out?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Problems installing MySQL 4.1 under Fedora Core 3

[John Schmidt]

I am pretty sure hat is due to the SELinux policy being set to restrictive. For 
the brute force fix try editing /etc/selinux/config and set the variable 
SELINUX=Permissive. There is a more subtle approach that maintains beeter 
security explained under the SELinux topic in the Fedora doc, sorry I don't 
have the link handy. Basically you can realx the policy via the "targetted" 
settings. 
Good luck
//jjs
  


-Original Message-
From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED]
Sent: Wed 4/13/2005 10:56 PM
To: mysql@lists.mysql.com
Subject: Problems installing MySQL 4.1 under Fedora Core 3
 
I have a Pentium III with Fedora Core 3 notebook and I need to install 
MySQL 4.1 on it.
There was no mysql installed before so I've downloaded the packages and 
did what I usually do to install
the packages. They install great. But when I try to run MySQL I have 
problems as it does not run.
It tries for a while and then quits.

I am using the 4.1.11-0.i386 version.

I have checked the persmissions under /var/lib/mysql and they were user 
mysql group root. So I've changed to
the MySQL group by using chgrp -R mysql /var/lib/mysql

I've created an /etc/my.cnf file by copying the standard 
/usr/share/doc/packages/MySQL-server/my-medium.cnf

It still does not work.

My /var/log/messages file shows the following:

Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:  
denied  { append } for  pid=5693 exe=/usr/sbin/mysqld 
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099 
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.252:0): avc:  
denied  { append } for  pid=5693 exe=/usr/sbin/mysqld 
path=/var/lib/mysql/presario2700.err dev=hda5 ino=653099 
scontext=root:system_r:mysqld_t tcontext=root:object_r:var_lib_t tclass=file
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.267:0): avc:  
denied  { write } for  pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5 
ino=653096 scontext=root:system_r:mysqld_t 
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:53:35 presario2700 kernel: audit(1113458015.269:0): avc:  
denied  { write } for  pid=5693 exe=/usr/sbin/mysqld name=mysql dev=hda5 
ino=653096 scontext=root:system_r:mysqld_t 
tcontext=root:object_r:var_lib_t tclass=dir
Apr 13 23:54:10 presario2700 lsb_log_message:  failed

I have no idea what might be wrong.

Can anyone help me out?

Thanks in advance!



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