MySQL View Points From Enterprise DBAs
One of our companies clients has asked us about redoing one of their highly profitable web applications in PHP and MySQL (The had an outside consultant recommend it). Their current application is built in C#.NET and SQL and is not currently clustered or load-balanced. They want to rebuild it as they have learned a lot about what they want since the original design and they know they need to make significant changes to the application to get the new features they are after and also address functionality that their current customers don't like.
The company contacted the original .NET author (who also happens to be an experienced Microsoft SQL DBA) to see about redoing the applciation in PHP and MySQL and the author panned MySQL and stated that their own company had attempted to migrate one of their applications to MySQL and that from his perspectives there were many things that causes A GREAT DEAL of extra DBA overhead.
I was hoping that more experienced DBAs using MySQL would be able to confirm or deny some of the Microsoft SQL DBAs claims - I have never worked with anything except MySQL and I have not used it for very long or even very often, so I am unable to successfully evaluate the claims.
By no means do I want to get into 'down with Microsoft' thread here.
Here are his claims:You can not centralize stored procedures for multiple databases. If you do you have to open up a whole lot of permissions that expose the system too much?The use of stored procedures and triggers is really complicated and you have to get down to a very low level of coding to understand, create or edit them, which makes the DBA's job harder and even once experienced more time-consuming?It lacks quite a few advanced features that Microsoft SQL has (he didn't cite which ones)?The upgrade path (of MySQL) is not standardizes so features that worked in version x are not guaranteed to work in version x.5 or y?There is no standardized way to backup and restore the databases and between the different methods there is a lack of compatibility. This means that backing up a MySQL database in one way - say for example MySQLdump and trying to restore it using phpMyAdmin or the MySQL administrator will not work.For those of you that have worked with different Databases do you think that MySQL is as easy to administrate and update as other Databases and even more specifically Microsoft SQL?
View Complete Forum Thread with Replies
Related Forum Messages:
Configuring MYSQL Enterprise Monitor
I was given the setup files for configuring the MYSQL Enterprise Monitor with dashboard for testing in Local. 1. I have installed MYSQL Server to my localhost [Windows]. MYSQL SERVER Version: 5.0.50sp1a-enterprise-gpl-nt. 2. I have installed the MYSQL Network Monitor to my local system [Windows] With all options like admin login, agent and other details. But my doubt is how to set my MYSQL DB Server for regular monitoring using the MYSQL Enterprise Monitor. I.e I just want to know about the Server setting like how to add my localhost server to the Mysql Enterprise Monitor. We have a Enterprise Subscription but as iam just a developer i dont have the login id so can i get help from here?
View Replies !
How Do You Install MySQL 5.0 Server And Administrator On Red Hat Enterprise Linux 3 WS
Could someone point me to instructions for installing MySQL 5.0 server and administrator on a Red Hat Enterprise Linux 3 WS system? I just freshly installed the system and deliberately did not select MySQL serveras part of the installation I downloaded the 5.0 rpm Community Server edition, and that installed fine. My problems came while trying to build the administrator. Basically, I got caught up in a loop of incompatable modules: # rpm -i mysql-3.23.58-16.RHEL3.1.i386.rpm warning: mysql-3.23.58-16.RHEL3.1.i386.rpm: V3 DSA signature: NOKEY, key ID 025e513b error: Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-16.RHEL3.1 MySQL conflicts with mysql-3.23.58-16.RHEL3.1 Suggested resolutions: /var/spool/up2date/perl-DBD-MySQL-2.1021-3.i386.rpm If up2date the suggested resolution, it says I need a library from mysql-3.23.58-16.RHEL3.1.i386.rpm. I will gladly read the "manual" or docs, and would love a pointer to them.
View Replies !
MySQL Enterprise Server With InnoDB For Mission-critical Data?
However recently the company I work for has the requirement to upgrade their database system. The data here really is mission-critical and we can't afford to loose any of it, and we need 24-7 uptime, with good performance (we have lots of clients so there'll be heavy load), any downtime will cost us. What I'd like to hear is peoples own experiences with MySQL Enterprise, good or bad, and if it's up to the job. Looking around the net I've seen lots of supposed expert DBAs saying that there's no argument; you should go with one of the "big 3", these being DB2, Oracle or MSSQL. However I get the feeling Oracle and DB2 would be a massive task to configure, and MSSQL - well it runs on Windows, we can't have any downtime no matter how good people say it is these days. So my idea was; InnoDB on 3 servers - a master and 2 replication slaves, with one slave off-site down a 10Mb line (wide enough pipe?). We use the on-site slave to run our daily backups, and point our reporting web sites at it too. the off site is for worst-case scenario disaster recovery. How much would we be better off spending more £ on one of the "big 3"? Would our data be safer on these? Would we get better performance with these? Is there a better model I could use for the MySQL setup?
View Replies !
Integrating Mysql, @mail, And Suse Linux Enterprise Server 9
I have recently received a admin job at a small web hosting company (small) and the current platform is windows and I will be responsible for creating a linux based platform and eventually moving all information over to linux form the windows setup. The idea is like this run Suse Linux Enterprise Server 9 using 2 web server running apache, 2 email servers using @mail, and having 2 front end servers with 4 storage nodes clustered. This company also has some custom database query programs to retrieve information. I might be in a tad over my head at this point, but that is ok there isnt an immediate rush on integration. I am looking for any other admins that might be able to offer any opions regarding this basic setup or that might have this setup and offer any suggestions concerning the actual construction of this type of platform.
View Replies !
MySQL Client Crashes With Segmentation Fault On Red Hat Linux Enterprise IA64
I have an IA64 Red Hat Linux Enterprise Edition system and I tried to install the binaries recommended on MySQL. However, when I run the mysql client, I get simply: Segmentation fault. So I tried to do a basic source compile, which went fine, but when I run mysql client, I get this: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 to server version: 4.1.12 Segmentation fault When I try the flags recommended by MySQL (--with-extra-charsets=complex among others), the make test fails on ucs_binlog - something to do with charsets I believe? The mysql user, group etc. are all set up. Strangely, the mysql client works in batch mode so if you make a file that reads: SHOW DATABASES; Then type: mysql < commandfile It'll display Database mysql test Without any faults.... Anyone any ideas or suggestions?
View Replies !
Decimal Points
I have a form for real estate listings. I have a field for the price, which would never have decimal points, as prices are in whole dolloars only. I set my field in my db to both double and then to decimal. If I enter a price without the dollar sign such as 6,000,000 the value 6 gets stored. If I enter 6000000 the value 6000000 gets stored.
View Replies !
Sum Points Of Opponents
I've 2 tables. Table `points` has 3 columns: Id Name Points Table `opponents` has also 3 columns: Id Name Opponents Now I need to make a ranking of the players from table `points`. So first I need to sort on points, but when points are equal I need to sort on the cumulative scores of the player's opponents. When player 1 has played against players 2, 3 and 4, I need to sum the scores of the players 2, 3 and 4 and output them behind the score of player 1. Does anyone know how to realize this?
View Replies !
Find Xy Points
I have successfully imported an ersi shapefile into a spatial indexed mysql table. I would like to use that table to store the polygons. I then have a 2nd table that stores x,y data of points. I need to select the rows of the points that fall within the polygons from the 1st table and give the selected row the polygon id in which it falls. I use php to run my queries. Should I use the spatial index table or is there an easier way to conduct this query?
View Replies !
Sakila Points
behind some peoples names I see "Sakila Points". Can anybody point me to an announcement/description of this?
View Replies !
Points Within Polygons
I've got a table with lat/longs for specific points: CREATE TABLE [Point] ( [PointID] [int], [Lat] [numeric](10, 6), [Lon] [numeric](10, 6) ) I have another table with polygon borders as lat/longs: ...
View Replies !
Getting Points Minus Lowest Two Entries
I'm trying to get a sum of fishing tournament points for each team in a club on my website, but I need to subtract the lowest two (2) entries for each team before displaying them. To complicate things, some teams may not fish all the tournaments in a given year, so I guess I would need to start by finding out using the current date how many tournaments there have been in the current year for the club (using my tournaments table) and how many tournaments have been fished in the current year by the team (using my results table). I would need to use a zero (0) for any "no shows". I'm really stuck. Here's an example of code I use for another club that throws away just the lowest score, and lucky for me, it can't be a zero (0), so I can just use min() here. Code: ....
View Replies !
MySQL Enterprise And MySQL Monitor
I'm trying to figure out the actual benefits of the MySQL Enterprise subscription service, which includes MySQL Monitor, a so-called "Virtual DBA Assistant". Has anyone used any of the four service levels of the MySQL Enterprise subscription service? The MySQL site has a lot of glossy marketing info and happy testimonials, but I'm still trying to determine if it makes sense for a small business (i.e., not a mega corporation) to invest anywhere from $695 to $4,9995 for the service. Has anyone used MySQL Monitor? Does it live up to the claim of being a "Virtual DBA Assistant"?
View Replies !
Enterprise Manager
I'm looking for a GUI manager tool like MSSQL Enterprise Manager for MySQL (free, if possible). I'm using XP. I'm especially interested in a GUI for creating views in which I can graphically manage INNER and OUTER join between objects Does anyone know a tool like the one I just described in my dream
View Replies !
Community To Enterprise Upgrade Path
We are a start-up business which has decided to use MySQL Community version but as we grow we dread the thought of redoing everything when we are ready for the Enterprise version. Is/will there be a utility/program that will simplify this process?
View Replies !
VIEW In MySQL
select NO_RESPONSABLE, NOM + PRENOM as NAME from RESPONSABLE Gives me the number of the ID, but also "0" as the result of the concatenated NAME.
View Replies !
Join On A View Without The View
I was wondering what the syntax would be to have a left join from one table to a resulting query without the view. Currently I use a view and it works fine but I was wondering how to achieve this without creating the view. Here is the current view's SQL statement ( without the create view etc)...
View Replies !
How To View Catalog In MySQL
How can I query mysql to view the catalog? (By the catalog I mean the special internal database that holds the meta-information about all other databases in the system, including descriptions of all the tables and their fields.) I know how to do this in psql, so I imagine there is a similar facility in mysql. FWIW, my OS is Linux.
View Replies !
MySQL View For A DB2 Database
I want to create a view in MYSQL, but the database/table is in a DB2 database. Is it possible ? I have heard that such a thing is possible between say ORACLE and SQL Server. Bottom line, my tables are there in DB2, and i want to create a view in MySQL database. The reason of doing this is that i'm using Ruby/RubyonRails on AIX platform, since i could not find a DB2 Driver on AIX for RAILS, i want to go for this indirect approach. (Rails works fine with MySQL). hence from Rails i want to access the MySQL (Indirectly to DB2)
View Replies !
View MYSQL TABLES VIA FTP
I have been asked to play around with the current website and make some changes etc, before i launch into it i want to get a good idea of how it is structured etc. It's written in PHP with a MYSQL backend. I have setup a test environment on my local machine and copied the files from the existing site by grabbing them via FTP ... now everything runs via localhost except i cannot connect to mysql via phpmyadmin i played around with the settings in the config.inc.php file and it allowed me into phpmyadmin but there was no database?? How do i see the actual mysql tables or at least get a .sql file dump so i can recreate an identical test db ??
View Replies !
MySQL 5.0.1 : CREATE VIEW And UNION
I don't manage to create a view from the UNION of two tables. This statement works all right : CREATE OR REPLACE VIEW myview AS SELECT 1 AS mycol UNION ALL SELECT mycol FROM mytable2; This one does not : CREATE OR REPLACE VIEW myview AS SELECT mycol FROM mytable1 UNION ALL SELECT mycol FROM mytable2; and I get the error "ERROR 1143 (42000): create view command denied to user 'root'@'localhost' for column 'mycol' in table 'myview'", although I am creating the view using the "root" account. Version used : MySQL 5.0.1 alpha snapshot (Windows binary).
View Replies !
View Mysql Users Password
How can i view a mysql users account's password, i have got all privileges so it should be possible? When i have a look in the mysql table the passwords are coded.
View Replies !
MySQL View Using Data From Firebird Db
The website uses data from a MySQL database by I need to also access data from a Firebird database. There is a common key between the 2 databases and I would like to create a View in MySQL that reads data from the Firebird database into the View and then displayed on the website. Is this possible in MySQL? I know that I could use a PHP script in the website itself to do something similar, but I would prefer to perform this at the database level as I believe the performance would be better.
View Replies !
How Do I Turn On Logging To View Errors In MySQL 5
I'm a new MySQL user. Just switched over from the Bill Gates world and am joining the open source side finally. I setup a website using Joomla! 1.x with PHP5 and MySQL 5 on the backend. However when I submit a post in my forum on my new site, the page goes blank yet says it's finished loading. In the Microsoft world you can echo back to the screen errors and figure out which row, record or table was causing the error. Can you do the same using MySQL or does it have a logging feature that could help me troubleshoot and see why my posts are hanging when hitting the submit button via my browser?
View Replies !
MySQL And Borland Database Engine And View
I have an application using Oracle and MSSQL with BDE, and I would extend it to support MySQL now. I have an error - 'invalid table name' - when I open a view. This is works with Oracle and MSSQL, but doesn't work with MySQL.
View Replies !
View The Timestamp In MySQL As The UNIX By Adding 0 To The End Of The Row
i've read that in MySQL 4.1, you can view the timestamp in MySQL as the UNIX by adding 0 to the end of the row. Can someone help me modify my code so that this same idea will also in my code? <tr><td colspan="2"> <?php $datetime=$row["time" + 0]; $year = substr( $datetime, 0,4 ); $mon = substr( $datetime, 5,2 ); $day = substr( $datetime, 7,2 ); $hour = substr( $datetime, 9,2 ); $min = substr( $datetime, 11,2 ); $sec = substr( $datetime, 13,2); $orgdate = date("l F dS, Y h:i A", mktime( $hour, $min, $sec, $mon, $day, $year)); ?> <b>Date:</b> <?php echo $datetime; ?></td></tr>
View Replies !
How To View Arabic Data In MySQL Query Browser
I am making a website on linux and i am using a program called rekall to view the database and also the table was created using this program. most of the data in the database are in Arabic and rekall views the data in an acceptable manner with some errors and without any configuration but when I tried MySQL Query Browser on windows and several other programs including navicat, toad, heidiSQL all the arabic data was not viewable except the data in blobs which can be exported to extenal files The website encoding is [ar-eg] and [utf-8] I want to view the arabic data stored in the table using a windows program and if the program is fast and simple it would be much better as I am not the one who is going to use it
View Replies !
MySQL VIEW -> MS Word Mail Merge - Grouping Problem
Basically I am trying to create a simple invoice system that takes a query of a particular set of customer orders in the form of a VIEW (see below) and then Microsoft Word's mail merge facilities access this VIEW and then do a mail merge based upon it. The fields in each invoice are things like ShopperID, address1, address2 and other customer info as well as the products bought along with the corresponding quantity, price and amount (this essentially being quantity*price) etc. MySQL CREATE OR REPLACE VIEW invoice AS SELECT o.*, i.Product AS Product, i.price AS Price, i.qty AS qty, i.amount AS Amount FROM orders o INNER JOIN items_ordered i ON i.ShopperID = o.ShopperID WHERE o.DATE BETWEEN ��-10-01' AND ��-10-31' MS Word's mail merge connects to the database and accesses the VIEW query results just fine. The problem arises with the grouping of products (along with their associated quantity, price etc.) under each Shopper (or ShopperID to be more precise). Now, in the VIEW above I did not GROUP BY ShopperID so I used GROUP_CONCAT in conjunction with GROUP BY ShopperID to get all the products bought by a customer on the same record. MySQL CREATE OR REPLACE VIEW invoice AS SELECT o.*, GROUP_CONCAT(i.Product SEPARATOR ' ') AS Product, GROUP_CONCAT(i.price SEPARATOR ' ') AS Price, GROUP_CONCAT(i.qty SEPARATOR ' ') AS qty, GROUP_CONCAT(i.amount SEPARATOR ' ') AS Amount FROM orders o INNER JOIN items_ordered i ON i.ShopperID = o.ShopperID GROUP BY o.ShopperID HAVING o.DATE BETWEEN ��-10-01' AND ��-10-31' I thought this would work just fine but the strange thing is that MS Word displays the Product field correctly (which has been concatenated using a new line ) but it doesn't display anything for the quantity, price or amount fields. I have tried using different separators like ' / ' and ',' but the same problem occurs. Does anyone have any ideas about why it is doing this and how I can find a solution?
View Replies !
Using A View
I want to use a view but I'm unsure how exactly MySQL handles a view and I can't seem to find anything about it in the docs. I guess my question is: Is a view stored as a physical table or is it stored in memory or what? I'm asking because I want to make sure that if it is stored in memory that the amount of ram on my db server doesn't become an issue.
View Replies !
What Is A VIEW?
What is a VIEW? Why when I go to chapter 23 does it tell me how to create, alter, and drop a view, but it doesn't say what it is or what they're used for? Maybe I missed it, but is there any place in the reference where a sentence starts, "A VIEW is...". The closest I've come (looking outside of MySQL site) is a view is 'a predefined join of specified tables'. I take that to mean it is essentially a shortcut to a specific data set relationship and based on that I can see how it is useful, but why isn't this concept defined in the manual? Is it really so obvious that it doesn't require any explanation, or god forbid a demonstration of how it is used? Even more evidence that this is a stupid question is that when I google 'what is a mysql view' I get bounced back to the reference.
View Replies !
Is This Possible With A View?
I'm trying to cut down on the size of my database so I've changed it so that rather than implicitly setting every value in one table, I've split the data into two tables: one table (iData) contains data for the start of evey week: iData ----------------------------- 2000-01-01 Volume: 20000000 2000-01-06 Volume: 30000000 2000-01-11 Volume: 40000000 Another table (jData) contains the delta (difference) from the previous iData entry: jData ----------------------------- 2000-01-02 Volume: 1 2000-01-03 Volume: -2 2000-01-04 Volume: 1 2000-01-05 Volume: 3 2000-01-07 Volume: 2 2000-01-08 Volume: -5 2000-01-09 Volume: 6 2000-01-10 Volume: 1 2000-01-12 Volume: 2 2000-01-13 Volume: -4 2000-01-14 Volume: 4 2000-01-15 Volume: 3 Obviously this will save a lot of space as the delta is much smaller than storing the exact value (standard compression technique). So what I would like to do is create a view to automatically display all of the data as if it were all stored in one table: All Data: --------- 2000-01-01 Volume: 20000000 2000-01-02 Volume: 20000001 2000-01-03 Volume: 19999999 2000-01-04 Volume: 20000000 2000-01-05 Volume: 20000003 2000-01-06 Volume: 30000000 2000-01-07 Volume: 30000002 2000-01-08 Volume: 29999997 2000-01-09 Volume: 30000003 2000-01-10 Volume: 30000004 2000-01-11 Volume: 40000000 2000-01-12 Volume: 40000002 2000-01-13 Volume: 39999998 2000-01-14 Volume: 40000002 2000-01-15 Volume: 40000005 I know I can do this programatically, but is it possible to do this with a view? Any pointers would be much appreciated.
View Replies !
View Schema
is there a tool to view a schematic of a MySQL DB I don't mean the .sql file I mean a pretty chart type schematic.
View Replies !
Mysqldump View
I try to dump the view definition and records using the following command. mysqldump -uroot -psecret! --databases test --tables proxy_view > mytest.txt What all I get is the view definition without records. What is the correct method of dumping a view?
View Replies !
View Dependencies
When creating a view, it will (almost certainly) take information from one or more existing tables/views in the database. If I come to the database with no knowledge about the view (other than its name), how can I determine which underlying tables it depends on? (I can do this is PostgreSQL by performing queries against the pg_class & pg_depends internal tables but I haven't discovered any way of doing it in MySQL either with SQL queries or with the C API.
View Replies !
Exponential View
how to store numbers in exponential view to MySQL. E.g. 4,56e-9 = 0,00000000456 I have 4,56e-9 as a variable in perl and want to store it to MySQL. Decimal as typ seems not to work. Does i realy have to convert it to 0,00000000456?
View Replies !
VIEW Problem
I create the next view: CREATE VIEW VideoDB.FindCustomer AS SELECT id, last_name&' '&name&' '&initial AS cname, picture, birthday, birthday_already, account_type, rents, exp_date, deposit, exp_deposit, auto1, kinship1, auto2, kinship2, auto3, kinship3, auto4, kinship4, auto5, kinship5, auto6, kinship6 FROM VideoDB.customers; When I execute the next statement the result is 0: select cname from videodb.findcustomer; How can I concat the whole name. last_name&' '&name&' '&initial AS cname
View Replies !
View Foreign Key
My problem is that after creating a foreign key, I cannot see in MySQL/phpMyAdmin that a column is a foreign key, here is an example of 2 tables (I wanted to create a table with users in a community an create a table=relationship which contains the information about which users know each other): CREATE TABLE `user` ( `id` double NOT NULL auto_increment, `security_code` varchar(50) NOT NULL, `active` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `knows` ( `user_id_1` double NOT NULL, `user_id_2` double NOT NULL, KEY `benutzer_id_1` (`benutzer_id_1`), KEY `benutzer_id_2` (`benutzer_id_2`) ); Now ... after creating (using the ALTER command of MySQL) two foreign keys (linking 'knows' with 'user') in the 'knows' database (which are of course 'user_id_1' and 'user_id_2') I cannot see any changes on my tables in phpMyAdmin. Why? After using the export function of phpMyAdmin, there is nothing about the foreign keys in the generated SQL. Why? And another question concerning the relationship 'knows', would you give me some idea about how to create it in DBDesigner 4 or Workbench? There is something that I do not understand: In school we learned a lot about ERM. And in ERM you were able to define attributes for relationships (for example 'date' for table 'knows', which would inform me about when we have added the info). In DBDesigner a relationship can only be made as a connection between Entities, but without any attributes. And how would I define such recursive relationship like 'knows' (from above) in DBDesigner 4 then, so that the tool would add to the relationships foreign keys automatically and export the relationship 'knows' as a table later on?
View Replies !
SQL Source For A View
How do you obtain the SQL source that created a View? I can Describe the view, but that doesn't give me the SQL that created it. . . . I should have searched the forums a little longer. The answer lies in the SHOW CREATE VIEW viewName statement.
View Replies !
|
TRACKER
