Re: Assistance with replication
On Mon, Nov 12, 2012 at 5:21 AM, Machiel Richards - Gmail machiel.richa...@gmail.com wrote: 1. the fact that the slaves say seconds behind master = 0 does not neccesarily mean that repliication is working as I have found this a lot of times where it shows 0 then have proven that replication was not working (I.e. data not being processed on the slave). The 0 seconds simply means that it has read all the data but does not mean that it has been processed or up to date. 2. the Exec_Master_Log_Pos and Master_Log_File are in fact out of sync with the master (i.e. on the master the file and log position are way ahead of the slaves by about 9 hours). 3. We have tested replication by creating a test table in the database with some data and it was not replicated to the slaves. show global variables like 'slave_net_timeout'; I'll bet it's some giant number so the slave just doesn't realize that it's lost its connection. ...Todd -- The total budget at all receivers for solving senders' problems is $0. If you want them to accept your mail and manage it the way you want, send it the way the spec says to. --John Levine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL Crash when Open_files reach 128
On Wed, May 2, 2012 at 4:58 AM, vishesh kumar linuxtovish...@gmail.com wrote: I am getting following in error log 120502 07:52:05 mysqld started 120502 7:52:05 [Warning] Asked for 196608 thread stack, but got 126976 120502 7:52:05 InnoDB: Started; log sequence number 0 479249 120502 7:52:05 [Note] /usr/local/mysql-5.0.67/bin/mysqld: ready for connecti. Version: '5.0.67-community' socket: '/tmp/mysql.sock' port: 3306 MySQL Comity Edition (GPL) 120502 7:56:01 [Note] /usr/local/mysql-5.0.67/bin/mysqld: Normal shutdown 120502 7:56:03 InnoDB: Starting shutdown... 120502 7:56:04 InnoDB: Shutdown completed; log sequence number 0 479249 120502 7:56:04 [Note] /usr/local/mysql-5.0.67/bin/mysqld: Shutdown complete 120502 07:56:04 mysqld ended Honestly, that doesn't look like an error. It looks like something is connecting to your server and telling mysql to shut down. It's one second after 7:56, so I'd look for something in a cronjob that's telling it to shut down. Please check that before deciding it's a memory or file issue. ...Todd -- Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Martin Golding -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
On Wed, Mar 28, 2012 at 1:47 AM, Nuno Tavares nuno.tava...@dri.pt wrote: Halász, not sure if I understood correcly, but you mysql console/client may have different charsets. This means you should set it accordingly. Check Good catch, I had not considered that. mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): ✔ convert(0xE29C94 using utf8): ✔ 1 row in set (0.00 sec) And THAT was what I expected. Thank you so much for clearing that up. ...Todd -- Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Martin Golding -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
2012/3/27 Halász Sándor h...@tbbs.net: 2012/03/27 00:22 +0200, Walter Tross it looks like it's as simple as this: insert into mytable (mycolumn) values (0xE29C94), (0xE29C98) In some contexts it might be necessary to force the character set like this, though: convert(0xE29C94 using utf8) Yes! and with this I can use it for a constant in the middle of a query, which I really want: IF(IFNULL(ReceivesFlyer, 1), CONVERT(X'E29C94' USING utf8), '') This seems backwards from what I expected: mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): ✔ convert(0xE29C94 using utf8): ? 1 row in set (0.00 sec) I expected the second one to print the check mark and the first one to have the problem because there is no heavy check mark in latin1. What am I missing, why is it printing out the correct value using latin1 but not using utf8 ? ...Todd -- Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Martin Golding -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: RES: RES: Force drop table
2012/1/26 Suporte Avanutri supor...@avanutri.com.br: [At this point I deleted the table “obras.frm”. Still trying to dump, crashing every time, and restarting mysqld with a higher “innodb_force_recovery” value at a time] It doesn't matter what you set in innodb_force_recovery. If you do not have the obras.frm file (which contains the schema definition of the table), I don't know if you can retrieve the info. Put that file back, start it with a value of 6, and see if you can get any of the data out. If the data is important and have a budget for recovery, you can try (whatever solution is local for you) hiring a consultant skilled in mysql crash data recovery: - Oracle - Percona - SkySQL Regards...Todd -- SOPA: Any attempt to [use legal means to] reverse technological advances is doomed. --Leo Leporte -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: RES: Force drop table
2012/1/24 Suporte Avanutri supor...@avanutri.com.br: I've tried this before, but the server stills going down. The first error is always this: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */* FROM 'usuario': Lost connection to MySQL server during query (2013) What's likely happening here is that the access to the table is causing a fatal error inside your mysql daemon, it dies and restarts, resulting in the log messages you see about mysql not being shut down properly and doing a crash recovery. The followup suggestion was for you to put innodb_force_recovery=1 in your my.cnf and restart it, but that didn't work for you. The value that you pass to innodb_force_recovery controls how much of the innodb startup / crash recovery process to use. The gory details are at http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html . Valid values are between 1 and 6. For example, I shut down my mysql daemon, I make a copy of my corrupted database at /var/lib/mysql/ into /var/lib/mysql_tmp/, and then manually start the mysql daemon in the foreground with this: su - mysql -c '/usr/libexec/mysqld --innodb_force_recovery=6 --datadir /var/lib/mysql_tmp/' It simply sounds like you need to find the appropriate value to use to make your data accessible. Regards... Todd -- SOPA: Any attempt to [use legal means to] reverse technological advances is doomed. --Leo Leporte -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: RES: Force drop table
On Wed, Jan 25, 2012 at 6:42 AM, Todd Lyons tly...@ivenue.com wrote: For example, I shut down my mysql daemon, I make a copy of my corrupted database at /var/lib/mysql/ into /var/lib/mysql_tmp/, and then manually start the mysql daemon in the foreground with this: su - mysql -c '/usr/libexec/mysqld --innodb_force_recovery=6 --datadir /var/lib/mysql_tmp/' I forgot to mention that when you do this, there will be copious amounts of debug output that are spit out and it will give really ugly warnings about tables that it finds but metadata doesn't match up. But usually it can get the data out, though if you're not processing any undo/redo logs, there's a chance that the most recent data written to the table will not be retrievable. Regards... Todd -- SOPA: Any attempt to [use legal means to] reverse technological advances is doomed. --Leo Leporte -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SLOW performance over network
On Thu, Sep 29, 2011 at 7:12 AM, Jim Moseby jmos...@elasticfabrics.com wrote: I still use the old MySQL Administrator GUI on my windows box. A simple 'select * from tablename' that would return only three records takes just over a minute to return (although it says '3 records returned in 0.0086 seconds' at the bottom). I have many ODBC clients accessing this server as well, they all have slowness problems too. I'm stumped. What could possibly be causing this issue? Wild Guess: Does DNS resolution, especially reverse dns resolution, still work on the mysql server? ...Todd -- If Americans could eliminate sugary beverages, potatoes, white bread, pasta, white rice and sugary snacks, we would wipe out almost all the problems we have with weight and diabetes and other metabolic diseases. -- Dr. Walter Willett, Harvard School of Public Health -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Chronicle of fixing broken replication, and a question
Yeah it happens to all of us. Some master-slave replication system breaks and goes unnoticed. In my case, I had a monitoring script, but it was being called with a --verbose flag, and internally that verbose was being mapped to debug, and the routine to notify sysadmins of broken replication doesn't fire if debug is set, so warning emails were not being sent out. You might ask, why is this a problem? Just restart replication, skipping problem statements. Well, the big issue was that I have automatic purge of binlogs set. Over the course of a few days, the purging of binlogs surpassed where the replication stopped. Damn... This is a mysql 5.0.77 (CentOS 5.x) installation with maatkit and xtrabackup utilities available. So there's really only one thing to do. xtrabackup to the rescue! 1. I did a full copy of the running master database server using xtrabackup to a backup server via nfs. It took 2 hours, of which the last 15 minutes did a write lock of the entire server as it copied over the *.frm files and the few myisam tables. This was the most troublesome part as it was visible to both users and website owners :-( 2. On the slave, I did the apply-logs step (via nfs to the backup server), taking another 2 hours. 3. I renamed the old directory on the slave server and created a new mysql directory, owned by the correct user. 4. I copied the backup data from the backup server to this new directory. 5. I deleted the iblogfile* files so that mysql would create them on startup based on the sizes set in the my.cnf. 6. I started it up. It still wanted to do a crash recovery since the ibdata and iblog files didn't match, but that was expected. After a few minutes of scanning (we use innodb_table_per_file, so it had a lot of tablespace spread out over lots of files), it started up and was accepting connections. Looking good so far. 7. I did 'cat xtrabackup_slave_info'. For some reason it had this: CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS= *UH OH* *CRAP* *$#^@%!* ** See note below 8. Calm down, think about things a bit. Obviously I didn't compensate for the fact that I was doing the original backup on the master, but the apply-log on the slave (and --slave-info option doesn't have the data available on the 9. I know about what time I started the backup. I know that this is a database server dedicated to ecommerce shopping carts. As a result, there is a steady flow in of data as customers and web_robots access the shopping carts. 10. Knowing the data in #9, I look at the statements being replicated with mysqlbinlog on the master with --start-datetime=2011-07-25 12:00:00. A quick query of the data in that database on the newly-recovered-but-not-yet-replicating slave found me a last timestamp of 13:16.49. 11. I adjust my timestamp and trace data from that point until I find one that didn't get inserted into the database by the replication process (meaning, this was when I started the backup). I determined that the timestamp of data not yet in the database was 13:16:52. 12. Comments in mysqlbinlog output at that timestamp indicated it was at position 487727085. 13. I repeated the mysqlbinlog using --start-position 487727085 and verified that it was the same output as #12. 14. I did a change master statement on the slave, setting the position to 487727085, I started replication, and it caught up after about 20 minutes. 15. If #14 would have had issues, I did have mk-slave-restart available to force it to skip problems and restart replication. So everything is alright. There are no issues. But there is a question. Is there a better way to do this? Is there something automated that will compare THIS binlog to THAT server and tell me where the data was no longer being inserted ? I'm looking to see how others deal with a complete restart of a running system like this. ** It turns out that xtrabackup gave me confidence of the log position I had determined. Running the backup on the master, it also made this file: # cat xtrabackup_binlog_info mysqld-bin.000259 487727085 which of course confirmed the log position I determined in step #12. To be honest, the biggest step in the sequence above is likely #8. The ability to assess a situation without freaking out (for too long) is a big part of a sysadmin job IMHO. Regards... Todd -- If Americans could eliminate sugary beverages, potatoes, white bread, pasta, white rice and sugary snacks, we would wipe out almost all the problems we have with weight and diabetes and other metabolic diseases. -- Dr. Walter Willett, Harvard School of Public Health -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Chronicle of fixing broken replication, and a question
On Tue, Jul 26, 2011 at 8:18 AM, Reindl Harald h.rei...@thelounge.net wrote: 1. I did a full copy of the running master database server using xtrabackup to a backup server via nfs. It took 2 hours, of which the last 15 minutes did a write lock of the entire server as it copied over the *.frm files and the few myisam tables. This was the most troublesome part as it was visible to both users and website owners why are not using two rsync-runs? the first while mysqld is running the second directly after stop mysqld this way you can be 100% sure that you can start the replication from scratch and your downtime is only a few seconds, best if 43 GB is more than a few seconds. enough space to have this target on the master-machine because while you take the slow way over the network the master is running with a fresh binlog I tested a slightly modified version of your quickie script, first using the nfs share: Starting first rsync real37m48.201s Stopping MySQL:[ OK ] Starting second rsync real4m24.536s Starting MySQL:[ OK ] Then I ran it using local spindles: Starting first rsync real26m10.747s Stopping MySQL:[ OK ] Starting second rsync real3m11.945s Starting MySQL:[ OK ] So I could have lowerd the amount of time mysql was not available by quite a bit doing it that way. Plus I would have removed the requirement to apply-logs (due to not copying innodb files while they were open). In the end, xtrabackup worked as designed, but the fact that my large number of databases and tables and innodb_file_per_table slows things down tremendously, so it isn't the best fit in this case. ...Todd -- If Americans could eliminate sugary beverages, potatoes, white bread, pasta, white rice and sugary snacks, we would wipe out almost all the problems we have with weight and diabetes and other metabolic diseases. -- Dr. Walter Willett, Harvard School of Public Health -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: why mysql choose a bad query
2010/11/4 Changying Li lchangy...@gmail.com: PRIMARY KEY (`threadid`), KEY `dateline` (`dateline`), KEY `forumid_2` (`forumid`,`thread_type_id`,`visible`,`sticky`,`dateline`), KEY `forumid` (`forumid`,`visible`,`sticky`,`dateline`) ) ENGINE=InnoDB AUTO_INCREMENT=660 DEFAULT CHARSET=utf8; mysql explain SELECT * FROM `abc` WHERE `forumid` = 25 AND `visible` = 1 AND `sticky` = 0 order by dateline \G possible_keys: forumid_2,forumid key: forumid_2 Extra: Using where; Using filesort why it choose forumid_2, not forumid ? 5.0 docs online say: With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column. See Section 7.2.1, “Optimizing Queries with EXPLAIN”. Your query is using filesort, so it cannot according to the above statement. The docs also say in section 7.3.1.11 that it might not use an index if: The key used to fetch the rows is not the same as the one used in the ORDER BY... I'm curious, if you change the SELECT to a few named fields instead of *, does it affect the key choice? If you only select on fields in the key (i.e. a covering index) does it still choose what you consider to be the wrong key? -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Capitalize Input via Auto Complete?
On Wed, Sep 15, 2010 at 6:50 AM, Johan De Meersman vegiv...@tuxera.be wrote: I commonly set up a tunnel to the SSH server at the office and then another tunnel from that server to my development rig, so I can run MySQL WB at home on my database at work. Is that what you mean? Pretty much, yeah. I've not got just the one database, though, so it's usually too much of a bother to set up all the tunnels, and I just SSH straight through and use the commandline :-) Still, the SSH tunnel capability in the client is nice for hosts that are only one jump away. It works for multiple jumps too. You just have to enable X11 forwarding either in the appropriate config files or do it on the commandline. Note that it's possible that a severely paranoid sysadmin may well have disabled it in the sshd service on one or more of the machines that you're ssh'ing to. # my workstation has ForwardX11=yes in ~/.ssh/config ssh host1 # then from there: ssh -X host2 On host 2, running a gui app pops it up on my local desktop. -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine nor...@khine.net wrote: hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: As one other stated, it should not lose position when you stop and start the master or slave in a controlled manner. And also stated was that the way you were doing it was causing a loss of data, and you'll need to use maatkit to fix it, or if you can afford some downtime, stop mysql on both and copy the whole database(s) from master to slave (and reconfigure replication of course). On the slave, I'd be curious what the result was when you did only a STOP SLAVE; and then START SLAVE; because in my estimation, chances are it would have picked right up where it was supposed to. Your issue is likely that your slave has a large slave_net_timeout value (default is 3600 seconds IIRC). That means your slave will take 1 hour to finally give up on the replication connection (that the master closed on it), close the connection, and then reopen it. Lower that setting to something smaller, I've gone as low as 30 seconds, but I would only do that on a reliable network. Try lowering it to 300 (5 minutes) on your slave. When you restart the master, the slave should timeout after only 5 minutes and then resume replication. -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Moving from one MySQL server to three MySQL servers?
On Thu, Aug 5, 2010 at 8:35 AM, Nunzio Daveri nunziodav...@yahoo.com wrote: The server in this case is a stand alone with nothing more then CentOS and MySQL 5.1.44 on it. The drives are sas 10K rpm drives. The problem I see is that when you stress test the server (typically by running loads of reports - selects, joins) the machine hits 98% cpu and leaves only 800mb of free RAM out of the 16 GB of which I told it to allocate 12GB for Innodb in my.cnf. Once the server sucks up all the memory when we are stress testing it, it holds the 12 gb as hostage and refuses to release it back into the pool, regardless of weather We had a similar system where the innodb data dictionary (poor-man's description is an in-memory map of all the tables it has opened up to that point) consumed all free memory and the kernel started killing processes to get some back. At the time, stock mysql did not have the ability to limit the size of the data dictionary memory usage (and I don't see it as an available option for 5.1.x currently), so I tested XtraDB and it prevented the memory usage from growing without bound. XtraDB also gave us lots of knobs to twiddle and extra insights into what innodb was doing. I've not experimented with current mysql 5.1.x to see how much of that extra control has made it into the mainline. only 800mb of ram free??? If I start to stress it again then it starts to go into swap. Really weird, thus wanting to split the load onto 3 machines. When you restarted the stress test, did it access new tables or the same ones that the previous stress test did? What happens if you tell innodb to use less memory and do your tests? Have you found any way to make mysql not run out of memory? -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Variable Expansion Using MySQL Client and .sql Files
Simple - brilliant. I've modified to cat a temporary script... 1) cat the file as suggested: cat ~/payload/scripts/create_mysqldb.sql EOF ###--- ### Display the current user ###--- create database puppet; ###--- ### Display all default accounts ###--- USE puppet; ###--- ### Remove anonymous accounts ###--- GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON puppet.* TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'; (variables are expanded) ###--- ### Verify new admin account was added ###--- SELECT user,Host,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Alter_priv from mysql.db; EOF 2) Execute the script 3) Remove the script rm -f ~/payload/scripts/create_mysqldb.sql It is often the simplest things that are most beautiful. Thanks for pulling my head out :D TT On 07/29/2010 03:19 PM, Travis Ard wrote: mysqlEOF GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT'; EOF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Automate Install/Configuration of MySQL on Linux
To answer both emails...
My first install is Puppet :)
1) I have many clients (schools with Macs/Linux) that could use this
package. This is in the works - good call.
For the sake of DRP (Disaster Recovery Planning) I automate all of my
installs on the 1% off-chance that my backups are partially fouled for
some reason.
2) I used the .sql file that you recommended and wow - it's so much
easier than expect.
Once I had the right key-words (mysql .sql script) Google returned the
right pages:
4.5.1.4. Executing SQL Statements from a Text File
http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html
The problem I have now is variable substitution. But, that's another thread.
Thanks guys,
TT
On 07/27/2010 11:40 PM, Andrés Tello wrote:
just place all your sql sentences in a file, setup the database and
then use:
mysql -uroot -hlocalhost file_with_allsql_you_need.sql
or
cat file_file_with_allsql_you_need.sql | mysql -u root -hlocalhost
and you are done.
On Tue, Jul 27, 2010 at 3:56 PM, Todd E Thomas
todd_...@ssiresults.com mailto:todd_...@ssiresults.com wrote:
I'm looking for automation direction...
I've found many packages that sit on top of MySQL. For the
purposes of consistency I'd like to automate these installs.
I've been able to automate the install and configuration of
everything except the mysql part.
I'm using CentOS 5.5. Installing/verifying is no big deal.
It's the MySQL configuration that's holding me up.
Basically I've created an expect script. It works 99% but it's a
PITA to finish. Here's what I'd like to accomplish:
*Set the default admin password
# mysqladmin -u root password 'root-password'
*login to mysql
mysql mysql -u root -p
*Drop the anonymous accounts
mysql DELETE FROM mysql.user WHERE user = '';
*Sync all of the root passwords
mysql UPDATE mysql.user SET Password =
PASSWORD('root-password') WHERE User = 'root';
*Remove the test database:
mysql drop database test;
In another script I would like to create databases for specific
packages. EG:
Concrete5, for example needs:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON
concrete5.db TO 'admin'@'localhost' IDENTIFIED BY 'admin-password';
If there is a better way to do this than using expect I would
greatly appreciate any pointers in the right direction. Bash is
comfortable for me and perl is within reach. I'm not much versed
in anything else right now.
--
Thanks for the assist,
Todd E Thomas
It's a frail music knits the world together.
-Robert Dana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mr.crip...@gmail.com
Variable Expansion Using MySQL Client and .sql Files
Hey all,
I've found many packages that sit on top of MySQL for various clients.
For the purposes of consistency I'd like to automate these installs.
I've been directed towards using .sql files and they work great.
The trouble I'm having now is that I would like to secure the
installation but variable expansion isn't clicking for me.
My setup is fairly straight-forward:
I have a single installer script that calls all other scripts. This is
how it works:
1) Source in all global environment variables from a working file:
1_GLOBAL_ENV.sh
2) execute script to create mysql db
3) secure mysql
. /root/payload/1_GLOBAL_ENV.sh
...
###---
### Configure MySQL
###---
set -x
mysql -v ${INST_SCRIPTS}/mysqld/secure_mysqld.sql
...
mysql -v ${INST_SCRIPTS}/mysqld/create_db.sql
...
---
The create_db.sql should be similar to this:
Concrete5, for example needs:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON
concrete5.db TO 'admin'@'localhost' IDENTIFIED BY '$PASSWD_PRIV_ROOT';
---
The secure_mysqld.sql script is fairly simple as well:
# Display the current user:
select user();
# Display all default accounts:
SELECT User,Host,password FROM mysql.user;
# Remove anonymous accounts:
DELETE FROM mysql.user WHERE user = '';
# Display all remaining accounts:
SELECT User,Host,password FROM mysql.user;
# Sync root passowrds:
UPDATE mysql.user SET Password = PASSWORD('$PASSWD_PRIV_ROOT') WHERE
User = 'root';
exit
---
It all works pretty well. Variable expansion is the problem. For now all
of my other scripts substitute $PASSWD_PRIV_ROOT (from my
1_GLOBAL_ENV.sh) for the actual password. The *.sql scripts do not.
If anyone can shed some light on this I would appreciate the help.
--
Thanks for the assist,
Todd E Thomas
C: 515.778.6913
It's a frail music knits the world together.
-Robert Dana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Automate Install/Configuration of MySQL on Linux
I'm looking for automation direction...
I've found many packages that sit on top of MySQL. For the purposes of
consistency I'd like to automate these installs.
I've been able to automate the install and configuration of everything
except the mysql part.
I'm using CentOS 5.5. Installing/verifying is no big deal.
It's the MySQL configuration that's holding me up.
Basically I've created an expect script. It works 99% but it's a PITA to
finish. Here's what I'd like to accomplish:
*Set the default admin password
# mysqladmin -u root password 'root-password'
*login to mysql
mysql mysql -u root -p
*Drop the anonymous accounts
mysql DELETE FROM mysql.user WHERE user = '';
*Sync all of the root passwords
mysql UPDATE mysql.user SET Password = PASSWORD('root-password')
WHERE User = 'root';
*Remove the test database:
mysql drop database test;
In another script I would like to create databases for specific
packages. EG:
Concrete5, for example needs:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON
concrete5.db TO 'admin'@'localhost' IDENTIFIED BY 'admin-password';
If there is a better way to do this than using expect I would greatly
appreciate any pointers in the right direction. Bash is comfortable for
me and perl is within reach. I'm not much versed in anything else right now.
--
Thanks for the assist,
Todd E Thomas
It's a frail music knits the world together.
-Robert Dana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How can I know if Mysql Crashed or stopped gracefully
On Tue, Oct 27, 2009 at 7:51 AM, Bryan Cantwell bcantw...@firescope.com wrote: I notice that issuing a reboot or shutdown -r now command, (in Linux) that the 'service mysql stop' is never run... it just seems to catch the sig 15 and does its own shutdown... I have scripted in the stop section of my init script to touch a file that I look for on restart, but if the stop is never executed on reboot/shutdown, then I have a problem. What distro? Which version of mysql? My comments below apply to our InnoDB heavy CentOS systems. Older versions of mysql (5.0.x on our CentOS machines) wait 60 seconds for the mysqld process to completely die. If mysql has a lot of data to flush to disk, it can take longer than that 60 seconds. The init script assumes that the kill command didn't succeed, it prints out FAILED, and the system shutdown process moves on to the next init script. Near the end of the system shutdown process, the shutdown script issues a final SIGKILL to remaining running processes. If mysqld didn't finish flushing that data before this final KILL signal, the mysqld process is killed instantly and you have an unclean shutdown. One quick solution is to manually stop mysql, watching to see when the process finally goes away (top, ps, etc), then do your shutdown -r now. Modern versions of mysql (5.1.x) seem to handle this better because it waits as long as it takes for mysqld to shut down properly or to start up (and accept connections). At least in my testing so far, I've not hit a timeout or received an incorrect OK or FAILED message in the 5.1.x series. -- Regards... Todd The best thing about pair programming is that you have the perfect audience for your genius. -- Kent Beck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HOW TO Backup a mysql innodb on windows?
On Mon, Sep 14, 2009 at 7:44 AM, Néstor rot...@gmail.com wrote: Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? You can also use the free tool from Percona which can backup innodb tables without having to shutdown or read lock the database (but it does read lock the database while copying *.frm files for Innodb tables and any MyISAM tables). http://www.percona.com/docs/wiki/percona-xtrabackup:start If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Depends. If you use foreign keys in innodb, then you cannot import that into myisam because myisam does not support foreign keys. If you do not use foreign keys, it should work. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HOW TO Backup a mysql innodb on windows?
On Mon, Sep 14, 2009 at 8:28 AM, Michael Dykman mdyk...@gmail.com wrote: If I may, If you have foreign keys on your InnoDB, you can still import your data to MyISAM but foreign keys will be lost. Otherwise, the data will load just fine. Very good point. My comment was based on the possibly erroneous assumption that if you were using foreign keys, when you switched to MyISAM you wanted to continue to use foreign keys. Without the op saying one way or the other, Michael's answer is more correct than mine. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DB/table problem
On Thu, Sep 10, 2009 at 2:49 PM, Gavin Towey gto...@ffn.com wrote: When using innodb, your data is stored in the data/ibdata* files. Copying the database directory is not a valid backup. In the future, I would suggest using mysqldump. However if you want to make a file-system copy backup with innodb you need to 1. Shutdown the server 2. Copy *everything* in the data directory 3. Store a copy of your my.ini with the backup as well. 4. Restart mysql Do you exclude xtrabackup as a potential solution because he's using Windows (it doesn't build on Windows) or because you have some issue or problem with xtrabackup? I'm not aware of any show-stopper bugs. I'm just curious, because it works well for us. The backup is online with the only lock held occuring while it's copying over the *.frm and *.MY* files, which is not very much for us. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slave log files going nuts...
On Thu, Jul 16, 2009 at 1:18 PM, Gary Smithg...@primeexalia.com wrote: I have a new slave I setup against a new master server. The master server has 4 log files in it, the most current being updated on the 16th. The slave server on the other hand has several files, many which seem to be blank. This slave is set to slave the master and act as a master for downstream slaves. Note, there is no master/master on this configuration, even though the master itself could do it. Any ideas? Something is doing several 'mysqladmin refresh' or a related command, all sequentially in a row in short order. Look at your cron jobs that start or end around the time that all those empty binlogs are being created. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Any tool convert ibdata1/ib_logfile0/ib_logfile1 files to myisam data offline
On Wed, Jul 15, 2009 at 11:58 AM, mosmo...@fastmail.fm wrote: I have backup all the database files(innodb engine). Now I want to recover it as myisam engine offline. Any suggestions? Why can't you copy the InnoDb files to another machine and open the database with another MySQL server? (You could do it on your current server but it will be CPU disk intensive). Then do a: create table MyTable1 engine=MYISAM select * from InnoTable1; If these tables are large, then you could use: create table MyTable1 engine=MYISAM select * from InnoTable1 limit 0; insert into MyTable1 select * from InnoTable1 limit 0,10; insert into MyTable1 select * from InnoTable1 limit 10,10; to import just 100k rows at a time. Now build the indexes for MyTable1. Could do it easier I would think: ALTER TABLE t1 ENGINE = MyISAM Repeat for each table. Script it if it's more than a few tables. Note that foreign keys will break if your InnoDB table uses them. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Copy 70GB ibdata, etc. and server won't start now
On Tue, Jul 14, 2009 at 2:14 AM, Marcus Bointonmar...@synchromedia.co.uk wrote: You should take a look at Percona's xtrabackup utility to do this. It takes a clean snapshot of an innodb database that can be restored on a target machine in a few minutes, though it does crash recovery at backup time which can take a while. Most likely the op only tried to copy the ibdata file(s) and not the iblog files. A big honking waving hand over here saying YES, use xtrabackup. It will be a pretty quick recovery process since the db was shut down cleanly. Note that it will restore ALL tables' data, not just the ibdata and iblog files. Just make sure that your innodb settings in my.cnf match. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Re: Copy 70GB ibdata, etc. and server won't start now (Action Required)
If a mailbox is protected by one of these types of services: 1) It should be smart enough to see mailing lists and use that as a qualifying address to be allowed through. 2) It should be smart enough to not reply to bulk precedence mail 3) Or the mailbox should never be subscribed to a mailing list. I'm firmly against these types of services, but then again I've never seen one that met the criteria of #1 and #2. Are there any intelligent services out there? #3 will cause people to cry about not being fair and I'm just trying to prevent spam. My answer is no, not being fair is someone expecting me to do their work for them just so they can receive emails from a public list that they joined. Regards... Todd On Wed, Jul 15, 2009 at 10:20 AM, postmas...@boxbe.com wrote: Hello Todd Lyons, I use Boxbe to protect my email address. While I did receive your email about Re: Copy 70GB ibdata, etc. and server won't start now, you are not currently on my email Guest List. I'll be more likely to see your email and future messages if you are on my priority Guest List. Click here to be put directly on my Guest List Thank you, gurunatha...@gmail.com About this Notice This courtesy notice is part of a free service to make email more reliable and useful. Boxbe (www.boxbe.com) uses your existing social network and that of your friends to keep your inbox clean and make sure you receive email from people who matter to you. Say Goodbye to Email Overload www.boxbe.com Final-Recipient: rfc822; gurunatha...@gmail.com Diagnostic-Code: X-Boxbe-Notice; Sender not pre-approved, delivery likely delayed. Follow instructions in above notice Status: 4.7.0 -- Forwarded message -- From: Todd Lyons tly...@ivenue.com To: Date: Wed, 15 Jul 2009 05:28:46 -0700 Subject: Re: Copy 70GB ibdata, etc. and server won't start now -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexing dynamics in MySQL Community Edition 5.1.34
On Sat, Jun 27, 2009 at 7:03 AM, Mike Spreitzermspre...@us.ibm.com wrote: Today's instance finished shortly after I sent the email below. BTW, here are some specifics on the table (which uses MyISAM). Thursday's instance has 11 GB of data and 0.78 GB of index. Today's instance has 26 GB of data and 1.8 GB of index. If you have the ability to test, I'd compare that to importing the csv into the table with the indexes already defined. The way you did it should be faster, but since you see that it's only using one core, I'd try splitting the data up into 16 separate files and importing them all at once. In theory (SWAG actually), multiple imports would each use their own core to whatever thread count you have innodb defined to use ... Oh, just saw that you were using myisam. Never mind. At any rate, I'd be very surprised if importing into a table with indexes already defined was the same speed or faster, but doing so could give you some useful information, such as at what point the import (and concurrent index creation) drops from expected level X to much reduced level Y. You could then (hopefully) find a correlation between some cache or buffer setting that will explain the sudden drop in speed. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Default Date and Time
Hi All, I want to create a table that defaults to current_date and current_time. I have: CREATE TABLE `personalevent`( `pevent` mediumint(10) NOT NULL, `eventid` mediumint(10) NOT NULL, `userid` mediumint(10) NOT NULL, `username` varchar(10) NOT NULL, `password` varchar(10) NULL, `country` varchar(45) NULL, `zipcode` varchar(5) NULL, `city` varchar(35) NULL, `hstate` varchar(45) NULL, `exclusive` varchar(7) NULL, `eventtime` time NULL DEFAULT current_time(), `eventdate` date NULL DEFAULT current_date(), `eventdura` varchar(35) NULL, `daysevent` varchar(10) NULL, `crowd` varchar(25) NULL, `venue` varchar(50) NULL, `activitytype` varchar(45) NULL, `actdetails` varchar(255) NULL, `encodedby` varchar(100) NULL, `curmo` varchar(2) NULL, `pageweb` varchar(50) NULL, PRIMARY KEY (`pevent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; But this throws a syntax error. I have tried Now() as well. What am I doing wrong? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
Hi Guys, i am still lost here: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX'; FLUSH PRIVILEGES; Here is the command that I am executing: [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/cloudsql.sql Stopping MySQL:[ OK ] Starting MySQL:[ OK ] [r...@server1 ~]# In cloudsql.sql I have: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES; commit; When I try to connect from my machine in my apartment I get: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server Can I start over some how or how do I fix? I have never had this much trouble, but I guess historically for me, I have not done a setup from scratch. -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
OK, I have done this: [r...@server1 ~]# mysqld_safe A mysqld process already exists [r...@server1 ~]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [r...@server1 ~]# mysqld_safe --init-file=~/cloudsql.sql Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:30 mysqld ended [r...@server1 ~]# mysqld_safe stop Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:54 mysqld ended [r...@server1 ~]# /etc/init.d/mysqld start Starting MySQL:[ OK ] [r...@server1 ~]# I tried to connect and still same error message of: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server I must be a dunce, I know you guys know what you are talking about! -Jason On Apr 29, 2009, at 12:28 PM, mark konetchy wrote: i dont think that the init.d script will accept the argument. you need to run /usr/bin/mysqld_safe --init-file=clouds.sql (or whatever the path to mysqld_safe is) 2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com Hi Guys, i am still lost here: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX'; FLUSH PRIVILEGES; Here is the command that I am executing: [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/ cloudsql.sql Stopping MySQL:[ OK ] Starting MySQL:[ OK ] [r...@server1 ~]# In cloudsql.sql I have: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES; commit; When I try to connect from my machine in my apartment I get: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server Can I start over some how or how do I fix? I have never had this much trouble, but I guess historically for me, I have not done a setup from scratch. -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
Hi Mark, I am all set now, The log said it could not find the sql file, so I put it in /tmp and hard coded the path to /tmp/cloudsql.sql and it works now! Weird because I had used ~/cloudsql.sql and /root/cloudsql.sql as paths too Thank you so much for your help! John and Andy too! -Jason On Apr 29, 2009, at 1:20 PM, mark konetchy wrote: Jason, It looks like mysql is erroring out when you try to start it from the command line. What does the error log say? 2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com OK, I have done this: [r...@server1 ~]# mysqld_safe A mysqld process already exists [r...@server1 ~]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [r...@server1 ~]# mysqld_safe --init-file=~/cloudsql.sql Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:30 mysqld ended [r...@server1 ~]# mysqld_safe stop Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:54 mysqld ended [r...@server1 ~]# /etc/init.d/mysqld start Starting MySQL:[ OK ] [r...@server1 ~]# I tried to connect and still same error message of: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server I must be a dunce, I know you guys know what you are talking about! -Jason On Apr 29, 2009, at 12:28 PM, mark konetchy wrote: i dont think that the init.d script will accept the argument. you need to run /usr/bin/mysqld_safe --init-file=clouds.sql (or whatever the path to mysqld_safe is) 2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com Hi Guys, i am still lost here: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX'; FLUSH PRIVILEGES; Here is the command that I am executing: [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/ cloudsql.sql Stopping MySQL:[ OK ] Starting MySQL:[ OK ] [r...@server1 ~]# In cloudsql.sql I have: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES; commit; When I try to connect from my machine in my apartment I get: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server Can I start over some how or how do I fix? I have never had this much trouble, but I guess historically for me, I have not done a setup from scratch. -Jason
Start MySQL with --intit-file?
I am trying to start MySQL with --init-file but i get that it is an invalid option. the 'man' page and --help dont help me decide what is. Here is what I am doing: r...@server1 ~]# mysql start --init-file = cloudsql.txt mysql: unknown option '--init-file' Thoughts? -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
Hi,
I am trying:
[r...@server1 ~]# /etc/init.d/mysqld restart --init-file=cloudsql.sql
Stopping MySQL:[ OK ]
Starting MySQL:[ OK ]
the cloudsql.sql file contains:
UPDATE mysql.user SET Password=PASSWORD('xxx') WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON mysql.* TO 'root'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON mysql.* TO 'root'@'173.8.172.53';
commit;
But I still get that I cannot connect from 173.8.172.53
I still dont see what I am doing wrong...
-Jason
On Apr 28, 2009, at 5:34 PM, mark konetchy wrote:
hey jason,
you need to restart the *server* with the init-file option, have a
look at:
http://dev.mysql.com/doc/refman/5.0/en/server-options.html
2009/4/28 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com
I am trying to start MySQL with --init-file but i get that it is an
invalid
option. the 'man' page and --help dont help me decide what is.
Here is what I am doing:
r...@server1 ~]# mysql start --init-file = cloudsql.txt
mysql: unknown option '--init-file'
Thoughts?
-Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=markkonet...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Resetting MySQL Root Password
Hi All, CentOS 5.3 I installed MySQL Server via yum and started it. I tried entering: mysqladmin -u root password yourrootsqlpassword mysqladmin -h server1.example.com -u root password yourrootsqlpassword But I get: r...@server1 ~]# /usr/bin/mysqladmin -u root -h localhost password mypassword /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' How can I reset this and allow Root access, otherwise nobody has access! Thanks, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL University session on March 12: MySQL and ZFS
On Mon, Mar 9, 2009 at 9:10 AM, Stefan Hinz stefan.h...@sun.com wrote: MySQL and ZFS http://forge.mysql.com/wiki/MySQL_and_ZFS This Thursday (March 12th, 14:00 UTC), Martin MC Brown will give a MySQL University session on MySQL and ZFS. MC works on the MySQL Documentation Team and has been involved with quite a few Solaris things, for example porting MySQL to openSolaris. MC has recently given MySQL University talks on developing MySQL on Solaris and about using DTrace with MySQL. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown March 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 BST / 15:00 CET / 17:00 MDT (Moscow) How to Use Charsets and Collations Properly Susanne Ebrecht A quick reminder, in the U.S. we're in daylight savings time now, so those times are 7AM PDT / 8AM MDT / 9AM CDT / 10AM EDT. Ultimately though, you can't go wrong if you base it on 14:00 UTC :-) -- Regards... Todd All truth passes through three stages. First, it is ridiculed. Second, it is violently opposed. Third, it is accepted as being self-evident. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Make Innodb give memory back?
= table_cache=4096 set-variable = thread_cache=32 set-variable = tmp_table_size=128M set-variable = wait_timeout=28800 innodb_file_per_table=1 innodb_flush_log_at_trx_commit=1 innodb_buffer_pool_size = 2048M innodb_log_file_size = 128M innodb_open_files=4000 innodb_flush_method=O_DIRECT server-id=2401 master-host=db2 master-user=replicate master-password=X master-connect-retry=30 log-bin expire_logs_days=20 log-slave-updates auto-increment-increment=2 auto-increment-offset=1 log-slow-queries = /var/lib/mysql/slow.log long_query_time = 5 log_queries_not_using_indexes = 1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick -- Regards... Todd All truth passes through three stages. First, it is ridiculed. Second, it is violently opposed. Third, it is accepted as being self-evident. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On OS X
Hi All, On OS X, I have some Obj-C that creates NSTasks to execute some sql statements. What do I deploy on the client to make this work for them? I want to make it park of my package installer, but I dont know what pieces. Thoughts? -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2008 6:23 AM To: mysql@lists.mysql.com Subject: RE: what is the proper way to store timezone information? Boyd, Todd M. wrote: When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. My concern is whether the time_zone_id is a fixed reference of the timezone. If the id might (for whatever reason) change in the future, I'd have to store the timezone name. http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html You can convert back and forth using the system's time zone table. Read the MySQL manual I've linked to above for more information. Apparently, you can even reference them by offset from UTC (i.e., -6:00 for US Central). The article warns against using the time zone's text description, but I saw nothing about dangers of offsets or time_zone_id. Todd Boyd Web Programmer
RE: Function Still Not Working
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 1:40 PM To: Martijn Tonies; MySQL List Subject: Re: Function Still Not Working Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. I think what he means is... in one instance of the function, the data is collated as latin-iso-blahblah, perhaps, and a different collation (one without case sensitivity, eh?) in the other table... As I don't deal with letters/characters outside of the 'standard' Latin-iso-asdfasdf collation, I'm afraid there's not much else I can explain using my limited knowledge. Hopefully, though, that helped to give you an idea of what he was driving at. ;) Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: natural sort via substrings
-Original Message- From: Emily Heureux [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 8:25 PM To: mysql@lists.mysql.com Subject: natural sort via substrings Hi, I am attempting to do a natural sort from within mysql, if possible. So, for example, jane2 would come before jane10, and normal strings would still sort as expected. I found some solutions, like using length for the numerical part, but that only works if the strings are the same length. Ideally, I would like to use substring_index, but stick a regexp in as the delimiter. So far, it seems you cannot do this. Does anyone know how to put a regexp as the delimiter in substring_index? For example, I want to do something like this: .order by substring_index(name, 'regexp [0-9]+', 1); Is this possible? Forgive me if I am incorrect, but wouldn't jane2 already be listed before jane10 if you just ORDER BY fieldname ASC? I suppose jane2 and jane20 would wind up next to each other if this were the case, but can you not zero-fill your values (i.e., jane02) to prevent this from happening? Sorry if my suggestion falls short of the mark, but the conditions for your test case were vague at best. :) Can you not zero-fill? Are you sorting by the entire field's value, or just a portion of it? Wouldn't substring_index() sort all jane## entries arbitrarily, since your (theoretical) example returns everything to the left of the first match (but not including the match)? Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 5:17 AM To: mysql@lists.mysql.com Subject: what is the proper way to store timezone information? All, I will be recording timezone information based on user input using the time zone names from mysql.time_zone_name - names like 'America/Los_Angeles'. When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. I'd say if it's easier (or perhaps more efficient, if this is a concern) for you to simply store the time_zone_id value, then do it that way. It's not as if it won't store your data if you store the time_zone_name... it just might be harder to use as a variable later, being text instead of an identifying number. Again... totally a matter of your preference. Todd Boyd Web Programmer
RE: natural sort via substrings
-Original Message- From: Boyd, Todd M. [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:33 AM To: Emily Heureux; mysql@lists.mysql.com Subject: RE: natural sort via substrings ---8--- snip Forgive me if I am incorrect, but wouldn't jane2 already be listed before jane10 if you just ORDER BY fieldname ASC? I suppose jane2 Man, what a morning. After reading what I wrote, it's obvious to me that this should not happen (jane2 coming before jane10, I mean.. 1 2). Maybe sort by length first, then sub-sort by alpha? As far as regex goes, I know of no way to use it inside the MySQL function substring_index(). Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:48 AM To: [EMAIL PROTECTED] Cc: mos; mysql@lists.mysql.com Subject: Re: Fulltext index -first query slow, subsequent queries fast Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley [EMAIL PROTECTED] wrote: mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ ---8--- snip GIYF: http://www.linux.com/feature/118721 I believe the *binaries* are only *pre-compiled* for Windows. Search the Sphinx site for info about compilation, or crack open one of the .tgz (an immediate I'm probably a *nix package flag) releases. I'll bet you can compile it yourself on *nix. (The article I've linked to also talks about using Sphinx in BSD distros.) Hope that helps, Todd Boyd Web Programmer -- 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
-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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: natural sort via substrings
-Original Message- From: Emily Heureux [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:12 AM To: Boyd, Todd M.; mysql@lists.mysql.com Subject: RE: natural sort via substrings Hi, I am sorry for being so vague. The values are not as simple as jane20, jane10, or jane2. There are names like, jane-2, alex 3, alex4, and just 'jane', etc. ORDER BY sorts by either numerical OR string, not both, as far as I can tell. I'll have to think about zero-fill, but I doubt that will work given the lack of standards for the names I am getting (protein names). I need a sort that works like the way a human would sort. As for substring_index(), I was using that because the names I am dealing with often have a distinguishing number at the end, so I would like to just grab all but the number(s), and then grab just the number(s) (So, actually, I am using substring_index() twice). If I can separate out the numbers at the end from the rest of the string with a regexp delimiter, the problem is solved. I just don't know if that can be done from within ORDER BY. ---8--- snip Emily, From an exhaustive search of the web (including MySQL's page and others), it appears that Regular Expression support in MySQL is limited at best. All you can do with it thus far is determine whether or not something matched the given expression--1 or 0 are the only results possible, from what I can gather. You may be forced to either nest a bunch of queries using substring_index() and other server-side string manipulation functions, or sort the data after queried-extraction. :( By all means, though, do not take my information as gospel. There may very well be a way to pull off what you're trying to do... but ORDER BY REGEXP is not it. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: natural sort via substrings
From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 12:10 PM To: Boyd, Todd M. Subject: Re: natural sort via substrings What I would do is form 2 additional fields from the first, an alphanumeric field and a numeric field so: jane-2 jane- 2 alex 3 alex 3 (maybe put a blank after the 'x' but be sure to handle it properly, trailing spaces are tricky) alex4 alex 4 jane jane (maybe null, or 0 for the numeric field, depending on the application) Then sort on the 2 fields. Depending on your application, you might want to keep the entire name field as well as the 2 derivative fields, just accept the de-normalization. You can use the entire field for display purposes. If your table is of any size whatsoever this would be vastly preferable to doing all sorts of substring-ing in your where clauses. You won't have a prayer of having the optimizer use an index. Jim On Fri, Jun 13, 2008 at 11:58 AM, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Emily Heureux [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:12 AM To: Boyd, Todd M.; mysql@lists.mysql.com Subject: RE: natural sort via substrings Hi, I am sorry for being so vague. The values are not as simple as jane20, jane10, or jane2. There are names like, jane-2, alex 3, alex4, and just 'jane', etc. ORDER BY sorts by either numerical OR string, not both, as far as I can tell. I'll have to think about zero-fill, but I doubt that will work given the lack of standards for the names I am getting (protein names). I need a sort that works like the way a human would sort. As for substring_index(), I was using that because the names I am dealing with often have a distinguishing number at the end, so I would like to just grab all but the number(s), and then grab just the number(s) (So, actually, I am using substring_index() twice). If I can separate out the numbers at the end from the rest of the string with a regexp delimiter, the problem is solved. I just don't know if that can be done from within ORDER BY. ---8--- snip Emily, From an exhaustive search of the web (including MySQL's page and others), it appears that Regular Expression support in MySQL is limited at best. All you can do with it thus far is determine whether or not something matched the given expression--1 or 0 are the only results possible, from what I can gather. You may be forced to either nest a bunch of queries using substring_index() and other server-side string manipulation functions, or sort the data after queried-extraction. :( By all means, though, do not take my information as gospel. There may very well be a way to pull off what you're trying to do... but ORDER BY REGEXP is not it. Jim, Thank you for formulating my idea into a more easily-understandable format. :) It's been a long week. What I meant to say when I mentioned sort the data after queried-extraction was that you would need to split the data extracted with a query into its sort-able parts as you described. Very well put on your part, though. Emily, You might try and find a conditional string function that will find the first digit character in a string (or a nifty manipulation of a different string function that accomplishes the same objective). I understand that's sort of along the line of what you were trying to do in the first place, but perhaps this new funneling of the problem will give you some different insight into a possible solution. I'll scour the web and documentation with the free time I may or may not get today and see what I can come up with, as well. Don't give up hope! :) This sort of data transformation is not an uncommon occurrence, and someone somewhere must have tried to tackle a similar problem--with positive results. *crosses fingers* If not, maybe someone else's idea for a solution can plant the seed. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2008 6:23 AM
To: MySQL List
Subject: Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time,
and
I'm
still having the problem. I spent HOURS the other day manually
going
through the data and Properizing these things by hand. I don't want
to do
that again if I can avoid it. If anyone has any clues on this one,
I
would
appreciate it.
---8--- snip
It's a very simple function used to properize a string sent to it.
When I
do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our
server
that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a
problem
with the older version that would cause this function not to work
properly?
Any difference in default collation?
I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).
Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?
Just spit-balling...
Todd Boyd
Web Programmer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enable and disable keys
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enable and disable keys
From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 10:27 AM To: Boyd, Todd M. Cc: mysql Subject: Re: enable and disable keys /tmp has 16GB free space On 6/12/08, Boyd, Todd M. [EMAIL PROTECTED] wrote: -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 9:41 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: enable and disable keys We have 200GB of free space on the file system where our database is located. ---8--- snip If the system is *nix, there's a high probability that the area used for temporary storage (unless specifically defined by a MySQL configuration) and the area that houses your database files are on different partitions--possibly even different storage devices altogether. Is this the case? Enable kyes goes fine for couple of hrs with REPAIR BY TMP, but then switches to REPAIR BY KEYCACHE and writes a log in the error log file Warning: Enabling keys got errno 28, retrying What could be the problem. System has 8 cpu and 16GB RAM I have set myisam_max_sort_file_size=98GB myisam_sort_buffer_size=750MB. /tmp folder has 16GB free space. ---8--- snip OS error code 28: No space left on device I would say you are running out of space. Sorry if this question is stupid, but... okay, your /tmp folder has 16GB available for use. Is this the folder that MySQL has been configured to use? I understand that it should be by default, but it doesn't hurt to examine every facet of a problem with confusing roots. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: purge binary logs on master
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, May 18, 2008 at 01:02:32PM +0530, Ananda Kumar wrote: I want to purge bin logs which are older than 4 days. I want to keep 4 days of bin logs so that in the event of any data issue, i can restore it from bin logs. So, i tired to use PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00'; But it did not work. Can you please help me on this I was going to say drop the time stamp and just use the date, but it works the same for me using '2008-05-XX 00:00:00' as it does using '2008-05-XX'. Just confirming that your command does seem correct, dunno why it's not working for you. - -- Regards... Todd when you shoot yourself in the foot, just because you are so neurally broken that the signal takes years to register in your brain, it does not mean that your foot does not have a hole in it. --Randy Bush Linux kernel 2.6.22-14-generic 6 users, load average: 0.89, 0.61, 0.40 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIMad/Y2VBGxIDMLwRAirdAJ9+76Ew8AB5Y6FurP4zF8l9Q60p5gCcDtKi 8qQbhM/cExUYVsoFhgS4Vm8= =THsx -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comma's in data?
Hi All, I have a client that wants to insert data into a VarChar field that contains commas. These are property addresses. Example: 2966 Moorpark Ave, San Jose, CA, 95128 1 Infinite Loop, Cupertino, CA, 95 How can I allow this? Thanks! -Jason
Replication Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've been looking for a way to fake replication from mysql to a local BDB database. I'm not finding anything. Anybody ever come across this? It seems like it wouldn't be too terribly difficult to read from the relay-log and keep track of where you are. But not having written anything like this before, I'm far from authoritative. Any comments or suggestions? (For reference, I'm implementing Bind-DLZ. I'm going to replicate tables from a multi-master server to a couple of slaves running on the nameservers. Then I want a daemon to read that relay-log and replicate those commands into a local BDB database. The reason is for maximum speed.) - -- Regards... Todd we're off on the usual strange tangents. next will be whether it is ethical to walk in your neighbor's open house if they're running ipv6:-). --Randy Bush Linux kernel 2.6.22-14-generic 4 users, load average: 0.18, 0.06, 0.02 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvKwfY2VBGxIDMLwRAuYJAJ9vwyx/iZ2iNjR6hk5vTT57pmViJgCeJ6sx QbkxNY3AbnTtRU7z2YqyWuU= =ryok -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sane values for replication
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've been experimenting with mysql replication, specifically error handling under varying error conditions. My tests are with 5.0.22 that comes with CentOS 5.0. My main concern is that I would like to lower the slave_net_timeout setting, but don't want to risk the integrity of replication stability. The default for the slave_net_timeout setting is 3600, which is 60 minutes. In my tests, setting it to 60 allows it to resume replication when the master comes back up, some time less than 60 seconds, which is desirable. So ... is slave_net_timeout = 60 a reasonable value to set on the slaves? Google was unable to find anything other than a blurb that says consider reducing it, but that blurb doesn't give a guideline for practical, real-world usage. Setting it to 10 doesn't seem all that terrible either, though at that point, any unforeseen severe spike in load resulting in mysql IO stalls could cause the slaves to trip that timer and trigger a network connection close and reopen. But I'm just speculating, I've not seen this nor have I ever heard of this. Why do I want to lower this setting? By lowering this setting to 60, I can achieve roughly the same time to resume replication between a clean shutdown and a hard (crowbar power) shutdown relative to when the mysql master comes back up. It also removes a step in resuming replication of STOP SLAVE; START SLAVE (which would kickstart the replication process) because it hits the timeout rather quickly. Of course this doesn't address potential errors with the Slave SQL Thread, but that is known. - -- Regards... Todd There are four boxes to be used in defense of liberty: soap, ballot, jury, and ammo. Please use in that order. --Ed Howdershelt Linux kernel 2.6.22-14-generic 7 users, load average: 0.02, 0.03, 0.00 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHWGeAY2VBGxIDMLwRAlZxAJ0eviFYfjIH33lmbrJGHGLiqTQ1YgCfUjlq mOlBBIOne3LUC6DWoAKemcw= =toIB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining Table Storage Engine Type on Crashed Table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, Dec 02, 2007 at 09:55:23AM +0530, Alex Arul Lurthu wrote: How about looking at the .frm file of the table. A couple of quickie tests shows something promising: strings table.frm | egrep 'MyISAM|InnoDB|engineA|engineB|etc' Unknonwn whether other engines obey the same rules and methods. - -- Regards... Todd Chris: grep 500 sendmail.mc undefine(`FAIL_MAIL_OVER_500_MILES')dnl Chris: just in case ... Linux kernel 2.6.22-14-generic 3 users, load average: 0.00, 0.00, 0.00 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHVw0rY2VBGxIDMLwRAgXTAJwJnbs7jSnXmEezsOhpfHZeITcTegCfYfhh LIv3YNplxWpzYjvluTeTKyk= =XdMs -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNODB database size
Hello I have inherited an INNODB database. I am new to MySQL and may not be describing my problem correctly so any suggestions or questions are welcome. I have data files named ib_data_00 through ib_data_14 all of which are 2.0G. I also have ib_data_15 which is 26G. I am receiving errors saying that the innodb space is full. I know that the space isn't full because it is still working. I am wondering how I can get a true size of the space used. I would like to get back into the 2G segments. Any suggestions. Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Grants problem
Hello Does anyone know why a user would show up in a select from user query but not in a show grants command. What I am trying to say is I tried SHOW GRANTS FOR 'user1'@'%' and received that there is no such grant defined for 'user1'... But when I run SELECT * FROM user WHERE User = 'user1' I do get results. Could the user have been added using an insert causing the SHOW GRANTS to fail? Any Ideas Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recover from plain text log
OK, I am ready to get flamed. I have done so much stuff wrong on this MySQL server that it finally came back to bite me. I've learned my lesson and hopefully someone here will find it in their heart to help me. I have a database which is updated quite often, running from a web server. There is no binary log. There is, however, a text query log going back to before my problem. The server had an I/O error and apparently corrupted the files for my database. I have a backup of the database from some time back, but need to recover the data since that time. I know there is a way to parse binary logs and recover from them, but is there a way to do the same for text logs? I think I can write a bash script to reformat the file into a series of queries, but I was hoping there was an easier way. Thanks so much, anyone willing to help. Todd Ellison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting information into multiple tables and problems
todd hewett wrote: mysql INSERT INTO BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER) mysql VALUES(36534); ERROR 1216: Cannot add a child row: a foreign key constraint fails Can you please post the output of: SHOW CREATE TABLE BOARD_SERIAL_NUMBER; mysql SHOW CREATE TABLE BOARD_SERIAL_NUMBER; + | BOARD_SERIAL_NUMBER | CREATE TABLE `BOARD_SERIAL_NUMBER` ( `BOARD_SERIAL_NUMBER_PK` int(10) unsigned NOT NULL auto_increment, `VCS_PO_NUMBER_PK` int(10) unsigned NOT NULL default '0', `BOARD_SERIAL_NUMBER` varchar(255) NOT NULL default '', PRIMARY KEY (`BOARD_SERIAL_NUMBER_PK`,`VCS_PO_NUMBER_PK`), KEY `BOARD_SERIAL_NUMBER_FKIndex1` (`VCS_PO_NUMBER_PK`), FOREIGN KEY (`VCS_PO_NUMBER_PK`) REFERENCES `productiontrack.VCS_PO_NUMBER` (`VCS_PO_NUMBER_PK`) ON DELETE CASCADE ) TYPE=InnoDB | +-+- Because you're not specifying a value for VCS_PO_NUMBER_PK in that INSERT statement, it will default to 0. It is a foreign key referencing the 'productiontrack.VCS_PO_NUMBER' table, which I bet doesn't have a row with VCS_PO_NUMBER_PK equal to 0. Hence the foreign key constraint fails. You need to either: 1/ Specify the relevant value for VCS_PO_NUMBER_PK in your insert statement, like INSERT INTO BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER, VCS_PO_NUMBER_PK) VALUES (36534, 12345); 2/ Remove the foreign key constraint 3/ Create a row in VS_PO_NUMBER where VCS_PO_NUMBER_PK is equal to 0 You should probably take option 1 if possible. # O.K. VCS_PO_NUMBER_PK is an auto increment primary key for VCS_PO_NUMBER How would I know that value... I am trying to inser the VCS_PO_NUMBER at this time. I guess what needs to be one in this case is: Insert the VCS_PO_NUMBER Search the table for VCS_PO_NUMBER_PK where VCS_PO_NUMBER IS a certain value and then use that info in the board serial number insert. Does that sound right? Thanks for the illumination. Todd 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]
Inserting information into multiple tables and problems
Howdy Folks I hope this is not too much information. I have mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386) I have multiple tables. All are innoD Tables. All AI on a Primary key integer All Columns are varchar 255 that are not keys. Table VCS_PO_NUMBER Has a one to many relationship with. Table BOARD_SERIAL_NUMBER Has a one to many relationship with the following tables: INSERT INTO BOARD_TYPE INSERT INTO RECIEPT_INSPECTION INSERT INTO PCB_MANUFACTURER I originally was taking form data and using many cold fusion use CFINSERT to write the information into the tables. And received this error: Error Executing Database Query. General error: Cannot add a child row: a foreign key constraint fails The error occurred in /home/httpd/vhosts/vistasystemsproductions.net/httpdocs/recieve_boards_actio n.cfm: line 10 8 : cfinsert datasource=productiontrack 9 :tablename=BOARD_SERIAL_NUMBER 10 : formfields=BOARD_SERIAL_NUMBER 11 : 12 : _ SQL insert into BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER) values ( (param 1) ) So now I thought I would wirite the insert statements in SQL instead of CF. I currently am failing with the following: mysql INSERT INTO VCS_PO_NUMBER (VCS_PO_NUMBER) VALUES(899234), - INSERT INTO BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER) VALUES(36534), - INSERT INTO BOARD_TYPE (BOARD_TYPE) VALUES(INPUT MODULE N12310), - INSERT INTO RECIEPT_INSPECTION (RECIEPT_INSPECTION_NAME) VALUES(BILlTHOMAS), - INSERT INTO PCB_MANUFACTURER (PCB_MANUFACTURER_COMPANY_NAME) VALUES(A); ERROR 1064: You have an error in your SQL syntax near 'INSERT INTO BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER) VALUES(36534), INSERT INTO' at line 2 Any help ideas recommendations are appreciated. Thanks, Todd
Re: Inserting information into multiple tables and problems
SNIP--- You need to end each INSERT INTO statement with a semicolon (;), not a comma (,). Also pressing enter after each one will allow you to easier diagnose any problems that might come up. - I have run the commands as single insert statements with the results below. The one with the child process error I do not know how to resolve. That is why I was trying to write one insert statement with a semicolon at the end. Two of the inserts seem to fail because MySQL sees my VALUES as unknown columns. Any other ideas out there? Thanks Todd mysql INSERT INTO VCS_PO_NUMBER (VCS_PO_NUMBER) VALUES(899234); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER) mysql VALUES(36534); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql INSERT INTO BOARD_TYPE (BOARD_TYPE) VALUES(INPUT MODULE N12310); ERROR 1064: You have an error in your SQL syntax near 'MODULE N12310)' at line 1 mysql INSERT INTO RECIEPT_INSPECTION (RECIEPT_INSPECTOR_NAME) VALUES(BILlTHOMAS); ERROR 1054: Unknown column 'BILlTHOMAS' in 'field list' mysql INSERT INTO PCB_MANUFACTURER (PCB_MANUFACTURER_COMPANY_NAME) mysql VALUES(A); ERROR 1054: Unknown column 'A' in 'field list'
RE: Inserting information into multiple tables and problems
mysql INSERT INTO BOARD_SERIAL_NUMBER (BOARD_SERIAL_NUMBER)
mysql VALUES(36534);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
Can you please post the output of:
SHOW CREATE TABLE BOARD_SERIAL_NUMBER;
mysql INSERT INTO BOARD_TYPE (BOARD_TYPE) VALUES(INPUT MODULE N12310);
ERROR 1064: You have an error in your SQL syntax near 'MODULE N12310)' at
line 1
Did you mean:
INSERT INTO BOARD_TYPE (BOARD_TYPE) VALUES('INPUT MODULE N12310')
###
It does appear that I did meanthat.
The Structure I should be using should be:
INSERT INTO TABLE_NAME (COLUMN_NAME) VALUES('VALUE_TO_INSERT_INTO_COLLUMN');
mysql SHOW CREATE TABLE BOARD_SERIAL_NUMBER;
+-+-
+
| Table | Create Table
|
+-+-
+
| BOARD_SERIAL_NUMBER | CREATE TABLE `BOARD_SERIAL_NUMBER` (
`BOARD_SERIAL_NUMBER_PK` int(10) unsigned NOT NULL auto_increment,
`VCS_PO_NUMBER_PK` int(10) unsigned NOT NULL default '0',
`BOARD_SERIAL_NUMBER` varchar(255) NOT NULL default '',
PRIMARY KEY (`BOARD_SERIAL_NUMBER_PK`,`VCS_PO_NUMBER_PK`),
KEY `BOARD_SERIAL_NUMBER_FKIndex1` (`VCS_PO_NUMBER_PK`),
FOREIGN KEY (`VCS_PO_NUMBER_PK`) REFERENCES
`productiontrack.VCS_PO_NUMBER` (`VCS_PO_NUMBER_PK`) ON DELETE CASCADE
) TYPE=InnoDB |
+-+-
+
1 row in set (0.02 sec)
Thanks again for your help in getting this right.
Todd
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]
Confused about privileges
I have created a table, sfyc and as root I issued the following: grant all on * to todd identified by 'my_password' with grant option; However, todd cannot access sfyc with mysql -u todd -p sfyc And the mysql db contains the following: user table +---+--+ | host | user | +---+--+ | % | todd | | linux | root | | localhost | root | +---+--+ db table +--+-+--+ | host | db | user | +--+-+--+ | %| mysql | todd | | %| test| | | %| test\_% | | +--+-+--+ tables_priv; +--+---+--++ | host | db| user | table_name | +--+---+--++ | %| mysql | todd | localhost | +--+---+--++ If I issue the following command, no changes take place in the above tables: grant all on sfyc to todd identified by 'my_password' with grant option; Do I need to do an insert SQL command to specifically enter the information? Todd -- Ariste Software 200 D Street Ext Petaluma, CA 94952 (707) 773-4523 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused about privileges
Chris et al - The MySQL online manual does show *.* to be used for global priviledges, but my MySQL book only used the *. My mistake! However, the online manual does not indicate (or I am missing it) what the use of * grants. Thank you for the help...it is now working and my DB, sfyc does show in the table, db. Todd Chris wrote: Did you run the statement witht he mysql database as the current database? If so , you're statement probably got converted to this: grant all on mysql.* to todd identified by 'my_password' with grant option; It seems like a logical thing The grant statement applying to all databases/tables should be: *.* Chris Todd Cary wrote: I have created a table, sfyc and as root I issued the following: grant all on * to todd identified by 'my_password' with grant option; However, todd cannot access sfyc with mysql -u todd -p sfyc And the mysql db contains the following: user table +---+--+ | host | user | +---+--+ | % | todd | | linux | root | | localhost | root | +---+--+ db table +--+-+--+ | host | db | user | +--+-+--+ | %| mysql | todd | | %| test| | | %| test\_% | | +--+-+--+ tables_priv; +--+---+--++ | host | db| user | table_name | +--+---+--++ | %| mysql | todd | localhost | +--+---+--++ If I issue the following command, no changes take place in the above tables: grant all on sfyc to todd identified by 'my_password' with grant option; Do I need to do an insert SQL command to specifically enter the information? Todd -- Ariste Software 200 D Street Ext Petaluma, CA 94952 (707) 773-4523 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Blew MySQL away by accident
Unfortunately, I blew MySQL away by replacing the MySQL control tables with others and not making a copy of the folder. My data is backed up, so that is not a problem, but I cannot reinstall MySQL and create the mysql directory with the control tables (e.g. user, db, host). I have run yum remove mysql and yum install mysql and yum install mysql-server and the control files are not recreated. Any suggestions? Todd -- Ariste Software 200 D Street Ext Petaluma, CA 94952 (707) 773-4523 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blew MySQL away by accident
Michael - Thank you for your patience! I had forgotten about that part of the install...been a long time I needed to do an install...nice part of Linux. However, I am trying to move my RH 9 system to Centos 4.1 and everything has gone well until I though I could just copy the mysql db into the new server. I looked in the manual to find out how to completely move a MySQL installation from one server to another and did not find any information (or overlooked it somehow). Moving the individual db's has not been a problem using the suggested mysqldump. Any suggestions? And thank you again! Todd Michael Stassen wrote: Todd Cary wrote: Unfortunately, I blew MySQL away by replacing the MySQL control tables with others and not making a copy of the folder. My data is backed up, so that is not a problem, but I cannot reinstall MySQL and create the mysql directory with the control tables (e.g. user, db, host). I have run yum remove mysql and yum install mysql and yum install mysql-server and the control files are not recreated. Any suggestions? Todd By MySQL control tables, it appears you mean the mysql db. Is it really the case that your backup process skips the mysql db? If so, you ought to fix that for the future. If you cannot restore your mysql db from backup, you can create a new one with installation defaults by running mysql_install_db. You should read the manual for the details http://dev.mysql.com/doc/mysql/en/unix-post-installation.html. In particular, note that the root users will have no password, and two anonymous users will be created. Be sure to follow the directions http://dev.mysql.com/doc/mysql/en/default-privileges.html to assign passwords to the root accounts. Also, you should probably delete the anonymous users, unless you have a very good reason to keep them. Michael -- Ariste Software 200 D Street Ext Petaluma, CA 94952 (707) 773-4523 -- 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.
Thanks Gleb, That was educational. Is there a way to log connections in such a way that is easy to tell how many connections were happening at one time? Thanks, Todd -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 12:26 AM To: mysql@lists.mysql.com Subject: Re: tracing connections to mysql. Hello. See: http://dev.mysql.com/doc/mysql/en/mysql-threads.html Please, next time answer to the list as well. Gleb thankyou. That was exactly what I thought I was looking for. It revealed two connections when logged in as admin through CLI: admin and the connection for the app that was having issues. Are connections the same as [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] -- 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]
4.1 client protocol?
I have an interesting problem. I have developed a custom mysql replication client that we use for monitoring our mysql database. Kinda like a poor man's trigger, but the power of an asynchronus trigger is very beneficial. Any way, I developed this client based on the 4.0 version of mysql. It appears that the network protocol has changed in 4.1. Is there any documentation of the 4.1 protocol, I figured the 4.0 protocol out from the code, but that was a very time consuming experience for me and i was hoping for a slightly higher level bit of documentation than c code. Does such a beast exist? --- Todd Gruben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cygwin and High School Lab
A couple of people have wondered why I'm using Cygwin with MySQL and seem to think I'm trying to use the Linux version of MySQL on Windows. I've installed the latest Windows version of MySQL. I'm using Cygwin as the launcher rather than the Windows command prompt, mostly because it's easier for me to set up environment variables correctly, to run SSH, Tomcat, etc. from a Unix-y prompt rather than a DOS prompt. Since when I type mysql it's actually mysql.exe that gets run, I don't think Cygwin could be the culprit in the crash, but I'm going to try running MySQL from the Windows command prompt today to see if that makes a difference. Thanks, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crash on Windows XP SP2 when not run as an administrator
I've tested mysql in my high school lab running from an administrator and from a student account, both from within the Cygwin terminal, and at the Windows command prompt. From the administrator account, it runs fine for hours. From the student account, it promptly (within 15 seconds or so) crashes with the same error each time. I managed to write down the error: -- Debug Assertion Failed! Program: mysqld.exe File: ftell.c Line: 50 Expression: stream != NULL --- This pretty successfully crashes the MySQL server so that if you're running the client, you get messages about the server being inaccessible and such. Is there something that non-administrators need access to that could be causing the problem? Could it be an SP2 issue? I have access to a server where I can set up student accounts so that we can do the tutorial I have planned, but I do, eventually, need to be able to run MySQL on my lab machines. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
GRANT usage on samp.* to 'permtest'@'localhost' identified by 'password'; GRANT insert, update, delete on samp.secrettable to 'permtest'@'localhost'; GRANT select, insert, update, delete on samp.Account to 'permtest'@'localhost'; This worked much better for me, though it's not a perfect solution (see below) This worked for me on 4.1.1a-alpha-nt-log. By explicitly denying access at the database level, the only tables I could see in my test database were the ones I specifically granted myself right to (even though there were others in the database). In my case I was able to see all the tables but could not select any data from the restricted table (mysql-server 4.0.18-5 from debian testing) I was blocked from running a SELECT query against secrettable. I could INSERT values but not DELETE them. I reason that this is because DELETE ... WHERE ... requires a SELECT to be run on the table to identify the rows to get rid of. The error I got when trying to delete was: ERROR 1143 (42000): SELECT command denied to user: 'permtest'@'localhost' for column 'col1' in table 'secrettable' Yes, this is the problem I ran into when trying to update any records in that table. So it appears to be insert only. Let us know if this works or what errors you get. Thanks for all your help. This may be the best solution available. Would it be possible to get it posted to a faq or mentioned in the docs? I imagine it's a situation that may come up frequently for users. Also, if you have any ideas on how one might perform updates on specific entries in that table without select privileges I'd be glad to hear them. (though it sounds like it may be impossible) Thanks again, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
On Monday 30 August 2004 4:08 pm, [EMAIL PROTECTED] wrote: I think that INSERT-only would be as good as we could hope for as I have been having a very hard time trying to think of a valid business reason why a user would be allowed to either UPDATE or DELETE rows from a table where they weren't allowed to even see the data. However I can think of several business reasons for an INSERT-only table: The situation I have is like this. End User enters data in web page. Web page data is encrypted using mysql's aes_encrypt and stored in remote DB (not the same machine as the web server). If End User wants to update the data the new data is encrypted and overwrites the old (End User cannot see what old data was). If the db user account gets compromised via the web server (username and/or password gets disclosed/cracked/etc), the data (encrypted or not) cannot be retrieved via that account, only overwritten. UPDATE secrettable SET column = 'value'; DELETE FROM secrettable; That may be possible, but I can't really see anywhere where that would be useful. Thanks. Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Thinking about this some more, it might be possible to achieve what my last email suggests by allowing select on the primary key column. Or would that set us back again? Thoughts? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Revoking select on a single table
Sorry to reply yet again, but I think I have the solution. After doing all we have said above I added grant select(ID_Num) on sampdb.secrettable to 'user'@'localhost' identified by 'password'; and of course updates and deletes are done via update secrettable set secretinfo=blah where ID_Num=2; So far as I've been testing this seems to work while preventing the user from ever selecting what's in the other secrettable columns. Thanks again! Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem running MySQL in high school lab
I'm trying to teach my students how to use MySQL, and have installed it on all the lab machines along with Cygwin. Originally, I had the permissions set wrong and my students couldn't start the server, but I fixed that, and now mysqld works fine. Unfortunately, if you then mysql -u root, after a rather short period of time, the program crashes and pops an error message to the screen. The message, which I should have written down but didn't, says that an assertion has failed in ftell.c (not sure about the filename, but the gist is right) and stream != NULL (that I'm sure of) and then the program dies. I don't have similar problems when I'm logged in as me (which has Administrator privileges) or the machine Administrator. It must be a permissions problem, but I don't know what I need to give the students to prevent it. The MySQL stuff on the local machines need not be secure, so I've given full access to all users in the entire /cygwin/usr/local/ directory and its subdirectories, which is where I installed MySQL and all the other packages we're going to be playing with. There are some kids in there who don't need the temptation of being logged in as an Administrator, and since we're going to be using JDBC later for which the MySQL server will need to be running almost constantly in the background, I'd like to get this resolved with the least amount of temptation. The lab is all Windows XP Professional machines, and the students log into a domain hosted by a server in another teacher's lab. Any ideas appreciated, Todd P.S. If you could cc me any replies, I'd appreciate it, since I read the list on digest. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data loading and foreign key constraints - help please
Thank you very much. I really appreciate your analogy to the waterfall. This helped me out tremendously. I was able to sort out the problem and all is now well! It appears that this wonderful little GUI tool the lets you create ER diagrams that auto-generate CREATE scripts assumes that you won't be including foreign keys in your entities. It expects you to build the relationship graphically and point out the primary keys, but it takes care of creating the foreign keys for you. My mistake. Since I put in foreign keys with the same name as the primary key in the related table, the GUI tool had no choice but to create new foreign keys with the same name appended with the number 1. The end result... total chaos. Fixed it though. I really appreciate your help. Todd On Aug 30, 2004, at 6:20 AM, [EMAIL PROTECTED] wrote: Foreign keys are used to enforce foreign relationships. Translated: Certain data values must exist in one table before another table can contain those values in columns that participate in foreign keys. Because data must first exist in one table before it can be used as data in another, you are required to fill in your FK-related structures from the top down. Start with your top-most table(s) in your structure (these are the ones that the foreign keys are referencing but have no foreign keys of their own). I think you said that you called them joblevel and jobtitile. Fill those tables with data. With those values in place you can create rows in the jobcode table that re-use certain values. You will not be able to assign a value to any row in jobcode that does not exist in either joblevel or jobtitle (for the columns that reference those tables as foreign keys). Keep filling in values in each layer of your structure until you get to the bottommost table(s). (These are the tables that FK reference other tables but have no tables that reference them.) It's kind of like a waterfall, you can't get data into some tables until it exists in other tables so it's like the data sort of trickles down the structure. (This analogy could also help to visualize how the use of the word cascade describes the auto-propagation of a delete or update to the dependent tables) HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Cranston-Cuebas [EMAIL PROTECTED] wrote on 08/29/2004 04:09:15 AM: I'm a total newbie to mySQL, but was hoping someone could answer a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows: # Create Table: 'Jobdesc' Job Description for Requisition # desccode: # jobdescription : # levelcode : (references JobCode.levelcode) # jobcode1: (references JobCode.jobcode) # jobcode : # titlecode : (references JobCode.titlecode) # CREATE TABLE Jobdesc ( desccode CHAR(8) NOT NULL UNIQUE, jobdescription MEDIUMTEXT NOT NULL, levelcode CHAR(2) NOT NULL, jobcode1 CHAR(8) NOT NULL, jobcodeCHAR(8) NOT NULL, titlecode CHAR(7) NOT NULL, PRIMARY KEY (desccode,jobcode), INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode), CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode) REFERENCES JobCode (titlecode,jobcode,levelcode) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=INNODB; This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description. One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to insert the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error: #1216 - Cannot add or update a child row: a foreign key constraint fails This happens if I just try to insert the desccode, the jobdescription, and jobcode data. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child rows. Can someone explain what I should do? Should I just change these fields of data into NULL? I'm literally just trying to populate the tables with enough data to run some test queries. Any suggestions? Todd -- 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
Data loading and foreign key constraints - help please
I'm a total newbie to mySQL, but was hoping someone could answer a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows: # Create Table: 'Jobdesc' Job Description for Requisition # desccode: # jobdescription : # levelcode : (references JobCode.levelcode) # jobcode1: (references JobCode.jobcode) # jobcode : # titlecode : (references JobCode.titlecode) # CREATE TABLE Jobdesc ( desccode CHAR(8) NOT NULL UNIQUE, jobdescription MEDIUMTEXT NOT NULL, levelcode CHAR(2) NOT NULL, jobcode1 CHAR(8) NOT NULL, jobcodeCHAR(8) NOT NULL, titlecode CHAR(7) NOT NULL, PRIMARY KEY (desccode,jobcode), INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode), CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode) REFERENCES JobCode (titlecode,jobcode,levelcode) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=INNODB; This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description. One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to insert the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error: #1216 - Cannot add or update a child row: a foreign key constraint fails This happens if I just try to insert the desccode, the jobdescription, and jobcode data. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child rows. Can someone explain what I should do? Should I just change these fields of data into NULL? I'm literally just trying to populate the tables with enough data to run some test queries. Any suggestions? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php script for new database and user
This is just my guess, I am still too new to know for sure, but I would say php can do what ever the user that is logging in can. phpMyAdmin will create databases, and it is just a PHP Script. Todd Hackathorn -Original Message- From: Elly Wisata [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 1:55 AM To: [EMAIL PROTECTED] Cc: 'BGLefty' Subject: RE: php script for new database and user I don't think php script can create database. But maybe I am wrong. If I am, please somebody make it straight. Create a database should do it through console or some kind like it. ~Elle~ -Original Message- From: W. D. [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 2:31 PM To: [EMAIL PROTECTED] Cc: BGLefty Subject: Re: php script for new database and user At 11:45 3/17/2004, BGLefty wrote: Is there a simple php script out there somewhere (or an example of how to do it) to create a new database, username and password? I would like to have a form in a password protected folder on my web site and be able to fill in those three variables to create a new database on my installation of mysql on my home computer (be able to do this from my office or another computer). It seems like a simple enough thing to do but I have been unable to find any examples... Any help would be appreciated. Thanks, BGLefty Do you have PHPmyAdmin installed? You can quite a bit of work fairly easily with it. Start Here to Find It Fast!T - http://www.US-Webmasters.com/best-start-page/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need to know how to export database to CSV format
Don't know if this will help, but this is how I offer web clients to save as excel in cold fusion: cfif #ViewInExcel# is Y cfcontent type=application/vnd.ms-excel /cfif the key is to change the content like above. I am not sure how to do this in PHP. But I am sure if you look around you will find it. Then just output the table, with each field broken up by td tags. No html tags or headings nothing but the table, TD, and TH tags need to be output. I have just recently started working in php, if I get a minute to put a example together in php I'll let you know. This will ask them if they want to save the file or open the file. The thing is you have to tell the client browser it is a type of file that it recognizes. Hope that helps, good luck. Todd Hackathorn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: need to know how to export database to CSV format I have a php website connecting to mysql database. How can i have a user export his database through the webpage to csv format? I would like the user to have a button he can press that would pop up a save as screen so he can save his database. I need to also be able that he can only save HIS OWN entries (PERSON_ID = ***) Can anyone direct me to a link that shows how you do that or explain to me how to do this? thanks very much Noam -- 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: Newbie - dependencies
When I upgraded to the newer version of mySQL I got the same errors, but the web site I down loaded the new rpms from had a rpm to put both versions of the dependency files on the system. Go here http://www.mysql.com/downloads/mysql-4.0.html then look in the Linux downloads about 1/4 the way down, It is labeled Libraries and header files, this put both the old version and the new version. When I found this error I searched all the forums and everyone had very complex(at lease for me) ways of recompiling mySQL. But, just installing this rpm fixed it for me. Good Luck, Todd Hackathorn -Original Message- From: Robert Ross [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 7:41 AM To: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi Victor, I tried that initially and thought I had installed everything - RH Enterprise has the option in the GUI to install mysql - what it doesn't tell you is that it is the client and not the server. When you (eventually) find this out and try to install the server, problems occur because the server should be installed before the client (I think). So you then uninstall both server and client, then re-install the server. If sucessful, you still get problems when you try to re-install the client as the GUI cannot find the server's headers and so will not install client software. So you go and find the rpm and try to install it yourself and get the dependencies problem. I think you get an idea of my frustration, and I have not even got to the part of trying to use mysql yet! Still, I will continue on...:-) Thanks for your input. Robb -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 March 2004 13:31 To: Robert Ross Cc: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi! anyway, why dont you use the graphic install utility redhat uses to install mysql?? it will resolve the dependencies for you =) PS: i think it's called redhat-config-packages Best Regards! On Thu, 2004-03-11 at 09:05, Robert Ross wrote: Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- 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]
Opinion about MaxDB, and Redhat
Hello, I only have one server available to be set up to handle a lot of data. I have looked through some documentation, and am a little confused. So I thought I would get the opinion of someone who has used this stuff. First will I see a performance different on a 2 GHz Intel w/ 500 MB Ram, running RedHat 9 if I set it to run in level 3 as compared to 5 that I am in now? Second I have tables in MS SQL Server that have over 50,000,000,000 records, will SQL Server handle that, and would it be a benefit to use MaxDB? I read through the MaxDB docs and am not sure when it is appropriate to use it instead. Also I am still learning mySQL and RedHat so don't need anything more complex that it has to be. I am used to just installing it and away I go, now I have all these different options. Thanks in advance. Todd Hackathorn
Iterating thru FullText Index
Is there any way to iterate thru the keys in a fulltext index? This issue
does not come up for other indexes since ORDER BY can achieve this:
For example if table t is indexed on part_id
SELECT DISTINCT part_id FROM t ORDER by part_id;
will return all keys in index order
Is there a way to do this for a fulltext index so that a query would
return a list of all words in the index - this could be helpful
to build dictionaries for ex.
Ex:
mysql CREATE TABLE articles (
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
- title VARCHAR(200),
- body TEXT,
- FULLTEXT (title,body)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO articles VALUES
- (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
- (NULL,'How To Use MySQL Efficiently', 'After you went through a
...'),
- (NULL,'Optimizing MySQL','In this tutorial we will show ...'),
- (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
- (NULL,'MySQL vs. YourSQL', 'In the following database comparison
...'),
- (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql SELECT * FROM articles
- WHERE MATCH (title,body) AGAINST ('database');
++---+--+
| id | title | body |
++---+--+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial| DBMS stands for DataBase ... |
++---+--+
2 rows in set (0.00 sec)
I'm looking for a way to get a result set that would look like
mysql phony query SELECT KEYS from articles USING INDEX (TITLE BODY);
MySql
Tutorial
DBMS
Efficiently
... etc...
This would be nifty.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scripting
Hello, I am new to Linux, and mySQL. I am currently a MsSQL Server 2000 and VB programmer, but trying to break away from that side. I have my database set up and working great, but I am really struggling on how to perform scheduled updates, and data imports. Can anyone please point me to a good resource to learn how to build a script, that can import data from an ODBC connection, and then maybe run multiple queries in the data one at a time. I don't know what language I want to use because I don't know enough about it. Once I find a way to do what I need, I will learn that language. I think there might be a way to use php as cron'd scripts, but I don't know. Also I looked at perl, or just bash in linux. I am used to being able to do anything I needed to with DTS packages and stored procedures. I had ask a similar question a while back, and still can't get going with this. So far the graphical packages for updates are the only thing I miss from SQL Server. Thanks in advance for any help. Todd Hackathorn
RE: Scripting
I think that will help, I'll give it a try, I have had a lot of trouble
finding good tutorials for bash also. Having been a windows person for so
long it seems very foreign to me. I see how you connect to the local data
base, does anyone know how to import data from another non mySQL database
server? To make two connections and pull data from one to the other?
Thanks,
Todd Hackathorn
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 04, 2004 8:30 AM
To: HACKATHORN, TODD (SWBT); [EMAIL PROTECTED]
Subject: Re: Scripting
Todd,
I'm also pretty new to MySQL and Linux but I have years of DB2 experience on
various platforms.
I've bash scripts very useful and cron them when I have something that I
want to automate, such as a daily backup of my databases. Here are some
examples of bash scripts that I use with MySQL.
This example, which is in file RI.sql, creates two related tables in the
MySQL 'tmp' database, populates them, and displays the results. NOTE: The
'Another Mistake' row will not be successfully inserted into the 'emp' table
because it has an invalid foreign key; there is no row for dept 'X99' in the
'dept' table.
---
use tmp;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
) Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
) Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values ('10', 'Christine', 'I', 'Haas','A00',5.00);
insert into emp values ('20', 'George', 'W', 'Bush', 'D11', 3.00);
insert into emp values ('30', 'Another', ' ', 'Mistake', 'X99',
15000.00);
insert into emp values ('40', 'John', ' ', 'Kerry', 'E21', 35000.00);
select * from dept;
select * from emp;
---
You can run the preceding script from the mysql prompt by preceding its name
with '\.'. For example:
mysql \. RI.sql
This script, called backup2.bash, is what I use to back up my databases each
night. It includes a comment showing the crontab entry I use to run it.
NOTE: We are using a Perl package called 'sendEmai'l instead of the
traditional 'sendmail' program.
---
#!/bin/bash
#This script makes a separate database-level backup of each of the current
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once per day
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash /home/rhino/MySQL/backup2.out
21; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t
[EMAIL PROTECTED] -u Daily Backup Report
USERID=foo; #The userid to use for creating the backup
PASSWORD=foopass; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d-%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backup
BACKUP_PATH=/home/rhino/MySQL/backup; #The directory into which the backup
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to
keep
echo ** REPORT BEGINS **;
echo
echo Program Name: $0
report_date=`/bin/date`
echo Report Date: $report_date;
echo
#Display the non-secret values used in this run.
echo Backup Values:;
echo Backup timestamp is $BACKUP_TIMESTAMP;
echo Backup path is $BACKUP_PATH;
echo Number of daily backups to keep = $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;
#For each database currently in MySQL, take a database-level backup, then
list any backups older than a certain number of day
for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
echo
echo Backing up database $ONE_DBNAME;
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TI
echo Deleting these old backups for this database...
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old
backups (i
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';';
#delete old
done
echo
echo ** REPORT ENDS **;
---
If you want to take a backup manually with this script (and omit the email
being sent to you), you can do this from the mysql prompt via:
mysql \. /home/rhino/MySQL/backup2.bash /home/rhino/MySQL/backup2.out
21
I think the biggest obstacle you're going to face in writing bash scripts is
the lack of good tutorials on it. There are umpteen bash references that
show you generic syntax and tell you the terminology used by bash but darned
few decent tutorials to show you how to write bash scripts. Some people can
do anything
updating tables in MySQL
Hello, Sorry if this is a obvious question, but I am new to mySQL, and PHP. I am used to working with MS SQL Server 2000 and coldfusion. In SQL server I can build DTS packages and schedule them to update the data in my tables with different queries, and to import current data from other databases. Is there a way I can do this in mySQL? I found how I can schedule scripts, but am not sure how to go about making a script do queries, or how to import data from a other data source. I could write a script to insert each record one at a time, but that seems like a lot of work. I work with fairly large tables. I am new to both Linux, and mySQL, but do have a decent amount of experience in other SQL system. I am using RedHat 9, mySQL Ver 12.22 Distrib 4.0.18, for pc-linux (i686). Thanks in advance, Todd
Ignore Replication Temp Tables
Is there any way to disable replication of all temp tables using replicate-ignore-table or some other means? The names of the temp tables are generated randomly by a script. Thanks Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export in XML
Is there a way to export/import MySQL tables in XML format? Todd -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export in XML
Jay - I am not an expert with XML, however I use it with Delphi and Delphi's TClientDataset. In this usage, I use the builtin SaveTo and LoadFrom methods. Todd Jay Blanchard wrote: [snip] Is there a way to export/import MySQL tables in XML format? [/snip] Well Todd, it is a little more complex than this. How much do you know about XML? -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export in XML
John - Right under my nose! Thanks Todd John Griffin wrote: Look at http://www.mysql.com/doc/en/mysql.html. The -X option may give you what you need. -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED]] Sent: Monday, December 01, 2003 9:57 AM To: Todd Cary; Jay Blanchard Cc: [EMAIL PROTECTED] Subject: RE: Export in XML I think that to answer Todd's question, no there is no inherent way in MySQL to export results as XML the way that sqlserver and oracle do. Another solution that comes to mind (if you're using java) is to write a simple class that converts a result set to an array of hashmaps (row in array is row of data, hashmap for column name - data value mapping), then send that to either castor (pre-java 1.4) or to the xml- object APIs (1.4 on). -Original Message- From: Todd Cary [mailto:[EMAIL PROTECTED]] Sent: Monday, December 01, 2003 9:48 AM To: Jay Blanchard Cc: [EMAIL PROTECTED] Subject: Re: Export in XML Jay - I am not an expert with XML, however I use it with Delphi and Delphi's TClientDataset. In this usage, I use the builtin SaveTo and LoadFrom methods. Todd Jay Blanchard wrote: [snip] Is there a way to export/import MySQL tables in XML format? [/snip] Well Todd, it is a little more complex than this. How much do you know about XML? -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- inline: NewLogo.gif
Re: Export in XML
Dan - Or from my place, "It pays for me to read the docs - thoroughly". Oh! Docs! What are they? :-[ Todd Dan Greene wrote: The most guarenteed way to have a feature in a product pointed out is to make a public statement that it's not available :) keyboard in mouth, Dan Greene -Original Message- From: John Griffin [mailto:[EMAIL PROTECTED]] Sent: Monday, December 01, 2003 10:49 AM To: Dan Greene; Todd Cary; Jay Blanchard Cc: [EMAIL PROTECTED] Subject: RE: Export in XML Look at http://www.mysql.com/doc/en/mysql.html. The -X option may give you what you need. -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED]] Sent: Monday, December 01, 2003 9:57 AM To: Todd Cary; Jay Blanchard Cc: [EMAIL PROTECTED] Subject: RE: Export in XML I think that to answer Todd's question, no there is no inherent way in MySQL to export results as XML the way that sqlserver and oracle do. Another solution that comes to mind (if you're using java) is to write a simple class that converts a result set to an array of hashmaps (row in array is row of data, hashmap for column name - data value mapping), then send that to either castor (pre-java 1.4) or to the xml- object APIs (1.4 on). -Original Message----- From: Todd Cary [mailto:[EMAIL PROTECTED]] Sent: Monday, December 01, 2003 9:48 AM To: Jay Blanchard Cc: [EMAIL PROTECTED] Subject: Re: Export in XML Jay - I am not an expert with XML, however I use it with Delphi and Delphi's TClientDataset. In this usage, I use the builtin SaveTo and LoadFrom methods. Todd Jay Blanchard wrote: [snip] Is there a way to export/import MySQL tables in XML format? [/snip] Well Todd, it is a little more complex than this. How much do you know about XML? -- -- 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] -- inline: NewLogo.gif
Installing MySQL on Fedora
I tried installing MySQL on Fedora and all seemed to go fine, but there is no mysqladmin. Have I missed something here? And I am not sure where the mysql executable resides. The version is MySQL-3.23.58-1.i386.rpm Todd -- inline: NewLogo.gif
Re: Getting the value of a row you just inserted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 09 October 2003 06:06 pm, Dan Anderson wrote: : Is it possible to SELECT the id (indexed PRIMARY KEY AUTO_INCREMENT) of : a row that was just inserted? I'm using PHP, I don't know if that makes : a difference? : : Something like: : : INSERT INTO foo (bar) VALUES (bar); : SELECT last_insert(id) FROM foo; : : Also, are there any problems with using this in an environment where : you're forking processes? : : Thanks in advance, : : -Dan In php you can use mysql_insert_id() to get the id of the auto_increment column. See http://www.php.net/manual/en/function.mysql-insert-id.php todd[1] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/hd7v9xvMmfxCaFkRAsoZAJ9eL5aQf0dCHwY/nFdH5vQIny0BrgCgrcI3 V64M7/8+BnyHjJLlzYvG1C4= =MFdB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How identify long running query etc?
Using MySQL and InnoDB, are there system tables or other tools that I can use to see which statements that are executing have consumed the most CPU time and find out which user, transaction and/or connection those statements belong to? If I identify a user that is causing a problem is there a way to disconnect that user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is the table cache used with InnoDB?
I cannot tell from the Reference Manual whether the table cache is used with the InnoDB database engine or not? It sounds like it only applies to tables that are stored in separate disk files. Is the table cache used by InnoDB? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do InnoDB rollback segments expand dynamically?
Heikki, You can get the new driver at http://codecentral.borland.com/codecentral/ccweb.exe/author?authorid=163237 I have not tried it to see if the multiple connection problem is fixed. I am new to MySQL and have not tried to use it with dbExpress yet. Bill -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:44 PM To: [EMAIL PROTECTED] Subject: Re: Do InnoDB rollback segments expand dynamically? Bill, - Original Message - From: Bill Todd [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 4:27 AM Subject: Do InnoDB rollback segments expand dynamically? Using InnoDB with an autoextend tablespace, if I start a transaction that results in many record versions, will the rollback segments grow dynamically and force the tablespace to grow dynamically to provide the required room for record versions in the rollback segments? yes. I have a question, too: if you are the Bill Todd who posts to the Borland newsgroups, what is the status of the new DBExpress driver for MySQL? The problem in old drivers was that they established a new connection for each individual SQL statement. Transactions and several other MySQL features did not work because of that. I saw some 4 weeks ago a Borland engineer mention that this is now fixed in a beta release of the driver. Is this so? Bill Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.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]
How much memory does MySQL need?
I know, I know, with database servers more is always better.g However, assume I need to deploy MySQL using InnoDB to multiple sites. Hardware cost is a significant consideration. Is there any information available on MySQL's memory requirements? Most database vendors give you something as a starting point but I could not find any information in the MySQL Manual. Can someone point me to a source? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BDB transaction isolation levels?
What transaction isolation level(s) does BDB support? I can't find any information on BDB transactions section 7.6 of the manual. Thanks, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difference between Serializable and Repeatable Read with InnoDB
Since InnoDB does not allow phantom reads with Repeatable Read isolation (which are allowed in the ANSI SQL definition of Repeatable Read) what is the difference between these two isolation levels. Is it just serialization and that is all? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do InnoDB rollback segments expand dynamically?
Using InnoDB with an autoextend tablespace, if I start a transaction that results in many record versions, will the rollback segments grow dynamically and force the tablespace to grow dynamically to provide the required room for record versions in the rollback segments? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
