[how to] Repairing Broken Binary Replication in PostgreSQL 9.0 |
- Repairing Broken Binary Replication in PostgreSQL 9.0
- Two nullable columns one required to have value
- Executing local scripts against remote server?
- How quickly repiar mongo database 1TB on production
- What's the best database for analysis/research applications, i.e. involving complex queries but few transactions?
- Failed copy job deletes all users
- Is there any way to connect to one server and utilize a linked server using Windows authentication and NTLM?
- Addtional configuration when deploying Data Tier Applications?
- Is a bad practice to create a transaction always?
- Restoring database backup file gives access error
- SQL Server: subscriber as publisher and hierarchical replication
- How to select nodes where all children is satisfied?
- Configuring PostgreSQL for read performance
- What does the size of a SQL table depend on?
- How to migrate SQL Server to MySQL
- Unique Non Clustered Column in Partitioned table
- Pattern similarity in time-series [closed]
- Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?
- Database setup/design for multiple services
- How to connect multiple users/schemas in Oracle 11g?
- mysql join by 2 ways - how it works? - which gives performance difference
- How to search a MySQL database with encrypted fields
- How to load more than one MS-Access-db tables with data in SQL-Server-DB
- mysql 5.5 strange bug
- how to send mail from gmail smtp server by using the ssis
- how to get unique records
- How to get server event notification
- Examples of SQL transaction procedures for sales tracking or a financial database
| Repairing Broken Binary Replication in PostgreSQL 9.0 Posted: 28 May 2013 08:25 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. |
| Two nullable columns one required to have value Posted: 28 May 2013 07:20 PM PDT No-Explanation Question:Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have a value Problem Description:Let's say I have table called Expense and have 2 dates : prevision_expense_expiration_date DATE NULLABLE expense_payment_date DATE NULLABLE the logic of those 2 columns is the following: I made a purchase of something and I know I have to pay for it, some date, like a phone bill. I will enter this as an expense with a expense_payment_date. This date is the supposed date I should pay but not the actual date of the payment, like the expiration date of the invoice. In other situation I sell a gift card of some provider for it's service. I may have the expense of buying to my provider the service transfered to my client only if the client redeem the card. Hence the gift card has an expiration date, I want to do a prevision for that 'expense' without inserting as an expense for the time the gift card is valid, if the gift card expires, that 'expense' should not enter into the account system. I know I can have 2 equally tables called prevision_expense and confirmed_expense but doesn't sounds right so I have in the same table, 2 dates, nullable, but I want to constrain or something so that one is always required. There's another posible strategy: payment_date DATE NOT NULL is_prevision_date BOOL NOT NULL So, in this case, if the date is prevision bool value would be 1, otherwise will be 0. No null values, all is good. except that I want the option to store BOTH values when first I have a prevision date and THEN (lets says two days later) have a confirmed date for that expense, in which case with strategy 2 i won't have that option. Am I doing everything wrong in the database design ? :D |
| Executing local scripts against remote server? Posted: 28 May 2013 05:30 PM PDT I am in a situation where I would like to execute a number of sql scripts that are on my local machine against a remote MySQL instance. I found this question which seems to point me in the right direction. After reading this I tried the following: But it's been throwing an "Access denied..." error for but this only returns the same error for user, Is there any way for me to specify that I want to connect at root on the destination server in my above example? |
| How quickly repiar mongo database 1TB on production Posted: 28 May 2013 08:50 PM PDT help me, production server is down every 15 min, i don't know how can fix it without lock |
| Posted: 28 May 2013 03:42 PM PDT I'd like to start a discussion on what people think what the best database environment (MySQL, MS SQL, PostgreSQL, ...) is for data analysis and research applications like encountered in empirical academic work or business analysis? Such applications are the opposite of web applications, as they involve very few transactions (like only 1 or 2 people querying the data at the same time) but complex queries that can (INNER) JOIN 20-50 tables, including derived tables. It can be assumed that the queries are optimized in the sense that tables have the right primary keys. Still, from my experience with MySQL 5.5 and 5.6 running on an Intel i7 Quad core machine with 16 GB of RAM and the data being stored on an Intel SSD, the most complex queries can take several minutes up to 20 or 30 minutes. My .ini file was optimized using Percona tools, i.e., that buffers are increased to 8 or 10 GBs, etc. (I post it below.) Even though MySQL supports multi-core processors, one transaction seems to be only computed by one core, which is why increasing the number of cores does not have any benefit. I wonder if other (preferably Windows-based) databases provide better performance in such a setting? Can I expect an improvement by moving to MS SQL, that is known to be a more full-fledged database of the stature of Oracle? I post my MySQL 5.6 my.ini file for completeness: (I override some Percona recommendations as indicated by #'s) |
| Failed copy job deletes all users Posted: 28 May 2013 08:25 PM PDT So, 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 CREATE QUERY action for a particular view, because the datasource for the view did not exist. This is interesting in its own right, as the source most certainly exists, and the view(s) in question are fully functional in the source database. I'm not really clear, just yet, on how significant this is, as I've yet to figure out precisely why this generated an error. 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. |
| Posted: 28 May 2013 08:30 PM PDT I currently have MSSQLS 2005 (Server A) that has a SSAS 2005 linked server (Server B). The powers that be do not want to enable Kerberos authentication, so I'm stuck with NTLM. The problem is that when developers connect from their local workstation to Server A and try to run queries against the linked server, Server B, they are not able to. They are only able to if they RDP into Server A and then run queries against the linked server, Server B. My understanding is that this is due to constraints of NTLM. Is there any way at all I can enable the developers to connect to Server A on their local workstation and be able to run queries against the linked server? To clarify, the user has access to both the SQL Server instance containing the linked server, as well as the SSAS catalog that the linked server points to. |
| Addtional configuration when deploying Data Tier Applications? Posted: 28 May 2013 02:20 PM PDT I'm starting a few new database projects and I'm attempting to create them at Data Tier Applications. There are two items I'm not able to find documentation for. I would like to set the db owner to SA and set the initial filesize and growth rate. Even if those items are outside the scope of the app, I would expect that there would some way to specify that at publish time, either in SSDT or SSMS. I can find no documentation either way. Is this the case? |
| Is a bad practice to create a transaction always? Posted: 28 May 2013 04:32 PM PDT Is a bad practice to create a transaction always? I mean is a good practice to create a transaction only for one simple select. how much is the cost of creating a transaction when is not really necessary? Even if you are using an isolation level read_uncomitted. Is a bad practice? because it shouldn't have problems with locking. |
| Restoring database backup file gives access error Posted: 28 May 2013 07:35 PM PDT I am running SQL server 2008 and trying to restore from a backup file. Note: the backup file and folder and read/write access to "everyone". Running the command as "master" Running Gives the following error: Thanks, Bruce |
| SQL Server: subscriber as publisher and hierarchical replication Posted: 28 May 2013 01:53 PM PDT In Oracle Streams one way replication from one DB to another is a basic block for many replication topologies (N-way, hierarchical, combined and so on), and changes could be captured and applied for the same table at the same time. But I can't find anywhere in SQL Server documentation whether table (article) could be a source (publisher) and destination (subscriber) simultaneously. Is it possible, for example, to setup bidirectional replication using two pairs of publisher->subscriber transactional replication? Naturally I am more interested in multi-level hierarchy: for example one table is replicated from root node through intermediate nodes to leaves, second one - from leaves to root, and third one - bidirectionally, and for all tables intermediate nodes could perform DMLs to be replicated too. With Oracle Streams it is easy to achieve, and even more sophisticated configurations are possible, but are they with SQL Server? UPDATE: It seems it is with use of merge replication and republishing (http://msdn.microsoft.com/en-us/library/ms152553.aspx), but what about transactional replication? |
| How to select nodes where all children is satisfied? Posted: 28 May 2013 02:46 PM PDT I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. The relation between the nodes in the tree structure is represented by table A: Table A:
Table B represents the nodes in the tree: Table B:
The state of table B represents the states true = on and false = off. Question: This is probably very simple, but I can't seem to get my head around it. |
| Configuring PostgreSQL for read performance Posted: 28 May 2013 04:31 PM PDT Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is similar for all our tables: timestamp(Timestamp) ; index(smallint) ; key(integer) A table can have millions of row, even billion of rows, and a read request is usually for a specific period (timestamp / index) and tag. It's common to have a query that return around 200k lines. Currently, we can read about 15k lines per second but we need to be 10 times faster. Is this possible and if so, how? Note: PostgreSQL is packaged with our software, so the hardware is different from one client to another. [Edit] Added details below, performance was better for this test because I don't have access to the real setup right now. I will update as soon as I can access the setup. [Edit2] Applied "dezso" suggestions, see configuration changes below and the specs of the server used for testing. Yes it's a VM used for testing, the VMs host is a Server 2008 R2 x64 with 24.0 GB of ram. Server Spec (Virtual Machine VMWare)postgresql.conf optimisationsTable DefinitionQueryThe query take about 30 seconds to execute in pgAdmin3, but we would like to have the same result under 5 seconds if possible. Explain (Edit2: Updated)In my latest test, It took 7 minutes to select my data!!! See below Explain (Edit3)Thanks a lot for help!! |
| What does the size of a SQL table depend on? Posted: 28 May 2013 05:35 PM PDT I mean SQL here can be any SQL like database such as SQL server, My SQL, SQLite, even MS Access. I want to know what the size of a table depends on, it depends on the actual rows in the table with a fixedly designed structure or it also depends on the content of cells in the table. For example: I have a table which has the fixedly designed structure like this: And here is the table with 1 row: and here is also a table with 1 row: If the size depends on the number of rows, the 2 tables above would have the same size, if it also depends on the cells' content, the second table would have the larger size. I would like to know which is larger? I care about this because, in some case, I really want to maximize the maximum number of characters for a field (8000 in SQL server), to make user free from inputing almost anything s/he wants, but I'm afraid of making my database file too large (unnecessarily, costly). Your help would be highly appreciated! |
| How to migrate SQL Server to MySQL Posted: 28 May 2013 05:13 PM PDT I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried:
Anyone have suggestions? |
| Unique Non Clustered Column in Partitioned table Posted: 28 May 2013 12:54 PM PDT We have partitioned one of our SQL Server tables on a Clustered Index The problem is most of our queries use the XYZ column to retrieve data as XYZ is guid and unique. At this point we are not sure what will be the impact of setting XYZ column ( |
| Pattern similarity in time-series [closed] Posted: 28 May 2013 12:14 PM PDT I have a PostgreSQL database and my data-set include time-series of temperature for two stations. I would like to look their pattern's similarity in similar time sequences. For station number 1: For station number 2: I would like to ignore true values and compare time-series with respect to the pattern. Moreover, 1 time slice by 1 time slice comparison is enough for my work. Please help me on discrete Fourier transformation or slope to compare the patterns. |
| Can I use a foreign key index as a shortcut to getting a row count in an INNODB table? Posted: 28 May 2013 03:13 PM PDT I have a table that has a large number of rows in it. The primary key (an auto-incrementing integer) is, by default, indexed. While waiting for a row count to be returned I did an Since the primary key is guaranteed to be unique, can I just take the number of rows from the BTW, I believe the difference in numbers is due to the fact that more data is continually being added to this table. |
| Database setup/design for multiple services Posted: 28 May 2013 02:13 PM PDT I am working on a new MySQL database that will store orders/customer information for around 15 different services. 7 of the services are similar in regards to the information that is stored with a few of the 7 services requiring an additional piece of information. There other 7-8 services are similar to each other as well but not as similar the the first 7. So my question is how should I break this down into a MySQL database? Should each service have its own table? If this is true, what is the best way to link tables? Any advice is greatly appreciated! Also, here is a sample of the type of info that will be stored: |
| How to connect multiple users/schemas in Oracle 11g? Posted: 28 May 2013 12:54 PM PDT I'm almost new user to databases, so I think I'm doing something wrong.
I'm learning that the schemas in I want to achieve that in Oracle, so I created the users/schemas; after that I got connected with Now, when I'm trying to create the alter relating ALTER I'm trying to do: I created those users because if I wanna see all the tables related to the Phone(user1), I got connected to that user and that should be all, I just wanna have the DB with some order. Admin privileges: User privileges: |
| mysql join by 2 ways - how it works? - which gives performance difference Posted: 28 May 2013 12:54 PM PDT I had 2 different ways to query the content which showed performance difference when executed. The 1st way is and the output of this query returned: The other way is The output of this looks like this: Why is there such a difference in the query execution plan between the 2 and number of rows being fetched in each step of query execution is more in the 1st approach? Please explain it. |
| How to search a MySQL database with encrypted fields Posted: 28 May 2013 12:43 PM PDT Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt. How would one search those fields anyway? Decrypting each record step by step is no option: Suppose I have multiple of thousands of records. It would take too much time and space to decrypt each record and check if each single record matches the search. UPDATE 2012-09-07 Adding further details to the database schema would be OK, since I'm about to implement a new application. Furthermore, I need to extend applications currently running in production. But even for those application, adding further details would be OK. UPDATE 2012-09-08 Encryption is the kernel of this question. Access restrictions, as proposed by some answers, already apply - but do not fit the formal requirement to encrypt data. This formal requirement is not Payment Card Industry Data Security Standard [PCI]. |
| How to load more than one MS-Access-db tables with data in SQL-Server-DB Posted: 28 May 2013 12:36 PM PDT I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service. One of my computer folders contains more than one MS-Access db, want to load all of this access tables with data in SQL-Server db. To load all tables I want to use ssis. Which control of ssis will help me to load all access tables on my SQL-Server db? I want to create (SSIS) package for importing an MS Access table into SQL Server, then running some stored procedures on this database and finally exporting the result to Excel. I am comfortable in writing queries and working in Management Studio but I don't know how to actually design an SSIS package. Need helping hand to do this? If have any query please ask. |
| Posted: 28 May 2013 03:16 PM PDT I am trying to create a database in MySQL Server 5.5. The database is created, I know this as when I do It shows me my database in the list of all databases from the commandline. But when i go inside
my database is not visible there. In fact with it shows me 6 databases in the list but inside
shows only two databases. Its really strange error I m experiencing, the database is created but not actually created feels like the commandline is cheating with me! thanks for the help in advance! |
| how to send mail from gmail smtp server by using the ssis Posted: 28 May 2013 01:34 PM PDT I'm just getting into sql-server SSIS, so forgive me if this is a basic question. Use the SSISwant to send mail from gmail smtp server. Using the above information I want to send mail from SSIS. How do I use SSIS to do this? If have any questions please ask. |
| Posted: 28 May 2013 12:38 PM PDT Table structure Table values Required output My table has two primary keys, I want to get the unique record from this table. The required output contain column If have any query please ask. |
| How to get server event notification Posted: 28 May 2013 12:37 PM PDT I'm just getting into sql-server, so forgive me if this is a basic question. I'm building an application using .NET 4.0 and VS 2010 and ms-server2008. For my application I need to detect the server response, suppose: in db-server I need to detect which table insert/update/delete records, and which is the affected record is? Is there any mechanism or technique to detect the server response. If have any questions please ask. |
| Examples of SQL transaction procedures for sales tracking or a financial database Posted: 28 May 2013 12:54 PM PDT I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions:
Anybody have some good similar or related sql examples I can look at to design these? Do people use transactions for sales databases? Or if you have done this kind of sql transaction before could you make an outline for how these could be made? My (closed as not a real question) thread so far on stack overflow: Need example SQL transaction procedures for sales tracking or financial database Latest update, user will send new inputs/ changes /and cancellations from a c# application. Application data:
C# App will package that data into XML format and then execute some SQL stored procedures with transactions holding together the xml to table conversions into the SQL Tables designed with the same parent/child node structure using something like what is described by the answers to this related question on Stack Overflow: http://stackoverflow.com/q/2756773/613799 I wish there was a book on designing multi-user sales databases, and the stored procedure transactions that will be used by the related user apps from scratch app->xml->database. Please let me know if you know of a good one, or a chapter of a book. |
| 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