[how to] MyISAM vs InnoDB for Forums |
- MyISAM vs InnoDB for Forums
- How do you get the graph plugin for Squirrel SQL?
- Lookup data while in mysql cli edit mode (external editor) (\e)
- How to -create- performance issues? - oracle [on hold]
- Combine - Select Into, with Insert Into Select
- ORA-7445 Error on 12c
- Execute a script with SQLPlus containing html entity mappings
- Tools to generate MySQL database migration script on Ubuntu [on hold]
- Connecting to Oracle Database with ODBC/Python
- How to prevent "ibdata files do not match the log sequence number"?
- Two Different Sources Taking Transaction Log Backups
- migration sql 2003 to 2008, Sql side and php side [on hold]
- Amazon rds lock wait timeout after restart
- Check if SQL Agent job is being executed manually
- SQL Server select with regex
- When to stop splitting tables?
- How do I deal with items that I need to repeat monthly?
- pg_dump format => custom vs sql
- Pick one single row each time from either table?
- Comparing binary 0x and 0x00 turns out to be equal on SQL Server
- Using max for each sub group does not seem to work.
- MySQL table architecture
- mongodb user for ubuntu EC2 instance
- Can't change root password: "The system cannot find the file specified."
- Fulltext stoplist replication
- Breaking Semisynchronous Replication in MySQL 5.5
- sql server database sharding - what to do with common data / non sharded data
- Is database normalization dead?
- Sql Server Management Studio slow opening new windows
| Posted: 30 Aug 2013 04:38 PM PDT I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ? |
| How do you get the graph plugin for Squirrel SQL? Posted: 30 Aug 2013 05:02 PM PDT I installed Squirrel SQL and the website claims the Graph plugin is part of the install. It is not available nor is it in the jar. How do I get the graph plugin? |
| Lookup data while in mysql cli edit mode (external editor) (\e) Posted: 30 Aug 2013 09:07 PM PDT I'm using the mysql cli in edit mode (\e). Goes to vim and I finish typing my command. Only to realize I've forgotten to Another example would be: I forget how a column name is spelled and I need to look it up after I've started typing a command in edit mode. Thanks! |
| How to -create- performance issues? - oracle [on hold] Posted: 30 Aug 2013 01:16 PM PDT I want to dedicate some time to learn more about performance and tuning issues. I assign for that a clean DB. I want to know how can I load into it some data and performance-problem queries/DML/DDL? Do you know some scripts that can cause/generate that (purposely or not)? The idea is to learn the basic(and maybe more) of em/awr/addm etc - tuning and performance wise. Books are great but I have the knowledge I need some actual work on that. |
| Combine - Select Into, with Insert Into Select Posted: 30 Aug 2013 01:12 PM PDT I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. Currently these sql scripts create new tables (assuming they don't exist) using Select Into, and this is causing a dilemma. When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted. I'd prefer not to tell my coworker to rewrite his hundreds of lines of code by specifying all the column names twice in all his scripts. Is there some minimalist way I can combine the idea of the "Select Into" and the "Insert Into" into a single query, without explicitly duplicating all the column names? maybe like "Select Into ... On Error " or something like that? |
| Posted: 30 Aug 2013 02:53 PM PDT I am installing an Oracle 12.1.0.1 Enterprise Edition on RHEL 6.4 I set it up with ASMM and HugePages (64G of RAM) There may be a correlation between the errors and signing in to em express (not 100% certain yet). I mention this because of the following blog post with a similar error: http://surachartopun.com/2013/06/learn-something-wtih-12c-and-enjoy-bug.html I have opened an SR already but wanted to see if anyone had thoughts on this. |
| Execute a script with SQLPlus containing html entity mappings Posted: 30 Aug 2013 11:40 AM PDT When executing a script with SQL PLus, I was prompted to enter value for lt, gt, and apos. Here are some code examples in this script. How to execute this script without being prompted to enter values? Thank you Frank |
| Tools to generate MySQL database migration script on Ubuntu [on hold] Posted: 30 Aug 2013 10:37 AM PDT I am basically looking for tools which can generate database migration script. My team maintains an application written in Java which uses hibernate to map MySQL Database to Java classes. We often change the application and also modify the class definition. Once we build the application it does provide the DB schema. We generally look for schema difference and manually create database migration script. This process is error prone and take some time to get it right. We are looking for tools on Ubuntu which can take the schema files and generate the migration script. Our main focus is schema migration, if tool also provides help in migrating data that would be great.. Any help will be greatly appreciated. Thanks. EDIT Currently I am looking at mysqldiff(libmysql-diff-perl). But I am not able to install this package. It throws the following error. |
| Connecting to Oracle Database with ODBC/Python Posted: 30 Aug 2013 12:25 PM PDT We've recently installed a new piece of software that uses an Oracle Database to store the data. My goal is to connect to that database and pull the data from the back end to do analysis on. I'm having the world's most difficult time connecting to it. I need to access it two ways:
Thanks for looking over this, and I appreciate your help. |
| How to prevent "ibdata files do not match the log sequence number"? Posted: 30 Aug 2013 10:04 AM PDT I am dealing with a very large set of databases that are all innodb. I've enountered this on mysql restart too many times for my comfort:
But I've clearly watched mysql shutdown properly just before the restart when that message happens. Then it "repairs" right up to the original sequence number with nothing lost. What is the best approach to deal with and fix this permanently? Using Percona with Example log: Note how the final log sequence number now matches what it thought was wrong in the first place, so there was 100% recovery? So why is the log sequence not being properly written to ibd? Is it possible shutdown is incomplete somehow? Thank you for any advice. ps. I always wonder if I should be asking this on serverfault or here? Is it okay I asked here? |
| Two Different Sources Taking Transaction Log Backups Posted: 30 Aug 2013 11:10 AM PDT I have inherited a database server with SQL Server 2005. The databases have full recovery mode with hourly transaction log backups. There are two sources taking full backups overnight and hourly transaction log backups: Tivoli Data Protection (TDP) which is part of IBM's Tivoli Storage Manager (TSM) as well as a regular SQL Server Agent job. If a disaster occurred and I needed to recover the database from backup, would I be missing data? I was thinking that if the SQL transaction log backup fires off, it will mark the committed transactions in the log file as ready to be overwritten. After that happens, users will commit more transactions. When TSM/TDP comes in, it will mark the committed transactions in the log as ready to be overwritten. If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data. |
| migration sql 2003 to 2008, Sql side and php side [on hold] Posted: 30 Aug 2013 08:27 AM PDT I have a system in php using MSSQL 2003. I need to change the database to MMSQL 2008. I want to know if there's issue I should look at on
Answer will be usefull even if it is a "dont worry, using standard connection will work" or "there's not so much depricated function between 2003 and 2008". If you goth link to other posts answering some of those interrogation or how to see it by myself. |
| Amazon rds lock wait timeout after restart Posted: 30 Aug 2013 07:07 AM PDT I have bunch of rails application servers which run a particular update query on every pageload on centralised amazon rds mysql (v5.1.63). Recently due to heavy load rds instance got restarted and when they were back we started having "lock wait timeout" error for the update queries. Can anyone please explain what exactly happened and what can I do to mitigate the problem. Update query is like " UPDATE TABLE x where x.y = z;" |
| Check if SQL Agent job is being executed manually Posted: 30 Aug 2013 08:29 AM PDT I have a stored procedure that performs database backups. Because I'm looking at Availability Groups within SQL Server 2012 I want to utilise the *sys.fn_hadr_backup_is_preferred_replica* function within that script to test whether the script should produce the backup. If the script is run manually instead of as part of a scheduled SQL Agent job, or if the SQL Agent job is executed manually rather than via a schedule, then I want the job to fail with an error message to allow the user to see that the backup has not succeeded due to not being on the preferred replica. If the job is run on a schedule then I obviously don't want it to fail as it would be producing errors and sending out alerts all day long on the server that is not the preferred replica. The only bit that I don't know how to do is to check whether the job is being executed by a schedule or manually. Is this possible? Failing that, is there any other way that I can alert a user that the script hasn't produced a backup, whilst not causing the scheduled task to fail and produce an alert? |
| Posted: 30 Aug 2013 12:05 PM PDT Can we use regular expression to select the item from database? The table item is like below Now my problem is that i need to select the code and get the substring like below any suggestion with regular expression or substring? |
| When to stop splitting tables? Posted: 30 Aug 2013 07:30 AM PDT I am creating a league management system. I came up with the diagram below. I am no database expert but I feel it would be better to merge the three following tables: season_league, league_division and division schedule(see second image). This is for mySQL using doctrine with Symfony2 if it matters. What are your opinions? Any suggestions? |
| How do I deal with items that I need to repeat monthly? Posted: 30 Aug 2013 07:20 AM PDT I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every month. How should I store this recurring item? We thought just add in 12 new entries so it atleast covers the year (The users will want to look a few months into the future) but then the problem is that if the cost changes, we have to update all following entries Our other option was to create another table which would store the key of the original with the dates or day of the month that it needs to repeat on? Is there a better solution than these? |
| pg_dump format => custom vs sql Posted: 30 Aug 2013 04:17 PM PDT There are 2 main formats for pg_dump: custom vs sql. For custom, it's compressed by default, and not readable if you try to open it. But is it faster to dump into this format as opposed to SQL format. Or is the speed the same? |
| Pick one single row each time from either table? Posted: 30 Aug 2013 05:16 PM PDT I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on Sample data I say for tblGAlert Same data for tblEAlert. The output I want to merge them together in such a way arrange by the gDateTime and eDateTime. Any idea how to merge them ? I have tried below script but the answer I get is not right and it hogs my db server. |
| Comparing binary 0x and 0x00 turns out to be equal on SQL Server Posted: 30 Aug 2013 01:27 PM PDT It seems that SQL Server considers 0x and 0x00 equal values: This outputs How can I get true binary bit-for-bit comparison behavior? Also, what are the exact rules under which two Also note the following behavior: Background of the question is that I'm trying to deduplicate binary data. I need to Note, that |
| Using max for each sub group does not seem to work. Posted: 30 Aug 2013 07:58 AM PDT I have a table that looks like this in Oracle 11g: I need to get the latest RUN_DATE based on Orga and Pers columns. So that the table looks like this: I tried using this query but it doesn't seem to work: Can anyone point me to the right direction? |
| Posted: 30 Aug 2013 10:17 AM PDT Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; frequently it uses an even tighter time scale and queries are restricted to information gathered in the last 30 days This system has been in existence from 2004 when there were 400 uploads, today there are 2+ million Table structure: Questions Is there a way to partition the table that would make the most sense? Does partitioning even make sense? How do I deal with new data if I do partition? |
| mongodb user for ubuntu EC2 instance Posted: 30 Aug 2013 08:17 AM PDT I am trying to install mongodb on Ubuntu EC2 instance. However, I am confused about what user the DB would run as: If I follow: http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/ Then it says : "mongodb" If I follow : http://docs.mongodb.org/ecosystem/tutorial/install-mongodb-on-amazon-ec2/ It says : "mongod" I think it leads me to inconsistent state. There was a process running is ps output for mongodb but What should be the user of mongodb? |
| Can't change root password: "The system cannot find the file specified." Posted: 30 Aug 2013 09:17 AM PDT I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message:
I'm using MySQL 5.1.70 (x86, 32-bit) on Windows 7 SP1 (64 bits). I've added MySQL's "bin" directory to my "Path" environment variable. In the comments of the MySQL documentation, I read that I should have installed the service using the absolute path, so I stopped MySQL, and uninstalled it: Then I installed it again, using the absolute path this time: I started MySQL, and tried to change the password again: I also tried with quotes: I also tried to change the current directory to MySQL's "bin" directory: What's wrong? |
| Posted: 30 Aug 2013 08:17 PM PDT In MS SQL Server 2008 R2 there is replication of table with fulltext index on it. But stoplist that is associated with replicated fulltext index doesn't replicate. Is there any possibility to replicate stoplist also? |
| Breaking Semisynchronous Replication in MySQL 5.5 Posted: 30 Aug 2013 06:41 AM PDT I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the slave database server. But due to some unknown reasons sometimes, Replication breaks. On running the command: It gives this status: Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave. Please help us in this regard. |
| sql server database sharding - what to do with common data / non sharded data Posted: 30 Aug 2013 07:17 PM PDT We have a very large scale enterprise level database. As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very heavy and continues to grow heavier the larger the company grows. sql proc optimization has been performed and hardware has already been scaled up to a very high level. We are looking to shard the database now to ensure that we can handle company growth and future loads. We have decided what particular data should be sharded. It is a subset of our database which is highly utilized. However, my question is regarding the non sharded data which is common/universal. An example of data like this may be an Inventory table for instance or possibly an Employee table, user table etc . I see two options to handle this common/universal data: 1) design 1 - Place the common/universal data in an external database. All writes will occur here. This data will then be replicated down to each shard allowing each shard to read this data and inner join to this data in t-sql procs. 2) design 2 - Give each shard its own copy of all common/universal data. Let each shard write locally to these tables and utilize sql merge replication to update/sync this data on all other shards. concerns about design #1 1) Transactional issues: If you have a situation in which you must write or update data in a shard and then write/update a common/universal table in 1 stored proc for instance, you will no longer be able to do this easily. The data now exists on seperate sql instances and databases. You may need to involve MS DTS to see if you can wrap these writes into a transaction since they are in a separate database. Performance is a concern here and possible rewrites may be involved for procs that write to sharded and common data. 2)a loss of referential integrity. Not possible to do cross database referential integrity. 3) Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards. 4). increased database trips. Like #1 above, when you run into a situation in which you must update sharded data and common data you are going to make multiple round trips to accomplish this since the data is now in separate databases. Some network latency here but I am not worried about this issue as much as the above 3. concerns about design #2 In design #2 each shard gets its own instance of all common/universal data. This means that all code that joins to or updates common data continues to work/run just like it does today. There is very little recoding/rewriting needed from the development team. However, this design completely depends on merge replication to keep data in sync across all shards. the dbas are highly skilled and are very concerned that merge replication may not be able to handle this and should merge replication fail, that recovery from this failure is not great and could impact us very negatively. I am curious to know if anyone has gone with design option #2. I am also curious to know if i am overlooking a 3rd or 4th design option that I do not see. thank you in advance. |
| Is database normalization dead? Posted: 30 Aug 2013 06:26 AM PDT I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't. With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant. (Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example) |
| Sql Server Management Studio slow opening new windows Posted: 30 Aug 2013 10:51 AM PDT After upgrading both sql server and management studio from 2008 to 2012, the management studio 2012 is very slow when new query windows and dialog boxes are opened. Even right click on tables is slow. There is typically at least a 5 second delay before I can start doing anything within a window. This happens every time, even if I open the same window twice in a row. What happens and how could I fix this? I tried a hit on google where I'd need to modify my hosts file, but that didn't help. |
| You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment