MYSQL :: Using Views In A Fairly High Demant Environment

MS Environment
A customer is pushing for the delivery of a MySQL backend solution for a third party system. Unfortunately beyond knowing it's a free open source equivalent of Microsoft SQL I know nothing about this product. The issue (well, it may be an issue...) is we are almost entirely Microsoft in our workplace and I'm unsure whether I should be concerned about placing a MySQL database on one of our Windows 2000 / 2003 servers (possibly) alongside existing SQL 2000 databases. Are there issues I need to be aware of or should I just give it its own server?

Proper Environment?
I'm not sure if MySQL is the environment I'm looking for or not. All I need is a simple database to track call-in records so that if a customer has to call back and they get a different rep than the one they spoke to the first time, the new rep can look up the notes from the last call. This would be much faster and much more efficient than the system we are using currently, in which we write down the call notes using pen and paper.

We only have 4 employees who would need to use the application at this time, including myself. I have the DB written in Access 2000 right now, and it's honestly such a simple DB that it would be quicker and easier to rewrite it in MySQL rather than migrating it to the server.

I'm thinking of using MySQL for this because we used it for a similar application at one of my previous employers, and because I don't have a budget or a dedicated server and MySQL is open source and can be run off of my current machine as long as I leave it running.

Can I Use Mysql4.1 On Aix 5.3 Environment?
I already know mysql4.1 binary does not provide for aix5.3.
So, can I use MySQL4.1 on AIX5.3 environment if I build from sourse file?

Upgrading From 3.23 To 5 In Development Environment
I am upgrading form MySLQ 3.23 to 5. What's the best
way to go about doing this? Do I nee to do anything special with the
permissions in the USER/DB/TABLE Tables or should it upgrade fine?

Client Sever Environment
i installed it then on a LAN environemnt in a computer lab, there mysql is installed on a single machine that would be used as server machine, and all other computers in the lab will be use as client machine, i have also added client machines ip in user table in server machine, but when i try to connect with mysql database server from client machine then it gives me error, i give the server machine ip in the conneciton but it does not recognizes it,how to make a client sever environment of MYSQL in a LAN .

How And Where Do I Place Stored Procedures In A Win Environment?
I use MySQL-front as a GUI.
A bit of a novice at this.
I do not run my own SQL server,mY Webhost does.
Will they have to make the stored procedure for me?

Migration GUI Toolkit For UNIX Environment
I would like to know if there is any Migration toolkit available for UNIX. I have a requirement where we want to migrate data and code from Oracle 9 to MySQL 5.

From Test Environment To Secure Commercial Use.
I'm relatively new in the database administration field. Uptil now I've only tried MySQL together with PHP in a test environment. I would appreciate if someone could answer a couple of question, I have regarding commercial use and a secure professional configuration.

Uptil now I've used XAMPP as my installation on my own computer on localhost. Now we'll install the same software package on a server. I guess XAMPP and PHPmyAdmin is not a professional setup right?

Questions:
1. What is the best recommended and professional setup for commercial use?
2. What is the best GUI for the database?
3. What is Frontend and Backend?
4. Backup setup? Just if anyone have some recomendation.

I've built a web based dynamic Content Management System in PHP and MySQL for internal use. Using the localhost as the developer platform. I've never before built something like this for commercial use.

5. How do best implement this system on our companys server, so we all can use the system?

MySQL Environment Vs Simple Standalone Databases Equivalence
Over the years I've been around several types of databases
DBase IV, FoxBase, Unify, Sybase (long long ago), Access, and now MySQL.
Most of them except Sybase which was client server were simple stand alone
applications in that each database could be associated with a specific file.

example: sales.db2, info.dbf, cars.mdb, recipies.mdb, etc.

Once created if I wanted to a) replicate them I could simply copy them to
different machines or b) copy it remotely to another machine I could use
FTP to accomplish that task. As long as a similar environment existed on
the remote machine then a person could access the data.

What I want to know is can I go through the development process (designing a database, the PHP files to to access and populate the data etc.) locally and then move the whole shebang up to a remote machine on the internet that runs mysql?

+----------------+----------------------+--------+
|Move database to| ftp from (exmpl. dir)|        |
|another machine | C:dbstuffmyDB.mdb  |  ??    |
|on the internet | to remote machine dir|        |
+----------------+----------------------+--------+

Does MySQL create a "file" that contains the table relationships and data that can be copied to another machine that has the MySQL environment enabled? (assuming the versions are similar)

Restart Slave After Master Has Died In Replicated Environment
Our master database ran out of connections and we had to restart the
server (couldn't SSH into the box). Rebooting the server worked and
of our clients resumed activity.

The problem is that the slave didn't start automatically so I tried to
do a START SLAVE; on it.

This is the latest shown in the error log on the slave.

040110 20:06:13 Error reading packet from server: binlog truncated in
the middle of event (server_errno=1236)
040110 20:06:13 Got fatal error 1236: 'binlog truncated in the middle
of event' from master when reading data from binary log
040110 20:06:13 Slave I/O thread exiting, read up to log
'user1_master.002', position 802078191

(THIS IS AFTER I DID A SLAVE_START TODAY 1/20)

040120 8:56:39 Slave I/O thread: connected to master
'slave@db1.ezboard.com:5000', replication started in log
'db1_master.002' at position 802078191
040120 8:56:39 Slave: there is an unfinished transaction in the
relay log (could find neither COMMIT nor ROLLBACK in the relay log);
it could be that the master died while writing the transaction to its
binary log. Now the slave is rolling back the transaction. Error_code:
0
040120 8:56:39 Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We
stopped at log 'db1_master.002' position 802078147.

High Availability In Mysql
Does anyone know what sort of HA features will be implemented in MySql ?
What I exactly want to know is whether MySql will provide a HA solution
similar to Oracle Real Application Clusters (RAC).

RAC allows multiple instances to manipulate a single database on shared
disks. If one instance fails, the others still can continue to provide the
same service. (This is quite different from MySql Replication which maintains
multiple copies of a single database.)

It may be too much to ask for free software, but I really wish that this kind
of features will be implemented in MySql.

High CPU Usage (99.9%) On RHEL4
i need your help on this issue. I am running a web server in my orginazation internally with mysql-4.1.12 and RHEL4. Now, its been only one day and using 'top' command its showing mysqld process using cpu 99.9% constantly. This is used by say around 4000 users.

My my.cnf file :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

output of mysqladmin version
..........
...
Threads: 53 Questions: 324297 Slow queries: 588 Opens: 39976 Flush tables: 1 Open tables: 64 Queries per second avg: 67.788

output of mysqladmin extended-status
Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 742 |
| Aborted_connects | 35 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 29401860 |
| Bytes_sent | 1049908921 |
| Com_admin_commands | 2352 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 40895 |
| Com_change_master ...................................................................

High Average Load (50%)
I have a Windows 2003 server with MySQL 5 installed (5.0.19). The server has a 3ghz processor and 2gb of memory.

The server has about 20 databases of the type MyISAM and there is almost no load trough queries. The mysqld-nt.exe process has an average load of 50% and goes up when a query comes in. Why is this? I have tried logging to see what MySQL is doing but I cannot find anything abnormal.

High Volume Of Inserts
I'm working on a pretty simple log analyzing stat app, and I've come to a point where I will need to make a large volume (2000+) of inserts into a mysql database.

Is there a way I can do this without crashing my server?

ORDER BY HIGH/LOW Count
here is my query,
SELECT CT_UID, COUNT(*) - COUNT(DISTINCT CT_UID) FROM clocktime WHERE CT_TimeOut BETWEEN $WeekBegin AND $time GROUP BY CT_UID;

All this query does is query a "detention" table and grabs a count of how many times a student has been sent to detention, see our school clocks students into a computer when they have "detention" and when they leave.
The query now displays the data by CT_UID (the students id), how do i order the data by highest or lowest count?

Select_full_join Number Is High
The variable "Select_full_join" is showing a number of 537. This seems very high, especially when the recommendation is to have this at zero.
I can get a full list of all queries generated on the site but how do I find out which ones aren't using an index in the join and how do I fix that?

MySQL Values (for Variables) That Seem To Be To High
phpMyAdmin shows my some values for variables in the status are marked red. I assume, that these values might be to high.

Section Handler:
Handler_read_rnd 414 k
Handler_read_rnd_next 316 M

Temporary data
Created_tmp_disk_tables 3 k

Key cache
Key_reads 78 k

Joins
Select_full_join 3 k

Tables
Opened_tables 6 k
Table_locks_waited 5

Now I've got a lot of questions:

1) For Handler_read_rnd it says "You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly."
How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this?

2) For Handler_read_rnd_next it says "Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have."
Same questions as in 1):
How could I find out which queries these are? And should I increase the Handler_read_rnd? How would I do this?

3) For Created_tmp_disk_tables it says "If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based."
The tmp_table_size value it 33,554,432 right now. How would I increase this?

4) Key_reads: "The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests."
My key_buffer_size is 16,777,216, Key_read_requests is 3M.
So the cache miss should be 78 k / 3072k???
What am I supposed to do here?

5) Select_full_join: "The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables."
Is there an easy way to check the indexes of all tables?

6) Opened_tables: "The number of tables that have been opened. If opened tables is big, your table cache value is probably too small."
table cache is 64. What size should it be? How would I increase this?

7) Table_locks_waited: "The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication."
Is there a way to find out which queries / tables should be optimized?

Group Votes Into Low, Medium And High
I have a table with votes:
---------
| vote |
| 5 |
| 1 |
| 9 |
| 6 |
| 2 |
| 1 |
| 3 |
---------
And I would like to see number of votes between 1-3, 4-6, 7-10

This select doesnt work:

SELECT count(vote<=3), count(vote>=4 AND vote<=6), count(vote>=7)
FROM user_bfcore_vote;

Detecting High CPU Usage Queries
I know I can log slow queries, but what about ones that require a huge amount of CPU power to process? I have a lot of those lately it seems and I am trying to track down the culprits.

Mysqld Process Running High CPU
My Mysql server is running at around 60% CPU even
when there's virtually no queries going through -
I turned on logging and have been watching the
queries and looked at mysqladmin processlist but there
is nothing unusual - this just started happening.
I tried using myisamchk which didn't find any
problems with my tables.

MYSQL - CPU Usage High On Windows 2003
I've been working on this for about a week now... When there is a lot of activity/traffic on the site mysql procees seems to "lock up" with high CPU usage and everything in windows seems to freeze. I am running mysql 4 on IIS 6 using an odbc connection via ASP.

I've tried everything, indexing, optimizing, pooling, disabled the virus software running in the background, I even tried using my.cmf, etc, nothing seems to help. Is there anyone who can help or anyone else experiencing this same problem? Could it be a hardware issue?

Mysql Select High Priority And Delayed Queries
I have a large database that has indexes on it. It also has alot of reads on the select statements of the database. I also have alot of people inserting new articles to the database through a submission form where they can only add one article per time.

The problem is that when someone inserts an article into the database it has to insert the info and also update the indexes that need to be changed.

Now when this is happening I think it is halting the select queries, making it sometime before a visitor can view a page that uses select queries.

I have now implemented the "SELECT HIGH_PRIORITY" tag in many of my normal select queries on the articles pages.

Is doing this just fine or will I also need to do something to the "INSERT" queries like "INSERT DELAYED"

Also, the inserts are inserted into the same table as the select queries are done.

I don't quite understand all of this, and have read about it on Mysql pages, but can't quite get it when it comes to speeding up things using high priority and delayed. I have the right indexes in place, so they are fine.

Mysqld Shows High Cpu Usage Over Extended Time, Restart = Normal
Occasionally in checking one of the servers, I noticed that mysql shows 85% + of
cpu usage essentially leaving the server at 0% idle. After monitoring it for a
few hours, the status did not change. After a stop and start of mysql, things
progessed normally. Checking back a few days later I noticed it was once again
sitting up there at 95% (or thereabouts) and doing nothing of value from what i
could tell.

Have restarted MySQL during peak usage times for that server and its database,
and it has showed normal loads and CPU usage (approx 20% CPU with .1 to .3
load). Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)?
Is there something I should check when next I notice the high CPU usage?

Views?
do views exist fysically a separate "table",
or are they generated on the fly whenever they are queried?

if they exist fysically they could improve performance (..php, web),
for example a view being a join between two or more tables..

Views?
do views exist fysically a separate "table",or are they generated on the fly whenever they are queried?

if they exist fysically they could improve performance (..php, web),for example a view being a join between two or more tables..

Views In 5.01
We have just loaded 5.01 so that we can take advantage of views. One question I have before I start experimenting is this: Can you "view" across databases? And if the answer is yes, can you "view" across servers?

Given two tables "company" and "people", each living in a different database (and potentially on different servers) can you give an example (the SQL) of how to link them in a view?

assume the following:

company
-------
c_id
c_name

people
------
p_id
p_name
c_id

Views In MySQL 5.x
Was wondering if anyone here has had any experience with views in mysql 5? I know what they are and how to use them, was just wondering if anyone here has before and if they feel it is better, in mysql, to just create the new table or use the view instead? Or perhaps a complex join?

Question About VIEWS
I have never used MySQL views before, however, in my current situation I will sooner or later need to use them.

I have one table which consists of sent messages and another which consists of recieved messages. When someone sends a message to another person, the message will be stored in their own sent messages. However, when they recieve a reply from the end user, the message will appear as a conversation as apart of their own inbox. So basically, the query which requests the inbox records will fetch both the sent and recieved message records.

So I need to use a view to fetch both messages, how do I do this?

If you guys are still wondering what I am talking about, then check out FaceBook's private messaging system.

Views Overhead
Not far long ago, I was posting a question about performance of database with a lot of tables. Now I have a good idea towards moving to a shared tables approach, so there will be only one set of tables for all users, but each of the users will have an own set of views like:

create view username_posts as
select id, title
from posts
where user_id = user_id
It would make database more efficient, and would need only a small set of changes to the application.

What do you think about this approach? Is there a huge overhead of using views like the one mentioned above, in comparison with adding the user_id = ... clause to the application? How well does MySQL (InnoDB) behave in case database has a lot of views?

As Views Really Supported
With MySQL 5 beta and InnoDB tables, are views really supported? I can see the syntax in the manual but I get syntax errors on even a trivial attempt to define a view as in

create view foo as select * from project

produces 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 'view foo as select * from project' at line 1

Phpmyadmin And Views
I created a veiw in phpmyadmin How can I modify it? when i click on structure instead of seeing the view I see it as if it was a table with fields.

Views In MySQL
Can I create views in MYSQL. Or is there an alternate technique.

Views In MySQL
I want to migrate my existing SQL Server database to MySQL. It had many views in it. When i tried to make views in MySQL 5.0, it gives error. I was suggested by this forum that views can be created in MySQL version 5.0.1, but i couldn't download it from anywhere. From where can i get it?

I tried to download from mysql.com file named "mysql-5.0.1-alpha-snapshot-win.zip".
This downloaded file gives me 5.0.0-alpha-nt server and 5.0.1-alpha client. This version is also not allowing me to create a view.

Views, Procedures
I have version 4.0.12 of MySql installed. I can not create views and stored procedures. Is this normal or is there something wrong in the installation?

Performance Of Views
I have a large table in a database that takes a long time to do any queries on. All user queries on this table are done as part of a background process at the moment.

The table holds transaction information from organisation and account id's; each organisation may have more than one account id. This table contains the raw transaction data, so there are many 1000's of records for each id. The table is keyed on customer number, which is different from organisation id and one customer number may have more than one organisation id. Code:

Views In MySql/PHP
This thread is answering a question from [url=http://www.codingforums.com/showthread.php?s=&threadid=12528[/url]this thread] -- I didn't want to distract from the original question.

To recap:A view is a virtual table. It is a stored query, usually based on two or more tables. It can be used as a table by other queries (by default, all queries created in Access are, in fact, views).

The main advantages are efficiency and security. You can merge complex tables that store complete information to produce more simple tables that contain live information without any data duplication. You can also give the view query different access, meaning that you can let people see only some columns of a table (eg a view restrict who can see creditcard numbers, without restricting who can see other customer details).

They are not supported in mySql.
Followed by:
what is the alternative to 'view' in mySQL/PHP then ...
The simple answer is there is no alternative. You can easily enough create a database layer that stores queries in php. But, if the user has access to run these queries on the underlying tables, then they also have access to bypass them.

You can also, with a little effort, create a method for building queries from SQL (this is a fairly standard way of operating a large system). However, you do not get the dabase level security benefits that you get from using views. Nor do you get the performance benefits that you get from views (I don't know all the details, but a view does run faster than a regular sql query).

You can use a degree of database level security -- by limiting access to tables using GRANT statements. With views, you can use database security to completely limit access to rows, columns, and calculated fields without any data duplication. Of course, it is possible to implement application level securty in PHP, but the dual (or more often treble) layer of security is quite useful when you're dealing with complex two and three tier systems.

Phpmyadmin And Views
When we create *.sql for out database, does it not copy views. I created *.sql and imported it to phpmyadmin, but it is not showing the view.

Do I have to re-write the view or there is some other solution?

Views In MySQL 4.1
Is it possible to create views in MySQL 4.1 or am I just wasting my time trying to figure out the syntax?

I've tried searching this site but can't find anything about it for 4.1.

Delete Views
how to drop a view in Ms Access using ColdFusion.I don't think the problem is ColdFusion, i think the problem is the query but it might be anything.

Views And MyODBC
well i just installed MySQL "5.0.1-alpha-nt-max" to play around with the new Features on my local PC.reating and accessing Views seems to work, but as it seems MyODBC 3.51.09 cant handle Views.
Are there any plans to support Views also via MyODBC ?
If so, is that Feature currently in the Bitkeeper-Repository of "MySQL Connector/ODBC 3.52" ?

Mysql Views
I have only ever developed database application using Mysql and so have limited experience of views and how they work. I'm sorry if this seems basic.
Suppose I create a view from a table, say A and tranform it using a select query to create my view, B.
If I want all the data from B, ie. SELECT * FROM B will the DBMS need to go back to the original data and execute the query that generated B and then return my results - or - does it store the data for view B in a table like format and then just quickly return that.
Essentially is there a performance advantage when using a view where the transformation from table A to view B is complex.

How To Overcome On Views Limits
I have 6 tables with many many columns and I have a SELECT statement that joins all the table and brings me all the columns in each table in single view. The number of columns that I get by executing this SELECT statement are more than 2000. The SELECT statement runs perfect and give me no problem. But the problem comes when I use this select statement in a view. I get the error message

CREATE VIEW failed because column '' in view '' exceeds the maximum of 1024 columns

Is there a way I can get all the columns in one single view. I know SQL Server View has maximum limit of 1024 colums.

Stored Procedure Vs Views
I am a little confused on the benefits of views when compared to stored procedures. When should you use a view and when should you use a stored procedure? Do either technology have a performance edge over the other?

Concept - Replacement For Views?
I'm kind of new to MySQL but am familiar with other DBs. Currently I'm working with a MySQL4 database and I have a very specific problem which could be easily solved via views.

Problem:
I have as follows -
tbl_cat
-id_cat
-txt_name
...

tbl_prop
-id_prop
-id_cat (fk)
-txt_desc
-txt_type
-txt_default
....

tbl_product
id_prod
txt_ref_name
...

tbl_prod_prop
id_prod_prop
id_prop
txt_hash

OK what is all of this? it's a dynamic product for a shop. How does it work conceptually? We create a product category(tbl_cat) and add properties to it (tbl_prop) such as name, picture, html text, etc...
Finally when we create a product, we determine it's category and fill in each of the properties.
My original idea was to use a view to generate the listing of the products and properties, but mysql4 doesn't have views...
What would be the best aproach? I thought of creating a cache table, where when a product is updated or inserted, I run a script that creates updates a record of a temporary dinamic table. Soething like a false view, updated on demand. Is that as stupid as it sounds?