Testing The Database? Backing Up, General Checks
I want to know what else I can be doing to check the stability of our database. Currently what I do every evening is download the complete database through PHPMyAdmin on our dedicated server, once downloaded I start up the Wamp Server and import the database locally through PHPMyAdmin on my own machine.
The next thing I do is run the Check Tables command on all the tables to see if everything is where it is. It always comes back as OK on each table so I presume everything is ok?
Is there anything else I should be doing, or is what I am doing correct?
Every so often we delete auctions to free up space in the database. Once this is done we have overhead in the tables. Sometimes this can be 2-10mb, so what I do then is run the optimise command on the tables, again am I correct in doing this?
Finally our database is now over 40mb, to me this massive as the largest database I have worked on before has been 4mb, please dont laugh.
What sort of database sizes have you seen in MySql or are you running at present? What is MySql like in the long run stability wise. If looked after can we get many years of good service from it.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
General Question About Special Query For News Database
I have a question about understanding how I should accomplish my aim: I have a database called news. It has the rows headline, date, category and the text of the news in it. These news should appear on my index page but only the five latest news should be visible to the user. (Older news should be archived and the user can look at them in a link at the bottom of the most recent news (5). When he/she clicks on the link all news are listed on a seperate page.) I would like to know how the query is for the most recent 5 news to display and whenever I add another news again only the latest news including the last added will be displayed on my index page. If this isn't possible with an MySQL query, maybe there's a different approach to this with PHP?
Bogus Datasets For Database Testing
I'm teaching myself mysql and wonder if there are any sources of inane, complex, bogus data that I can download and insert into a table(s.) I have created some very small sets to practice joins and whatnot but they are not very big or complex, or exciting. compressed tar of text strings (no blobs) could have plenty of data but still be a manageable download. It would also be a nice test of my machine and tuning skills.
Backing Up A Database?
Just to see if it would work, I exported my database using phpmyadmin (as a SQL file), then I dropped the database. I also use a ulility (MYSQLautobackup) to do the same thing, only it emails me a gzip file. Now, when I try to import the emailed file, I get this? (screenshot #1) But if I import the .sql file instead I get this (screenshot #2) and everything works.
Backing Up A Database
I want to back up a database that I have on my university computing account. Problem is they don't have phpmyadmin. I do have ssh access though.
Backing Up A Database
I have been using phpMyAdmin and have been very happy wih it. My website is hosted by a very capable/helpful person and that is good too. However, I have been having problems transfering large amounts of data. For instance to export a table with student information, names, courses, addresses, schedules etc., is impossible because the request times out. I don't have access to telnet. How should I handle such exports/imports?
Backing Up MySQL Database
I have an application using MySQL running on Windows Server 2000. If I were using Microsoft SQL Server 2000, I would need special software to back up an open SQL Server database. Does this same problem exist with My SQL? In otherwords, could I take the native NTbackup.exe that comes with Server 2000 and back up an open MySQL database without stopping the engine first?
Backing Up A MySql Database
I've taken a look at mysqlhotcopy and mysqldump but neither seem to do what I need them to do which is to backup only the data which has changed since the last backup. Is there a way to do this? Of course, being able to recover the database from these delta backups is important.
Backing Up MySQL Database
I have an application using MySQL running on Windows Server 2000. If I were using Microsoft SQL Server 2000, I would need special software to back up an open SQL Server database. Does this same problem exist with My SQL? In otherwords, could I take the native NTbackup.exe that comes with Server 2000 and back up an open MySQL database without stopping the engine first?
Backing Up Mysql Database
How can I use phpMyAdmin to backup both my database structure and the data within each table. My database has 4 tables. I need to move the database to another server and i do not know much about MySQL commands.
Backing Up A Non-local Database
I've just started working with MySQL, and have gone from developing php applications on my own computer to putting them out on the Web. I used to have a small batch program that would run on my machine that would do a backup of my Timesheet database. The syntax was simply: mysqldump --user=dave --opt --result_file="time_backup.sql" timesheet When I moved the application over to our company's Web server, I still wanted to do an automatic backup daily, and I put this into a batch file: mysqldump --user=dave --host=annexweb.kwic.com -p******* --opt --result_file="time_servback.sql" timesheet But when I run it to test it out, I get this error message: #1045 - Access denied for user: 'dave@adsl-216-94-26-35.kwic.com' (Using password: YES) I know I have the username and password right (I use them to log on through PHPMyAdmin). Can anyone point the way to figuring this one out?
Backing Up Very Active MySQL Database
I'd like to know how some of you with very active MySQL databases (like large vBulletin forums) handle backups? mysqldump raises the server load significantly and slows the site. I think I may lose queries here if they back up while it's trying to do the backup. mysqlhotcopy complained about not being able to open some of the table files. I think it's running into locked files despite its attempts to lock them for itself as there are many queries per second on almost all tables. This is a dual-core Opteron 280 server with 2GB RAM. The database is about 2GB in size right now, and all tables are MyISAM type. Are there any settings I should tweak to get mysqldump or mysqlhotcopy to do the backup without effecting live services, even if the backup takes slightly longer to run?
RDBMS Checks
I mean: which RDBMS 1) allows to declare that relationship is in fact 1-1, and 2) protests when one tries to violate the 1-1 relationship, that is inserts into a table two records with the same foreign key?
Group With Two Date Checks
please accept apologies - post edited 23:19. Query below successfully groups for records having greater than 1 results while joining to a State table: State.StateAbbr | State.StateTerm posting table: JPosts2.JID | JPosts2.JState | JPosts2.Class runtime table: JRun.JRID | JRun.FK_JobID | JRun.StartRun | JRun.EndRun SELECT JPosts2.JID, JRun.StartRun, JRun.EndRun, JRun.JRID,StateTerm, COUNT(JState) FROM JPosts2 INNER JOIN State ON State.StateAbbr=JPosts2.JState INNER JOIN JRun ON JRun.FK_JobID =JPosts2.JobID AND `JClass` = 'Mechanics' GROUP StateTerm HAVING count(JState) > 1 ORDER BY JState ------------------------------------------ QUESTION: Can the query be coaxed to return results for the same JState where there are both: at least >0 expired and at least > 0 current date checks grouped by StateTerm for the given JClass? ...JRun.JRID =(SELECT max(JRun.JRID) from JRun WHERE JRun.FK_JobID = JID) AND (CURRENT_DATE () > DATE_ADD(JRun.EndRun,INTERVAL 18 DAY)) // expired ...JRun.JRID =(SELECT max(JRun.JRID) from JobRun WHERE JRun.FK_JobID = JID) AND JRun.StartRun BETWEEN DATE_SUB(JRun.EndRun,INTERVAL 18 DAY) AND CURRENT_DATE //current
Turning Off Foreign Key Checks
Is it possible to temporarily turn off foreign key checks in a db? I know how to do it from the mysql prompt but I have a number of scripts using Java, etc which seem to be breaking because of the table ordering with respect to foreign keys does not seem to be honored (tables with FKs are declared before the tables the keys refer to). If there was some way to turn off checking for a db, run all the creation and import scripts I have, then turn the checking back on it would save a ton of effort.
General Questions
I want to learn PHP but I hear that I need MySQL in order to run PHP. Firstly: 1. What is MySQL?? 2. What version should I download??? 3. What do I have to learn in MySQL in order to run PHP??? 4. Is MySQL difficult to learn???
Coding In General
I just started a website, and am trying to run phpbb on it. I installed (I believe correctly) php and (again, I believe correctly) MySQL. I have enabled the MySQL extension in the php.ini, and added the extension_dir field for the lbrmysql.dll as well. When I try to run the install.php now for the phpbb, I now get a CGI time out. If I undo the changes I made, then it tells me it can't access MySQL. I know that I have PHP issues as well, but most of my problems seem to stem from the fact I have no clue how to configure the relationship between SQL and PHP. Please tell me where to start looking, and any information I need to post to help give an idea of what's going on.
General MySQL Questions
Im working on a project that involves table full of domains and I wanted to do a search script which searches those domain names based on certain criteria. I have just a general sql question list so if you would be so kind just to give rough simple queries on how to do them or toss me a link or two that I could read up about it. Example 1: Domain: domaintools.com I figured to search the domain for a word match I would use like? Select * from table where domain like='%domain%' Example 2: Domain: domain-tools.com I am trying to figure out some way to search for all domains where a - is not present, not sure how to do this one. Example 3: Domain: abc.com Domain: abcabc.com I am trying to figure out some way to return results based on there length I am not sure how to count in sql to return the length. For example Select * from table where length(domain) > 8 Example 4: Domain: search4.com I am trying to figure out a way to return all results where a number is present or not present, no clue how to do this one. Basically just a general how to is what im looking for or any resources that could help me on this. Unfortunatly I havent a clue how to do queries like this so I have no Idea what to even start searching for.
Create General Query Log
I am trying to turn on General logging on mysql server [on a RHE linux box]. If I go >mysqld -l filename on shell, it says "command not found". What is the best way to create General log on a live server. Somehow MYSQL server doesn't have a my.cnf file. I have searched all over but there are no signs. Whenever I have to start/stop/restart the server, I use the ready-made mysql script.
General Field Name In Query
I wanted to know if it's possible to have a query like this: SELECT *_as FROM table so that every column which ends in _as is selected.
General Update Question Please
I have been learning about various join statments that are used to DISPLAY data on a web page. My question pertains to updating that same information. For example, here is a sql statement I use to populate a form. Code: mySQL = "select id , email , title, url, description, status, verification_url, entry_date, category from link_partners inner join links on links.link = link_partners.id and links.shop = '" & session("shop") & "' where link_partners.id = '" & request("link") & "'" The sql joins two tables in a database and uses the data to populate form. The form is intended to enable someone to update the data and upon submission of the form UPDATE the db data. On the server side do I write multiple sql UPDATE statments and execute them for each table or is there a way to do this in one swoop.
General (and Simple) Question About Mysql/PHP
I have just come across something I'm supprised I havent run into before..... If I have a table called users: id | group | username | password | email | site 1 | admin | user1 | hashed-pass | myemail@gmail.com | mysite.com 2 | mod | user2 | hashed-pass | myemail@gmail.com | sitepoint.com 3 | standard | user3 | hashed-pass | email@yahoo.com | google.com ect.....like for 1000 users, so I cant just grab them all and match the id to the user_id in the comments table And then a table called comments: id | user_id | comment_main 1 | 2 | this is a comment 1 | 3 | wow nice 1 | 1 | lol And you wanted to output this: HTML <a href="sitepoint.com">user2</a> <p>this is a comment</p> <a href="google.com">user3</a> <p>wow nice</p> <a href="mysite.com">user1</a> <p>lol</p> ....how would you do it? fetch the comments table, foreach it, and then indivudally grab each users row one by one? (I know that would work, but seems like with 100 comments on a page, it wouldnt be a good idea for a stable site...) EDIT: or is it best to foreach the comments table, and: MySQL SELECT * FROM users WHERE id=Ƈ' AND id=ƈ' AND id=Ɖ' ...etc.....ending up with a VERY long mysql query? Please advise...thanks Sorry if this is abit of a newbie question, its just one of those things I've never had to do, so never worried about it, and cant find seem to find any "tutorials" like it...
MySQL General Query Log And Performance
I'm wondering how big impact on performance would have turning on the general query log. I read in manual it should be turned off in production environment to increase the speed but I didn't find any information about performance (how much cpu/disk usage it would take). Is it like 1% performance lost or is it bigger?
Latin1 (swedish_ci) To Utf-8 (general Ci) Conversion In MySQL 5?
Could someone tell me the easiest way to convert a MySQL 5 database (including all tables and content) from latin1 with the default swedish collation to utf-8 with the general collation. I know this has been asked before, but the answer seems to depend on the MySQL and PHPMyAdmin version thats being used. I'm currently running PHPMA 2.7.0-pl2 and MySQL 5.0.19.
General Error: Field 'event_date_time' Doesn't Have A Default Value
im posting the code of my page and i want to submit a date and time in my database for now eveyrthing is working but when i try to submit it it shows me this error and i really donr know why: (if someone can help me i will be very thankfull!!!) The web site you are accessing has experienced an unexpected error. Please contact the website administrator. The following information is meant for the website developer for debugging purposes. Error Occurred While Processing Request Error Executing Database Query. General error: Out of range value adjusted for column 'type' at row 1 The error occurred in C:CFusionMX7wwwrootexamplesdatetimeprompt.cfm: line 18
Testing MySQL
I'm trying to test MySQL (i've already tested php and it works, so I'm not real sure I need this step, but I'm sure I need to know how to do it reagardless). I test it using localhost as the hostname and it works except that this is what the page looks like. ....
Testing For Bit Set In Int Field
Say I have a column of type int(4) and it represents a bitmapped pattern, so bit pos 1 means "apples", 2 means "oranges", 3 means pears etc. The value 3 is "apples & oranges". How would I construct a query such that I could return these? For example, if I was searching for oranges and the bitmap field was called type in table fruit the logic would be SELECT FROM fruit WHERE (type & 0x02) > 0; All my tests so far just return all values.
DB Testing Software
Are there any available tools for testing mysql db performance? What do you guys typically use for testing your db's performance and data integrity?
MySQL Testing
I just recently installed MySQL and am also running an APACHE 2.0 server on my computer (Windows XP machine). I followed the instructions in the help document on installing MySQL and I believe it is installed properly, no errors occured, it was pretty straight forward. However, when it comes to testing, the documentations on give commands using the "Windows Command Line Client" and not the MySQL Command Line Client. I installed MySQL as a service, that turns on and off with Windows.
How Do I Set Up My System As A Testing Server
I work on a Windows XP Pro. Platform and I am having trouble setting my system up as a Testing Server for PHP pages. I want to be able to test scripts and build my database. I tried downloading and installing MYSQL and PHP but it still does not work. Is there some kind of Configuration I need to do in the PHP.ini file?
Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to stress test. Can someone please point me to some testing tools we can use?
Equality Testing With NULLs?
When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?
Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to stress test. Can someone please point me to some testing tools we can use?
Testing SSL Conections W/ MYSQL
I recently installed all MySQL 4.1.1 including max RPMS. During the installation i used "--nodeps" on MAX package and at Shared-compat cause they were requiring the libssl.0.9.6 and libcrypto.0.9.6. I am using a RH 9 and these files are installed on my system. The result for mysql query : show variables like 'have_openssl'; is have_openssl | YES I created a user using the grant command with 'require ssl' like this: [color=blue] > grant insert,select on MyDatabase.* to myssluser@myhost require ssl;[/color] Query OK I can connect using this username and CLIENT_SSL param (isn't this param just for php 4.3 or higher?): mysql_connect($dbserver, "myssluser", $dbpass, CLIENT_SSL); It runs on my php 4.2.2 compiled with openssl 0.9.7, but i need to TEST if this is a real ssl connection.
Load Testing MySQL
I know this is quite a bit off topic, but i was wondering if anyone else out there had any experience with Segue's Silk Performer load testing tools. More specifically, i am trying to compile a test script to allow me to perform some transactional database testing to load test a MySQL 4.0.13 database server running behind some apache webservers. I have obtained an ODBC driver from MySQL and have some specific queries i would like to use in the test. I am have used SP to load test the webservers specifically, but i am not 100% sure how to run this against a database server now. If anyone has any experience they can share, please feel free to email me off list.
Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE. However, when I tried testing that this works by entering:- select * from address, name where address.name_ID=name.ID; It says empty set. What am I doing wrong?
Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys. The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again. for example: select * from table where id=2 order by index_field desc; 4/5 seconds. select * from table where id=2 order by index_field desc; 0.02 seconds. select * from table where id=3 order by index_field desc; 4/5 seconds. select * from table where id=3 order by index_field desc; 0.02 seconds. etc, etc...
Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE. However, when I tried testing that this works by entering:- select * from address, name where address.name_ID=name.ID; It says empty set. What am I doing wrong?
Testing Parameters Within A SELECT Statement
I am building a SELECT statement where I will be passed search parameters depending on what user has entered. Each parameter may have a value or maybe NULL. What construct (CASE, IF, etc) can I use to build my WHERE clause so that the passed value is included or if NULL, all rows are returned. SELECT * FROM table WHERE col_a = val_a (or if VAL is NULL then all rows) AND col_b = val_b (or if VAL is NULL then all rows) AND col_c = val_c (or if VAL is NULL then all rows) etc ...
ERROR 1045 When Testing In The Control Center
In the MySQL Control Center 0.9.4-beta, I right click the top level server name and choose the Edit option. Then I have entered a User Name and a Password in the General TAB. I then mark the option "Promt for Password" and hit the "Test" button. I get a small window where I should enter the password for User name@Host name. I enter the password and hit the OK button. Now I get the error message "ERROR 1045; Access denied for user; 'User name@Host name' (Using password: YES)" When looking in the Help menu, the Contents - F1 is not activated, so I can't get any online help. I'm just wondering why the test for the password failed and why there are no online help in the consol?
Testing Access To MySQL-DB In Windows-Network
Background: I am writing in C# a program, which reads data out of a MySQL-DB. When the DB is on my local developer PC everything is fine. But when trying to access the MySQL-DB on a server in our Windowsnetwork, I receive the message: "Host '**' is not allowed to connect to this MySQL server".
Backing Up
I am running off of a MySQL database on a remote server. I am truly concerned about backup. I have phpMyAdmin running on the remote server, and I see I can "Export" to various formatsd from there, but nothing to "Import if I need to reinstate my data. How do you typically backup a MySQL database? Cant I simply copy the files? How does one do this if I want, say, to backup the files from the remote server, to my local harddrive?
Backing Up
I am running off of a MySQL database on a remote server. I am truly concerned about backup. I have phpMyAdmin running on the remote server, and I see I can "Export" to various formatsd from there, but nothing to "Import if I need to reinstate my data. How do you typically backup a MySQL database? Cant I simply copy the files? How does one do this if I want, say, to backup the files from the remote server, to my local harddrive?
Testing For An Empty Set Returned By A Nested Select Statement
For those of you following the "While Loop Within Select" thread... I've been restructuring my database to incorporate the changes. Now I've got a table of skipped stops rather than a table of stops (I need it that way for various reasons) and I need to return the schedule to the user as long as the user's stop isn't in the list of skipped stops. SO say something like: SELECT yada yada yada FROM schedule AS 's1' WHERE ( SELECT skippedstop_id FROM skippedstops WHERE schedule_id=s1.schedule_id AND location_id=$origin ) == "EMPTY SET" Only I have no idea how to actually test for the empty set from the nested select...
Backing Up Just Portions Of DB
I have a DB of NFL players and stats. What I want to do is a sql 'backup' of just parts of 3 tables i.e. create a new DB for each team. I have tables for nflteams, players and player stats for all the NFL. I want to create a DB for say just the Rams. Any ideas?
|
TRACKER
