texts:
My current situation is this: I have 328 million lines of data in a flat csv file. Not optimal. I want to be able to query this data (I'll explain in more detail). Grep is getting tired. This data is static and will not change.

I'm new to PostgreSQL and am working with this data on my 2010 MacBook Pro, 2,4 GHz Intel Core 2 Duo, 4 GB 1067 MHz DDR3. Storage size is not really a problem, the data is around 65GB on a 1TB disk, but read / write speed is not great (it's not an SSD unfortunately). From what I remember from my CS studies a few years ago, indexing could help me improve query speeds, and I'm reading up on how best to do that.

Why am I using PostgreSQL? Peer pressure. Not really but let's go with that. I have PostgreSQL 9.3.4 running on OSX 10.9.2.

Now, what I'm trying to do. The data includes date and time info and spans about 10 months. I want, for example, to be able to:

Find and summarize all activity on, say, all mondays in the data set for a specific user and/or for multiple users
Look at differences between weeks in the summertime vs weeks in the wintertime
Between months; and
Hours of day, etc.

It's mostly timestamp related summaries that I'm interested in.

Each data record includes, besides the timestamp, a userID and a servID. In another file (which is stored in another table), each servID is linked to a location as X and Y values. It's these locations that I'm interested in displaying on a map, so I would be exporting these to plot using matlab, in aggregate for many users or focusing on specific users at a time.

All values are repeated, i.e. there are about 700.000 unique userIDs and 1500 unique servIDs. Timestamps are not unique either, the resolution is only seconds, so there are repeated timestamps.

So far I've created two tables, one with a small subset of test data and the X-Y location table:

create table test (userID varchar, junk1 varchar,
     date varchar, time varchar, junk2 varchar,
     junk3 varchar, servID varchar, junk4 varchar, junk5 varchar);
copy test from '/Users/path/someuser.csv' delimiter ';' CSV;
create table locations (col1 int, servID_DEC int,
     Col2 varchar, servID_HEX varchar, Locname varchar,
     Locname2 varchar, SERV_LOCY float8, SERV_LOCX float8, junk1 float8, junk2 float8);
copy locations from '/Users/path/locations.csv' delimiter ',' header CSV;

I had to trim the data, one column at a time, which was annoying, but I repeated this for each value:

update test set userID=trim(userID);

Then I added a column for the actual timestamps I had to construct:

alter table test add column timestamp timestamptz;
update test set timestamp=to_timestamp(date || '-' || time, 'DD.MM.YYYY-HH24:MI:SS');

Now my question (finally! Sorry about that): How do I create an index for the data in Postgres such that a query similar to this would be fast:

select userID, date, time, servID, timestamp, servID_HEX, SERV_LOCY, SERV_LOCX
     from test, locations
     where servID=servID_HEX
     and userID='<someusers>'
     and extract(dow from timestamp)=2;

I would also like to be able to quickly summarize the frequency of the servIDs used by a userID over a specified timeframe, i.e. he uses servID=1 1000 times, servID=2 600 times, so on (similar to the tabulate command in matlab)

(I renamed some of the variables and path names for reasons, I hope I didn't mess that up).


texts: InnoDB, Hash Table, Reference Table and Fragmentation

texts:
I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format:

CREATE TABLE IF NOT EXISTS `domains_list` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `domain` varchar(350) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Which is fine. New records are assigned what is effectively an auto-increment, so there's no fragmentation and inserts are in ascending order.

When new data comes in however (typically between 250K and 2M rows), I use two separate 'hash' tables to see whether the domain or URL already exists in the database. Strictly speaking it's not a 'hash' table, just a bunch of MD5s which I use ensuring values are unique, with the added benefit of the table being fixed length. The table is also partitioned.

CREATE TABLE IF NOT EXISTS `domains_hashes` (
  `id` int(10) unsigned NOT NULL,
  `segment` tinyint(3) unsigned NOT NULL,
  `hash` binary(15) NOT NULL,
  PRIMARY KEY (`segment`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (segment)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 ...
 PARTITION p254 VALUES LESS THAN (255) ENGINE = InnoDB,
 PARTITION p255 VALUES LESS THAN (256) ENGINE = InnoDB) */;

segment is basically the first character of the hash, which is used for the partition. With the remaining 15 bytes going into hash.

For seeing whether a bunch of domains already exist in the database, this works relatively well, however, the table gets fragmented due to the random nature of insertions.

The hash table is basically only used for insertions and quickly looking up whether a domain exists in the DB or not. During insertions, a script walks from 0-255 and performs the necessary check.

My question is, do you know of a better procedure in order to handle inserts/selects better? I believe when I started out with this database I simply had a key on domains_list.domain, which was slow.

I find that when the partitions are re-organised the lookups are very quick, but after a number of batch insertions, the same lookups slow down somewhat. Server has 32GB of RAM and I use 16GB for the buffer pool, while the table itself takes up 5.4GB on disk.


texts: Find Stored procedure which are using temp tables

texts:
We have got nearly 700 plus stored procedures with nearly 50% uses #tmp tables in their queries.

Need to write query to search through all procedures, and list procedures which uses tmp tables.

Can this be done by querying some system tables where procedure information is stored.


texts: Which grants are needed to access a table?

texts:
I want to know which grants at the very least are required to select/insert/update/delete from a table.

Say I have a table t in schema s in database d and a role r.

I start with granting the required permissions mentioned above:

grant select, insert, update, delete on table s.t to r;

What else do I need? Are USAGE on schema s and CONNECT on the database absolutely required, but also enough?

grant connect on database d to r;
grant usage   on schema   s to r;


texts: Pass values to column to create graphs in excel

texts:
I'm trying to convert the result of a sentence into columns to create a graph in excel.

I have this sql

select
hss.instance_number INST,
to_char(snap.begin_interval_time,'dd/mm/yyyy hh24:mi:ss') as Snap_Inicial,
hss.pool,hss.name,trunc(hss.bytes/1024/1024,2) SIZE_IN_MB
FROM DBA_HIST_SGASTAT hss,  dba_hist_snapshot snap
WHERE snap.instance_number = hss.instance_number
and hss.snap_id=snap.snap_id
and hss.pool='shared pool'
and hss.instance_number=1
and hss.pool is not null
and BEGIN_INTERVAL_TIME BETWEEN sysdate-5 and sysdate
and hss.name in ('KGLHD','SQLA','KGLS')
ORDER BY INST, BEGIN_INTERVAL_TIME;

and I get this output

INST SNAP_INICIAL        POOL          NAME           SIZE_IN_MB
---- ------------------- ------------- -------------- ----------
   1 03/05/2020 09:00:30 shared pool   SQLA           150
   1 03/05/2020 09:00:30 shared pool   KGLS           23
   1 03/05/2020 09:00:30 shared pool   KGLHD          14
   1 03/05/2020 09:30:32 shared pool   SQLA           160
   1 03/05/2020 09:30:32 shared pool   KGLS           33
   1 03/05/2020 09:30:32 shared pool   KGLHD          10

but I want an output like this

   INST SNAP_INICIAL        POOL          SQLA         KGLS    KGLHD
---- ------------------- ------------- -----------  --------  ---------
   1 03/05/2020 09:00:30 shared pool      150        23        14
   1 03/05/2020 09:30:32 shared pool      160        33        10

Is this posible?

Thanks.


texts: Zombie Sql Server DB on Secondary Replica is both in and out of an Availability Group

texts:
I have a database in SQL Server 2012 that is on the secondary replica of an availability group.  I removed the database from the AG db list on the primary and the db is currently in the Restoring state on the secondary.

The problem is that I'm unable to drop the database off the secondary.

DROP DATABASE [ZOMBIEDB]

Gives me the following error:

  "The database 'ZOMBIEDB' is currently joined to an availability group.
  Before you can drop the database, you need to remove it from the
  availatility group."

However:

ALTER DATABASE [ZOMBIEDB] SET HADR OFF;

Yields the following error:

  "Cannot complete this ALTER DATABASE  SET HADR
  operation on database 'ZOMBIEDB'. The database is not joined to an
  availability group. After the database has joined the availability
  group, retry the command."

So, how do I convince SQL Server that the db isn't in an AG so I can drop it?


texts: NLS_TIMESTAMP_FORMAT changing automatically and randomlly

texts:
I am using Oracle 11gR2. My nls_timestamp_format is changing randomly from dd-mon-rr to yyyy/mm/dd.

Does anyone have any clue why this is happening?


texts: MySQL Workbench - question about creating FK relationships

texts:
Forgive me a little new at databases and I came across something while working with Workbench that I have a question on.

In a very simple testing database, with three tables: Company, Department, Employee

Company          Department      Employee
 - id (PK)(AI)   - id (PK)(AI)    - id (PK)(AI)
 - Name          - Name           - Name

I created a 1 to many relationship between Company and Department and since Employees can only have one Department and One company, I created a 1 to many relationship between Company and Employee and Department and Employee.

When I did this, Workbench created a third Relationship column in Employee Called Company_Department_id and all of my columns that have FK all have now been check as PK as well, tables now looks like this:

    Company       Department             Employee
    - id (PK)(AI) - id (PK)(AI)           - id (PK)(AI)
    - Name        - Name                  - Name
                  - Company_id (PK)(FK)   - Company_id (PK)(FK)
                                          - Department_id (PK)(FK)
                                          - Company_Department_id (PK)(FK)

Questions

Why does workbench automatically create the Company_Department_id table? I feel like I should be able to delete it, but I just want to make sure I'm not missing something
How come Workbench automatically tagged each related column as a primary key?  Is it because they are primary keys in their own tables?  Should I keep them as primary keys?


texts: How can I replicate MySQL data from many servers to one?

texts:
All over the internet I can find manuals on how to replicate databases from master(s) to slaves.

What I need is to continuously keep a copy of table from 5 different servers in sync.

We have one application running at 5 locations. We need to have a copy of the system_log table from every one of those 5 servers always in sync on one new server at our 'headquarters'.

What is the best way to achieve this without using custom scripts?


texts:
How might one increment a "fixed" value in the SELECT clause of a query, so that each row has a different value set? For an obviously not working example that illustrates the intention:

SELECT
    name, ++x as ordinal
FROM
    foo;

name   | ordinal
----------------
john   | 0
paul   | 1
george | 2
ringo  | 3

The ultimate intention is to select users from a database to win first, second, and third prizes. I would have one user win first prize, five second prize winners, and twenty third prize winners:

SELECT
    name, IF(++x<2, 'first',  IF(x<7, 'second', 'third') ) as prize
FROM
    foo LIMIT 26;

name     | prize
------------------
alice    | first
bob      | second
charlie  | second
dotan    | second
erik     | second
frank    | second
george   | third
henry    | third
:        | :
:        | :

This is running on MySQL v5.5. Thank you.


texts: Where/How to generate the validation key and decryption key for SSRS View State Validation

texts:
According to this Technet article, I need to :

Generate a validation key and decryption key by using the autogenerate functionality provided by the .NET Framework.

The step does not explain how to generate the validation key and decryption key.


texts: Round-tripping column datatype causes size of table to grow

texts:
Table LogCount:

Column1 int       not null
Column2 int       not null
Column3 int       not null
[Date]  datetime  not null
[Count] float(53) not null

This table contains ~86 million rows and has the following indexes:

alter table LogCount add constraint PK_LogCount_Id primary key clustered
( [Date], Column1, Column2, Column3 )
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

sp_spaceused gives the following:

name        rows        reserved    data        index_size  unused
LogCount    85800181    8089216 KB  4226664 KB  3860968 KB  1584 KB

The Count column doesn't and never will store floating-point numbers, only integers, so I changed it to a smallint which (I expected) will save 6 bytes per row (float(53) = 8 bytes, smallint = 2 bytes):

drop index LogCount.IX_Column2_Date
go
alter table LogCount alter column [Count] smallint not null
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

Then I reran sp_spaceused:

name        rows        reserved    data        index_size  unused
LogCount    85800181    7670848 KB  5255528 KB  2414496 KB  824 KB

As expected, the index size has decreased drastically, but the data size has increased by a gigabyte!

I then reran the drop/alter/create statement above but using int (4 bytes) and got the following result:

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848032 KB  5255528 KB  2591688 KB  816 KB

Then I tried float(1) (also 4 bytes):

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848016 KB  5255528 KB  2591672 KB  816 KB

Finally I went back to the original float(53):

name        rows        reserved    data        index_size  unused
LogCount    85800181    10680584 KB 7726896 KB  2952464 KB  1224 KB

Compared to the original, the data size has increased by ~3.3 GB (almost doubled) while the index size has decreased by ~900MB.

A colleague suggested the culprit could be that MSSQL is allocating additional pages for the alter column statement and not freeing them afterwards, so I also tried executing dbcc shrinkdatabase after each step, but the results were the same.

So my questions:

Why does altering a column from a larger to a smaller datatype, cause more data space to be used?
Is sp_spaceused reliable? If not, what should I be using instead? If there isn't a better option, how do I determine if changing a column's datatype will have a positive or negative effect on disk space usage?


texts: Migrate database from SQL Server 2005 to SQL Server 2008 R2

texts:
DBAs... please be gentle with me, I'm a 'greenhorn' SQL Server DBA, pressed into service because my company's DBA had resigned and we haven't found his replacement, yet.

First, my situation:

I'm supposed to 'transfer' a web-app from a server in Singapore to a (pair of) newly-built server(s) in Indonesia. Unfortunately, the Singapore DB server is running SQL Server 2005 Express Edition, while the new server is running SQL Server 2008 R2. To make things worse, the developers of the web-app is no longer supporting us (we're practically coasting with the last version of the web-app)*.

I plan on creating a new Instance on the SQL2008R2.

I have several questions that have been bugging my mind:

How do I find out what 'features' (e.g., Reporting Service, Replication, etc.) has been installed on the Singapore server?
How to ensure a complete backup (Databases and Stored Procedures and other relevant tidbits)?
I noted that there are several Stored Procedures (not many, just 4 that I can see under the "Databases\AppDB\Programmability\Stored Procedures" tree), how to ensure successful migration of these Stored Procedures? Any gotchas I need to be aware of?

Thank you for any inputs.

* The plan actually is to just migrate the web-app as a "read-only" app for archival/audit
purposes; management has plans to deploy a better web-app in its place.


texts:
I'm trying to design a database to store information about an entity. This entity will pass through 5-6 stages. In every stage may happen some property modifications. It is necessary to have access to the state of the entity in every stage. Also each stage will have some additional properties unique for the current stage.

I'm wondering what would be the best way to design the tables. I was thinking to have a separate table for each stage (but the tables would have the same structure as the structure remains the same through all stages). Also for each stage table I would be able to create relations tables.

The only thing that is bugging me about this design is that I will have 5-6 tables with the same structure. On the other hand if I would put all the stages in one table and have a column which determinates the stage, then I don't see a way to create relations table per stage.

Any thougts ?


texts:
I have a database with an owner for it. My problem is that, though I have assigned a password for the owner, any other database user can access this database locally, even without a password.

How can I configure the database so that only a specific user has access to it (only with right password)?


texts: Difference between Clustered Columnstore Index Table in Azure SQL and Table in Azure Data Warehouse

texts:
What is the essential difference between a Clustered Columnstore Index table in Regular Azure SQL,  and a table in Azure Data Warehouse?

They both have columnar storage,  no foreign keys, no primary keys , etc. Seems structurally they are the same.
This is what our team is trying to understand.
So what would be the difference?


texts: Dealing with Mysql Replication and Functions

texts:
In MySQL replication Master-Slave, is there anyway to deal with the functions:

LOAD_FILE()
UUID(), UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE()
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RAND()
RELEASE_LOCK()
SLEEP()
VERSION()

We have Replication and I am kind of worry for the consistency of the data, I.E in my localhost.

MASTER

mysql> CREATE TABLE `t1` (   `number_id` varchar(30) DEFAULT NULL );
mysql> insert into t1 values (uuid_short());
mysql> select * from t1;
+-------------------+
| number_id         |
+-------------------+
| 95769054064672779 |
+-------------------+
1 row in set (0.00 sec)

Slave

mysql> select * from t1;
+--------------------+
| number_id          |
+--------------------+
| 167826651055390725 |
+--------------------+
1 row in set (0.00 sec)

Master

mysql> update t1 set number_id=0 where number_id='95769054064672779';
mysql> select * from t1;
+-----------+
| number_id |
+-----------+
| 0         |
+-----------+
1 row in set (0.00 sec)

Slave

mysql> select * from t1;
+--------------------+
| number_id          |
+--------------------+
| 167826651055390725 |
+--------------------+
1 row in set (0.00 sec)

Is there anyway to deal with it? I am not a programmer, should the programmers knows that it's replication database so he can start using variables with Mysql Functions to keep the consistency?


texts:
I have a one to many relationship between the Account table and a ScheduledPayment table. One account can have many payments but there should be a maximum of one payment per day.
Although unlikely, there are two scenarios which I need to cater for...

If there is one or more Payment records for an AccountId on a
day, with different payment and or reference then all payments
should be marked as cancelled.
If there is more than one Payment record for an AccountId on a
day, with the same payment and reference then one record should be
the winner and the other be marked as cancelled.

In reality, I don't anticipate seeing this scenario all that much because it is down to a concurrency issue in code, if two users are working on the same record at the same time.

Any ideas would be greatly appreciated!

Account
---------
AccountId
---------
    1
    2
    3
    4

ScheduledPayment
----------------
ScheduledPaymentId | AccountId | Amount | Reference | State
------------------------------------------------------------
       1           |    1      |  100   | ABCDE     | Live
       2           |    1      |  100   | ABCDE     | Live  (For these two, 1 would the winner and two marked as cancelled)
       3           |    2      |  100   | ABCDE     | Live
       4           |    2      |  110   | ABCDE     | Live
       5           |    2      |  110   | ABCDE     | Live
       6           |    2      |  130   | ABCDE     | Live (For these, all would be marked as cancelled)
       7           |    3      |  100   | ABCDE     | Live
       8           |    4      |  100   | ABCDE     | Live


texts: Group activities in activity feed by users and products

texts:
On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there.

One of them is the grouping. I've built it so that if multiple users buys the same 2 or more products the activity feed shows it as one activity: "user1 & user2 bought product 1, 2 & 3".

The problem is that if user1 buys product 1 & 2 and user2 buys product 1, 2 & 3 it appears as two different activities: "user1 bought product 1 & 2" AND "user2 bought product 1, 2 & 3".

I would like it to show as "user1 & user2 bought product 1 & 2" and then another saying "user2 bought product 3"

I've made a simplified sqlfiddle of what I've got so far. Try changing it by deleting NULL, 'Bought', 1, 3

Is this something that is possible to do in MySQL or should I do it in PHP?


texts: Retrieving SQL Server InfoMessages in PowerShell 5

texts:
The following PowerShell function worked correctly on PowerShell 2 but now I'm testing it on PowerShell 5 and can't make it work. It is meant to automate backup restore tests. But since SQL Server backup related tasks use messages instead of returning a recordset I had to add a add_InfoMessage with a handler.

I think I could do it with Invoke-Sqlcmd -Verbose but I prefer this method.

I've also searched how to do it with SMO but found nothing

function Restore-Test {
    param (
        [string[]] $bkpPath,
        [string[]] $testServer
    )
    $message = "Inicio: " + (get-date);
    $message += "`nArchivo: " + $bkpPath;
    $message += "`nTestServer: " + $testServer;
    $connString = "Server=" + $testServer + ";Database=master;Integrated Security=SSPI;";
    $commandString = "print 'Message from MSSQL!';";
    #$commandString = "DBA_TOOLS.dbo.sp_RestoreTest N'" + $bkpPath + "'";
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString;
    $conn.Open();
    $cmd = $conn.CreateCommand();
    $cmd.CommandText = $commandString ;
    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $message += "`n" + $event.Message };
    $conn.add_InfoMessage($handler);
    $conn.FireInfoMessageEventOnUserErrors = $true;
    $cmd.CommandTimeout = 0;
    $cmdResult = $cmd.ExecuteNonQuery();
    $conn.Close();
    $message += "`nExecution result: " + $cmdResult + "`nFinalizacion: " + (get-date);

    return $message;
}
export-modulemember -function Restore-Test


texts:
I'm trying to set up a database on a Solaris 10 platform that had its database corrupted before. Due to the lack of backups, I was forced to reinstall Oracle server. Currently I'm facing an issue where answers I have found keep sending me in loops.

I opened sqlplus, and saw that it was connected to an idle instance, so I performed the STARTUP command. This led me to the following errors:

ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist
           [/export/home/oracle/u01/app/oracle/product/11.2.0/dbhome_1/dbs/<ORACLE_BASE>]

So after some googling, I was told to try changing the diagnostic_dest via the following: ALTER SYSTEM SET diagnostic_dest='u01/app/oracle';, which was equivalent to setting the diagnostic_dest to my ORACLE_BASE.

However, the error below occurred:

ORA-01034: ORACLE not available

After some googling again, I found that the solution was to start the database (which I can't)! I have no idea how I should proceed from here. Any help will be much appreciated. If any other information is required, I will gladly update it in the OP.

I have tried restarting lsnrctl, but it doesn't seem to help.

I would change all the default parameters within the file locations, but from what I found, the init.ora file which was supposed to be used already has diagnostic_dest='<ORACLE_BASE>', the spfile.ora file also has diagnostic_dest='/u01/app/oracle'. I'm really lost as to how I should carry on.


texts: PostgreSQL ERROR: cannot freeze committed xmax

texts:
First of all, I am new to PostgreSQL database.

In my PostgreSQL log I can see errors bellow and I don't know what is wrong or what I should change.

2018-03-29 10:54:38 CEST::@:[5059]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:55:38 CEST::@:[5144]: ERROR:  cannot freeze committed xmax 710196
2018-03-29 10:55:38 CEST::@:[5144]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:56:38 CEST::@:[5182]: ERROR:  cannot freeze committed xmax 710196
2018-03-29 10:56:38 CEST::@:[5182]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:57:38 CEST::@:[5208]: ERROR:  cannot freeze committed xmax 710196
2018-03-29 10:57:38 CEST::@:[5208]: CONTEXT:  automatic vacuum of table "jiradb.public.propertyentry"

I use default vacuum settings except this one parameter:

vacuum_cost_limit = 200

We are using PostgreSQL 9.4.16, single node solution on RedHat 7.

Can you help me?


texts:
I work on a transaction banking service project, and we need to have a central database to keeping client's info, account info, transaction details, etc.

So, how can I estimate the size of database needed?
Or, is there any industry benchmark for database size?

Thank you!


texts:
I have this table in my db

id | major | code_lesson | files

Which the contents look like this

id | major | code_lesson | files
1  | Math  | MAT12       | mat.pdf
2  | Socio | SOC15       | soc.pdf
3  | Math  | MAT12       | mat_pre.pdf
4  | Physic| PHY19       | physci.pdf
5  | Physic| PHY19       | phy2.pdf

I want to display this table like this

major |file1       |file2
Math  |mat.pdf     |math_pre.pdf
Socio |soc.pdf     |
Physic|physci.pdf  |phy2.pdf

Can you please explain me how to achieve this, since my output result  1 rows :

select major, files as file1, (select files where max(id)) as file2 from db_school

Thanks in advance


texts: MYSQL showing access denied for use '"@ localhost' when trying to create a new database

texts:
I just installed a new server and tried to create a new database via phpmyadmin.

When I try to create a new database I get the below error message:

To be precise MYSQL said:

  #1044 - access denied for use '"@ localhost'

Does anyone know what may be causing this error or what steps should I take to further troubleshoot?


texts:
I am trying to setup replication b/w two Derby databases. Its embedded with an application. Configuration for replication is done as per the documentation here
When the application tries to start master instance, I get to see the following message. There is some exchange happening on the replication port b/w master & slave. But master quits with the message. Per the documentation,before starting master, the database is copied to the slave location. And no transaction happens in master after that.  What does the message ( mismatch in log instance) mean? What could I be missing?
 Lost DB connection URL= jdbc:derby:db/NMS;startMaster=true;slaveHost=10.200.10.66 + REASON= The log files on the master and slave are not in synch for replicated database 'db/NMS'. The master log instant is 1:200335, whereas the slave log instant is 1:196097. This is fatal for replication - replication will be stopped.:


texts: Spike and time out errors every x th minute

texts:
I have very complicated situation here.

Application team was reporting that they see spikes and errors every 20 mins. We checked what was running and found log backups.

So we tuned log backups using different compression level available in idera third party software. They used to run for 5 mins and now started competing in 3 mins.

Application team saw little relieved but issues seems to be relevant and they are still concern on spikes.

To play little further, we changed log backup to run every 13 mins to find whats really going on. And here it is, mo more time outs when log backup completes or run other than every 20 mins schedule.

Checked everything on SQL Server and did not see any other stuff running every 20 mins.

The issue starts every 20 mins which last for about on avg 20 seconds. Unable to find any SQL queries or related process. So far no scheduled task at O/S level found as well.

Waits: LCK-XX being the higher no followed by Pageiolatch...

Snaphshot isolation for all db's: RCSI

I will try to add more info, but please suggest what can be checked here to find and resolve this performance issue occurring every x mins.


texts:
I have the following query:

select
    sum(leads.source_cost) as cost,
    coalesce(sum(assignments.recipient_revenue), 0) as revenue,
    (coalesce(sum(assignments.recipient_revenue), 0) -
              sum(leads.source_cost)) as profit,
    date_format(leads.updated_at, "%m/%d/%Y") as date
from `leads`
left join `assignments` on `assignments`.`lead_id` = `leads`.`id`
                       and `assignments`.`deleted_at` is null
where leads.updated_at between "2017-08-24 04:00:00"
                           and "2017-08-26 03:59:59"
group by `date`

I need the sum(leads.source_cost) to use distinct leads.id in my query because a lead can be assigned more than once.

How do I do this? I can't do a group by because I need the group by to use the date alias.


texts: Using XQuery to filter out some children, but preserve parents

texts:
SET NOCOUNT ON;
DECLARE @xml AS Xml = 'bbb<c>ccc</c><d>ddd</d>';
SELECT @xml;
SELECT @xml.query('/a/*[self::b or self::c]');
SET @xml.modify('delete /a/d');
SELECT @xml;

Gives the following resultsets

Original:

bbb<c>ccc</c><d>ddd</d>

Filtered to exclude non-(b|c) - but parents are missing:

bbb<c>ccc</c>

What I want (available with delete steps but not with .query):

bbb<c>ccc</c>

Is is possible to preserve the parents in an XQuery?


texts: How to get a selection from a join, where there is no result in one of the tables

texts:
I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify the table structure as an example.

I have 2 tables I am working with. Each is an audit table which keeps track of changes such as updates and inserts via a trigger. Each table also has a column to store the date of change.

One table contains a county code, the other contains the street address: t_county and t_address

t_county

id:       NUMBER(9)
county:   NUMBER(2)
date:     DATE

t_address

id:       NUMBER(9)
address:  VARCHAR2(60)
date:     DATE

I need to build a query which selects the county and address if either one has a row on a specific day.

Say there is a change of address on a specific day, but there is no change in the county. I would like for the query to have a result such as below

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  NULL   |123 test|
+---------+---------+--------+

If there is a change of county but the address is the same:

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  32     |  NULL  |
+---------+---------+--------+

How can I build such a query?


texts: MEMORYCLERK_SQLOPTIMIZER in SQL 2012 Memory usage extremely high and causes server to crash

texts:
I have been having issues with our SQL server for a while now.  It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory and eventually cause the server to crash.

Here is the SQL version we are using:

  Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64)   Jul 22 2014
  15:22:00      Copyright (c) Microsoft Corporation     Enterprise Edition
  (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

It is on a VM on Windows 2012 Server.

It has 20 GB of RAM allocated to it and the Max Server Memory is set to 16.5 GB.

I have seen the MEMORYCLERK_SQLOPTIMIZER grow to about 11 GB at the time of the server crash.

Does anyone know why that is happening?  What is causing the MEMORYCLERK_SQLOPTIMIZER to get so high?

I have looked it up and it looks like it has to do with ad-hoc requests, but is there something I can do to bring that memory down when it gets so high so that I can prevent a server crash?

Do we just need to add more memory or is there a memory leak somewhere?

I have already enabled optimize for adhoc and that does not seem to help.  We will be deploying SP2 soon. We are  using VMWare 5.1 and we are not using dynamic memory.


texts: SQL Server 2008 R2 There is insufficient system memory in resource pool 'default' to run this query

texts:
I have a 32-Bit Windows 2003 Enterprise Edition SP2 Server running SQL Server 2008 R2 (10.50.1617.0) Enterprise Edition. The server is a dedicated SQL Server box for our SharePoint 2007 environment.

The server has 36 GB of ram and I have enabled AWE in SQL Server.

I am continuously receiving the error: "There is insufficient system memory in resource pool 'default' to run this query" in the Windows event logs.

I am running perfmon and monitoring the SQL Server Memory Manager counters for 'Target Server Memory' and 'Total Server Memory' as well as the Memory counter for 'Available KBytes'. Currently the 'Total Server Memory' counter is sitting at 5 GB.

I have configured the 'Minimum server memory' to 25 GB and the 'Maximum server memory' to 30 GB in SQL Server. The server is showing 31 GB of available memory, but I continue to constantly receive this error.

The index creation memory is set to 0 KB (dynamic memory) and the Minimum memory per query is set to 1024 KB.

I have experimented with the Minimum memory per query value but it did not stop the error from occuring.

If my server has 31 GB of available memory (20 GB of this are available to SQL Server), why am I receiving this error?

I have spent hours searching in forms, but others who are receiving this error seem to be low on virtual memory, which is not the case for me.

Does anyone have any ideas what could be causing this error?

I have tried restarting SQL Server and even restarting the server itself, but this error always returns within a minute or two.

I am planning on updating to Service Pack 1 as soon as I have approval to do so, but I doubt that will solve my problems.

Any advice would be greatly appreciated.


texts: mongodump failing to connection replica set

texts:
I'm able to connect to secondary node from arbiter node of the replica set.
mongo -u backup02 -p backup02_password --port 8133 --host p1dummy:8133,p2dummy:8133,drdummy:8133 --authenticationDatabase admin --ssl --sslAllowInvalidCertificates

mongo8133:SECONDARY>

when I try to connect the secondary node form arbiter node using mongodump command which is not happening . with mongodump command I'm passing the same parameter as mongo command.
I tried enabling verbose log but that's not giving me the complete log information.
I'm able to connect specifying p1dummy as the parameter however I should also be able to connect using connection string like I used for mongo for host argument:
/usr/bin/mongodump -u backup02 -p backup02_password --port 8133 --host p1dummy:8133,p2dummy:8133,drdummy:8133 --authenticationDatabase admin --out /tmp --db mydbname --ssl --sslAllowInvalidCertificates --excludeCollectionsWithPrefix=system --verbose=5

2021-04-01T12:48:43.899+0000    will listen for SIGTERM, SIGINT, and SIGKILL
2021-04-01T12:48:47.400+0000    Failed: error connecting to db server: no reachable servers


texts: Deploying SQL Server database changes from test to production server

texts:
I have a high availability production server that runs SQL Server. It is constantly being read/write to by hundreds of users.

I have an exact copy of the database on a testing server and do all the development in that environment. Every few weeks, there will be major changes made to the front-end application and the database itself (e.g. new stored procedures, new columns, new tables).

Because the data on the production server is always the latest, I don't want to lose that data when I want to make changes to the database schema. The current process for deploying changes is:

backup the production server and pause it
restore it into the testing server
make all database changes in the testing server
Backup the testing server
Restore the new version to the production server and un-pause it

The steps seem convoluted and is causing unnecessary downtime on the production server. What I would like to do is update the production server database in the background while still allowing users to continue reading/writing the database.

So far I can see that you can use copy database wizard or use scripts to make changes, but they seem to want to drop/create the database on the production server to make changes. How could I achieve a less severe way of updating the production server's database?


texts:
Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space.  However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive.

I looked at what the log_reuse_wait_desc was and found  it to be ACTIVE_TRANSACTION

I started to wonder if for some reason the log file was filling up and the auto grow not kicking in, and after some research I found that the log file should still grow even during an ACTIVE_TRANSACTION.

I found an article on a similar issues where the tempdb log ran out of space:

http://sqltimes.wordpress.com/2014/07/05/sql-server-error-messages-the-transaction-log-for-database-tempdb-is-full-due-to-active_transaction/

Here they issued a CHECKPOINT to resolve the issue on tempdb.  I know a CHECKPOINT flushes dirty pages to disk, however I do not understand how this would fix the ACTIVE_TRANSACTION problem?

Furthermore I also do not know why we get this alert when there is plenty of space.  Is there a situation where a tempdb can fill and auto grow not work for some reason?


texts: Postgres Database too large to start service

texts: Due to some craziness on a server I am looking after, I have a huge database that needs to be truncated (44GB despite being a simple site). However, I can't start the postgres service as there is not enough room on the server. How can I delete records, vacuum or do anything else that will make space without being able to start the pgsql service?


texts: SQL Server 2008 Patch on Clustered Server

texts:
I am patching SQL server, going from SP1 to SP3, on a cluster while following Microsoft's instructions on how to do it.

Every single place I read tells me to do the following:

Pause the passive node
Update the passive node
Resume the passive node

And then it goes on, but when I try step number 2, I get the following error in the analysis before actually updating:

  Not clustered or the cluster service is up and online

If I try to do the update without pausing the node everything works fine, but I don't think that's a safe way to go.

What am I doing wrong here?

update:
I was actually following Windows itself patching instructions which states that the node should be paused. The instructions on how to patch SQL don't include pausing/resuming the node. But still, I believe this would be a best practice since something can happen to the active node making the cluster attempt to failover to the node being patch, with the results of that being unknown to myself.

Does anyone know what would happen?


texts: Efficency of stored procedures vs raw queries

texts: I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries?  I am specifically interested in SQL Server but would be interested in any and all databases.


texts:
I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports.
I know how to do a transactional replica but I don't know how to replicate only the inserts and updates, I don't want to replicate the deletions.
Is it possible to do this with replication between servers or do you have to program it yourself through triggers?


texts: Data modeling for employee has multiple managers

texts: I've referred this link for modelling the table for employee-manager relationship. However, in my use case, an employer can have multiple managers. I am wondering what would be the best solution for this. Any suggestions for modelling this use case?


texts: Is there way to get transaction commit timestamp in Postgres?

texts:
I have data-pulling functionality that once in 5 seconds grabs all the data from Postgres table basing on modified_timestamp column. It works the following way:

SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp
_some_persisted_timestamp = CURRENT_TIMESTAMP
Processing data received from step 1
Sleep for 5s
Go to step 1

Where modified_timestamp updated with trigger (after any row update modified_timestamp becomes CURRENT_TIMESTAMP).
It worked fine until I noticed that CURRENT_TIMESTAMP in Postgres is transaction start timestamp in fact and some of the updates are lost. Why are they lost? That's quite simple - at the moment when I execute query SELECT * FROM my_table WHERE modified_timestamp > _some_persisted_timestamp some of the changes have already occurred, but modified_timestamp is before updated _some_persisted_timestamp because transaction is in progress still.

This problem could be easily solved by assigning in step 2 timestamp when update becomes visible for other transactions (transaction commit timestamp in other words) instead of CURRENT_TIMESTAMP or clock_timestamp().

I read documentation, but have found nothing related to transaction commit timestamp. Could you kindly suggest smth?

Btw, I'm aware of logical decoding and I know that this mechanism suits better for my needs in theory, but there are certain practical problems not allowing me to use it.


texts: We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with  innodb_file_per_table or leave just as one .ibd? I read some articles say do not go with it as you need more disk access when there joins to be performed? We will have join between this table and others for reporting generation purposes.


texts: SSDT Drop and Recreate Tables when nothing has changed

texts:
We have a Visual Studio Database Project consisting of about 129 Tables. It is the primary database for our Internal Web Based CRM/Call Centre product, which is still under active development.

We use the SSDT Publish from within VS to deploy to instances as changes are made. We develop locally via SQL Express (2016), also have a LAB environment for performance and load tests running SQL 2014, a UAT environment running 2012 and finally a deploy to production which is running SQL 2016.

All environments (Except Production) the script generated on publish is very good, only does the changes. The production script does a massive amount more work. Seems to drop and recreate a lot more tables, that I know have not changed (37 tables last deploy). Some of these tables have rows in the millions, and the whole publish is taking upwards of 25mins.

If I repeat the publish to production, it again drops and recreates 37 tables. The production DB does have replication which I have to disable before deployments (unsure if that's a factor).

I don't understand what the Production publish always wants to drop and recreate tables even though nothing has changed. I'm hoping to get some advice as to where to look to establish why SSDT thinks these need to be re-created.

using Visual Studio Professional 2017 V 15.5.5 and SSDT 15.1


texts: Moving DataFile to Another Location ORACLE

texts:
I'm having some problems moving DATAFILE from one location to another due to the shortage of storage space. I've managed to change the path file of control file via command(linux). Now I would like to change the path file of the datafile and logs to another since I've already manual moved the folder of the database.
 SQL> startup;
 ORACLE instance started.

 Total System Global Area 6710886400 bytes
 Fixed Size                  2939560 bytes
 Variable Size            3506439512 bytes
 Database Buffers         3187671040 bytes
 Redo Buffers               13836288 bytes
 Database mounted.
 ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
 ORA-01110: data file 1: '/app/oracle/oradata/ENOVIA/system01.dbf'

Apparently, the data files have already been moved to another location, whereas the path of the files has yet to be changed.
How can I check for the current location of Database in SQL and which command is needed to be used to alter the path file?
The new location is home/oradata/ENOVIA.


texts:
I just wanted to ask a clarification about something on the MSDN from the "Optimizing MERGE Statement Performance" page.

I am working with a data warehouse that takes in records from many different databases and stores the data. All of the tables in my warehouse database basically follow this same pattern:

CREATE TABLE Foo (
    database_guid UNIQUEIDENTIFIER
    ,FooPk BIGINT
    ,Bar NVARCHAR(20)
    ,Qix NCHAR(10)
    ,CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED (
        database_guid ASC
        ,FooPk ASC
        )
    )
GO

CREATE PROCEDURE [iv].[LoadSomeTable]
    @databaseGUID UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON

    MERGE Foo
    USING #FooStaging AS Source
    ON Foo.FooPk = Source.FooPk AND Foo.database_guid = @databaseGUID
    WHEN MATCHED THEN
        UPDATE SET Bar = Source.Bar
                  ,Qix = Source.Qix
    WHEN NOT MATCHED THEN
        INSERT (database_guid, FooPk, Bar, Qix)
            VALUES (@databaseGUID, FooPk, Bar, Qix);
END
GO

CREATE TABLE #FooStaging (
    FooPk BIGINT
    ,Bar NVARCHAR(20)
    ,Qix NCHAR(10)
    )

--Data gets loaded in to #FooStaging from a C# call to SqlBulkCopy then calls iv.LoadSomeTable

The thing I am now concerned about is I just read this statement from that MSDN page

  Specify only search conditions in the ON <merge_search_condition>
  clause that determine the criteria for matching data in the source and
  target tables. That is, specify only columns from the target table
  that are compared to the corresponding columns of the source table. Do
  not include comparisons to other values such as a constant.

After reading that I am thinking I did my query wrong and my merge statement should be

MERGE Foo
USING #FooStaging AS Source
ON Foo.FooPk = Source.FooPk
WHEN MATCHED AND Foo.database_guid = @databaseGUID THEN
    UPDATE SET Bar = Source.Bar
              ,Qix = Source.Qix
WHEN NOT MATCHED THEN
    INSERT (database_guid, FooPk, Bar, Qix)
        VALUES (@databaseGUID, FooPk, Bar, Qix);

But that does not "feel" right to me because the database_guid field is part of the primary key so shouldn't it be included with the on? If I had it in the WHEN MATCHED and I upload one database with a FooPk of 1 then I upload a second database with a FooPk and a different @databaseGUID I am not sure if NOT MATCHED will fire or not (Just tested, it does not).

Which way is the correct way to use MERGE?


texts: Defragmentation of SQL Server 2008 indexes

texts:
I am having issues defragmenting indexes. I keep using the various defragmentation methods for defragmenting a SQL Server index such as DBCC INDEXDEFRAG and DBCC DBReIndex, but none seem to be changing the fragmentation.

I have even tried the method suggested here in this link
http://msdn.microsoft.com/en-us/library/ms177571.aspx
but with no change.


texts:
This query was already developed and produces line by line totals, then is copy/pasted into Excel to get Aggregate Totals.  I am sure this syntax can be altered to produce Aggregate Totals - I just do not have the skillset to achieve such.

Right now the syntax produces

Big Boy Records 200  400  0.00
Big Boy Records 400  200 10.00
Big Boy Records 300  100  4.00

And copied into Excel to get the below

Big Boy Records 900  700 14.00

What change needs to be made in this query to get the aggregate totals like above?

SELECT vendor.venID,vendor.venName,((
select  coalesce(sum(rl.amountToInvoice),0)
from    AR r join ARItems rl on r.arID=rl.AR
       join scottCalvin sc on rl.scottCalvin=sc.asID
where   rl.AR=AR.arID and sc.arRPT=true
   )) AS TotalAmountToInvoice,((
select  coalesce(sum(ja.actualcost),0)
from    workDetails ja
where   ja.workID=AR.workID and ja.
       vendorID=AR.vendorTran
   )) AS costamt,((
select  coalesce(sum(ja.markup),0)
from    workDetails ja
where   ja.workID=AR.workID and ja.
       vendorID=AR.vendorTran
   )) AS fullon
FROM "public".AR AR LEFT OUTER JOIN "public".vendor
       vendor ON AR.venID=vendor.venID
WHERE ((NOT ( (AR.vendorTran = 'Pending') ))) AND ((NOT (
       (AR.vendorTran = 'Basis') ))) AND ((AR.payDay
       BETWEEN '01/01/2017' AND '02/01/2017'
       AND vendor.venName = 'Big Boy Records'))
ORDER BY vendor.venID DESC


texts: Stored Procedure result is not showing in Datagrid pane in Toad for Oracle

texts:
I have created a procedure in Toad for Oracle 12. When I execute the procedure it does not show the dataset in datagrid pane, while the procedure is being executed successfully. What I am doing wrong?

CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(OpNo CTS_OPERATIONS.OPERATIONNUM%Type ,BName CTS_OPERATIONS.BLENDNAME%Type)
AS
p_recordset Types.cursor_type;
BEGIN
    OPEN p_recordset FOR

    SELECT FILLED_MCB.BARCODE,TILTED_MCB.TILTINGTIME,MAKERS.FEEDERNUM,MAKERS.MAKERNUM
    FROM FILLED_MCB INNER JOIN TILTED_MCB ON FILLED_MCB.FM_ID = TILTED_MCB.FM_ID
    INNER JOIN MAKERS ON MAKERS.M_ID = TILTED_MCB.M_ID
    INNER JOIN CTS_OPERATIONS ON CTS_OPERATIONS.CTS_ID = FILLED_MCB.CTS_ID

    --WHERE CTS_OPERATIONS.OPERATIONNUM=OpNo AND CTS_OPERATIONS.BLENDNAME=BName;
    WHERE CTS_OPERATIONS.OPERATIONNUM=3004 AND CTS_OPERATIONS.BLENDNAME='PG';
END;

---- Execute Procedure ---------------

exec FEEDERS_TO_MAKERS_MCB(3004,'PG');


texts:
I've googled and I can't find any information about it. I'm wondering why does SQL Server uses .bak extensions by default.

I know that there are a lot of different software which use .bak extension, not only SQL Server.

And another question, is there any other file formats that can be used for the backup file?


texts:
I have four tables - A, B, C and D.

Table A holds inventory movement records, table B holds sales records on a per item basis, table C holds non-sales records on a per item basis and table C holds the type of non-sales movement.

For each record in A there's either a line in B or a line in C. So to get a single row of the movements for each item I do the following:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C on C.Id = A.IdIntoC

So far so good. However, as I need to know what type of movement it was in C as I want to return the following:

ItemId, Date, Sales, Goods In, Returns to Supplier

i.e. have separate columns for good in and goods returned to a supplier.

The type (goods in or return to supplier) is stored in D and there is a many to one relationship between C and D as D is the "document" that groups the items in C.

If I do:

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 on C1.Id = A.IdIntoC
    left join D on D.Id = C1.IdIntoD and D.Type = 1
left join C as C2 on C2.Id = A.IdIntoC
    left join D on D.Id = C2.IdIntoD and D.Type = 2

I just get duplicate rows as the two joins from A to C are effectively the same.

What I want to do is this (non-legal syntax):

select column1, column2, ... from A
left join B on B.Id = A.IdIntoB
left join C as C1 on C1.Id = A.IdIntoC and D.Type = 1
    left join D on D.Id = C1.IdIntoD
left join C as C2 on C2.Id = A.IdIntoC and D.Type = 2
    left join D on D.Id = C2.IdIntoD

Basically I want separate out the joins between A and C based on a value in D. I know that I have to use a left join between C and D each time otherwise I'll get no data, but logically that's what I feel I need to do.

How do I do this?


texts: SSMS generated script failing to execute

texts:
So I am in a situation where I need to import a small DB (under 1GB) from a newer version of SQL Server (I am restoring to a 2014 SQL Server from a 2016 SQL server), without a direct connection between the two. This DB is the backend for a CMS and so stores not only conventional data but html content as well.

The solution I found (I followed this guide.)was to use "Generate Scripts" in SSMS (schema+data) to script out the database, and then execute the script to recreate it on the destination server. The resulting script is about 900MB total.

The first issue I ran into was that SSMS would not execute the script as it was apparently too large for it. Sqlcmd was able to start executing the script, but then failed 10000+ lines into it. When I cut out the script at the failed point, it continued to run no problem where it left off, so I'm guessing it was out-of-memory related or something? The specific error I received was a

"Sqlcmd Error: Syntax Error at line 14769 near command ' " ' in file <path\script.sql>".

The actual line at 14769 is:

INSERT [dbo].[sf_control_properties] (
    [val],
    [validation],
    [prnt_prop_id],
    [ordinal],
    [nme],
    [last_modified],
    [language],
    [id],
    [flags],
    [description_],
    [control_id],
    [caption_],
    [app_name],
    [voa_version]
    )
VALUES (
    N'RulesGroupBackend',
    NULL,
    NULL,
    1,
    N'ControlDefinitionName',
    CAST(N'2018-03-23T12:35:50.977' AS DateTime),
    NULL, N'7cf975d2-f37d-4716-a634-0d6424921f38',
    1,
    NULL,
    N'27c93f05-386b-4fd2-8aae-d6664ee7e57a',
    NULL,
    N'Title/',
    1)

The line before (multiple line statement 14766-14768) is:

INSERT [dbo].[sf_control_properties] (
    [val],
    [validation],
    [prnt_prop_id],
    [ordinal],
    [nme],
    [last_modified],
    [language],
    [id],
    [flags],
    [description_],
    [control_id],
    [caption_],
    [app_name],
    [voa_version]
    )
VALUES (
    N'$(document).ready(function(){
       $("#content > div > section:nth-child(2) > div > div > div > div > div:nth-child(1) > h2").text("English Support");
});',
    NULL,
    N'5d056cac-51db-492c-b601-e88c82a8bc72',
    1,
    N'InlineCode',
     CAST(N'2018-11-27T20:33:59.060' AS DateTime),
     NULL,
     N'1f8ac206-d771-42b2-9991-0ca66bceff19',
     1,
     NULL,
     NULL,
     NULL,
     N'Title/',
     1
     )

I don't know what the error is referring to when it says syntax error on line 14769 near command ' " '

So in summary I'm wondering A) What may be going wrong with the script at these lines B) Whether this is an issue with sqlcmd (since the script was also failing in SSMS). To me the script appears fine, and after all it was automatically generated by SSMS so I'm confused what the problem could be. Thanks!


texts: Incorrect syntax near the keyword 'Exec'

texts:
Can you please tell me what is wrong in this query? I am trying to write a SQL query to create a table dynamically

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =+ 'Create Table' + GETDATE() + '_' + 'Table' + '' + '(' + 'Column1' + ' ' + 'Nvarchar(50)' + 'Null' + ')' +

Exec @SQLString ;


texts:
In the book Designing Data-Intensive Applications, the author suggests that replication lag can lead to an anomaly were causality is violated. In the example that the author uses, the system is both replicated and partitioned. I was wonder if the violation of causality - what the author terms "consistent prefix reads" - is a problem in non-partitioned systems as well.
P.S. I want to expand on this. In a single leader replicated system, the single leader acts as a synchronization point. All follower replicas will see the writes in the correct order, so I do not see how a third-party observer would see the writes in the wrong order. I am starting to arrive at the conclusion that causality can be violated in a multi-leader replicate system because the lag between L2 and F1 can be larger than L1 and F1.


texts: I've seen it mentioned a few times that you can configure an event session to take an action when an event fires. Does anyone have any information on how to do this?  I know that you can use the service broker to take an action on an event, but I don't consider that to be part of an event session.


texts: Transform a number in symbols in a MySql SELECT

texts:
I want to know if is possible to transform a numeric return (INT column) in a sequence of symbols direct in a MySQL SELECT.

My table is like below:

| ID        | category |
|-----------|----------|
| product_1 | 5        |
| product_2 | 4        |
| product_3 | 3        |

I want a SQL SELECT like SELECT ID, some_mysql_fancy_func(category,'*') AS symbol_category FROM MyTable that could returns the following:

| ID        | symbol_category |
|-----------|-----------------|
| product_1 | *****           |
| product_2 | ****            |
| product_3 | ***             |

Is it possible?

I can do it programmatically, but this is not my intention, I want some built-in (or even custom) function that can achieve this result direct in the SELECT statement.


texts: Two mysql db's load the same data at two different rates

texts:
Any idea why this might be happening?  We have the same mysql (*.sql file) data being imported into two virtually identical mysql databases every day, but one consistently takes 30% longer to load than the other.

We're using the RDS system on Amazon's AWS.  We have a "staging" db and a "production" db.  The staging db is the one that loads slowly.  We are loading the data through a mysql client that sits on an EC2 Container.  The staging container is virtually identical to the production container.  The staging db is virtually identical to the production db. The staging RDS instance is virtually identical to the production RDS instance.

In the past, the staging db loaded at the same speed as the production db.  At one point, we did some experiments with staging on a different RDS instance.  When those didn't work out, we went back to our previous staging RDS instance and began getting consistently slower import times.

The sql file that gets loaded is a large one and takes almost 30 minutes to load on the production db and 40 minutes on staging.

I can see in the Monitoring charts that the write operations per second on production are a lot higher ... spiking to over 600 vs. only spiking to 240.

EDIT (more info)

These are where they are exactly the same ...

Engine: MySQL 5.1.73b
Option Group: default: mysql-5-1
Parameter Group: barracuda-mysql51
Availability Zone: us-east-1b
Instance class: db.m1.small
Storage Type: General Purpose (SSD)
IOPS: disabled
Storage: 20 GB
Multi AZ: No

They are in different VPC's and different security groups.  Also, the production EC2 container that does the upload is in us-east-1b but the staging one is in us-east-1a.  I'll have to check if that could make a difference.


texts: I would lIke to have Mysql group by days interval for example group by every 20 days from current day, like 1 - 20, 21 - 40, 41 - 60 and son on up to lets 120 days. The user can choose the days interval and up to how many days


texts: Grouping based on the status, then count its product and the total based on its category ID


texts: Aggregate query on multiple values of same field

texts:
I am using SQL-Server 2012, I have a table of credit card transactions as follows:

CardScheme  RespCode
=========================
Visa        Declined
MasterCard  Approved
MasterCard  Approved
MasterCard  Declined
Visa        Declined
MasterCard  Approved
MasterCard  Declined
Visa        Approved
Visa        Approved
MasterCard  Approved

I need to write a query to get the following results

CardScheme  Declined  Approved
==============================
Visa        2         2
MasterCard  2         4

Please help


texts: how to generate the root CA.pem in mongodb for configuring the ssl certificates?

texts:
I am trying to configure the SSL certificates in the MongoDB community edition.
The configuration of my mongod.conf
net:
  port: 27017
  bindIp: 0.0.0.0  # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6
 addresses or, alternatively, use the net.bindIpAll setting.
  ssl:
    mode: requireSSL
    PEMKeyFile: /etc/ssl/mongodb.pem
    CAFile: /etc/ssl/ca_bundle.crt
    allowConnectionsWithoutCertificates: false
    allowInvalidHostnames: false
    disabledProtocols: TLS1_0,TLS1_1

Here what I am doing is converting certificate.crt and private.key as mongodb.pem and passing the ca_bundle.crt as CAFile in mongod.conf
I am trying to connect with the server using the command
mongo --ssl --sslPEMKeyFile /etc/ssl/mongodb.pem --sslCAFile /etc/ssl/ca_bundle.crt --host myapptest.tk

Getting the error like

MongoDB shell version v4.0.8
connecting to: mongodb://myapptest.tk:27017/?gssapiServiceName=mongodb
2019-04-04T19:57:40.401+0000 E NETWORK [js] SSL peer certificate validation failed: unable to get local issuer certificate
2019-04-04T19:57:40.402+0000 E QUERY [js] Error: couldn't connect to server myapptest.tk:27017, connection attempt failed: SSLHandshakeFailed: SSL peer certificate validation failed: unable to get local issuer certificate :
connect@src/mongo/shell/mongo.js:343:13
@(connect):2:6
exception: connect failed

I want to know, how to create the root_CA.pem file and pass it as --sslCAFile?
where can found the ca.pem file in MongoDB?
Could anybody suggest me how to configure the SSL certificates in MongoDB?


texts:
I have installed 2 instances of sql server plus SSIS on the following server.

Note the amount of RAM is nearly 384 GB

And this is the max and min memory settings that I have applied to my 2 instances.
I assume that both instances will use equal amount of resources, if that would really be the case, 184,320 MB which is 180 GB be a good starting number to set up my memory?

How much memory would I allocate to SSIS?


texts: How to FETCH from a cursor into a table variable?

texts:
I have a cursor defined as:

DECLARE idCursor CURSOR
    FOR SELECT DISTINCT [id], [data]
        FROM #tempTable

And I have a variable defined as:

DECLARE @currentId TABLE (
                            [id] int,
                            [data] char(1)
                         );

But when I try to fetch the next result into @currentId

FETCH NEXT FROM idCursor INTO @currentId

I get this error:

Must declare the scalar variable "@currentId".

How can I get the result of a cursor fetch in a table? I understand that I can declare two variables and store the results individually. But for a table with many columns that would be time consuming.


texts: How to grant all privileges on all tables in a schema to a user in IBM DB2?

texts:
At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move command.

I had exported myschema schema and all of tables from dbemp database in remote IBM DB2 AIX using this command:

db2move dbemp export -sn myschema

When I run that command I was login as user named dbuser1.

It generates bunch of files in the current folder. I grab the files into my local machine (windows) which has IBM DB2 LUW installed.

Now in my local machine's IBM DB2, I dropped the existing schema myschema first by using IBM Data Studio (I also had a local dbemp database too).

Then I imported the files using this command:

db2move dbemp import

It successfully import the schema and tables into local dbemp database.

When I did above actions, I was login as user winuser1.

I have a local user named dbuser1 too in my local machine (windows). In IBM Data Studio, I created a connection profile that connect to local dbemp using dbuser1 user and I can browse myschema schema and the tables, but I can't browse the table's data (I got privilege error). User winuser1 can browse table's data, but for development purpose, I have to connect to the database using user dbuser1.

So I found out that I can grant privilege to a user on the table like this:

GRANT ALL ON myschema.table1 TO USER dbuser1

The problem is I have 100 tables, I don't want to type that lines for each table. And unfortunately, there is no wildcard solution like this too:

GRANT ALL ON myschema.* TO USER dbuser1 -- this doesn't work

So the question is how to grant all privileges on all tables in a schema to a user? Or is there alternative better solution? maybe by copying all privileges from winuser1 to dbuser1?


texts:
I want to fetch the oracle user login detail for the month February.
I tried the below query.

select distinct
    os_username,username,userhost,terminal,timestamp,action_name,logoff_time
from  dba_audit_session
where timestamp between '01-FEB-2017'
                    and '28-FEB-2017'
order by timestamp;

but I need the IP address as well.
Can anyone help me! how to fetch the user IP address along each user session?

I am stuck here.

I have check dba_audit_trail table as well but didn't help.

I need the below columns:

username,os_user,logon_date,logoff_date,IPADDR, terminal, sessionid,hostname.


texts: What's the overhead of updating all columns, even the ones that haven't changed

texts:
When it comes to updating a row, many ORM tools issue an UPDATE statement that sets every column associated to that particular entity.

The advantage is that you can easily batch the update statements since the UPDATE statement is the same no matter what entity attribute you change. More, you can even use server-side and client-side statement caching as well.

So, if I load an entity and only set a single property:

Post post = entityManager.find(Post.class, 1L);
post.setScore(12);

All columns are going to be changed:

UPDATE post
SET    score = 12,
       title = 'High-Performance Java Persistence'
WHERE  id = 1

Now, assuming that we have an index on the title property as well, shouldn't the DB realize that the value hasn't changed anyway?

In this article, Markus Winand says:

  The update on all columns shows the same pattern we have already
  observed in the previous sections: the response time grows with each
  additional index.

I wonder why is this overhead since the database loads the associated data page from disk into memory and so it can figure out whether a column value needs to be changed or not.

Even for indexes, it does not to re-balance anything since the index values don't change for the columns that haven't changed, yet they were included in the UPDATE.

Is it that the B+ Tree indexes associated to the redundant unchanged columns need to be navigated as well, only for the database to realize that the leaf value is still the same?

Of course, some ORM tools allow you to UPDATE just the changed properties:

UPDATE post
SET    score = 12,
WHERE  id = 1

But this type of UPDATE might not always benefit from batch updates or statement caching when different properties are changed for different rows.


texts:
I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like

Insert into MYTABLE (DATE_INSERT)
VALUE (MAX(DATE_DEFAULT, DATE_INSERT_VALUE))

where DATE_DEFAULT and DATE_INSERT_VALUE are local variables of type DATE.  The MAX function, however, is an aggregate function so I can't pass two arguments to it.


texts: MySQL - How to check for a value in all columns

texts:
I am curious, is there a good way to search all columns for a given value? For my purposes, it doesn't need to be at all fast, it's just a 1-off kinda thing, and I don't really want to have to type out every field name. That's precisely what I'll be doing for now, but I think surely there's a better way.

I would like to turn this:

SELECT * FROM table WHERE col1 = 'val' OR col2 = 'val' OR col3 = 'val';

into this:

SELECT * FROM table WHERE * = 'val'

...or, even better (though I seriously doubt it...)

SELECT * FROM table WHERE * like '%val%'

I found this, which seems kinda-not-really close, but I'm not finding anything closer:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

Difference being, that searches a selection of columns for the specified values, whereas I'm trying to search ALL columns for a single value.

It's not important though, like I said more than anything I'm just curious


texts: MySQL - Add index to low cardinality text column?

texts: I'm working on project where an enum column is being converted into a text column (I cannot change this). The cardinality of the column is low (7 unique values). Would I gain a performance increase by adding a 10-15 char index, or is the cardinality low enough where the index would result in diminishing returns?


texts:
Consider a table that contains around 9,000,000 rows. Each row has a int-based "ID" IDENT column whose value automatically increments on each insert. The table undergoes a lot of "churn," that is to say, a good number of the rows are deleted and added to this table in the course of a day. Although the total number of rows isn't growing at a particularly high rate, the value of the numeric ID assigned to individual rows could conceivably hit the maximum ID value (2^32 ?) at some point over the next couple of years.

Is there some SQL code that can be run to reset all IDs in all rows of the table ? Ideally, if the table contains 'x' number of rows, IDs between 1 and 'x' would be assigned. I'd then re-seed the identity generator to 'x+1'.

The ID column is not a foreign key for any other table.

The process would only need to be performed once every few years so I wouldn't mind running the query in a single-user environment if necessary.

I can't change the type of the ID column to long or varchar to solve the problem. Apparently there is legacy code that depends on the value being a 32bit integer.

I am using SQL Server 2008.

Alright, then.


texts: SQL Server 2012 High Availability on VMware virtualization

texts:
We're going to host a large SQL Server 2012 under a VMware virtualization and we need it to be highly available.

These are the three options I found supported, but couldn't find a lot of info and comparison on who's better and why (and mostly - what's the disadvantages):

VMware HA - pro: cheap and easy to use and configure, just let the VM team deal with it. con: no HA for system upgrades, e.g. SQL upgrades, Windows services-packs\KBs etc.
SQL Server Failover Cluster (FCS) - pro: well known, supported, saves space (compared to AlwaysOn Availability Group), HA for system upgrades. con: I haven't heard of successful clusters over VM, or at least good ones. Heard it's a mess and to "stay away from it". A bit harder to admin, some problems may take days to figure out (no-one with a lot of knowledge).
SQL Server AlwaysOn Availability Group - pro: HA for everything! system, DB etc. con: New, added administering and hard to confiugre, new problems to deal with, double the space(!), "no strings attached" (to other servers).

That's pretty much it.

I would appreciate any info you may have about this, and would appreciate even more recommendations (hopefully based on test cases).

~Thanks.


texts: What size of data can PostgreSQL handle?

texts:
We wanted to use OpenERP along with PostgreSQL database. OpenERP has a functionality to upload documents and stored in to database in binary format. Our worry is if user decides to upload to huge number of files to database.

Can PostgreSQL take care of this situation itself, or do we need to do anything specifically to scale up the database? I worry about both space as well as performance.


texts: Is SQL Server 2014 capable of having Availability Groups set up across multiple domains?  If not, is there a third party tool you can recommend to mirror a 13TB (and growing) database across domains?


texts: In what range can you expect a cloud based Sql Server data reading in mb/s?

texts:
Say everything is optimized, queries are simple, indexes are good, etc.
Should a SQL (Azure) database where you spend about 150 to 300 euros per month easily handle data reading with about 250-500 mb per second on a table of ~100 million rows or is that getting close to the limits and should I look at other solutions?
I understand this is a very broad and general question, but I'm looking for something like very rough estimates. The problem is that I have a very complicated legacy project and I am clearly hitting some limits.
I tried (after consulting Azure experts) various other data solutions in the past to only hit other bottlenecks after I migrated the project and had the real production traffic coming along again.
The site has extreme peak traffic in a very short period of time. So it will be on 5% of the database (eDTU) consumption the whole week with about one or two times a day extreme traffic for half an hour. About +50.000 very active concurrent users with a lot of dynamic data being written and mainly requested. The data can't be stale.
So I'm just looking for a very rough estimated guess so I know if I need to move into a really different direction like i.e. memory-cached microservices.
If 500 mb/second should be no problem then I will try to explore trying to layout my data differently or other SQL based solutions.


texts: Security implications of running SSRS and an OLTP database on the same server

texts:
I am in the process of building a new environment for my company's Customer Relationship Management (CRM) system. This system is a commercially available product which uses SQL Server as its back end database. The CRM system also uses SQL Server Reporting Services (SSRS) to query the CRM database to provide reports. The database which is queried by SSRS is the live OLTP database - there is no separate reporting database.

There is some debate in my team on whether it is acceptable from a security point of view to run SQL Server Reporting Services on the same server which hosts the CRM database, or whether it must be run on a separate server.

The specific security concern is that running SSRS on the server increases the attack surface, which increases the likelihood that the server will be compromised by a bad guy. If the SSRS server were compromised it would be better if it weren't also hosting the CRM database with all its valuable data.

The counter argument is that if a bad guy wants the data, they will attack the CRM database server directly and won't be fooled by the presence of an SSRS server nearby. Also, all of this is on the internal network, so should be at relatively low risk of attack in the first place.

Is running SSRS and an OLTP database on the same server contra-indicated for security reasons?


texts: How do I tactfully bring up database shortcomings?

texts:
A little background:

I recently inherited responsibility for support and development of a database application after the original developers left. Looking through the application, the database appears to have several... issues.

Weekly shrink and re-index plan
Database account used for web application access has db_owner role
No foreign keys
All tables use GUID column as primary key
Queries built in C# are not parameterized

How can I bring these issues up with management?

Normally, this should be no trouble, but I have a problem: I have less than six months of SQL experience, whereas the managers and former developers were working on this project for several years.


texts: Is there any disadvantages if i shift from myisam to innodb in mysql 5.7?

texts: We are using myisam primarily for full text on mysql 5.5 ,However as 5.6+ innodb supports full text i want to know any performance or other bottleneck ,Please suggest ,we have huge data set in millions and currently tables are mix of myisam and innodb.


texts: Use LDAP for name look up with Oracle Instant Client

texts:
Partly as a result of having problems with conflicting versions or bitness with the full installation of Oracle client, my development team (of which I'm a member) has been moving towards using Oracle Instant Client and avoiding the installation on our development machines for most new projects. This has worked out pretty well, even to the point that we deploy to production with this setup. Since we're mostly a .NET shop, we're using the native client (with .NET wrappers), rather than the Java one.

Now a client is requiring that we register the database service with an LDAP server and use the LDAP server for name look up. They have provided sqlnet.ora and ldap.ora files (and an accompanying encryption config file) that appear to be correct. In particular, the sqlnet.ora file does specify LDAP as a possible source for looking up net service names:

names.directory_path=(tnsnames,ldap)

I am confident that the machine I'm working with can use LDAP to access the service since I was able to do so using SQL Developer (which allowed me to input the LDAP information directly). Assuming they are correct, how can I get Instant Client to recognize this configuration and use LDAP for the name look up?

What I've tried so far

I have tried setting the TNS_ADMIN environment variable to the directory that contains the provided files, and while this works fine for making Instant Client recognize a tnsnames.ora file, it doesn't seem to be enough to get it to start looking at LDAP. Here are the results (with paths, usernames, and passwords changed):

(The following commands happen to be Windows Command Prompt, but I imagine something similar is doable on Linux. The sqlplus seen here is the Instant version as well, with accompanying Instant Client binaries in the same directory.)

C:\TEMP>SET TNS_ADMIN=C:\path\to\sqlnet\ldap\and\tnsnames\ora\files

C:\TEMP>echo %TNS_ADMIN%
C:\path\to\sqlnet\ldap\and\tnsnames\ora\files

C:\TEMP>sqlplus.exe USERNAME/PASSWORD@LOCALTNSNAME

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 7 10:22:25 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produ
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

C:\TEMP>sqlplus.exe USERNAME/PASSWORD@LDAPTNSNAME

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 7 10:24:21 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Side note

Are there tags for TNS, LDAP, or Instant Client (or even just normal Oracle Client) that could be added to this question? I couldn't locate any.


texts: Mongos down when I shutdown one of three servers

texts:
I deploy 3 server of mongodb, layout is

node-01:

mongos
config server 1
replication server 1

node-02:

mongos
config server 2
replication server 2

node-03:

config server 3
arbiter

After deployment I found that if I shutdown node-01 or node-02, mongos fail immediately.

I think it's because replication server down will require metadata change, but I didn't find information from google.

Here is a part of log from mongos:

> 2014-05-21T18:57:03.888+0800 [Balancer] reconnect 10.162.54.97:28002 (10.162.54.97) failed failed couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:08.892+0800 [Balancer] scoped connection to 10.161.236.222:28001,10.162.54.97:28002,10.132.42.79:28003 not being returned to the pool
> 2014-05-21T18:57:08.892+0800 [Balancer] caught exception while doing balance: error checking clock skew of cluster 10.161.236.222:28001,10.162.54.97:28002,10.132.42.79:28003 :: caused by :: 13647 could not get status from server 10.162.54.97:28002 in cluster 10.162.54.97:28002 to check time :: caused by :: 11002 socket exception [CONNECT_ERROR] server [10.162.54.97:28002] connection pool error: couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:10.176+0800 [ReplicaSetMonitorWatcher] warning: Failed to connect to 10.162.54.97:27017, reason: errno:115 Operation now in progress
> 2014-05-21T18:57:12.693+0800 warning:  couldn't check dbhash on config server 10.162.54.97:28002 :: caused by :: 11002 socket exception [CONNECT_ERROR] server [10.162.54.97:28002] connection pool error: couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:19.895+0800 [Balancer] SyncClusterConnection connecting to [10.161.236.222:28001]
> 2014-05-21T18:57:19.895+0800 [Balancer] SyncClusterConnection connecting to [10.162.54.97:28002]
> 2014-05-21T18:57:24.896+0800 [Balancer] SyncClusterConnection connect fail to: 10.162.54.97:28002 errmsg: couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed
> 2014-05-21T18:57:24.896+0800 [Balancer] SyncClusterConnection connecting to [10.132.42.79:28003]
> 2014-05-21T18:57:24.899+0800 [Balancer] trying reconnect to 10.162.54.97:28002 (10.162.54.97) failed
> 2014-05-21T18:57:25.176+0800 [ReplicaSetMonitorWatcher] warning: Failed to connect to 10.162.54.97:27017, reason: errno:115 Operation now in progress
> 2014-05-21T18:57:29.899+0800 [Balancer] reconnect 10.162.54.97:28002 (10.162.54.97) failed failed couldn't connect to server 10.162.54.97:28002 (10.162.54.97), connection attempt failed


texts: Remove column from transactional replication

texts:
I have a situation where in I need to remove 1 column from  transactional replication without actually dropping the column from table. Is it possible to remove it? When I try to do it from GUI it directly gives option to reinitialize the replication which I don't want to do it.

As a workaround what I am planning to do is remove article from replication and then re-add it with required columns and run snapshot for only 1 article.

The environment is SQL Server 2012. Table is of 100GB.

The question is Whether it is possible to drop the column from replication only(i.e. without dropping it from table) and that too without affecting already running replication? For one time request it is ok to go with the workaround I mentioned. But I need to do this quite frequently and dont want to go with the workaround as resnapshotting 100GB table takes long in our environment. Please let me know if you need any further details.


texts: Strategies to get more performance in a specific scenario

texts:
Introduction
Hi Guys, my name's Joao, i'm currently working as backend and that's my first post.
I'll appreciate any help!
My team are working on a new feature. During the process of development we face a doubt about database performance, and so far we have some divided opinions... so here i'm rs
Tabble structure
For the example, a many-to-many relationship:

The scenario
Considering tables above, our API must serve an POST endpoint that, receive a Domain ID as a path variable and a json containing a list of Users Id, something like that:
Path : /domain/1

Body Json :
    {
      "users":[
        "1",
        "2"
      ]
    }

With that list we must persist on the many-to-many domain_users that relationship between users and domains. Resume, in one endpoint we must manager the many-to-many table registers.
The question
We made this in two different ways, i'm here to ask your opinion what's the more efficiently, performatic etc.. by the database side, thinking bout I/O
Strategie A (Clean and Insert)
First we delete ALL the registers in domain_users where the field domain_id is equals the Domain ID of the request, and then we insert in domain_users all users of the request list for that domain. Considering the above Json example, in this strategie we'll perform one delete and one insert with two registers on the table domain_users.
Strategie B (Logical relationship)
First we compare the list of users that came with the request and the list of users that are already linked for that Domain ID, by a select on the domain_users where the field domain_id is equals the Domain ID of the request, so we execute these conditionals:
1 - There's a user that came in the Json Request list and aren't present in database list? Insert that!
2 - There's a user that are present in the database list and aren't present in the Json Request ? Delete that!
3 - There's a user that are present in both list (database and Json Request) ? Do nothing.
In this strategie, the number of operations performed on the database depends of registers already present on the domain_users many-to-many table.
 Which of the two strategies should we follow, thinking about better performance of database?
Obs : Our backend are in Java and the database we are working now is a PostgreSQL version 12


texts:
I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community.

Please consider the diagram:

I would like to add a unique constraint such that only one User can have only one Role per Event (Which is stored in the UserEventRoles table). Not sure how this can be solved? Perhaps with different design? Also the goal is not have a scenario where keys can be "out of sync", such as users role would point to an event where that role does not exist.

EventRoles table describes which Roles are valid at each Event.

I am sure this problem has come up before with other database designs and would like know what the best approach may be.

Also here is a link to sql fiddle to play around schema and some sample data.

sqlFiddle


texts: Table with 4 million records - different size and performance in development and production

texts:
At my company we use SQL Express 2008 R2, which supports databases up to 10GB.

We have 2 databases with almost the same number of rows in all tables.
But, one of them is 4.5 GB in size (which is in the development department) and the other (the one in production) is 9.87GB.

The database under development was auto generated by inserting rows in bulk for testing purposes. While the database in production, slow almost 1 year to generate that amount of data.

One table in particular, has 4,700,000 rows, and make the following query:

SELECT colum1,column2 FROM table WHERE column1 = 2

Takes about 2 minutes to respond on the production database and 20 seconds in the development database.

I did a shrink in production database and it took about 20 minutes to complete, but after that, the size of the database was reduced to 7 GB.
I tried to rebuild the indexes, with right click, but it gives timeout and fails to finish the reconstruction.

My question is:
Why would 2 databases with the same amount of records be so different in size and what can I do to improve performance on production database?

Thanks.-


texts: Retrieving database dump after copying from remote server

texts:
I used command

pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser

to copy database on remote server to my local machine.

Can I somehow retreive the dump obtained after the first part of the command, i.e.

pg_dump -C -h remotehost -U remoteuser db_name

is executed? Can it be stored in some temporary location?


texts:
I need your help to implement MySQL table versioning.
Currently I run python script which is populating DB. As always there are some changes over time in the data send to the DB. Script and DB needs some adjustments.  Things were easy, to keep script and DB updated, for one instance.
Now things are more complicated as there are different versions of script and DB layout in production. I face simply versioning issues.
I need to start to control this.
Currently I see two options:

Introduce new table where I put all environmental details and script + DB layout version. Script will alter DB layout each time script and DB versions are not the same.
Alter DB each time script is running (daily). This is more easy but its dirty hack.

What to choose? Which solution is better?
Maybe there is other more elegant option?


texts: postgres update parent if entity / foreign key updates

texts:
I have a table recipes and every recipe can have multiple entries.

CREATE TABLE IF NOT EXISTS "recipes" (
    "ident" SERIAL PRIMARY KEY,
    "identifier" TEXT NOT NULL UNIQUE,
    "modified_on" TIMESTAMP WITH TIME ZONE DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS "entries" (
    "ident" SERIAL PRIMARY KEY,
    "recipe_id" INTEGER NOT NULL,
    "name" TEXT
    FOREIGN KEY ("recipe_id") REFERENCES "recipes"("ident") ON DELETE CASCADE
);

Now when a recipe is updated, the following function is called and updates my timestamp:

CREATE OR REPLACE FUNCTION update_recipe_timestamp_proc()
RETURNS TRIGGER AS $$
BEGIN
    NEW."modified_on" = now();
    PERFORM pg_notify('notify_recipes_update', CAST(NEW.ident AS text));
    RETURN NEW;
END;
$$ language 'plpgsql';

Now I also want my timestamp in the recipe modified_on to get updated whenever an entry which belongs to it is updated.

The following trigger is not working.

DROP TRIGGER IF EXISTS update_recipes_timestamp_e ON entries;
CREATE TRIGGER update_recipes_timestamp_e BEFORE UPDATE ON entries FOR EACH ROW EXECUTE PROCEDURE update_recipe_timestamp_proc();

ERROR: Record "NEW" has no field "modified_on"

Someone can give me a hint what I have to do?


texts: Where can I find a drawing of the physical structure of a MySQL Server?

texts: I'm new to MySQL but want to learn more about it.  I know Oracle and Rdb quite well.  Can you show me where I can locate some type of drawing of the physical structure of a MySQL Server?


texts: PREEMPTIVE_OS_WRITEFILEGATHER wait on database RESTORE with IFI on

texts:
I have an Azure VM with Windows Server 2012 R2 and SQL Server 2014 Enterprise Edition. I have enabled IFI (added SQL Server user NTSERVICE\MSSQLSERVER to "Perform Volume Maintenance Tasks").

I have tried to restore a 3 GB database with files on C drive (SSD) which took about 40 minutes to complete. Checking restore progress, after about 20 minutes it was at 100% complete.

During the restore, I had PREEMPTIVE_OS_WRITEFILEGATHER constantly showing up for my restore.

40 minutes for 3 GB database is too much.

Files being on C drive (not good) and with all the rest of activity that was on C drive - could have this impact my restore that bad taking it to 40 minutes, having in mind this is still a SSD drive?

Is there anything else I could look for in order to find what is causing it to take THAT long?

EDIT:
I have checked the following so far:

1 - checked with EXEC xp_cmdshell "whoami /priv" and I get SeManageVolumePrivilege       Perform volume maintenance tasks          Enabled - so sql server user does have permissions

2 - restarted sqlservice, just to be sure, after the answers below

3 - checked creating a new database with trace flags 3004,3605 ON and it's only logging ldf zeroing, not MDF - so IFI is ON


texts: PRODUCT_ID as primary key rather then the UPC(UNIVERSAL PRODUCT CODE)

texts: I have a very strange kind of question in my mind, I'm using the EAV model in my E-COMMERCE DB. I have a product table with primary key p_id which is auto_increment, and to save its attributes I have a different table product_attribute, that also stores SKU of every product and have p_id as FOREIGN KEY, which there I am using attribute-value pair. So, my question is, if I missed storing a product of size 7 of any product of a company A in product_attribute table and stored all the other sizes. How, will I know that what was my p_id for the product I missed at a later time when I want to store it? SHOULD I use UPC instead of p_id?


texts: perform multiple conditional join on multiple tables

texts:
I have a situation where I need to get result based on conditional joins

For example,

    SELECT
      CGI.SALES_BRAND_BSNSS_ID,
      CGI.SALES_BRAND_CD,
      NVL(MTRX. matrix_score_cd,'RV1') MATRIX_SCORE_CD,
      MDL.CORP_GROUP_ID,
      MDL.MKTBRIDGE_RUN MKTBRIDGE_RUN_CD,
      MDL.MKTBRIDGE_GROW MKTBRIDGE_GROW_CD,
      MDL.MACRO_SEG MACRO_SEG_CD
FROM

      BIRTAB.CGI_ACTIVE_CUST_SEG_V MDL INNER JOIN

      BIRTAB.SBBID_CGI_HIST_XREF CGI ON

      MDL.CORP_GROUP_ID = CGI.CORP_GROUP_ID LEFT JOIN

      BIRTAB.CUST_SEG_SCORE_MATRIX MTRX ON

-- if below two join conditions matches then it need to go to 3rd join condition else ignore all joins,

            NVL(trim(MDL.ECHECK_DECILE_CD),'%') = trim(MTRX. ECHECK_DECILE_CD) AND

            NVL(trim(MDL.MACRO_SEG),'%') = trim(MTRX.MACRO_SEGMENT_CD)  AND

-- if the above two joins matches and below join doesn't matches then ignore below two joins and get result based on above 2 joins

            NVL(trim(MDL.MKTBRIDGE_RUN),'%') = trim(MTRX.MKT_BRIDGE_RUN_CD) AND

-- if the above three joins matches and below join doesn't matches then ignore below join and get result based on above 3 joins

            NVL(trim(MDL.MKTBRIDGE_GROW),'%') = trim(MTRX.MKT_BRIDGE_GROW_CD)

Can anyone suggest some ideas to perform this logic.

Ty


texts:
Does high fragmentation on a heap impact query performance?

Can I rebuild the heap with ALTER TABLE REBUILD to get rid of the fragmentation?

This is a staging table used for reporting. It does have non-clustered indexes but there is no clustered index.


texts: How to relax file permissions conditions on postgres database folder?

texts:
Postgresql service refuses to start if the data folder (PGDATA) doesn't belong exclusively to the postgres user (i.e. permissions = 700).

Is there any way to disable or circumvent that condition? Ideally I would like postgresql to start even if the permissions on that folder is set to 777.

Explanations:

I have built a Docker image with a running instance of Postgresql. The requirements are:

a) the container should be able to run on Windows Home edition (which
means Docker for Windows is not an option and we have to use
docker-machine and VirtualBox)
b) the user should use Kitematic to
download and run the Image, without any need to go to the command
line or do any post-install configuration (i.e. it should work out of
the box). This means that I only have control over what happens
within the container running Psql; I can't change the VM or Windows
configs.
c) the database must be stored on a data volume mounted on
the file system of the host OS (because I want the container to be
disposable and upgradable)

a) and b) works perfectly well. But as soon as the user enables the Data Volume in Kitematic (condition c) ) Postgres refuses to start because the owner of the DB folder is reset to 1000:staff which are the default user and group in boot2docker Virtal Machine. There is no way to programmatically change the owner or permissions inside the VirtualMachine due to the way VBox shares the folder with Windows (via a vboxfs mount).

So basically within the container I'm stuck with a PGDATA folder with full access to everyone (i.e. 777, which is not a really an issue in itself since the data is not sensitive and not exposed to other machines).


texts:
I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history.

Right now, I can identify records from main with no status_history at all, but I need to query records from main that did not make it to the delivery status.

Current query is:

SELECT
   m.id
   ,m.date_completed
   ,s.status_message
FROM
   main m
   LEFT JOIN status_history s ON s.main_id on m.id
WHERE
   m.date_completed IS NOT NULL and s.status_message is null;

That will show me everything that has been completed but has no status_history at all. The problem is, that a typical delivery has multiple status histories (let's say for simplicity: new,confirmed and delivered).

I want to find all records that are missing the 'delivered' status history (hopefully this won't make a difference, but in the real DB, I'm looking for a dozen different messages that are equivalent of delivered). I'm guessing this is a simple sub-query but I'm having a mental block right now.

Examle schema for the above is pared down to:

main table:
id (Unique Key)
date_completed (datetime)

status_history table:
id (unique key)
main_id (INT)
status_message (varchar)

For example, main may look like:

+----+---------------------+
| id | complete_date       |
+----+---------------------+
|  1 | 2016-05-09 09:12:09 |
|  2 | 2015-05-21 14:39:16 |
|  3 | NULL                |
|  4 | 2015-05-21 13:57:05 |
|  5 | 2015-05-21 14:43:17 |
|  6 | 2015-05-26 08:49:17 |
|  7 | 2016-05-09 14:20:06 |
|  8 | 2015-05-26 14:07:53 |
|  9 | 2015-05-21 15:16:08 |
| 10 | 2015-05-21 15:34:02 |
+----+---------------------+

And status_history would have this (plus a datetime stamp column):

+----+---------+----------------+
| id | main_id | status_message |
+----+---------+----------------+
| 1  |   1 | New                |
| 2  |   1 | Confirmed          |
| 3  |   1 | Delivered          |
| 4  |   3 | New                |
| 5  |   4 | New                |
| 6  |   3 | Confirmed          |
| 7  |   4 | Confirmed          |
| 8  |   5 | New                |
| 9  |   5 | Confirmed          |
| 10 |   4 | Delivered          |
+-------+---------+-------------+

So, if the above was the entire output of both tables, I want to find that m.id's 2,5-10 are missing "Delivered" status messages.