Re: Mysql Schema design Rollback necessity Question
Hi, I am new to using mysql. I want to prepare an application for my employer. The application will be accessed by staff from as many as 10 different departments such as sales, marketing, admin, finance etc. The users will be using DML commands on the tables. My question has two parts: Part I: While designing the schema of the database, I have two choices: Scenarios: 1. Create multiple tables, one for each department. The relationship for most of the tables is one-to-one. 2. Create one master table so that each department updates its respective columns in the same table. Please advise which choice is better. I fail to see what kind of data a table (one for each department) would hold? How would a single table (one master table) hold the data for the processes of this application? What should the application do? Questions: 1. With single table will table locking become an issue if multiple users edit the table simultaneously or is it something that mysql can handle without problem? It depends on the storage engine. 2. What is the maximum recommended size of a table for mysql? How many columns should be master table should have ? Is it recommended to design a master table having more than 200 columns? Have you ever read a book on database design normalization? PART II: Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This is my typical DML command: I am not using any rollback statement to rollback the db if the DML command is not completed successfully. Is it advisable to use rollback? If it is how should I modify the above statement to include it ? If the DML command failed and modifies a single row only, the rollback won't do anything, but if it updates multiple rows or trying to do a unit of work inside the same transaction, things become different. Have you ever read about atomicy on database transactions? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=arch...@jab.org
RE: Security issues
Good Morning Rob- I agree with you that security is a very serious topic and should be addressed as such Please read security alert page listed at tech-resources http://dev.mysql.com/tech-resources/articles/security_alert.html I hope this addresses your question, Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: wult...@gmail.com Date: Mon, 24 May 2010 13:45:35 -0700 Subject: Re: Security issues To: mgai...@hotmail.com CC: je...@gii.co.jp; mysql@lists.mysql.com On Mon, May 24, 2010 at 1:42 PM, Martin Gainty mgai...@hotmail.com wrote: Good Afternoon Rob- if you're implementing either glassfish or weblogic webserver your best fit solution would be Oracle Identity Manager there are 'other' identity solutions such as RSA which are 1)far more complex .. 2)virtually hackproof.. at random intervals RSA implements an alternate encryption algorithm with an alternate keysize RSA issues smart cards which contain sufficient biometric information to authenticate you (and pass the authentication token to the OS) does this help? Martin Gainty I am explicitly not setting up identity solutions or anything else. All I want is a page from mysql which lists security issues.and what versions are effected. I don't think that this is such an insane thought... -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
Re: Mysql Schema design Rollback necessity Question
Hello Lightingale,
Lightingale wrote:
Hi there,
I am new to using mysql. I want to prepare an application for my employer. The
application will be accessed by staff from as many as 10 different departments
such as sales, marketing, admin, finance etc. The users will be using DML
commands on the tables. My question has two parts:
First problem: you are letting your users run direct commands against
the database.
One of the biggest roles in an application is to isolate and protect the
data from stupid user mistakes. Not only should your application
filter, validate, and sanitize their input but you also need to
encapsulate (with your application code) all of the functions they need
to perform against the data. That way, if there is a problem with how
things are going you will know exactly where to look.
If it is a requirement that the users change data directly, then why
write an application in the first place?
Part I:
While designing the schema of the database, I have two choices:
Scenarios:
1. Create multiple tables, one for each department. The relationship for most
of the tables is one-to-one.
2. Create one master table so that each department updates its respective columns in the same table.
Please advise which choice is better.
You actually have more choices than that. You could create multiple
databases, each with a full compliment of application tables.
#2 may be a bad option - it's fine to have columns that only certain
users can update but if you propose to have several sets of columns
copies where each set belongs to a single group, that would be horrible.
Work up from a rational database design and build an application to
support it. Try very hard to not design a database that works with your
code. Databases operate most efficiently when you use set theory and
not iterative application design principles to access your data. What
that means, specifically, is avoid writing code that does dozens or
hundreds of small single-row manipulations when one statement could be
written to process the entire batch of data. Of course, there are rare
exceptional cases to consider but at this stage, I don't think you are
there yet.
Questions:
1. With single table will table locking become an issue if multiple users edit
the table simultaneously or is it something that mysql can handle without
problem?
It depends on how you use the table, how it is organized, and which
storage engine you choose.
2. What is the maximum recommended size of a table for mysql? How many columns
should be master table should have ? Is it recommended to design a master table
having more than 200 columns?
For me, the design any table with more than about 20 or so columns is
suspicious. Please do some homework and learn more about relational data
modeling and the principles of normalization
We, the other members on the list, will be happy to answer any specific
questions you may have.
PART II:
Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This is
my typical DML command:
$query=update users set
id='$id',password=\$password\,pin=\$pin\,hint=\$hint\,fname=\$fname\,lname=\$lname\,manager=\$manager\,deptt=\$deptt\
where username=\$myuser\;
if ($debug $dbgusr == $ses_username) { echo($query); }
if (!($rs1 = $db-execute($query)))
{
DisplayErrMsg(sprintf(Data Select Error: %d:%s\n, mysql_errno(),
mysql_error()));
return 0;
}
else
{
// updatelog($id,users,$query,usrmgr.php,$ses_username,$myip);
DispMsg(User Profile edited successfully);
}
I am not using any rollback statement to rollback the db if the DML command is
not completed successfully. Is it advisable to use rollback? If it is how
should I modify the above statement to include it ?
Thanks in advance for your help.
As mentioned in the other reply, ROLLBACK only applies to active
transactions. Please do some additional homework and figure out which
storage engines support transactions and how you start and end a
multiple-statement transaction.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Security issues
Back when this was a day-to-day concern of mine, I used to check CERT's website (the section now known as their Vulnerability Notes Database, http://www.kb.cert.org/vuls). Unfortunately, I see that the last entry for MySQL is from years ago. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
source backup.sql - troubleshoot
Hello, I have a mysqldump file created by AutoMySQLBackup. And it hangs when I do a mysql source backup.sql It is 32 MB - it creates 4 tables and after creation of each table it populates it. Actually it only creates the 1st table before hanging. My first thought is it would be nice to echo each of the commands it is executing so I can tell whoch command it is that is the problem. My second thought is that there must be a quite a few troubleshooting techniques i could/should use - except I dont know hwtat they are. All advice gratefully received! BTW Platform redhat 5.2, Server version 5.0.54a-enterprise -Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: source backup.sql - troubleshoot
Use something like SQLYog, load your .sql file into the editor, run all the commands one at a time, or in bulk. Look at the info window for what line failed. -Original Message- From: Sydney Puente [mailto:sydneypue...@yahoo.com] Sent: Tuesday, May 25, 2010 2:39 PM To: mysql@lists.mysql.com Subject: source backup.sql - troubleshoot Hello, I have a mysqldump file created by AutoMySQLBackup. And it hangs when I do a mysql source backup.sql It is 32 MB - it creates 4 tables and after creation of each table it populates it. Actually it only creates the 1st table before hanging. My first thought is it would be nice to echo each of the commands it is executing so I can tell whoch command it is that is the problem. My second thought is that there must be a quite a few troubleshooting techniques i could/should use - except I dont know hwtat they are. All advice gratefully received! BTW Platform redhat 5.2, Server version 5.0.54a-enterprise -Syd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: source backup.sql - troubleshoot
On Tue, May 25, 2010 at 2:39 PM, Sydney Puente sydneypue...@yahoo.com wrote: Hello, I have a mysqldump file created by AutoMySQLBackup. And it hangs when I do a mysql source backup.sql It is 32 MB - it creates 4 tables and after creation of each table it populates it. Actually it only creates the 1st table before hanging. My first thought is it would be nice to echo each of the commands it is executing so I can tell whoch command it is that is the problem. My second thought is that there must be a quite a few troubleshooting techniques i could/should use - except I dont know hwtat they are. All advice gratefully received! BTW Platform redhat 5.2, Server version 5.0.54a-enterprise -Syd The easiest way to see what MySQL is working on is to start up another connections and run SHOW PROCESSLIST. Note that the command output will be truncated if it is long at all. If you need to see the entire query run SHOW FULL PROCESSLIST. Alternatively start up the mysql client with the --verbose flag. I don't recall in what version that became available. You will probably flood your term if you use this option. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb buffer pool size
Machiel, I'm going to disagree strongly with the previous advice you got. You should NOT configure the buffer pool to be larger than the amount of RAM you have. If part of the buffer pool is swapped out, then swapping it back in is MUCH worse than re-fetching the page. InnoDB doesn't know the difference between in-memory and swapped out. (That's the point of virtual memory; it's invisible to the program.) It assumes that a memory access is fast. If it turns out not to really be a memory access, but instead is a disk access to swap something in, then everything goes very badly. If you search for buffer pool size on mysqlperformanceblog.com, you will get good advice. You should also get a copy of High Performance MySQL, Second Edition. (I'm the lead author.) In short: ignore advice about ratios, and ignore advice about the size of your data. Configure the buffer pool to use the amount of memory available, subtracting what's required for the OS and other things on the computer to run effectively. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
