texts: Frequent Delete & Insert, impact on performance

texts:
In my application, we have very few updates. Most of the data gets deleted and inserted.

My question is, how this will impact the performance?

We are using SQL Server 2012.

Table statistics (will these get outdated faster?)
Fragmentation

Please post your experiences.

Thanks in advance!!


texts: WAL files not being deleted and pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive

texts:
My database has stopped deleting WAL files and pg_xlog grows to eventually take all my disk space.  I have to use pg_controldata and pg_archivecleanup to delete them manually.

I'm almost certain it's to do with my tinkering with replication, but I thought I removed it, and the only remnant of that I can see is the replication slot, but when I try to remove it: pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive.  Which I find strange because I'm trying to remove it, not use it.

$ barman list-server shows nothing

SHOW wal_level minimal

SHOW archive_command (disabled)

SELECT * FROM pg_replication_slots
barman,,physical,,,false,,,,23/DF000000

SHOW server_version 9.5.14

My postgresql.conf is absent of all WAL settings except max_replication_slots = 1, which I tried to set to 0, but the database will not start if I do.

How can I remove my replication slot, or otherwise troubleshoot postgres not deleting its WAL files?


texts: mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

texts:
I am troubleshooting a backup script (bash), and I have limited working knowledge of the current configuration since the last 3 predecessors did not leave any documentation.
I do know that my server is running MySQL Server 5.5.37 on a Debian machine. I'm manually testing the script and found mysqldump to be the problem. I realize there are very many posts on this same error message (1, 2, 3, 4), but none of them have worked for me.
Running this command
mysqldump -uroot -p******** --all-databases > backup.all.sql 2> backup.all.err

produces the following error:
Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

Here's a summary of what I've tried.

Verifying that root user can log in, and is being logged in correctly (ie. USER(), CURRENT_USER();).

I can log in to mysqlwith root user and supplying the -p flag. Yet if I supply the password as part of the cmd (ie. mysql -uroot -p********) I receive the same error as above.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Verified that every user has a password, there are no anonymous users, and root is in the users table with the normal hostnames (list below, shortened).
+-------------+--------------+
| user        | host         |
+-------------+--------------+
| root        | 127.0.0.1    |
| root        | localhost    |
+-------------+--------------+

I can run mysqldump -p --all-databases with limited success but will not work for my use case because I'd like the script to run automatically without the need for user interaction (eg. supplying the password before proceeding with the backup process).

Root user has all privileges on all dbs.
+-------------------------------------------------------------------+
| Grants for root@localhost                                         |
+-------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY   |
| PASSWORD '*********' WITH GRANT OPTION                            |
+-------------------------------------------------------------------+

Values in /etc/mysql/my.conf all seem to be pretty standard and are probably default. There are no additional .conf files.

Tried forcing a connection to localhost (which also forces use of the TCP protocol)
mysql -h localhost -uroot -p********

Reviewed and tested each of the Causes of Access-Denied Errors from the MySQL 5.5 docs.

Reset root password (not OS root user) using dpkg-reconfigure mysql-server-5.5

Flushed privileges, just in case, using mysqladmin flush-privileges

Ran mysqlcheck --all-databases in case mysql was corrupted. No errors were found.

Is there any reason why 'root'@'localhost' would still be denied when supplying the password for both mysql and mysqldump? Could both modules be ignoring the password I'm supplying and, if so, why? Lastly, is there anything else that I can try in order to allow me to log in by providing a password as part of the cmd?


texts: Constrain/limit certain records in sql table definitions

texts:
Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table?
My guess is not, but just want to make sure there is nothing I am missing.

----------------------------------
| user_id | post_id | comment_id |
----------------------------------
| 1       | 1       | 1          |
| 1       | 1       | 2          |
| 1       | 1       | 3          |
| 1       | 1       | 4          |
| 1       | 1       | 5          |
| 1       | 1       | 6          |
| 1       | 1       | 7          |
| 1       | 1       | 8          |
| 1       | 1       | 9          |
| 1       | 1       | 10         |
----------------------------------

So adding another comment for user 1 on post 1 should not be possible in the above case. But the table is just an example, if I can achieve this with a different database schema, don't hesitate to answer. I simply want to limit the amount of comments for a user on a certain post.

I work with MySql through Doctrine 2. I can of course build in such limitations in my abstraction layer, but I am curious if such constraints are possible in MySql (or maybe MongoDB) directly.


texts: How to generate a self-signed SSL certificate for MS SQL server 2008 R2 using OpenSSL?

texts:
Using OpenSSL, I'd like to generate a self-signed certificate for usage with Microsoft SQL server 2008 R2. This might be unusual, but I'd like to learn what's going on behind the scenes.

I have several problems:

1) The certificate, after having it installed into the trusted root certificates of the local computer, is not listed in MSSC utility.

2) Therefore, I have forcibly added it by changing the registry as described in several posts. After having done so, the SQL server service didn't start any more. The event log shows

  Unable to load user-specified certificate [Cert Hash(sha1)
  "350F3D0D987525361CE4CC5E6D72274228C5AF5B"]. The server will not
  accept a connection. You should verify that the certificate is
  correctly installed. See "Configuring Certificate for Use by SSL" in
  Books Online.

followed by other error messages which are undoubtedly caused by that first one.

I am nearly sure that the certificate I generated does not fulfill SQL server's requirements. However, for the life of me, I can't figure out what is missing. This leads to the third problem:

3) Many posts out there recommend the tool CheckSQLssl.exe to examine the certificate and to debug issues. However, I can't find that tool anywhere (not on my SQL server, no download available, even Microsoft doesn't seem to know about it).

These are the commands I am using to generate the certificate:

openssl genrsa -out key.pem 2048
openssl req -new -key key.pem -out csr.pem -subj /CN=server.example.com
openssl req -x509 -sha256 -days 36500 -key key.pem -in csr.pem -out cert.pem
openssl x509 -extfile /etc/ssl/pp-openssl.cnf -sha256 -days 36500 -signkey key.pem -in cert.pem -out ext.pem
openssl pkcs12 -export -out cert.pfx -inkey key.pem -in ext.pem

So I generate the key and a CSR which contains the server's FQDN as common name (lines 1 and 2). Then I sign the CSR and generate a self-signed X509 certificate (line 3). Then I add the EKU for Server Authentication (see below) (line 4) to the certificate and finally convert it into PFX format (line 5).

pp-openssl.cnf just contains one line to add the correct EKU:

extendedKeyUsage=critical,1.3.6.1.5.5.7.3.1

When examining that PFX certificate on the server, it seems that it should meet the requirements:

C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx |find "EXC"
    KeySpec = 1 -- AT_KEYEXCHANGE
    Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE

and (only relevant snippet of the output)

C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx
    2.5.29.37: Flags = 1(Critical), Length = c
    Enhanced Key Usage
        Server Authentication (1.3.6.1.5.5.7.3.1)

I have read that the EKU and AT_KEYEXCHANGE are important requirements; both are obviously met.

What else could be missing? I'll provide the complete output of certutil -dump -v if it helps.


texts: Oracle Enterprise Manager 13.3 - OMR Inventory Views PDB GUID

texts:
I have only PDB GUID.
How I can find PDB name in Oracle Inventory?
https://docs.oracle.com/cd/cloud-control-13.3/EMVWS/GUID-93E9DA8F-3560-4A3D-9126-09E8E36EDE64.htm#EMVWS32166
Which view and column?
Oracle Enterprise Manager Cloud Control 13.3


texts: SQL Server 2014 Express has reached database size limit. What is the quickest solution?

texts:
My SQL Server 2014 Express database has reached database size limit.

What is the quickest solution to get back up and running? Is Express software the same as the full version, with just some limitations, or is it a complete different software?

i.e. Can I just go to Microsoft store online, order a key for full version, and type it in to the current Express to activate full features, or do I need to do full uninstall of Express, reinstall of full version, copy database and such?


texts: How to determine the service pack of Sql Server

texts:
I am having a sql server 2012 enterprise edition installed on my win 2012 r2 server.

But i am unable to determine whether its installed with service pack or not.
how to find out which service pack is installed ?
Here is my screen shot, and my screen shot says that , its a ent.edition
with version number:11.2.5058.0 .

When I serached in msdn, i didnot see this version number in any of the service packs of sql server 2012.


texts: What's the best way to maintain a writable DB slave which is always overwritten by the master?

texts:
I need to replicate our main server's databases (MariaDB) to a local server in our office, which will be used as an offline mirror when we have connectivity issues.

The data on the master should always take priority over the data on the local slave. Our users will be instructed never to write anything to the local "offline" slave, however it will still need to be writeable to enable auth & sessions.

I believe that with a standard Master-Slave setup, this will create problems with data consistency.

Short of writing a shell script to drop & re-migrate all of the databases, & reset the replication logs when the connection is re-established, is there a better way to configure such replication natively with MariaDB or Galera?


texts: What is the significance of phpMyAdmin's configuration storage and what should I know about it?

texts:
After initializing phpMyAdmin on my server, I created a test database in the GUI platform. I realized that I had then received a notification in the phpMyAdmin browser that I needed to set up an initial configuration, in any database, and to "click here" to do it. So, I clicked the link and the database I was in was populated with a bunch of tables beginning with pma, which I believe represents the control user? I'm not really sure what this means or why every table begins with "pma_".

So, since the database I was in is now my "configuration storage" database, I renamed it and am just leaving it as is; no extra tables or data other than what the automatic configuration executed when I clicked the button that I mentioned previously.

The wiki for phpMyAdmin explains the configuration storage setup, but I don't believe I need to do that because the button that I had clicked in the alert space of the GUI did that for me.

So, what's the point of this database, what is pivotal information that I should know about it, and is it safe for me to just leave it alone and move on about my business? Thank you.

NOTE: I've used MySQL and phpMyAdmin before but it was under my university's environment and provided database; I never had to set up one of my own or do this confutation step.


texts: SqlPackage does not pick up variables from profile

texts:
I want to upgrade a database using .dacpac and sqlpackage.exe

here is how I run sqlpackage:

SqlPackage.exe
    /Action:Publish
    /SourceFile:"my.dacpac"
    /Profile:"myprofile.publish.xml"

The error I get is:

* The following SqlCmd variables are not defined in the target scripts: foo.

I have verified that myprofile.publish.xml file does contain that var:

<ItemGroup>
  <SqlCmdVariable Include="foo">
    <Value>bc\local</Value>
  </SqlCmdVariable>

I also verified that project that creates dacpac does publish successfully from within visual studio using myprofile.publish.xml

What else could I be missing?

(I'm using SQL Server 2012)


texts: Modelling the structure of a database involving products with multiple barcodes

texts:
What would be the best way to store barcodes for products? I have a situation where the same product can have many different barcodes, let's say I have 1 product and this product can have 100 different barcodes (which must be unique).

Is it better to store those barcodes in separate table? For example, the following diagram

means I will have 1 product and 100 rows with different barcodes for this product (same name and everything).

Or is it better to have 100 products with the same name, each with barcode column, as shown below?


texts: Optimize duplicated but negated subquery

texts:
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
 (b.ToBe = 1 and a.Being in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)
 or
 (b.ToBe <> 1 and a.Being not in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)

The most-nested queries being identical, so I thought the database would reuse the result instead of doing 2 queries.
But in the execution plan the query shows twice with the same cost of 19%.
Can this be optimized manually? Or a hint added?
I'd prefer a portable solution instead of a maximum perf one.
There are indexes on all named columns, sometimes even unique. The example is simplified, the whole where clause is in a join condition itself etc. So please optimize just the pattern, not the whole query.


texts: SQL Server DATEDIFF: How's dayofyear different from day?

texts:
When I look at the DATEDIFF() function on SQL Server, I see that it takes a datepart as its first parameter.

Possible datepart values include day and dayofyear.

I understand that dayofyear is the day number of the year (so that Feb 2 is 33, for example) and day is the day of the month (so that Feb 2 is 2).  However, I don't understand the difference when it comes to the DATEDIFF function.

Here are some examples:

select DATEDIFF(dayofyear, '2012-01-01', '2012-02-02')
returns: 32

select DATEDIFF(day, '2012-01-01', '2012-02-02')
returns: 32

select DATEDIFF(dayofyear, '2011-02-01', '2012-02-02')
returns: 366

select DATEDIFF(day, '2011-02-01', '2012-02-02')
returns 366

Are dayofyear and day equivalent for the purposes of DATEDIFF()?


texts: Insert on conflict return false otherwise true in single query in stored function

texts:
At now I use code like that

create function something()
returns boolean
as
$$
begin
   if exists (select 1 from some_table where some_cond) then
       return false;
   end if;

   insert into some_table (value)
   values (2)
   -- on conflict do !!! return false
   returning true;
end
$$
language 'plpgsql';

Is there a way to return value from on conflict section without defining a variable or temp table in one query?


texts: Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds?

texts:
Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not done), in an effort to improve it I rewrote it from scratch, and I came up with the following:

Now here's the boring word-problem portion:

We want to pull a list of the TOP 5 clients per sales rep, but exclude the TOP 10 total clients from that list. (So if John Doe has clients A, B, C, D, and E, and client C is one of the top 10, then only pull A, B, D, and E.)

To do this, the first query used a IN (... NOT IN ( ) ), so I thought that the nesting of IN was the issue, to rewrite it I did an OUTER APPLY that really broke everything.

Anyway, I fixed all that and I ran the query, and it still took 10-15 seconds which I assumed was parameter sniffing. To investigate I ran the query in SSMS, added OPTION (RECOMPILE) (to see what query plan would generate), and got the following:

It can be viewed here on Brent Ozar's 'Paste The Plan'. The query that generated this was:

DECLARE @Top10Temp TABLE (Id INT)
INSERT INTO @Top10Temp
SELECT TOP 10 Id
FROM Object1
WHERE Column2  = @ReportId
  AND Column3  = 0
GROUP BY Id
ORDER BY SUM(Column4 + Column5) DESC

SELECT Object2.*
FROM Object1 AS Object2
OUTER APPLY (
    SELECT TOP 5
        Object3.Id,
        SUM(Object3.Column4 + Object3.Column5) AS Column6
    FROM Object1 AS Object3
    WHERE Object3.Column3 = 0
      AND Object3.Column7 = Object2.Column7
      AND Object3.Column2 = @ReportId
    GROUP BY
        Object3.Id
    ORDER BY
        SUM(Object3.Column4 + Object3.Column5) DESC
) AS Object4
WHERE Object2.Column2 =      @ReportId
  AND Object2.Column3 =      0
  AND Object2.Id      =      Object4.Id
  AND Object2.Id      NOT IN (SELECT Id FROM @Top10Temp)
ORDER BY Object2.Column7
OPTION (RECOMPILE)

Now the same query but with OPTION (OPTIMIZE FOR UNKNOWN) generated the following plan:

Which can also be viewed at 'Paste the Plan'. This plan executed in less than 1 second.

If I add OPTION (OPTIMIZE FOR (@ReportId = #)), where # is the same as the @ReportId variable, I get the same query plan as the second.

Did I do something wrong? I'm having trouble understanding what happened, so any information is much appreciated. (I also really don't like trying to influence the optimizer via hints, but if it's necessary I'll keep it.)


texts:
We have a huge table with nested tables and such.  We are using Bulk Collect & FORALL to insert.

When loading data, I am getting this error:

ORA-00001: unique constraint (.) violated

There is no constraint name......so, how can I find out 1)  What or where is causing the error, and, 2) Which record it is?

I know you can keep exceptions with FORALL, but we are doing this in lots of 500, so it is hard to find out the actual record, and what constraint / nested table it is, etc.

I'm looking for any help anyone can give.

Many thanks!


texts:
As jkavalik sais: I'm looking for "the greatest value not greater than". I have a table containing a kind of IDs and values like those

ID     value
101    A
2003   B
4005   C

when searching for "2003" I want to get "B"
when searching for "2004"I want to get "B", too.
when searching for "2002" I want to get "A"

If a always had exact matching IDs i would write

SELECT value FROM table WHERE id = 2003

But is there a way to handle my problem describe above? I'm using firebird 2.5.


texts:
i need to use a mysql  update query with regex so my example is

update table_name set coulmn_name = replace(coulmn_name, 'regex', '');

i will explain a specified thing i need to do. my column is contained some text & some html tags i need to delete all img tags from this coulmn  what can i do to achieve this ?

this is my ReGex

/</?(?i:img)(.|\n)*?>/g

but i don't know how to make this
any help please


texts: Does DB2 have equivalents for Oracle's UTL_ENCODE and UTL_RAW modules?

texts:
I'm looking for something similar to:

utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw(<username>)));

I couldn't find any reference among the DB2's system-defined modules and I've read that:

  after migrating to DB2 there is a little surprise - there is nothing like UTL_RAW and UTL_ENCODE functions

that was written on March 28, 2010.

Considering that DB2's Oracle compatibility has increased with every release from v9, do the newer versions of DB2 provide any equivalents for Oracle's UTL_ENCODE and UTL_RAW?


texts:
If I have database db1 with schema sch1. I want to create another schema sch2 which will be used by different application and the tables in sch2 will be populated from tables in sch1. So my approach is to create triggers on insert, update, delete perform this task

So my questions are

1-  Is this  a good way to do this or there is a better way?

2-  I don’t want to affect the performance on db1 so I am want to replicate it to different server and create the triggers on the subscriber DB, so replicated server will have Sch1 and Sch2 on it,  Can I do that?

This is on SQL Server 2012 Standard Edition

Note: tables in sch2 have different names and different columns name. also more than one table in sch2 may get populated from data from sch1


texts: Design datastore persistence based on state of the Entity

texts:
I have an entity Content to persist in the database. The entity has an attribute LifeCycleState that denotes the present state of the content.

For the sake of elaborating further, assume we have DRAFT, REJECTED, ARCHIVED life cycle states. Corresponding to each of these, there is some detailed information that is supposed to be persisted in the database. Such as:

DRAFT {creationDetails}
REJECTED {creationDetails, updateDetails, rejectReason}
ARCHIVED {creationDetails, updateDetails, archivalReason}

Moreover, we are currently using a NoSQL datastore(mongodb) to persist the Content entity.

The question here in terms of designing the schema is based on the fact that the content would be in one state at a time, which is when any other state information would be useless to read.

For example, a content moves from state DRAFT to REJECTED to ARCHIVED, the users would just be interested in the archivalReason and would not worry about the intermediate state. [Not of interest to the question, there is a status change history that we are persisting anyway for bookkeeping the writes.]

Our current implementation defines an attribute stateSpecificDetails under Content and dumps all the attributes pertaining to various states within it, but that seems like an unwanted clutter to a single attribute knowing the fact that only one of such attribute would actually be set based on the state transitions.

Is there are a cleaner way of defining the entity or creating a separate collection for such content state based persistence?


texts: How do you tune MySQL for a heavy InnoDB workload?

texts:
Assuming a production OLTP system with predominantly InnoDB tables

What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from their defaults?
How do you spot potential bottlenecks before there is a problem?
How do you recognize and troubleshoot active problems?

Any anecdotes detailing specific status variables and diagnostics would be appreciated.


texts: Raid 1 or Raid 1+0 for SQL Server Architecture

texts:
We are looking at new servers.  Today our SQL Servers are built as follows:

C/D on RAID 1 ,
E : Tempdb on RAID 1,
F:log files on RAID 1 ,
G : Catalog data on RAID 1 ,
H: model data on RAID 1 ,
I : symbols on RAID 1,
J: DB maintenance on RAID 1 ,

So we have a lot of SSD’s, all on RAID 1 with no sharing.

We are considering changing to a large RAID 1+0.  However the concern is if we will end up with performance impacts since there are random reads that happen throughout the day but not as much as writes.

My question is - whether a large 1+0 RAID is suggested / recommended, which would reduce the quantity of SSD’s that we use and allow us to expand a single container easier or stick with single RAID 1’s?


texts: How to find the column value with the biggest storage size in the DB?

texts:
Is there any way to find out which column value has the largest storage out of all tables in PostgreSQL DB?

I'm looking for currently used storage, information_schema.column only shows the length of the datatype. Looking for something like max(pg_column_size()).


texts: How to design a database where parent table has default values

texts:
I have following tables (Its not actually accessories but I am using it as an analogy),

Accessories {AccessoriesId, Name, other accessory related attributes...}
ClientAccessories (ClientAccessoryId, AccessoriesId, CLIENTID, LastModified, CreatedDate, Username, isDeleted)
EmployeeAccessories (EmployeeAccessoryId, AccessoriesId, EMPLOYEEID, LastModified, CreatedDate, Username, isDeleted)

Now problem is that each Employee and each client will get a set of default accessories, which they can delete/update as well, which will set a flag in there specific table. They will add there own accessories as well in future.

I am not sure about if this is a valid design ?


texts: I am working with an application for students management. For the student's table I used a surrogate key (auto increment id). The problem I face with the surrogate key is when the user wants to show information about a student he must enter the id but the user knows only  the name of the student (and I can't use the name as a primary key because it's not unique). How can I solve this problem?


texts:
I have a table with name, email, user_id, and price. On this table, a user can appear more than once depending on how many purchases. I am trying to get select all details of users with the highest sum of price.
id   name  price   email    user_id
--   ----  ----- ---------  -------
1    Mike  100   @mike.com      5
2    John  50    @john.com      6
3    Mike  100   @mike.com      5
4    John  50    @john.com      1
5    Mark  50    @mark.com      3
6    Mike  100   @mike.com      5

As you can see from above Mike has the highest pricing in total I want a query that will get the sum total of column "price" and select all details of the row with the highest total first in an array and follow by the next higher. The result should be like below:
id   name  price   email    user_id
--   ----  ----- ---------  -------
1    Mike  300   @mike.com      5
2    John  100   @john.com      6
3    Mark  50    @mark.com      3

I want the 'id' to reset and start listing 1,2,3,4,5... and also I have other columns on the table I will like to get.
Also in addition I will like to know the count(how many times the users purchase).


texts: SQL Server 2014 Install Not Creating Default Instance

texts:
I am trying to install SQL Server 2014. The problem that I am having is that during the installation it is not letting my choose whether I want to use a default or a named instance. I have been through SQL Server Configuration Manager and under services there is nothing listed. I have tried this install with both 2014 express and enterprise on three PCs, two windows 8.1 and win 7. All three results are the same.

When at the "Connect to Server" at the start of Management Studio, there is nothing listed in the server name field. When I go to browse for more there is nothing listed in the local/network servers tab.

Is there anything that I need before the install? I have been searching for answers for about 3 days. Very few with the same issue. Any help would be appreciated.

**Note - This is a repost from https://stackoverflow.com/questions/24914930/sql-server-2014-install-not-creating-default-instance. Posting it here as this would be the more correct side.


texts:
I am running MySQL on a linux server.  I have taken a copy-back mysql enterprise backup from another linux full backup.  Ever sense then I have had problems getting MySQL to start.  The problem is that MySQL is not updating the PID file, it's not even create a PID file.  Is there anyway that I can manually create a PID for the MySQL instance?

One work around that I have been able to implement is the using the mysql-debugger which creates its own pid, but when I stop the debugger it still won't create/update the PID file.


texts: Not enough space for MySQL optimize; do new rows overwrite existing data?

texts:
My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space.

Is there any path around this? One idea is that as I add new data, it might consume from this extra space, rather than add to the disk usage, but I don't know how MySQL implements this.

The DB use is around 70GB and the available disk space is about 72GB. This is an isolated machine to which I have no physical access, hence I cannot add space myself.


texts:
Is there a way of making MySQL slow logs to start new log file every day ?
At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have separate files for every days slow logs.

Do I have to configure a my.cnf or some linux feature ?


texts: What's the difference between quiesce the instance and enable restricted session?

texts:
You can set the database in quiesced mode by the following command.

  alter system quiesce restricted;

After running this command all sessions except the ones that have DBA privileges will hang there.

Also you can enable the restricted session like this:

  alter system enable restricted session;

After running this command, just the users that have enough privileges can connect to the database.

My question is: are there any other differences between them? When should use them?

Thanks,


texts: Efficient SQL query to return parents that satisfy constraints on children

texts:
I've got a schema something like having an Entity table, with a to-many relationship to Attribute, which has a to-many relationship to Value. Attribute has a column Name, and Value has a column Value:

Entity
------
Id
Name

Attribute
---------
Id
EntityId
Name

Value
-----
Id
AttributeId
Value

I need to be able to construct a query that will return only Entity rows that have certain combinations of attribute/value children. Currently I'm writing the query something like:

select
    *
    from Entity e
    where exists(
        select * from Attribute a
            join Value v on v.AttributeId = a.id
            where a.EntityId = e.id and a.Name = 'color' and v.Value = 'red')
    and exists(
        select * from Attribute a
            join Value v on v.AttributeId = a.id
            where a.EntityId = e.id and a.Name = 'size' and v.Value = 'small')

This works, but every extra attribute filter adds another exists clause, and I'm nervous about performance. Is there a more efficient way to write queries of this type, or is it likely to be perfectly performant if I use correct indexes etc?


texts: Wanted: Android versions of JDBC drivers for common RDMBSes

texts:
I've got a Java-based application library I'm trying to port to Android. The package presently supports:

Oracle
Informix
DB2
Postgres
Sybase

...And can easily be configured to use any other SQL92 compliant RDBMS that has a JDBC driver. It would be nice to keep this flexibility.

However, I've been having troubles converting over the JDBC drivers. I've asked about this on Stack Overflow, but no solution yet.

Meanwhile, I'm wondering if anyone has successfully converted any of these JDBC driver packages and has made them available via the internet. There should be no license issues with at least any of these packages since all these are are access libraries and not the DBMS itself - all these vendors give away the JDBC drivers! But I've been looking and haven't found anything.

Anyone?


texts:
I was tuning this query, the execution plan looks fine for me but based on the statistics the Lock waits time is very high .What should i do to fix this problem?

(@P0 bigint)
SELECT mgmtemails0_.Id AS Id3_0_,
   mgmtemails0_.BCCToId AS BCCToId3_0_,
   mgmtemails0_.BCCToMe AS BCCToMe3_0_,
   mgmtemails0_.CCToId AS CCToId3_0_,
   mgmtemails0_.CCToMe AS CCToMe3_0_,
   mgmtemails0_.CreatedDate AS CreatedD6_3_0_,
   mgmtemails0_.EmailBody AS EmailBody3_0_,
   mgmtemails0_.FromId AS FromId3_0_,
   mgmtemails0_.InstanceId AS InstanceId3_0_,
   mgmtemails0_.isDraft AS isDraft3_0_,
   mgmtemails0_.EmailTemplateId AS EmailTe17_3_0_,
   mgmtemails0_.Priority AS Priority3_0_,
   mgmtemails0_.ProfileId AS ProfileId3_0_,
   mgmtemails0_.ReplyToId AS ReplyToId3_0_,
   mgmtemails0_.ReplyToMe AS ReplyToMe3_0_,
   mgmtemails0_.SendToId AS SendToId3_0_,
   mgmtemails0_.Subject AS Subject3_0_,
   mgmtemails0_.UpdatedDate AS Updated16_3_0_
   FROM dbo.MgmtEmailSent mgmtemails0_
 WHERE mgmtemails0_.Id=@P0

Usage stats : Reads: 892,766 (892,766 seek) Writes:37,457

Op Stats : 901,699 singleton lookups; 332,618 scans/seeks; 0 deletes; 0 updates;

size : 3,034,470 rows; 3.5GB; 29.2MB LOB

Lock waits : Row lock waits: 681; total duration: 7,317 minutes; avg duration: 10 minutes;

these are the top wait stats


texts: MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

texts:
For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode.

3 machines:
First machine runs Active Directory.
Second machine runs Node-1 (instance-1).
Third machine runs Node-2 (instance-2).

Availability group properties:
Cluster type: windows server failover cluster
Database level health detection is checked.

Availability replicas:

Instance-1 (Node-1) properties:
Role: primary
Availability mode: synchronous commit
Failover mode: manual
Connections in primary role: allow all connections
Readable secondary: yes
Seeding mode: manual
Session timeout: 10 seconds
Endpoint URL: TCP://somehost:port

Instance-2 (Node-2) properties:
Role: secondary
Availability mode: synchronous commit
Failover mode: manual
Connections in primary role: allow all connections
Readable secondary: yes
Seeding mode: automatic
Session timeout: 10 seconds
Endpoint URL: TCP://somehost:port (same port with instance-1)

Failover cluster manager properties:
Roles: 1 role is running and owner ise node-1.
Nodes: 2 nodes are up.
Disks: 1 cluster disk is online, owner node is node-2, capacity is
10GB.
Networks: 1 network is up, cluster use: cluster and client.

How can I fix it?
Best regards.


texts:
SELECT distinct [product_group]
      ,[product_type_name]
  FROM [Yield_Reporting].[dbo].[d_product]

For every product_group entry I want to have another product_type_name = AllType

So the resulting table should look like this,

I can't figure out how to make that result using an SQL query.


texts: How to resolve the master server shut down/unavailability in mysql with master - slave replication

texts:
Let us suppose that we have a master/slave setup and the application is running fine for the time being. All of a sudden the master server suddenly shuts down due to some problem.

Now how can I address this situation? What am I thinking is that:

stop the slave on slave
get the show master status on slave.
get the additional trasactions that were not replicated from the masters binary log
execute them on the slave
point the master to the applicatin and start as master.
recover the original master and make this as slave.

Is there anything else that adds more consistency?


texts: Do I Alter user quota in USERS tablespace?

texts:
I'm new to Oracle so please bear with me.

After installing Oracle XE 11.2, I created user SALES and granted create session and create table to the user.

Once I logged in with SALES I tried to create a table and I get the error ORA-01950: no privileges on tablespace 'SYSTEM'.

A related question in DBA StackExchange says that I should alter database default tablespace users;. and then assign a higher quota to user, like so: alter user SALES quota 50m on system;

I've already changed the default tablespace to users.

My question is: shouldn't I alter user SALES quota 50m on users; instead of tablespace system?


texts: Is it always faster to create indexes after loading data?

texts:
I have a large number of records (~1 billion) that I need to load into MongoDB (actually TokuMX, but whatever). I have about 6 different indices I need to create on the collection. Is it always faster to load the data, and then create the indices? When I look at Mongo's logfile, It seems like Mongo does some kind of large operation (maybe a row count?) before actually starting index creation, and it does this for every index I create.

Will it always be faster to create the indices after loading the data?

If I wait until after loading the data, would it be faster to create each index in the background at the same time rather creating them than one-by-one?


texts: Why does MS SQL Server return a result for empty string check when Unicode string is not empty

texts:
select * from (select N'ግዜ ' as t) as t2 where t= ''

the string 'ግዜ ' matches the above check, why is this?


texts: Unexpected index scan when doing query against varchar column with numeric parameter

texts:
I have a test table with schema like this:

CREATE TABLE `indextest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

and here are the rows in the table:

mysql [localhost] {msandbox} (test) > select * from indextest;
+----+--------+
| id | name   |
+----+--------+
|  3 | 111222 |
|  1 | hello  |
|  2 | world  |
|  4 | wow    |
+----+--------+
4 rows in set (0.00 sec)

when I query the table against the name column with a string, it looks good:

mysql [localhost] {msandbox} (test) > explain select * from indextest where name='111222'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indextest
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 13
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

But if I use a numeric as the query parameter, explain shows that the query optimizer is doing index scan:

mysql [localhost] {msandbox} (test) > explain select * from indextest where name=111222\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indextest
         type: index
possible_keys: idx_name
          key: idx_name
      key_len: 13
          ref: NULL
         rows: 4
        Extra: Using where; Using index
1 row in set (0.00 sec)

and under some other conditions (found in online slow queries), the query optimizer even suggests doing similar queries with table scan.

I don't understand why it behaves like this but not just raises error or converts the numeric to a string automatically.


texts:
We have increased disk space on the SQL Server, and there is definitely enough space on the server, yet below error is raised:

  com.softmar.fileserver.WebFileException: com.microsoft.sqlserver.jdbc.SQLServerException: Could not allocate space for object 'dbo.WebFileImage'.'pkWebFileImage' in database 'sslFileServer' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The properties of the database shows that there is only 1.1 MB available, yet other databases on the same SQL server shows much more space available on the properties.  So why is only this database not accommodating the disk size increase?  Looking at the file settings it is set to unrestricted growth.

This is rather critical so will appreciate your swift response.


texts: Help - SQL query // Extracting from XML // getting error if no data found

texts:
I am building a query, where I am extract values from XML. I am getting output if the xml has values. But if no xml found am getting error I need help to fill those columns with "yet to get" or something.

below is sample query:

Select Column1,Column2,Column3,Column4,
       extractvalue(xmltype(column5), '/PymtInit:notifyOutwardCrdtTrnsfrStatusReq/') "Response",
Extractvalue(Xmltype(Responsemsg)
From Table.Name
where column5 is not null;

Note: if I give Column5 is not null, I am getting output but I need output without "column5 is not null" also.


texts: SELECTs in Database Change Log and Source Control

texts:
We are overhauling the way that we store our database in source control and keep a change log of it. I was reading the following article: http://thedailywtf.com/articles/Database-Changes-Done-Right, and in the Short Section of "The Taxonomy of Database Scripts" it describes the three types of scripts (QUERY, OBJECT, and CHANGE). I like the idea of generalizing scripts into these three categories but I'm wondering about the QUERY type. Questions:

Why would someone want to put a SELECT statement into source control
outside of an object?
The database will change afterwards and make the QUERY script
unusable, what then?
The data may change returning a different result set, this would
defeat purpose of source control, what then?
Would the original result set have to be saved to solve the 2nd and
3rd issues?
What is an example of a SELECT statement that might be put into
source control?
Wouldn't a INSERT statement work better and store the results in a
table as in baselines?

I just can't see the purpose of storing SELECT statements into source control.

If there is a purpose could someone please answer the above questions and maybe state the pros and cons of storing a SELECT statement into source control?


texts: Create a user with privileges to create users

texts:
For our software we need a MySQL user who is able to create other MySQL users. This is possible with the MySQL root user but we do not want to use the root user in our software application. Is there a way to create a MySQL user which can create other users?

I read multiple topics and pages on the internet which say it is possible with the GRANT OPTION. But when I check the MySQL permission description, this permission is "The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess." So provides not the option to create users, only give them permissions that you have.

Maybe I understand something wrong but I'm afraid to do something wrong on our server. That's also the reason for my question.

I hope someone can explain to me if it's possible, and how, to create a user with the privileges we need.


texts: Mass data upload from Excel - technology

texts:
I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list.

Requirement:
There will be a web application which will allow user to upload Excel document in specified format(expecting 1 Million of rows) and it will then Save somewhere in DB and run some calculation against our data base values. After that will present user with some numbers/chart based on calculations.

My first question is MySQL DB good enough for this requirement over any other DB like Postgres?
Second question is how much time will it take to upload 1 million rows of data from Excel, considering there will be 6 columns?
Third, which technologies should I consider to make this application fast and efficient like InnoDB, etc.

Once again I have done my research but I could not reach into conclusions, and any DB expert feedback/help will be highly appreciated.


texts: How to ensure security with SQL Server Restore

texts:
I'm building a shared hosting SQL Server tool for linux, where users only receive access to their own database, with a SQL Login and SQL Database User that has db_datareader, db_datawriter and db_ddladmin rights to make sure they can not run any destructive actions on the database server (or choose settings we don't want them to).

We want to enable them to restore backups through a script. Obviously we'll run this restore with a sysadmin account to facilitate this for them (as a means of privilege escalation). Now on the Microsoft documentation site here the following notes are made (quoted verbatim)

  We recommend that you do not attach or restore databases from unknown or untrusted sources. These databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Herein lies my problem. How can I make sure that I do not risk my database server security when allowing end-users (unknown an untrusted sources) to restore arbitrary backup files? Since this process would be automated, there's no time for 'user intervention', but instead perhaps I need to (re)set some safe defaults and/or settings. Does anyone has guidance on what would be my best approach?


texts: Better performance with (Recompile) option, How?

texts:
I've got a query which runs for 1 hour without any query hint, But executes in just 2 seconds flat with the "Recompile" query hint. When I got a look at the actual execution plan produced by both the queries, The query without the "recompile" hint has got more Table scans(It's actually a huge plan, and there were are a lot of scans).

To make sure the query plan is not being used from the plan cache and to rule out parameter sniffing, I cleared the entire cache using DBCC Freeproccache and did the tests again, Still no difference!.

So, From my understanding,

With Recompile option tells SQL server not to cache the Query plan and generate a brand new one.(Result : generates a good plan and Performs well)

Without recompile option tells SQL server to reuse the plan in cache if it's available(but it's not there since I cleared the plan cache), or generate a new plan.(Result : generates a bad plan and Performs poorly)

So in both the cases SQL server will be generating a brand new plan, why the difference in plan and a humungous difference in performance all with just an extra Recompile option!!!!.


texts: Get members of a given group from N:M relationship

texts:
I have this N:M relationship:

CREATE TABLE auth_user (
    id integer NOT NULL PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer NOT NULL PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer NOT NULL PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

How to select all usernames which are in the group 'group1'?

I use PostgreSQL, but SQL which works everywhere is preferred.


texts: Package Check Failed: Oracle DB 11g on RHEL 7

texts:
Trying to install Oracle DB 11g EE on RHEL 7. Installed the necessary packages as mentioned in the below link (Section 6.1).

But I am stuck at the package check step, which fails because it couldn't find earlier versions of the packages.

Ex of one of the package error:

![Error Snippet of one particular package]

Expected Value: libaio-0.3.105 (i386)

Also, when I try to install the required package, the repo doesn't find those versions. If I ignore, I am getting the below error.

![Snippet: Stuck here]

Error in Invoking target "client_sharedlib" of makefile '<path>/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk'.

Stuck here. Please help.

List of other packages getting failed:

libaio-0.3.105
compat-libstdc++-33-3.3.3
compat-libstdc++-33-3.3.3 (twice)
libaio-devel-0.3.105
libgcc-3.4.6
libstdc++-3.4.6
unixODBC-2.2.11
unixODBC-devel-2.2.11
pdksh-5.2.14

Exact versions:

OracleDB: Oracle Database 11g Release 2 (11.2.0)
RHEL: 3.10.0-123.el7.x86_64 - Red Hat Enterprise Linux Server release 7.0 (Maipo)


texts: MYSQL food ordering portal database design

texts:
I'm trying to create a database for a restaurant ordering portal, I've created the tables excluding the payment table. I'm a bit stuck on whether its the correct structure.  If i could get any feedback it would be greatly appreciated! so the database should allow the following:

allow the customer to order
restaurant can update/add to menu
restaurant can confirm/deny an order

(i havent added Pkeys or Fkeys yet)
CREATE TABLE Customer (
customerID INT(11) NOT NULL,
Fname VARCHAR(100) NOT NULL,
Lname VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
phone INT(14) NOT NULL,
address VARCHAR(255) NOT NULL,
postcode VARCHAR(20) NOT NULL

CREATE TABLE CusOrder (
orderID INT(11) NOT NULL,
prodname VARCHAR(55) NOT NULL,
quantity INT(11) NOT NULL,
price FLOAT(10 , 2 ) NOT NULL,
totalprice FLOAT(10 , 2 ) NOT NULL,
menuID INT(11) NOT NULL,
deliveryfee FLOAT(10 , 2 ) NOT NULL,
customerID INT(11) NOT NULL

CREATE TABLE resturantMenu (
menuID INT(11) NOT NULL,
deliveryfee FLOAT(10 , 2 ) NOT NULL,
productName VARCHAR(255) NOT NULL,
productPrice FLOAT(10 , 2 ) NOT NULL

CREATE TABLE resturantLogin (
resturantUserID INT(11) NOT NULL,
menuID INT(11) NOT NULL,
username VARCHAR(255) NOT NULL,
pass VARCHAR(255) NOT NULL,
restDetails VARCHAR(255) NOT NULL,
restPhone INT(14) NOT NULL,
restAddress VARCHAR(255) NOT NULL,
restPostcode VARCHAR(20) NOT NULL

CREATE TABLE orderConfirm (
orderID INT(11) NOT NULL,
resturantUserID INT(11) NOT NULL,
status ENUM('yes','no') NOT NULL

not afraid of criticism so please fire away :)


texts: How really big applications (such as facebook) scale their database

texts:
I am curious of how one will scale huge database for example facebook DB. What I know is the application is deployed somehow to multiple data centers which means that each "node" in each data center should have fast access to that big DB.

On first place I can not image how they do deploys but that is another question (I guess?).

So if I am not wrong the question is how so many separate nodes access huge database and everything is consistent, stable, well-performing and scalable?


texts: Why does a plan with FULL optimization show simple parameterization?

texts:
I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized.

Then why is this plan showing Full Optimization, and Simple Parameterization at the same time?


texts: what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?

texts:
what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them?

when I fire below query

    SELECT TOP 20
qs.last_worker_time, qs.last_worker_time/1000000 last_worker_time_in_S,
qs.last_elapsed_time, qs.last_elapsed_time/1000000 last_elapsed_time_in_S
FROM sys.dm_exec_query_stats qs
order by qs.last_worker_time desc

I get reult like below.

On thing i notice was either both are equal or elapsed time is more than worker time.I would like to unsderstand significany of both so it also might help me in performance tunning.


texts:
I've got a simple table below,

Select * from message_log where  Message = 'BUILD' order by 1;

+------------+-------------+
|  **Message |     Log**   |
+------------+-------------+
|    Build   |  Day Start  |
+------------+-------------+
|    Build   |  Day End    |
+------------+-------------+
|    Build   |  Dusk Start |
+------------+-------------+
|    Build   |  Dusk End   |
+------------+-------------+
|    Build   |  Night Start|
+------------+-------------+
|    Build   |  Night End  |
+------------+-------------+

If I for example write a CASE statement

SELECT DISTINCT CASE WHEN (Log = 'Day Start') THEN 'RUNNING' END AS A
from message_log where  Message = 'BUILD' order by 1;

It'll return a Null value and a 'RUNNING' value, can I write this in such a way that it doesn't include the null row?


texts: SQL Server Service Broker with DML triggers

texts:
What I want to do:

Client database (C) is SQL Server 2008, has schema X
My database (D) is PostgreSQL 9.1, has schema Y

I want to transfer data from specific tables in C to D in near real time.

My approach:

Create DML trigger for each table I want to query
That DML trigger will query updated table and retrieve the data, form an xml and save it in the Service Broker queue
A script/ application that would read the Service Broker message queue and send them to D

Questions I have:

If the DML trigger I created throws an Error the transaction will not be committed. That is really bad. Is there a way to prevent it? or is there a way of sending the inserted data to the Service Broker via other means?
I am really looking for stability and reliability. In case Service Broker can't provide it, does polling the db makes sense?
Open to suggestions if my approach is completely wrong (for instance CDC (change data capture) is an option but the client server might sometime be SQL Server Express, so it's not an ideal solution).


texts: TimeStamp Column Auto Update Upon Insert and Modification

texts:
I have been assigned with a task that is update Timestamp column of a table upon each insert or modification of table  data. As per my kowledge this can posssible in case of inserts using default getdate() for the column but NOT modification without trigger.
I am looking for help on a mechanism to update timestamp column upon modification of table data without trigger, of course if possible in SQL Server.
Thanks in advance


texts: decrypt data in column before load into destination

texts:
I have a table named city which contains two columns

city_id (int)
city_name (varbinary) -- encrypted column

I want to extract data from this table, transform (decrypt the city_name) and load (decrypted city_name) into new table (destination).

I used the following query in oledb source in ssis. but it returns city_name column as NULL.

select city_id, CONVERT(nvarchar(50), decryptbykeyautocert(cert_id('Usercert'),NULL,city_name)) as city_name
from city

The above query works fine in sql server management studio query editor.

plz guide how to decrypt data before insert into destination.

regards,


texts: Failed to attach SQL Express DB, Could not continue scan with NOLOCK

texts:
We have a remote client that had to rebuild their server and has been having trouble with their SQL Express database since. I cannot remotely connect to this client unfortunately. I had them run "DBCC CHECKDB" and it showed one of the tables to be corrupted. I had them copy and send me the .mdf and .ldf files so that I could hopefully do something to resolve that.

I can't even attach that database though. I get the below errors when attempting to attach it...

  Attach database failed for Server 'GW1\SQLEXPRESS'.

  Could not continue scan with NOLOCK due to data movement.
  Converting
  database 'QAData1' from version 655 to the current version 782.
  ...
  Database 'QAData1' running the upgrade step from version
  690 to version 691. (Microsoft SQL Server, Error: 601)

Any idea how I can get around this error or where I could look to possible get a better error message?

Any tips how I can repair a .mdf file if I can't attach it?


texts: Postgres: Can I associate index with FK constraint?

texts:
Postgres FK constraint and index are independent.

Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by:

 WHERE NOT EXISTS (SELECT 1
                   FROM pg_constraint c
                   WHERE c.conindid = pg_stat_user_indexes.indexrelid)

We created FK constraints independently from indexes.

Is it possible to somehow associate index to FK constraint so pg_constraint.conindid will point from FK to index?

Something like:

ALTER TABLE my ALTER CONSTRAINT my_your_fk USING INDEX my_your_fk_i;

UPDATE I see something related in Postgres docs:

ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;


texts: Good morning everyone, i made this database i would like to know your opinions usertable and projecttable have a many to many relationship , ProjectCreatedByUser have two FKs (userID, projectID) , same for usertable and issuesID,IssueCreatedByUser have two FKS( IssueID and UserID) in every project it can have one or too many issues hence why i added projectID as a FK in the IssueTABLE


texts:
Trying to connect database with an encrypted connection, tried below sql queries:

sqlcmd -N -E -S tcp:SQLSERVER,1234  -U username -P password -d Database -Q "INSERT INTO table1 VALUES(1,2,2)

  ERROR: Sqlcmd: The -E and the -U/-P options are mutually exclusive.

sqlcmd -N -S tcp:SQLSERVER,1234  -U username -P password -d Database -Q "INSERT INTO table1 VALUES(1,2,2)

  ERROR Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SSL Provider: The target principal name is incorrect. Sqlcmd: Error:
  Microsoft ODBC Driver 13 for SQL Server : Client unable to establish
  connection

sqlcmd -S tcp:SQLSERVER,1234  -U username -P password -d Database -Q -E -N "INSERT INTO table1 VALUES(1,2,2)

  Sqlcmd: '-Q': Missing argument. Enter '-?' for help.

Am I missing something here?


texts: Treating certain Arabic characters as identical

texts:
In Arabic we have characters like ا (alef) and أ (alef with hamza).

Users write them interchangeably and we want to search them interchangeably.  SQL Server treats them as separate characters. How can I make SQL treat them as the same character?

I thought to replace any أ (alef with hamza) with ا (alef) at insertion but we have a lot of alternatives in Arabic language not just ا (alef) and أ (alef with hamza).

I tried Arabic_CI_AS and Arabic_CI_AI but that doesn't solve the problem.

Here is a script to regenerate the issue:

CREATE TABLE [dbo].[TestTable] (
    [ArabicChars] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
    (
       [ArabicChars] ASC
    )
) ON [PRIMARY];

INSERT INTO TestTable values (N'احمد');
INSERT INTO TestTable values (N'أحمد');

SELECT *
FROM TestTable
WHERE ArabicChars like N'ا%';

The result is:

ArabicChars

احمد

(1 row(s) affected)

The desired result would be both of the rows we inserted.


texts: Loading Oracle ctl export file into postgresql

texts:
I have a number of tables exported from an oracle database in the form of Ctl files with that data included within the file.

Im looking for a way to import these into postgresql but the the enterprise db toolkit seems to want a direct link to an already running oracle instance. A program similar to sql-loader would be excellent.


texts: Using postgres database from another computer

texts: I have PostgreSQL version 9.5 installed at my machine (Windows 7 Enterprise x64 bit version). I have several databases in schema 'public'. I would like to use/access one of these databases from another machine (Windows 7) using LAN connectivity (so that this database may act as a shared database between two machines). I was wondering whether is it possible to do through "listen_addresses" option in postgresql.conf dialogue window? If not, is there any alternative way to do this?


texts: How does InnoDB represent composite integer keys?

texts:
Let's say I have the following table:

Create Table Orders(server : tiny int, ticket : integer, primary key (server, ticket));

How is the composite primary key representation when stored on disk?

server * 2^32 + ticket (a numeric composition of the two keys)
server & " " & ticket (a string concatenation of the two attributes)
Something entirely different


texts:
In my PostgreSQL logs, I find this message:

LOG:  invalid magic number 0000 in log file 0, segment 1, offset 16121856

Which file is log file 0?  Here is my pg_xlog directory:

-bash-4.1$ ll pg_xlog/
total 49160
-rw------- 1 postgres postgres 16777216 Dec  6 06:27 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jan 21 09:10 000000020000000000000001
-rw------- 1 postgres postgres 16777216 Dec 21 05:29 000000020000000000000002
-rw------- 1 postgres postgres       56 Dec  6 06:30 00000002.history
drwx------ 2 postgres postgres     4096 Dec  6 06:30 archive_status
-bash-4.1$ ll pg_xlog/archive_status/
total 0
-rw------- 1 postgres postgres 0 Dec  6 06:30 00000002.history.ready


texts: Access to database denied :ELEPHANTSQL on Bluemix - “No pg_hba.conf entry for host localhost, user ADMIN, database SYSTEM”

texts:
While trying to connect to my ElephantSQL database on Bluemix using pgadmin, it throws the following error.

Access to database denied
The server doesn't grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host "XX.XXX.XX.XX", user "abcd", database "postgres", SSL on FATAL: no pg_hba.conf entry for host "XX.XXX.XX.XX", user "abcd", database "postgres", SSL off

To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

How can I access the pg_hba.conf file on bluemix?


texts: Is it possible to re-initialize sequence

texts:
How do I re-initialize the sequence value without drop and recreating them again
For example, the current sequence value is 3, now I want to restart it with 1 again.


texts: How to get the data using where_in (1,3 ) table in mysql?

texts:
We have 2 Tables

Table 1 is User

Table 1:

 id  |  name     |   sub_code
-----------------------------
  1  |    name1  |    1
  2  |    name2  |    1,3
  3  |    name3  |    2,4

Table 2 is Sub

Table 2:

  sub_id  |  sub_name
-----------------------
  1       |      sub1
  2       |      sub2
  3       |      sub3
  4       |      sub4

We need the following result:

 name2 | sub1, sub3


texts: How to run multiple sql statements in Postgres using the tool PGADMIN?

texts:
I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint.
All of these should be run in one in one script as batch script and I'm using PGADMIN IV tool.

Tried executing them between Begin and End as follows like Oracle,

BEGIN
ALTER Statement1..,,
ALTER Statement2..,,
TRUNCATE Statement3..,,
TRUNCATE Statement4..,,
INSERT Statement5..,,
INSERT Statement6..,,
ALTER Statement7..,,
ALTER Statement8..,,
---
-----
-------
---------
END;

But its not working,

Can some one please give a suggestion on how I can do this?


texts: Logical trees: difference between Before Project Normalization and Join Collapsed

texts:
The Before Project Normalization tree follows the Join Collapsed tree.
Using a sample set of ~250 queries - and Traceflags 8605, 8606, 8607, 3604 - I have not detected any difference between these two trees, in any of the sample queries.
Is it possible to construct a query where there is a difference between those two trees?
(This is research for a blog post - I will credit anyone who can help.)


texts: Stored procedure performs worse than straight query

texts:
I have a stored proc that takes 2 dates as parameters. The dates are used to specify a range of dates for the first in a series of CTEs to limit the scope.

When I run the query in SSMS as a query, it runs in 15 seconds and the query plan shows 81% on a sort.

When I make it a proc with the two date variables as parameters, it runs 19 MINUTES. 46% is on a clustered index scan.

Removing the parameters from the stored proc and adding them as variables inside the proc, it's back to 15 seconds and 81% on a sort.

Can someone explain to me why this would be?


texts: Based from experience, is dreamfactory better paired with a RDBMS like MySQL or NoSQL like MongoDB

texts:
This is our first time to create a data logging web application and we have chosen to use Dreamfactory as the backend framework. We have been used to with MySQL from the past with small amount of data but now considering into trying NoSQL like MongoDB.

These are the data that we are trying to log is basically timestamp of devices and when it was detected by a certain sensor and the signal strength when it was detected. So these items will be logged:

ClientDeviceID
HostDeviceID
Timestamp
SignalStrength

I'm trying to determine from the two database if which one is efficiently handling this type of data to be logged


texts:
If I want to implement both AlwaysOn Availability Groups and Encrypted Channels, how would I do this?

There is a desktop application (Visual Studio 2008 built) that interacts with this database, and the client wants traffic between the application and the database encrypted.  They also want redundancy in the database.


texts: PANIC HELP Locked out of Postgres Databases and unable to find pg_hba.conf

texts:
I am using PgAdmin 4 to access a number of local databases created and hosted on postgresql 11. In a recent update, I set a global password for my databases, but forgot to set passwords for my user accounts (postgres and my personal account). When trying to access my databases, I repeatedly get a user authentication failure.

However, when I went looking for the pg_hba.conf file to update the permissions, I was unable to find it. I searched my entire computer including hidden folders and was only able to pg_hba.conf.sample.

I have a lot of code and data stored in these tables and it would be a serious problem to lose access to them. Any help is greatly appreciated because I don't know what else I can do!

Thanks!

UPDATE

After still being unable to find my pg_hba.conf file, I installed an older version of Postgresql (v9.6) with clear passwords. Going into my root PostgreSQL folder (in /Library/PostgreSQL/), I cleared permissions on the /data/ folder and manually dragged it into the older PostgreSQL version (from /Library/PostgreSQL/11 to /Library/PostgreSQL/9.6). When rebooting PgAdmin, I was able to access my old data files in the newly created 'PostgreSQL 9.6' database, though the Backup function was disabled due to conflicting version IDs. I manually exported all of the data files I needed to .csv files saved locally, and to ensure no problems in the future, I did a fresh uninstall and reinstall of PostgreSQL. After reimporting, I am happy to say I have my files restored!

Thank you all for your quick comments and suggestions!


texts: Linux Error: 111: Connection refused LSNRCTL

texts:
I have a problem starting my listener, I am using Centos 6.5 and Oracle 12cR1.

Database has been installed successfully.

Now my issue is when I start my listener, the following error is shown:

LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gaurav)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
   Linux Error: 111: Connection refused
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "tuts" has 1 instance(s).
Instance "tuts", status UNKNOWN, has 1 handler(s) for this service...
 Handler(s):
  "DEDICATED" established:0 refused:0
     LOCAL SERVER
 The command completed successfully

My hostname is gaurav

So i create an entry in hosts file as

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
 192.168.1.1        gaurav      oracle

Also, I have created a listener.ora file, with contents as follows:

LISTENER =
  (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST =gaurav)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

sid_list_listener=
   (sid_list=
   (sid_desc=
   (oracle-home=/home/oracle/app/oracle/product/12.1.0/dbhome_1)
   (sid_name=tuts)))

I am able to ping my host gaurav.

Can anyone please help me in fixing the issue?

EDIT

[oracle@gaurav ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 05-JUN-2014 22:38:57

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr:  please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /home/oracle/app/oracle/product/12.1.0/dbhome_1 /network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/gaurav/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gaurav) (PORT=1521)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gaurav)(PORT=1521)))
  TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
  Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                05-JUN-2014 22:38:59
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/12.1.0/dbhome_1 /network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/gaurav /listener/alert/log.xml
 Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gaurav)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tuts" has 1 instance(s).
Instance "tuts", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


texts: Get ID which has two rows with different values for one column

texts:
Basically, I have a dataset that is a list of internal location identifiers attached to an ID (so each 'set' of geographical points has its own ID). So an 'id' column and a 'location' column exist in the table.

I need a way to find an ID which contains two different locations, that is:

ID | location
--------------
1  | a
1  | b
1  | c
2  | a
2  | c
2  | d

If I looked for 'a' and 'd', I would get 2.If I looked for 'a' and 'b', I would get 1. If I looked for 'a' and 'c', I would get either 1 or 2.

I am using PostgreSQL 9.4.


texts:
so i have been tasked to design a log database and i would appreciate some
feedback about my design.
I have an application that consists of three basic parts:

A frontend
A backend
A low level component

Each part can create a log message which needs to be stored in a database.
The parts(front end, backend, low level component) that create the log messages should be uniquely identified.
At the same time when looking at a message it should be possible to see which part created the message.
Each message has a specific type assigned to it and the type can be one of the following destinct values

Error,
Warning,
Info,
Debug

The message itself should also be unique, it should have a text that says what is the problem
and possibly also a description with extra information about the problem and under which circumstances
it could have happened. In addition the time the message was created is very important. Because of the
low level component we need microsecond accuracy.
Examples
Message : Pump Failure
Description: The pump is not pumping enough oil. Check the amount of oil and also check the temperature of the system.
Finally there are some extra "requirements" that in my opinion could affect the design of the system:
The low level component produces a lot of messages in a short amount of time. This could lead to
the database reaching its storage limit relatively fast. In that case the older messages should be
deleted first. However there are  rules that need to be taken into consideration before deleting a message.
An info is less important than a warning and a warning is less important than an error.
Another rule is unless I have reached a specific threshold I am not allowed to delete messages
of a specific type e.g Only if have more than 500 errors am I allowed to start deleting the older
errors.
My current design is the following:
Message
    Id (PK)
    Name varchar
    MessageTypeId(FK)
    Description varchar

MessageType
    Id (PK)
    Name Varchar

Sender
    Id (PK)
    Name Varchar

MessagesLog
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

However taking into consideration these extra requirements and thinking that I will need to do
a lot of checking on an application level if certain criteria are fulfilled before i delete a record
from the database, i thought about creating a separate table for each message type:
Message
    Id (PK)
    Name varchar
    Description

Sender
    Id (PK)
    Name Varchar

MessagesLogError
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

MessagesLogWarning
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

MessagesLogInfo
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

MessagesLogDebug
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

What do you think?


texts: Is the Service Broker good for publishing data between servers?

texts:
I'm trying to find a better solution when publishing data to a remote table.

So we have Server A and Server B.  Server A holds a master table which should be replicated to Server B.

Currently, we have this pretty chaotic stored procedure that runs every minute or so.  It locates any changes and inserts them into a temporary table on Server B.  Once loaded, it inserts new records or updates existing records from this temp table (The table is hit constantly and we want to limit any for of lock).

We don't have a DBA currently so I'm trying my best (I'm just a web developer) to figure out better solutions that could scale in the future.

Note: We have a couple dozen stored procedures that are like this.


texts: What is the VERSIONING_COMMITTING wait type in SQL Server 2012?

texts:
I have a fairly large SQL Server 2012 database in an availability group that experienced performance problems for about an hour, with the bulk of the wait in HADR_SYNC_COMMIT (waiting for synchronous replicas to commit) and VERSIONING_COMMITTING.

What does the VERSIONING_COMMITTING wait type signify?


texts:
I am unable to start postgresql on Ubuntu

postgres@DESKTOP-1TPO12P:~$ /etc/init.d/postgresql start
     * Starting PostgreSQL 10 database server                                                                                * Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with status 1:
    2019-04-30 14:58:57.825 DST [123] LOG:  listening on IPv4 address "127.0.0.1", port 5432
    2019-04-30 14:58:57.837 DST [123] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
    2019-04-30 14:58:57.891 DST [124] LOG:  database system was interrupted; last known up at 2019-04-30 14:44:15 DST
    2019-04-30 14:58:57.891 DST [124] PANIC:  could not flush dirty data: Function not implemented
    2019-04-30 14:58:57.891 DST [123] LOG:  startup process (PID 124) was terminated by signal 6: Aborted
    2019-04-30 14:58:57.891 DST [123] LOG:  aborting startup due to startup process failure
    2019-04-30 14:58:57.901 DST [123] LOG:  database system is shut down
    pg_ctl: could not start server
    Examine the log output.

I installed it using apt-get install postgresql-10


texts: Postgresql and MS Access unable to insert new data

texts:
I have a Postgresql server with a data base and table, columns dont matter. My issue is my primary key is set to BIGSERIAL, it auto-increments. However, on MS ACCESS when I create a form to insert new entrys - I am unable to do so unless I enter a digit into the bigserial ID field. It will not automatically increment.

I've tried playing with the enabled and locked settings in Access with no luck.

Found this person with same issue that seemed to never have been resolved.

https://stackoverflow.com/questions/3065130/how-to-use-serial-autonumber-in-a-postgresql-access-combination


texts: Database design for Subscription, Coupons and Plans

texts:
I have searched a lot in SO and SE but I did not get anything particularly about this kind of database design with coupon codes included. Please take a look at this problem.

I am developing a website in which I sell a service. People buy this service in advance (Pre-paid).

I sell that service in four plans

Monthly plan,

3 months plan,

6 months plan and

yearly plan.

But as encouragement for people to use my service initially, I want to give them a free coupon code(One or more). So that they can redeem in their accounts and use.

Coupons codes can be

Monthly Coupon Code,

2 months Coupon Code,

3 months Coupon Code and so on.

Each account will have an account_id in the user table.

I am struggling a lot to link This subscription model to coupons and Plans. I am a beginner at this kind of database design. Put me in a correct path.


texts: Log Drive getting filled up in SQL Server

texts:
We are facing an issue with the Log Drive (E:) getting filled up frequently.

Our DBMS is SQL Server 2014 SP1 with AOAGs set up.

We have weekly maintenance tasks:

Rebuild Indexes (Runs close to 1 Hour)
Update Statistics (Runs Cloe to 30 Minutes)
Check DB runs close to 30 minutes
Full Backup runs close to 4 Hours
Differential runs close to 2 Hours

We are not seeing any heavy transaction processing.

But we can see E: getting filled up.

Below are the Auto-Growth Settings:

10% Growth with unlimited.

Note: Every 30 minutes we have transaction log backup scheduled as this is AOAG environment.

My questions:

In this case, what is making the E: getting filled up?
Why is my Log not getting truncated?
Do we need to expand E: in this case?
What should be the action plan to get my log truncated?

As of now we are shrinking file now and then to free up space before we decide next plan of expanding drive.

Please suggest.


texts: Multiple Instances Of SQL Server Windows NT Eating Up Resources - What do they do?

texts:
I am fairly new to MS SQL Server and am curious as to what each instance of SQL Server Windows NT does. I currently have 4 of them running, and they are consuming most of my test server's resources. A few days ago my cpu would idle around 0~5% usage even with the 4 instances of SQL Server Windows NT running, but now they've gone wild and are using up almost all of my cpu and ram. (See image below)

I am only running 1 database as far as I know, and was curious what would happen - or if I could, end the task on 2 or 3 of these.

I also tried restarting my database, but they all came right back.


texts: Should not shrink database to release space in production?

texts:
My understanding is that we should never shrink the database in production to free up space?   Rather, we should shrink the Transaction log.

Could someone explain why?
Thanks!


texts: How can I migrate a WordPress site from MySQL to SQL Server Express?

texts:
I have developed a web portal in WordPress using xampp server. I have my database in MySQL.

Now I need to deploy this WordPress portal on other system with SQL Server Express Edition.

So how can I deploy it successfully with SQL Server?

What software or driver or service will be needed for this deployment on the system?


texts: Changing MaximumErrorCount when executing maintenance plan

texts:
I have defined a Maintenance Plan in Microsoft SQL Server 2012. It doesn't execute and it says

   DTS_W_MAXIMUMERRORCOUNTREACHED.
 The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure.
 This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors////

Where should change it?


texts:
MySQL Version 5.7, PHP 7.2, Ubuntu 18
When I run the following query,
SELECT schemaRev FROM table_na ORDER BY id DESC LIMIT 1

I was getting a PHP warning like,

PHP Warning:  mysqli::query(): Premature end of data (mysqlnd_wireprotocol.c:980)
PHP Warning:  mysqli::query(): EOF packet 2 bytes shorter than expected

I am executing this PHP file from cron by using /usr/bin/php filename.php
Kindly help me to get out of this, please.
In that query, if I add a single space, the warning will go away and everything will work perfectly fine.
I have rebuilt the table with the MySQL ALTER command, https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html#rebuilding-tables-repair-table
Now, one of the other program connecting to this database is complaining about the "MySQL Server has gone away".
Please help guys.. this is killing me for the last couple of days..!!
UPDATE...!!!!
When I turn off the MySQL Query Cache, the problem has resolved. But if I turn on the cache, the problem will come back.
Any ideas??


texts: How many tables are needed to implement the scenario depicted in my entity-relationship diagram?

texts:
How many tables are required to implement the scenario expressed in the ERD shown below?

My try:

Table (CR2) with the columns {B1, C1, C2}
Table (R1) with the columns {A1, P, B1}
Table (A) with the columns {A1, A2, A3}
Table (R3) with the columns {A1, B1, Q}
Table (B) with the columns {B1, B2}

Have I got it right? Am I missing something?


texts: MySQL query not using an index when table contains many records

texts:
I have a table:

CREATE TABLE `p` (
    `id` BIGINT(20) unsigned NOT NULL,
    `rtime` DATETIME NOT NULL,
    `d` INT(10) NOT NULL,
    `n` INT(10) NOT NULL,
    PRIMARY KEY (`rtime`,`id`,`d`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

A query that I run against this table is:

SELECT id, d, SUM(n)
FROM p
WHERE rtime BETWEEN '2012-08-25' AND DATE(now())
GROUP BY id, d;

I'm running explain on this query on a tiny table (2 records) and it tells me it's going to use my primary key index:

id  | select_type  | table | type   | possible_keys | key     | key_len | ref  | rows | Extra
1   | SIMPLE       | p     | range  | PRIMARY       | PRIMARY | 8       | NULL | 1    | Using where; Using temporary; Using filesort

When I run this query on the same table with 350 million records - it prefers to go through all the records and ignore my keys:

id  | select_type  | table  | type | possible_keys  | key  | key_len | ref  | rows      | Extra
1   | SIMPLE       | p      | ALL  | PRIMARY        | NULL | NULL    | NULL | 355465280 | Using where; Using temporary; Using filesort

Obviously, this is extremely slow.

I've tried losing the GROUP BY and even changed the BETWEEN to a simple '>', but it still won't use the key.

The only time I got it to use the key was when I used 'rtime = ..'.

I should note that all this data is actually only going about a week back, so when I try to get rtime > 3-days-ago I'm expecting a significant chunk from those 350 million records.

This query is supposed to run every 15 minutes, so the current 30-40 minute execution time is definitely unacceptable.

How can I construct the query such that it uses the indexes, or how should I index the table in order to get the fastest performance?


texts: PostgreSQL update join vs SQL Server update join

texts:
I recently started converting a personal project from Microsoft SQL Server to PostgreSQL and I was surprised at the abysmal performance I encountered doing an UPDATE JOIN between two tables.
Suppose they look something like:
CREATE TABLE foo (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

CREATE TABLE foo2 (
  id INTEGER NOT NULL PRIMARY KEY,
  bar INTEGER NULL
);

In T-SQL I would do an update using a join using something like this:
UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2
ON t1.id = t2.id;

But running in Postgres, the query is glacially slow.
If I change it to:
UPDATE foo
SET bar = t2.bar
FROM foo2 t2
WHERE foo.id = t2.id;

it's not a problem.
I get that the syntax is different but I would've expected the query optimizer to work out something in the same ball park. Instead, things go bananas. Besides the syntactical differences, is there a nuanced difference between the two queries that I fail to see?
Explain plans
Update on foo  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
  ->  Nested Loop  (cost=85852.43..6211995294.24 rows=338326628280 width=1027)
        ->  Seq Scan on foo  (cost=0.00..145721.10 rows=582410 width=1010)
        ->  Materialize  (cost=85852.43..247935.91 rows=580908 width=17)
              ->  Hash Join  (cost=85852.43..241627.37 rows=580908 width=17)
                    Hash Cond: (t1.id = t2.id)
                    ->  Seq Scan on foo t1  (cost=0.00..145721.10 rows=582410 width=10)
                    ->  Hash  (cost=75754.08..75754.08 rows=580908 width=15)
                          ->  Seq Scan on foo2 t2  (cost=0.00..75754.08 rows=580908 width=15)

Update on foo (cost=87575.47..535974.25 rows=581621 width=1022)
  ->  Hash Join  (cost=87575.47..535974.25 rows=581621 width=1022)
        Hash Cond: (foo.id = t2.id)
        ->  Seq Scan on foo (cost=0.00..151301.17 rows=1140417 width=1011)
        ->  Hash  (cost=75761.21..75761.21 rows=581621 width=36)
              ->  Seq Scan on foo2 t2  (cost=0.00..75761.21 rows=581621 width=36)


texts: Copying large amounts of data to another target tables

texts:
I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution time etc.

I have considered the below check list:

Set recovery model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.
Load data with the TABLOCK hint to release any locking during the executions.
Set MAXDOP to 30 and change it back to normal.

Example of the statements:

/*Insert Example*/

INSERT INTO [dbo].[Order](

      [CREATED]
      ,[LAST_MODIFIED]
      ,[number]
      ,[LAST_MODIFIED]
      ,[Owner_FK]
      ,[Owner2_FK]
      ,[Owner3_FK]

SELECT
      [CREATED]
      ,[LAST_MODIFIED]
      ,[number]
      ,[LAST_MODIFIED]
      ,[Owner_FK]
      ,[Owner2_FK]
      ,[Owner3_FK]

FROM [Order_old]
WHERE [Owner_FK] IS NOT NULL
   OR [Owner2_FK] IS NOT NULL
   OR [Owner3_FK] IS NOT NULL

/*Delete Example*/

DELETE Ord FROM [Order] Ord
WHERE [Owner_FK] IS NOT NULL
   OR [Owner2_FK] IS NOT NULL
   OR [Owner3_FK] IS NOT NULL

/*Update Example*/

UPDATE Order_Type
SET [TYPE]=NULL
WHERE [TYPE]=''

I thought about integration services but that is a limitation I have, Also I had a look at BCP tool, but seems there is no way to transfer from one table to another unless we export it to a file and then we import it from that file.


texts: How do I know that I am connected to a config server

texts:
I need a simple check for identifying that I am connected to a MongoDB config server.

I can check the process to differentiate between mongod and mongos:

> db.serverStatus().process;
mongod

Now I make an assumption that config server is the one without a replica set name, but it would not always hold true. I check that below returns a NULL value.

> db.serverStatus().repl.setName;

What is a better way to identify config server from mongod in a simple manner? This would be useful for custom prompt function based on the connection.


texts: Postgres - Multiple commands on table at the same time

texts:
How robust is Postgres when it comes to running multiple commands on a table at the same time?

Can I for instance, set a primary key and create a couple of indexes using multiple separate commands at the same time?

How about a query that will only affect/change data in one column? Is it ok to run other commands as long as they don't interfere with the column affected by the first command?


texts: How to match column names and values from joining and related tables?

texts:
I have the following DB structure:

cars:

id
make

features:

id
name

cars_feature:

id
car_id
feature_id
value

I want to be able to select all cars with all features and return results with feature.name as the column name and cars_feature.value as the value. Right now I am able to get all feature and all values but I only figured out how to do that with group_concat.

What I am looking for is the following output:

car_id  car_make  color  wheels  doors
1       Ford      blue   alloy
2       Audi             alloy   3

Data example: SQLFiddle


texts: Alter table statement waiting (for hours, on dev machine), but no locks shown

texts:
I have been trying to issue a simple:

ALTER TABLE tablename ADD COLUMN id_col character varying (30)

type statement on a Postgres 9.1.13 build on Debian. The application is still in private beta, so the volume is low, and yet something is blocking this statement. Following this Postgres lock monitoring post, and running the query,

SELECT bl.pid        AS blocked_pid,
     a.usename       AS blocked_user,
     kl.pid          AS blocking_pid,
     ka.usename      AS blocking_user,
     a.current_query AS blocked_statement
 FROM  pg_catalog.pg_locks  bl
 JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
 JOIN pg_catalog.pg_locks    kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
 WHERE NOT bl.granted;

returns no results.

If I run,

SELECT * FROM pg_stat_activity

all I see is the ALTER TABLE statement with waiting = t, and a couple of other queries in an IDLE state.

I am not a DBA, more of a database developer, and so apologies if I am missing something really obvious, but I have never seen a situation like this on a low volume dev box, so am at a loss as to how to proceed.


texts: Efficient methods to store and apply relationships defined by formulas?

texts:
I have a set of tables that are updated regularly such that batch processing calculations are not ideal.  The columns in the tables can be combined to produce metrics but each table uses a different combination of columns for each metric. Not all tables share all metrics.

For example, say there is table T1 and T2 with the following columns.

T1 (A, B, C, D)
T2 (Q, R, S, T)

And some metrics such that

Table Metric Formula
T1    M1     A+B
T2    M1     R
T1    M2     C-D
T2    M2     (S+Q)/T
T1    M3     B*C
T2    M4     T

My current thoughts are to create a table similar to the above and use dynamic SQL in a stored procedure to calculate the needed metrics when they are requested.  Imagine another table defining datasets by sets of table-metric pairs.

Is there a more efficient (by SQL lines to construct the query and readability for other coders) way to go about this?

I know SQL Server can store JSON and XML but it seems to me that simply storing the formula's as strings would do.  I am also considering an insert/update trigger to calculate and store the metrics to a table as the data arrives/updates; trigger would need to be fast so as to not hold up the table.

Using SQL Server 2016


texts: Can't insert arabic text into mysql database using mysql prompt

texts:
I had a local table with arabic values.when exported i got the following query.

DROP TABLE IF EXISTS `type`;
 CREATE TABLE IF NOT EXISTS `type` (
 `bs_id` int(11) NOT NULL AUTO_INCREMENT,
 `types` varchar(250) NOT NULL,
  PRIMARY KEY (`bs_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Data for table `business_type`
--

INSERT INTO `type` (`bs_id`, `types`) VALUES
 ('1', 'صناعة'),
 ('2', 'معارض'),
 ('3', 'أسواق'),
 ('4', 'إستيراد وتوزيع'),
 ('5', 'خدمات'),
 ('6', 'إستشارات'),
 ('7', 'أخرى');

I want to create this table on server so i just copied the above query and run it on server terminal.The query works fine but the values in table is showing like ".....".

tried this

 SET NAMES utf8;

before inserting the values,but failed.I don't have a cpanel installed on server.Command prompt is the only option.

Can anyone help!!

Thanks


texts: Can I "Execute T-SQL Statement Task" against all user databases?

texts:
I'm setting up a maintenance plan on Sql Server.

I want to add an Execute T-SQL Statement Task to the plan, however I do not see any way to set this task to run in every [user|system|all] database as you can with pretty much every other task type.

Is there a way to do this, perhaps with an "in-place" parameter in the script that would be used to substitute the database name?

I want to run a nightly script in the maintenance plan, but it needs to be able to put the database name in the script.

Should I call a stored procedure from the maintenance plan that iterates through all the databases desired using dynamic SQL to execute my statement?


texts:
I have to update multiple rows (> 100k) satisfying a particular criterion in production environment. Since this query takes much time, it comes in slow query.  someone told me that instead of making a bulk update, we should send update query row wise.

Example-
old query:

update table_1
set column_20 = 'abc'
where updatedOn > date_sub(now(), interval 1day) and column_19 = 'xyz'

New query/method:

select auto_increment_id_column
from table_1
where updatedOn > date_sub(now(), interval 1day) and column_19 = 'xyz'

Now use a "for  loop"  for all id's extracted from above, then send below query for each id:

update table_1
set column_20 = 'abc'
where auto_increment_id_column = id_from_for_loop;

This method seems to be counter intutive, so, please confirm, if it should be done. Also how it will affect master slave replication.

dbinfo -  mysql and using statement based replication

Major Update: Our DBA recently updated the replication to row based


texts:
So I am in the process of updating an existing notifications database design AS it wouldn't be optimal for storing group chat notifications.

Notifications:

id
user_id
type (enum)
thread_id (used for chat notifs)
contact_request_id (used for linking contact requests to a notif)
created_at
read (whether they read it or not)

At the moment, for each contact request, we would need to store 2 rows, for every single message, we would need to UPDATE 2 existing rows (each user has there own notif)...this is the main reason I want to find an optimal solution! Because when we add group chats, storing and updating 2..* rows will get ugly, and I can see data getting out of sync quickly.

I have thought of 3 solutions:

We create 1 notification AND link the users to that notification in another table (notification_users)
We create a seperate table for message notifications. So that we can facilitate for multiple users per notif?!


texts: Do concurrent in-memory databases exist?

texts:
I spent days searching the internet but all the databases I found are at most in-memory but not concurrent.

By concurrency I mean that they run on multiple threads and can do multiple reads concurrently without read locks. This way a system which uses a lot of complex SQL queries could scale much better with increased number of requests per time interval.

The only "solution" to this kind of scaling I found was just running multiple servers with duplicate databases.

If they exist, can you give me an example of some in-memory concurrent (relational) database?

If not, why do they not exist?


texts: Race condition in DELETE with nested query?

texts:
Is there a race condition if two processes both execute this query at (nearly) the same time?

delete
    table
from
    table with (readpast)
    join (
        select top (1)
            notPK
        from
            table with (readpast)
        order by
            table.id
    ) as selected on
        table.notPK = selected.notPK

Assume there are a variety of duplicate notPK's in table, that id is (of course) an identity and primary key, and that there is no shortage of records in this table. Also assume the default isolation level (read committed).

In other words, is there a chance that one of those processes will not get to delete any records from table even when there's no shortage of records there?

In other words, when does SQL Server apply row locks on the records that will get deleted by the query--before or after they've been SELECTed in that subquery?

I mentioned this in a comment, but the design is to process a First-In-First-Out queue that contains duplicates. In the real world notPK uniquely identifies a unit so in the ideal world it would be the primary key, but here in the real world there are duplicate entries in the queue for each unit. So when one unit gets dequeued then all the duplicates need to be dequeued as well, because each unit should be processed only up to once.

So the question I'm wondering is whether this query guarantees that a unit (and its duplicates) will always be deleted (when there are records in the table), or if it is sometimes possible for zero records to be deleted. For example, does it do this?

Read a notPK
Get references to all records with that notPK and that aren't locked, and place locks on them
Delete those records
Release the locks

...or does it do this?

Read a notPK and get references for all records with that notPK and place locks on all of those records, all in an atomic operation
Delete those records
Release the locks

My gut feeling is that it does the former. Therefore two processes could both read the same notPK, but only one of those processes would "win" the race and acquire locks to delete. The other would end up deleting zero records.

Is that correct?

Here's the same query written a little different with the help of @SlavaMurygin:

delete
    table
from
    table with (readpast)
where
    notPK in (
        select top (1)
            notPK
        from
            table with (readpast)
        order by
            table.id
    )