Question questions regarding mysql

[marianne]

Good Day Banana,

Hi, yesterday I have explored for the first time mysql, using workbench. and I havent done anything major yet, I just explored around the IDE.

I have few questions to ask Banana:

1. What is the database file of mySQL database, as access have MDB and ACCDB files?

2. In the workbench, what are the purpose of the following: a.) EER diagram B.) views, routine, routine group in the physical schemata, c.) schema privileges, SQL scripts, d.) model notes.

3. what is the default storage engine of mysql?

4. What is sakila database?

5. How do you distribute the ms access FE and mySQL BE to the client or how do you package them all?

6. How to embed mysql server to access project that will be distributed thru the package?

7. I have this scenario: the main database of mysql is at the head office and we intend that the branches mysql database will be sent to the main office to be able to update the main mysql database with the branches mysql database records. How can I do that in mysql?

 

[Banana]

1) There is no single file here. Depending on engines being used, it can be spread across several files. However all of those files goes into a common folder named after the database which is stored inside the data folder.

Just to emphasize: In MySQL and SQL Server world, we don't work with files anymore. We just work with daemons and passing around files usually does not make sense except to perform an administrative task such as backing up.

2) As you're familiar with Access... Think of those as:

EER Diagram = Access's relationship windows
Views = Access's query
Routine = Procedure in a VBA module
Routine Group = VBA module (but that's misnomer because MySQL has no such concept; all routines are stored in a single group; WorkBench provides a extra layer that does not exist at all in MySQL)
SQL Scripts = (not sure if that is right as it's by memory) text file import (but more than just that)
Model Note = Comments you can make to help yourself keep track.

3) If you selected defaults in the installation, the default storage engine is MyISAM. It's nontransactional, non-ACID complaint but very fast. If you want something more like Access's engine (e.g. supports transaction, ACID, foreign keys) then you would want to use InnoDB as the default. However, there is no requirement that you must choose single engine; you can use several engines depending what is most appropriate within certain restrictions.

4) It's a sample database. It used to be a part of certification curriculm.

5) You do not distribute MySQL BE at all. You just distribute the Access FE & MySQL's ODBC driver and verify that all client machines can contact the server that hosts the MySQL daemon.

Again to emphasize; you have to change the way you think about packaging and distributing a front-end client to a RDBMS. With Access you had to worry about both FE and BE, but with a RDBMS, it's just FE and simply making sure everyone can ping the server and you're done. You do not need to provide them any access to MySQL's files. Let the daemon manage it.

6) Embedded solution would require a different license that would cost money. I do not have specifics but it does cost money to embed and I would say it's not worth effort because it's more for small devices, not for personal computer. For personal computer, using Access to connect to MySQL server remotely is good enough.

7) I really would not think about having multiple instances of MySQL at this point. Start out with just one and have all branches connect to this one. It can be scaled up when need arises but for now I'd sooner concentrate on developing the tool to work at all rather losing months doing nothing while they wait for no-show.

 

[marianne]

1. So if I am going to create tables in a database, where can I find it? Where is that data folder?

2. I thought so. why do I need to create relationship and view other than tables while I can create them on Access FE.

3. I see. the default made there was InnoDB. But what engine do you recommend most ?

4. I see.

5. When I say distribute the mysql be, I mean the database where the tables for data storage. Ofcourse I have to give them that database doesn't it?

6. I was thinking embedding both mysql and access in the package so that when the installation begin, both of them will be installed and the client need not worry to install them separately.

When I mysql 5.1, in the installation, it say there something in the developer components, "embedded server" sub component. What is it? Isnt it somekind for embedding mysql server?

7. this company does not use internet for its connections from head office to branches and vice versa. that is why it needs the mysql database to be backed up and sent to head office for merging of data. I need advice how to do this one Banana.

 

[Banana]

1) You don't "open" a file and choose "create table". With a RDBMS, you have to communicate with a daemon and make requests (e.g. passing a SQL statement "CREATE TABLE ....") and let daemon handle the creation of objects for you.

2) You want to define relationship in MySQL, not in Access. MySQL has no knowledge about relationship (and it's not really relationship when you create it in front-end). You have to explicitly tell the daemon that there is a relationship.

3) InnoDb makes more sense in context where we must have data integrity. MyISAM is good if you want performance and are willing to tolerate data corruption or loss due to lack of safety checks that most RDBMS enforce. But if you want to have a relationship with foreign key, then InnoDb is the only option.

5) No, no, no. They need to connect to the daemon in a session and request for the data. You *never* provide the direct access to files and there is no need ever to. The only people working with the files should be the database administrator and usually that's in context of performing administrative tasks such as backing up the database.

6) I'm not up and up with nuances of licensing terms and for all I know they may allow development but to deploy an embedded solution would require a substantial fee because that is not covered under GPL license, and I do not think it's appropriate for this scenario because it's usually intended in context of devices such as PDAs, routers, or other appliances but not on someone's PC.

7) Oh, boy. I wished you had mentioned that earlier (or maybe you did and I missed it) but that makes everything even more complicated and possibly even change everything.

Exactly why can't they use internet to connect? How would they share the data in first place? Do they have to be updated frequently and rapidly or can they more or less work independently and "blindly"?

 

[marianne]

1. ok. I will experiment on that.

2. I am thinking that only the manipulation of data like creating report are done in the FE. that is why it is my belief that I only need to create tables in the workbench and all other objects in the FE.

3. I decided to use innodb from now on.

5. On my projects, there is no database administrators, there is only the regular employees and the owner. Mostly the database BE and FE are in the same local computer.

6. So I need to install the Access package and the mysql installer separately, since there in no embedding allowed?

7. Yes, I think this project is a bit complicated. First off, the company does not use internet connection because they are afraid of viruses that may corrupt the whole computer system base on their experience. Secondly, dont want to incur more expense. Thirdly, they only need to merge the data on a monthly basis, that is once a month, or need basis.

Im am very sorry Banana, am really have no experience in sql's please bear with me.

 

[Banana]

1. I forgot to make a point. In Workbench, you create all tables & views and maybe some stored procedures you need. You then would export it into a .sql file which is just a text file with SQL commands which you can then execute in a MySQL client using SOURCE so the daemon will create all objects you need.

2. Yes, you would need forms and reports, but in this context, all tables & views & stored routines would be created in workbench and handed off to the MySQL daemon leaving you to concentrate on forms & reports & VBA modules in the FE, with maybe few queries.

5. How many users are going to actually use it?

6. Basically, yes. But again- you only need *one* MySQL server for the whole network (e.g. your office) to which several users would then connect and thus "share" the server.

7. Well, monthly merge is not so bad. I was thinking of daily or hourly which would almost demand an internet connection. Anyway, you would also use SOURCE command to output the contents of tables into a .sql file and pass it to the central office which can then read them into the master database. The only thing is that you may want to ensure that each server for each office assign unique IDs so it's easy to merge without duplicates. But first things first. You need to build the application and get it working...

 

[marianne]

thank you for your replies Banana.

1. so the .sql is the database of mysql like ms sql has .mdf. So what is this SOURCE that you added Banana? I dont know that SOURCE. So for example I have already create my supplier table and then I have recorded my first supplier information, where does those supplier information store? In the .sql file ?

2. Ok. But can I have the option to make ONLY the tables in the mysql database and the rest to the Access FE?

5. All the users that are going to be granted to use the FE.

6. I got this one. There would only be one computer that has mysql server and the other computers will use the Access FE and using ODBC to connect to the server.

7. You mention SOURCE again here Banana.

I have another question Banana.

8. How do I make the database of mysql secure if it is locally installed?

 

[Banana]

1. No. .sql file is just a script file. If you create a .sql file and open it in a notepad, you can see it contains several SQL commands, which is useful for porting data but it's not a data storage. You said you're using InnoDB, so the data would be stored in the InnoDb tablespace, which is probably something like ibdataXXX. It's contained inside a directory with the same name as whatever you named your database in the data directory. The data directory is contained in Application Data folder, which is a hidden folder and depends on which Windows you are using.

2. Yes.

8. Use Windows file permissions and deny anyone who's not the administrator the access to the MySQL folders in both Program Files and Application Data. That then leaves them with only option of opening a session with the daemon (who will then manage the security permissions to the data).

1 & 7: Source command.

As you are learning to use MySQL, I encourage you to get comfortable with consulting its manual very often. Here's three places to get started with understanding what SOURCE does.

http://dev.mysql.com/doc/refman/5.1/en/batch-commands.html
http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html (you need to scroll down a bit to get to source command)
http://dev.mysql.com/doc/world-setup/en/world-setup.html

 

[marianne]

1. I see. So if I am going to sent my database file to the head office or the head office will send database file to the branch, the ibdataxxx or something like that is the that file. Am I correct? Should it needs the .sql to accompany the ibdataxxx to be sent?

2. Ok

8. Ok. I got this one too.

1 & 7. Where is this done? in the command window of Windows? Cant this be done in the workbench or somewhere gui like Reverse Engineer subsection in the workbench?

I have the idea where but it needs password....is there a default password? I tried root, admin and blank only but I did not entered the command window.

I have another question:

A. Am I going also to use the DNS-less connection for connecting to server?


Thanks.!!!

 

[marianne]

Banana, if you can read this new post.

On this post number 6, number 7:

1. what do you mean output the contents of tables into .sql file..... I thought the database is the ibdataXXX which is the data storage ?

I am sure that all branches including head office are assigning unique ID.

Thanks.

 

[Banana]

1. This is my third time repeating this:

You do not need to touch the data directory for this purpose. The only legitimate purpose would be to create a complete backup, and even so, you don't want to backup individual files but rather the whole folder, to avoid situations where you have missing files that's required for restoring.

But in the context of sharing & merging data, forget about it. Do not even go there. Just have the daemon build the .sql or .csv file. You even could create a stored procedure so only a month's worth of data gets written into the file with single call and you would pass this file back to the headquarters and they would have the daemon read those files and insert/update the records.

So let me emphasize: Do not worry about files in Data directory. This is not like Access anymore. You go through the daemon for 99% of the time.

It is done using MySQL client, which you can access via Windows Command prompt.

Default password is blank, but when you did the installation, it does ask you if you want to set the password. Whatever you used to set the password... use that.

Workbench can do "Forward Engineer" to export all schema you make, but I do not think free edition allow "Reverse Engineer", and even so, it is not appropriate tool to use for administering. It's great for developing a database schema and that's it.

A. If you want to. I like using DNS-less connection but it's not required. If you want code for that, google for Doug J. Steele's DNS-less connection and Carl Prothman's connection strings for sample DNS-less connection with MySQL's ODBC driver.