[how to] Database design for user information in mySQL |
- Database design for user information in mySQL
- How can I drop a stored procedure whose name is in a variable?
- Dual column Primary Key [on hold]
- Max_packet_allowed setting for Windows 7
- Why is my Amazon RDS instance so slow?
- Running out of Transaction Log space during Alter Table
- When MySQL table gets too large
- Adding an index to a system catalog in Postgres
- Replication error
- What's a good way to model user authorization to hierarchical data?
- Need advice regarding RMAN's working
- Oracle shared memory exception ORA-04031
- Memcached plugin on MariaDB?
- How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure
- Oracle database link via ODBC to SQL Server does not recover automatically
- Repairing Broken Binary Replication in PostgreSQL 9.0
- Failed copy job deletes all users
- Validate each parent intermediary is also a parent to itself
- Azure SQL Administrator Can't Access Master Database
- SQL Server 2005 Replication
- Constructing an SQL query with time intervals
- Multiple parents and multiple children in product categories
- Mysqldump tables excluding some fields
- Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error
- Why do we need to rebuild and reorganize Indexes in SQL Server
- Mongo connection failing with 'Transport endpoint is not connected'
- Copy Database option not found in SQL Server Management Studio 2008 R2
- First time running a replication on SQL Server 2008. "The process could not connect to subscriber 'SERVER2'"
- How to the get current date without the time part
| Database design for user information in mySQL Posted: 28 Jul 2013 09:00 PM PDT I'm trying to design an app where users take a new survey everyday (that's not the main part of app but it's an important part). I've never designed a database before so what I'm trying to do might not make any sense. I'm using MySQL and I have a table for user information (user_id, name, password, etc). What I plan on doing is making a new table for each new survey and linking it with the user_id. So basically, I would have one table having a relationship with hundreds of other tables. Is that how it would work? I thought about adding a column to the user table for every survey but I read that it's bad design to add columns often. |
| How can I drop a stored procedure whose name is in a variable? Posted: 28 Jul 2013 07:17 PM PDT Given a variable that contains a stored procedure name: How can I drop the stored procedure? |
| Dual column Primary Key [on hold] Posted: 28 Jul 2013 02:41 PM PDT Order ID Customer ID Inventory ID Number ordered Number ordered would depend on Order ID and Inventory ID right? Because an order ID could contain several Inventory IDs. So would Order ID and Inventory ID need to be my primary Key, and if so, would they need to be next to each other? Also is this in 3nf? At first I was thinking that order ID would be my primary key and customer id and inventory id would be foreign keys. Can Inventory ID be part of the primary key and a foreign key? Thanks in advance for any help! |
| Max_packet_allowed setting for Windows 7 Posted: 28 Jul 2013 06:33 PM PDT We have windows xp machine with older version of mysql. So when we need to import huge data we just set the max_allowed_packet=1500M in the C:\Program Files\MySQL\MySQL Server 5.1 . Now we loaded the latest mysql 5.6.12. We are trying to import the same date and we find there are few my.cnf file on is in the program file and another in programme data both change yet we cant import the same data. Where else to change even after the change we started the service by going into the admistrative tool. |
| Why is my Amazon RDS instance so slow? Posted: 28 Jul 2013 08:41 AM PDT I currently run a Windows Server with SQL Server Express. I'd like follow best practice and separate the application from the database, so I've created an RDS instance and exported my database. But... it's painfully slow. I'm running a test query from my local machine that selects 12000 rows - it takes less than 1 second against my original setup but 16 seconds against RDS. Surely it can't be question of specification; my small RDS instance actually has a better spec. than my Windows Server. What could be going on here? |
| Running out of Transaction Log space during Alter Table Posted: 28 Jul 2013 01:10 PM PDT I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that I'm guessing that it's not possible to break this down into modifying fewer rows at a time, so I'm wondering what my options are for making this change to the database. SQL Azure will not let me change the size of the transaction log (limited to 1gb). I'm guessing that my best bet is going to be creating a new table with the new layout, migrating the data across into that one, deleting the original table and then renaming the new table to match the name of the old table. If that's the case, how is it best to structure those commands? Scheduled downtime for our system is not an issue currently so this operation can take as long as it needs to. |
| When MySQL table gets too large Posted: 28 Jul 2013 12:07 PM PDT I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the table takes a really long time (more than 48h) and every minute downtime is one minute too much. So whenever I need to add a new index or add a new column, the problem with the altering is there. Are 12 billion rows too much for MySQL to handle? What would you suggest to do? |
| Adding an index to a system catalog in Postgres Posted: 28 Jul 2013 08:47 PM PDT I'm having a situation very similar to the one described here:
and, while the application itself appears to be working fine, some queries involving system catalogs are very slow. Also, In particular, I need to calculate the on-disk size of each schema using something like this: which is quite slow. Looking at the query plan, I see Which, if I understand it right, tells that 90% of the query time is spent sequentially scanning I stopped postmaster, ran the backend in single-user mode and added the following indexes: (I've also got thousands of tablespaces too). Now the query is ~100 times faster and the plan looks much nicer: However, in the above thread, Tom Lane, who is one of Postgres core contributors, says:
I'm also worried by the fact that modification of system catalogs seemed to be completely disabled in Postgres 9.0 and 9.1 (I'm using 9.2) - I suppose it was done for a reason? So, the question is: what are the gotchas in adding an index to a system catalog in Postgres and will I be nuts if I (eventually) do that on a production system? |
| Posted: 28 Jul 2013 09:03 AM PDT We have a slave server that has stopped replication due to the following error: What could be the cause of this error? And what would be a way to fix it? Version of both master and slave is MySQL 5.5.30 What else I can't figure out is how a temporary table would lead to such an error (in master): The last lines from master error log: Additional info:
|
| What's a good way to model user authorization to hierarchical data? Posted: 28 Jul 2013 07:44 PM PDT I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012). We're working with farm production information. We will have users who need to be authorized to view production records, but only at certain vertical levels in a hierarchy. Our data hierarchy looks like this: The idea is that some users will have access at the System level, and can see records for all Farms, Groups, and Animals within that System. Likewise, some users will have permission starting at the Farm level, and need access only linked to that Farm and all Groups (and animals) within it. Each table contains a primary key column, and a foreign key column linking it to the parent record (along with whatever other attributes each entity requires). What I've implemented in the past is two-table system for linking users to the appropriate items they're allowed to see. Implemented here, it would look like this: The application inserts a record into Authorizations, with the user to authorize, the record id (System id, Farm id, etc), and the type of record (System, Farm, etc). The FullAuthorizations table is used to denormalize the farm hierarchy for easier/faster filtering of data. A trigger is used on the Authorizations table (and each of the farm, etc, tables) to update FullAuthorizations. I considered using a View here, in a previous project with additional levels of entities, and the performance was quite poor once we began getting several hundred thousand records. The queries would look something like: In the other project where we're doing this, the solution is performant, but feels like a bit of a hack, and I especially don't like that we can't maintain referential integrity on Authorizations with the associated objects. I'd appreciate feedback on some other possible solutions. I've been looking at things like Nested Sets, but not sure something like that fits this particular problem. |
| Need advice regarding RMAN's working Posted: 28 Jul 2013 05:43 AM PDT I have configured a RMAN script which first takes the backup of the database and then deletes the obsolete backupsets according to the present retention policy.Now is there any possibility of the given scenario Scenario: The backup process starts and failed abruptly dues to some reason creating incomplete backupset.Then the backup deletion part of script runs and deletes the latest full/working backup(not the one that failed but the one which the script created on the previous run)of the database provided that the retention policy is redundancy 1.Hence I lose the only available/working backup of my database. Can it happen? |
| Oracle shared memory exception ORA-04031 Posted: 28 Jul 2013 01:43 PM PDT I'm trying to establish an Oracle database connection. It throws the following exception: I have tried connecting the database through JDBC as well as SQL Developer, however it throws the exception in both case. How can I fix this? |
| Posted: 28 Jul 2013 02:43 PM PDT I'd like to try new NoSQL feature in MySQL 5.6 but I am using MariaDB 10.0.2 on Debian 6 and don't fancy coming back to MySQL. I'm wondering whether the memcached plugin has been added to MariaDB? And if not whether one can still use it as an addon? And if not, can I use the existing Cassandra plugin of MariaDB to the same effect? |
| How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure Posted: 28 Jul 2013 03:43 PM PDT I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package. I am using SQLDeveloper 3.0 with an Oracle 11g database. I have already developed a stored procedure 'acctg_detail_proc()' that generates a detailed list of accounting transactions within a specified period. What I am trying to do is create a summary report procedure 'acctg_summary_proc()' using the detailed data returned by acctg_detail_proc(). Since I am new to PL/SQL, all I have learned so far is how to retrieve the detailed data via a ref cursor, then LOOP through it, FETCHing each individual detail row. I want to figure out how acctg_summary_proc() can substitute that call to acctg_detail_proc() for a table name in a SELECT statement with a GROUP-BY clause. Here is the source code for an anonymous block where I tried to test it: When I try to execute this code, I get the following error: PL/SQL: ORA-00942: table or view does not exist I realize I could use a nested SELECT statement instead of a table name, but I want to avoid duplication of source code. Is there any way to 'alias' a ref cursor so I can reference its data in a SELECT statement? Here is some further background info: The called sub-procedure has ~600 lines of code, and selects 40 columns of data from a de-normalized VIEW. The corporate DBAs will not let me create any VIEWs that contain WHERE clauses, so that is not an option for me. Thanks in advance, Ken L. |
| Oracle database link via ODBC to SQL Server does not recover automatically Posted: 28 Jul 2013 08:43 AM PDT I have a test environment with an Oracle 10XE database which is linked to an SQL-Server 2005 via an ODBC data source. For reference here I named the connection As a test of error conditions in production we unplugged the network cable. This broke the connection. A subsequent executed statement (e.g. This was somewhat expected. Unfortunately plugging the cable back into the machine does not lead to an automatic reconnect of the link. The problem persists until I drop the link and create it anew with the commands Also the same happens if I just disconnect the cable for a short time and reconnect it before I execute any statements over the link. The interim breakup is still detected by the ODBC driver but not fixed on its own. As mentioned by Ste, creating a link will tell Oracle only about the connection parameters like host, user name, password, but not create a connection by itself. The link can be created without the network cable connected to the Oracle machine. If I create the link without the cable connected, then connect it to and execute a simple select on the linked server, the same Oracle error occurs. Obviously in production mode this is an unacceptable situation which needs to be fixed, preferably by an automatic procedure. Three questions do I have about this:
|
| Repairing Broken Binary Replication in PostgreSQL 9.0 Posted: 28 Jul 2013 12:43 PM PDT I have a binary replication that was stopped for so long that the WALs were removed and as a result, it ended up being old. I'm trying to reestablish the replication and the best way I've found so far is following the steps on the PostgreSQL wiki:
But the database is too big (300 GB), my connection is not really amazing (like 800 kB/s) and the files in base keep changing. So I was wondering if there's a more optimal way to do this. |
| Failed copy job deletes all users Posted: 28 Jul 2013 05:43 PM PDT Since the progression of this was apparently somewhat difficult to follow the first time around: I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy database wizard with the detach/reattach method. The copy failed. The log indicates that it was unable to execute a This resulted in the deletion of all non-system user associations from the source database, leaving me with users dbo, information_schema, sys, and guest. Non-system roles were also deleted. Schemas were unaffected. I have since restored the damaged database from backup. Academically, however, I would like to know the following:
This is entirely repeatable. I've made a handful of copies (manually) for the sake of experimenting with this particular issue, and in each case, the failed copy job obliterates the users and roles from the source database. Removing the views that generated errors allows the copy to complete, and, as one would expect, produces a copy with identical data, users, etc., in addition to leaving the source database unaltered. If it's important, I've tried rebuilding the indexes of the system databases, as well as the damaged database, to no appreciable effect. The error generated: |
| Validate each parent intermediary is also a parent to itself Posted: 28 Jul 2013 07:43 AM PDT I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any. I need to do is first of all get all parents records. This should be done by joining I hope the requirement is clear. Can you give me query for this? Create the test table Insert the test data into the test table I have tried with this query: |
| Azure SQL Administrator Can't Access Master Database Posted: 28 Jul 2013 09:43 AM PDT I created an SQL server and database. I can log in to the database manage page with my administrator account, and manage the database I created, but when I try to look at the master database, it says: Failed to create context. Details: The current user does not have VIEW DATABASE STATE permissions on this database. What am I doing wrong? I couldn't find anything else like this on the internet. I log in with the account that the sql server page on the azure managing portal says is the "Administrator Login". |
| Posted: 28 Jul 2013 08:44 PM PDT I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected by vpn SQL Server 2005 domain1.local server 2 windows 2003 server 192.168.10.6 connected by vpn SQL Server 2005 domain2.local When I setup up Replication everything looked fine until I looked at the sync status and it said: From user sa refused because the job is already running from a request by user sa changed database context to technical error 22022. I have cleared jobs in the server agent as well as restarted the service. Could this be something to do with authentication between two non trusted domains as I can browse and even control each sql server via SQL studio but just not setup replication? Yes I can manage each SQL Server in SSMS and we are using merge with snapshot. |
| Constructing an SQL query with time intervals Posted: 28 Jul 2013 06:43 AM PDT I've a simple but large table in an SQL database which has four fields (id(int PK), time (unix timestamp, not null), value (double, not null) and an ID_fk (integer foreign key). Data is recorded every minute, but at a seemingly random second for subsets of sensors, each sensor has a foreign key value though. My question is this, I need to write a query that will tell me when, over all two minute intervals in the dataset, a series of sensors have a value greater than 0.9. I was thinking for trying to create a temporary table and do it that way, simplifying the time by only storing it at minute resolution? Any advice would be greatly received, Thanks, James |
| Multiple parents and multiple children in product categories Posted: 28 Jul 2013 11:43 AM PDT I am making a ecommerce site. In this site I want to categorise the items into three different layers In the above table, I want to display the sub category after the customer selects the primary one. At the same time the 'samsung' also comes under 'camera'. Like this a parent can have any number of children and one child can have many parents. In the future we may change the primary and secondary. What is the best solution for this? Which model will adopt our category: tree or nested? |
| Mysqldump tables excluding some fields Posted: 28 Jul 2013 06:43 PM PDT Is there a way to mysqldump a table without some fields? Let me explain: How can I do this? |
| Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error Posted: 28 Jul 2013 10:43 AM PDT we are using a Sybase SQL Anywhere 12 db. In the db there are indices, which are unique, but shouldn't be unique. Therefore I search for a quick way to list all tables with unique primary keys. I tried The result was an error message: Anweisung konnte nicht ausgeführt werden. Tabellenname 'sysindexes' ist mehrdeutig SQLCODE=-852, ODBC 3-Status="42S13" Zeile 1, Spalte 1 Roughly translated: sysindex is ambiguous. I found on internet the query: Which looked what i wanted. But there was still the same result of ambigious sysindexes. What dows ambigious indexes mean in this context? Will/Can this cause any error in future? As workaround I used sybase central (which by the way opens always on first monitor, not on the one where it was closed - ugly behaviour), and found that a item indices showed what i searched for. But I still want to know how a programmatically solution looks like. |
| Why do we need to rebuild and reorganize Indexes in SQL Server Posted: 28 Jul 2013 07:41 PM PDT After searching the internet i couldn't find the reason for
An article on a site says :
I couldn't understand this, though it says that |
| Mongo connection failing with 'Transport endpoint is not connected' Posted: 28 Jul 2013 04:43 PM PDT I have a 2 server installation. A web server with Apache and a DB server with MongoDB. I am load testing it, and on ~300 RPS I am getting this error:
The only thing I am noticing is that right before the fail, I am getting a lots of connections on Mongo: Almost 500 connections here... but never more than that! Mongo is 2.0.3. PHP is 5.3.x (latest of Debian install...) Help! |
| Copy Database option not found in SQL Server Management Studio 2008 R2 Posted: 28 Jul 2013 08:58 AM PDT I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2. Can anyone tell me whats going on please? |
| Posted: 28 Jul 2013 05:53 PM PDT I've set up Replication between two remote servers, Using Peer to Peer & Transactional updates. The subscription is all set, but the actual replication doesn't take place. When viewing the Synchronization Status of the subscription, i get an error, saying "The process could not connect to Subscriber 'SERVER2\DBINSTANCE'." Clicking on start, this is the error message I get: TITLE: View Synchronization StatusThe agent could not be started. For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=CantStartAgent&LinkId=20476 ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent Error: Request to run job SERVER\DBINSTANCE-DBNAME-DBPUBLICATION-SERVER2\DBNAME-3 (from User sa) refused because the job is already running from a request by Start Sequence 0. Changed database context to 'DBNAME'. (Microsoft SQL Server, Error: 22022) For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476 Viewing the Agent History, trying to get more specific into the error, i get the following Error: Error messages: The process could not execute 'sp_replcmds' on 'SERVER\DBNAME'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: help/MSSQL_REPL20011 Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517) Get help: help/15517 The process could not execute 'sp_replcmds' on 'SERVER\DBNAME'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: help/MSSQL_REPL22037 I have looked each and every one of the errors online, went through all this kind of explanations and changes, and nothing worked. Many thanks to anyone who can pour some light on the issue. |
| How to the get current date without the time part Posted: 28 Jul 2013 06:40 PM PDT In SQL Server 2005 how do I get the current date without the time part? I have been using |
| 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