SV: Determine version of *.frm, *.MYD and *.MYI
Well... It did document it In my wiki... which is gone... Nils -Oprindelig meddelelse- Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: fr 05-05-2006 21:57 Til: Nils Lastein Cc: mysql@lists.mysql.com Emne: Re: Determine version of *.frm, *.MYD and *.MYI No backups? And you compiled it yourself with no records of how you compiled it or what version you used? what if you want to compile a new version with the same flags, how would you remember how to do that? If those don't help, trial and error is the only way. And you know it's not 4.1.19, that was JUST released. And you just eliminated one other one. Only 17 left to go. I wish I could be more helpful, but when you don't backup or document, that's what happens. :( -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > I know it a 4.1... But as I compiled it my self it is not so easy to figure > it out And it might take a while to trial-n-error all 4.1.x > > Nils > > > -Oprindelig meddelelse- > Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sendt: fr 05-05-2006 20:28 > Til: Nils Lastein > Cc: mysql@lists.mysql.com > Emne: Re: Determine version of *.frm, *.MYD and *.MYI > > > You don't have ANY idea what branch it was created with? 3.2x, 4.0, > 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your > dept and see what it's running (assuming there's no standards doc, or > sysadmin to ask, etc). > > Do you remember around when the time was that you last > installed/upgraded mysql on the box? If so you might be able to > figure out which release it was. > > You can try to use something in the same branch -- if it was > 4.1.something, try the latest in the 4.1 branch. > > Where were your backups stored? If you used mysqldump it logged the > server version at the top of the output file. > > -Sheeri > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files > > from the disk. When putting these files into another mysql server I get: > > > > mysql> select * from validate; > > ERROR 1033 (HY000): Table './mydb/validate' was created with a different > > version of MySQL and cannot be read > > > > Unfortunately I'm unable to access the disk anymore, so I cannot see > > what version of the server generated these. > > > > How do I do that? > > > > Nils > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > >
single database ... many aplications
Hi, as newbie and with some trubles understanding English language, i have a question that I dont know howto put on search engines (I dont know technical keywords for my particular case). This is why I post here. This is the whole picture: I have four programs running on my place, Freeradius http://www.freeradius.org/ metadot web portal http://www.metadot.com/index_static.html mail toaster con qmail http://www.tnpi.biz/internet/mail/toaster/ y Xpanel http://www.xpanel.com/ each of them use MySQL and they has different databases and different tables...this four applications handle same usernames and password for same users, I mean same user has an e-mail account, and uses same username and password for authenticate freeradius modem access for a protmaster3, same username and password for login our metadot portal, and . Same thing for access Xpanel virtual hosting for their home page. How can I use just one shared common database for use of all of my portal applications? Is this possible? It would be nice if I have a single login feature for all is MySQL related topic or is particular hack for each program? Thank you for share any experience related or knowledge about this doubt. best regards from mexico, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlmanager logging?
Hi, you sort of suggest to look further beyond file permission problems but if I may: > drwx--x--x 2 mysql mysql 1752 2006-05-01 09:33 mysql doesn't look quite right, lack of read permissions to group/other. Thanks, Michael Izioumtchenko -Original Message- From: sheeri kritzer <[EMAIL PROTECTED]> To: Duzenbury, Rich <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Fri, 5 May 2006 16:00:05 -0400 Subject: Re: mysqlmanager logging? su - mysql touch /var/lib/mysql/mysqlmanager.log see if that helps; maybe having the file there will kick it into gear. Is mysqlmanager actually running? Is there a pid file? -Sheeri On 5/5/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > > > > -Original Message- > > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > > Sent: Thursday, May 04, 2006 3:12 PM > > To: Duzenbury, Rich > > Cc: mysql@lists.mysql.com > > Subject: Re: mysqlmanager logging? > > > > Can the program write to /var/lib/mysql/mysqlmanager.log? > > check permissions. > > > > # su - mysql > [EMAIL PROTECTED]:~> whoami > mysql > [EMAIL PROTECTED]:~> cd /var/lib/mysql > [EMAIL PROTECTED]:~> touch foo.txt > [EMAIL PROTECTED]:~> ls -al > total 3 > drwxr-xr-x 4 mysql mysql 120 2006-05-05 13:25 . > drwxr-xr-x 56 root root 1472 2006-05-05 04:25 .. > -rw-r--r-- 1 mysql mysql 0 2006-05-05 13:25 foo.txt > drwx--x--x 2 mysql mysql 1752 2006-05-01 09:33 mysql > drwxr-xr-x 2 mysql mysql 48 2006-05-01 09:33 test > > I don't think there is a permission problem. > > Any further ideas? > > Thanks. > > Regards, > Rich > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ Try the New Netscape Mail Today! Virtually Spam-Free | More Storage | Import Your Contact List http://mail.netscape.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Memory Problem causing mysql to crash
Dobromir, you are running a 32-bit operating system. Then the size of the mysqld process is limited to 2 GB, or at most to 4 GB. The amount of total RAM 8 GB does not help here, since 2^32 = 4 G. You should reduce the key_buffer_size or innodb_buffer_pool_size in my.cnf. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: ""sheeri kritzer"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, May 05, 2006 10:50 PM Subject: Re: InnoDB Memory Problem causing mysql to crash Well, according to my calculations: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB (I used the default binlog_cache_size value of 32K plus your settings) MySQL could use up to 4.991913 G of memory. Shouldn't be a problem, unless of course your 8G of machine is running something other than MySQL. Is it? Because the fact that it could not allocate memory means that something was trying to use memory that didn't exist Did MySQL dump a core file? Did you follow this advice? You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap= for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html Did you read the man page? The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Also, did you try to look at your slow query logs to see if there was some kind of query hogging memory? What about backups running at the same time? I'll note that you maxxed out your connections, which shouldn't cause a crash, but might indicate that your server tuning is not up-to-date with your actual usage. Are your data and logfiles are on a diffferent partitions? We had problems with one machine where the data and logfiles were on the same partition, and it would crash -- we moved to a machine that was the same except for the different OS partitions, and it didn't crash! We figure the disk seeking just killed the OS so it segfaulted the mysql process. -Sheeri On 5/4/06, Dobromir Velev <[EMAIL PROTECTED]> wrote: Hi, I'm trying to resolve why InnoDB is crashing. It happened twice for the l= ast month without obvoius reason Any help will be appreciated. Dobromir Velev My Server is Red Hat Enterprise Linux ES release 3 (Taroon Update 7) 2.4.21-32.0.1.ELs= mp Dual 3.2 GHz Intel Xeon 8 GB RAM with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives my.cnf settings innodb_buffer_pool_size=3D2000M innodb_additional_mem_pool_size=3D20M innodb_log_file_size=3D150M innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D0 innodb_lock_wait_timeout=3D50 key_buffer_size=3D1000M read_buffer_size=3D500K read_rnd_buffer_size=3D1200K sort_buffer_size=3D1M thread_cache=3D256 thread_concurrency=3D8 thread_stack=3D126976 myisam_sort_buffer_size=3D64M max_connections=3D600 The error log shows the following message: InnoDB: Fatal error: cannot allocate 1048576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 2263507272 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly buil= t, or misconfigured. This error can also be caused by malfunctioning hardwar= e. We will try our best to scrape up some info that will hopefully help diag= nose the problem, but since we have already crashed, something is definitely w= rong and this may fail. key_buffer_size=3D1048576000 read_buffer_size=3D507904 max_used_connections=3D600 max_connections=3D600 threads_connected=3D473 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = =3D 1935995 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap= for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=3D(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you se
RE: blank user names in user table (SOLVED)
> -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Friday, May 05, 2006 2:37 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: blank user names in user table > > Rich, > > anonymous access means that ''@host has access. That is, > "blank" at host, as opposed to [EMAIL PROTECTED] > > To see if anonymous access is allowed, at the command prompt type: > > mysql -u asdf This doesn't work. If I grant all on *.* to ''@workstation.domain.local Then, I find a record in mysql.user with host=workstation.domain.local, user is blank, password is blank. If I then connect to the server from my workstation using the MySQL query browser and leave the user id and password blank, it connects and works. After much horsing around, I can make it work as: mysql --user=anything --host=host -p (Press enter at the password prompt) Silly me, I thought I had to somehow supply a blank value for the user name. Instead, I have to simply supply an empty password. Thanks for your help! Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible to select from multiple databases?
Bing Du wrote: > I have two separate databases that I need to query data from. In the > following SELECT statement, 'title' and 'db_entry_name' are in database1, > and 'projectID' is in database2. If they were in one database, this > SELECT should work. How should I tweak it to get data from both database1 > and database2? Is that even possible in single SELECT? > > SELECT title, db_entry_num, projectID FROM account_info, ResearchProjects > WHERE ResearchProjects.IDNo = $idno AND account_info.db_entry_num = > ResearchProjects.projectID This is easy as long as the dbs are on the same machine: SELECT * from db1.table1, db2.table1... Eric -- Eric Braswell Web Manager MySQL AB Cupertino, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: possible to select from multiple databases?
Hello, Have you taken a look at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html It is a storage engine that accesses data in tables of remote databases rather than in local tables. Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Bing Du [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 4:09 PM To: mysql@lists.mysql.com Subject: possible to select from multiple databases? Hello, I have two separate databases that I need to query data from. In the following SELECT statement, 'title' and 'db_entry_name' are in database1, and 'projectID' is in database2. If they were in one database, this SELECT should work. How should I tweak it to get data from both database1 and database2? Is that even possible in single SELECT? SELECT title, db_entry_num, projectID FROM account_info, ResearchProjects WHERE ResearchProjects.IDNo = $idno AND account_info.db_entry_num = ResearchProjects.projectID Thanks in advance, Bing -- 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]
possible to select from multiple databases?
Hello, I have two separate databases that I need to query data from. In the following SELECT statement, 'title' and 'db_entry_name' are in database1, and 'projectID' is in database2. If they were in one database, this SELECT should work. How should I tweak it to get data from both database1 and database2? Is that even possible in single SELECT? SELECT title, db_entry_num, projectID FROM account_info, ResearchProjects WHERE ResearchProjects.IDNo = $idno AND account_info.db_entry_num = ResearchProjects.projectID Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex <[EMAIL PROTECTED]> wrote: This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- 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: importing a dumpfile from with the mysql client
Ah, I get it.I don't know if there's a way to do that, but why not just put the SQL statements in the file? -Sheeri On 5/5/06, George Law <[EMAIL PROTECTED]> wrote: I think what he is saying is that be began the "transaction" in a command line client session from one location but was not able to give the > mysql> SET FOREIGN_KEY_CHECKS = 1; > mysql> COMMIT; commands in the same session. (ie - I owe, I owe, its off to work I go :) ) This is a good question, one I have wondered about myself. Is there a way in mysql to "attach" to session to issue a commit? -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 3:02 PM To: Luke Vanderfluit Cc: MySQL List Subject: Re: importing a dumpfile from with the mysql client On 5/4/06, Luke Vanderfluit <[EMAIL PROTECTED]> wrote: [snip] > I started this process remotely then went to the site to finish it. > But when the dump finished (several hours later) I was not able to > execute the following commands from my original location. > > mysql> SET FOREIGN_KEY_CHECKS = 1; > mysql> COMMIT; What do you mean "you were not able"? Did you get an error? Was the server hung? Did the keyboard stick to make you unable to type the commands? > > My question is: > Since the import completed the database has grown in size and been > backed up etc. > Yet from the original session I have not executed those 2 commands. > > Is it safe to execute them? Or would executing them cause corruption or > other unforseen stuff? > Is it unnecessary to execute them? > Is it safe to kill that original mysql session? Those 2 commands, in and of themselves, aren't dangerous. It's what's in the script that could be the problem. However, since you really didn't mention what error you got, it's not easy to figure out how to fix it. It depends what tables you're running these on. If you're running them on MyISAM tables, deadlocking cannot happen. Then again, transactions are meaningless too. If you're running on InnoDB you have the possibility of deadlocking, but MySQL is pretty good about avoiding that. If other users/applications are writing to the SAME data then there's the possibility of inconsistent data, but I wouldn't expect corruption. if you post what errors you actually got, I might be able to help. Also, are you using the same db and tables that other apps/users are using? Does the dumpfile contain "DROP TABLE IF EXISTS" commands? -Sheeri > > -- > Luke Vanderfluit. > Analyst/Programmer. > Internode Systems Pty. Ltd. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching a large table
well, you'd still have to use limit and offset with your search table would you store a different table for each unique query? That sounds like a lot of [temporary?] tables. are you doing ore than 3-4 table joins on ths one fulltext search query? If not, it's probably more work. If your queries are well-written and your indexes are good, there's no reason you'd need to make a search table unless you're doing some kind of data analysis with a warehouse. In my opinion, it's a waste of time, because paging queries with limit and offset will be fast enough. We do that on our site, and fulltext searching rarely shows up in our slow query logs. But this advice is worth exactly what you're paying for it, so there you have it. -Sheeri On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: Hi Sheeri: Yes, you are misunderstanding my question. I certainly know how to limit my resultset to certain rows. I'm asking more about the effiencency of searching large volumes of data. Is making a search table like vBulletin does a good mechanism to avoid resource contention on the main table, or is that more work than what it's worth in my case? -- Steve - Web Applications Developer http://www.sdwebsystems.com On Fri, May 5, 2006 2:35 pm, sheeri kritzer said: > Sounds like you want LIMIT and OFFSET -- > > everything after my name and before your post is copied from the doc at > > http://dev.mysql.com/doc/refman/4.1/en/select.html > > (or am I misunderstanding your question?) > -Sheeri > > The LIMIT clause can be used to constrain the number of rows returned > by the SELECT statement. LIMIT takes one or two numeric arguments, > which must both be non-negative integer constants (except when using > prepared statements). > > With two arguments, the first argument specifies the offset of the > first row to return, and the second specifies the maximum number of > rows to return. The offset of the initial row is 0 (not 1): > > SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 > > To retrieve all rows from a certain offset up to the end of the result > set, you can use some large number for the second parameter. This > statement retrieves all rows from the 96th row to the last: > > SELECT * FROM tbl LIMIT 95,18446744073709551615; > > With one argument, the value specifies the number of rows to return > from the beginning of the result set: > > SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows > > In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. > > For prepared statements, you can use placeholders (supported as of > MySQL version 5.0.7). The following statements will return one row > from the tbl table: > > SET @a=1; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; > EXECUTE STMT USING @a; > > The following statements will return the second to sixth row from the tbl > table: > > SET @skip=1; SET @numrows=5; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; > EXECUTE STMT USING @skip, @numrows; > > For compatibility with PostgreSQL, MySQL also supports the LIMIT > row_count OFFSET offset syntax. > > On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: >> All: >> >> I am developing a search engine and using MySQL as the backend database >> management system. Under normal circumstances, when users search >> through >> large volumes of records on a search engine site, the results are broken >> down into pages. When a user clicks on the 'Next' link, the system will >> re-query the database and return those records that pertain to that page >> (records 10 through 20, perhaps). This, as you can plainly see, >> requires >> the application to re-query the same data from the same table each time >> the user clicks on a 'Next' or 'Previous' link. I would imagine that >> this >> may lead to some resource contention on that main table, especially when >> many users are using the system simultaneously. >> >> I've never seen vBulletin's code, but I have been able to determine from >> careful analysis (and testing) that they employ a search table, of >> sorts, >> that contains the returned records from a search. So, when a user of >> the >> system submits a search query, the system returns the records and throws >> them into a separate search table, identified by a sequential primary >> key >> field. Then, the system uses that search table to display the >> appropriate >> records according to the respective search identifier (referenced in the >> URL), never touching the main table again until a brand new search is >> performed. >> >> This seems to be a pretty good way to facilitate large text-based >> searches. Are there any other mechanisms that can be used to build a >> powerful, yet quick and light on resources, search system? Is a >> fulltext >> index the best way to achieve maximum performance with this kind of >> search? >> >> Thanks. >> >> -- >> Steve - Web Applications Developer >> http://www.sdwebsystems.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubs
Re: Coded fields
On 5/5/06, John Heim <[EMAIL PROTECTED]> wrote: In fact, I would not gain clarity by using the 5-char codes that have been imposed upon me. Freshman='10'. Sophomore='20'. There's even a '00' code and a '05' for some status less than Freshman. Imposed upon you? You're the DBA, right, not an end-user of this data? Data values are not "imposed upon you". Perhaps they're "introduced into an existing system" and "are not compatible". Honestly, I'd change the system to conform to theirs. Or add their codes to your code table and change it to varchar(5). Your system has been proven to be not flexible enough! I don't think you can get "them" to change their system, and it obviously works for "them". You might gain clarity, believe it or not. Folks who work with this data probably know that 10 is Freshman, and 20 is Sophomore, and 00 is "someone who just applied" or whatever. What's so difficult about changing your codes table so the code_key is a varchar(5)? How is that more work than translating the 5-char codes to 1-char codes and sticking with what I've got. ?? If somebody had given me a dramitcally better way to handle coded fields than what I'm already doing, I might have been willing to re-write everything. But right now, I'm leaning more toward the "if it ain't broke, don't fix it" theory. What happens when you need more than 52 codes? And, um, it IS broke, because the data values don't fit in it. :) If you want to change it to conform to your standards, that's fine, but it sounds like it's you, a DBA, who created this DB (or inherited it) up against a large registrar system, and I'd vote that the latter had more schema design done. No offense, I've worked for a university and I know how fubar'd those things can be. Changing it to your way isn't a BAD idea. I'm not saying "Run screaming!" I'm just saying what I'd do. And of course I have no idea how much code/whatever you'd need to change. But if you changed your system to gracefully accept new codes by having your code_key be varchar(5) that sounds like a win all around. *shrug* Somebody suggested (via private email I think) that I use enum. I had mentioned in my original message that I didn't want to do that because some of the coded fields allow end-users to add/remove codes. But he pointed out that that could be done via alter table commands. That's kind of scary to me. I dunno. Yeah, I've been convinced that altering ENUMs is a bad idea too. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlmanager logging?
None from me, sad to say. :( -Sheeri On 5/5/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Friday, May 05, 2006 3:00 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: mysqlmanager logging? > > su - mysql > touch /var/lib/mysql/mysqlmanager.log > > see if that helps; maybe having the file there will kick it into gear. > > Is mysqlmanager actually running? Is there a pid file? > > -Sheeri LX03:~ # ps -ef | grep mysql mysql18706 1 0 May04 ?00:00:00 /usr/sbin/mysqlmanager --user=my sql --pid-file=/tmp/manager.pid And, both my instances are up. Excerpt from /etc/my.cnf: [mysql.server] use-manager [manager] socket=/tmp/manager.sock pid-file=/tmp/manager.pid monitoring-interval=30 LX03:/tmp # ls -al /tmp | grep manager -rw-rw 1 mysql mysql 6 May 5 15:30 manager.pid srwxrwxrwx 1 mysql mysql 0 May 5 15:30 manager.sock Creating the mysqlmanager.log file does not seem to have helped. It's still empty. Any further advice? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlmanager logging?
> -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Friday, May 05, 2006 3:00 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: mysqlmanager logging? > > su - mysql > touch /var/lib/mysql/mysqlmanager.log > > see if that helps; maybe having the file there will kick it into gear. > > Is mysqlmanager actually running? Is there a pid file? > > -Sheeri LX03:~ # ps -ef | grep mysql mysql18706 1 0 May04 ?00:00:00 /usr/sbin/mysqlmanager --user=my sql --pid-file=/tmp/manager.pid And, both my instances are up. Excerpt from /etc/my.cnf: [mysql.server] use-manager [manager] socket=/tmp/manager.sock pid-file=/tmp/manager.pid monitoring-interval=30 LX03:/tmp # ls -al /tmp | grep manager -rw-rw 1 mysql mysql 6 May 5 15:30 manager.pid srwxrwxrwx 1 mysql mysql 0 May 5 15:30 manager.sock Creating the mysqlmanager.log file does not seem to have helped. It's still empty. Any further advice? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
I think you're thinking of mysqlcheck: (from the documentation) -check-upgrade, -g Invoke CHECK TABLE with the FOR UPGRADE option to check tables for incompatibilities with the current version of the server. This option was added in MySQL 5.0.19. -Sheeri On 5/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: This is right off the top of my head so I might be wrong! Does myisamchk not return the mysql version that the table was created with, if you use a very verbose option to check the *.MYI files? You could make a copy of your tables and experiment with myisamchk on the copies, see if that throws any light on things. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 5 May 2006, sheeri kritzer wrote: > To: Nils Lastein <[EMAIL PROTECTED]> > From: sheeri kritzer <[EMAIL PROTECTED]> > Subject: Re: Determine version of *.frm, *.MYD and *.MYI > > No backups? > > And you compiled it yourself with no records of how you compiled it or > what version you used? what if you want to compile a new version with > the same flags, how would you remember how to do that? > > If those don't help, trial and error is the only way. And you know > it's not 4.1.19, that was JUST released. And you just eliminated one > other one. Only 17 left to go. I wish I could be more helpful, but > when you don't backup or document, that's what happens. :( > > -Sheeri > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > > > > I know it a 4.1... But as I compiled it my self it is not so easy to > > figure > > it out And it might take a while to trial-n-error all 4.1.x > > > > Nils > > > > > > -Oprindelig meddelelse- > > Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] > > Sendt: fr 05-05-2006 20:28 > > Til: Nils Lastein > > Cc: mysql@lists.mysql.com > > Emne: Re: Determine version of *.frm, *.MYD and *.MYI > > > > > > You don't have ANY idea what branch it was created with? 3.2x, 4.0, > > 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in > > your > > dept and see what it's running (assuming there's no standards doc, > > or > > sysadmin to ask, etc). > > > > Do you remember around when the time was that you last > > installed/upgraded mysql on the box? If so you might be able to > > figure out which release it was. > > > > You can try to use something in the same branch -- if it was > > 4.1.something, try the latest in the 4.1 branch. > > > > Where were your backups stored? If you used mysqldump it logged the > > server version at the top of the output file. > > > > -Sheeri > > > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > After a disk crash I managed to save the *.frm, *.MYD and > > > *.MYI-files > > > from the disk. When putting these files into another mysql > > > server I get: > > > > > > mysql> select * from validate; > > > ERROR 1033 (HY000): Table './mydb/validate' was created with a > > > different > > > version of MySQL and cannot be read > > > > > > Unfortunately I'm unable to access the disk anymore, so I > > > cannot see > > > what version of the server generated these. > > > > > > How do I do that? > > > > > > Nils -- 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: How to rename a DB
I think the correct way... as planned for the 5.1 release is as follows: -- -- Start renaming database named "old_db" into "new_db" CREATE DATABASE new_db; ALTER TABLE old_db.table_1 RENAME new_db.table1; ALTER TABLE old_db.table_2 RENAME new_db.table2; ALTER TABLE old_db.table_N RENAME new_db.tableN; DROP DATABASE old_db; -- -- End renaming database -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer
Re: Determine version of *.frm, *.MYD and *.MYI
This is right off the top of my head so I might be wrong! Does myisamchk not return the mysql version that the table was created with, if you use a very verbose option to check the *.MYI files? You could make a copy of your tables and experiment with myisamchk on the copies, see if that throws any light on things. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 5 May 2006, sheeri kritzer wrote: > To: Nils Lastein <[EMAIL PROTECTED]> > From: sheeri kritzer <[EMAIL PROTECTED]> > Subject: Re: Determine version of *.frm, *.MYD and *.MYI > > No backups? > > And you compiled it yourself with no records of how you compiled it or > what version you used? what if you want to compile a new version with > the same flags, how would you remember how to do that? > > If those don't help, trial and error is the only way. And you know > it's not 4.1.19, that was JUST released. And you just eliminated one > other one. Only 17 left to go. I wish I could be more helpful, but > when you don't backup or document, that's what happens. :( > > -Sheeri > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > > > > I know it a 4.1... But as I compiled it my self it is not so easy to > > figure > > it out And it might take a while to trial-n-error all 4.1.x > > > > Nils > > > > > > -Oprindelig meddelelse- > > Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] > > Sendt: fr 05-05-2006 20:28 > > Til: Nils Lastein > > Cc: mysql@lists.mysql.com > > Emne: Re: Determine version of *.frm, *.MYD and *.MYI > > > > > > You don't have ANY idea what branch it was created with? 3.2x, 4.0, > > 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in > > your > > dept and see what it's running (assuming there's no standards doc, > > or > > sysadmin to ask, etc). > > > > Do you remember around when the time was that you last > > installed/upgraded mysql on the box? If so you might be able to > > figure out which release it was. > > > > You can try to use something in the same branch -- if it was > > 4.1.something, try the latest in the 4.1 branch. > > > > Where were your backups stored? If you used mysqldump it logged the > > server version at the top of the output file. > > > > -Sheeri > > > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > After a disk crash I managed to save the *.frm, *.MYD and > > > *.MYI-files > > > from the disk. When putting these files into another mysql > > > server I get: > > > > > > mysql> select * from validate; > > > ERROR 1033 (HY000): Table './mydb/validate' was created with a > > > different > > > version of MySQL and cannot be read > > > > > > Unfortunately I'm unable to access the disk anymore, so I > > > cannot see > > > what version of the server generated these. > > > > > > How do I do that? > > > > > > Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlmanager logging?
su - mysql touch /var/lib/mysql/mysqlmanager.log see if that helps; maybe having the file there will kick it into gear. Is mysqlmanager actually running? Is there a pid file? -Sheeri On 5/5/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 04, 2006 3:12 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: mysqlmanager logging? > > Can the program write to /var/lib/mysql/mysqlmanager.log? > check permissions. > # su - mysql [EMAIL PROTECTED]:~> whoami mysql [EMAIL PROTECTED]:~> cd /var/lib/mysql [EMAIL PROTECTED]:~> touch foo.txt [EMAIL PROTECTED]:~> ls -al total 3 drwxr-xr-x 4 mysql mysql 120 2006-05-05 13:25 . drwxr-xr-x 56 root root 1472 2006-05-05 04:25 .. -rw-r--r-- 1 mysql mysql0 2006-05-05 13:25 foo.txt drwx--x--x 2 mysql mysql 1752 2006-05-01 09:33 mysql drwxr-xr-x 2 mysql mysql 48 2006-05-01 09:33 test I don't think there is a permission problem. Any further ideas? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
No backups? And you compiled it yourself with no records of how you compiled it or what version you used? what if you want to compile a new version with the same flags, how would you remember how to do that? If those don't help, trial and error is the only way. And you know it's not 4.1.19, that was JUST released. And you just eliminated one other one. Only 17 left to go. I wish I could be more helpful, but when you don't backup or document, that's what happens. :( -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: I know it a 4.1... But as I compiled it my self it is not so easy to figure it out And it might take a while to trial-n-error all 4.1.x Nils -Oprindelig meddelelse- Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: fr 05-05-2006 20:28 Til: Nils Lastein Cc: mysql@lists.mysql.com Emne: Re: Determine version of *.frm, *.MYD and *.MYI You don't have ANY idea what branch it was created with? 3.2x, 4.0, 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your dept and see what it's running (assuming there's no standards doc, or sysadmin to ask, etc). Do you remember around when the time was that you last installed/upgraded mysql on the box? If so you might be able to figure out which release it was. You can try to use something in the same branch -- if it was 4.1.something, try the latest in the 4.1 branch. Where were your backups stored? If you used mysqldump it logged the server version at the top of the output file. -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files > from the disk. When putting these files into another mysql server I get: > > mysql> select * from validate; > ERROR 1033 (HY000): Table './mydb/validate' was created with a different > version of MySQL and cannot be read > > Unfortunately I'm unable to access the disk anymore, so I cannot see > what version of the server generated these. > > How do I do that? > > Nils > > -- > 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: Coded fields
At 01:41 PM 5/5/2006, sheeri kritzer wrote: The best way to do it is NOT to make new codes. If they're giving you codes, use theirs -- why make up a new system if you don't have to? [...] Well, I made up my own codes before I knew I had to import data from somewhere else. I was quite happy with the single binary char approach until I had to import data from an oracle db. It definately would be easier for me to translate the new coded field into single chars during the import than it would be for me to re-do all the coded fields I have so far. But I was thinking that since I have to do something with this 5-char coded field, it might be a good time to overhaul the whole coded field system I invented. In fact, I would not gain clarity by using the 5-char codes that have been imposed upon me. Freshman='10'. Sophomore='20'. There's even a '00' code and a '05' for some status less than Freshman. Somebody suggested (via private email I think) that I use enum. I had mentioned in my original message that I didn't want to do that because some of the coded fields allow end-users to add/remove codes. But he pointed out that that could be done via alter table commands. That's kind of scary to me. I dunno. Another problem with the enum approach is getting the possible values into a select list on a web page. I've seen people say they parse the output from a show table command. That seems breakable to me. What if version 7 of mysql adds a space somewhere. Or reorders the columns. So I still don't know how I want to handle this problem. Right now I'm leaning toward translating the 5-char codes to 1-char codes and sticking with what I've got. If somebody had given me a dramitcally better way to handle coded fields than what I'm already doing, I might have been willing to re-write everything. But right now, I'm leaning more toward the "if it ain't broke, don't fix it" theory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table so slow to read
1) See if the DNS on one machine is different. If it's trying to resolve the host on one and failing, that might be why it's slow. 2) how did you copy the database? 3) what's the data usage like on each machine? If one machine is used a lot and the other is used very little, then it may be that MySQL's query cache, or the OS cache, is returning the results faster. -Sheeri On 5/4/06, Barry <[EMAIL PROTECTED]> wrote: Gabriel Mahiques schrieb: > My name is Gabriel, "Saludos Cordiales" is the same than "Best Regard" > in spanish. > > > The server explanin is the same. The table structure is the same, the > application is the same (redirect the data source only), the quantity > of record is the same. All is the same, I copy the database from one > server to other > But the servers are not the same, right? This could be one problem. The other problem might be missing indezies on your other server. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- 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: InnoDB Memory Problem causing mysql to crash
Well, according to my calculations: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB (I used the default binlog_cache_size value of 32K plus your settings) MySQL could use up to 4.991913 G of memory. Shouldn't be a problem, unless of course your 8G of machine is running something other than MySQL. Is it? Because the fact that it could not allocate memory means that something was trying to use memory that didn't exist Did MySQL dump a core file? Did you follow this advice? You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html Did you read the man page? The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Also, did you try to look at your slow query logs to see if there was some kind of query hogging memory? What about backups running at the same time? I'll note that you maxxed out your connections, which shouldn't cause a crash, but might indicate that your server tuning is not up-to-date with your actual usage. Are your data and logfiles are on a diffferent partitions? We had problems with one machine where the data and logfiles were on the same partition, and it would crash -- we moved to a machine that was the same except for the different OS partitions, and it didn't crash! We figure the disk seeking just killed the OS so it segfaulted the mysql process. -Sheeri On 5/4/06, Dobromir Velev <[EMAIL PROTECTED]> wrote: Hi, I'm trying to resolve why InnoDB is crashing. It happened twice for the last month without obvoius reason Any help will be appreciated. Dobromir Velev My Server is Red Hat Enterprise Linux ES release 3 (Taroon Update 7) 2.4.21-32.0.1.ELsmp Dual 3.2 GHz Intel Xeon 8 GB RAM with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives my.cnf settings innodb_buffer_pool_size=2000M innodb_additional_mem_pool_size=20M innodb_log_file_size=150M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=50 key_buffer_size=1000M read_buffer_size=500K read_rnd_buffer_size=1200K sort_buffer_size=1M thread_cache=256 thread_concurrency=8 thread_stack=126976 myisam_sort_buffer_size=64M max_connections=600 The error log shows the following message: InnoDB: Fatal error: cannot allocate 1048576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 2263507272 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576000 read_buffer_size=507904 max_used_connections=600 max_connections=600 threads_connected=473 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1935995 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbff1f558, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8072d74 0x826d678 0x8213c74 0x8213d04 0x8218b84 0x81d5ba6 0x80fd659 0x826ae2c 0x82a0cda New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060503 16:37:21 mysqld restarted 060503 16:37:21 Can't start server: Bind
Error 1714. The older version of Mysql Query Browser1.1 cannot be removed.
Hi All, I submitted this query in Mysql Query Browser forum last month and haven't got any help to fix this, so here I'm submitting this again. I tried to install mysql query browser 1.1.20 and previous i had version 1.1.6, but every time i try installing i keep getting the following error. "Error 1714. The older version of Mysql Query Browser1.1 cannot be removed. Contact your technical support group." Can someone help me fixing this or can someone tell me how i can install the previous version, since i couldn't find achieves for query browser on mysql downloads section. It would be great if I can just install any version of mysql query browser. Thanks, Romy
Re: Searching a large table
Hi Sheeri: Yes, you are misunderstanding my question. I certainly know how to limit my resultset to certain rows. I'm asking more about the effiencency of searching large volumes of data. Is making a search table like vBulletin does a good mechanism to avoid resource contention on the main table, or is that more work than what it's worth in my case? -- Steve - Web Applications Developer http://www.sdwebsystems.com On Fri, May 5, 2006 2:35 pm, sheeri kritzer said: > Sounds like you want LIMIT and OFFSET -- > > everything after my name and before your post is copied from the doc at > > http://dev.mysql.com/doc/refman/4.1/en/select.html > > (or am I misunderstanding your question?) > -Sheeri > > The LIMIT clause can be used to constrain the number of rows returned > by the SELECT statement. LIMIT takes one or two numeric arguments, > which must both be non-negative integer constants (except when using > prepared statements). > > With two arguments, the first argument specifies the offset of the > first row to return, and the second specifies the maximum number of > rows to return. The offset of the initial row is 0 (not 1): > > SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 > > To retrieve all rows from a certain offset up to the end of the result > set, you can use some large number for the second parameter. This > statement retrieves all rows from the 96th row to the last: > > SELECT * FROM tbl LIMIT 95,18446744073709551615; > > With one argument, the value specifies the number of rows to return > from the beginning of the result set: > > SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows > > In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. > > For prepared statements, you can use placeholders (supported as of > MySQL version 5.0.7). The following statements will return one row > from the tbl table: > > SET @a=1; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; > EXECUTE STMT USING @a; > > The following statements will return the second to sixth row from the tbl > table: > > SET @skip=1; SET @numrows=5; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; > EXECUTE STMT USING @skip, @numrows; > > For compatibility with PostgreSQL, MySQL also supports the LIMIT > row_count OFFSET offset syntax. > > On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: >> All: >> >> I am developing a search engine and using MySQL as the backend database >> management system. Under normal circumstances, when users search >> through >> large volumes of records on a search engine site, the results are broken >> down into pages. When a user clicks on the 'Next' link, the system will >> re-query the database and return those records that pertain to that page >> (records 10 through 20, perhaps). This, as you can plainly see, >> requires >> the application to re-query the same data from the same table each time >> the user clicks on a 'Next' or 'Previous' link. I would imagine that >> this >> may lead to some resource contention on that main table, especially when >> many users are using the system simultaneously. >> >> I've never seen vBulletin's code, but I have been able to determine from >> careful analysis (and testing) that they employ a search table, of >> sorts, >> that contains the returned records from a search. So, when a user of >> the >> system submits a search query, the system returns the records and throws >> them into a separate search table, identified by a sequential primary >> key >> field. Then, the system uses that search table to display the >> appropriate >> records according to the respective search identifier (referenced in the >> URL), never touching the main table again until a brand new search is >> performed. >> >> This seems to be a pretty good way to facilitate large text-based >> searches. Are there any other mechanisms that can be used to build a >> powerful, yet quick and light on resources, search system? Is a >> fulltext >> index the best way to achieve maximum performance with this kind of >> search? >> >> Thanks. >> >> -- >> Steve - Web Applications Developer >> http://www.sdwebsystems.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: blank user names in user table
Rich, anonymous access means that ''@host has access. That is, "blank" at host, as opposed to [EMAIL PROTECTED] To see if anonymous access is allowed, at the command prompt type: mysql -u asdf if you get a mysql login, you have anonymous access. Otherwise you'll get: ERROR 1045 (28000): Access denied for user 'asdf'@'localhost' (using password: NO) The best way is to do: mysql> select host,user,password from mysql.user; and see if any users or passwords are blank. if users are blank, it means anyone can login, and if passwords are blank it means the password isn't set. You can also look at: mysql> show grants for ''@localhost; mysql> show grants for ''@'%'; hope it helps! -Sheeri On 5/4/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: Hi all, I've got a database I recently inherited where there are a number of records in the mysql.user table that have no user id. According to the mysql docs, this is supposed to allow guest access, and there is mention of how to turn it off. How does one actually connect in guest mode? My attempts with the mysql client have so far been in vain. How can I ask mysql to log connection attempts by users so I can see if any of these ID's are actually in use? Thank you. Regards, Rich -- 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: Adding a second slave and Load Data from master questions
LOAD DATA FROM MASTER only works for MyISAM tables. Is
tracking.session an InnoDB table?
-Sheeri
On 5/4/06, Hunter Peress <[EMAIL PROTECTED]> wrote:
Hi. im trying to add a second slave using load data from master ,and
it seems to me that when i run this command on the
new slave that its simply picking up from where the first slave is
replicating from. Does this make sense? Also interesting is that
when both the IO and SQL threads are No on the new slave, it
downloads until 661 MB then stops. Another fact is that the first
slave is actually
out of sync.
So i have some questions on load data from master:
is it designed to work with a partway updated slave, or is it only
designed to work from a completely blank database?
Heres the error logs from the new slave im trying to set up.
May 4 09:29:41 localhost mysqld[29920]: 060504 9:29:41
[Note] Slave SQL thread initialized, starting replication in log
'FIRST' at position 0, relay log './djembe-relay-bin.01' position: 4
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]',
replication started in log 'FIRST' at position 4
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[ERROR] Slave: Error 'Table 'tracking.session' doesn't exist' on
query. Default database: 'tracking'. Query: 'insert into `session`
(phpsessio
nid, useragent, remoteip, guid, userid, entryurl, referurl,
created) values('10e55f72ff0321de6199df3c650608d3', 'Python-urllib/
1.15', '10.2.1.11', NULL, NULL, '/', NULL, NULL)', Error_code: 1146
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[ERROR] Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We
stopped at log
'mysql-bin.000256' position 4
May 4 09:30:06 localhost mysqld[29920]: 060504 9:30:06
[ERROR] Slave I/O thread killed while reading event
May 4 09:30:06 localhost mysqld[29920]: 060504 9:30:06
[ERROR] Slave I/O thread exiting, read up to log 'mysql-bin.000256',
position 6268185
Hunter Peress
[EMAIL PROTECTED]
Web Programmer
The New Mexican, Inc.
www.freenewmexican.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]
Re: question about utf and collation
I don't know what version of MySQL you're using, but a google search on "mysql croatian" got me: http://bugs.mysql.com/bug.php?id=16373 and http://bugs.mysql.com/bug.php?id=6504 which implies you can use CHARACTER SET latin2 COLLATE latin2_croatian_ci but also shows that it's not quite working yet. Follow those bugs, and you'll find what you want. (note the link at the bottom of one of those bugs: http://www.ambra.rs.ba/ I can't read croatian so I can't tell if that website is of any use). -Sheeri On 5/4/06, Marko Zmak <[EMAIL PROTECTED]> wrote: I'm sorry if this is not the apropriate list, but I couldn't fined any other list where this question would fit in. If someone know where to post it, please suggest. I have a question about collation and utf in mysql. I'm using mysql on several of my websites, but the mysql database doesn't have croatian collation for utf. And most of my sites are in croatian. Is there any plan to add croatian collation for utf, and when? Thanks. -- Marko Žmak, dipl.ing.mat. Mob: +385 98 212 801 Email: [EMAIL PROTECTED] Web: http://www.studioartlan.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]
Re: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
You are not being honest with us on the list. Firstly, the error you got: You have an error in your SQL syntax near 'USING USING A RIGHT JOIN B ON B.id = A.sectionid' at line 1 SQL=DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null indicates that you used the USING keyword twice in your query, which won't work in any version of MySQL. I tried to replicate what you have: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1568 to server version: 4.1.12-standard-log mysql> create table A (A int, sectionid int); Query OK, 0 rows affected (0.31 sec) mysql> create table B (id int, A int); Query OK, 0 rows affected (0.23 sec) mysql> select * from A USING A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null' at line 1 As you can see, on MySQL 4.1.12 I'm getting an error. ( I used select * from instead of delete from because that's how I test out delete queries to make sure I don't do something dumb). I think you don't want the "USING A" at all: select * from A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null; works just fine for me. And it's true that in 3.23 you could not do a multiple-table UPDATE (that was introduced in 4.0.0). So I'm guessing that's what you really meant. Why are you even bothering, though? why not just use DELETE FROM A WHERE sectionid IS NULL; DELETE FROM B WHERE id IS NULL; ? Because that's all you're really doing in those queries. Not that it needs to be said, but you should upgrade. -Sheeri On 5/4/06, The Nice Spider <[EMAIL PROTECTED]> wrote: >> This query running fine on 4.0.25 but when trying on >> 3.23 an error occurs. >> can one help me to find correct command for 3.23? > Probably if you post the error message you get. > DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null error message on 3.23 is: You have an error in your SQL syntax near 'USING USING A RIGHT JOIN B ON B.id = A.sectionid' at line 1 SQL=DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null BUT this query run ok in 4.0.25. i need to find error free syntaks for 3.23 version. any help? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: importing a dumpfile from with the mysql client
I think what he is saying is that be began the "transaction" in a command line client session from one location but was not able to give the > mysql> SET FOREIGN_KEY_CHECKS = 1; > mysql> COMMIT; commands in the same session. (ie - I owe, I owe, its off to work I go :) ) This is a good question, one I have wondered about myself. Is there a way in mysql to "attach" to session to issue a commit? -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 3:02 PM To: Luke Vanderfluit Cc: MySQL List Subject: Re: importing a dumpfile from with the mysql client On 5/4/06, Luke Vanderfluit <[EMAIL PROTECTED]> wrote: [snip] > I started this process remotely then went to the site to finish it. > But when the dump finished (several hours later) I was not able to > execute the following commands from my original location. > > mysql> SET FOREIGN_KEY_CHECKS = 1; > mysql> COMMIT; What do you mean "you were not able"? Did you get an error? Was the server hung? Did the keyboard stick to make you unable to type the commands? > > My question is: > Since the import completed the database has grown in size and been > backed up etc. > Yet from the original session I have not executed those 2 commands. > > Is it safe to execute them? Or would executing them cause corruption or > other unforseen stuff? > Is it unnecessary to execute them? > Is it safe to kill that original mysql session? Those 2 commands, in and of themselves, aren't dangerous. It's what's in the script that could be the problem. However, since you really didn't mention what error you got, it's not easy to figure out how to fix it. It depends what tables you're running these on. If you're running them on MyISAM tables, deadlocking cannot happen. Then again, transactions are meaningless too. If you're running on InnoDB you have the possibility of deadlocking, but MySQL is pretty good about avoiding that. If other users/applications are writing to the SAME data then there's the possibility of inconsistent data, but I wouldn't expect corruption. if you post what errors you actually got, I might be able to help. Also, are you using the same db and tables that other apps/users are using? Does the dumpfile contain "DROP TABLE IF EXISTS" commands? -Sheeri > > -- > Luke Vanderfluit. > Analyst/Programmer. > Internode Systems Pty. Ltd. > > > -- > 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]
SV: Determine version of *.frm, *.MYD and *.MYI
I know it a 4.1... But as I compiled it my self it is not so easy to figure it out And it might take a while to trial-n-error all 4.1.x Nils -Oprindelig meddelelse- Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: fr 05-05-2006 20:28 Til: Nils Lastein Cc: mysql@lists.mysql.com Emne: Re: Determine version of *.frm, *.MYD and *.MYI You don't have ANY idea what branch it was created with? 3.2x, 4.0, 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your dept and see what it's running (assuming there's no standards doc, or sysadmin to ask, etc). Do you remember around when the time was that you last installed/upgraded mysql on the box? If so you might be able to figure out which release it was. You can try to use something in the same branch -- if it was 4.1.something, try the latest in the 4.1 branch. Where were your backups stored? If you used mysqldump it logged the server version at the top of the output file. -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files > from the disk. When putting these files into another mysql server I get: > > mysql> select * from validate; > ERROR 1033 (HY000): Table './mydb/validate' was created with a different > version of MySQL and cannot be read > > Unfortunately I'm unable to access the disk anymore, so I cannot see > what version of the server generated these. > > How do I do that? > > Nils > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: importing a dumpfile from with the mysql client
On 5/4/06, Luke Vanderfluit <[EMAIL PROTECTED]> wrote: [snip] I started this process remotely then went to the site to finish it. But when the dump finished (several hours later) I was not able to execute the following commands from my original location. mysql> SET FOREIGN_KEY_CHECKS = 1; mysql> COMMIT; What do you mean "you were not able"? Did you get an error? Was the server hung? Did the keyboard stick to make you unable to type the commands? My question is: Since the import completed the database has grown in size and been backed up etc. Yet from the original session I have not executed those 2 commands. Is it safe to execute them? Or would executing them cause corruption or other unforseen stuff? Is it unnecessary to execute them? Is it safe to kill that original mysql session? Those 2 commands, in and of themselves, aren't dangerous. It's what's in the script that could be the problem. However, since you really didn't mention what error you got, it's not easy to figure out how to fix it. It depends what tables you're running these on. If you're running them on MyISAM tables, deadlocking cannot happen. Then again, transactions are meaningless too. If you're running on InnoDB you have the possibility of deadlocking, but MySQL is pretty good about avoiding that. If other users/applications are writing to the SAME data then there's the possibility of inconsistent data, but I wouldn't expect corruption. if you post what errors you actually got, I might be able to help. Also, are you using the same db and tables that other apps/users are using? Does the dumpfile contain "DROP TABLE IF EXISTS" commands? -Sheeri -- Luke Vanderfluit. Analyst/Programmer. Internode Systems Pty. Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql memory utilization
The total possible memory MySQL will use is: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB Someone please correct me if I'm wrong. Are your most heavily used tables innodb? If not, then you're not looking at the right fields. You're doing a TON of reads per second, I'm impressed. 6.65 inserts/s, 370.98 updates/s, 0.00 deletes/s, 163055.10 reads/s You might want to check your slow query logs and use EXPLAIN on the queries to see if an index will help you use less memory (ie, if you're doing a full table scan). Total memory allocated 61180; in additional pool allocated 20954624 So you have about 583 M of memory allocated. That's not a lot, given the huge amount of reads you're doing. And your additional pool gives you about 2M more. I just did some stats on one of our medium-use servers: 7.00 inserts/s, 227.89 updates/s, 2.00 deletes/s, 14198.90 reads/s Total memory allocated 4753221600; in additional pool allocated 33100800 That's almost 4.5G of memory (and we need it all) allocated with an additional pool of 31M. From your stats: Buffer pool size 32768 Free buffers 0 This means you're using ALL your buffers! From our medium use server: Buffer pool size 262144 Free buffers 37108 (and we peak Sun and Mon nights, so having free buffer space now is a good thing). What is innodb_buffer_pool_size set to? (ours is 4G) I'm betting that needs to be changed. -Sheeri On 5/5/06, Anil <[EMAIL PROTECTED]> wrote: Hi list, We are facing memory problems for our application and to analyze memory utilization by application we require below information on mysql memory utilization. how much of RAM mysql is utilizing and amount of memory allocated for innodb buffer is utilizing ,how much of innodb buffer is free and amount of thread based buffers allocated. We are getting some information with " show innodb status " please let me know how to interpret the output . I am pasting output of " show innodb status " below for reference. Please give us detailed explanation how to interpret the output. = 060504 20:00:01 INNODB MONITOR OUTPUT = Per second averages calculated from the last 20 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 522818462, signal count 504180146 Mutex spin waits 1363737867, rounds 305233322, OS waits 106732853 RW-shared spins 740792501, OS waits 360301857; RW-excl spins 45582064, OS waits 16212977 FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 383418129 OS file reads, 59580243 OS file writes, 9957395 OS fsyncs 10.10 reads/s, 27901 avg bytes/read, 3.30 writes/s, 2.10 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 506, seg size 508, is empty Ibuf for space 0: size 1, free list len 506, seg size 508, 13076970 inserts, 13077112 merged recs, 2977056 merges Hash table size 2212699, used cells 679260, node heap has 796 buffer(s) 1161.94 hash searches/s, 984.85 non-hash searches/s --- LOG --- Log sequence number 89 54255649 Log flushed up to 89 54141193 Last checkpoint at 89 30596518 0 pending log writes, 0 pending chkp writes 2089244 log i/o's done, 1.30 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 61180; in additional pool allocated 20954624 Buffer pool size 32768 Free buffers 0 Database pages 31972 Modified db pages 5217 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1455127697, created 6339651, written 91917358 17.20 reads/s, 5.10 creates/s, 18.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 2 queries inside InnoDB, 0 queries in queue Main thread process no. 12278, id 28680, state: sleeping Number of rows inserted 172622749, updated 475137381, deleted 45859392, read 4138993176 6.65 inserts/s, 370.98 updates/s, 0.00 deletes/s, 163055.10 reads/s END OF INNODB MONITOR OUTPUT Thanks Anil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Coded fields
John,
You're close -- If a single case-sensitive letter won't help (or isn't
descriptive enough -- is 'S' sophomore or senior or special student?)
The best way to do it is NOT to make new codes. If they're giving you
codes, use theirs -- why make up a new system if you don't have to?
create table codes (
code_type varchar(10,
code_key varchar(5),
code_text varchar(80)
);
and then you can change your own codes from 's' and 'S' to 'soph' and
'sen' or whatever. Or just use whatever they use for those statuses.
Of course, if you're dealing with registrars from different schools
who may use the same code for 2 different things, then you might want
to use a surrogate key (ie, autoincrement field). But if they're
giving you special codes, why make up your own?
-Sheeri
On 5/4/06, John Heim <[EMAIL PROTECTED]> wrote:
What is the best way to create a coded field? I want to do something
similar to enumeration but I don't want to have to define the values at
table creation time because sometimes the end-users need to add or remove
the codes.
I've been using char binary fields in my database to this point figuring
that takes only one byte per stored code. Then the values can be ASCII
chars and would be kind of meaningful if retrieved from the database. For
instance, I might have 'f' for freshman, 'S' for Sophomore, 'J' for Junior,
and 's' for Senior. Actually, there's like 20 different categories for
students. So then I have a lookup table for codes:
create table codes (
code_type varchar(10,
code_key char binary,
code_text varchar(80)
);
Then I can do left joins to retrieve a description of the code if
necessary. For example:
INSERT INTO codes VALUES ('class', 'F', 'Freshman');
INSERT INTO codes VALUES (class', 'S', 'Sophomore');
INSERT INTO codes VALUES ('class', 'J', 'Junior');
INSERT INTO codes ('class', 's', 'Senior');
SELECT C.code_text AS academic_class
FROM students S
LEFT JOIN codes C ON (C.code_type='class' AND S.class=C.code_key);
That particular example might work better with enum but it's a deliberately
trivialized example. Most of my coded fields have 5-20 possible values.
My problem is that I've had some codes imposed upon me that are 5 chars. I
don't know if I should just start over or what. Maybe other people deal
with coded fields in a totally different way that is way better than what
I've invented.
Suggestions?
--
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: Searching a large table
Sounds like you want LIMIT and OFFSET -- everything after my name and before your post is copied from the doc at http://dev.mysql.com/doc/refman/4.1/en/select.html (or am I misunderstanding your question?) -Sheeri The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements). With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last: SELECT * FROM tbl LIMIT 95,18446744073709551615; With one argument, the value specifies the number of rows to return from the beginning of the result set: SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a; The following statements will return the second to sixth row from the tbl table: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: All: I am developing a search engine and using MySQL as the backend database management system. Under normal circumstances, when users search through large volumes of records on a search engine site, the results are broken down into pages. When a user clicks on the 'Next' link, the system will re-query the database and return those records that pertain to that page (records 10 through 20, perhaps). This, as you can plainly see, requires the application to re-query the same data from the same table each time the user clicks on a 'Next' or 'Previous' link. I would imagine that this may lead to some resource contention on that main table, especially when many users are using the system simultaneously. I've never seen vBulletin's code, but I have been able to determine from careful analysis (and testing) that they employ a search table, of sorts, that contains the returned records from a search. So, when a user of the system submits a search query, the system returns the records and throws them into a separate search table, identified by a sequential primary key field. Then, the system uses that search table to display the appropriate records according to the respective search identifier (referenced in the URL), never touching the main table again until a brand new search is performed. This seems to be a pretty good way to facilitate large text-based searches. Are there any other mechanisms that can be used to build a powerful, yet quick and light on resources, search system? Is a fulltext index the best way to achieve maximum performance with this kind of search? Thanks. -- Steve - Web Applications Developer http://www.sdwebsystems.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]
Re: Group-wise maximum
Try looking at the documentation for "groupwise maximum". http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html -Sheeri On 5/5/06, Ian Klassen <[EMAIL PROTECTED]> wrote: Hello, I'm working on using a temporary table to get the group-wise maximum for a number of items. I have a table structure like: CREATE TABLE item ( item_id int not null primary key, name varchar(100) not null); CREATE TABLE attribute_a ( index (item_id, time_id), item_id int not null, time_id datetime not null, value int not null); Each item has many attributes. The time_id of an attribute contains the time of the /last /update to the attribute. My objective is to return the the attribute's value for each item at a requested time. I believe the best solution is to get the maximum time_id's (<= requested time) and dump them into a temporary table. Then use these times to retrieve the actual values. My problem is that as I add a number of attributes the query time bogs down when creating the temporary table. The query looks something like: CREATE TEMPORARY TABLE times (INDEX (item_id)) SELECT item.item_id, MAX(attribute_a.time_id) AS attribute_a_time FROM item LEFT JOIN attribute_a ON (attribute_a.item_id = item.item_id AND attribute_a.time_id < '2000-10-01 00:00') GROUP BY item.item_id; I add a LEFT JOIN for each attribute that I want to retrieve (up to 60). Let's say each attribute table contains 10,000 rows (all with dates less than '2000-10-01'). If I understand things correctly, using this query 10,000 rows would be added for each attribute that I joined. If I added 60 attributes MySQL would have to handle grouping 600,000 rows! Is that correct? Is there a better solution? I tried using sub queries but that didn't seem to be much faster. If I had 1,000 items to search for 60 attributes the sub queries would be called 60,000 times. Any ideas? Thanks! Ian -- 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: Determine version of *.frm, *.MYD and *.MYI
You don't have ANY idea what branch it was created with? 3.2x, 4.0, 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your dept and see what it's running (assuming there's no standards doc, or sysadmin to ask, etc). Do you remember around when the time was that you last installed/upgraded mysql on the box? If so you might be able to figure out which release it was. You can try to use something in the same branch -- if it was 4.1.something, try the latest in the 4.1 branch. Where were your backups stored? If you used mysqldump it logged the server version at the top of the output file. -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files from the disk. When putting these files into another mysql server I get: mysql> select * from validate; ERROR 1033 (HY000): Table './mydb/validate' was created with a different version of MySQL and cannot be read Unfortunately I'm unable to access the disk anymore, so I cannot see what version of the server generated these. How do I do that? Nils -- 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: mysqlmanager logging?
> -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 04, 2006 3:12 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: mysqlmanager logging? > > Can the program write to /var/lib/mysql/mysqlmanager.log? > check permissions. > # su - mysql [EMAIL PROTECTED]:~> whoami mysql [EMAIL PROTECTED]:~> cd /var/lib/mysql [EMAIL PROTECTED]:~> touch foo.txt [EMAIL PROTECTED]:~> ls -al total 3 drwxr-xr-x 4 mysql mysql 120 2006-05-05 13:25 . drwxr-xr-x 56 root root 1472 2006-05-05 04:25 .. -rw-r--r-- 1 mysql mysql0 2006-05-05 13:25 foo.txt drwx--x--x 2 mysql mysql 1752 2006-05-01 09:33 mysql drwxr-xr-x 2 mysql mysql 48 2006-05-01 09:33 test I don't think there is a permission problem. Any further ideas? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is the Optimizer on 5.0.20 broken?
You're comparing apples to oranges -- your where clauses are
different. The first query uses the primary key because you're
actually comparing 'account' to something. The second query doesn't
because you're using the 'street' field -- what does SHOW INDEXES
FROM account; show? any indexes on 'street', in 4.1 that didn't get
moved to 5.0?
-Sheeri
On 5/5/06, Juri Shimon <[EMAIL PROTECTED]> wrote:
Hello All,
I have a next problem. After upgrade from 4.1 branch to 5.0,
the productivity of our applications has decreases too much.
Below is a tipical case.
Table 'account' has primary(department,account) and 40777 records.
Table 'payment' primary(department,account,year,month) and 3831797 records.
In this case all ok ('where' uses primary key of table 'account'):
> desc select * from
> account a inner join payment p using(department,account)
> where a.department='1' and a.account=1000
++-+---+--+-+-+-++---+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+--+-+-+-++---+-+
| 1 | SIMPLE | a | ref | PRIMARY,account | account | 4 | const
| 1 | |
| 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 10 |
HAS.a.department,const | 28738 | Using where |
++-+---+--+-+-+-++---+-+
This case not working in 5.0.20 ('where' uses index of 'account' with
10 resulting rows):
> desc select * from
> account a inner join payment p using(department,account)
> where a.street=10
++-+---+--+-+-+-++-+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows| Extra |
++-+---+--+-+-+-++-+-+
| 1 | SIMPLE | p | ALL | PRIMARY | | |
| 3831797 | |
| 1 | SIMPLE | a | ref | PRIMARY,account | PRIMARY | 10 |
HAS.p.department,HASVODA.p.account | 1 | Using where |
++-+---+--+-+-+-++-+-+
I don't think that selecting for fullscan of table with 3.5 million
rows (instead of table with 40 thousends) is a good idea.
Is this a known bug?
WBR!
Juri.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
On 5/5/06, Sander Smeenk <[EMAIL PROTECTED]> wrote: Quoting Kishore Jalleda ([EMAIL PROTECTED]): > >Can anyone shed any light on this issue? > This might shed more light into your problem > http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the "You can also get these errors if you send a query to the server that is incorrect or too large." topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... What is happening is that when you start you perl script which contains a lot of queries, you are first establishing a connection to MySQL, and then you are running some queries, and somewhere in between you are losing connection to the server , now when it comes to Line 695, it tries executing a query again, but the connection has gone and the query has failed, and possibly thescript has exited.Now this means you don't have automatic reconnection enabled in your script, if you do then your script will try to connect to MySQL again and then get a new connection ID and issue that query again. So I would suggest here that you make your Perl script auotmatic reconnect aware. After this what you really have to diagonize is why you are losing connection to MySQL initially (i.e. at line 98)..Since this is occuring only intermittently I am guessing if your max_connections limit was reached at the time the connection was lost, what are your "wait_timeout" and "max_connections" settings, and are the initial queries very large... Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to see why client got blocked
If your server has log-warnings set to ON, you can check the error logs, and use a script to count how many times for each host, in a row, this happens. -Sheeri On 5/5/06, Kishore Jalleda <[EMAIL PROTECTED]> wrote: On 5/5/06, Dominik Klein <[EMAIL PROTECTED]> wrote: > I experience that my slave gets blocked after a while (a couple of slave > stop and slave start happen in the meantime). In errorlog I see > > Slave I/O thread: error reconnecting to master > '[EMAIL PROTECTED]:3306': Error: 'Host 'myhost.mydomain.de' is > blocked because of many connection errors; unblock with 'mysqladmin > flush-hosts'' errno: 1129 retry-time: 60 retries: 86400 > > Slave and Master are 5.0.20. > How can I see why the slave was blocked? > > Regards > Dominik > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > What is your max_connect_errors setting on the master, because your slave has failed to connect to the master for more than max_connect_errors times, please look at http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html.. Do you also see numerous failed connects in your slave log, along with the blocked error. >another question on this error message: > >is it possible to see the count of errors for each host from some table >or file? I don't think you can do this, but you can write a shell/perl script to parse your error logs and count the errors for each host. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Do a "show status like 'uptime'" after the script runs. See if the server crashed (if so, uptime will be low) -- if the server crashed you might get that error. show variables like "%connections" should show you how many connections you can have per user and total. That shouldn't be the problem; you'd get a "too many connections" error if that was the problem, but it couldn't hurt to check. show grants for your user to see if you have any limits on your user resources: http://dev.mysql.com/doc/refman/4.1/en/user-resources.html again, the errors would be different. What's max_allowed_packet set to? You said you can run the query just fine -- did you just try on commandline, or can you run the query in a script by itself? What happens if you run the script to echo all the SQL commands into a text file, and then source the text file from the mysql commandline prompt? Same error? Are the script and the host on the same machine? Is it using TCP/IP to connect, or a unix socket? Is there any firewalling in place? Does anyone else have admin privileges to the database? They might be manually killing the query, if it hangs up. (I've had this done to me, where an admin kept killing long queries without asking folks who was doing them). Are you working on an InnoDB table? Try turning on the InnoDB monitor while the query runs and see if you're getting any deadlocking. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html -Sheeri On 5/5/06, Sander Smeenk <[EMAIL PROTECTED]> wrote: Quoting Kishore Jalleda ([EMAIL PROTECTED]): > >Can anyone shed any light on this issue? > This might shed more light into your problem > http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the "You can also get these errors if you send a query to the server that is incorrect or too large." topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... :) Sander. -- | Depression is merely anger without enthusiasm. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting Kishore Jalleda ([EMAIL PROTECTED]): > >Can anyone shed any light on this issue? > This might shed more light into your problem > http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the "You can also get these errors if you send a query to the server that is incorrect or too large." topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... :) Sander. -- | Depression is merely anger without enthusiasm. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect application
http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ Is a good article. -Sheeri On 4/21/06, Chris White <[EMAIL PROTECTED]> wrote: I was looking around the list search and didn't find much on this subject (maybe didn't look back far enough), but I was discussing with a coworker about a reasonable application of subselects vs. a WHERE clause or table join. Thank you in advance. -- Chris White Interfuel -- 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: How to see why client got blocked
On 5/5/06, Dominik Klein <[EMAIL PROTECTED]> wrote: I experience that my slave gets blocked after a while (a couple of slave stop and slave start happen in the meantime). In errorlog I see Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Host 'myhost.mydomain.de' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'' errno: 1129 retry-time: 60 retries: 86400 Slave and Master are 5.0.20. How can I see why the slave was blocked? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] What is your max_connect_errors setting on the master, because your slave has failed to connect to the master for more than max_connect_errors times, please look at http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html.. Do you also see numerous failed connects in your slave log, along with the blocked error. another question on this error message: is it possible to see the count of errors for each host from some table or file? I don't think you can do this, but you can write a shell/perl script to parse your error logs and count the errors for each host. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
On 5/5/06, Sander Smeenk <[EMAIL PROTECTED]> wrote:
Hi,
I'm running MySQL 5.0.19 (Debian sid, package revision 3) on a Dual
Opteron250 64 bit machine with 4gb memory.
The problem i'm experiencing is that at certain moments, the connection
between my perlscripts and the database gets disconnected:
| DBI connect('products','beheerv',...) failed: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98
| Master.pm: unable to connect to products db: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98.
And:
| DBD::mysql::st execute failed: Lost connection to MySQL server during
| query at /var/web/cron/stats.pl line 673.
| DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
| /var/web/cron/stats.pl line 677.
I thought the query at that spot would be wrong, but it turns out that
this alone is not causing the problem. If i skip over the rest of the
perlscript and jump to that line 672 immediately, it works like
expected.
I'd like to hear how I can debug this. MySQL doesn't seem to log
ANYTHING about this issue. All I get is the messages above. I turned on
query logging, and it just shows the query that is executed at line 673
of stats.pl:
| SELECT theme, cat, subcat, p_ids,
| UNIX_TIMESTAMP(insert_time) as insert_time
| FROM log_product
| WHERE insert_time >= FROM_UNIXTIME('1146348000')
| AND insert_time < FROM_UNIXTIME('1146952800');
(this query, when redirected to a textfile returns about 25 megabytes of
data, in ~540784 rows)
After that no more queries from the same connection ID are logged.
But that is expected, as the connection was lost ;)
Can anyone shed any light on this issue?
Thanks!!
Sander.
--
| Honk if you love peace and quiet.
| 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This might shed more light into your problem
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Kishore Jalleda
http://kjalleda.googlepages.com/projects
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql4.0
On Fri, May 05, 2006 at 03:40:10PM +0100, Chris wrote: > Is mysql 4.0 still supported or EOL, as the recent advisory says users > of 4.0 should upgrade to 4.1 to patch the security problem. > > Subsequently I have noticed a new 5.x release and new 4.1.x release > and no 4.0.x release. There are currently no plans to release a new version of 4.0. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query using between
Good morning James -
It looks like you have a multi-column index on the startIpNum and
endIpNum columns, but it's not doing you any good, at least not for
this query.
You don't mention how many rows of data you're searching against,
which would give a better idea as to what might be reasonable to
expect for maximum speed of this query.
Part of the reason is that MySQL (at least prior to 5) will only use
one index per instance of a table per query, and it has chosen the
primary index from that "t2" table. Another part of the reason is
that MySQL can't compare a value to a value in an index that is not
at the beginning of said index.
A final reason you may experience a speed problem is that you've put
the number inside quotes, which makes it a string and could well be
forcing MySQL to do a datatype conversion on the data in your tables
prior to comparisons. Try your query without quotes first to see
what kind of difference that makes for you.
Otherwise -
Try joining on the second table again, and comparing against
startIpNum on that table. This makes your query more complex but
might allow MySQL to better use your existing indices. You could
take it a step further and add an index on endIpNum all by itself,
and add the table in a third time, also.
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1, hn_iprange as t2, hn_iprange as t3
where t1.locid=t2.locid
and t1.locid = t3.locid
and t2.locid = t3.locid
and (2720518136 between t3.startIpNum and t3.endIpNum)
limit 1
or for real fun, add an index to endIpNum and run:
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1, hn_iprange as t2, hn_iprange as t3, hn_iprange as t4
where t1.locid=t2.locid
and t1.locid = t3.locid
and t1.locid = t4.locid
and t2.locid = t3.locid
and t2.locid = t4.locid
and t3.locid = t4.locid
and (2720518136 >= t3.startIpNum)
and (2720518136 <= t4.endIpNum)
limit 1
Hope this helps!
Dan
Hello,
I am running Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu
(i386) using readline 4.3 on a Dual Xeon 2.4Ghz RHEL4 box with 4GB
of RAM.
I have a query that takes anywhere from .25 - .85 seconds to
run. Following are the query and the related table structures I have
currently set up and the output from 'explain'. After twiddling with
this query for some time, i cannot seem to get it to run any faster
and was curious if i am over looking something, or am i simply stuck
with a slow query. The problem is that this query runs each time a
user comes to our website, so the slowness tends to add up a little.
Query:
select t1.city, t1.region, t1.latitude, t1.longitude from
hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1
Explain:
mysql> explain select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid
and ('2720518136' between t2.startIpNum and t2.endIpNum) limit 1\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 20029
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: locId
key: locId
key_len: 5
ref: helloneighbour_com_1.t1.locId
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
Table structure T1:
***
mysql> describe hn_iplocation;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| locId | int(16) unsigned | | PRI | NULL| auto_increment |
| country| char(2) | YES | | NULL
||
| region | char(2) | YES | | NULL
||
| city | varchar(45) | YES | MUL | NULL
||
| postalCode | varchar(7) | YES | MUL | NULL
||
| latitude | float(9,5) | YES | | NULL
||
| longitude | float(9,5) | YES | | NULL
||
++--+--+-+-++
7 rows in set (0.00 sec)
Table structure T2:
***
mysql> describe hn_iprange;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| startIpNum | int(10) unsigned | YES | MUL | NULL| |
| endIpNum | int(10) unsigned | YES | | NULL| |
| locId | int(16) unsigned | YES | MUL | NULL| |
++--
Is the Optimizer on 5.0.20 broken?
Hello All,
I have a next problem. After upgrade from 4.1 branch to 5.0,
the productivity of our applications has decreases too much.
Below is a tipical case.
Table 'account' has primary(department,account) and 40777 records.
Table 'payment' primary(department,account,year,month) and 3831797 records.
In this case all ok ('where' uses primary key of table 'account'):
> desc select * from
> account a inner join payment p using(department,account)
> where a.department='1' and a.account=1000
++-+---+--+-+-+-++---+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+--+-+-+-++---+-+
| 1 | SIMPLE | a | ref | PRIMARY,account | account | 4 | const
| 1 | |
| 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 10 |
HAS.a.department,const | 28738 | Using where |
++-+---+--+-+-+-++---+-+
This case not working in 5.0.20 ('where' uses index of 'account' with
10 resulting rows):
> desc select * from
> account a inner join payment p using(department,account)
> where a.street=10
++-+---+--+-+-+-++-+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows| Extra |
++-+---+--+-+-+-++-+-+
| 1 | SIMPLE | p | ALL | PRIMARY | | |
| 3831797 | |
| 1 | SIMPLE | a | ref | PRIMARY,account | PRIMARY | 10 |
HAS.p.department,HASVODA.p.account | 1 | Using where |
++-+---+--+-+-+-++-+-+
I don't think that selecting for fullscan of table with 3.5 million
rows (instead of table with 40 thousends) is a good idea.
Is this a known bug?
WBR!
Juri.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
Nils Lastein schrieb: After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files from the disk. When putting these files into another mysql server I get: mysql> select * from validate; ERROR 1033 (HY000): Table './mydb/validate' was created with a different version of MySQL and cannot be read Unfortunately I'm unable to access the disk anymore, so I cannot see what version of the server generated these. How do I do that? Nils Tried to load them into a hex-editor? Tried to find something to it in the docs? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group-wise maximum
Hello, I'm working on using a temporary table to get the group-wise maximum for a number of items. I have a table structure like: CREATE TABLE item ( item_id int not null primary key, name varchar(100) not null); CREATE TABLE attribute_a ( index (item_id, time_id), item_id int not null, time_id datetime not null, value int not null); Each item has many attributes. The time_id of an attribute contains the time of the /last /update to the attribute. My objective is to return the the attribute's value for each item at a requested time. I believe the best solution is to get the maximum time_id's (<= requested time) and dump them into a temporary table. Then use these times to retrieve the actual values. My problem is that as I add a number of attributes the query time bogs down when creating the temporary table. The query looks something like: CREATE TEMPORARY TABLE times (INDEX (item_id)) SELECT item.item_id, MAX(attribute_a.time_id) AS attribute_a_time FROM item LEFT JOIN attribute_a ON (attribute_a.item_id = item.item_id AND attribute_a.time_id < '2000-10-01 00:00') GROUP BY item.item_id; I add a LEFT JOIN for each attribute that I want to retrieve (up to 60). Let's say each attribute table contains 10,000 rows (all with dates less than '2000-10-01'). If I understand things correctly, using this query 10,000 rows would be added for each attribute that I joined. If I added 60 attributes MySQL would have to handle grouping 600,000 rows! Is that correct? Is there a better solution? I tried using sub queries but that didn't seem to be much faster. If I had 1,000 items to search for 60 attributes the sub queries would be called 60,000 times. Any ideas? Thanks! Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determine version of *.frm, *.MYD and *.MYI
After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files from the disk. When putting these files into another mysql server I get: mysql> select * from validate; ERROR 1033 (HY000): Table './mydb/validate' was created with a different version of MySQL and cannot be read Unfortunately I'm unable to access the disk anymore, so I cannot see what version of the server generated these. How do I do that? Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rename a DB
Hardi Have a look at "Rename Database" at http://www.artfulsoftware.com/queries.php#16 PB Hardi OK wrote: Hi, Anybody know how can i safely and easily rename a database in MySQL 5.0.19. Have tried some tips from google results (most of them are for MySQL 4.x) but no luck till now. Any help would be greatly appreciated. Rgds/Hardi No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.4/332 - Release Date: 5/4/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.4/332 - Release Date: 5/4/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql4.0
Hi Is mysql 4.0 still supported or EOL, as the recent advisory says users of 4.0 should upgrade to 4.1 to patch the security problem. Subsequently I have noticed a new 5.x release and new 4.1.x release and no 4.0.x release. Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server has gone away. Suddenly.
Hi,
I'm running MySQL 5.0.19 (Debian sid, package revision 3) on a Dual
Opteron250 64 bit machine with 4gb memory.
The problem i'm experiencing is that at certain moments, the connection
between my perlscripts and the database gets disconnected:
| DBI connect('products','beheerv',...) failed: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98
| Master.pm: unable to connect to products db: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98.
And:
| DBD::mysql::st execute failed: Lost connection to MySQL server during
| query at /var/web/cron/stats.pl line 673.
| DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
| /var/web/cron/stats.pl line 677.
I thought the query at that spot would be wrong, but it turns out that
this alone is not causing the problem. If i skip over the rest of the
perlscript and jump to that line 672 immediately, it works like
expected.
I'd like to hear how I can debug this. MySQL doesn't seem to log
ANYTHING about this issue. All I get is the messages above. I turned on
query logging, and it just shows the query that is executed at line 673
of stats.pl:
| SELECT theme, cat, subcat, p_ids,
| UNIX_TIMESTAMP(insert_time) as insert_time
| FROM log_product
| WHERE insert_time >= FROM_UNIXTIME('1146348000')
| AND insert_time < FROM_UNIXTIME('1146952800');
(this query, when redirected to a textfile returns about 25 megabytes of
data, in ~540784 rows)
After that no more queries from the same connection ID are logged.
But that is expected, as the connection was lost ;)
Can anyone shed any light on this issue?
Thanks!!
Sander.
--
| Honk if you love peace and quiet.
| 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to rename a DB
DOH! Sorry - I totally misread the question! A quick google for "rename database" shows Adrian is correct! -Original Message- From: Adrian Bruce [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 9:51 AM To: George Law Cc: Hardi OK; mysql@lists.mysql.com Subject: Re: How to rename a DB Stop the server, go to the MySQL data directory and physically change the name of the directory that corresponds to the database. Restart MySQL server and SHOW DATABASES to see the change take effect. George Law wrote: > Hardi > > I rotate tables out on a monthly basis. The way I do it is: > > rename table1 to table2 > > If you need a new copy of table1, you can do : > > create table table1 like table2 > > > -- > George > > -Original Message- > From: Hardi OK [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 04, 2006 11:19 PM > To: mysql@lists.mysql.com > Subject: How to rename a DB > > Hi, > > Anybody know how can i safely and easily rename a database in MySQL > 5.0.19. > Have tried some tips from google results (most of them are for MySQL > 4.x) > but no luck till now. > Any help would be greatly appreciated. > > Rgds/Hardi > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rename a DB
Stop the server, go to the MySQL data directory and physically change the name of the directory that corresponds to the database. Restart MySQL server and SHOW DATABASES to see the change take effect. George Law wrote: Hardi I rotate tables out on a monthly basis. The way I do it is: rename table1 to table2 If you need a new copy of table1, you can do : create table table1 like table2 -- George -Original Message- From: Hardi OK [mailto:[EMAIL PROTECTED] Sent: Thursday, May 04, 2006 11:19 PM To: mysql@lists.mysql.com Subject: How to rename a DB Hi, Anybody know how can i safely and easily rename a database in MySQL 5.0.19. Have tried some tips from google results (most of them are for MySQL 4.x) but no luck till now. Any help would be greatly appreciated. Rgds/Hardi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to rename a DB
Hardi I rotate tables out on a monthly basis. The way I do it is: rename table1 to table2 If you need a new copy of table1, you can do : create table table1 like table2 -- George -Original Message- From: Hardi OK [mailto:[EMAIL PROTECTED] Sent: Thursday, May 04, 2006 11:19 PM To: mysql@lists.mysql.com Subject: How to rename a DB Hi, Anybody know how can i safely and easily rename a database in MySQL 5.0.19. Have tried some tips from google results (most of them are for MySQL 4.x) but no luck till now. Any help would be greatly appreciated. Rgds/Hardi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to see why client got blocked
another question on this error message: is it possible to see the count of errors for each host from some table or file? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query of a query?
> Not necessarily. I would think the CREATE SELECT statement would be > the closest equivalent. > > http://dev.mysql.com/doc/refman/4.1/en/create-table.html > Thanks much for the reply, John. That surely would help with our future applications. But this time, we only have read access to the database. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The MySQL Stored Procedures and Functions is ready ?
> The MySQL Stored Procedures and Functions is ready to use on production > systems ? > > or support is very ammature ? > > I need to know because is a project desing decision ! > > Tnks !! > > PLEASE I NEED OPINIONS By the time your project is finished designing, it will be ready ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The MySQL Stored Procedures and Functions is ready ?
The MySQL Stored Procedures and Functions is ready to use on production systems ? or support is very ammature ? I need to know because is a project desing decision ! Tnks !! PLEASE I NEED OPINIONS i'm crazy to use this ? Tnks in advance MySQL , InnoDB and Linux ! -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://javacoffe.blogspot.com*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into "my eyes" Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching a large table
All: I am developing a search engine and using MySQL as the backend database management system. Under normal circumstances, when users search through large volumes of records on a search engine site, the results are broken down into pages. When a user clicks on the 'Next' link, the system will re-query the database and return those records that pertain to that page (records 10 through 20, perhaps). This, as you can plainly see, requires the application to re-query the same data from the same table each time the user clicks on a 'Next' or 'Previous' link. I would imagine that this may lead to some resource contention on that main table, especially when many users are using the system simultaneously. I've never seen vBulletin's code, but I have been able to determine from careful analysis (and testing) that they employ a search table, of sorts, that contains the returned records from a search. So, when a user of the system submits a search query, the system returns the records and throws them into a separate search table, identified by a sequential primary key field. Then, the system uses that search table to display the appropriate records according to the respective search identifier (referenced in the URL), never touching the main table again until a brand new search is performed. This seems to be a pretty good way to facilitate large text-based searches. Are there any other mechanisms that can be used to build a powerful, yet quick and light on resources, search system? Is a fulltext index the best way to achieve maximum performance with this kind of search? Thanks. -- Steve - Web Applications Developer http://www.sdwebsystems.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Output to a file
- Original Message - From: "Payne" <[EMAIL PROTECTED]> To: Sent: Friday, May 05, 2006 12:09 AM Subject: Output to a file Hey, been trying to output a select statment to a file, all the books I have only show how to input from a file, what is the correct way I thought I could do select * from my_toy >> `/tmp/my_toys` But I get an error. Here is a snippet from some documentation about MySQL which I wrote for myself. It shows a different technique for capturing output from a batch file into an output file; if the batch file contains 'select * from my_toy', it will capture the output in a file. It's not exactly what you want but maybe it will be "close enough". Running a script from OS prompt If you are connected to the database and are at an OS prompt, use this pattern: mysql < batch-file > output-file For example, if I want to run a script or batch file named my_batch_file.sql and write the output of the script to a file named my_batch_file.out, I'd need to do this: mysql < my_batch_file.sql > my_batch_file.out If you are NOT connected to the database, use this pattern: mysql -u username -p < batch_file > output_file For example, if your user name is 'fred' and your password is 'dino' and you want to run a script or batch file named my_batch_file.sql against database 'barf' and write the output to a file called my_batch_file.out, you'll need to do this: mysql barf -u fred -p < my_batch_file.sql > my_batch_file.out [Be sure to supply the password when prompted.] -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.4/332 - Release Date: 04/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to see why client got blocked
I experience that my slave gets blocked after a while (a couple of slave stop and slave start happen in the meantime). In errorlog I see Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Host 'myhost.mydomain.de' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'' errno: 1129 retry-time: 60 retries: 86400 Slave and Master are 5.0.20. How can I see why the slave was blocked? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Coded fields
I would approach this in practically the same way as yourself but used
an enum field in the student table (like what you were thinking). When
a user wants to add or remove an enum value you can build an appropriate
MODIFY statement like:
ALTER TABLE my_table MODIFY my_col ENUM('K','X') NOT NULL;
It is slightly more robust this way as depending on what SQL mode you
are running in it is possible to identify or block any incorrect values
being inserted into the student table (although this already may not be
possible depending on your interface). You would also have to change
the code_key to varchar though to allow different length and mixes of
lookup codes.
Does this help?
Ade
John Heim wrote:
What is the best way to create a coded field? I want to do something
similar to enumeration but I don't want to have to define the values
at table creation time because sometimes the end-users need to add or
remove the codes.
I've been using char binary fields in my database to this point
figuring that takes only one byte per stored code. Then the values can
be ASCII chars and would be kind of meaningful if retrieved from the
database. For instance, I might have 'f' for freshman, 'S' for
Sophomore, 'J' for Junior, and 's' for Senior. Actually, there's like
20 different categories for students. So then I have a lookup table
for codes:
create table codes (
code_type varchar(10,
code_key char binary,
code_text varchar(80)
);
Then I can do left joins to retrieve a description of the code if
necessary. For example:
INSERT INTO codes VALUES ('class', 'F', 'Freshman');
INSERT INTO codes VALUES (class', 'S', 'Sophomore');
INSERT INTO codes VALUES ('class', 'J', 'Junior');
INSERT INTO codes ('class', 's', 'Senior');
SELECT C.code_text AS academic_class
FROM students S
LEFT JOIN codes C ON (C.code_type='class' AND S.class=C.code_key);
That particular example might work better with enum but it's a
deliberately trivialized example. Most of my coded fields have 5-20
possible values.
My problem is that I've had some codes imposed upon me that are 5
chars. I don't know if I should just start over or what. Maybe other
people deal with coded fields in a totally different way that is way
better than what I've invented.
Suggestions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
