find duplicates
The db in question is a shopping cart and I was looking for products I added that might have been duplicated in another category. My first attempt that worked. select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml order by cnt; The thing wrong with this of course is that I wanted only the prodid's where cnt1. Eventually: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1; I.e. replacing order by with a having clause. After trying many variations; are 'order by' and 'having' mutually exclusive? If so - how would you order the result table? As always thank you for any thoughts pointers. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize table vs. dump and insert
Hi. I wonder if optimize table results are equal to dump such table and insert it back ? Regards. -- Grzegorz Paszka sql,mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Socket Error
Hello,
Thankyou for your quick response.
When I start getting this error, the mysql crashes, any application which I
try to start which uses mysql backend, says mysql.sock error could not
connect .
-
DBI connect('database=search;host=localhost','web',...) failed: Can't
connect to local MySQL server through socket '/tmp/mysql.sock' (61) at (eval
2) line 1
--
When i try to restart the mysql server nothing happens and the only option
that remains is to reboot the entire system. Everything works fine then for
a few days and again the same error.
Birju Shah
-
- Original Message -
From: Daniel Kasak [EMAIL PROTECTED]
To: Birju Shah [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, January 12, 2004 2:13 PM
Subject: Re: Mysql Socket Error
Birju Shah wrote:
Hello Friends,
We are using free bsd, mysql 4.0.15. We are using this system since more
than a year now. We have a autoresponder script which has mysql database.
Since a month or so we are noticing that mysql gives the mysql.sock error
and the only option which remains is to reboot the entire system.
This error didnt happen before, the same scripts are running, we havent
changed anything in the scripts. The mysql gives the socket error sometimes
in 3 days sometimes in 7-8 days.
What can be causing this error and what is the solution.
Sorry to bother you.
Birju Shah
Another mysql.sock error!
Firstly, when you start getting the error, what's happening to MySQL? Do
you access it in any other way, or only though the script which works
via the socket? Can you connect to MySQL with the command-line client,
or with MySQLCC?
Maybe MySQL is crashing and this is the reason which you can't connect
through the socket. What do the mysql logs say? Maybe examine / post them.
--
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]
Re: hierarchical records, I need some help!! ;(
Hi again, Second: specify what you mean with all relations. Can you share some sample data and sample output (that is: what do you expect the query to return). Ok my table noms is like | id | int(11) | | PRI | [NULL] | auto_increment | reference| varchar(255) | | component| varchar(255) | The relation between refefence and component are like parent - child Here is an example Table contains : IDREFERENCE COMPONENT 1 A004 B001 2 B001 C003 3 B001 D003 (I have more records but only this are what i want to take as an example) So the relations are like a hierarchical tree: A004 BOO1 C003 D003 And i want one sentence that takes all the relations. The SQL statement result should be like: REFERENCE COMPONENT AOO4 B001 B001 COO3 B001 COO3 Coudl somebody give me a tip to do it?. Thanks. Victor. Victor, First: don't ask the same thing twice. I have one table called noms like this: | id | int(11) | | PRI | [NULL] | auto_increment | | reference| varchar(255) | | component| varchar(255) | the relation between reference and component is hierarchical like reference - component reference - component I want to have all the items into a recorset with only one query like Select id, reference, component from NOMS where reference = '4' but i want not only the primary relations, i want all relations. Second: specify what you mean with all relations. Can you share some sample data and sample output (that is: what do you expect the query to return). With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Linux Rulez!! Registered Linux User: 230557 La vida es modular. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication errors
Hi I've got a master and a slave both at 3.23.49. I've been running replication for the last 3 months and all of a sudden I've got queries failing on the slave with duplicate entries (error 1062). From the documentation all I can figure out is that there's a possibility I might be using different character sets on databases. How do I check what the default character set is? Failing that... any other areas that might cause a similar problem. I've already tried set slave skip Rgds Mike E-mail: [EMAIL PROTECTED] -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find duplicates
On Tue, Jan 13, 2004 at 02:03:26AM -0500, [EMAIL PROTECTED] wrote: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1; I.e. replacing order by with a having clause. After trying many variations; are 'order by' and 'having' mutually exclusive? If so - how would you order the result table? They shouldn't be, you just need to get the order right: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1 order by cnt; Is perfectly valid syntax. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_init function issues :Connecting MySQL to COBOL
Hi MySQLians!
I have clearly mentioned my objective and the portion of work i have finished in my
last mail.
unfortunatly I don't find any reply for the past weekdays. Tii I have a trust, some
one would take
time to read this and sent me the suggestion to this list.
My current issue is from mysql_init function of MySQL in libmysql.lib library file.
i.e., MYSQL* mysql_init(MYSQL *mysql), here actually this function require
a parameter
of MYSQL type NULL pointer and return back the MYSQL handle.
From COBOL, If I pass a NULL pointer to this function, it wouldn't agree with that
and produce
runtime ERROR as RDB010E: Interrupt trapped: Illegal storage access.
The error says that, the function trying to access some invalid location other than
MySQL - right?
To resolve this I try to frame the MYSQL datatype NULL Pointer in COBOL, for that when
I look the
Datatype used in mysql.h header file, I found that it has number of decalarations
using standard
datatypes and some user defined data types. see below;
typedef struct st_mysql
{
NET net;
gptrconnector_fd;
char*host,
*user,
*passwd,
*unix_socket,
*server_version,
*host_info,
*info,
*db;
struct charset_info_st *charset;
MYSQL_FIELD *fields;
MEM_ROOTfield_alloc;
my_ulonglong affected_rows;
my_ulonglong insert_id;
my_ulonglong extra_info;
unsigned long thread_id;
unsigned long packet_length;
unsigned intport,
client_flag,
server_capabilities;
unsigned int protocol_version;
unsigned int field_count;
unsigned int server_status;
unsigned int server_language;
struct st_mysql_options options;
enum mysql_status status;
my_bool free_me;
my_bool reconnect;
char scramble_buff[9];
my_bool rpl_pivot;
struct st_mysql * master,
*next_slave;
struct st_mysql* last_used_slave;
struct st_mysql* last_used_con;
} MYSQL;
- I want to confirm that Is this much lengthy decalration of pointer needed? (OR)
- We can just find the length and given it to COBOL pointer declaration?
- Incase Length does the needs how to fix the length for each dataitem in C?
If it succeed we all, as MySQL user can proud of put a mile stone for the support to
COBOL.
I would be wonder if any one read this clearly and suggest me.
thanks
Arun.
--- Arunachalam [EMAIL PROTECTED] wrote: Hi!
I am trying to connect MySQL Database to COBOL. I have made it possible to some
stages by the
way
as follows; I have developed this under;
Client: Microsoft Windows 2000 Service pack 4.
CA-Realia COBOL Compiler Version 6.0.45
Microsoft (R) Incremental Linker Version 6.00.8168
MySQL server 4.0.17-max in SUSE Linux,
MySQL provides set of C API to interact with MySQL database, to link the functions
to the
compiler
it provides a library file libmysql.lib. COBOL has the External subroutine featurs
to CALL the
program written in other languages.
When I try to link existing libmysql.lib file directly to the COBOL compiler it
wont recognize
it. So I have prepared a COBOL compiler specific library file from the corresponding
libmysql.dll
file, in such a way. (The libmysql.dll I have used to create COBOL compiler specific
libmysql.lib
is gathered from Mysql 4.0.17-max-debug for windows, in my localhost)
The COBOL compiler specific libmysql.lib is linked successfully to the COBOL
Compiler and I have
written some subroutine call to the function related to database connection from
COBOL. Such
that
mysql_init, mysql_real_connect, mysql_real_query, mysql_error and mysql_close.
Here mysql_init requires a Null pointer argument and return the MySQL handle back.
mysql_real_connect use that MySQL handle with additional arguments host name,
userid, password,
DB
name, port number, socket and flag to establish connection to the specified database.
In my case mysql_init return a handle to mysql_real_connect, but instead of
connection
establishment to MySQL it shows runtime error in COBOL as
RDB0104E: Interrupt trapped: Illegal storage access.
And store an error message in variables memory space as;
Unknown MySQL Server host
So I want to have suggestion regarding the following;
1.Unknwon MySQL server host means it could not able to recognize the
particular Host what I
have mentioned or anything else?
2.For the socket parameter in mysql_real_connect I have passed NULL value. Is it
correct?
3.I have prepared the COBOL compiler specific libmysql.lib from libmysql.dll
which I have taken
from Windows as per the suggestions read from MySQL documentation. is this correct?
4.From my client machine i.e., windows platform I can able to connect to MySQL
by writing
simple
C program using that C APIs. Then why it does not work with COBOL?
Thanks , any suggestion or help would be greatly
Implement one statement w/o subqueries.
Hello. Table 1: Items id, Name Table 2: Properties id, Item, Name, Value I want select Items _and_ all thier props only if Item have specified property. Example: Table Item: 1, Mouse 2, Monitor 3, Keyboard Table Properties: 1, 1, Color, Red 2, 2, Color, Gray 3, 1, Interface, Wireless 4, 2, MaxResolution, [EMAIL PROTECTED] 5, 3, Color, Gray 6, 3, NumberOfKeys, 101 I want select all Items and all thier properties if item's Color is Gray: Item, Name, Prop, Value 2, Monitor, Color, Gray 2, Monitor, MaxResolution, [EMAIL PROTECTED] 3, Keyboard, Color, Gray 3, Keyboard, NumberOfKeys, 101 I hope you've understand what I mean. Thanks. Ruslan. PS: MySQL 4.0.x -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext creation on 4.1: ERROR 1034
Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table I'm using version 4.1.1-alpha of the MySQL database, a source-compiled version with the --with-raid option. I'm trying to build a newpaper article search engine. I've built an 'articles' table with the following DDL: CREATE TABLE articles ( filename varchar(40) default NULL, source varchar(30) default NULL, pubdate varchar(30) default NULL, text text ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2366 RAID_TYPE=striped RAID_CHUNKS=16 RAID_CHUNKSIZE=2048; I've inserted 7806867 articles in dutch and french into it, which gives me a table of about 16Gb, leaving 75+Gb of free space on my Compaq Proliant DL380G2, 1.2Gb RAM. The kernel is compiled with HIGHMEM support, and the MySQL database is using a cnf based on my-huge.cnf. Somebody knows where to start looking? Thanks in advance, Kurt.
MySQL benchmarks
Hi All, Is there any AS3AP benchmark suite readily available for MySQL? Thanks in advance, Ram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving Bookmark Table Data
I'd like to move some bookmarks in one database to another. The format
of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly trucated):
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| bookmark_id | int(11) unsigned | | PRI | NULL| auto_incr |
| group_id | int(10) unsigned | | MUL | 0 | |
| bookmark_title | varchar(255) | | | | |
| bookmark_url | varchar(255) | | | | |
| bookmark_descrip | varchar(255) | YES | | NULL| |
| bookmark_creatio | datetime | | | - | |
| bookmark_private | char(1) | | | 0 | |
| bookmark_last_hi | datetime | YES | | NULL| |
| user_id | int(10) unsigned | | MUL | 0 | |
| bookmark_deleted | char(1) | | | 0 | |
+--+--+--+-+-+---+
I want to move this into the online-bookmarks 'bookmark' table which
has the format:
+-+---+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+---+--+-+-++
| user| char(20) | | | ||
| title | char(70) | | MUL | ||
| url | char(200) | | | ||
| description | char(200) | YES | | NULL||
| private | enum('0','1') | | | 0 ||
| date| timestamp(14) | YES | | NULL||
| childof | int(11) | | | 0 ||
| id | int(11) | | PRI | NULL| auto_increment |
| deleted | enum('0','1') | | | 0 ||
+-+---+--+-+-++
The only field mappings I care about are:
bookmark_title - title
bookmark_url - url
bookmark_description - description
Now I know I can generate insert statements and then run that script on
the target but is there a better way? The databases do not have direct
access as each is running on localhost only.
Thanks,
Mike
--
A program should be written to model the concepts of the task it
performs rather than the physical world or a process because this
maximizes the potential for it to be applied to tasks that are
conceptually similar and, more important, to tasks that have not
yet been conceived.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table/Column Name Completion
Hi Ya I believe the Dos Mysql Client has limited table name completion, but do any of the GUI Clients have this feature?? zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication syncronization lag.
Hello, Many thanks for your reply! I am currently syncronizing two MySQL servers (version 3.23.49) on a very high traffic website. There are, at peak times upwards of 600 updates a second (and many many more selects) During these times the slave database will fall out of sync, sometimes by several thousand seconds (im aware that this calculation is the 'time now - timestamp of last update from the master) What is the status of the slave thread in show processlist? If it is 'Locked' then the slave thread may be starving because of too many (long) selects on the slave. The status of the slave thread is 'Locked' for quite some time. Just now I have checked and a single query (normally very quick) was in Locked status for 11 seconds. If your my.cnf contains low-priority-updates and your slaves are serving selects continuously, then the updates won't come through. This setting is mentioned in the manual. If you remove low-priority-updates, the updates will be processed sooner but you'll get in trouble if you have long running selects. These will delay all following selects on the slave when an update is waiting for the long query to end. Hmm, ok. I dont have low-priority-updates in the config file (i did see that in the manual) Unfortunately I am in a position where we can not have lag on the databases (people pay for live data, if their select gets run on the slave and the results are delayed due to slave lag, they tend to be pissed off) so is there anything I else I can do to reduce this while still keeping the redundnacy of master/slave? (we cant run the website just on one DB, it falls over with 'can not create thread errors' when we run out of memory :() I am splitting up the database at the moment, and moving some of the biggest tables on to a new server, and this week I will be upgrading both these servers to mysql 4.0.16 which i understand will potentially give us quite a performance increase. Do you have any other suggestions that you may be able to offer? Ive copied the my.cnf files from the master and the slave at the bottom of this email if that may offer any clues to what else we can do. many thanks in advance for any assistance you can give. -- Jonathan Tullett my.cnf on the MASTER: # Here follows entries for some specific programs [safe_mysqld] err-log = /var/log/mysql/mysql.err # The MySQL server [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 skip-locking basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= max_connections=400 set-variable= max_user_connections=360 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log= /var/log/mysql/mysql.log server-id = 1 master-host = 10.0.2.1 master-user = replicate master-password = replicate master-connect-retry = 60 replicate-ignore-db = mysql skip-slave-start log-bin = /mysql-log/db1-new-bin log-slow= /mysql-log/db1-new-slow.log my.cnf on the SLAVE: # Here follows entries for some specific programs [safe_mysqld] err-log = /var/log/mysql/mysql.err # The MySQL server [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 skip-locking basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= max_connections=400 set-variable= max_user_connections=360 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log= /var/log/mysql/mysql.log server-id = 2 master-host = 10.0.2.2 master-user = replicate master-password = replicate master-connect-retry = 60 replicate-ignore-db = mysql skip-slave-start log-bin = /mysql-log/db2-bin log-slow= /mysql-log/db2-slow.log -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database take too much hard drive space
Hi, I've been inserting a csv file of about 150Mb into a mysql database. The problem is that there is no enough free space on the hard disk, and the process have been collapsed. Now I've been trying to access to the database to drop the table and change the directory where mysql stores the information, but I can't access it. I suppose that the procedure is to stop mysql server and then drop the table, but I don't know how to drop a table when mysqld does not run. And, appart from that, is this the best procedure? I'm running mysql 3.23 in a Linux Mandrake 9.0 Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with a dump
I have tried two ways of dumping data but it doesn't seem to be working.
One using the admin window with mysqldump dbname dumptest.sql but I
don't know if it has done anything because all it did was return to a
new blank line. I can't find anywhere a file named dumptest.sql
The other way was to use MySQL-front by way of export, this did produce
a file but when I come to import it again to ensure that the file is OK
I got this error:
1064 - You have an error... ...near'()' at line 1.
I have looked on the MySQL site and it is a ER_PARSE_ERROR. I don't
know what this means. When I exported and imported I just used the
default settings that MySQL-front has to offer, I didn't select any
other options that were available.
Line one of the sql document when opened in notepad is '# MySQL-Front
Dump 2.5'. The first line of what I call sql is 'CREATE TABLE
all_articles (' and the following is the first line of data to import:
INSERT INTO all_articles VALUES(1, 3, 2003-03-14 14:21:00,
2003-03-14 14:21:00, N, 2003-03-14 00:00:00, UNITED STATES: ALPA
Applauds Senate Action, NULL, The following statement was issued
bAir Line Pilots Association (ALPA), International/b, \'We applaud
the action taken. (Tel: +0 000 000 000; web site:
http://www.site.org/), 1, 1);
I have removed what is basically a lot of text in one of the fields and
left any bits that might be causing my problem.
Any help/suggestions is greatly appreciated.
Mat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext creation on 4.1: ERROR 1034
Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table I'm using version 4.1.1-alpha of the MySQL database, a source-compiled version with the --with-raid option. I'm trying to build a newpaper article search engine. I've built an 'articles' table with the following DDL: CREATE TABLE articles ( filename varchar(40) default NULL, source varchar(30) default NULL, pubdate varchar(30) default NULL, text text ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2366 RAID_TYPE=striped RAID_CHUNKS=16 RAID_CHUNKSIZE=2048; I've inserted 7806867 articles in dutch and french into it, which gives me a table of about 16Gb, leaving 75+Gb of free space on my Compaq Proliant DL380G2, 1.2Gb RAM. The kernel is compiled with HIGHMEM support, and the MySQL database is using a cnf based on my-huge.cnf. Somebody knows where to start looking? What is the exact command that generates en error ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification Question
On Sat, 2004-01-10 at 12:04, Bernard Clement wrote: Hello Aman, For instructions on taking the exam in India goto the URL: http://www.vue.com/mysql/ and click on test center of To register for exams in India, please contact the test center directly. This will bring you a window containing all the Pearson VUE Test Center in India. You should of course also check up on all the certification material available on the MySQL web site: http://www.mysql.com/certification Most importantly, make sure to read through the Certification Candidate Guide. Best regards and good luck! / Carsten -- Carsten H. Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext creation on 4.1: ERROR 1034
Sergei Golubchik wrote: Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table I'm using version 4.1.1-alpha of the MySQL database, a source-compiled version with the --with-raid option. I'm trying to build a newpaper article search engine. I've built an 'articles' table with the following DDL: CREATE TABLE articles ( filename varchar(40) default NULL, source varchar(30) default NULL, pubdate varchar(30) default NULL, text text ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2366 RAID_TYPE=striped RAID_CHUNKS=16 RAID_CHUNKSIZE=2048; I've inserted 7806867 articles in dutch and french into it, which gives me a table of about 16Gb, leaving 75+Gb of free space on my Compaq Proliant DL380G2, 1.2Gb RAM. The kernel is compiled with HIGHMEM support, and the MySQL database is using a cnf based on my-huge.cnf. Somebody knows where to start looking? What is the exact command that generates en error ? Regards, Sergei Hi Sergei, alter table articles add fulltext( text ); After several hours of processing, the error below is generated. Regards, Kurt.
spatial types
hi - ive been playing with the OGC support for WKT but cant find data type size constraints for GEOMETRY types. does anybody know what they are? Ive a polygon with 140,000 bytes as WKT but inserts all produce a NULL geometry... any ideas? regards Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Rich Cook
Re: Problem creating sp
adburne [EMAIL PROTECTED] wrote:
I'll be trying to work with sp on win32/5.0.0-alpha-max-debug; I take the
mysql's page example to test but makes an error:
mysql delimeter |
You made a typo. You should write 'delimiter'.
- create function hello (s char(20)) returns char(50)
- return concat('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual
that
corresponds to your MySQL server version for the right syntax to use near
deli
meter |
create function hello (s char(20)) returns char(50)
return concat('H' at line 1
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [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]
email attachments, stripped, coverted to text and inserted into MySQL
I've been asking around for awhile without a solution to this problem and thought I'd post it here perhaps some one will know of a script that will help. I need to have emails sunmitted to a database field but more to the point I need to have the attachment stripped and added to MySQL. People will be mailing in Word documents and pdf files that I need to be inserted into the database. I'm assuming there has to be a script that I can run that will strip the attachment, convert to text file, and insert into the database. Anyone heard or know of anything? Thanks
Re: Foreign key contraints, on delete cascade not working?
Andrew DeFaria [EMAIL PROTECTED] wrote: I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However: drop database if exists MYDB; create database MYDB; use MYDB; create table user ( userid varchar (8) not null, name tinytextnot null, primary key (userid) ) type=innodb; -- user create table useropts ( userid varchar (8) not null, name tinytext, value varchar (128), key user_index (userid), foreign key (userid) references user (userid) on delete cascade ) type=innodb; -- useropts insert into user values (userA, User A); insert into useropts values (userA, option, value); select * from user; select * from useropts; delete from user; select * from useropts; select * from user; $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35215 to server version: 4.0.10-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql source MYDB.sql Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) +++ | userid | name | +++ | userA | User A | +++ 1 row in set (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Empty set (0.00 sec) As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: email attachments, stripped, coverted to text and inserted into MySQL
On Tue, Jan 13, 2004 at 09:41:57AM -0400, Vernon Webb wrote: I've been asking around for awhile without a solution to this problem and thought I'd post it here perhaps some one will know of a script that will help. I need to have emails sunmitted to a database field but more to the point I need to have the attachment stripped and added to MySQL. People will be mailing in Word documents and pdf files that I need to be inserted into the database. I'm assuming there has to be a script that I can run that will strip the attachment, convert to text file, and insert into the database. Anyone heard or know of anything? I don't know of an existing script. I would probably just write something quick in perl using MIME::Tools or in python using mimetools. Both offer easy processing of MIME messages (ie, modern message with attachments), and their languages also have good database bindings. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help with syntax for mysqldump
I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql If I can't get the dump file to work, how do I get the database on my local machine up to the remote server. I am using version 4.0.15. Are there any bugs? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql if you could send errors you are getting that would be a help for us One thing, you don't use mysqldump from the mysql prompt, you use it from the msyql directory on your C:/Drive --example C:\mysqlbin/mysqldump -u yourusername -pyoupassword --alldatabases outfile.sql specifying the password in the string is not the best way to go hth Jeff If I can't get the dump file to work, how do I get the database on my local machine up to the remote server. I am using version 4.0.15. Are there any bugs? Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem creating sp
Victoria, sorry for waste your time with this =(
Just is a EMS MySQL Manager 2.0.1.4's error, from command line (mysql) works fine; the typo was writing the mail.
Alejandro.
---Mensaje original---
De: Victoria Reznichenko
Fecha: 01/13/04 11:04:50
Para: [EMAIL PROTECTED]
Asunto: Re: Problem creating sp
"adburne" [EMAIL PROTECTED] wrote:
I'll be trying to work with sp on win32/5.0.0-alpha-max-debug; I take the
mysql's page example to test but makes an error:
mysql delimeter |
You made a typo. You should write 'delimiter'.
- create function hello (s char(20)) returns char(50)
- return concat('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax.Check the manual
that
corresponds to your MySQL server version for the right syntax to use near
deli
meter |
create function hello (s char(20)) returns char(50)
return concat('H' at line 1
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
_ ___ __
/|//_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [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]
_ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql mysqldump -udavidrayner -pdavidrayner eeetic eeetic.sql mysqldump -udavidrayner -pdavidrayner -A all.sql zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
abnormal client termination
Scenario: Client gets a connection from MySQL. The client process is killed i.e. client does not close the connection to MySQL. Observation: When I use Show ProcessList command to look at all the open connections, the connection with the client (terminated abnormally) doesn't show up in the list. Question: a. Will the connection held by an Abnormally Terminated Client automatically be closed by MySQL server??? b. If Yes is the answer to the previous question, how does MySQL detect this?? c. Is there a timeout associated with each client connection?? If so, how can I change this?? Thanks Jawahar
Re: Database take too much hard drive space
Xavier Fernández i Marín wrote: Hi, I've been inserting a csv file of about 150Mb into a mysql database. The problem is that there is no enough free space on the hard disk, and the process have been collapsed. Now I've been trying to access to the database to drop the table and change the directory where mysql stores the information, but I can't access it. I suppose that the procedure is to stop mysql server and then drop the table, but I don't know how to drop a table when mysqld does not run. And, appart from that, is this the best procedure? I'm running mysql 3.23 in a Linux Mandrake 9.0 Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] As long as the table is a standard mysql table (MyISAM), you can just delete the data files for that table from the database directory. I'd make a backup copy of the datafiles before you delete them just in case something doesn't go correctly. Also make sure mysql isn't running. There may be a better way, but this should work. To remove a table called customer from a database called office for example, you'd simply do. rm /var/lib/mysql/office/customer.* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
Matthew Stuart wrote: I am really struggling with a mysqldump. I am trying to create a dump of a complete database called csi_db01 and I am trying to save the dump file to My Documents on the C drive. I am sure I am doing right, but could somebody email me the full syntax to use which comes after the mysql Run mysqldump from a shell (DOS) prompt, not from the mysql client. Something like: prompt% mysqldump -u root -prootpassword csi_db01 :: will dump to standard out; add the path to where you want to save the dump, like: prompt% mysqldump -uroot -p csi_db01 /path/to/dumpfile.sql I'm not sure how a path with spaces -- My Documents -- is going to work, but experiment (or pick another location). :-) HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wildcards in the field
At 03:52 AM 1/13/2004, Harald Fuchs wrote: SELECT * FROM tbl WHERE user = 'jones' AND '/data1/index.php' LIKE concat(path, '%'); Thanks for the tip. Bob == Bob Ramsey Applications Development Support II ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Socket Error
Birju,
What messages do you get in the mysql log file? The message you're
getting from perl just says that the database is not available. Before
you try to restart mysql, have you checked to see if there are still
mysql processes running (ps -eax |grep -i mysql). Have you run myisamchk
on the tables since mysql has crashed.
walt
Birju Shah wrote:
Hello,
Thankyou for your quick response.
When I start getting this error, the mysql crashes, any application which I
try to start which uses mysql backend, says mysql.sock error could not
connect .
-
DBI connect('database=search;host=localhost','web',...) failed: Can't
connect to local MySQL server through socket '/tmp/mysql.sock' (61) at (eval
2) line 1
--
When i try to restart the mysql server nothing happens and the only option
that remains is to reboot the entire system. Everything works fine then for
a few days and again the same error.
Birju Shah
-
- Original Message -
From: Daniel Kasak [EMAIL PROTECTED]
To: Birju Shah [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, January 12, 2004 2:13 PM
Subject: Re: Mysql Socket Error
Birju Shah wrote:
Hello Friends,
We are using free bsd, mysql 4.0.15. We are using this system since more
than a year now. We have a autoresponder script which has mysql database.
Since a month or so we are noticing that mysql gives the mysql.sock error
and the only option which remains is to reboot the entire system.
This error didnt happen before, the same scripts are running, we havent
changed anything in the scripts. The mysql gives the socket error sometimes
in 3 days sometimes in 7-8 days.
What can be causing this error and what is the solution.
Sorry to bother you.
Birju Shah
Another mysql.sock error!
Firstly, when you start getting the error, what's happening to MySQL? Do
you access it in any other way, or only though the script which works
via the socket? Can you connect to MySQL with the command-line client,
or with MySQLCC?
Maybe MySQL is crashing and this is the reason which you can't connect
through the socket. What do the mysql logs say? Maybe examine / post them.
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COMP_ERR is missing in windows binary distribution zip file of 4.1.1a
Viktor [EMAIL PROTECTED] wrote: subj. It's inconvenient, because one should get source distribution and compile this tool. You can use comp-err.exe from older packages. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Please help with syntax for mysqldump
mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? Sorry for my ignorance, I am still a beginner. Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find duplicates
blush ouch /blush To Jimmy and Chris and the list in general. This list is particualiarly kind to question of this nature. I for one appricate it. In this case it is amazing how many wrong things I came up with. On Tue, 13 Jan 2004, Chris Elsworth wrote: On Tue, Jan 13, 2004 at 02:03:26AM -0500, [EMAIL PROTECTED] wrote: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1; I.e. replacing order by with a having clause. After trying many variations; are 'order by' and 'having' mutually exclusive? If so - how would you order the result table? They shouldn't be, you just need to get the order right: select prodid,count(groupid) as cnt from products where (groupid=65 or groupid=66) group by imgsml having cnt1 order by cnt; Is perfectly valid syntax. -- Chris _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using BETWEEN or = =
I am attempting to construct a select statement in which I can find values between two fields: start, and end. I have tried using BETWEEN and comparing with = and =, but neither meet success. Can someone please set me straight? This is meant to be in a PHP page, but I'm assuming the syntax is similar if not the same. I understand there is also a min/max, but I'm not sure it would work in this instance. $sql = SELECT * FROM federal-married WHERE start = .$_POST['salary']. AND end = .$_POST['salary']; Table: federal-married (finds federal tax rate based on marital status) start = min field, ie. 804 end = max field, ie. 2801 $_POST['salary'] = salary of individual posted from a form -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext creation on 4.1: ERROR 1034
Kurt Haegeman [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sergei Golubchik wrote: Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table Hi Sergei, alter table articles add fulltext( text ); After several hours of processing, the error below is generated. Regards, Kurt. Did you try to check the table using myisamcheck or CHECK TABLE articles; ??? It might be that your table is corrupted.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MIN with negative numbers in VARCHAR
We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
checking that any element from one group appears in another group?
Hello All,
In MySQL it is possible to check if an element is existing in a group, like:
... WHERE 'a' IN ('a','b','c','d') ...
but that checks one element only.
I want to check if any element from a group exists in another group, like:
... WHERE ('a','f','g') IN ('a','b','c','d') ...
'a' in the first group appears in the second group, so it will return true,
no matter if 'f' or 'g' exist in too.
Well, is there anything like above that I can use?
-thanks, Eli
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MIN with negative numbers in VARCHAR
Could you CAST them first, then apply MIN? -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: MIN with negative numbers in VARCHAR We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- 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]
MySQL Existing with other DBMSes
Hi everyone, Excuse me for asking this question but I would like to know if I can install and use other DBMSes like: PostgreSQL, Oracle, and Sybase. I'm using Mac OS X v10.3.2, and MySQL v4.0.14 I like MySQL and haven't found a need for other DBMSes except that most Job listings I come across require experience with Oracle or Sybase. Thank you. -Gohaku -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: checking that any element from one group appears in another group?
Couldn't it be something like:
WHERE ('a' IN ('a','b','c','d')
OR 'f' IN ('a','b','c','d')
OR 'g' IN ('a','b','c','d')
)
On Tue, 2004-01-13 at 15:34, Eli Hen wrote:
Hello All,
In MySQL it is possible to check if an element is existing in a group, like:
... WHERE 'a' IN ('a','b','c','d') ...
but that checks one element only.
I want to check if any element from a group exists in another group, like:
... WHERE ('a','f','g') IN ('a','b','c','d') ...
'a' in the first group appears in the second group, so it will return true,
no matter if 'f' or 'g' exist in too.
Well, is there anything like above that I can use?
-thanks, Eli
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving Bookmark Table Data
From: Michael B Allen [mailto:[EMAIL PROTECTED]
I'd like to move some bookmarks in one database to another. The format
of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly
trucated):
+--+--+--+-+-+
---+
| Field| Type | Null | Key | Default
| Extra |
+--+--+--+-+-+
---+
| bookmark_id | int(11) unsigned | | PRI | NULL
| auto_incr |
| group_id | int(10) unsigned | | MUL | 0
| |
| bookmark_title | varchar(255) | | |
| |
| bookmark_url | varchar(255) | | |
| |
| bookmark_descrip | varchar(255) | YES | | NULL
| |
| bookmark_creatio | datetime | | | -
| |
| bookmark_private | char(1) | | | 0
| |
| bookmark_last_hi | datetime | YES | | NULL
| |
| user_id | int(10) unsigned | | MUL | 0
| |
| bookmark_deleted | char(1) | | | 0
| |
+--+--+--+-+-+
---+
I want to move this into the online-bookmarks 'bookmark' table which
has the format:
+-+---+--+-+-+
+
| Field | Type | Null | Key | Default | Extra
|
+-+---+--+-+-+
+
| user| char(20) | | | |
|
| title | char(70) | | MUL | |
|
| url | char(200) | | | |
|
| description | char(200) | YES | | NULL|
|
| private | enum('0','1') | | | 0 |
|
| date| timestamp(14) | YES | | NULL|
|
| childof | int(11) | | | 0 |
|
| id | int(11) | | PRI | NULL|
auto_increment |
| deleted | enum('0','1') | | | 0 |
|
+-+---+--+-+-+
+
The only field mappings I care about are:
bookmark_title - title
bookmark_url - url
bookmark_description - description
Now I know I can generate insert statements and then run that
script on
the target but is there a better way? The databases do not have direct
access as each is running on localhost only.
While it's a little clunky, why not do this on the first server:
CREATE TABLE apb_bookmarks_tmp
SELECT '' AS user,
bookmark_title AS title,
bookmark_url AS url,
bookmark_description AS description,
0 AS private,
NULL AS date,
0 AS childof,
NULL AS id,
0 AS deleted
FROM apb_bookmarks;
I'm assuming that by these are the only field mappings that I care about, you mean
that those are the only fields you want brought over. If not, then replace the NULLs
and 0s with the proper field names.
Once you've done this, all you need to do is mysqldump the table, scp it over the the
new host, and import it. If you had indices on the original table, you'll have to
manually create them on the new table -- CREATE TABLE ... SELECT FROM doesn't carry
those over.
HTH!
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using BETWEEN or = =
From: Eve Atley [mailto:[EMAIL PROTECTED]
I am attempting to construct a select statement in which I
can find values between two fields: start, and end. I have
tried using BETWEEN and comparing with = and =, but
neither meet success. Can someone please set me straight?
This is meant to be in a PHP page, but I'm assuming the
syntax is similar if not the same. I understand there is
also a min/max, but I'm not sure it would work in this
instance.
$sql = SELECT * FROM federal-married WHERE start =
.$_POST['salary']. AND end = .$_POST['salary'];
Table: federal-married (finds federal tax rate based on
marital status)
start = min field, ie. 804
end = max field, ie. 2801
$_POST['salary'] = salary of individual posted from a form
Have you tried this?
$sql =
SELECT *
FROM federal-married
WHERE {$_POST['salary']} BETWEEN start AND end
;
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MIN with negative numbers in VARCHAR
DOUBLE doesn't seem to be an option with CAST At 10:31 am 1/13/2004, you wrote: Could you CAST them first, then apply MIN? -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: MIN with negative numbers in VARCHAR We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Implement one statement w/o subqueries.
From: Ruslan U. Zakirov [mailto:[EMAIL PROTECTED] Hello. Table 1: Items id, Name Table 2: Properties id, Item, Name, Value I want select Items _and_ all thier props only if Item have specified property. Example: Table Item: 1, Mouse 2, Monitor 3, Keyboard Table Properties: 1, 1, Color, Red 2, 2, Color, Gray 3, 1, Interface, Wireless 4, 2, MaxResolution, [EMAIL PROTECTED] 5, 3, Color, Gray 6, 3, NumberOfKeys, 101 I want select all Items and all thier properties if item's Color is Gray: Item, Name, Prop, Value 2, Monitor, Color, Gray 2, Monitor, MaxResolution, [EMAIL PROTECTED] 3, Keyboard, Color, Gray 3, Keyboard, NumberOfKeys, 101 I hope you've understand what I mean. Thanks. Ruslan. PS: MySQL 4.0.x I believe you just need to join the Properties table twice: SELECT I.id, I.Name, P2.Name, P2.Value FROM Properties P1 INNER JOIN Items I ON I.id=P1.Item INNER JOIN Properties P2 ON P2.Item=I.id WHERE P1.Name='Color' AND P1.Value='Gray'; -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MIN with negative numbers in VARCHAR
ok... you might have two options: 1- (don't know if this will work) do a min(cast(Value * 100 as signed integer) / 100 2- or min(Value + 0.0) and see what happens. -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 11:24 AM To: [EMAIL PROTECTED] Subject: RE: MIN with negative numbers in VARCHAR DOUBLE doesn't seem to be an option with CAST At 10:31 am 1/13/2004, you wrote: Could you CAST them first, then apply MIN? -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: MIN with negative numbers in VARCHAR We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql listed in spamcop?
In the last episode (Jan 13), [EMAIL PROTECTED] said: This may have been mentioned.. I have not been recieving message for 12+ hours now.. And it appears: Jan 13 01:23:49 cyclone tcplog: smtp connection attempt from 213.136.52.31 Jan 13 01:23:50 cyclone sendmail[10674]: ruleset=check_relay, arg1=lists2.mysql.com, arg2=213.136.52.31, relay=lists2.mysql.com [213.136.52.31], reject=553 5.3.0 Spam blocked see: http://spamcop.net/bl.shtml?213.136.52.31 Somehow the mysql mailserver got listed in spamcop? You shouldn't be using spamcop as a blacklist. Spamcop has a very low spam threshold, and large sites get autolisted multiple times a month, and are usually removed quickly. It's better used as an indicator of spam along with other tests. For example, spamcop hits are 1.5 points in Spamassassin's ranking (4 points = spam). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recreating InnoDB tables -WITHOUT- .frm
To all the InnoDB gurus out there: I have a similar problem to this person's predicament, except my situation is that I have all the innodb data and log files, but have absolutely no .FRM files. Are there any general tools for data recovery from InnoDB databases? Any companies that can do this for a fee? Anything??? :) Thanks.crossing my fingers that myself and Adam can find resolutions to our respective situations! [EMAIL PROTECTED] wrote: I'm cleaning up a user-error where the innodb data files were deleted without a useful backup. I need to reconstruct the tables and still have the frm files. Is this possible? A significant amount of time was put into these tables' structures and I hate to lose that effort... Yes the users are kicking themselves about the backup... MTIA Adam -- Matthew Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
On Tue, 13 Jan 2004 14:07:38 +, Matthew Stuart [EMAIL PROTECTED] wrote: Similarly. SELECT intDEVID,txtDEVPOSTCODE INTO OUTFILE c:/aaa/dump.sql from ytbl_development; (dump.sql file must NOT already exist) zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installing mySQL on Windows 2000
I'm attempting to install version 4.0.16 When I try to install the software I get an error that reads: An error occurred during the data move process: 103 Can anyone give me any guidance on how to resolve this. Thanks, Ken This e-mail and any files transmitted with it may contain information that is PRIVILEGED, CONFIDENTIAL, and exempt from disclosure under applicable law. It is intended only for the individual(s) or entity named above. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained in it or attached to it is strictly prohibited. If you have received this e-mail in error, please delete it and immediately notify the person named above by reply e-mail. Thank you.
Re: JOIN types
Sorry, here are the EXPLAINS of a similar case, where what I did was switch the first two tables in the join, and make it LEFT not INNER (note: the query may look a little odd, as I took out some of the fields I was selecting, etc...to make it shorter ;p) mysql EXPLAIN SELECT t.profile_alias FROM iwantu_tables_2 t INNER JOIN iwantu_profile_2_1 p USING(uid) INNER JOIN iwantu_desired_2_1 d USING (uid) INNER JOIN profile_descriptions s ON (p.description_id = s.description_id) WHERE (p.profile_status IN (1,2)) ORDER BY p.profile_updated DESC LIMIT 30; +---+++-+-+ --+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+++-+-+ --+-+--+ | d | index | idx_uid| idx_uid | 3 | NULL | 1002592 | Using index; Using temporary; Using filesort | | p | ref| idx_profile_status,uid | uid | 3 | iwantu_new.d.uid | 1 | Using where | | t | eq_ref | PRIMARY| PRIMARY | 3 | iwantu_new.p.uid | 1 | | | s | eq_ref | PRIMARY| PRIMARY | 4 | p.description_id | 1 | Using index | +---+++-+-+ --+-+--+ 4 rows in set (0.00 sec) mysql EXPLAIN SELECT t.profile_alias FROM iwantu_profile_2_1 p LEFT JOIN iwantu_tables_2 t USING(uid) INNER JOIN iwantu_desired_2_1 d USING (uid) INNER JOIN profile_descriptions s ON (p.description_id = s.description_id) WHERE (p.profile_status IN (1,2)) ORDER BY p.profile_updated DESC LIMIT 30; +---++++-+- -++-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++++-+- -++-+ | p | range | idx_profile_status | idx_profile_status | 4 | NULL | 684324 | Using where; Using filesort | | t | eq_ref | PRIMARY| PRIMARY| 3 | iwantu_new.p.uid | 1 | | | d | ref| idx_uid| idx_uid| 3 | iwantu_new.t.uid | 1 | Using index | | s | eq_ref | PRIMARY| PRIMARY| 4 | p.description_id | 1 | Using index | +---++++-+- -++-+ 4 rows in set (0.00 sec) Also, I'm running MySQL 4.0.13-standard, STRAIGHT JOIN doesnt seem to exist for me ;p -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager +50 6 280 2284 ext.108 Quoting Matt W [EMAIL PROTECTED]: Hi Keith, I would assume it's because LEFT JOIN forced a change in the join order (in EXPLAIN). Does using STRAIGHT JOIN give the same result? So your query was this? SELECT a.field FROM table1 a LEFT JOIN table2 b USING (field2) ORDER BY b.field3 DESC If table1 is read first (which it should be), then I don't see how there's no temp table/filesort because the column(s) you're ordering by don't come from the first used table. Oh yeah, and is there an index on field2 in both tables? It'd be better to see the EXPLAIN output for the different queries. :-) It could also be an optimizer bug. What version of MySQL are you using? Matt - Original Message - From: Keith Bussey Sent: Monday, January 12, 2004 4:49 PM Subject: JOIN types Hey all, I've read the pages in the MySQL manual that explain the types of JOINs many times, but still think I'm missing something. I usually was always using INNER JOIN in all my join queries, and in a few cases LEFT JOIN (in cases I wanted the rows in one table that were not in the other one). I've started to discover, with the help of EXPLAIN, that the join type can seriously affect performance. For example, I had a query such as this: SELECT a.field FROM table1 a INNER JOIN table2 b USING (field2) ORDER BY b.field3 DESC It was using both filesort and a temporary table (in EXPLAIN) and took about 4.50 seconds to run. I switched the order of the tables in the join, putting table2 first, and nothing changed in my EXPLAIN. I then changed the join to LEFT JOIN, and suddenly I had no more filesort or temporary table, and the query took
firewall ports to open
I am trying to allow someone to access our mysql server that is behind our firewall. If I open the firewall ports 3306-65000/tcp the person can get through. If I just try to open up the port 3306/tcp, they can not get through. Is there another port that I need to open also? I have tried 3306-3307/tcp but that wasn't it. Any help would be appreciated. I have also searched the web and could only find that it uses 3306 and 3307, but that sounded kind of weird to me. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MIN with negative numbers in VARCHAR
min(Value + 0) works. It's not the most direct way I've run into but it does the job. I wonder why they didn't do something more common like the C cast syntax (double) Value? At 11:35 am 1/13/2004, you wrote: ok... you might have two options: 1- (don't know if this will work) do a min(cast(Value * 100 as signed integer) / 100 2- or min(Value + 0.0) and see what happens. -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 11:24 AM To: [EMAIL PROTECTED] Subject: RE: MIN with negative numbers in VARCHAR DOUBLE doesn't seem to be an option with CAST At 10:31 am 1/13/2004, you wrote: Could you CAST them first, then apply MIN? -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: MIN with negative numbers in VARCHAR We have a table with floating point measurement values stored in VARCHAR's. How can I get MIN in SELECT to evaluate these as numbers instead of strings so negative value sort correctly? Right now I've kludged it as MIN(SIGN(Value) * ABS(Value)). Is there a more straight forward way? -- 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] -- 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] Eldon Ziegler President ProAtion Systems, Inc. www.proation.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Broadcast to search available MySQL Server in Network?
I tried a lot of things. Knowing now: MySql is listening at... INET, TCP, Port 3306. Direct connection is possible! The server answered with its version. But what is with broadcast? Because I don't known were the servers! That is what I like to find out! Do any buddy know how to call a MySQL server in network via broadcast? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with Replication in 4.0.17
I am using 4.0.17 rpm on Red Hat 7.3 (fully updated). I have a server colocated at my local ISP, and my workstation is on ADSL behind a Netsys router (the ADSL ISP uses PPPoE, don't know if that's relevant or not). The server has RAID 1, and has always been 100% reliable (up since 2000). I have been using MySQL for over four years now, and have never had any problems until recently, when I tried using replication. I wanted to mirror the database to my workstation over the DSL connection. I got it working correctly, but quickly found that the slave would just stop replicating if I went away and left it for a while (hours). It would be fine while I sat there, but overnight or after a couple of hours away from my workstation, I would return and it had just stopped. There were no errors in the log on either end. It just wasn't updating. Restarting the slave would quickly bring things up to date again. Eventually I tried lowering the master-connect-retry to 10 seconds, and slave-net-timeout to 60 seconds. This seemed to fix this particular problem. Overnight I could come back and everything was still synced up. I don't know why this could cause an issue, since I keep long-lived ssh connections to my server all day long without problem. I have also noticed other problems - most worrying of which is that records inserted into the master database have actually disappeared completely from the master and slave. My website has message boards, and on two occasions now I have posted a message, seen it in the database (i.e. read the website) and then come back to see that the new message is just gone. These boards have been in operation for years, and are extremely reliable. Never have messages simply vanished. The first time this happened, it only took a few seconds to go away. The second time, it was overnight. This is extremely scary behaviour. Also, in multiple unrelated instances, one of the master index files have become corrupted, and had to be repaired using myisamchk. All my tables are MyISAM. The same corruption has also happened on the slave. I have never had corrupted tables before now. The other thing that keeps happening is that the slave seems to get out of sync somehow with the master - I came in this morning to find that it had choked on a duplicate primary key. I made the slave skip 2 and it recovered itself, but this has happened a number of times now. There is no work being done on the slave version of the database, no possible way that it would get out of sync as a result of changes on the workstation. I am the only user, and there are no processes doing anything with the database. It is a pure slave. Yet, somehow, it ends up with a duplicate key. I am worried enough about all this that I have disabled replication for the time being. Has anyone else experienced missing updates and/or table index corruption as a result of enabling replication? The replication mechanism should surely do no harm on the master as a result of being active, but this is clearly happening. I am fairly sure that this is a bug, but since it is so sporadic and non repeatable, it's very hard to say what could be causing it. I should make clear that I am fairly certain that replication is set up correctly - it replicates very well in normal circumstances. Updates on the server appear on the slave almost instantaneously. If anyone else has any insight or similar experiences, please let me know. I would like to know if this is a known bug or something that hasn't been nailed down yet. I should finally say that I've always been 100% happy with the robustness of MySQL, so this was a little shocking to me! I think MySQL is an extremely useful database system, and I plan to continue using it. Hopefully all this is just an obscure bug. Thanks, -Neil Gunton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: firewall ports to open
Our server works with remote access just with 3306/tcp. Maybe it's something to do with what you are using to access mysql. At 12:53 pm 1/13/2004, you wrote: I am trying to allow someone to access our mysql server that is behind our firewall. If I open the firewall ports 3306-65000/tcp the person can get through. If I just try to open up the port 3306/tcp, they can not get through. Is there another port that I need to open also? I have tried 3306-3307/tcp but that wasn't it. Any help would be appreciated. I have also searched the web and could only find that it uses 3306 and 3307, but that sounded kind of weird to me. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Eldon Ziegler President ProAtion Systems, Inc. www.proation.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: firewall ports to open
At 11:53 -0600 1/13/04, Steve Buehler wrote: I am trying to allow someone to access our mysql server that is behind our firewall. If I open the firewall ports 3306-65000/tcp the person can get through. If I just try to open up the port 3306/tcp, they can not get through. Is there another port that I need to open also? I have tried 3306-3307/tcp but that wasn't it. Any help would be appreciated. I have also searched the web and could only find that it uses 3306 and 3307, but that sounded kind of weird to me. The server uses one TCP/IP port. By default this is 3306, but can be configured to be something else. You should have to open up only the port that the server is listening on. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What Does This Mean
OK I got the database created and stuff; When in the MySQLAdmin screen under databases it doesn't show under databases. That is the first problem. And for some reason the MYSQLadmin screen list the local user name as Administrator and will not let me change it. But I can get into the mysql shell and see the database I created and I did the GRANT ALL ON MariluMessageBoards.* TO [EMAIL PROTECTED]; I also try the MySqlCC and try to get on the server and it won't let me. This is what it tells me: ERROR 1130: Host 'NapMarilu' is not allowed to connect to this MySQL server. Do I need to do something to allow me to connect? Sorry for asking so many questions, but I am puzzled... Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical records, I need some help!! ;(
On 13 Jan 2004, at 09:19, Victor Reus wrote: Hi again, Second: specify what you mean with all relations. Can you share some sample data and sample output (that is: what do you expect the query to return). Ok my table noms is like | id | int(11) | | PRI | [NULL] | auto_increment | reference| varchar(255) | | component| varchar(255) | The relation between refefence and component are like parent - child Here is an example Table contains : IDREFERENCE COMPONENT 1 A004 B001 2 B001 C003 3 B001 D003 (I have more records but only this are what i want to take as an example) So the relations are like a hierarchical tree: A004 BOO1 C003 D003 And i want one sentence that takes all the relations. The SQL statement result should be like: REFERENCE COMPONENT AOO4 B001 B001 COO3 B001 COO3 Coudl somebody give me a tip to do it?. Thanks. Victor. Why not SELECT * FROM THE_TABLE WHERE REFERENCE = 'B001' OR COMPONENT = 'B001' This will work given your example. However, I suspect this isn't the full story. Do you also want to see what components make up C003 and D003 in the same query? I think you're after a feature not yet implemented in MySQL - the 'CONNECT BY PRIOR' SELECT statement, just the ticket for hierarchical queries. http://www.mysql.com/doc/en/TODO_future.html - the first item in the list! Here's an article on how it's used in Oracle - is this what you need? http://www.oracleadvice.com/Tips/pkfktree.htm BTW - MySQL people - any idea when this will be implemented? I have a couple of projects with hierarchical records which would just love this query! -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical records, I need some help!! ;(
Steve Folly said: However, I suspect this isn't the full story. Do you also want to see what components make up C003 and D003 in the same query? I think you're after a feature not yet implemented in MySQL - the 'CONNECT BY PRIOR' SELECT statement, just the ticket for hierarchical queries. http://www.mysql.com/doc/en/TODO_future.html - the first item in the list! Here's an article on how it's used in Oracle - is this what you need? http://www.oracleadvice.com/Tips/pkfktree.htm BTW - MySQL people - any idea when this will be implemented? I have a couple of projects with hierarchical records which would just love this query! I most certainly hope this Oracle idiosyncracy will never make it into MySQL. The SQL standard defines a different syntax for doing recursive queries, using WITH RECURSIVE. I see no reason for MySQL to implement a non-standard way for doing recursive queries when there is a viable alternative (DB2 already uses WITH conform the SQL standard) that has standardized behaviour. I know that a CONNECT BY PRIOR patch is readily available for PostgreSQL but the PostgreSQL developers reject it exactly because it does not follow the SQL standard. I think the PostgreSQL developers are right to do so, non-standard features have caused enough trouble for application portability as it is. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1.1a
On Mon, 12 Jan 2004, Viktor wrote: Hello mysql, Table-level privileges do not work at all... (on Windows) Works fine for me: 4.1.1a-alpha-max-nt:tmp GRANT SELECT ON tmp.tmp TO [EMAIL PROTECTED] IDENTIFIED BY 'aaa'; Query OK, 0 rows affected (0.18 sec) 4.1.1a-alpha-max-nt:tmp \q Bye C:\mysql\4.1-tree\bin.\mysql -u tmp -p -P 3308 Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.1a-alpha-max-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 4.1.1a-alpha-max-nt:(none) SELECT * FROM tmp.tmp; +--+ | a| +--+ |1 | +--+ 1 row in set (0.42 sec) 4.1.1a-alpha-max-nt:(none) SELECT * FROM tmp.meep; ERROR 1142 (42000): select command denied to user: 'tmp'@'localhost' for table 'meep' 4.1.1a-alpha-max-nt:(none) cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird
Ok I was able to use mysqlcc and as long as I keep the host name localhost it will let me in. But the name of the host should be Napmarilu or the IP number right? When you change it to either one of those it will not let you in. So how would I fix this problem. However on the other hand mysqladmin screen it still only shows the user Administrator and the test database sheets. Any one got any ideas? Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED]
Re: Recreating InnoDB tables -WITHOUT- .frm
Matthew, http://www.innodb.com/ibman.php#InnoDB_Monitor Starting from 3.23.44, there is innodb_table_monitor with which you can print the contents of the internal data dictionary of InnoDB. The output format is not beautiful, and you have to manually reconstruct the MySQL CREATE TABLE statements from it. Adam, you can try creating a dummy InnoDB table with enough PRIMARY KEY columns. Then replace its .frm file with an old one you have, and try to print SHOW CREATE TABLE. I do not know if mysqld will crash or assert. This question was discussed on this mailing list some 2 years ago. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ ... List:MySQL General Discussion« Previous MessageNext Message » From:Matthew ScottDate:January 13 2004 5:33pm Subject:Recreating InnoDB tables -WITHOUT- .frm To all the InnoDB gurus out there: I have a similar problem to this person's predicament, except my situation is that I have all the innodb data and log files, but have absolutely no .FRM files. Are there any general tools for data recovery from InnoDB databases? Any companies that can do this for a fee? Anything??? :) Thanks.crossing my fingers that myself and Adam can find resolutions to our respective situations! [EMAIL PROTECTED] wrote: I'm cleaning up a user-error where the innodb data files were deleted without a useful backup. I need to reconstruct the tables and still have the frm files. Is this possible? A significant amount of time was put into these tables' structures and I hate to lose that effort... Yes the users are kicking themselves about the backup... MTIA Adam -- Matthew Scott [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key contraints, on delete cascade not working?
Victoria Reznichenko wrote: Andrew DeFaria [EMAIL PROTECTED] wrote: I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However: drop database if exists MYDB; create database MYDB; use MYDB; create table user ( userid varchar (8) not null, name tinytext not null, primary key (userid) ) type=innodb; -- user create table useropts ( userid varchar (8) not null, name tinytext, value varchar (128), key user_index (userid), foreign key (userid) references user (userid) on delete cascade ) type=innodb; -- useropts insert into user values (userA, User A); insert into useropts values (userA, option, value); select * from user; select * from useropts; delete from user; select * from useropts; select * from user; $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35215 to server version: 4.0.10-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql source MYDB.sql Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) +++ | userid | name | +++ | userA | User A | +++ 1 row in set (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) +++---+ | userid | name | value | +++---+ | userA | option | value | +++---+ 1 row in set (0.00 sec) Empty set (0.00 sec) As you can see I when I delete from user (the parent table) the useropts (child table) entry remains. Shouldn't it be deleted? Heikki Tuuri asked me to look at my innodb variables and I found: mysql show variables like %innodb%; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.00 sec) But I still must ask: Why is that? How do I turn it on? Do you use 3.23.xx version? I'm using 4.0.10-gamma as mysql monitor indicates. Also: $ mysqld --version mysqld Ver 4.0.10-gamma for mandrake-linux-gnu on i586 You should install MySQL-Max binary if you want to use InnoDB: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html This doesn't apply as MySQL version is 4.0.10. Any other ideas? -- Hidden DOS secret: add BUGS=OFF to your CONFIG.SYS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN types
Keith Bussey wrote: ... Also, I'm running MySQL 4.0.13-standard, STRAIGHT JOIN doesnt seem to exist for me ;p I think it is actually STRAIGHT_JOIN... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crash on FreeBSD-Alpha (64 Bit)
Hi! On Jan 12, Holm Tiffe wrote: Description: mysqld 4.0.17 crash on FreeBSD 5.1-current-alpha How-To-Repeat: Any acces over IP (not domain socket) crashes mysqld: #/usr/local/bin/mysqladmin: connect to server at 'install' failed error: 'Lost connection to MySQL server during query' syslog: install mysqld[78066]: warning: can't get client address: Bad file descriptor I suspect it is a an issue of KSE library (either a bug or some incompatibility with MySQL). Could you run mysqld with libc_r and see if it helps ? Unfortunately we don't have FreeBSD-5/Alpha to try this ourselves. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
mysql select Notice_ID from Notices where match (Text) against
('+pollution +control' in boolean mode);
Empty set (0.00 sec)
mysql select Notice_ID from Notices where match (Text) against
('pollution control' in boolean mode);
Empty set (0.02 sec)
mysql select Notice_ID from Notices where Text like '%pollution
control%';
+---+
| Notice_ID |
+---+
|192090 |
+---+
1 row in set (5.00 sec)
Your LIKE query is not equivalent to your MATCH AGAINST. For example,
Text containing pollution controls would match LIKE '%pollution
control%', but would not MATCH AGAINST ('+pollution +control'...). Have
you looked at Text for this row to be sure it contains exactly
pollution and control?
No, it isn't equivalent but it would match less than the AND would,
since it is an exact phrase. Yes, those words and that exact phrase
exists multiple times. It has been confirmed as a bug.
mysql select Notice_ID from Notices where match (Text) against
('+pollution +air' in boolean mode);
Empty set (0.03 sec)
mysql select Notice_ID from Notices where match (Text) against ('air
pollution' in boolean mode);
Empty set (0.00 sec)
mysql select Notice_ID from Notices where Text like '%air pollution%';
+---+
| Notice_ID |
+---+
|196349 |
|196569 |
|188183 |
|192090 |
|192686 |
|199283 |
+---+
6 rows in set (0.17 sec)
(NOTE on the search for air -- my.cnf has ft_min_word_len=3)
Are you certain that air is indexed? What does
SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('air');
return?
Just over 6 thousand results. And it was answered just below, here:
All OR searches work perfectly fine, as per:
mysql select count(*) from Notices where match (Text) against ('air
pollution');
+--+
| count(*) |
+--+
| 100 |
+--+
1 row in set (0.03 sec)
How many do you get with
SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('pollution');
If air isn't indexed, I'd expect 0 hits for 'air' and 100 hits for
'pollution'.
17 hits for pollution.
Do you normally talk down to people as if they are too retarted to have
tested the obvious themselves? This wasn't a complaint or a how-to
question, but a researched and tested bug. I specifically demonstrated
that the phrases exist and that clearly stated that the OR searches worked.
--
Joe Rhett Chief Geek
[EMAIL PROTECTED] Isite Services, Inc.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Socket Error
Birju,
Here's a little Linux/Unix info for you.
The /tmp/mysql.sock in question is used for ipc (Inter Process
Communication). This allows other applications to communicate with the mysql
server without using a TCP/IP connection. This eliminates the need to pass
everything through the networking code.
In the post below, the person is having problems when trying to connect
through the network layer. It would be like changing your database connect
string to
DBI connect('database=search;host=192.168.1.5','web',...).
As far as the 1045 error, it does not look like you typed everything
correctly. What is the -number supposed to be?
walt
On Wednesday 14 January 2004 05:03 am, you wrote:
Hello Walt,
I did and it says the following
mysql -u websquash -number
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
NO)
One more thing, I just saw that there is someone who has posted a query
where the mysql crashes on freebsd. I am pasting it below
if that can be of help to you taking my situation in consideration.
--Some one posted this
Hi!On Jan 12, Holm Tiffe wrote: Description: mysqld 4.0.17 crash on
FreeBSD 5.1-current-alpha How-To-Repeat: Any acces over IP (not domain
socket) crashes mysqld: #/usr/local/bin/mysqladmin: connect to server at
'install' failed error: 'Lost connection to MySQL server during query'
syslog: install mysqld[78066]: warning: can't get client address: Bad file
descriptorI suspect it is a an issue of KSE library (either a bug or
someincompatibility with MySQL).Could you run mysqld with libc_r and see if
it helps ?Unfortunately we don't have FreeBSD-5/Alphato try this ourselves.
Regards,Sergei--__ ___ ___ __ / |/ /_ __/ __/ __ \/ /
Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB,
Senior Software Developer/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com-- MySQL General Mailing ListFor list archives:
http://lists.mysql.com/mysqlTo unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
- ---
- Original Message -
From: walt [EMAIL PROTECTED]
To: Birju Shah [EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 12:17 PM
Subject: Re: Mysql Socket Error
On Wednesday 14 January 2004 04:41 am, you wrote:
Hello,
When I run the command it gave me this
Command history
ls /tmp/mysql.sock
/tmp/mysql.sock
Now, how do I execute the client through the command prompt ?
Let me know
Thankyou
birju shah
try something like
`mysql -u your_user_name -p`
when it asks for your password, type it in.
walt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical records, I need some help!! ;(
I want to have all the items into a recorset with only one query like Select id, reference, component from NOMS where reference = '4' but i want not only the primary relations, i want all relations. Could somebody help a newbie like me? It can also help to change the way you look at the relationships, and use nested sets. With nested sets you do not record the parent-child relationship but rather the range that a given entity has control of. So instead of having a manager with an ID of 1 and having three employees with a manager field pointing to 1, you have three employees numbered 1,2,3 and the manager has a left_value and a right_value of 0 and 4 respectively. That is an over-generalization, but check the following links. BTW original credit for the idea goes to Joe Celko and I reccommend his book SQL for Smarties. http://www.sitepoint.com/article/1105 - Tutorial on this that uses PHP http://www.dbmsmag.com/9603d06.html - More detail from Celko http://www.dbmsmag.com/9604d06.html - http://www.dbmsmag.com/9605d06.html - http://www.intelligententerprise.com/001020/celko.shtml http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci5 37290,00.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
Not a bug. In the manual, section Upgrading from Version 3.23 to 4.0, there is * To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables, you need to rebuild them with `REPAIR TABLE table_name USE_FRM'. Based on a guess, or did you analyze the data file I sent? And if so, may I suggest that the upgrade documentation REALLY needs to be broken into sections? 1. Table changes 2. Privilege changes 3. Configuration changes 4. API/result changes Yeah, I did overlook that statement -- stuck between a large number of notes about result changes relative to character set implementations. -- Joe Rhett Chief Geek [EMAIL PROTECTED] Isite Services, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL_NO_CACHE
Hi, I am trying to do some performance analysis by trying different indexing schemes and testing how long it takes. To get consistent results, I would like to use something like SQL_NO_CACHE. However, the mysqld version that I have installed does not seem to support it..its 4.0.16 Could anyone suggest any other way of achieving this. Thanks, Priyanka _ Get a FREE online virus check for your PC here, from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: hierarchical records, I need some help!! ;(
I have been using adjacency lists to solve problems with hierarchical data.
The algorithm is described in a relatively database independent way here:
http://www.intelligententerprise.com/001020/celko1_1.shtml
And by the same author here: http://www.dbmsmag.com/9603d06.html
Includes a tiny bit more on nested set models.
Cheers,
Matt
-Original Message-
From: Steve Folly [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 1:59 PM
To: MySQL MySQL
Subject: Re: hierarchical records, I need some help!! ;(
On 13 Jan 2004, at 09:19, Victor Reus wrote:
Hi again,
Second: specify what you mean with all relations. Can you
share some sample data and sample output (that is: what
do you expect the query to return).
Ok my table noms is like
| id | int(11) | | PRI | [NULL] | auto_increment
| reference| varchar(255) |
| component| varchar(255) |
The relation between refefence and component are like parent - child
Here is an example
Table contains :
IDREFERENCE COMPONENT
1 A004 B001
2 B001 C003
3 B001 D003
(I have more records but only this are what i want to take as an
example)
So the relations are like a hierarchical tree:
A004
BOO1
C003
D003
And i want one sentence that takes all the relations.
The SQL statement result should be like:
REFERENCE COMPONENT
AOO4 B001
B001 COO3
B001 COO3
Coudl somebody give me a tip to do it?.
Thanks.
Victor.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_init function issues :Connecting MySQL to COBOL
I have somehow managed to create the datatype in COBOL matching
to C datatype and passed as argument to mysql_init and
mysql_real_connect.
My COBOL coding seems to working fine, but it could not able to
connect to MySQL and retrive Data. Instead it produce an error as;
Unknown MySQL Server Host 'Ì' (11001)
It seems that MySQL Server Host identified by C API function
as 'Ì'. I have given my MySQL server host nams as 'MySERVER'.
Is this error message means what i write above? or anything else?
If so, How could I resolve this issue?
any help would be highly appriciated...
thanks
Arun.
Hi MySQLians!
I have clearly mentioned my objective and the portion of work i have finished in my
last mail.
unfortunatly I don't find any reply for the past weekdays. Tii I have a trust, some
one would
take
time to read this and sent me the suggestion to this list.
My current issue is from mysql_init function of MySQL in libmysql.lib library file.
i.e., MYSQL* mysql_init(MYSQL *mysql), here actually this function
require a
parameter
of MYSQL type NULL pointer and return back the MYSQL handle.
From COBOL, If I pass a NULL pointer to this function, it wouldn't agree with that
and produce
runtime ERROR as RDB010E: Interrupt trapped: Illegal storage access.
The error says that, the function trying to access some invalid location other than
MySQL -
right?
To resolve this I try to frame the MYSQL datatype NULL Pointer in COBOL, for that
when I look
the
Datatype used in mysql.h header file, I found that it has number of decalarations
using
standard
datatypes and some user defined data types. see below;
typedef struct st_mysql
{
NET net;
gptr connector_fd;
char *host,
*user,
*passwd,
*unix_socket,
*server_version,
*host_info,
*info,
*db;
struct charset_info_st *charset;
MYSQL_FIELD *fields;
MEM_ROOT field_alloc;
my_ulonglong affected_rows;
my_ulonglong insert_id;
my_ulonglong extra_info;
unsigned long thread_id;
unsigned long packet_length;
unsigned int port,
client_flag,
server_capabilities;
unsigned int protocol_version;
unsigned int field_count;
unsigned int server_status;
unsigned int server_language;
struct st_mysql_options options;
enum mysql_status status;
my_bool free_me;
my_bool reconnect;
char scramble_buff[9];
my_bool rpl_pivot;
struct st_mysql * master,
*next_slave;
struct st_mysql* last_used_slave;
struct st_mysql* last_used_con;
} MYSQL;
- I want to confirm that Is this much lengthy decalration of pointer needed? (OR)
- We can just find the length and given it to COBOL pointer declaration?
- Incase Length does the needs how to fix the length for each dataitem in C?
If it succeed we all, as MySQL user can proud of put a mile stone for the support to
COBOL.
I would be wonder if any one read this clearly and suggest me.
thanks
Arun.
--- Arunachalam [EMAIL PROTECTED] wrote: Hi!
I am trying to connect MySQL Database to COBOL. I have made it possible to some
stages by the
way
as follows; I have developed this under;
Client: Microsoft Windows 2000 Service pack 4.
CA-Realia COBOL Compiler Version 6.0.45
Microsoft (R) Incremental Linker Version 6.00.8168
MySQL server 4.0.17-max in SUSE Linux,
MySQL provides set of C API to interact with MySQL database, to link the functions
to the
compiler
it provides a library file libmysql.lib. COBOL has the External subroutine
featurs to CALL
the
program written in other languages.
When I try to link existing libmysql.lib file directly to the COBOL compiler it
wont
recognize
it. So I have prepared a COBOL compiler specific library file from the
corresponding
libmysql.dll
file, in such a way. (The libmysql.dll I have used to create COBOL compiler
specific
libmysql.lib
is gathered from Mysql 4.0.17-max-debug for windows, in my localhost)
The COBOL compiler specific libmysql.lib is linked successfully to the COBOL
Compiler and I
have
written some subroutine call to the function related to database connection from
COBOL. Such
that
mysql_init, mysql_real_connect, mysql_real_query, mysql_error and mysql_close.
Here mysql_init requires a Null pointer argument and return the MySQL handle back.
mysql_real_connect use that MySQL handle with additional arguments host name,
userid,
password,
DB
name, port number, socket and flag to establish connection to the specified
database.
In my case mysql_init return a handle to mysql_real_connect, but instead of
connection
establishment to MySQL it shows runtime error in COBOL as
RDB0104E: Interrupt trapped: Illegal storage access.
And store an error message in variables memory space
Re: SQL_NO_CACHE
On Tue, 13 Jan 2004, Priyanka Gupta wrote: Hi, I am trying to do some performance analysis by trying different indexing schemes and testing how long it takes. To get consistent results, I would like to use something like SQL_NO_CACHE. However, the mysqld version that I have installed does not seem to support it..its 4.0.16 Could anyone suggest any other way of achieving this. What does SHOW VARIABLES LIKE 'query_cache_type'; show? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Socket Error
Birju Shah wrote:
Hello,
Thankyou for your quick response.
When I start getting this error, the mysql crashes, any application which I
try to start which uses mysql backend, says mysql.sock error could not
connect .
-
DBI connect('database=search;host=localhost','web',...) failed: Can't
connect to local MySQL server through socket '/tmp/mysql.sock' (61) at (eval
2) line 1
--
When i try to restart the mysql server nothing happens and the only option
that remains is to reboot the entire system. Everything works fine then for
a few days and again the same error.
I am attaching the mysql log file. thanks for your help
Birju Shah
See below for comments on logs...
030724 09:46:23 mysqld started
Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
If you do not want to use transactional InnoDB tables, add a line
skip-innodb
to the [mysqld] section of init parameters in your my.cnf
or my.ini. If you want to use InnoDB tables, add to the [mysqld]
section, for example,
innodb_data_file_path = ibdata1:10M:autoextend
But to get good performance you should adjust for your hardware
the InnoDB startup options listed in section 2 at
http://www.innodb.com/ibman.html
It wouldn't be a bad idea to do what MySQL is suggesting and either set
the innodb data file path, or ad the skip-innodb option to your my.cnf
snipped
031030 11:41:08 mysqld started
Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
If you do not want to use transactional InnoDB tables, add a line
skip-innodb
to the [mysqld] section of init parameters in your my.cnf
or my.ini. If you want to use InnoDB tables, add to the [mysqld]
section, for example,
innodb_data_file_path = ibdata1:10M:autoextend
But to get good performance you should adjust for your hardware
the InnoDB startup options listed in section 2 at
http://www.innodb.com/ibman.html
/usr/local/libexec/mysqld: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail
key_buffer_size=8388600
record_buffer=131072
sort_buffer=2097144
max_used_connections=40
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
Right. Here is where I'd be getting worried. Once a db server crashes
like this, I do a re-install and restore my data from a backup. I've
found that if you continue after a crash like this, the chances of
another crash are increased, and also there is no easy way of knowing if
your data is corrupt or missing.
If you use the rpm installation method, sorry ... you're on your own. I
compile MySQL and install into /usr/local/mysql, and if things go bad (
which hasn't happened for over a year for me now, and was hardware
related in my case anyway ), I:
mv /usr/local/mysql /usr/local/mysql_CORRUPT
cd /usr/src/mysql-4.0.17
make install
scripts/mysql_install_db
and then restart the server, import my backups that I create with
mysqldump, and run the transaction logs that were created after the last
backup.
I don't remember MySQL-4.0.15 doing this on me ever, but that's not to
say that it's not a bug. The latest version is 4.0.17 anyway, so I
suggest that the first thing you do is do a full dump of your
database(s), upgrade to 4.0.17, and import your data.
Next, I'd do some hardware checks. Get a memory testing app ( memtest86
- I think - is available on freshmeat ). After testing with this, and
maybe doing a big compile job ( some kde libs should do nicely ), if you
are sure it isn't a hardware issue, make a bug report. Don't overlook
hardware issues, especially on a built-it-myself box. My crashes went
away completely when I dumped our old K6-2 500 for our new Athlon 2000XP
box :)
--
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]
Connecting to remote server
I have a shell script that is supposed to connect to a remote server running MySql 3.23.53. It comes up with an error ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) The script looks like this: /usr/local/mysql/bin/mysql --user=$username --password=$password cetechnology -e \ That makes sense to me, I don't have permissions set correctly right for the $username (hardware). I know the password is set correctly. Then why am I able to connect to the server in the terminal with: /usr/local/mysql/bin/mysql -h 204.xxx.xxx.xxx -u hardware -p cetechnology And then enter my password. What is the difference? Mike Tuller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
Hi! On Jan 13, Joe Rhett wrote: Not a bug. In the manual, section Upgrading from Version 3.23 to 4.0, there is * To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables, you need to rebuild them with `REPAIR TABLE table_name USE_FRM'. Based on a guess, or did you analyze the data file I sent? Based on your data. There is a ft_dump utility program that comes from source distribution. I did ft_dump -d Notices 0|grep '\\(control\|pollution\)\' log then I noticed that entries are ordered by weight, not by rowid. It is what was changed in 4.0 to make boolean search to work, and it's what is fixed by `REPAIR TABLE table_name USE_FRM' And of course I tried this myself before writing to you :) And if so, may I suggest that the upgrade documentation REALLY needs to be broken into sections? 1. Table changes 2. Privilege changes 3. Configuration changes 4. API/result changes Yes, I agree. Not necesarily to these particular division, but to the fact that our upgrading sections are difficult to follow. I'm forwarding this request to our doc team. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
Based on a guess, or did you analyze the data file I sent? Based on your data. .. .. And of course I tried this myself before writing to you :) Cool. Thanks for the analysis. And if so, may I suggest that the upgrade documentation REALLY needs to be broken into sections? Yes, I agree. Not necesarily to these particular division, but to the fact that our upgrading sections are difficult to follow. I'm forwarding this request to our doc team. I don't really care what divisions, just that it's easier to separate out changes that need to be made to production databases during the upgrade versus changes that need to be made to code using the new system. -- Joe Rhett Chief Geek [EMAIL PROTECTED] Isite Services, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird
On 13 Jan 2004, at 19:49, Chris L. White wrote: Ok I was able to use mysqlcc and as long as I keep the host name localhost it will let me in. But the name of the host should be Napmarilu or the IP number right? When you change it to either one of those it will not let you in. So how would I fix this problem. However on the other hand mysqladmin screen it still only shows the user Administrator and the test database sheets. Any one got any ideas? See the thread entitled mysql database, user table, two root accounts I posted a question about the difference between localhost and 127.0.0.1 Apparently, mysql see's localhost and uses the unix socket. Any other name forces it to use TCP/IP. Perhaps your server doesn't have TCP/IP enabled? (Can you see anything listening on port 3306?) HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User quotas, how?
Hiya all! I'm curious about if there's a simple way to manage user quotas in a MySQL database? F.x. user one can have 10MB data in the database and user two can have a maximum of 5MB? Anders Norrbring -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical records, I need some help!! ;(
On 13 Jan 2004, at 19:11, Jochem van Dieten wrote: I most certainly hope this Oracle idiosyncracy will never make it into MySQL. The SQL standard defines a different syntax for doing recursive queries, using WITH RECURSIVE. I see no reason for MySQL to implement a non-standard way for doing recursive queries when there is a viable alternative (DB2 already uses WITH conform the SQL standard) that has standardized behaviour. I know that a CONNECT BY PRIOR patch is readily available for PostgreSQL but the PostgreSQL developers reject it exactly because it does not follow the SQL standard. I think the PostgreSQL developers are right to do so, non-standard features have caused enough trouble for application portability as it is. Jochem Point taken. I never knew about the WITH RECURSIVE syntax. Certainly, if CONNECT BY PRIOR is an Oracle only extension, then WITH RECURSIVE does sound like the better option of the two. Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weird
Steve, This is on Server 2003 Enterprise Edition. TCP/IP is installed, so I have not a clue. I know it should be using the IP address of the machine or the Machine name instead. Well At least I need it to function that way eventually, cause this is for a client. Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED] See the thread entitled mysql database, user table, two root accounts I posted a question about the difference between localhost and 127.0.0.1 Apparently, mysql see's localhost and uses the unix socket. Any other name forces it to use TCP/IP. Perhaps your server doesn't have TCP/IP enabled? (Can you see anything listening on port 3306?) HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access Denied, How To Fix This
I get the following access denied error: Warning: mysql_pconnect(): Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in C:\Apache\Apache2\html\C2R\catalog\includes\functions\database.php on line 17 Unable to connect to database server! Why Is this happening and how can I fix it? It's been several months since I've done anything with the mysql server, but I do know that I was able to connect perfectly up until a few weeks back. The 192.168.1.102 is the LAN IP address of another computer on my home network. My mysql server is called samserver and I believe it used to log me in as [EMAIL PROTECTED] I'm not sure why it started denying me access. I've unplugged my cable modem and that's all...didn't change any settings through phpadmin or mysql. Why Is this happening and how can I fix it? Please help. Thanks, Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird
Even though Napmarilu and localhost are resolving to the same machine, they are different host names. MySQL security uses a combination of username and hostname in the form of: [EMAIL PROTECTED] That means that [EMAIL PROTECTED] is a different user than [EMAIL PROTECTED] Since you have the Control center up, user the User Administration features to add a user with Napmarilu as it's host. If you want to do this with the command line client, look up Grant in the manual. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL problem
Hi there, I have recently installed mysql and php on my mechine and both work fine. But when i wrote an HTML/Php to access my databse I got the following error message: Fatal error: Call to undefined function: mysql_pconnect() in /var/www/html/employee.php on line 48 I tried also with mysql_real_connect() and mysql_connect() functions bud didn't solve the problem. Does it mean that the mysql_pconnect() doesn't exitst or is some configuration problem? Your help is appriciated. cheers, Bereket L __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL problem
From: Aron Bereket [mailto:[EMAIL PROTECTED] Hi there, I have recently installed mysql and php on my mechine and both work fine. But when i wrote an HTML/Php to access my databse I got the following error message: Fatal error: Call to undefined function: mysql_pconnect() in /var/www/html/employee.php on line 48 I tried also with mysql_real_connect() and mysql_connect() functions bud didn't solve the problem. Does it mean that the mysql_pconnect() doesn't exitst or is some configuration problem? Your help is appriciated. Are you sure the MySQL API was compiled with your install of PHP? Create a test script and just put the following in it: ? phpinfo() ? Then view that in your web browser. Near the top should be a header Configure Command -- does it include an appropriate --with-mysql=xxx argument? If that checks out, scroll further down to view information about PHP's planned interactions with MySQL. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL problem
Thanx for the quick reply peter. That of one of the things I did when I had the problem. It was not enabled. But when I enable it it gave me mysql.so doesn't exits. I checked it on the path there is not mysql.so. cheers, Bereket --- Peter Lovatt [EMAIL PROTECTED] wrote: check your php.ini to make sure the mysql extensions are enabled. Peter -Original Message- From: Aron Bereket [mailto:[EMAIL PROTECTED] Sent: 13 January 2004 23:36 To: [EMAIL PROTECTED] Subject: MYSQL problem Hi there, I have recently installed mysql and php on my mechine and both work fine. But when i wrote an HTML/Php to access my databse I got the following error message: Fatal error: Call to undefined function: mysql_pconnect() in /var/www/html/employee.php on line 48 I tried also with mysql_real_connect() and mysql_connect() functions bud didn't solve the problem. Does it mean that the mysql_pconnect() doesn't exitst or is some configuration problem? Your help is appriciated. cheers, Bereket L __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Peter Lovatt [EMAIL PROTECTED] wrote: check your php.ini to make sure the mysql extensions are enabled. Peter -Original Message- From: Aron Bereket [mailto:[EMAIL PROTECTED] Sent: 13 January 2004 23:36 To: [EMAIL PROTECTED] Subject: MYSQL problem __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL problem
Dear Mike, Thanx for the quick reply. I did what you specified on your rely. There is --with-mysql argument but is not set it is just '--with-mysql' does it mean I have to recompile it again? cheers, Bereket --- Mike Johnson [EMAIL PROTECTED] wrote: From: Aron Bereket [mailto:[EMAIL PROTECTED] Hi there, I have recently installed mysql and php on my mechine and both work fine. But when i wrote an HTML/Php to access my databse I got the following error message: Fatal error: Call to undefined function: mysql_pconnect() in /var/www/html/employee.php on line 48 I tried also with mysql_real_connect() and mysql_connect() functions bud didn't solve the problem. Does it mean that the mysql_pconnect() doesn't exitst or is some configuration problem? Your help is appriciated. Are you sure the MySQL API was compiled with your install of PHP? Create a test script and just put the following in it: ? phpinfo() ? Then view that in your web browser. Near the top should be a header Configure Command -- does it include an appropriate --with-mysql=xxx argument? If that checks out, scroll further down to view information about PHP's planned interactions with MySQL. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installing mySQL on Windows 2000
Are you logged on as Administrator? Is there enough disk space free? Does it generate the same error if you try re-installing? Is there an existing installation with the server still running? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
loading dates
Hi, I've got a csv archive with a date field 15/02/03, how can I load it to a date field with the mysql date format (ISO ? ) 2003-02-15 ? thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Existing with other DBMSes
These are completely different programs so you shouldn't have any problems. I've never run into mention of any conflicts listed in the MySQL docs. You might check the docs of the other applications. I know the MSSQL and MySQL can co-exist. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: loading dates
Hi, I've got a csv archive with a date field 15/02/03, how can I load it to a date field with the mysql date format (ISO ? ) 2003-02-15 ? thank you I wouldn't mind knowing this too, my work around is doing it in php using fgetcsv to extract the csv data, finding the right rows and columns and formatting the date and inserting that way. Like i use preg_split on the 15/02/03 and change it to 2003-02-15, hmm actually dont know how you would format the 03 part to 2003, could be anything :\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: loading dates
Antonio De Luna wrote:
Hi, I've got a csv archive with a date field 15/02/03, how can I load it to a
date field with the mysql date format (ISO ? ) 2003-02-15 ?
Well, if you're a Unix type, then you could pipe the input through a
small Perl script:
#!/usr/bin/perl -w
while ()
{
s/(\d\d)\/(\d\d)\/(\d\d)/20$3-$2-$1/g;
print $_;
}
Note that this assumes the years are all in the 2000's. If you want to
distinguish then you need to know the cutoff for the 1900's and add some
conditionals to the script.
Then if the script is called something like convert_date_format.pl, you
could use:
cat input.csv | convert_date_format.pl | whatever
... whatever being either another filter or mysql (if it's now in the
right format) or some output file.
Probably the Perl buffs in the audience can hack together an even
shorter one-liner that takes into account the Y2K possibilities...
HTH
-Neil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with syntax for mysqldump
Matthew Stuart wrote: mysqldump is run at the system command-line, not within the mysql client environment. This is most likely to be my problem then. I assumed that what is called the system command line to be the mysql client environment. The tutorial book that I have been going through instructs me to issue this command in the start menus run dialogue box: C:\Windows\Desktop cd C:\mysql\bin and from there I have issued all commands in the black window. Is this not the command line, and if not, what is? That is the command line. Open a new Command Prompt in windows (something like Start-Programs-Accessories-Command Prompt) Then type cd c:\mysql assuming that is where you installed MySQL then type : bin\mysqldump -uusername -ppassword dbname csi_db01 c:\csi_db01.sql This will create a file called csi_db01.sql in the base of your C: drive that contains all the SQL needed to recreate your table. Note that the c:\mysql isn't strictly needed - and it would work just as well to type c:\mysq\bin\mysqldump in any folder in your system. I'm sure this functionality definately works, so if you can't make it work then post back to the list and someone will realise what mistake you're making. Andrew [Also - most individuals choose not to disclose address and telephone numbers on public mailing lists because they are so widely distributed - espically popular ones like this. I'd suggest removing that information from your signature. But it's just a suggestion ;) ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing a dumpfile
Right having just got to grips with the mysqldump command, I would like to be able to know how to import the database back in to MySQL should anything happen to my PC. Does mysqlimport have to be done in the command line window like mysqldump, and if so, how? It's just that I tried to import stating terminated, enclosed, escaped, etc and by the time I had come to list the db name to import in to and the path to the file I wish to import, the window wouldn't let me type anymore. Why? Did it get as bored as I did? What syntax do you people out there use? Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting to remote server
Mike Tuller wrote: I have a shell script that is supposed to connect to a remote server running MySql 3.23.53. It comes up with an error ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) The script looks like this: /usr/local/mysql/bin/mysql --user=$username --password=$password cetechnology -e \ That makes sense to me, I don't have permissions set correctly right for the $username (hardware). I know the password is set correctly. Then why am I able to connect to the server in the terminal with: /usr/local/mysql/bin/mysql -h 204.xxx.xxx.xxx -u hardware -p cetechnology And then enter my password. What is the difference? I'm not certain about this one (I'm not totally confident with MySQL's permission system myself). But it seems to me that assuming that the IP that you x'd out in your second example _is_ the IP of the local server in your first example then MySQL is going to treat them incoming connections from two different locations. One is going to be a connection from [EMAIL PROTECTED] the other is a connection from [EMAIL PROTECTED] - I think there's a good chance that MySQL will treat these as entirely different hosts to be GRANTed on despite the fact that they are actually the same physical machine. Therefore - if connections to 204.xxx.xxx.xxx work and connections to localhost don't, you need to GRANT the right permissions to [EMAIL PROTECTED] Have a look over the relevent section of the handbook for how to use GRANT HTH Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: loading dates
I've a second look to de csv file and the format of the date is 09/14/1988, so I think it's easier to load it to the mysql date format, could it be done using mysqlimport, or LOAD DATA INFILE ?? Thanks On Tuesday 13 January 2004 18:24, Antonio De Luna wrote: Hi, I've got a csv archive with a date field 15/02/03, how can I load it to a date field with the mysql date format (ISO ? ) 2003-02-15 ? thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for a tool
Several years ago, I used a cgi based tool that allowed me to upload a csv file to a server and load the data into either a new table or an existing table. Anyone happen to know of one like it now. I can't seem to find it in my archives anymore. I know I can do it with load data infile, but I want the web interface so that I can had the task off to an admin with minimal skills. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL benchmarks
On Tue, 2004-01-13 at 01:58, Prasad Budim Ram wrote: Hi All, Is there any AS3AP benchmark suite readily available for MySQL? Ram, Yes but you're asking it in the wrong place :) It is still not published to the public (we plan to publicly open our Benchmark BitKeeper tree later this month) Ranger could you please send to Ram our current version ? You can also try OSDB - AS3AP implementation in C. P.S For benchmarks issues it is better to use [EMAIL PROTECTED] list which is dedicated for this purpose. -- Peter Zaitsev, Full-Time Developer MySQL AB, www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
