Can't open privilege tables
Using 4.0.20 on Slack 10.0 New setup. mysql_install_db was run. On startup, the error log shows the following error message: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) It would appear the paths are not being set. - Need advice on what to do next. URLs of documentation would be appreciated as well. - When I ran mysql_install_db, part of the message displayed at that time was: To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system And I'm sorry to say, but I don't know what that means. duh Again, advice and clarification of these instructions are welcome. Thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql user name length
Just to make things REALLY messy... try setting the default character set of a 4.1 server to utf8, and then importing your data from 4.0... your mysql usernames are in real trouble now, because utf8 considers itself to be multi byte and takes more space, cutting down on the 16 characters by.. well... let's just say it's painful. Took me some time to figure out why it wasn't accepting my new usernames - very unpleasant. Best Regards, Bruce On Wed, Jul 06, 2005 at 03:46:02PM -0700, Tim Traver wrote: Is there any reason why I shouldn't increase the size of the allowable user names in mysql to var(32) instead of the default var(16) ??? Couldn't really find much on it, but wanted to ask if anyone knows of any troubles this may cause... Yes, there are a number of places within the server that only expect the username to be 16 characters, and will almost certainly break in the face of longer usernames. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Retrieving list of all datatypes from mysql command line
Is there any command to list all the datatypes available on mysql? I can get the information about datatypes from mysql manual as well but I need to get them from mysql command line. Is there any such command available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0 to 4.1 migration and charset problems
On Thursday 07 of July 2005 00:16, Gleb Paharenko wrote: Hello. For a pity, I could give explanations only for your query about selecting @@global.xxx variables. I think server returns correct results, because you're selecting global variables, while character_set_client, character_set_connection, character_set_results are session variables. And with SET NAMES you're setting @@character_xxx variables which are synonym for @@session.character_xxx. I see. In what way have you done your upgrade? If you haven't used mysqldump you could get some problems. Make the dump, and restore it setting the correct connection variables for mysql program. Be aware of that mysqldump could put SET NAMES at the beginning of the dump file. Use set-names=latin2 for it. See: http://dev.mysql.com/doc/mysql/en/mysqldump.html Dump restore was done properly. dump on 4.0, add set names latin2; load into 4.1. The problem is that by default connections from client are as latin1, db is latin2 so servers needs to do conversion from latin2-latin1 which can't be done and thus I'm getting '?' characters instead of latin2 characters. The thing I need is how to force default latin2 in all client connections even if client won't request latin2 by using set names. Tried doing things like in mysqld.conf: init-connect = SET @lchar = IF(@@session.character_set_client = _utf8latin1, @@global.character_set_client, @@session.character_set_client); SET character_set_client = @lchar; SET character_set_results = @lchar; SET character_set_connection = @lchar; but that doesn't work unfortunately from init-connect (works from mysql cmdline client) ;-( -- Arkadiusz MiśkiewiczPLD/Linux Team http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with LEFT JOIN
Hello. Check that MySQL doesn't hang, and that your system doesn't swap. You can connect to MySQL server and check with 'SHOW PROCESSLIST' states of MySQL threads. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html Juan Pablo Espino [EMAIL PROTECTED] wrote: Hello all! I have two tables in my database: results(20 000 rows) and data_lab1(3 000 rows) Both are related by a sample number (sample_id.) I need to find the samples of data_lab1 table that they are not in results table. I think the following query is the solution: SELECT data_lab1.sample_id, results.sample_id FROM data_lab1 LEFT JOIN results ON results.sample_id =3D data_lab1.sample_id WHERE results.sample_id IS NULL=20 But 15 minutes later, it does not return any results and then I stop it. I don't have a lot of experience with MySQL. My system is: PC: Pentium 3, 900 MHz, 512 MB-RAM White Box Linux 3 MySQL v4.0 Something wrong with the query?, another idea?, thanks in advance for any suggestion, regards Juan P. Espino -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables
Hello. [EMAIL PROTECTED] gleb]$ perror 13 OS error code 13: Permission denied Check that you have correct permissions set on MySQL datadir. See: http://dev.mysql.com/doc/mysql/en/mysql-install-db.html Tim Johnson [EMAIL PROTECTED] wrote: Using 4.0.20 on Slack 10.0 New setup. mysql_install_db was run. On startup, the error log shows the following error message: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) It would appear the paths are not being set. - Need advice on what to do next. URLs of documentation would be appreciated as well. - When I ran mysql_install_db, part of the message displayed at that time was: To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system And I'm sorry to say, but I don't know what that means. duh Again, advice and clarification of these instructions are welcome. Thanks tim -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing huge amount of binary data
Hi Everyone, I will be starting a project for which I will need to store about 1 million image files all of which are about 50-100K in size. I will be using Linux for this project. The database won't be very busy, there will be batch image uploads 1-2 times a day, and around 100-200 concurrent users at most, most of which will be reading from the db and writing only session information type of data, etc... And, I don't really need transaction support (InnoDB)... Adding this up, the image data size will be around 50-100 Gb, and I will need to store a total of 1-2 Gb text information (1K for each image) along with each of these images... First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing huge amount of binary data
Sometimes, the easiest way to do this is to use the file system of the linux machine to store the files, and make reference to them in the DB...storing not data in the DB and getting rid of all your possible problems. Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -Original Message- From: Cabbar Duzayak [mailto:[EMAIL PROTECTED] Sent: Saturday, July 09, 2005 12:01 AM To: mysql@lists.mysql.com Subject: Storing huge amount of binary data Hi Everyone, I will be starting a project for which I will need to store about 1 million image files all of which are about 50-100K in size. I will be using Linux for this project. The database won't be very busy, there will be batch image uploads 1-2 times a day, and around 100-200 concurrent users at most, most of which will be reading from the db and writing only session information type of data, etc... And, I don't really need transaction support (InnoDB)... Adding this up, the image data size will be around 50-100 Gb, and I will need to store a total of 1-2 Gb text information (1K for each image) along with each of these images... First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? Thanks... -- 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: Storing huge amount of binary data
Cabbar Duzayak wrote: So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? I would say so, yes. One of my biggest databases holds 50-60million rows, and takes up about 5Gb diskspace. I don't think mysql will have any problems running what you describe. 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? Clustering and replication is more to do with data-availability. You'll probably benefit from using RAID in some form - depends on whether you need reliability or speed. 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? I don't think so, no. /Per Jessen, Zürich -- http://www.spamchek.com/freetrial - managed anti-spam and anti-virus solution. Sign up for your free 30-day trial now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: telnet localhost 3306 Connection refused [SOLVED]
Yep. That was it. No firewall rules needed to change. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 1:24 AM To: mysql@lists.mysql.com Subject: Re: telnet localhost 3306 Connection refused Hello. Are you sure that mysql is running? Is it possible that you have skip_networking in your configuration file? See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Daevid Vincent [EMAIL PROTECTED] wrote: What is causing me to have this problem in mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686). I am running shorewall, but that shouldn't affect localhost should it? My firewall, web, and mySQL server are the same machine. # telnet localhost 3306 Trying 127.0.0.1... telnet: Unable to connect to remote host: Connection refused I've tried to comment, uncomment and change to * this 'bind-address' line in /etc/mysql/my.cnf # keep secure by default! #bind-address= 127.0.0.1 #bind-address= * port= 3306 Of course, I can't connect from any of the other IP addresses that my mySQL server is assigned either: # telnet 192.168.1.1 3306 Trying 192.168.1.1... telnet: Unable to connect to remote host: Connection refused # telnet 10.10.10.1 3306 Trying 10.10.10.1... telnet: Unable to connect to remote host: Connection refused # ifconfig eth1 Link encap:Ethernet inet addr:192.168.1.1 Bcast:192.168.1.255 Mask:255.255.255.0 loLink encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 wlan0 Link encap:Ethernet inet addr:10.10.10.1 Bcast:10.255.255.255 Mask:255.255.255.0 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL .net connector errors
Hi. This is Sinyavin Anatoly from Intel (Moscow department). I attempt to use MySQL benchmark. ( Version 05 July 2005, http://linux.dell.com/blog ). I want to complain of MySQL .net connector. :-) I describe symptom. I launch MySQL benchmark with parameters ds2.exe --target=192.168.9.6 --n_threads=100 and null reference exception in MySQL .net connector occurs. Source code investigation shows that exception rises in CharSetMap class and sometimes in method MySqlDataReader.GetFieldValue(). As is easy to see that benchmark provides multithread environment. I think that this MySQL connector is not thread safe library ... :-( It seems that it is very big problem as there are many multithread data base applications. I hope that criticism helps to make MySQL software to do better. Many thanks, Anatoly Sinyavin Intel EMEA Enterprise SW Enabling, Moscow SED Lab support Tel +7 (095) 721-4900 ext (4744) (291-4744 iNET) Fax +7 (095) 721-4905 http://www.intel.com/ids/emea/
character set in MySQL 4.1
Dear all, I am a Chinese and using Chinese in my MySQL databases. On my old server, the version of MySQL is 3.23.58. And my new MySQL is 4.1.7. On my old server, the MySQL works well with my Chinese contents. However, after I transfer tables to the new server using: mysqldump --opt database | mysql -h 'newserver' database The new server can't display Chinese contents correctly. What need I do? What's more, I can't find gbk.xml and gb2312.xml at /usr/share/mysql/charsets, need I download them from somewhere? Thanks a lot, CB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comments on hot databases migration
I'm looking for comments/suggestions on the best method to migrate multiple databases from one machine to another. It has been requested that both machines be placed into production and that a slow migration occur of services and databases over a period of time. I realize that mysql doesn't support synchronization per sey but has anyone done real time migration in small blocks. I have multiple services accessing the same databases. Although not my choice, they want to leave some services running on the old machine and writing to one db while re-writing code/moving other services. My inclination is to deny this request but I'd like to pool the community for comments. I'm very comfortable shutting down one db, migrating a whole db, re-point services and start the new db. I am just digging for a creative solution. I like creative suggestions/challenges. Thanks in advance, Ross Anderson __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
SQLyog Enterprise
I'm contemplating buying SQLyog Enterprise for $49 dollars (non-commerical) for personal use. Do anyone use it and how does it fare in your opinion? -- Power to people, Linux is here.
possible BUG in 'between' comparisons
Description:
There appears to be a type promotion problem involving sql statements which
include a 'between' comparison and a decimal type field is one of the operands.
How-To-Repeat:
CREATE TABLE `foo1` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo1` (`test1`, `test2`) VALUES ('97.50','154.30');
CREATE TABLE `foo2` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo2` (`test1`, `test2`) VALUES ('154.30','154.30');
CREATE TABLE `foo3` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo3` (`test1`, `test2`) VALUES (97.50,154.30);
CREATE TABLE `foo4` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo4` (`test1`, `test2`) VALUES (154.30,154.30);
select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++---+---+
| test1 | test2 | test1 | test2 |
+---++---+---+
| 97.50 | 154.30 | NULL | NULL |
+---++---+---+
1 row in set (0.00 sec)
select * from foo3 left join foo4 on foo4.test2 between foo3.test1 and
foo3.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
1 row in set (0.00 sec)
mysql select * from foo1 where test1 between 97.50 and 154.30;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
mysql select * from foo1 where 154.30 between test1 and test2;
Empty set (0.00 sec)
mysql select * from foo1 where test2 between 154.30 and test2;
Empty set (0.00 sec)
Fix:
A work around is to include OR conditions that are equal comparisons to the
boundary condition of the BETWEEN. This query demonstrates a work around.
mysql select * from foo1 where test2 between 154.30 and test2 or 154.30=test2;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
Originator:Tom Allen
Organization:
10 East Corp
MySQL support: none
Synopsis: BETWEEN comparisons with one or more DECIMAL type fields as
operands don't handle boundary conditions properly
Severity: [ non-critical | serious | critical ] (one line)
Priority: [ low | medium | high ] (one line)
Category: mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one line)
Release: mysql-4.1.12-max (MySQL Community Edition - Experimental (GPL))
C compiler:2.95.3
C++ compiler: 2.95.3
Environment:
machine, os, target, libraries (multiple lines)
System: Linux docs2 2.6.12 #1 SMP Mon Jun 20 12:08:43 EDT 2005 i686 unknown
unknown GNU/Linux
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-slackware-linux/3.2.3/specs
Configured with: ../gcc-3.2.3/configure --prefix=/usr --enable-shared
--enable-threads=posix --enable-__cxa_atexit --disable-checking --with-gnu-ld
--verbose --target=i486-slackware-linux --host=i486-slackware-linux
Thread model: posix
gcc version 3.2.3
Compilation info: CC='ccache gcc' CFLAGS='-O2 -mpentiumpro' CXX='ccache gcc'
CXXFLAGS='-O2 -mpentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Apr 16 2004 /lib/libc.so.6 -
libc-2.3.2.so
-rwxr-xr-x1 root root 1458907 May 18 2003 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2467548 May 18 2003 /usr/lib/libc.a
-rw-r--r--1 root root 204 May 18 2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Community Edition - Experimental (GPL)'
'--with-extra-charsets=complex' '--with-server-suffix=-max'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-berkeley-db' '--with-big-tables' '--with-raid'
'--with-readline' '--with-embedded-server' '--with-archive-storage-engine'
'--with-blackhole-storage-engine' '--with-ndbcluster'
'--with-example-storage-engine' '--with-innodb' 'CC=ccache gcc' 'CFLAGS=-O2
-mpentiumpro' 'CXXFLAGS=-O2 -mpentiumpro -felide-constructors' 'CXX=ccache gcc'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQLyog Enterprise
Scott Hamm ha scritto: I'm contemplating buying SQLyog Enterprise for $49 dollars (non-commerical) for personal use. Do anyone use it and how does it fare in your opinion? Yes I use it. It's a good sw, little bit better vs phpMyAdmin (IHMO) but only under win :-(. It's a better choice to sync different server (I use it to sync my powerbook, my Zaurus SL6000, my winpc my Linux Server). Paolo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with LEFT JOIN
Juan Pablo Espino [EMAIL PROTECTED] wrote on 07/08/2005 03:37:14 PM: Hello all! I have two tables in my database: results(20 000 rows) and data_lab1(3 000 rows) Both are related by a sample number (sample_id.) I need to find the samples of data_lab1 table that they are not in results table. I think the following query is the solution: SELECT data_lab1.sample_id, results.sample_id FROM data_lab1 LEFT JOIN results ON results.sample_id = data_lab1.sample_id WHERE results.sample_id IS NULL But 15 minutes later, it does not return any results and then I stop it. I don't have a lot of experience with MySQL. My system is: PC: Pentium 3, 900 MHz, 512 MB-RAM White Box Linux 3 MySQL v4.0 Something wrong with the query?, another idea?, thanks in advance for any suggestion, regards Juan P. Espino My suspicion is that you do not have the right indexes to speed this up. Make sure the columns `results`.`sample_id` and `data_lab1`.`sample_id` are the both the FIRST columns in at least one index for each table (Look at the EXPLAIN plan for your query to see which indexes are or are not being used). I have a test machine (a laptop) that has several times as much data as your test but I get results in only a few seconds. show table status; +-++-++- | Name| Engine | Version | Row_format | Rows +-++-++- | report | InnoDB | 9 | Dynamic| 206331 | sample | InnoDB | 9 | Dynamic| 173680 +-++-++- select count(s.id) from sample s left join report r on r.sample_id = s.id where r.sample_id is null; +-+ | count(s.id) | +-+ |1756 | +-+ 1 row in set (2.49 sec) You should be seeing similar response times (or faster). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Ambiguous column names in derived table
Hi, Suppose I have two tables Tbl1 and Tbl2. Both have a column called A. MySQL 5.0.7 rejects this query as ambiguous: SELECT A from Tbl1, Tbl2; But it accepts this and returns the A column from Tbl1: SELECT A FROM (SELECT * FROM Tbl1, Tbl2) AS foo; But this query is rejected again: SELECT * FROM (SELECT * FROM Tbl1, Tbl2) AS foo ORDER BY A; Why is this? Is it a bug? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0 to 4.1 migration and charset problems
Hello. I've tested your solution. It doesn't work for users which have SUPER privilege. This mentioned at: http://dev.mysql.com/doc/mysql/en/server-system-variables.html However, it works with with ordinary users which don't have SUPER privilege. Here are pieces of my my.cnf (the init_connect is one big string without line breaks): [client] default_character_set=latin1 [mysqld] default_character_set=latin2 init_connect='SET @lchar = IF(@@session.character_set_client = _utf8latin1, @@global.character_set_client, @@session.character_set_client); set @@[EMAIL PROTECTED]; set @@[EMAIL PROTECTED]; set @@[EMAIL PROTECTED]; ' When root user connects init_connect doesn't execute and we see: mysql show variables like '%char%'; +--+---+ | Variable_name| Value | +--+---+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin2 | | character_set_results| latin1 | | character_set_server | latin2 | | character_set_system | utf8 | | When user without SUPER privilege connects we see: | Variable_name| Value | +--+---+ | character_set_client | latin2 | | character_set_connection | latin2 | | character_set_database | latin2 | | character_set_results| latin2 | | character_set_server | latin2 | | character_set_system | utf8 | So it works for me. http://dev.mysql.com/doc/mysql/en/mysqldump.html Dump restore was done properly. dump on 4.0, add set names latin2; load i= nto=20 4.1. The problem is that by default connections from client are as latin1, = db=20 is latin2 so servers needs to do conversion from latin2-latin1 which can't= =20 be done and thus I'm getting '?' characters instead of latin2 characters. The thing I need is how to force default latin2 in all client connections e= ven=20 if client won't request latin2 by using set names. Tried doing things like in mysqld.conf: init-connect =3D SET @lchar =3D IF(@@session.character_set_client =3D _utf8= latin1,=20 @@global.character_set_client, @@session.character_set_client); SET=20 character_set_client =3D @lchar; SET character_set_results =3D @lchar; SET= =20 character_set_connection =3D @lchar; but that doesn't work unfortunately from init-connect (works from mysql=20 cmdline client) ;-( =2D-=20 Arkadiusz Mi=B6kiewiczPLD/Linux Team http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with LEFT JOIN
Thanks to all, As you says me, the solution was the indexes. I didn't have an index in results.sample_id. Now the query returns succesfully in a few seconds, regards Juan P. Espino On 7/11/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Juan Pablo Espino [EMAIL PROTECTED] wrote on 07/08/2005 03:37:14 PM: Hello all! I have two tables in my database: results(20 000 rows) and data_lab1(3 000 rows) Both are related by a sample number (sample_id.) I need to find the samples of data_lab1 table that they are not in results table. I think the following query is the solution: SELECT data_lab1.sample_id, results.sample_id FROM data_lab1 LEFT JOIN results ON results.sample_id = data_lab1.sample_id WHERE results.sample_id IS NULL But 15 minutes later, it does not return any results and then I stop it. I don't have a lot of experience with MySQL. My system is: PC: Pentium 3, 900 MHz, 512 MB-RAM White Box Linux 3 MySQL v4.0 Something wrong with the query?, another idea?, thanks in advance for any suggestion, regards Juan P. Espino My suspicion is that you do not have the right indexes to speed this up. Make sure the columns `results`.`sample_id` and `data_lab1`.`sample_id` are the both the FIRST columns in at least one index for each table (Look at the EXPLAIN plan for your query to see which indexes are or are not being used). I have a test machine (a laptop) that has several times as much data as your test but I get results in only a few seconds. show table status; +-++-++- | Name| Engine | Version | Row_format | Rows +-++-++- | report | InnoDB | 9 | Dynamic| 206331 | sample | InnoDB | 9 | Dynamic| 173680 +-++-++- select count(s.id) from sample s left join report r on r.sample_id = s.id where r.sample_id is null; +-+ | count(s.id) | +-+ |1756 | +-+ 1 row in set (2.49 sec) You should be seeing similar response times (or faster). Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character set in MySQL 4.1
Hello. First read: http://dev.mysql.com/doc/mysql/en/charset.html mysqldump could put SET NAMES 'utf8' at the beginning of the dump file, check it and remove or perform a dump using --set-names=gbk. Use --defaults-character-set=gbk for mysql client (or put correct values for character_set_xxx variables in configuration file). Use show variables like '%char%'; to debug this issue. What's more, I can't find gbk.xml and gb2312.xml at /usr/share/mysql/charsets, need I download them from somewhere? Probably it is a compiled in character set. See README file in charsets directory. Chenzhou Cui [EMAIL PROTECTED] wrote: Dear all, I am a Chinese and using Chinese in my MySQL databases. On my old server, the version of MySQL is 3.23.58. And my new MySQL is 4.1.7. On my old server, the MySQL works well with my Chinese contents. However, after I transfer tables to the new server using: mysqldump --opt database | mysql -h 'newserver' database The new server can't display Chinese contents correctly. What need I do? What's more, I can't find gbk.xml and gb2312.xml at /usr/share/mysql/charsets, need I download them from somewhere? Thanks a lot, CB -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL .net connector errors
Hello. Please, could you provide more information about versions of MySQL Server, .NET connector and operating system which you have used. You could report a bug at: http://bugs.mysql.com Specific list related to MySQL and .NET exists: http://lists.mysql.com/dotnet This is Sinyavin Anatoly from Intel (Moscow department). I attempt to use MySQL benchmark. ( Version 05 July 2005, http://linux.dell.com/blog ). I want to complain of MySQL .net connector. :-) I describe symptom. I launch MySQL benchmark with parameters ds2.exe --target=192.168.9.6 --n_threads=100 and null reference exception in MySQL .net connector occurs. Source code investigation shows that exception rises in CharSetMap class and sometimes in method MySqlDataReader.GetFieldValue(). As is easy to see that benchmark provides multithread environment. I think that this MySQL connector is not thread safe library . .. :-( It seems that it is very big problem as there are many multithread data base applicatio ns. Sinyavin, Anatoly [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables/mysql.sock
* Gleb Paharenko [EMAIL PROTECTED] [050711 07:17]: Hello Gleb: Check that you have correct permissions set on MySQL datadir. See: http://dev.mysql.com/doc/mysql/en/mysql-install-db.html I will add to the comments above. I had to set the owner and group to mysql as per my redhat setup. More problems tho: == When invoking mysql, I get the following error message: Can't connect to local MySQL server through socket at 'var/lib/run/mysql/mysql.sock' hmm! mysql.sock was created at var/lib/mysql. owner=mysql, permissions=777 So again, is there paths/permissions problems? The following is in /etc/my.cnf -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - Thank you for your help so far. Further support greatly appeciated. :-) Further pointers to docs invited! Regards Tim Using 4.0.20 on Slack 10.0 New setup. mysql_install_db was run. On startup, the error log shows the following error message: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) It would appear the paths are not being set. - Need advice on what to do next. URLs of documentation would be appreciated as well. - When I ran mysql_install_db, part of the message displayed at that time was: To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system And I'm sorry to say, but I don't know what that means. duh Again, advice and clarification of these instructions are welcome. Thanks tim -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SET FOREIGN_KEY_CHECKS=0 being ignored
Hey, I have an application using the C API that is doing a REPLACE command into an innodb table that has other tables with cascading deletes relying on it's entries. Rather than use an UPDATE/Check affected/Insert/Check success/repeat method we have wrapped the REPLACE query in a SET FOREIGN_KEY_CHECKS=0; then after SET FOREIGN_KEY_CHECKS=1; pair of commands. This is working great in our dev and test environments but its been discovered that on our production servers it is apparently having no effect and the cascading deletes are occurring anyway. So, the first thing I thought was 'something must be wrong with the permissions' but I've been unable to find any discrepancies and the manual doesn't seem to indicate you even need any special permissions to execute the set command. Has anyone else experienced anything similar? Does anyone have any ideas what environmental differences could cause the SET FOREIGN_KEY_CHECKS command to be ignored? I'm at my wits end here... any suggestions appreciated. John A. McCaskey
A problem with privileges
Hi everyone, My environment: - Linux 2.6.7-1.7asp #1 Thu Jul 15 17:36:07 YEKST 2004 i686 i386 GNU/Linux - server version: 4.0.13 I wrote a script-SQL like this: #Begin of script-SQL ... DATA_BEGIN=$1 DATA_END=$2 TIME_BEGIN=$3 TIME_END=$4 USER_NAME=$5 PRICE=$6 CUR_TABLE=acc_cur TMP_TABLE=acc_tmp mysql -h 198.168.68.1 -u info blg TTT2 DROP TABLE IF EXISTS $TMP_TABLE; CREATE TABLE $TMP_TABLE SELECT cur_date,cur_time,traffic FROM $CUR_TABLE LIMIT 1; DELETE FROM $TMP_TABLE; INSERT INTO $TMP_TABLE SELECT cur_date,cur_time,count(*) FROM $CUR_TABLE WHERE user_name = '$USER_NAME' AND cur_date = '$DATA_BEGIN' AND cur_date '$DATA_END' AND cur_time = '$TIME_BEGIN' AND cur_time = '$TIME_END' GROUP BY cur_date,cur_time; SELECT (count(*)*($PRICE)/60) FROM $TMP_TABLE; DROP TABLE $TMP_TABLE; TTT2 #End of script-SQL When I grant privileges for user 'info' like this: +--+ | Grants for [EMAIL PROTECTED]/255.255.255.0 | +--+ | GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'info'@'198.168.68.0/255.255.255.0' | | GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `blg`.* TO 'info'@'198.168.68.0/255.255.255.0' | +--+ The script-SQL, printed above, execute Ok. When I grant privileges for user 'info' like this: +--+ | Grants for [EMAIL PROTECTED]/255.255.255.0 | +--+ | GRANT USAGE ON *.* TO 'info'@'198.168.68.0/255.255.255.0'| | GRANT SELECT ON `blg`.* TO 'info'@'198.168.68.0/255.255.255.0' | | GRANT INSERT, DELETE, CREATE, DROP ON `blg`.`acc_tmp` TO 'info'@'198.168.68.0/255.255.255.0' | +--+ When I tried to execute the script-SQL, I get error: ERROR 1142 (0) at line 2: drop command denied to user: '[EMAIL PROTECTED]' for table 'acc_tmp' Help me, pls. Many thanks Vitalij -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET FOREIGN_KEY_CHECKS=0 being ignored
Oh, I should also mention we have binary logging on and I verified by looking at the binary log that the commands are being excuted and logged with an error code of 0. So the obvious thought of the codes broken and not running them is unfortunately not the problem. It seems to be some sort of legitimate mysql setup error on our part or a bug in mysql. John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Monday, July 11, 2005 9:46 AM To: mysql@lists.mysql.com Subject: SET FOREIGN_KEY_CHECKS=0 being ignored Hey, I have an application using the C API that is doing a REPLACE command into an innodb table that has other tables with cascading deletes relying on it's entries. Rather than use an UPDATE/Check affected/Insert/Check success/repeat method we have wrapped the REPLACE query in a SET FOREIGN_KEY_CHECKS=0; then after SET FOREIGN_KEY_CHECKS=1; pair of commands. This is working great in our dev and test environments but its been discovered that on our production servers it is apparently having no effect and the cascading deletes are occurring anyway. So, the first thing I thought was 'something must be wrong with the permissions' but I've been unable to find any discrepancies and the manual doesn't seem to indicate you even need any special permissions to execute the set command. Has anyone else experienced anything similar? Does anyone have any ideas what environmental differences could cause the SET FOREIGN_KEY_CHECKS command to be ignored? I'm at my wits end here... any suggestions appreciated. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables/mysql.sock
Tim Johnson wrote: * Gleb Paharenko [EMAIL PROTECTED] [050711 07:17]: Hello Gleb: Check that you have correct permissions set on MySQL datadir. See: http://dev.mysql.com/doc/mysql/en/mysql-install-db.html I will add to the comments above. I had to set the owner and group to mysql as per my redhat setup. More problems tho: == When invoking mysql, I get the following error message: Can't connect to local MySQL server through socket at 'var/lib/run/mysql/mysql.sock' hmm! mysql.sock was created at var/lib/mysql. owner=mysql, permissions=777 So again, is there paths/permissions problems? Since this is where your my.cnf says to put it, it would be a problem if it was not there. If you are going to override the default location of the socket for the server, you will also need to add an entry for the client. [mysql] socket=/var/lib/mysql/mysql.sock The following is in /etc/my.cnf -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - Thank you for your help so far. Further support greatly appeciated. :-) Further pointers to docs invited! Regards Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql cygwin
Warren Young wrote: I believe people have gotten MySQL to build under Cygwin, but you should be aware of another option: Cygwin's build system can link against Microsoft C libraries, so you could just use the regular Win32 binary distribution and link your program against that. That sounded like a great idea. Here are my results: I first tried the system documented in the Cygwin users guide: nm libmysql.dll grep ’ T _’ | sed ’s/.* T _//’ foo.def but nm didn't find any symbols. I found a link that described the following which worked better: pexports libmysql.dll libmySQL.def dlltool --input-def libmySQL.def --dllname libmysql.dll --output-lib libmysql.a -k Now I have a library that works to compile against, and I can even perform queries, but there are two problems: 1. The pexports did not find _mysql_server_init so I cannot initialize (or finalize) the library calls. 2. The programs compiled crash in the call to mysql_close(sock) at the end of my routine. If this isn't the best way, what is the recommended wayt to use mysql with g++/gcc and cygwin? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables/mysql.sock
hmm! mysql.sock was created at var/lib/mysql. owner=mysql, permissions=777 So again, is there paths/permissions problems? Since this is where your my.cnf says to put it, it would be a problem if it was not there. If you are going to override the default location of the socket for the server, you will also need to add an entry for the client. [mysql] socket=/var/lib/mysql/mysql.sock ... of course ... duh That's what I needed. (the my.cnf was copied from a RH 9.0 partition with mysql 3.23.58) Well done! thanks tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0 to 4.1 migration and charset problems
On Monday 11 of July 2005 16:24, Gleb Paharenko wrote: Hello. I've tested your solution. It doesn't work for users which have SUPER privilege. This mentioned at: http://dev.mysql.com/doc/mysql/en/server-system-variables.html However, it works with with ordinary users which don't have SUPER privilege. Here are pieces of my my.cnf (the init_connect is one big string without line breaks): [client] default_character_set=latin1 [mysqld] default_character_set=latin2 init_connect='SET @lchar = IF(@@session.character_set_client = _utf8latin1, @@global.character_set_client, @@session.character_set_client); set @@[EMAIL PROTECTED]; set @@[EMAIL PROTECTED]; set @@[EMAIL PROTECTED]; ' [...] So it works for me. I've ended doing this in a different way. I've created a patch which forces a file to be read - /etc/mysql/mysql-client.conf (which is the same as .my.cnf) at mysql_init() time. All clients that use libmysqlclient.so should read it now. http://cvs.pld-linux.org/cgi-bin/cvsweb/SOURCES/mysql-client-config.patch?rev=1.1 Now I can put defaults in that global config: [EMAIL PROTECTED] ~]$ more /etc/mysql/mysql-client.conf [client] default-character-set=latin2 Now all my clients connect with latin2 as default. Any possible problems with this approach? -- Arkadiusz MiśkiewiczPLD/Linux Team http://www.t17.ds.pwr.wroc.pl/~misiek/ http://ftp.pld-linux.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tracing connections to mysql.
Howdy Folks, I have an app that cannot get information from mysql database for some reason. Here is an query which application is trying to run but after some timeout (something about 2-3 minutes): 11 Jul 2005 03:32:18,485 DEBUG [Thread-20] (PressReleaseDAO.java:328) - sqlselect prd.press_release_id, pr.start_date, prd.attention_title, prd.headline, prd.sub_headline, prd.summary, prd.company_name, prd.body, prd.city, prd.state, ind.industry_id, ind.industry_name from press_release_detail prd, press_release pr, industry ind where pr.active_flag = 'Y' and pr.press_release_id = prd.press_release_id and prd.industry = ind.industry_id and start_date = date_add(current_timestamp(), INTERVAL 3 HOUR) order by pr.start_date desc Then errors follows: 11 Jul 2005 03:38:24,125 ERROR [Thread-18] (PressReleaseDAO.java:357) - SQLException:java.sql.SQLException: Communication link failure: java.net.SocketException 11 Jul 2005 03:38:24,126 ERROR [Thread-18] (BaseAction.java:75) - com.flierwire.common.FlierwireSystemException: A system error has occurred, please send an email to support at flierwire.com with details of what occurred. I've tried to run this request in mysql and it has been ran fine. After that I've increased max_connections limit in /etc/my.cnf and app runs fine. Is there a way to determin what connectiions to MySQL are being used by whom? Thanks, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET FOREIGN_KEY_CHECKS=0 being ignored
Ok, So I recreated a brand new user in our production server and it now works correctly. However, I still have no idea why the old user did not work. Here is the permission info for the old user: mysql show grants for 'scopeuser'@'10.254.%'; +--- --+ | Grants for [EMAIL PROTECTED] | +--- --+ | GRANT ALL PRIVILEGES ON *.* TO 'scopeuser'@'10.254.%' IDENTIFIED BY PASSWORD '2326f23b5ff9232' | +--- --+ 1 row in set (0.00 sec) mysql So it looks like this is some sort of bug with the user being corrupted somehow and permissions checks causing the SET FOREIGN_KEY_CHECKS=0 to not work but still return success? Seems like a mysql bug then right? John A. McCaskey -Original Message- From: John McCaskey Sent: Monday, July 11, 2005 9:51 AM To: John McCaskey; mysql@lists.mysql.com Subject: RE: SET FOREIGN_KEY_CHECKS=0 being ignored Oh, I should also mention we have binary logging on and I verified by looking at the binary log that the commands are being excuted and logged with an error code of 0. So the obvious thought of the codes broken and not running them is unfortunately not the problem. It seems to be some sort of legitimate mysql setup error on our part or a bug in mysql. John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Monday, July 11, 2005 9:46 AM To: mysql@lists.mysql.com Subject: SET FOREIGN_KEY_CHECKS=0 being ignored Hey, I have an application using the C API that is doing a REPLACE command into an innodb table that has other tables with cascading deletes relying on it's entries. Rather than use an UPDATE/Check affected/Insert/Check success/repeat method we have wrapped the REPLACE query in a SET FOREIGN_KEY_CHECKS=0; then after SET FOREIGN_KEY_CHECKS=1; pair of commands. This is working great in our dev and test environments but its been discovered that on our production servers it is apparently having no effect and the cascading deletes are occurring anyway. So, the first thing I thought was 'something must be wrong with the permissions' but I've been unable to find any discrepancies and the manual doesn't seem to indicate you even need any special permissions to execute the set command. Has anyone else experienced anything similar? Does anyone have any ideas what environmental differences could cause the SET FOREIGN_KEY_CHECKS command to be ignored? I'm at my wits end here... any suggestions appreciated. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Generating Soccer Standings
First, I have spent hours searching the web and the list archives and can't find anything helpful. Second, I'm using MySql 4.0.24 Third, I'm still a novice at query formulation, so be kind. The Problem--- I'm working with a new youth soccer league. I need to generate weekly standings that includes total wins, losses and draws for each team. Additionally, I also need to calculate the standings. In most soccer leagues, including this one, a winning team receives 3 points, a tie gets 1 point and a loss get 0 points. The pertinent table structure follows: CREATE TABLE `games` ( `id` int(11) NOT NULL auto_increment, `hcoach` varchar(20) NOT NULL default '', `vcoach` varchar(20) NOT NULL default '', `hscore` tinyint(4) default NULL, `vscore` tinyint(4) default NULL, `hpts` tinyint(4) default NULL, `vpts` tinyint(4) default NULL, PRIMARY KEY (`id`), KEY `hscore` (`hscore`,`vscore`,`hpts`,`vpts`) ) TYPE=MyISAM AUTO_INCREMENT=4162 ; I located the following query. Unfortunately, it uses subselects which aren't available in 4.0.24 SELECT team, SUM( wins ) , SUM( losses ) FROM ( ( SELECT hcoach AS team, SUM( IF ( hscore vscore, 1, 0 ) ) AS wins, SUM( IF ( vscore hscore, 1, 0 ) ) AS losses FROM games GROUP BY team ) UNION ( SELECT vcoach AS team, SUM( IF ( vscore hscore, 1, 0 ) ) AS wins, SUM( IF ( hscore vscore, 1, 0 ) ) AS losses FROM games GROUP BY team ) GROUP BY team ORDER BY losses I would think this would be a fairly common issue. However, I don't know enough to even know where to start looking. How do I accomplish this? Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET FOREIGN_KEY_CHECKS=0 being ignored
Some additional info would probably be helpful: What version of mysql are you running? On what platform? For the sake of comparison, what does SHOW GRANTS say for your brand new user? Finally, did you 'SELECT CURRENT_USER()' to verify that you were logged in as 'scopeuser'@'10.254.%' when it didn't work? Michael John McCaskey wrote: Ok, So I recreated a brand new user in our production server and it now works correctly. However, I still have no idea why the old user did not work. Here is the permission info for the old user: mysql show grants for 'scopeuser'@'10.254.%'; +--- --+ | Grants for [EMAIL PROTECTED] | +--- --+ | GRANT ALL PRIVILEGES ON *.* TO 'scopeuser'@'10.254.%' IDENTIFIED BY PASSWORD '2326f23b5ff9232' | +--- --+ 1 row in set (0.00 sec) mysql So it looks like this is some sort of bug with the user being corrupted somehow and permissions checks causing the SET FOREIGN_KEY_CHECKS=0 to not work but still return success? Seems like a mysql bug then right? John A. McCaskey -Original Message- From: John McCaskey Sent: Monday, July 11, 2005 9:51 AM To: John McCaskey; mysql@lists.mysql.com Subject: RE: SET FOREIGN_KEY_CHECKS=0 being ignored Oh, I should also mention we have binary logging on and I verified by looking at the binary log that the commands are being excuted and logged with an error code of 0. So the obvious thought of the codes broken and not running them is unfortunately not the problem. It seems to be some sort of legitimate mysql setup error on our part or a bug in mysql. John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Monday, July 11, 2005 9:46 AM To: mysql@lists.mysql.com Subject: SET FOREIGN_KEY_CHECKS=0 being ignored Hey, I have an application using the C API that is doing a REPLACE command into an innodb table that has other tables with cascading deletes relying on it's entries. Rather than use an UPDATE/Check affected/Insert/Check success/repeat method we have wrapped the REPLACE query in a SET FOREIGN_KEY_CHECKS=0; then after SET FOREIGN_KEY_CHECKS=1; pair of commands. This is working great in our dev and test environments but its been discovered that on our production servers it is apparently having no effect and the cascading deletes are occurring anyway. So, the first thing I thought was 'something must be wrong with the permissions' but I've been unable to find any discrepancies and the manual doesn't seem to indicate you even need any special permissions to execute the set command. Has anyone else experienced anything similar? Does anyone have any ideas what environmental differences could cause the SET FOREIGN_KEY_CHECKS command to be ignored? I'm at my wits end here... any suggestions appreciated. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET FOREIGN_KEY_CHECKS=0 being ignored
See comments inline below... However also note that it now turns out after more testing that the new user is having the same issue some of the time. Sometimes the replace does not cascade other times it does. Every single time in the binary log it properly lists the SET FOREIGN_KEY_CONSTRAINTS as succeeding right before the replace though. John A. McCaskey -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, July 11, 2005 11:42 AM To: John McCaskey Cc: mysql@lists.mysql.com Subject: Re: SET FOREIGN_KEY_CHECKS=0 being ignored Some additional info would probably be helpful: What version of mysql are you running? 4.0.18 On what platform? Debian Linux (2.4 kernel) For the sake of comparison, what does SHOW GRANTS say for your brand new user? +--- -+ | Grants for [EMAIL PROTECTED] | +--- -+ | GRANT USAGE ON *.* TO 'proxyuser'@'10.%' IDENTIFIED BY PASSWORD '12345273123400f' | | GRANT ALL PRIVILEGES ON `IPS_config`.* TO 'proxyuser'@'10.%' | | GRANT ALL PRIVILEGES ON `IPS_data`.* TO 'proxyuser'@'10.%' | +--- -+ 3 rows in set (0.00 sec) Finally, did you 'SELECT CURRENT_USER()' to verify that you were logged in as 'scopeuser'@'10.254.%' when it didn't work? These are being called from a c program using the c_api, so no. However I do use show processlist to see the connections and can verify there that they are connected as the correct user. Michael John McCaskey wrote: Ok, So I recreated a brand new user in our production server and it now works correctly. However, I still have no idea why the old user did not work. Here is the permission info for the old user: mysql show grants for 'scopeuser'@'10.254.%'; +--- --+ | Grants for [EMAIL PROTECTED] | +--- --+ | GRANT ALL PRIVILEGES ON *.* TO 'scopeuser'@'10.254.%' IDENTIFIED BY PASSWORD '2326f23b5ff9232' | +--- --+ 1 row in set (0.00 sec) mysql So it looks like this is some sort of bug with the user being corrupted somehow and permissions checks causing the SET FOREIGN_KEY_CHECKS=0 to not work but still return success? Seems like a mysql bug then right? John A. McCaskey -Original Message- From: John McCaskey Sent: Monday, July 11, 2005 9:51 AM To: John McCaskey; mysql@lists.mysql.com Subject: RE: SET FOREIGN_KEY_CHECKS=0 being ignored Oh, I should also mention we have binary logging on and I verified by looking at the binary log that the commands are being excuted and logged with an error code of 0. So the obvious thought of the codes broken and not running them is unfortunately not the problem. It seems to be some sort of legitimate mysql setup error on our part or a bug in mysql. John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Monday, July 11, 2005 9:46 AM To: mysql@lists.mysql.com Subject: SET FOREIGN_KEY_CHECKS=0 being ignored Hey, I have an application using the C API that is doing a REPLACE command into an innodb table that has other tables with cascading deletes relying on it's entries. Rather than use an UPDATE/Check affected/Insert/Check success/repeat method we have wrapped the REPLACE query in a SET FOREIGN_KEY_CHECKS=0; then after SET FOREIGN_KEY_CHECKS=1; pair of commands. This is working great in our dev and test environments but its been discovered that on our production servers it is apparently having no effect and the cascading deletes are occurring anyway. So, the first thing I thought was 'something must be wrong with the permissions' but I've been unable to find any discrepancies and the manual doesn't seem to indicate you even need any special permissions to execute the set command. Has anyone else experienced anything similar? Does anyone have any ideas what environmental differences could cause the SET FOREIGN_KEY_CHECKS command to be ignored? I'm at my wits end here... any suggestions appreciated. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generating Soccer Standings
Albert Padley [EMAIL PROTECTED] wrote on 07/11/2005 02:30:51 PM: First, I have spent hours searching the web and the list archives and can't find anything helpful. Second, I'm using MySql 4.0.24 Third, I'm still a novice at query formulation, so be kind. The Problem--- I'm working with a new youth soccer league. I need to generate weekly standings that includes total wins, losses and draws for each team. Additionally, I also need to calculate the standings. In most soccer leagues, including this one, a winning team receives 3 points, a tie gets 1 point and a loss get 0 points. The pertinent table structure follows: CREATE TABLE `games` ( `id` int(11) NOT NULL auto_increment, `hcoach` varchar(20) NOT NULL default '', `vcoach` varchar(20) NOT NULL default '', `hscore` tinyint(4) default NULL, `vscore` tinyint(4) default NULL, `hpts` tinyint(4) default NULL, `vpts` tinyint(4) default NULL, PRIMARY KEY (`id`), KEY `hscore` (`hscore`,`vscore`,`hpts`,`vpts`) ) TYPE=MyISAM AUTO_INCREMENT=4162 ; I located the following query. Unfortunately, it uses subselects which aren't available in 4.0.24 SELECT team, SUM( wins ) , SUM( losses ) FROM ( ( SELECT hcoach AS team, SUM( IF ( hscore vscore, 1, 0 ) ) AS wins, SUM( IF ( vscore hscore, 1, 0 ) ) AS losses FROM games GROUP BY team ) UNION ( SELECT vcoach AS team, SUM( IF ( vscore hscore, 1, 0 ) ) AS wins, SUM( IF ( hscore vscore, 1, 0 ) ) AS losses FROM games GROUP BY team ) GROUP BY team ORDER BY losses I would think this would be a fairly common issue. However, I don't know enough to even know where to start looking. How do I accomplish this? Thanks. Albert Padley I think one of the easiest ways to approach the statistical analysis is to normalize your `games` table by eliminating the duplicate sets of information. CREATE TABLE gamestats ( `game_id` INT, `home_vis` char(1) default='V', `coach` varchar(20) NOT NULL default '', `points` tinyint(4) NOT NULL default=0, `standingpts` tinyint(4) default=0, Primary Key (`game_id`, `coach`) ); INSERT gamestats (game_id, homevis, coach, points, standingpts) SELECT `id`,'H',`hcoach`, `hscore`, `hpts` FROM `games`; INSERT gamestats (game_id, homevis, coach, points, standingpts) SELECT `id`,'V',`vcoach`, `vscore`, `vpts` FROM `games`; Now it will be much easier to compute the statistics. For example, this query will give you the # of games one, # of games won at home, Total points for season, Avg points per game, and total rank (in descending order) SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home , sum(`points`) as total_points , avg(`points`) as avg_points FROM `gamestats` GROUP BY `coach` ORDER BY `standings` DESC; It became simple because we normalized the data. Here is how to get your original view of the data: SELECT game_id as id , max(if(home_vis='H',coach,null)) as hcoach , max(if(home_vis='V',coadh,null)) as vcoach , max(if(home_vis='H',points,null)) as hscore , max(if(home_vis='V',points,null)) as vscore , max(if(home_vis='H',standingpts,null)) as hpts , max(if(home_vis='V',standingpts,null)) as hpts FROM gamestats GROUP BY game_id; Can you see the patterns? We are creating what is called pivot tables or crosstab queries (depending on who you ask). It's the flexible way of computing the statistics you want to keep. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Ambiguous column names in derived table
Hello. It seems a bug for me. Any columns in the subquery select list must have unique names according to: http://dev.mysq.com/doc/mysql/en/unnamed-views.html I've reported a bug. You could add your comments at: http://bugs.mysql.com/bug.php?id=11864 Richard Cyganiak [EMAIL PROTECTED] wrote: Hi, Suppose I have two tables Tbl1 and Tbl2. Both have a column called A. MySQL 5.0.7 rejects this query as ambiguous: SELECT A from Tbl1, Tbl2; But it accepts this and returns the A column from Tbl1: SELECT A FROM (SELECT * FROM Tbl1, Tbl2) AS foo; But this query is rejected again: SELECT * FROM (SELECT * FROM Tbl1, Tbl2) AS foo ORDER BY A; Why is this? Is it a bug? Thanks, Richard -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tracing connections to mysql.
Hello. Is there a way to determin what connectiions to MySQL are being used by whom? SHOW PROCESSLIST could be helpful. See: http://dev.mysql.com/doc/mysql/en/show-processlist.html [EMAIL PROTECTED] wrote: Howdy Folks, I have an app that cannot get information from mysql database for some reason. Here is an query which application is trying to run but after some timeout (something about 2-3 minutes): 11 Jul 2005 03:32:18,485 DEBUG [Thread-20] (PressReleaseDAO.java:328) - sqlselect prd.press_release_id, pr.start_date, prd.attention_title, prd.headline, prd.sub_headline, prd.summary, prd.company_name, prd.body, prd.city, prd.state, ind.industry_id, ind.industry_name from press_release_detail prd, press_release pr, industry ind where pr.active_flag = 'Y' and pr.press_release_id = prd.press_release_id and prd.industry = ind.industry_id and start_date = date_add(current_timestamp(), INTERVAL 3 HOUR) order by pr.start_date desc Then errors follows: 11 Jul 2005 03:38:24,125 ERROR [Thread-18] (PressReleaseDAO.java:357) - SQLException:java.sql.SQLException: Communication link failure: java.net.SocketException 11 Jul 2005 03:38:24,126 ERROR [Thread-18] (BaseAction.java:75) - com.flierwire.common.FlierwireSystemException: A system error has occurred, please send an email to support at flierwire.com with details of what occurred. I've tried to run this request in mysql and it has been ran fine. After that I've increased max_connections limit in /etc/my.cnf and app runs fine. Is there a way to determin what connectiions to MySQL are being used by whom? Thanks, Todd -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing huge amount of binary data
First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? I don't have much experience with individual tables being that large, though I have used MySQL databases with 100G total data with no problems at all. Based on my experience, it's not the table size which can degrade performance, but the index size - if the index is too large or clumsy, then MySQL can not search quickly enough. If the index is well-structured, the data is found quickly and overall performance is great regardless of the quantity of data. Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). This is a good way to help keep your indexes small and thus keep search/seek time to a minimum :) However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. A suggestion to help keep your indexes on this table small: if you have large text fields that you need to index, you may want to use partial indexes. (for example, if you have a column filename varchar(200), create an index on filename(50), or what ever is appropriate. This will cut the size of the index file a lot, and even though it may result in some duplicate entries in the index, it will probably increase overall performance.) And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? Based on my experience with 100GB InnoDB databases, with the right indexes, MySQL can easily handle 3,000 reads/sec on text/numerical data. (This is on dual xeon 3Ghz, 4 GB RAM, SCSI Raid-5 disks.) I've never had any problem with data corruption, but I use primarily the InnoDB engine, which is not prone to corruption in the same way as MyISAM. I don't have experience storing large amounts of binary data, so I can't say anything about MySQL's performance in that area specifically. 2. Can I implement this using regular SCSI disks with regular mysql? Probably. Or do I have need advanced solutions such as clustered, replicated, etc? No need for clustered or distributed databases, from what you've described. Cluster would be useful if you need 100% availability, even in the event of hardware failures. Replication, such as single master - multiple slaves, is useful if you have massive reads and minimal writes, and _may_ be something you will need. I would recommend using the command SHOW PROCESSLIST, or a tool like MyTop, to see what state the client connections spend the most time in. (searching the index, or sending the data over the network? if it's the latter, then you would benefit from distributing the read load to multiple slave servers.) Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generating Soccer Standings
Shawn, Thank you. I've been working with what you provided learning as I figure out the why of each step. One thing I don't understand is the syntax of these 2 lines: , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home Specifically the =3,1,0 in the first line and the = 'H',1.0 in the second line. Thanks. Albert Padley On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote: SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home , sum(`points`) as total_points , avg(`points`) as avg_points FROM `gamestats` GROUP BY `coach` ORDER BY `standings` DESC; It became simple because we normalized the data. Here is how to get your original view of the data: SELECT game_id as id , max(if(home_vis='H',coach,null)) as hcoach , max(if(home_vis='V',coadh,null)) as vcoach , max(if(home_vis='H',points,null)) as hscore , max(if(home_vis='V',points,null)) as vscore , max(if(home_vis='H',standingpts,null)) as hpts , max(if(home_vis='V',standingpts,null)) as hpts FROM gamestats GROUP BY game_id; Can you see the patterns? We are creating what is called pivot tables or crosstab queries (depending on who you ask). It's the flexible way of computing the statistics you want to keep. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Index question
Howdy all, I've noticed some strange behavior with the way that mysql is choosing indexes with a particular query I'm trying to optimize. First off, I'm using MySQL 4.0.24 on MAC OSX. I've got a table that I'm searching on based upon a set of preferences. From one query to the next the set of preferences may change (as well as the values of the preferences). However, there are a couple of basic preferences that all queries will have, so I created a composite index on that set of preferenes (with the least selective pref being the left most column in the index and getting more restrictive going to the right). I also have another index on the leftmost column mentioned above because that field is a FK and MySQL wouldn't let me use the comp index for the FK. So, there are times when I actually want the single column index to be used and other times the composite key, based on how broad the preferences are. So far so good. I added another index, to see if I could speed things up even more and the query performance took a nose dive (about a factor of 7 worse). When I ran the explain I noticed that mysql changed the index that it was using, but not to the new index (the third one). Adding a fourth index made mysql select the orginal index and performance was restored. My question is, why is mysql choosing differet indexes based on the presence of these new indices (that it chooses not to use in place to the old indices)? I got things back on track by just adding and removing indices until things were working the way that I wanted them to, but it seems really strange. Thanks, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql cygwin
Maclen Marvit wrote: 1. The pexports did not find _mysql_server_init That function is only needed when using the embedded MySQL server. It's probably an optional configuration setting, so it's no big suprise that you don't have that function. If you're connecting to a separate MySQL server, you don't need to call that function. If this isn't the best way, what is the recommended wayt to use mysql with g++/gcc and cygwin? Clearly going with a completely Cygwin-native build is better. For one thing, it will make it easier to debug your crash. I only pointed out the option of linking to the VC++-built library as one option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generating Soccer Standings
OK. I'm responding to my own post since I figured out the syntax. This allowed me to figure out how to compute the ties as well as the wins. How to I compute the losses. I can't use the same technique because I'd be looking for '0' in the standingpts column and that column defaults to '0'. Thus it would also be counting the games that have yet to be played. It would seem to involve simple arithmetic of games - wins - ties = losses, but I can't seem to get the syntax correct. One final question. We get the total goals scored in the season by sum (`points`) as total_points. How would I find the total goals scored against a team in the context of the query below? Thanks. Albert Padley On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote: SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts`=3,1,0)) as wins , sum(if(`standingpts`=3 AND `home_vis` = 'H',1,0) as wins_at_home , sum(`points`) as total_points , avg(`points`) as avg_points FROM `gamestats` GROUP BY `coach` ORDER BY `standings` DESC; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Storing huge amount of binary data
Per Jessen wrote: 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? I don't think so, no. what are you basing this on? SQL Server is a truly great database package, don't let some foolish bias blind you to the fact that it is professional grade software. i like MySQL as much as the next guy, but its being open source does not mean you should ignore the facts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
beginner needs help servin up a lan
Hello there i need to import an entire database or find a way to get all the info in a microsoft access .mdb database into a MySQL database. here is another trick, the access database is on an WinXP computer, the MySQL database is on a debian linux computer. They are on the same internal network though. is this even possible ? if so, where can i read up on how to do it? thanks, shawn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Hi All! I have a MySQL database (I have them using MySql at work for more stuff now!), and the definition is as follows: uid mediumint(6) NOT NULL auto_increment, ym varchar(6) default NULL, fileid varchar(8) default NULL, off char(3) default NULL, PRIMARY KEY (`uid`) TYPE=MyISAM uid is not really of importance for what I need - it is just a unique identifier of records in the database. The variables of importance are: I have YM (which is year/month and looks like this: 200503 for example) and fileid (an 8 digit unique identifier of a person) and off (a three digit identifier which is really just an office number) The ym actually is fiscal and goes from 200404 to 200503. I would like to make a little report showing how many people have changed the office with which they deal. It would be a) transfers into an office or b) transfers out of an office (of course leaving an office implies joining another one). It is possible for people to stop dealing with these offices completely. We just want to know who has transferred to or from offices during this fiscal period. Is it possible to do this with plain vanilla sql? All I can come up with is a loop using php, but I would love to know how to do it with just sql. . . I appreciate any time anyone has to consider this problem! Thanks heaps! -Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: beginner needs help servin up a lan
odbc driver for your MySQL database on your windows machine. export tables in access to odbc source that you created On Mon, 11 Jul 2005, nephish wrote: Hello there i need to import an entire database or find a way to get all the info in a microsoft access .mdb database into a MySQL database. here is another trick, the access database is on an WinXP computer, the MySQL database is on a debian linux computer. They are on the same internal network though. is this even possible ? if so, where can i read up on how to do it? thanks, shawn -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using START SLAVE [SQL_THREAD] UNTIL syntax
[ Note to list admin, you are blocking messages from gmail.com ] MySQL List, I have a large MySQL database (around 20 gigs in total, some tables =3gigs. All tables are MyISAM. We have replication setup to a number of slaves including one dedicated for backups. All are running some semi-recent version of 4.1. As we know: Slaves are fine for distributing SELECT load. Slaves are fine for failing over. Slaves are fine for making backups. Slaves do nothing to help after a destructive query like delete from important_table; This should never happen but developers aren't perfect and don't always test enough in their dev environment so I want a solution for this type of catastrophe. Here's what I've thought up. I wanted the lists thoughts before I code it up. google was of no help. Setup: PRE) Setup a slave and configure skip-slave-start in the my.cnf. Configure the slave with CHANGE MASTER command and bring it up to sync w/ master. STOP SLAVE. System is now ready. 1) start mysql (skip-slave-start) 2) start mysql-slave-keepalive script on local machine. mysql-slave-keepalive script would be (more or less, I'd have more checks/auditing): 1) lynx --source http://private.internal.server/operations/ replication-status [contents $master_log_file:$master_log_pos] 2a) if (values == previous_values || values == 'HALT'); do stop slave; page_operations_team; sleep 30 mins; done; 2b) otherwise... 3) issue 'START SLAVE UNTIL MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=$master_log_pos. sleep 30; goto 1. In our internal admin system there would be an interface for pressing the big red button to HALT slaving which could be issued by any team member at any time. This would, hopefully, give anyone 30 minutes to ensure access to a currently running database that hasn't been destroyed by the nefarious query. Some of my questions: 1) What are the benefits to using relay_log_file and relay_log_pos instead of master_log_file and master_log_pos? that the slave binlogs would already exist locally? Perhaps that's good or bad? thoughts? 2) Has anyone done something like this? 3) If I made it robust and flexible would people be interested in it? 4) Is there a better way? I've tested this all by hand and it seems to work extremely well and be well suited to scripting... In fact, I was surprised I wasn't able to google anything about it. Thanks for all feedback! David Ulevitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
