Default date format
Hello! Is there a way how to change the date and time format accepted and returned by MySQL from '-mm-dd' to something different? I've seen several posts here telling it's impossible but they are quite old. Is it still true in recent MySQL versions? I can see date_format, datetime_format and time_format in SHOW VARIABLES, it's even documented somewhere (e.g. http://mysqld.active-venture.com/SET_OPTION.html ) but it seems it doesn't do anything in MySQL 4.1.7. -- Jakub Vrana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Innodb Status: Output truncated
Hi, this is on 4.0.22-standard. The output of show innodb status is truncated so that the sections FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY and ROW OPERATIONS are missing. We have more than 600 concurrent connections so that the TRANSCATIONS section becomes long. What causes that behaviour and can you work around the problem? Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default date format
Is there a way how to change the date and time format accepted and returned by MySQL from '-mm-dd' to something different? I've seen several posts here telling it's impossible but they are quite old. Is it still true in recent MySQL versions? I can see date_format, datetime_format and time_format in SHOW VARIABLES, it's even documented somewhere (e.g. http://mysqld.active-venture.com/SET_OPTION.html ) but it seems it doesn't do anything in MySQL 4.1.7. `mysqld.exe --help --verbose | grep _format` says: --date_format=name The DATE format (For future). So I guess the variable is prepared but not used for anything yet. -- Jakub Vrana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
minimal privileges for a replication slave
Hello, I want to set up multiple replication slaves to the same master but with different access to databases, one database for one client... just in case if sb would brake into one of our slaves then he should not be able to download other databases from a master Is it possible? I found that REPLICATION SLAVE can be set only globally, to all databases... I would be very thankful for a help Regards, Irek Slonina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions About Creating Tables
I see you found some DB code for a different platform. Most of your
questions are based on the fact that these are NOT MySQL commands (or
scripts) but SQL statements phrased for a different system. If you know
what system it is designed for (Oracle, DB2. ??? ) you should be able to
read their docs, understand their commands and translate this script into
something MySQL-compatible.
Here are my best guesses (not being 100% certain precisely which DB system
it's written for)
David Blomstrom [EMAIL PROTECTED] wrote on 12/25/2004 06:32:12
PM:
I have some questions about some instructions for
recreating tables I'm trying to follow:
1. This first table is the simplest. The only thing I
don't understand is serial. I created char and date
columns easily enough, but I don't see serial listed
in phpMyAdmin.
Serial is *not* a MySQL data type, that's why it isn't listed. I would
suspect that you could use int auto_increment instead.
snip
2. In the table below, why do they designate two
numerals for the second field, when it's varchar? How
do you enter 80,5 without getting an error?
Again, I refer you to the documentation for the DB system this command was
written for. This is not a MySQL convention. I suspect that you could use
VARCHAR(80) as a MySQL equivalent.
snip
3. I think I've figured out that the unique key above
refers to three combined fields - tsn, vernacular_name
and language. So how do I create a unique triple-field
key?
UNIQUE (field1, field2, field3)
read:
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
4. Constraint refers to a foreign key, right? If so,
then I ought to be able to leave this blank for now
and add it later, right?
No, constraint does not refer to only foreign keys. A constraint is
anything that limits what data is allowed into your table. the phrase NOT
NULL is one kind of constraint, so are primary, unique, and foreign keys,
among others.
read:
http://dev.mysql.com/doc/mysql/en/Constraints.html
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
5. In the example below, what do row size and index
size mean? How do I designate them?
{ TABLE itis.change_operations row size = 12 number
of columns = 3 index size = 42
The most simple answer is: You don't need to designate those values for
MySQL. Each MySQL storage engine will compute those values for you.
Finally, am I correct that the text below is designed
to automatically create all my tables? I haven't been
able to make it work yet, but that's my
interpretation.
The text below (snipped) may work on another DB system but not on MySQL.
You will need to translate it first in order to make it MySQL-compatible.
Thanks.
NP
enormous snip
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Can't install Mysql on Windows XP
Hello: I am trying to install mysql on my laptop which is running Windows XP professional. But after installation when trying to connect to the MySQL server I am getting the following error message: Error 2003: Can't connect to MySQL server on 'localhost' (10061). Tried all the options with my knowledge. No luck yet. Can anybody help me out. MySQL installation file/version - mysql4.0.20d-win Regards Sheni R Meledath Assistant Manager - Web Development * Cyber Gear LLC P.O. Box 53735 Dubai, United Arab Emirates Tel: (971 4) 331-2627, Fax: (971 4) 331-8812 Email: [EMAIL PROTECTED], http://www.cyber-gear.com VISIT OUR ASSOCIATE SITES: http://www.Click2Advertising.com http://www.DubaiCityGuide.com http://www.MiddleEastDirectory.com http://www.MiddleEastEvents.com http://www.MiddleEastPostBox.com http://www.PressReleaseNetwork.com * This message has been checked for all known viruses by McAfee. No liability is accepted by virtue of checking this message and you are recommended to have in place your own anti-virus detection procedures.
Re: Can't install Mysql on Windows XP
Did you install MySQL as a service and have you started the service? Sheni R. Meledath wrote: Hello: I am trying to install mysql on my laptop which is running Windows XP professional. But after installation when trying to connect to the MySQL server I am getting the following error message: Error 2003: Can't connect to MySQL server on 'localhost' (10061). Tried all the options with my knowledge. No luck yet. Can anybody help me out. MySQL installation file/version - mysql4.0.20d-win Regards Sheni R Meledath Assistant Manager - Web Development * Cyber Gear LLC P.O. Box 53735 Dubai, United Arab Emirates Tel: (971 4) 331-2627, Fax: (971 4) 331-8812 Email: [EMAIL PROTECTED], http://www.cyber-gear.com VISIT OUR ASSOCIATE SITES: http://www.Click2Advertising.com http://www.DubaiCityGuide.com http://www.MiddleEastDirectory.com http://www.MiddleEastEvents.com http://www.MiddleEastPostBox.com http://www.PressReleaseNetwork.com * This message has been checked for all known viruses by McAfee. No liability is accepted by virtue of checking this message and you are recommended to have in place your own anti-virus detection procedures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql memory,users monitoring
First of all, thanks for the mailing list for giving details about myisamchk. At present we are working in mysql 3.23.32 ,windows xp os. Are there any system tables that store mysql related info.(like memory related issues etc). Are there any other ways to find memory used and free memory available by data tables,log files,temporary tables.We want the information so that we can use it in programs.Information available through commands could not be used in programming(For ex: myisamchk works with single table.we need to monitor entire datadirectory so we need write programs.) Thanking you, yours sincerely, sirisha. Yahoo! India Matrimony: Find your life partneronline.
Re: Questions About Creating Tables
Thanks so much for the tips! It's all falling into
place now.
I'm still using the animal database tables you
suggested for me some time ago, but I also wanted to
tap into this ITIS database. Combining or linking the
two should be interesting. :)
--- [EMAIL PROTECTED] wrote:
I see you found some DB code for a different
platform. Most of your
questions are based on the fact that these are NOT
MySQL commands (or
scripts) but SQL statements phrased for a different
system. If you know
what system it is designed for (Oracle, DB2. ??? )
you should be able to
read their docs, understand their commands and
translate this script into
something MySQL-compatible.
Here are my best guesses (not being 100% certain
precisely which DB system
it's written for)
David Blomstrom [EMAIL PROTECTED] wrote on
12/25/2004 06:32:12
PM:
I have some questions about some instructions for
recreating tables I'm trying to follow:
1. This first table is the simplest. The only
thing I
don't understand is serial. I created char and
date
columns easily enough, but I don't see serial
listed
in phpMyAdmin.
Serial is *not* a MySQL data type, that's why it
isn't listed. I would
suspect that you could use int auto_increment
instead.
snip
2. In the table below, why do they designate two
numerals for the second field, when it's varchar?
How
do you enter 80,5 without getting an error?
Again, I refer you to the documentation for the DB
system this command was
written for. This is not a MySQL convention. I
suspect that you could use
VARCHAR(80) as a MySQL equivalent.
snip
3. I think I've figured out that the unique key
above
refers to three combined fields - tsn,
vernacular_name
and language. So how do I create a unique
triple-field
key?
UNIQUE (field1, field2, field3)
read:
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
4. Constraint refers to a foreign key, right? If
so,
then I ought to be able to leave this blank for
now
and add it later, right?
No, constraint does not refer to only foreign
keys. A constraint is
anything that limits what data is allowed into your
table. the phrase NOT
NULL is one kind of constraint, so are primary,
unique, and foreign keys,
among others.
read:
http://dev.mysql.com/doc/mysql/en/Constraints.html
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
5. In the example below, what do row size and
index
size mean? How do I designate them?
{ TABLE itis.change_operations row size = 12
number
of columns = 3 index size = 42
The most simple answer is: You don't need to
designate those values for
MySQL. Each MySQL storage engine will compute those
values for you.
Finally, am I correct that the text below is
designed
to automatically create all my tables? I haven't
been
able to make it work yet, but that's my
interpretation.
The text below (snipped) may work on another DB
system but not on MySQL.
You will need to translate it first in order to make
it MySQL-compatible.
Thanks.
NP
enormous snip
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
__
Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
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]
Re: Show Innodb Status: Output truncated
Frank, CREATE TABLE innodb_monitor(a INT) TYPE=InnoDB; causes the output to be written to the .err file untruncated. DROP the table to stop the printouts. There is a limit in the mysql client that restricts the output length to 64 kB. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Dr. Frank Ullrich [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 27, 2004 1:21 PM Subject: Show Innodb Status: Output truncated Hi, this is on 4.0.22-standard. The output of show innodb status is truncated so that the sections FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY and ROW OPERATIONS are missing. We have more than 600 concurrent connections so that the TRANSCATIONS section becomes long. What causes that behaviour and can you work around the problem? Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- 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]
Where can I download HPUX11.0 PA-RISC 2.0 (LP64) platform binaries for 4.1.8 mysql?
Hello- I am looking for ELF-64 relocatable object file - PA-RISC 2.0 (LP64) platform binaries for 4.1.8 mysql. Please lemme know where I can download this from? Thanx, Vivek PS: I downloaded this from the mysql.com mysql-standard-4.1.8-hp-hpux11.00-hppa2.0w ptdh172:vkrajanfile libmysqlclient.a libmysqlclient.a: archive file -PA-RISC1.1 relocatable library ptdh172:vkrajanfile libmysqlclient_r.a libmysqlclient_r.a: archive file -PA-RISC1.1 relocatable library ptdh172:vkrajan I don't see PA-RISC 2.0 binaries.
Newbie: Using SELECT to Get Table Status?
The mysqlSHOW TABLE STATUS command returns such things as the number or rows and time of the last update. Is it possible to get the same information using a SELECT statement? FYI, I am writing a script to monitor replication status by comparing this information between the master and slave servers. -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data
Hi, I have done an incredibly stupid thing. I have a server that suffered a bit of a failure. It wasn't a hard failure, but it was enough of one to have to rebuild the server from scratch. I was and am running mysql 4.0.22 on Gentoo with a 2.6 kernel. It rocks. However, The dumb thing that I did was fail to perform a mysqldump -a on the machine before I finished killing it. I rebuilt mysql from scratch. I then copied over the my.cnf files and I copied over the old /var/lib/mysql directory in tact. mysql wouldn't start at that point. I then deleted all the files in /var/lib/mysql and ran mysql_install_db. mysql starts automatically. If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create set column with possible values 1 to 64
Hi, Is there any elegant way of saying the following statement in a shorter format: create table dummy ( col1 set(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,..64) ); That is, I would use all the 64 possible values that are offered by the SET column structure, and I am lazy to write out all of them. Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data
Curtis Maurand wrote: If I create one of the databases and then put the files for that database from the old installation in place of the newly created ones, the database is recognized, but i get errors saying that the columns in the tables are not recognized. I'd really like to get this data back is there a way? Did you try simply repairing the table? http://dev.mysql.com/doc/mysql/en/REPAIR_TABLE.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow as Christmas join
Hello! I have a couple of tables I am doing a join on. Both have 6 fields. Most fields have fewer than 16 characters in them, but at most 75. Neither table is huge: one with 14004 rows and the other with 23677. I created a temp table to insert the data into. When I perform the join, it takes about 17 minutes to complete. The result is only 23674 rows. Question is, why is this query taking so long? I have searched the manual and only found an example where they talk about millions of rows being slow and mine has far fewer than that. It is a one to many relationship between the two tables, but should that really slow things down that much? Is there a way to speed things up a little...or a lot? Just for reference, here is the syntax I am using: insert into tmp1 select table2.field1, table1.field2, table1.field3, table2.field2, table2.field3, table2.field4, table2.field5, table2.field6, table1.field4, table1.field5, table1.field6 from table2,table1 where table2.field1=table1.field1; (I have tried the same query with the tables reversed, i.e, table1.field1=table2.field1 but it still takes a long time.) MySQL Version: 4.0.15 Mac OS X 10.3.7 Explain: ++--+---+--+-+--+--- +---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--- +---+ | table1 | ALL | NULL | NULL |NULL | NULL | 14004 | | | table2 | ALL | NULL | NULL |NULL | NULL | 23677 | | ++--+---+--+-+--+--- +---+ If there is anything else that will help you solve this problem, let me know and I will be happy to provide it! Thanks In Advance! --kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
