mysql-5.1 64bit and windows 7
I installed mysql 5.1 to windows 7 using mysql-5.1.73-winx64.msi and got confused on how to relocate the datadir. my basedir was F:/MySQL and datadir was F:/MySQL/data It works fine and I'm trying to relocate the datadir: (0) stop mysql service (1) copy F:/MySQL/data to F:/DBData/MySQL/data (2) modify F:/MySQL/my.ini change the line datadir="F:/MySQL/Data/" into datadir="F:/DBData/MySQL/Data/" (3) restart the service and it says access permission denied even I used cmd window with win admin privileges. I've doe such on XP 32bit mysql many times without any problem. So what wrong above? Thanks a lot
mysql-5.1.67-osx10.6-x86_64.dmg
I downloaded mysql-5.1.67-osx10.6-x86_64.dmg, installed it to my snow leopard book. it works but from the preference pane I see MySQL(32-bit) What I should believe, how do I make sure it's 64bit? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: copy some values between rows
mysql> update student t1, student t2 -> set t1.gpa = t2.gpa -> where t1.id=5 and t2.id=1; Query OK, 1 row affected (0.36 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +++--+ | id | name | gpa | +++--+ | 1 | Bob | 4.0 | | 2 | Ming | 3.8 | | 3 | Ann| 3.8 | | 4 | Howe | 3.8 | | 5 | nobody | 4.0 | +++--+ 5 rows in set (0.00 sec) mysql> On Thu, Aug 23, 2012 at 2:47 PM, Elim Qiu wrote: > Consider two rows with id m and n in a table tb with a column cl, > what's the sql command to set the value of cl in row n the same as > that in row m? > > Below is my testing > > mysql> desc student; > +---+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +---+--+--+-+-++ > | id| int(11) | NO | PRI | NULL| auto_increment | > | name | varchar(32) | YES | | NULL|| > | gpa | decimal(2,1) | YES | | NULL|| > +---+--+--+-+-++ > 3 rows in set (0.00 sec) > > mysql> select * from student; > +++--+ > | id | name | gpa | > +++--+ > | 1 | Bob | 4.0 | > | 2 | Ming | 3.8 | > | 3 | Ann| 3.8 | > | 4 | Howe | 3.8 | > | 5 | nobody | 3.8 | > +++--+ > 5 rows in set (0.00 sec) > > mysql> update student set gpa = (select gpa from student where id=1) where > id=5; > ERROR 1093 (HY000): You can't specify target table 'student' for > update in FROM clause > mysql> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
copy some values between rows
Consider two rows with id m and n in a table tb with a column cl, what's the sql command to set the value of cl in row n the same as that in row m? Below is my testing mysql> desc student; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(32) | YES | | NULL|| | gpa | decimal(2,1) | YES | | NULL|| +---+--+--+-+-++ 3 rows in set (0.00 sec) mysql> select * from student; +++--+ | id | name | gpa | +++--+ | 1 | Bob | 4.0 | | 2 | Ming | 3.8 | | 3 | Ann| 3.8 | | 4 | Howe | 3.8 | | 5 | nobody | 3.8 | +++--+ 5 rows in set (0.00 sec) mysql> update student set gpa = (select gpa from student where id=1) where id=5; ERROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause mysql> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: trouble with perl
I really confussed about this. I don't know whether my mysql is 32bit or 64bit, so I just fresh installed mysql 5.5.27 for osx 10.6 64bit. And I did reinstalled DBI and DBD::mysql make give me a lot of warnings like "warning: format not a string literal and no format arguments" but make test did went through. I can then populate database tables using a perl script ($ perl install.plfrom mwforum package) But when I use browser for the system testing (perl_test.cgi and forum.pl), both report me that DBD:mysql is not installed (or not available) Again, this is on snow leopard 10.6.8. I didn't do anything related perl before. And mysql+php worked quite well here. On Tue, Aug 7, 2012 at 2:24 PM, Perrin Harkins wrote: > Hi, > > This probably means you changed your MySQL libraries but didn't > recompile or reinstall DBD::mysql, Usually this kind of issue is > solved by reinstalling DBD::mysql in the same way that you previously > installed it, so it can build against your current libraries. > > - Perrin > > On Mon, Aug 6, 2012 at 5:42 PM, Elim Qiu wrote: > > To populate table for a perl forum, I got the following error: > > > > aMac:mwf elim$ perl install.pl > > dyld: lazy symbol binding failed: Symbol not found: _mysql_init > > Referenced from: > > > /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle > > Expected in: flat namespace > > > > dyld: Symbol not found: _mysql_init > > Referenced from: > > > /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle > > Expected in: flat namespace > > > > My OS is Mac OS X 10.6.8 64 bit, Apple built-in perl is 5.10.0 (64bit) > and > > MySQL is newly upgraded to 5.5.27 MySQL Community Server (GPL) (64bit) > > > > I don't know how to make DBD:mysql work. Any idea is appreciated > Maybe > > I have to get mysql source and compile/install it with custom options? >
Re: trouble with perl
Thanks Stillman Benjamin. The link is about the mismatch of 32/64 bit among mysql, perl and os processor but I don't have that issues. My system is missing DBD::mysql module, and I still have trouble with that. On Tue, Aug 7, 2012 at 6:49 AM, Stillman, Benjamin < bstill...@limitedbrands.com> wrote: > http://bixsolutions.net/forum/thread-18.html > > > -Original Message- > From: Elim Qiu [mailto:elim@gmail.com] > Sent: Monday, August 06, 2012 5:42 PM > To: mysql@lists.mysql.com > Subject: trouble with perl > > To populate table for a perl forum, I got the following error: > > aMac:mwf elim$ perl install.pl > dyld: lazy symbol binding failed: Symbol not found: _mysql_init > Referenced from: > /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle > Expected in: flat namespace > > dyld: Symbol not found: _mysql_init > Referenced from: > /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle > Expected in: flat namespace > > My OS is Mac OS X 10.6.8 64 bit, Apple built-in perl is 5.10.0 (64bit) and > MySQL is newly upgraded to 5.5.27 MySQL Community Server (GPL) (64bit) > > I don't know how to make DBD:mysql work. Any idea is appreciated Maybe > I have to get mysql source and compile/install it with custom options? > > > > Notice: This communication may contain privileged and/or confidential > information. If you are not the intended recipient, please notify the > sender by email, and immediately delete the message and any attachments > without copying or disclosing them. LBI may, for any reason, intercept, > access, use, and disclose any information that is communicated by or > through, or which is stored on, its networks, applications, services, and > devices. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
trouble with perl
To populate table for a perl forum, I got the following error: aMac:mwf elim$ perl install.pl dyld: lazy symbol binding failed: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace dyld: Symbol not found: _mysql_init Referenced from: /Library/Perl/5.10.0/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle Expected in: flat namespace My OS is Mac OS X 10.6.8 64 bit, Apple built-in perl is 5.10.0 (64bit) and MySQL is newly upgraded to 5.5.27 MySQL Community Server (GPL) (64bit) I don't know how to make DBD:mysql work. Any idea is appreciated Maybe I have to get mysql source and compile/install it with custom options?
Re: manage mysql-bin.xxxxxx files on mac
Thanks a lot to all helped me out. Really appreciated. Rik Wasmus, Thanks for the Binary Log Documentation http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#sysvar_max_binlog_size Rick James, I'll try this: expire_logs_days=7 Vikas Shukla, My server-id is 1 (thought that's the default). I'll try reset master = Here are some finding that I got === (1) What server fail to start, it always says that missing mysql.sock (2) If I comment both 'log-bin=…' and 'binlog_format=…' setting lines in my.cnf, I can actually stop the logging (3) mysql commands PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; Worked very well (4) Got to learn how to use those log files and turn the logging on with some sort of controls. Weekly rotate and/or size limit are good ideas Thank you all again
manage mysql-bin.xxxxxx files on mac
I don't really know how to use those files and so like to know how to stop populating them. or at least control the number and size of them. I'm running os x 10.6.8, mysql Server version: 5.1.61-log MySQL Community Server (GPL) I tried comment out the line "log-bin=mysql-bin" and those mysql-bin.xx files in the data directory. It causes mysql server starting failure. So what's advise here? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql for os x 10.6 (64bit) cannot start service
Looks like 5.1.61 mac installer messed up the privilege setting... But how to fix it? I remember the server account is mysql, not _mysql What do you got? And how to fix it? Thanks On Wed, Feb 29, 2012 at 2:20 PM, Elim Qiu wrote: > I created (or activated) the root account and now I see the error log: > > sh-3.2# cat miniMac.local.err > 120229 10:36:09 mysqld_safe Starting mysqld daemon with databases from > /usr/local/mysql/data > 120229 10:36:09 [Warning] Setting lower_case_table_names=2 because file > system for /usr/local/mysql/data/ is case insensitive > 120229 10:36:09 [Note] Plugin 'FEDERATED' is disabled. > /usr/local/mysql/bin/mysqld: Table 'plugin' is read only > 120229 10:36:09 [ERROR] Can't open the mysql.plugin table. Please run > mysql_upgrade to create it. > 120229 10:36:09 InnoDB: Initializing buffer pool, size = 8.0M > 120229 10:36:09 InnoDB: Completed initialization of buffer pool > InnoDB: The first specified data file ./ibdata1 did not exist: > InnoDB: a new database to be created! > 120229 10:36:09 InnoDB: Setting file ./ibdata1 size to 10 MB > InnoDB: Database physically writes the file full: wait... > 120229 10:36:09 InnoDB: Log file ./ib_logfile0 did not exist: new to be > created > InnoDB: Setting log file ./ib_logfile0 size to 5 MB > InnoDB: Database physically writes the file full: wait... > 120229 10:36:09 InnoDB: Log file ./ib_logfile1 did not exist: new to be > created > InnoDB: Setting log file ./ib_logfile1 size to 5 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Doublewrite buffer not found: creating new > InnoDB: Doublewrite buffer created > InnoDB: Creating foreign key constraint system tables > InnoDB: Foreign key constraint system tables created > 120229 10:36:10 InnoDB: Started; log sequence number 0 0 > 120229 10:36:10 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to > file '/usr/local/mysql/data/miniMac.local.pid' (Errcode: 13) > 120229 10:36:10 [ERROR] Can't start server: can't create PID file: > Permission denied > 120229 10:36:10 mysqld_safe mysqld from pid file > /usr/local/mysql/data/miniMac.local.pid ended > > > On Wed, Feb 29, 2012 at 12:35 PM, Darryle Steplight > wrote: > >> If you are going to use "su" to officially switch to the root users >> just make sure you do "su -" with the dash. >> >> On Wed, Feb 29, 2012 at 2:10 PM, Reindl Harald >> wrote: >> > >> > >> > Am 29.02.2012 19:20, schrieb Larry Martell: >> >> Is the sudo succeeding? If it is, then there's no reason you shouldn't >> >> be able to cd into that dir. If not, then you're going to have to be >> >> able to get root privileges on your own machine. >> >> >> >> Alternatively, you could explicitly set the location of the error log >> >> in your mysql config file (my.cnf) , to a location you can access, >> >> e.g. >> >> >> >> log-error=/tmp/mysqld.log >> >> >> >> put it under [mysqld] and [mysqld_safe] >> > >> > but you would have still NO PERMISSIONS to that logfile >> > because it is owned by mysqld and a normal user has >> > usually no permissions to daemon-logs especially >> > because /tmp has normally 1777 -> everybody can >> > write but after create a file only the owner is >> > allowed to access it >> > >> > why not using "su" to REALLY switch to root? >> > >> >> >> >> -- >> -- >> "May the Source be with you." >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql >> >> >
Re: mysql for os x 10.6 (64bit) cannot start service
I created (or activated) the root account and now I see the error log: sh-3.2# cat miniMac.local.err 120229 10:36:09 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 120229 10:36:09 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 120229 10:36:09 [Note] Plugin 'FEDERATED' is disabled. /usr/local/mysql/bin/mysqld: Table 'plugin' is read only 120229 10:36:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 120229 10:36:09 InnoDB: Initializing buffer pool, size = 8.0M 120229 10:36:09 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 120229 10:36:09 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 120229 10:36:09 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 120229 10:36:09 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 120229 10:36:10 InnoDB: Started; log sequence number 0 0 120229 10:36:10 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/usr/local/mysql/data/miniMac.local.pid' (Errcode: 13) 120229 10:36:10 [ERROR] Can't start server: can't create PID file: Permission denied 120229 10:36:10 mysqld_safe mysqld from pid file /usr/local/mysql/data/miniMac.local.pid ended On Wed, Feb 29, 2012 at 12:35 PM, Darryle Steplight wrote: > If you are going to use "su" to officially switch to the root users > just make sure you do "su -" with the dash. > > On Wed, Feb 29, 2012 at 2:10 PM, Reindl Harald > wrote: > > > > > > Am 29.02.2012 19:20, schrieb Larry Martell: > >> Is the sudo succeeding? If it is, then there's no reason you shouldn't > >> be able to cd into that dir. If not, then you're going to have to be > >> able to get root privileges on your own machine. > >> > >> Alternatively, you could explicitly set the location of the error log > >> in your mysql config file (my.cnf) , to a location you can access, > >> e.g. > >> > >> log-error=/tmp/mysqld.log > >> > >> put it under [mysqld] and [mysqld_safe] > > > > but you would have still NO PERMISSIONS to that logfile > > because it is owned by mysqld and a normal user has > > usually no permissions to daemon-logs especially > > because /tmp has normally 1777 -> everybody can > > write but after create a file only the owner is > > allowed to access it > > > > why not using "su" to REALLY switch to root? > > > > > > -- > -- > "May the Source be with you." > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
Re: mysql for os x 10.6 (64bit) cannot start service
-rw-r--r-- 1 rootwheel 17987 Dec 17 09:01 COPYING -rw-r--r-- 1 rootwheel 7371 Dec 17 09:01 INSTALL-BINARY -rw-r--r-- 1 rootwheel 2552 Dec 17 09:01 README drwxr-xr-x 46 rootwheel 1564 Dec 17 09:01 bin drwxr-x--- 8 _mysql wheel272 Feb 29 10:36 data drwxr-xr-x 4 rootwheel136 Dec 17 09:01 docs drwxr-xr-x 35 rootwheel 1190 Dec 17 09:01 include drwxr-xr-x 22 rootwheel748 Feb 29 10:33 lib drwxr-xr-x 4 rootwheel136 Dec 17 09:01 man drwxr-xr-x 15 rootwheel510 Dec 17 09:01 mysql-test drwxr-xr-x 3 rootwheel102 Dec 17 09:01 scripts drwxr-xr-x 35 rootwheel 1190 Dec 17 09:01 share drwxr-xr-x 29 rootwheel986 Dec 17 09:01 sql-bench drwxr-xr-x 16 rootwheel544 Dec 17 09:01 support-files I found the MySQL document is often difficult to read, but a web search indicates that should in data directory, but I cannot even cd to that directory (sudo is not good enough!) On Wed, Feb 29, 2012 at 10:33 AM, Larry Martell wrote: > On Wed, Feb 29, 2012 at 10:17 AM, Elim Qiu wrote: > > Thanks Larry and Darryle for your help > > > > Where the error log should be? > > http://dev.mysql.com/doc/refman/5.0/en/error-log.html > > > > > > On Wed, Feb 29, 2012 at 8:22 AM, Larry Martell > > wrote: > >> > >> On Wed, Feb 29, 2012 at 7:46 AM, Elim Qiu wrote: > >> > Yes, there is an icon. I can open the preference but the start service > >> > button cannot do the job > >> > >> What is in the mysql error log? > >> > >> > >> > > >> > On Wed, Feb 29, 2012 at 7:20 AM, Darryle Steplight > >> > > >> > wrote: > >> > > >> >> Do you see a MySql icon under System Preferences > Other ? That's > how > >> >> I start MySql on my Mac. > >> >> > >> >> ** > >> >> > >> >> > >> >> > >> >> > > > > >
Re: mysql for os x 10.6 (64bit) cannot start service
Thanks Larry and Darryle for your help Where the error log should be? On Wed, Feb 29, 2012 at 8:22 AM, Larry Martell wrote: > On Wed, Feb 29, 2012 at 7:46 AM, Elim Qiu wrote: > > Yes, there is an icon. I can open the preference but the start service > > button cannot do the job > > What is in the mysql error log? > > > > > > On Wed, Feb 29, 2012 at 7:20 AM, Darryle Steplight > > > wrote: > > > >> Do you see a MySql icon under System Preferences > Other ? That's how > >> I start MySql on my Mac. > >> > >> ** > >> > >> > >> > >> >
Re: mysql for os x 10.6 (64bit) cannot start service
Yes, there is an icon. I can open the preference but the start service button cannot do the job On Wed, Feb 29, 2012 at 7:20 AM, Darryle Steplight wrote: > Do you see a MySql icon under System Preferences > Other ? That's how > I start MySql on my Mac. > > ** > > > >
Re: mysql for os x 10.6 (64bit) cannot start service
The vertion of MySQL that I cannot start is 5.1.61 (the only one for 5.1* mac at mysql.com) On Wed, Feb 29, 2012 at 7:05 AM, Elim Qiu wrote: > *I downloaded **Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive, installed > to my pretty clean os x 10.6.8 (snow leopard) after (1st time) started > apache. The installation went smoothly but the service just cannot be > started.* > > ** > > > >
mysql for os x 10.6 (64bit) cannot start service
*I downloaded **Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive, installed to my pretty clean os x 10.6.8 (snow leopard) after (1st time) started apache. The installation went smoothly but the service just cannot be started.* **
Re: query for twin primes
> How about: > > select t1.pv, t2.pv from prime t1, prime t2 where t2.pv=t1.pv+2 and > t1.oid<100 order by t2.pv > > Mike Very nice and simple. Thanks Mike! Also Thanks Singer X.J. Wang and shawn wilson. best regards, Elim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
query for twin primes
I have a prime table +-+--+-+ | oid | pv | descipt | +-+--+-+ | 1 |2 | NULL| | 2 |3 | NULL| | 3 |5 | NULL| | 4 |7 | NULL| | 5 | 11 | NULL| | .| +-+--+-+ what the query will be if i like to find all rows where pv+2's are also in colum 'pv', where oid < 100? In other words, how to get the list of yonger brother of the twin primes within certain bound? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql shell with utf8 databa
I'm still using win2k mysql 5.1 With default databas encoding, I can do querys with bh English and Chinese GB. But when database encoding is uft8, all the Chinese become question maks. Any idea about how to mysql work unicode databa? Thanks a lot.
Re: query counts of a database
Thanks a lot Edwards! I'm using MySQL 5.1. Your query works great! - Original Message - From: "Rolando Edwards" <[EMAIL PROTECTED]> To: "Elim Qiu" <[EMAIL PROTECTED]>; Sent: Friday, June 13, 2008 10:32 AM Subject: RE: query counts of a database If you are using MySQL 5.0 or later, use the INFORMATION_SCHEMA database. It has an in-memory table of table names called (as you would expect) 'tables'. SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = ''; If you are using a current database then do this: SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = DATABASE(); Or SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = SCHEMA(); Give it a try !!! -Original Message- From: Elim Qiu [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:56 AM To: mysql@lists.mysql.com Subject: query counts of a database I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query counts of a database
Sorry, my email didn't make the intention clear. I want the number of records per table in a database like: querytime | -xx-xx-xx:xx:xx tNm1 | yyy tNm2 | zzz .. where tNm1, tNm2 are table names in the database. - Original Message - From: "Boyd, Todd M." <[EMAIL PROTECTED]> To: "Elim Qiu" <[EMAIL PROTECTED]>; Sent: Friday, June 13, 2008 10:02 AM Subject: RE: query counts of a database > -----Original Message- > From: Elim Qiu [mailto:[EMAIL PROTECTED] > Sent: Friday, June 13, 2008 10:56 AM > To: mysql@lists.mysql.com > Subject: query counts of a database > > I'm looking for a query that reports the count of each table in the > database. > the query should not assume the table list of the database. > > Thanks for any inputs Is "SELECT COUNT(*) FROM *" too open-ended? BTW... do you want the number of records per table, or number of tables per database?? Todd Boyd Web Programmer No virus found in this incoming message. Checked by AVG. Version: 8.0.100 / Virus Database: 270.3.0/1501 - Release Date: 2008-6-13 6:33 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query counts of a database
I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timezone questions
Dear list, I never tried any timezone features(options) of mysql and like to learn from you. (1) In what situation one need to set mysql timezone? (I know there are application level timezone solutions) (2) What the effect if a custome timezone is settled with mysql? I use mysql for web applications and need to deal with timezone at web page generation level. Also I hope my datetime related data in the database be server timezone independent so that the data can be replicated among database servers located in possibly different zones. Thanks -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 2/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.02 on Win2K
Here is where i stuck: I stopped the mysql service, renamed the 5.01 folder(backup), unzip the 5.02 and put it where 5.01 worked. Delete the data folder shipped with 5.02zip and restart the service. Since my data location is outside of the mysql installation location and this is working with my.ini settings for 5.01, I don't see anything else needed and just restart the service. It said the survice started ok and I logged in with mysql client fine. (version 5.02 alpha shown). now the real interaction: mysql> use emailer Database changed mysql>show tables; Now mysql used 100% cpu and the whole system hardly response any commands. Maybe i need to uninstall the service and reinstall the new one? - Original Message - From: "Whil Hentzen" <[EMAIL PROTECTED] To: "Elim Qiu" <[EMAIL PROTECTED] Cc: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.02 on Win2K
Anyone successfully make MySQL 5.02 working on windows2000? I'm using 5.01 (work fine with me) , and tried 5.02 serveral times with no success. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 2/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wanted: back up script
This is on windows 2000.
I did the following as a temp solution for the full back up of a database. I
know this is not safe and possibly
not complete. Any suggestions (how to lock/unlock a db for read here)?
Thanks
#include
#include
int main() {
printf("start backup ESite data\n");
system("tar cf ESite_dt.tar F:/DBData/MySQLdata/ESite");
system("gzip ESite_dt.tar");
system("mv ESite_dt.tar.gz ESite_dt.tgz");
printf("done\n");
return 1;
}
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.6 - Release Date: 12/5/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0.2-alpha-nt
when switch from 5.0.1 to 5.0.2 (without any config /my.ini change) it used 100% cpu for a single command "show tables" (i've 42 tables) and running forever (ate more and more memory) So get back to 5.0.1 and things are fine. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.4.5 - Release Date: 12/3/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help optimize query
Dear list, i have some small tables but for some reason the mysql took very long to find the results. my query looks like below and mysql'e explain is attached for better format. Thanks for your help! select teu.name, eca.owner_id, ece.value fromtyped_enterprise_unit teu, e_contact_association eca, e_contact_entry ece where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) and eca.owner_id > 45 order by eca.owner_id limit 50; mysql> select teu.name, eca.owner_id, ece.value -> -> fromtyped_enterprise_unit teu, -> e_contact_association eca, -> e_contact_entry ece -> -> where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and -> eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) -> -> order by eca.owner_id limit 22; +---+--++ | name | owner_id | value | +---+--++ | Asian Book One| 45 | [EMAIL PROTECTED]| +---+--++ 22 rows in set (4.97 sec) mysql> explain select teu.name, eca.owner_id, ece.value -> -> fromtyped_enterprise_unit teu, -> e_contact_association eca, -> e_contact_entry ece -> -> where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and -> eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) -> -> order by eca.owner_id limit 22; ++-++--+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--+--+-+ | 1 | PRIMARY | eca| ALL | NULL | NULL | NULL| NULL | 2712 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | ece| ALL | NULL | NULL | NULL| NULL | 2669 | Using where | | 1 | PRIMARY | | ALL | NULL | NULL | NULL| NULL | 1440 | Using where | | 2 | DERIVED | tp | ALL | NULL | NULL | NULL| NULL | 100 | | | 2 | DERIVED | eu | ALL | NULL | NULL | NULL| NULL | 1444 | Using where | ++-++--+---+--+-+--+--+-+ 5 rows in set (0.37 sec) No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.3.1 - Release Date: 11/15/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange difference in creating views (mysql 5.0.1)
I'm trying to create a view from two tables address and address_association. So I did the following and expect they are the same: CREATE VIEW associated_address AS select t0.association_id AS association_id, t0.property_dict AS property_dict, t0.type_id AS type_id, t0.owner_id AS owner_id, t0.owner_class_name AS owner_class_name, t0.status_code AS asso_status, t0.flag AS flag, t1.* from address_association t0, address t1 where (t0.address_id = t1.address_id); CREATE VIEW vaa AS select t0.association_id AS association_id, t0.property_dict AS property_dict, t0.type_id AS type_id, t0.owner_id AS owner_id, t0.owner_class_name AS owner_class_name, t0.status_code AS asso_status, t0.flag AS flag, t1.* from address_association t0 left join address t1 on (t0.address_id = t1.address_id); But you see the differences: mysql> select count(1) from vaa; +--+ | count(1) | +--+ | 1443 | +--+ 1 row in set (7.30 sec) mysql> select count(1) from associated_address; +--+ | count(1) | +--+ | 1441 | +--+ 1 row in set (3.32 sec) I have one row in address_association which address_id value not found in table address. Does this cause the above difference?
load data from file: warnings
I want to see the warnings when load data from text file using command mysal> load data local infile 'mydata.txt' into table my_table; When I got Query OK, 1431 rows affected, 1506 warnings (0.27 sec) Records:1431 Deleted: 0 Skipped:0 Warnings:1506 I have to find out what cause the warnings. I found column size is one problem and by fixing one column size, 1400 warnings were gone. Maybe the remaining 1506 warnings are related to column definition or null data in the file etc, but where can i see the warnings themselves? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: incredible performance difference
Cool! Thanks a lot Shawn.
> No, but you can. Modify your scripts so that the word EXPLAIN is the first
> thing in each one then re-execute them. This will product the optimizer's
> execution plan for each query. The results of all of those EXPLAIN
> SELECT statements will give us the most information to work from.
>
> Thanks.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "YL" <[EMAIL PROTECTED]> wrote on 10/19/2004 02:58:35 PM:
>
> > The following are the real tests but not the real logic i'll apply:-)
> >
> > i have 4 very simple script files below and like to show you the
> > performance differece
> >
> > tst0.sql:
> >
> > select t1.participation_id id, t1.owner_id from participation t1 where
> > (t1.participation_id in (24,469)) and
> > (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *314346');
> >
> > tst1.sql:
> >
> > select t2.participation_id from participation t2 where
> > t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *222224';
> >
> > tst2.sql:
> >
> > select t1.participation_id id, t1.owner_id from participation t1 where
> > (t1.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *222224')
> > and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb
> > =[^;]*314346');
> >
> > and finally
> >
> > tst.sql
> >
> > select t1.participation_id id, t1.owner_id from participation t1 where
> > (t1.participation_id in (select t2.participation_id from
> > participation t2 where
> > t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *222224'))
> > and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb
> > =[^;]*314346');
> >
> > Now the performance comparison:
> >
> > mysql> source tst0.sql
> > +-+--+
> > | id | owner_id |
> > +-+--+
> > | 24 |1 |
> > | 469 |4 |
> > +-+--+
> > 2 rows in set (0.02 sec)
> >
> > mysql> source tst1.sql
> > +--+
> > | participation_id |
> > +--+
> > | 24 |
> > | 469 |
> > +--+
> > 2 rows in set (0.02 sec)
> >
> > mysql> source tst2.sql
> > +-+--+
> > | id | owner_id |
> > +-+--+
> > | 24 |1 |
> > | 469 |4 |
> > +-+--+
> > 2 rows in set (0.03 sec)
> >
> > mysql> source tst.sql
> > +-+--+
> > | id | owner_id |
> > +-+--+
> > | 24 |1 |
> > | 469 |4 |
> > +-+--+
> > 2 rows in set (30.45 sec)
> >
> > Basically this seems to me that the sql composite tst.sql is
> > terribly slow than the time needed for separate executions of
> > tst1.sql and tst0.sql. And best of all is tst2.sql.
> >
> > Can someone explain my results?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Chinese
> you can use MySQL-4.1.6 and the UTF-8 character >set for both Chinese and European languages. What's the default character set for MySQL 5.0.1? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to optimize multiple many-to-many relationship related query
I have, to make it clear, 2 many-to-many relationships for table person: PersonPerson_Club ClubPerson_Creditcard CreditCard ----- - - - person_idperson_id club_idperson_id credit_id first_name club_id name credit_id company_name last_name role_id additional_info additional_info additional_info middle_ini additional_info====== gender=== to find people with specified club access and credit access, i have to compose sql involving all tables here. Here 'additional_info' stands for possibly multiple additional columns. Suppose tables person and person_creditcard have large number of rows, and each club has a lot less members than any creditcard company does. What my sql should look like? I'm looking at this: select t0.* from person t0 where (...some conditions within table person...) and t0.person_id in (select t1.person_id from person_creaditcard t1, creditcard t2 where (...some conditoons about t1 and t2) and t1.person_id in (select t3.person_id from person_club t3, club t4 where (...some conditions within t3, t4) ) ) ); I'm hoping in this way, mysql gets smallest possible person_id set from sub query about person_club and club, and then use this to speet up the search on tables person_creditcard and creditcard and finaly speed up the person table search. Any inputs? Thanks a lot
Re: fetch certain number of matches
I have a table got about 20 rows. I want my web application be able to perform search (dynamic query on this table). the search can be quite complex because we need to look at other tables through relationships. So the query may be an expensive one. But the ideal thing is to get the count of the matches and then just fetch, say 25 rows at a time to be displayed on a web page. And my question is, give a query, how to let mysql just return certain number of matches? Of course I can let the web app ignore what not be displayed (hide them, say), but there may be expensive memory usage and cpu usage to do so. - Original Message - From: [EMAIL PROTECTED] To: Elim Qiu Cc: [EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 2:18 PM Subject: Re: fetch certain number of matches Could you possibly describe what you would like to find? Table definitions, sample data, and sample queries (especially ones that you have already tried with explanations of what was wrong with their output) would all help us to answer your question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Elim Qiu" <[EMAIL PROTECTED]> wrote on 10/13/2004 03:55:35 PM: > Is there a way to ask mysql to return certain number of matches? > This is very useful for search functionality.
fetch certain number of matches
Is there a way to ask mysql to return certain number of matches? This is very useful for search functionality.
Re: data with dynamic schema stored in a column as a property list.
Thanks Shawn. I guess your suggestion maybe the only thing I can do about it.
But the problem itself has an interesting background:
I developed an web application handling dynamic online conference registrations;
including
a table BusinessSeason to hold the information about the registration specification
(one
record per event) and a table Participation to hold all the registration records. My
app reads
the registration spec and the registration record (the latter only exist for returning
users) to
generate web pages for user to edit/submit the registration data (preferences for
programs or
lodging etc.)
Records in BusinessSeason are for different events/registrations hence very different
in
terms of reg specification. And the future conference spec can be inserted to the
table and
you see why I just cannot have a fixed schema for the registration data. By using xml
or
plist or any kind of generic data storage, I can store the reg data into the
participation table
along with some standard attributes like event_id, submit_time, reg_id etc.
Now for the management reason, I need to get some statistics from the registration data
and that's why I have to query the column that holds the reg data as xml or plist text.
Things were not too bad as I tried for conferences around 500 people without index the
column. But I should make the database perform better whenver I can.
Thanks again for your help. Can your 2-step query can merge into 1?
Also, just out of curiosity, can oracle do such things? I'm kind of far away from
oracle but
not too long ago I learned there is no way that I can store long text and using sql
query
the text in oracle tables.
- Original Message -
From: [EMAIL PROTECTED]
To: Elim Qiu
Cc: MySQL mailing List
Sent: Friday, October 08, 2004 8:15 AM
Subject: Re: data with dynamic schema stored in a column as a property list.
Have you considered a combination of Full-text indexing (to quickly locate
a subset of records that may match your criteria) and regular expression
matching (to eliminate the non-matching results from the results of the
full-text search)? I know it's two steps but your "data" is practically
opaque to the database engine. The field names and the values you want to
search for exist as content, not as standalone fields or name/value pairs
of columns.
Without somehow converting your data stream into some kind of relational
structure, I think that you will be quite restricted in your searching
options.
Sorry I couldn't be more helpful,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Elim Qiu" <[EMAIL PROTECTED]> wrote on 10/07/2004 11:14:49 PM:
> Hi, instead of xml, i stored arbitrary data of the form
> (the actual usage of such mechanism is for more fancy stuff,
> say, dynamic configuration, otherwise this is really not necessary)
>
> {
> name = "Fn, Ln"; // string value
> gender = F; // single word string
> interests = (reading,"drive fast"); // array
> children = (
> { lastName = Howe; firstName = Sam; gender = M; dob =
> "1994-10-07 16:59:26"; },
> { lastName = Howe; firstName = Ann; gender = F; dob =
> "1998-01-26 04:09:12"; }
> );
> creditCards = {
> visa = "XXX-x";
> master = "YY-";
> };
> }
>
> This is called plist and the depth of the hierarchy can go arbitrary
> deep (unknown limit). And it can be converted back
> and forth from dictionary object by a framework.
>
> My task is to find out ways of querying a column holds such text
> data? say, find out whether there is certain key or
> whether a key has certain value. I got some solution via regular
> expression feature of MySQL.
>
> The column type that I use is text. My question now is how to make
> the whole thing perform good. In other words,
> for regular expression querying, should I index the column for
> performance? If so, what kind of index should I use?
>
> Thanks a lot.
data with dynamic schema stored in a column as a property list.
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = "Fn, Ln"; // string value
gender = F; // single word string
interests = (reading,"drive fast"); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob = "1994-10-07 16:59:26"; },
{ lastName = Howe; firstName = Ann; gender = F; dob = "1998-01-26 04:09:12"; }
);
creditCards = {
visa = "XXX-x";
master = "YY-";
};
}
This is called plist and the depth of the hierarchy can go arbitrary deep (unknown
limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text data? say, find out
whether there is certain key or
whether a key has certain value. I got some solution via regular expression feature of
MySQL.
The column type that I use is text. My question now is how to make the whole thing
perform good. In other words,
for regular expression querying, should I index the column for performance? If so,
what kind of index should I use?
Thanks a lot.
data with dynamic schema stored in a column as a property list.
Hi, instead of xml, i stored arbitrary data of the form
(the actual usage of such mechanism is for more fancy stuff,
say, dynamic configuration, otherwise this is really not necessary)
{
name = "Fn, Ln"; // string value
gender = F; // single word string
interests = (reading,"drive fast"); // array
children = (
{ lastName = Howe; firstName = Sam; gender = M; dob = "1994-10-07 16:59:26"; },
{ lastName = Howe; firstName = Ann; gender = F; dob = "1998-01-26 04:09:12"; }
);
creditCards = {
visa = "XXX-x";
master = "YY-";
};
}
This is called plist and the depth of the hierarchy can go arbitrary deep (unknown
limit). And it can be converted back
and forth from dictionary object by a framework.
My task is to find out ways of querying a column holds such text data? say, find out
whether there is certain key or
whether a key has certain value. I got some solution via regular expression feature of
MySQL.
The column type that I use is text. My question now is how to make the whole thing
perform good. In other words,
for regular expression querying, should I index the column for performance? If so,
what kind of index should I use?
Thanks a lot.
query on a column with xml content
Is there any tools or built-in functions for querying contents of a column that holds a xml file?
server timezone and system timezon
Hi, I'm looking for help on timezone issues. I'm using mysql for a web application about time related events. I've two servers in different locations with different timezones. How to setup the database server so that the datetime value will not depend on the server machine's timezone? In other words, how to make the datetime value of the same record fetched from both mysql servers unaffected by locations? Thanks for any helps.
