texts: Adding where condition with index make query slower(use filesort and temporary)

texts:
I'm trying to implement soft-delete so I added deleted_at (datetime) column to users table.
Also there is a query i need to run:

    SELECT
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
GROUP BY `users`.`category_id`;

type, possible_keys, key, key_len, ref, rows, filtered, Extra
'index', 'index_users_on_category_id', 'index_users_on_category_id', '5', NULL, '840926', '100.00', 'Using index'

which was running just fine until i added  WHERE clause

    SELECT
    COUNT(*) AS count_all,
    `users`.`category_id` AS users_category_id
FROM
    `users`
WHERE
    `users`.`deleted_at` IS NULL
GROUP BY `users`.`category_id`;

type, possible_keys, key, key_len, ref, rows, filtered, Extra
'ref', 'index_users_on_category_id,index_users_on_deleted_at', 'index_users_on_deleted_at', '6', 'const', '420463', '100.00', 'Using index condition; Using temporary; Using filesort'

Of course it becomes slower, i can't find information

why type changed
why key_len changed from 5 to 6
why rows in second query shows half of total number (800k)

And how can i make it faster without using USE INDEX (index_users_on_category_id)


texts:
I have a SQL instance (2012 Standard Edition) which consumes all CPU resources because of a rollback-process. The instance has been restarted, so the database is currently In Recovery. I cannot see any SPID in Status ROLLBACK with sp_who2 or sp_whoisactive.

The data within that database is not important, but it's currently wasting all my CPU and slowing down the application.

Is there any chance of cancelling the rollback. I am absolutely willing to completely lose the affected database.


texts: Changing mysql root password on Windows after removing mysqld-nt.exe

texts:
I am trying to  change password of MySQL root by following the steps here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

unfortunately the mysqld-nt.exe doesn't exist in Windows anymore according to thread here http://forums.mysql.com/read.php?11,206806,207257#msg-207257

So how would I go about this which exe is the replacement now. I should mention I have MySQL server 5.6

Thank You,

Saher


texts: Oracle: Create a hidden user for RMAN backups only

texts:
It's been a long time since I worked with Oracle and I can't remember whether the following is possible:

I'm trying to create a hidden DBA user [ alternatively do this process as root user ] to perform complete and differential backups on Oracle 11g with RMAN. I cannot use the "SYS" user, because I don't have the password and I don't want to change it.

The sole purpose for the hidden user is to have complete db backups in case of a problem, since we suspect the software company that should be providing us with servicing our application and db of damaging the database, either out of sheer incompetence or on purpose. We don't care which it is, our company needs to be able to fix such issues ASAP so that we can work while the software provider sorts out their problems in their own time. But the only type of backup they do is a db dump and it's insufficient.


texts: SQL Server Agent Jobs starting but is idle

texts:
We have a data warehouse with a few databases. Every morning we import data from source systems to our data warehouse. The final step used to be to initiate loading of an Essbase cube by a SQL job which remotely executes a bat-file on the Essbase server.

This has worked well for years. Now we added some complexity. We have started to import data from another source system. We have developed a new SSIS package which imports the very small files. The new data is not needed in the Essbase cube so we have scheduled the new sql server agent job to run at a time when the Essbase cube is still loading data from our fact tables.

The problem:

The new sql server agent job starts as planned but does nothing. In job activity monitor the job can be seen as running. But nothing has been done in SSIS, we have extensive logging in the SSIS packages, but the log files has not even been touched. Then, 30 minutes later when the cube is fully loaded and the sql job managing it completes, our new job stops idling and finishes the import process within a few seconds.

It seems that sql server agent refuses to execute the SSIS package while the cube job is executing. The server is not choking under the workload. Manually running the SSIS-package while the cube is still loading works fine.

Any suggestions welcome...


texts: sp_blitz reporting both msdb backup history not purged and purged too frequently

texts:
Just downloaded the last version of sp_blitz from github, and have run it on two different database servers.

On both of them it reports both of the following findings:

MSDB Backup History Not Purged
MSDB Backup History Purged Too Frequently

On one of the servers there were 2 years of backup history, but on the other there were only one month of backup history.

Anyone know why both of these are reported? Seems to contradict each other.

Could it be a bug in sp_blitz?


texts: Automatic recovery of the failed postgresql master node is not working with pgpool II

texts:
I am new to Postgresql and Pgpool II setup. I have configured the Postgresql HA/Load balancing using Pgpool II and Repmgr.
I have followed the link to do the setup.

The setup consist of 3 nodes and verison of Application and OS is as mentioned below:
OS version => CentOS 6.8 (On all the 3 nodes)

Pgpool node => 192.168.0.4
Postgresql Nodes:
node1 (Master in read-write mode) => 192.168.0.6
node2 (Standby node in read only mode) => 192.168.0.7

Pgpool II version =>  pgpool-II version 3.5.0 (ekieboshi).
Postgresql Version => PostgreSQL 9.4.8
Repmgr Version => repmgr 3.1.3 (PostgreSQL 9.4.8)

I have configured the Pgpool in Master-Slave mode using Streaming replication.

The setup is as shown in the below image:

When I bring down the Master node(192.168.0.6), the automatic failover happens successfully and the Slave node(192.168.0.7) becomes the new Master node.

After failover, I have to recover the failed node(192.168.0.6) manually and sync it with the new Master node. Then register the node(192.168.0.6) as a new Standby node.

I want to automate the Recovery process of the failed node and add it to the cluster back.

The pgpool.conf file on the Pgpool node(192.168.0.4) contains parameter recovery_1st_stage_command. I have set the parameter recovery_1st_stage_command = 'basebackup.sh'. I have placed the script 'basebackup.sh' file on both the Postgresql nodes(192.168.0.6, 192.168.0.7) under the data directory '/var/lib/pgsql/9.4/data'. Also I have placed the script 'pgpool_remote_start' on both the Postgresql nodes(192.168.0.6, 192.168.0.7) under the directory '/var/lib/pgsql/9.4/data'.

Also created the pgpool extension "pgpool_recovery and pgpool_adm" on both the database node.
After the failover is completed, the 'basebackup.sh' is not executed automatically. I have to run the command 'pcp_recovey_node' manually on the Pgpool node(192.168.0.4) to recover the failed node(192.168.0.6).

How can I automate the execution of pcp_recovery_node command on the Pgpool node with out any manual intervention.

Scripts used by me as follows:

basebackup.sh script

#!/bin/bash
# first stage recovery
# $1 datadir
# $2 desthost
# $3 destdir

#as I'm using repmgr it's not necessary for me to know datadir(master) $1
RECOVERY_NODE=$2
CLUSTER_PATH=$3
#repmgr needs to know the master's ip
MASTERNODE=`/sbin/ifconfig eth0 | grep inet | awk '{print $2}' | sed 's/addr://'`

cmd1=`ssh postgres@$RECOVERY_NODE "repmgr -D $CLUSTER_PATH --force standby clone $MASTERNODE"`
echo $cmd1

pgpool_remote_start script

#! /bin/sh

if [ $# -ne 2 ]
then
    echo "pgpool_remote_start remote_host remote_datadir"
    exit 1
fi

DEST=$1
DESTDIR=$2
PGCTL=/usr/pgsql-9.4/bin/pg_ctl

ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null &

failover.sh script

#!/bin/sh
failed_node=$1
new_master=$2
(
date
echo "Failed node: $failed_node"
set -x

# Promote standby/slave to be a new master (old master failed)
/usr/bin/ssh -T -l postgres $new_master "/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

Help me with the procedure to automate the recovery of the failed node.
Also let me know, for failover is it compulsory to use repmgr or we can do it without repmgr. Also specify any other method for failover without using Repmgr, its advantages and disadvantages over Repmgr.


texts: mysql - importing large Tablespace: Lost connection during query

texts:
iam trying to recover innodb table which has 1.5M rows from ibd file ( 5.5 GB )

this is the exact steps i do:

getting create table query using mysqlfrm command
create the table
Alter Table  discard tablespace
moving the new tablespace to the db directory
Alter Table  import tablespace;

and i'm getting this error after 5 minutes :-

ERROR 2013 (HY000): Lost connection to MySQL server during query
my.cnf:

[client]
port=3307
[mysql]
no-beep

[mysqld]
max_allowed_packet=8M
innodb_buffer_pool_size=511M
innodb_log_file_size=500M
innodb_log_buffer_size=800M
net_read_timeout=28800
net_write_timeout=28800
connect_timeout=28800
wait_timeout=28800
open_files_limit=100000
innodb_open_files=100000
delayed_insert_timeout=28800
innodb_lock_wait_timeout=28800
slave_net_timeout=28800

skip-grant-tables
port=3307
datadir=D:\dbrecover\_home_db_\home\db
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="WIN-36LFCDISVVC.log"
slow-query-log=1
slow_query_log_file="WIN-36LFCDISVVC-slow.log"
long_query_time=10
log-error="WIN-36LFCDISVVC.err"
relay_log="WIN-36LFCDISVVC-relay"
server-id=1
report_port=3307
lower_case_table_names=2
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=123M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=236M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=9
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_connect_errors=100
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

is there any way to import it ??


texts: MySQL Trigger problem with the email address (cause contains the @ sign)

texts:
I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger:

CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO prova (provaemail)
    VALUES (NEW.email);
END

I obtain an empty field in the 'prova' table.

If I try to "copy" another field it's all ok, the problem seems to be only with the email address (cause contains the @ sign).


texts: Two versions of Postgres installed, how do I clean this up?

texts:
If I try to enable PostGIS with CREATE EXTENSION postgis; the error I see indicates that psql is looking for postgres.control in a 9.4 directory:

  ERROR:  could not open extension control file "/usr/share/postgresql/9.4/extension/postgis.control": No such file or directory

postgis.control  exists at /usr/share/postgresql/9.5/extension/postgis.control

I don't have any need for two versions, and I don't know how I got myself into this situation. I'm on Ubuntu and just used synaptic to install I tried to migrate and got another affirmation that 9.5 is already installed:

sudo pg_upgradecluster 9.4 main
Error: target cluster 9.5/main already exists

What is the Right Way™ to switch over to using 9.5 so I can actually use PostGIS?


texts: Adding a group of rows in postgresql based on column id

texts:
I have a table with a sets of rows such as displayed below:

Qty       Equip_ID
21487.92;"Load Center-6"
99272.59;"Load Center-10"
68088.61;"Load Center-11"
21821.5;;"Load Center-6"
102165.2;"Load Center-10"
72973.88;"Load Center-11"
21759.25;"Load Center-6"
102176.9;"Load Center-10"
63559.5;"Load Center-11"

How would I perform an addition operation and get the total of Load Center-6, 10 and 11?
That way I can get the total for each group (6, 10, 11)

I tried using this sql statement based from the answer on this stackoverflow post
https://stackoverflow.com/questions/9600587/how-to-sum-multiple-lines-in-sql
but I get an sql error since WHERE condition can't be placed inside an SUM operation.

SELECT SUM(qty WHERE (equip_id = 'Load Center-6' AND equip_id = 'Load Center-10' AND equip_id = 'Load Center-11'))
FROM td_m2_fact GROUP BY facility_code, period_start;

Trying the SQL statement:

SELECT SUM(qty),Equip_ID FROM table GROUP BY Equip_id

Results:
Qty            Equip_ID
672882459.189999;"Load Center-10"
124347378.953;"Load Center-6"
298342277.620001;"Load Center-11"

What I want:

Qty                                ID
SUM(21487.92,99272.59,68088.61)    Load Center-6,Load Center-10,Load Center-11
SUM(21821.5,102165.2,72973.88)     Load Center-6,Load Center-10,Load Center-11


texts: Database design for assign ticket or task


texts: ParallelPeriod returning null for Feb. 29 in date dimension

texts:
I have a calendar date dimension backed by a physical table of dates (originally created on SQL Server 2000, hence the datetime instead of date):

CREATE TABLE [dbo].[PostDate_Dimension](
    [post_date] [datetime] NOT NULL PRIMARY KEY,
    [day_of_year] [int] NOT NULL,
    [day_of_month] [int] NOT NULL,
    [month_of_year] [int] NOT NULL,
    [post_year]  AS (datepart(year,[post_date])),
    [post_month]  AS (datepart(month,[post_date])),
    [post_day]  AS (datepart(day,[post_date]))
)

The Post Date dimension has four attributes (with member key columns listed, some of which are calculated in the DSV):

Day (Dimension Key) - post_date
Month - post_year, post_month
Quarter - post_year, post_quarter = DatePart(quarter, "post_date"))
Year - post_year

It's nothing too fancy, obviously. I also have a few calculated measures that use ParallelPeriod to calculate YTD figures from the previous year, for quick side-by-side comparison without requiring the user to choose a specific slice of dates. Just pick the current year, and it will find the latest date with sales in it, then compare to that same range from the previous year.

Finding the appropriate date in the previous year normally boils down to this:

ParallelPeriod(
    [Post Date].[Post Date].[Year],
    1,
    Tail(
        NonEmpty(
            Descendants(
                [Post Date].CurrentMember,
                ,
                Leaves
            ),
            Measures.[Total Price]
        ),
        1
    ).Item(0)
)

The Tail call is where it finds the latest date beneath the currently selected Post Date member (typically the current year). That works fine. But if that returns Feb. 29, meaning the last sale for a particular combination of dimension members occurred on Feb. 29, then it passes Feb. 29 into the ParallelPeriod function, which subsequently returns null. And then the previous-year YTD measure also returns null.

So, in a nutshell: Based on this particular schema, is there a simple way to have ParallelPeriod behave nicely for Feb. 29 inputs? If it just returns Feb. 28 of the previous year, that's fine.

EDIT:

A few things I've tried:

Using this expression to adjust the Post Date member:
Iif(MONTH([Post Date].[Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].[Post Date].CurrentMember.Member_Caption) = 29, [Post Date].[Post Date].CurrentMember.PREVMEMBER, [Post Date].[Post Date].CurrentMember)
This works, but the code would be atrocious, since I'd have to replace all [Post Date].[Post Date].CurrentMember with Tail(NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]), 1).Item(0)).
Using Except to remove all Feb. 29 dates from the results of NonEmpty(Descendants([Post Date].CurrentMember,, Leaves), Measures.[Total Price]). I can't figure out the proper syntax (if any) to get a set of all Feb. 29s from the dimension.
Creating a .NET assembly with a user-defined function that takes a member as a parameter, and returns the previous member if it's a Feb. 29. It seems like the classes in Microsoft.AnalysisServices.AdomdServer are extremely limited and don't even allow for this basic task (nor even retrieving the member key as a date value).


texts: Unexplainable DBCC CHECKDB Command Output

texts:
Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1
  (X64) Mar 22 2019 22:33:11 Copyright (C) 2017 Microsoft Corporation
  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0
   (Build 14393: ) (Hypervisor)

My Production server executes these command inside a SQL Agent job to output results to a table in my Administration database:

DECLARE @SQL VARCHAR(MAX) = 'master..xp_cmdshell ''SQLCMD -E -Q"DBCC CHECKDB('+@Database+') WITH NO_INFOMSGS, ALL_ERRORMSGS"'''; -- If all is well, there will be no output.
INSERT INTO DBCC_CHECKDB_Results EXEC(@SQL);

Last night, one line of information was output and the job stopped executing, with no errors shown in the job history. The one line of information was:

  "Sqlcmd: Warning: The last operation was terminated because the user
  pressed CTRL+C."

How is this possible? At 10:00 p.m. Sunday night, no one is logged onto the server. CTRL+C is not in the command line.

As a test, I executed this command, and while it was executing, typed: CTRL+C:

USE ARC
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS:
Nothing happened.

Any ideas on what would cause that line of output?

Thanks, Lee


texts: Why can't a committed transaction be undone

texts:
Once a transaction has been committed, we cannot undo its effect by aborting it.
We have the log file that has all information to undo a committed transaction, so why is this not possible? We should be able do the same with a committed transaction as we can with an aborted one.


texts:
My SQL statement returns properly on my laptop's MySQL (Server version: 5.5.62-0ubuntu0.14.04.1 - (Ubuntu)) but on my server (Server version: 5.7.26-0ubuntu0.16.04.1 - (Ubuntu)) it returns an error.

SELECT *
FROM `orders`
WHERE `mail_sent`='No'
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY `contact_id`
ORDER BY `datetime` ASC;

  1055 - Expression #1 of SELECT list is not in GROUP BY clause and
  contains nonaggregated column 'shop.orders.id' which is not
  functionally dependent on columns in GROUP BY clause; this is
  incompatible with sql_mode=only_full_group_by


texts: Can I tell SQL Server to "do something" when a connection is closed?

texts:
Can I tell SQL Server to "do something" (e.g. release an application-level lock) when the connection is closed for whatever reason?

Background: I want to lock a record on the application level, not on the database level. However, I'm having trouble releasing the lock when the application crashes. Basically, I am implementing the following algorithm in my application:

function editRecord(recordId):
    begin transaction
    if (select lockedBy from myTable where id = recordId) is not empty:
        commit
        show "Sorry, record already in use by ..."
    else
        update myTable set lockedBy = current_user() where id = recordId
        commit
        show UI window to let user edit and update record
        update myTablet set lockedBy = empty where id = recordId

I'm trying to find out if there is an easy way to get rid of the application-level lock when the connection crashes.


texts: Is it possible to automate the configuration of dataguard broker?

texts:
We deploy a number of oracle databases every year and a good percentage of them require a dataguard configuration.

I've developed a script which is able to perform the database synchronization very well:

modify init.ora / generate spfile
generate listener.ora
generate tnsnames.ora
create standby control file
create standby redo log
gather all files in a tar ball ready to be shipped to the standby server.

On the standby server another script:

Dispatch the files different data files, control files, redo logs to where they must be
Modifies the init.ora and generate the spfile
generate listener.ora
generate tnsnames.ora
run the recover managed standby database command

Then I hit a wall on data guard manager :( How can I automate its configuration.

It would be really great if I could do something like this:

dgmgrl sys/manager -e "create configuration broker1 as primary database is 'primary' connect identifier is to_primary;"

I haven't seen anything like that.

Anybody has an idea of what could be done to perform this automatically?


texts: Duplicate database for standby returns error ORA-19625: error identifying file /home/oracle/stdbyctrl.ctl

texts:
I'm working on creating duplication scripts for standby purposes. and it's constantly failing even though the backups are located in the exact same folder.
DB structure is identical and spfile is already configured for standby.
and it results in this.

channel aux1: restoring control file
ORA-19625: error identifying file /home/oracle/stdbyctrl.ctl
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is control file  (/home/oracle/stdbyctrl.ctl)
ORA-19601: output file is control file  (/opt/oracle/oradata/vcas/control01.ctl)
failover to previous backup

I have been searching a solution for a few days now and I think I need the help of the community. It does work if I take the backup in the flash recovery area, but i need this method because it will be applied on a cluster to cluster replication using ASM on both clusters.
Thank you in advance:

Details below:

This is the procedure I'm using:
From my primary server:
I perform the backup.

rman target sys/verimatrix@to_primary auxiliary sys/verimatrix@to_standby catalog rman/cat@to_primary << EOF
> RUN {
> allocate channel d1 type disk;
> backup format '/home/oracle/ForStandby_%U.bkp' database;
> sql 'alter system archive log current';
> backup format '/home/oracle/ForStandby_arc_%U.bkp' archivelog all;
> backup current controlfile for standby format '/home/oracle/ForStandbyCTRL.bkp';
> release channel d1;
> }
> EOF

Then I copy them to the standby in the oracle home as well:

$scp ForStandby* vmx@192.168.1.149:/home/vmx
ForStandby_0qoib29b_1_1.bkp                                  100%  545MB  23.7MB/s   00:23
ForStandby_0roib29i_1_1.bkp                                  100% 6976KB   6.8MB/s   00:00
ForStandby_arc_0soib29n_1_1.bkp                              100%   49MB  24.6MB/s   00:02
ForStandbyCTRL.bkp                                           100% 6944KB   6.8MB/s   00:00

On standby server:

[oracle@standby ~]$ ll
total 622716
-rw------- 1 oracle dba  571039744 Aug 27 13:51 ForStandby_0qoib29b_1_1.bkp
-rw------- 1 oracle dba    7143424 Aug 27 13:51 ForStandby_0roib29i_1_1.bkp
-rw------- 1 oracle dba   51538432 Aug 27 13:51 ForStandby_arc_0soib29n_1_1.bkp
-rw------- 1 oracle dba    7110656 Aug 27 13:51 ForStandbyCTRL.bkp

Then I execute the duplicate command from the primary:

[oracle@primary ~]$ rman target sys/verimatrix@to_primary auxiliary sys/verimatrix@to_standby catalog rman/cat@to_primary << EOF

> RUN
> {
>   ALLOCATE CHANNEL C1 type disk;
>   ALLOCATE AUXILIARY CHANNEL aux1 TYPE DISK;
>   DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
>   RELEASE CHANNEL C1;
>   RELEASE CHANNEL aux1;
> }
> EOF

Which fails like this invariably:

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 27 07:59:02 2013

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

connected to target database: VCAS (DBID=3516284596)
connected to recovery catalog database
connected to auxiliary database: VCAS (not mounted)

RMAN> 2> 3> 4> 5> 6>
allocated channel: C1
channel C1: sid=139 devtype=DISK

allocated channel: aux1
channel aux1: sid=152 devtype=DISK

Starting Duplicate Db at 27-AUG-13

contents of Memory Script:
{
   set until scn  429183;
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 27-AUG-13

channel aux1: restoring control file
ORA-19625: error identifying file /home/oracle/stdbyctrl.ctl
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is control file  (/home/oracle/stdbyctrl.ctl)
ORA-19601: output file is control file  (/opt/oracle/oradata/vcas/control01.ctl)
failover to previous backup


texts: We are moving from SQL Server 2014 to 2017, I see in the options that we have Parameter Sniffing, I believe we should we keep it OFF, but what about the options that we used in 2014 to avoid that? can be affected in 2017, like creating local variables of the parameters and put option(recompile) to the queries.


texts: Taking a backup of a streaming WAL slave

texts:
I have an environment with pair of postgresql servers set up to do WAL streaming replication.

I also have a test environment and periodically I want to snapshot the production DBMS and restore that onto the test server.

Right now I do:

ID=db-$(date +%F_%T)
pg_basebackup --format=tar --pgdata=- --xlog --gzip --label=$ID

and then restore that archive on the test server.

Unfortunately that makes things really slow down on the production server so I'd like to switch that to pulling the clone from the replication slave.

But I can't - the error I get is:

pg_basebackup: could not connect to server: FATAL:  recovery is still in progress, can't accept WAL streaming connections

What do I need to do to accomplish my goal of not killing performance on the master when I need to clone it?

Running on Ubuntu 12.04 x86_64, postgresql server 9.1.9-0ubuntu12.04


texts: cloud computer to speed up sql big data queries?

texts:
i have been doing a lot of data mining at my new job.
we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis.

we're currently using mysql on a dedicated virtual server.
i'm commonly getting the sum of 100,000+ rows in a single query. and let me say, that some of my more complex multi-table queries are taking 14sec+ :P i'm constantly trying to optimize my sql routines to increase performance, but there's only so much i can do.

my boss keeps asking me if we need to move our data to the cloud. and honestly, i'm not sure. i have read a lot about "how great it is", "how fast it is", etc... but it's hard to tell what's marketing and what's legit.

i have also been thinking about caching data. i have done a bit of this in the past and can see a real benefit from it. but i'm not really sure how applicable it is in my situation. we're getting live data all the time, and give the users the ability to mine that data in a verity of ways. (e.g. hourly, daily, weekly, monthly, quarterly, yearly, all time, or a user defined custom time range) if you looking at a data-set from say last month, that data is finalized. there will be no new data added to it ever. but for this month, new transactions are happening every moment, and i'm not sure if i can even cache that type of thing... sigh

i'm not opposed to moving away from sql either. it's just the model we're currently using. but as our company grows (thus the volume of data read+write) i want to continue to offer a very fast service for both the businesses using it as well as their users.

so can i get some feedback (and suggestions) from real world users?
cloud sql? or using/creating some caching engine?


texts: Run Multiple Scripts In One Transaction Across Multiple Servers

texts:
We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a way to run all of the scripts as one big transaction so that all scripts either commit or rollback as a whole.

How do I do this?

I would prefer a way to do this from ADO.NET, but SSMS is cool, too.

My current solution (that does not work) is that I begin transaction in every database, run all my scripts, and then commit/rollback once everything is good. However, I can't run all my scripts since cross-database dependencies are blocking indefinitely.


texts:
I've tried using a stored procedure to create indices, and got the following error:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and
                    (utf8_unicode_ci,IMPLICIT) for operation '='

There's no line number or any other debugging hint.

What's the best way to debug this error?
Are there any good guidelines to avoid encoding inconsistencies?


texts: MySQL overtaking system memory and forced to restart

texts:
I'm facing a frustrating issue.

I have a MySQL Server 5.5 running on top of Ubuntu Server 12.04.3 LTS (VMWare guest w/ 16GB RAM - 8 Cores)

mysqltuner.pl says

[OK] Maximum possible memory usage: 6.6G (41% of installed RAM)

Also 'my.ini' says these:

But MySQL is draining all my physical memory and making me swap, even the server restarts MySQL when it detects the memory is exhaust.

How can i REALLY restrict MySQL memory consumption???

EDIT

Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 128   |
+-------------------+-------+


texts: Recovered database files can't be attached

texts:
I have recovered my hard drive with my databases mdf and ldf files. When I want to attach them I get a 9004 error:

  Error: 9004, Severity: 21, State: 1.
  An error occurred while processing the log for database 'mydb'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

I have already tried following this advice:

  Create an empty database with the same name and physical file layout, shut down the server, swap in the files you want to attach in place of the empty DB files, and start the server.  The database should come up in suspect mode.  You can then ALTER DATABASE <foo> SET EMERGENCY to put it in emergency mode, and then run CHECKDB REPAIR_ALLOW_DATA_LOSS.  This will pull as much data as possible out of the log to make the database consistent, but may have to delete some data in order to make the database consistent.  This is the option which is most likely to get the maximum data back.

I also tried this solution:

  You can attempt to use the CREATE DATABASE FOR ATTACH_REBUILD_LOG to see if that will bring it back.  If the database was cleanly shut down, you MIGHT be able to succeed.  There is also the chance that the database will be inconsistent or corrupt if there are transactions which could not be rolled back.  You should in any event run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS to make your database consistent.  In this event, SQL Server will make no attempt to mine information from the log.  It will ignore the contents of the log.  If there were transactions in process no rollback will be possible, so the ALLOW_DATA_LOSS will be required.

Is there another solution I can try?


texts: Combine two child tables based on parent

texts:
Suppose I have 3 tables parent, child1 and child2.
The parent table may look as follows:
+--------+-----------+-----------+
| Column | Child1_Id | Child2_Id |
+--------+-----------+-----------+
| Name1  | 1         | 1         |
+--------+-----------+-----------+
| Name1  | 2         | 2         |
+--------+-----------+-----------+
| Name2  | NULL      | 3         |
+--------+-----------+-----------+
| Name1  | 4         | 4         |
+--------+-----------+-----------+
| Name2  | 5         | 5         |
+--------+-----------+-----------+

Currently I have tried this:
SELECT        Parent.*, Child_1.*, Child_2.*
FROM            Lead INNER JOIN
                     Child_1 ON Parent.Child1_Id = Child1.Id INNER JOIN
                     Child_2 ON Parent.Child2_Id = Child_2.Id

The problem is, that I only get returned 4 records, but optimally it should return 5 records.
I believe it is because, Child1_Id is Null. However, I still want my records from Child2_Id.. Is there any way to achieve this?


texts: If I want to INSERT a row only if the primary key doesn't yet exist, is it more efficient/simpler to execute INSERT directly and ignore the error in the case of duplication, or should i always run SELECT to check if it already exists first?


texts: SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

texts:
On Production SQL Server, we have following config:

3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to single Dell SAN storage unit which is a RAID array

From time to time, on PRIMARY we are seeing messages similar to below:

  SQL Server has encountered 11 occurrence(s) of I/O requests taking longer
      than 15 seconds to complete on file [F:\Data\MyDatabase.mdf] in database id 8.
      The OS file handle is 0x0000000000001FBC.
      The offset of the latest long I/O is: 0x000004295d0000.
      The duration of the long I/O is: 37397 ms.

We are novice in performance troubleshooting

What are the most common ways or best practices in troubleshooting this particular issue related to storage ?

What performance counters, tools, monitors, apps, etc. must be used to narrow down to the root cause of such messages ?

Might be there is a Extended Events that can help, or some kind of audit / logging ?

UPDATE: added my own Answer (see below) which explains what we did to fix the issue


texts: Create separate temporary table/view and variables for every query

texts:
How can I create something similar to VIEW or TEMPORARY TABLE for every query separately?

I have 1 process, it creates and holds one connection (doesn't destroy connection after request, so doesn't destroy temporary tables) the whole time it's running. I have multiple users connecting to this one process randomly and I performing queries for which I need to store some variables and also resultsets of parts of the query for re-using them.

They cannot be stored as @variables and temporary tables in order not to interfere to other queries.


texts:
We have a parent table, Journal and a child table, Stops.

A Journal can have X amount of Stops.

For a good 80% of our SQL queries, our customers are just interested in the first and last Stop for each Journal.

We often have queries that do something like this:

SELECT
      MINStop.PhysicalCity AS MinStopCity,
      MAXStop.PhysicalCity AS MaxStopCity,
      MINMax.DocNo AS JournalDocNo
FROM (
    SELECT
        MIN(Stops.StopsPkey) AS MINStopPkey,
        MAX(Stops.StopsPkey) AS MAXStopPkey,
        Stops.JournalPkey,
        Journal.DocNo
    FROM Journal
    LEFT OUTER JOIN Stops ON Journal.JournalPkey=Stops.JournalPkey
    WHERE Journal.Type=150
    GROUP BY Stops.JournalPkey, Journal.DocNo) MINMax
LEFT OUTER JOIN Stops MINStop ON MINStop.StopsPkey=MINMax.MINStopPkey
LEFT OUTER JOIN Stops MAXStop ON MAXStop.StopsPkey=MINMax.MAXStopPkey

It works fine, but definitely suffers at the larger locations.

It's just how it's always been done here, and I wanted to reach out for possible improvements. Would it be worth it to store the first and last Stop data in the Journal table, even if that data is duplicated?

We use Advantage Database 11.x so are a bit limited on the SQL features. Probably our only route would be through table modifications.


texts:
I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to execute:

SELECT post_id
FROM pageviews
WHERE user_id = 981
GROUP BY post_id

Here is the execution plan:

mysql> EXPLAIN SELECT post_id FROM visits WHERE user_id = 981 GROUP BY post_id;
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows  | Extra                                        |
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | visits | ref  | user_id       | user_id | 5       | const | 54696 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+---------------+---------+---------+-------+-------+----------------------------------------------+

I'm not sure how to look for the cause of the slowness: maybe the table is not well configured, the mysql server not well tuned, other queries locking stuff, ... Or maybe just 4M rows is a good size to start partitioning.

Production database is on Amazon RDS

CREATE TABLE `pageviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `post_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `FK_444839EAA76ED395` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `visits_ibfk_2` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4587432 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: Survey Database - Respondent Table (1NF or 3NF - Pros and Cons)

texts:
Building out a Survey ERD ODS layer.  Now i'm debating on the design for my Respondent Table.  Not sure if its best to have it as 1NF or 3NF.  FOr a quick back story I get a survey extract with respondent, response, and questions data.  This data is loaded as his in a staging area.  From there I am moving the data from staging to ODS than to DW.  But wanted to get some feedback and pros and cons on my respondent table.

Now here is what i'm thinking for my design for the 1NF respondent table and 3NF respondent table.

- **1NF Respondent Table**
 - RESPONDENT_ID PK
 - RESPONDENT_SERIAL_ID
 - RESPONDENT_AGE_CODE
 - RESPONDENT_AGE_DESC
 - RESPONDENT_GENDER_CODE
 - RESPONDENT_GENDER_DESC
 - RESPONDENT_STATELIVEIN_CODE
 - RESPONDENT_STATELIVEIN_DESC
 - RESPONDENT_REGION_CODE
 - RESPONDENT_REGION_DESC
 - CREATE_DATE
 - UPDATE_DATE
 - CHECK_SUM - MD5
 - CURRENT_IND

And 58 other attributes that define a respondent such as zipcode, political party, employment and etc.

 - **3NF Respondent Table**
 - RESPONDENT_ID PK
 - RESPONDENT_SERIAL_ID
 - RESPONDENT_TYPE -- Example (AGE, STATELIVEIN, ZIPCODE, POTPARTY and
   etc)
 - RESPONDENT_CODE
 - RESPONDENT_DESC
 - CREATE_DATE
 - UPDATE_DATE
 - CHECK_SUM - MD5
 - CURRENT_IND

Now my question what are some of the pros and cons on both design of each table and lastly which would you go with and if there are any improvements you see please let me know.


texts: mysqldump large innodb tables - only importing small number of records

texts:
I've inherited a website I didn't originally build.
The database includes a users and a bookings table both of which are relatively large (innodb) tables.

I'm trying to download a copy of the database to use locally but despite the mysqldump command seemingly working when I run the dump file against my local database server I am missing the majority of rows in these large innodb tables.

The mysqldump command I've used is:

mysqldump --compress --add-drop-table --routines --events  --comments --extended-insert  -u root -p dbname > dbdump.sql

And I've attempted to restore this using simply:

mysql dbname -u root -p < dbdump.sql

Both the dump and import commands work without errors.

The imported bookings table contains only 4722 of the 41477 records that should be there.

I've found some suggestion it could have been a problem with the max_allowed_packet setting on the server but increasing this to 16777216 and re-running mysqldump made no difference.

Any suggestions?


texts: Relational Algebra: how to express the combination of aggregation and projection?

texts:
Here I have a table, I would like to query the average salary of departments, the SQL statement should be

select dept_name, avg(salary) from employee

I try to express this SQL in relational algebra. My assumption is as follows.

You can see that dept_name is not wrapped by an aggregation function, which is not following the standard.

Is my expression correct?
What is the right way to express the combination of aggregation and projection?

Reference

https://en.wikipedia.org/wiki/Relational_algebra#Aggregation


texts: Data Files Outgrowing Hardware - Split the File?

texts:
Where I'm currently working, the primary business database is located on a virtual server (SQL Server 2005 standard). The temp DB and logs are on one disk, the database itself on another. The SAN is configured in such a way that they can only provision 512GB disks (4K blocks I'm told, but I nothing about SANs). There is about 4% free space on the disk that holds the DB.

I was tasked with working a way to archive off old data and purge it from the database into one year chunks for archive to tape (I'm a contractor and really I'm a developer although I have dabbled in DBA world in the past and have 15 or so years experience developing with and for SQL Server, not a DBA, and it was handed to me as soon as I walked in the door. We do have a DBA so I don't know why I'm doing this but never mind). That was about 6 months ago and since then I have, on and off, built something which will do just that. But I am increasingly concerned that we cannot possibly test the resulting database enough to verify that we really haven't broken something and I've been casting my mind around for an alternative solution.

I first thought about partitioning but that's out because we have SE and no chance of upgrading to EE as in 6 or so months the whole shebang will move to a dedicated data centre with larger, more modern infrastucture and the whole system will be replaced by new developments in the next 2-4 years. But then it occurred to me that surely we can split the database into multiple files within a filegroup and spread them across multiple disks (we have no problem provisioning more 512GB disks). This leaves the entire DB intact and may even have a slight performance benefit, although that isn't the primary objective here.

I'm pretty sure this is OK in SE (again, I'm not a DBA) and I see this as far less risky and potentially quicker to implement. so my questions:

Is it possible to have a database split across multiple files in 2005 SE?
Is it possible to split an existing DB that is currently in one file on a single disk into multiple files across multiple disks?
Do I even need to present any more than this information to convince any sane person that it would be foolish to risk the disruption to a multi-million pound business of purging data from a production database?


texts: SSRS : How to give parameter values in URL Http requests?

texts:
I created a dataset in an SSRS report and this dataset uses a parameter. Here is the request:
select { [Measures].[ID Distinct Count]} on COLUMNS,
non empty filter({ [DIM ANESTH].[PBM ANE NOM].[PBM ANE NOM] },
not InStr( [DIM ANESTH].[PBM ANE NOM].CURRENTMEMBER.MEMBER_VALUE, 'Unknown') = 1) on ROWS
from BDD_PAT_ANESTH where ( StrToSet(@param_req, Constrained))

param_req is a parameter name I gave, and SSRS created a corresponding report parameter and joined both. Here are some screenshots:

All it works, but I want to call the report server via http requests (in C#), and provide with some parameter values. Usually, SSRS http URLs finish by the parameters : ...&param1=value1&param2=value2 and so on. But I don't see how I can provide with the value "[DIM ANESTH].[ID].&[3]" for example.
An other way could be to define a custom parameter whose values are integers, and to define a second parameter whose origin is the first one and whose values are some members like "[DIM ANESTH].[ID].&[3]".
Do you know how I can achieve either method (I think the first one could be easier).
thank you.
PS: I found this about parameters but it seems to be only partially relevant.
EDIT : @McNets
I tried to follow your advice, but with no success: considering the third screenshot, the one with default values, I tried to put in my browser this url :

http://localhost/reportService?%2fSSRS%2fReport6_tryParamFrom0&rs:Command=PDF&param_req=5

and I got this error:

Reporting Services Error Default value or value provided for the
report parameter 'param_req' is not a valid value.
(rsInvalidReportParameter)

Please see : here from microsoft.
What am I missing?


texts: sql server database size shown different in properties pane

texts:
while monitoring when checking  database space used,my favorites are

use indexinternals
sp_spaceused

select (sum(size)*8)/1024 as size From sys.master_files where name like 'indexinternals%'

but for a curiosity I also checked the properties of database
but when I take the properties of the database its shows the size 380MB

Its just different on this database only ,all other databases shows correct values.Any suggestion where to look at and why its showing like that ?


texts: Is there a way to specify which schema the database task "Export Data-tier Application" exports for the other entities besides the tables?

texts: I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object types too such as views and stored procedures. Is this possible?


texts:
I have set up a profile and operator within SQL Server 2008. I also have an operator that is a group in MS Outlook.

When I use the single user address as the notification address when a job completes, the email is sent out (so profiles and mail is working). When i use the group address (its exactly the same format as the single user one - abc@abc.co.uk), nothing is sent out.

Any suggestions?

trobbie


texts: Non clustered index for non-evenly distributed data in large SQL table

texts:
I have the following table containing 2.000.000.000 rows:

CREATE TABLE [VeryLargeTable]
(
   [dataCol1] [NVARCHAR(MAX)]
   , [dataCol2] [NVARCHAR(MAX)]
   , [Manufacturer] [NVARCHAR(10)]
   , [MsaiSalesType] [NVARCHAR(2)]
   , [Return Volume] [NVARCHAR(2)]
)

The data is distributed this way:

I have to determine the first column(Sales Types), which is determined by the following 3(Manufacturer, MsaiSalesType, Return Volume).

The table will be created on the spot, then the data will be inserted via SSIS from a flat file. After that, data will not be inserted, updated, deleted.

I have taken into consideration filtered indexes and partitioned tables.

Would they be efficient if the data is so un-evenly distributed ?
What are my other options ?

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data.


texts: Postgresql 9.2 - Auto vacuum running continuously for 18+ hours

texts:
I currently have a table that is updated daily (easily 20%+ rows are updated, all floats/integers) and the vacuum is kicking off and running for more than 18 hours.  There are 19 million rows total.

Does this seem indicative of a problem?  It slows the entire database down and it's running on a pretty powerful machine (32 cores, 128 GB RAM, SSD).

Any suggestions on how to correct/fix/debug further?

All vacuum settings are default.


texts: How to change a MySQL previous slave to be a master and remove slave status information?

texts:
I have a master -> slave configuration where the master failed.  I've been able to reset the old-slave to be a master and the old-master to slave from it.  Fine.

What I can't seem to do is to remove the master information on the old-slave which is now the new-master.  I see:

mysql> show slave status \G
*************************** 1. row ***************************
           Slave_IO_State:
              Master_Host: 10.1.2.101
              Master_User: replicationSlave
              Master_Port: 3306
              ...
              Slave_IO_Running: No
              Slave_SQL_Running: No

I've read a lot of MySQL documentation but I still haven't found a way to clear the slave information from the new-master.  I've tried:

RESET SLAVE which does not seem to clear those settings. [[Actually it does remove the master.info file but not the memory settings.  See below.]]
CHANGE MASTER TO MASTER_HOST='' which just spits on an error since it was deprecated recently.
Checking my.cnf which does not have the master information since they were added programmatically.
RESET MASTER because some mysql docs recommended it.  That only resets the bin logs.
Poking around in the internal MySQL tables to see if I can find the fields to clear.

What is the proper way to do this on MySQL ~5.5.9?  Thanks for any help.

Edit:

So it turns out that RESET SLAVE removes the master.info file as @RolandoMySQLDBA implied.  However, you still need to restart the server before the slave information is removed.

Is there any way to remove this slave information without having to restart mysqld?


texts: Type of an id field that references another id field

texts:
I'm on postgresql. I have a table user (id serial not null) and another table client(id) i need to create a :
foreign key client(id) references user(id).

What the client(id) type should be? Int or serial? Thanks for help.


texts: Synchronization between master and backup server

texts:
I am searching for an option to synchronize data between production server and backup server automatically. Does somebody know a method, how I can do this:

if I delete, insert or update a record it should update it should do the same action for the backup server
if I select some entries it should select it from the primary server
if the primary server is not available it should select, insert, update and delete on the backup server
if the backup server is available again, it should write the data back to the primary sql server


texts:
I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy of an MS Access database and starting again from scratch. Obviously this leads to multiple copies of a database lying around a network, with all the attendant problems you'd expect.

The new design includes a Dataset table, which holds a list of data cuts (e.g. 2014 H1, 2014 H2... etc.).

What is the best way to implement this into the larger design? I can think of two ways to do it:

1 - Add a 'Dataset' field to all fact table records, i.e. prices, volumes etc. are associated with a particular reporting period.

2 - Add a new table acting as a junction between dimension tables and the data cut, i.e. each asset (e.g. product, stock item etc.) in the database is assigned a new Id for each cut.

I can see benefits and drawbacks to each approach: then first leads to a more complex key in many tables, and the second leads to more tables but potentially greater efficiency.

Is there a standard method/best practice of implementing this, or is there a better approach entirely that I'm unaware of?


texts: Extended Events Collation resolution error

texts:
When trying to expand the sessions under extended events I am getting a Collation Resolution error in SSMS 18.2 (15.0.18142):

Looking at a trace I have found that this query is what is giving me the collation error but I can't see why? All of the system DB's have the same collation

SELECT * FROM sys.server_event_sessions AS session
LEFT OUTER JOIN sys.dm_xe_sessions AS running ON running.name = session.name


texts: Make sure two columns are sorted in the same order

texts:
I have a table like this:

CREATE TABLE T (
 ID INT NOT NULL IDENTITY PRIMARY KEY,
 CreateDateTime datetime2 NOT NULL,
 --other columns
)

How can I make sure that ID and CreateDateTime are both sorted in the same order? In other words, the following queries would return an identical order:

select * from T order by ID
select * from T order by CreateDateTime

I need this guarantee because for performance reasons I want to query a date range by filtering on the ID column. This is possible if the guarantee holds.

I want to fail DML that would violate that property. My intention is to catch bugs breaking this invariant.

Is there a way to do this without using triggers? My hope is that there is a more elegant solution.


texts: How to configure database mail in sql server 2012

texts: when I configure the database mail it is showing the following error, what it means ?


texts: How to store immutable, unique, ordered, lists?

texts:
I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists and entity instances are immutable. There will be far more entity instances than lists, and the database has to support fast insertions of entities.
So what's an insert-efficient, robust, way of storing immutable, unique, ordered lists?

Edit: The list items are simple integers, and the typical length is about 5 items. Long lists of, say 10 or 20 items are very unlikely but possible.

Edit: So far, I've considered these approaches:
1)
lists table has these columns: <list_id> <order> <item> so if list #5 contains the elements [10,20,30] the table will contain:
5   1   10
5   2   20
5   3   30

The entity table will have a item_list_id column that references the lists table (it's not a foreign key since list_id is not a unique column in the lists table - The this can be solved by adding another table with a single column that which contains all valid list_ids
).

This solution makes inserts a bit tricky
It also places the responsibility for enforcing the uniqueness of lists on the application, which isn't great.

2)
lists table has these columns: <list_id> <item1> <item2> <item3> ... <itemN> so if list #5 contains the elements [10,20,30] the table will contain:
5   10   20   30

The entity table will have a item_list_id column that references the lists table.

This solution is less robust since list lengths are limited (although this isn't a huge problem for me since my lists are VERY unlikely to contain more than 10 or 20 elements)
This approach is quite horrible to query ("find all lists in which a particular item appears" has to specify each and every column), and a nightmare to map using an ORM.
Insertions of new entities is not too bad since my lists are typically 4-5 items long, so I can probably index the first few columns.
Enforcement of uniqueness is still in the hands of the application.

3)
Use solution #1, but replace the meaningless list_id with a hash (say SHA-1) on the list in serialized form.

This way uniqueness is more strictly enforced since lists will have unique hashes
Insertions are simpler and quicker(?)
The data integrity enforcement is still in the hands of the application.


texts: After moving database (backup, restore), I have to re-add user

texts:
I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio.

Whenever I do this, the applications on the target machine cannot access the database until I delete the user they use, "george", from the database users (Database, Security, Users in SQL Server Management Studio), and re-add it as owner under Security, Logins, george/properties, user mapping.

Is there a better way to do this? It seems a little convoluted.


texts: OK to put temp tablespace on volatile storage or to omit it from backups? (Postgresql)

texts:
I would intuit that it's fine, but I just want to make sure there are
no gotchas from a recovery point of view:

If I were to lose my temp tablespace upon system crash, would this
prevent proper crash recovery?

Also, if I were to omit the temp tablespace from the base backup,
would that prevent proper backup recovery?


texts: Can you gain the performance benefits of partition elimination without changing SQL module or table definitions?

texts:
I work for a company that has a multi-tenant database model that doesn't currently use database partitioning. The ideal end-state would be adding a new TenantID column to every existing table, including this new column as the first column in every primary key, and rewriting all existing SQL definitions to filter and equi-join on TenantID.

However, with thousands of existing SQL modules, this is a resource intensive solution, outside of a complete rewrite of the entire database. So my new plan is to make this update in phases.

Phase I will be keeping the existing database schema exactly as is, and creating aligned database partitions that use the existing SupplierID column (which is in nearly every table) as a proxy for TenantID. SupplierID already lines up well to what I want to do with TenantID. Existing suppliers are clustered into supplier groups, so a group of suppliers can be thought of as a single tenant.

So my question is. If I do this, create aligned database partitions on the existing tables without changing any table schema or the definitions of any existing SQL modules, will I see any performance benefits?

You can ask for additional information in the comments, and I will update the question to reflect them. But for starters there are many existing queries that filter on SupplierID, but few that equi-join on it.


texts:
I have a ORACLE DB and when i try inserting "INDIA" and "india" as values into column which is carrying unique constraints on it , it is allowing the value to insert .

It looks like case sensitive options should be enabled in the Db , But not able to figure out where to do that so that it will not allow values with the same name either small / capital letters.

Regards
Chenna B.

But I have many columns which need to be updated in the same , so instead of that , can i change parameter at DB level which will not allow of that sort in any columns


texts: What does "Latest Restore Time" says in AWS RDS(Mysql) instances page

texts:
I have two RDS MySQL instances and when I look at the instances page, I have seen a field named "Latest Restore Time" and the value for it is some time stamp in IST time zone.

I would like to know what it actually mean. Being the individual server:

What is the need for this to be Restored?
Where it gets restored?


texts: Moving a collection to a new database (v2.2)

texts:
To take advantage of the improved concurrency in Mongodb 2.2--namely database level locking--we would like to move a sharded collection to a new database.

I have been unable to find any documentation which will help with this particular scenario.

I know I cant just "rename" this sharded collection: https://groups.google.com/d/topic/mongodb-user/lajAcp3HkZ4/discussion

The only thing I can think of is mongodump the entire collection and mongorestore into a new database. What are my other options?


texts: Check on whether a person is subscribed to another person

texts:
I have a table of subscribers. It has a connection to the user table.

 id | user_id | follower_id |
-----------------------------
 1  |   46    |     71      |
 2  |   46    |     53      |
 3  |   46    |     67      |
 4  |   46    |     34      |
 5  |   89    |     71      |
 6  |   50    |     32      |
 7  |   89    |     53      |
 8  |   12    |     34      |

I am user with id 89. I display all subscribers of the user with ID 46

SELECT F.USER_ID, F.FOLLOWER_ID, U.USERNAME, U.NAME, U.LAST_LOGIN, (U.USER_ID IS NOT NULL) AS IS_FOLLOWER
FROM FOLLOWERS AS F
INNER JOIN USERS AS U ON (U.USER_ID = F.USER_ID)
WHERE F.USER_ID = '46'

I get the result

 id | user_id | follower_id | if_follower |
-------------------------------------------
 1  |   46    |     71      |    true     |
 2  |   46    |     53      |    true     |
 3  |   46    |     67      |    true     |
 4  |   46    |     34      |    true     |

My problem is how do I check that I am subscribed to users with indexes 52, 53, 67, 89?
What am I waiting for as a result

 id | user_id | follower_id | if_follower |
-------------------------------------------
 1  |   46    |     71      |    true     |
 2  |   46    |     53      |    true     |
 3  |   46    |     67      |    fale     |
 4  |   46    |     34      |    fale     |

That is, I received a list of the subscribers of the user with ID 46 and found that I subscribed to users with IDs 71 and 53


texts: The issue of sp_MScheckIsPubOfSub stored procedure

texts:
We have SQL Server 2008 R2 Merge replication set up.

We have 20+ push subscriptions.

Once in a while I get the following error message during the sync:

Error messages: The merge process could not set the status of the subscription correctly.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200963)
Could not find stored procedure 'dbo.sp_MScheckIsPubOfSub'.
(Source: MSSQLServer, Error number: 2812)

I have found the code for the mentioned SP & ran it against the subscriber database & everything worked fine.

My question is: why is this stored procedure getting deleted every now and then by itself ? And how I can troubleshoot this issue?


texts: MySQL Cluster: cannot start the management node

texts:
When I try to start the management node of MySQL Cluster on Windows platform, I get the following error:

  Could neither create or open key 'SYSTEM\CurrentControlSet\Services\EventLog\Application\MySQL Cluster Management Server', error: 5
  Failed to setup event logging

Could someone explain this to me?


texts:
In mysql, I can inspect the current Connection ID by using:
SELECT connection_id();

And it returns the id:
# connection_id()
'6558851'

Or:
SELECT * from information_schema.processlist
WHERE id = connection_id();

# ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
'6558851', 'abc', '99-51-01-something:55353', 'XA', 'Query', '0', 'executing', 'select * from information_schema.processlist plist where id=connection_id()'

Where is that function (if it's a function?) defined though? I've checked in sys and information_schema and was unable to find a function defined with that name. Where is this located then?

Update, on reviewing this a bit more, it seems like these are just built-in functions to the mysql database -- i.e., no different than using a GROUP_CONCAT() or any other function, for example:
SELECT connection_id(), now(), id, email FROM auth_user limit 10;

In this way it would be defined in the C++ code rather than as a user/system-defined function. Is that a correct understanding?


texts: INSERT INTO with subquery that might return 0 results

texts:
I have a status table that I am using to sync other long running processes, and my following query/system is functioning, but also filling up my error logs:
CREATE TABLE status(subject_id UUID PRIMARY KEY REFERENCES users(subject_id)
ON DELETE CASCADE, status1 varchar, status2 varchar)

CREATE TABLE users(subject_id UUID PRIMARY KEY, start_date BIGINT)

problem query:
INSERT INTO status
(status1, subject_id) VALUES ('processing',
(SELECT u.subject_id FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
        WHERE (status.status1 IS NULL or status.status1 = 'ready')
        and usrs.start_date IS NOT NULL LIMIT 1))
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

The ON CONFLICT clause correctly handles the case where the subject_id already exists in status, but no the case where my subquery returns 0 results. In that case I don't want anything to happen, but I would also prefer not to get errors in my log when that happens.
I need to handle cases where:

Claim a subject_id that has no entry in status. This works well.
Claim a subject_id that has an entry in status for another status column. The current ON CONFLICT clause handles this.
Handle the case where there are no more users available. The current query tries to insert a NULL subject_id in this case.

This should be a single query because there are several worker processes that are using the status1, status2, ... columns to synchronize their work and two could both start a long running process on the same subject if the select and insert are separate queries.


texts: Windows SQL Server Express 2012 and LDAP external server integration

texts:
I have a new installation of Windows Server 2012 with SQL Server Express 2012 with the management tools.

I'm planning to create there a simple synchronization that will make queries twice per day to an external LDAP service (not a Windows active directory server) and fill a table in the local SQL instance. I've seen other questions here about this, and all of them refer to the creation of a Linked Server, but I think that will only work if the remote LDAP service was a Windows AD server.

I'm able to query this server using the LDP tool, but looks like I cannot add it as a linked server. Any ideas?


texts: Mysql: Query which orders by default and rank (pin/hold up entries?)

texts:
I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to "hold up"/"pin" certain products to a place in the returned query collection.
Means, I want to set something like rank_index which contains the number the product should have in the returned query collection.
Example:
id     title                rank_index
1      An awesome product
2      Another product      3
3      Baby car
4      Green carpet         1
5      Toy

Lets assume the default order would be the id, descending. But descending? Because as I pointed out the products table gets permanently filled with new products and I always want to show the newest product on top. However, when e.g. an product has a rank_index of 2 the database always should return this entry as the second no matter how money new products are written to the database!
The result of the table above would be:
id     title                rank_index
4      Green carpet         1
5      Toy
2      Another product      3
3      Baby car
1      An awesome product

Is this somehow possible to do (without recursion)? The rank_index column was just an idea of mine. I mean.. I also could do this on the php side and do a normal query which does only include the products without an rank_index and one which only contains products with an index_rank and order them manually on the php side. Do you would recommend this? Would that be faster?
Furthermore, late on I want to add a perfomance_index which is not an +1 incrementing index but rather an index which show the performance of an product. This should be sorted descending as well if that makes any difference to this problem.
This here is doing a good job but only for ascending tables, I want a descending table with products being added permanently!
SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5)

Btw: I am using Laravel 8 if this makes any difference.
Kind regards


texts: When is a multi-attribute index less beneficial?

texts:
I'm studying non-key organizations to know when are they better to use than classical key ones.
With the following SQL queries, which of them takes less benefices from the multi-attribute index on R with A as a first attribute and B as the second attribute ?

SELECT * FROM R WHERE A = 10;
SELECT * FROM R WHERE B = 20;
SELECT * FROM R WHERE A >B;
SELECT * FROM R WHERE A>C;
SELECT * FROM R WHERE C<100 AND A =10;

I would say that those which aren't equality searches are those that may have less benefices from the multi-attribute index, but I'm not sure why...

Here is an an example of a multi-attribute index (which remains as a link for I don't know which reason ... :


texts:
I have designed a db for Drug store which will sell medicine both wholesale and retail. I store each strip, capsule and vial of medicine in a separate fields for retail selling. Is there any better approach for this? As a programmer, I can't think of whether it is necessary to store amount paid by each customer in the order table. And is it necessary to have in and out inventory in separate table? Here is my DB:

Could there be any issue with this db design? Please suggest a better approach.


texts: ALTER TABLE SET LOGGED locked by autovacuum

texts:
metrics is an unlogged table that has around 3M rows, I have just executed the ALTER TABLE metrics SET LOGGED to keep this data logged. But it seems to be locked by some autovacuum running process.

  pid  | usename  | blocked_by |              blocked_query
-------+----------+------------+------------------------------------------
 13462 | postgres | {32090}    | ALTER TABLE sensors.metrics SET LOGGED;

    duration     |                                 query                                  |  pid
-----------------+------------------------------------------------------------------------+-------
 02:03:08.131365 | autovacuum: VACUUM pg_toast.pg_toast_315873283 (to prevent wraparound) | 32090

My autovacuum settings for a 32 core and 98gigs RAM:

 autovacuum_max_workers
------------------------
 12
 autovacuum_vacuum_cost_limit
------------------------------
 -1
 autovacuum_vacuum_cost_delay
------------------------------
 20ms
 vacuum_cost_page_hit
----------------------
 1
 vacuum_cost_page_miss
-----------------------
 10
 vacuum_cost_page_dirty
------------------------
 20

Can i do something to avoid locking, or fix this locking without terminating a the alter table process that is loggin the data?


texts: PostgreSQL storing vectors and computing dot product

texts:
I am working on a small NLP project in Python that involves performing text similarity on documents(news articles, blog posts etc) and I am thinking of storing each document features vector in Postgres, for instance say I have a document with the content Breakthrough on cancer research..., assuming after all the Python preprocessing it would become something like [(1, 0.225), (2, 0.1), (5, 0.11),...] where the first item in the tuple represents the index of the "feature" (in this case a word), and the second item represents its "score/magnitude".

Now if I have a second document with a similar data structure, say query_vector = [(1, 0.525), (6, 0.9), (7, 0.1221),...] I need to run a query like SELECT * FROM vectors_table WHERE dot_product(vectors_table.features_vector, query_vector) > 0.5. What dot_product will do is multiple the "score/magnitude" of both vectors, which in the example above its gonna be SUM(0.525*0.225, 0.9*0, 0.1221*0, ...) (0 because the other vector don't have any score for the feature with index 6 or 7).

I have a working solution but it is way too slow. My most straightforward solution is to store the vectors as jsonb so it'll become something like {1: 0.225, 2: 0.1, 5:0.11, ...} and I also have just the keys on the jsonb in another column (for faster array intersection computation later on) then I have an UDF as such:

CREATE OR REPLACE FUNCTION dot_product_overlap(query jsonb, query_keys anyarray, target jsonb, target_keys anyarray)
RETURNS float AS $$
DECLARE
    row integer;
    result float := 0;
BEGIN
       FOR row IN SELECT * FROM array_intersect(query_keys, target_keys)
       LOOP
          result:=result+((query->>row)::float*(target->>row)::float);
       END LOOP;
    RETURN result;
END;
$$  LANGUAGE plpgsql;

WHERE array_intersect is another UDF to compute the intersection between 2 array (something like & in intarray). What this UDF did was first compute the intersection between two array of keys and then only compute the multiplication if both vectors has the same key and lastly sum it all up.

This actually works perfectly, just that its extremely slow, running a query against a table with 5000 documents takes ~5s. Now I was wondering is there a more efficient way to store these feature vectors and performing the dot product? I researched a little bit and stumbled upon MADlib cosine_similarity function (which is essentially dot product in this case) but AFAIK it doesn't fit the structure of my feature vectors.


texts: Are there design patterns for naming table fields? For example, I've always used user (table)> email (field), but I see other well-used modes like user (table)> user_email (field). Are there any rules / conventions to follow?


texts: How do I manage SSL credentials for multiple Postgres connections?

texts:
We're setting up Redash and need to connect to multiple Data Sources that are Postgres databases that require SSL connections. I have a postgres.key and postgres.crt file from each database. Redash is running as user redash on Ubuntu 16.04.

What should I do with these keys so that Redash (and the Python psycopg2 library, behind the scenes) can find the proper credentials to connect to the Data Sources?

The best I've been able to do is put one set of them in /home/redash/.postgresql, which is where psycopg2 looks for credentials by default. However, that doesn't allow me to connect to the second database.


texts: Pros and cons of accessing sql server using ssms locally over on the actual server

texts:
I need your help DBAs, I need to know the pros and cons of my question.

We have this new "DBA", who doesn't know about DBA really, he got promoted to DBA temporarily to become an IT supervisor, He does not know any sql and no scripting skills.

He just took out all our developers' access to sql server to connect locally. I want to justify to our director that whatever this guy is doing is not comfortable for us..accessing the server and running the ssms.

Btw, he just changes access without even sending any courtesy emails. I need your advise on whether I'm wrong. I'm a developer for this company and for my past 10yrs, I've never worked in a company where you actually run your SSMS on the actual server.

Here's what I gather and my opinion.

Risk to accidentally rebooting the SQL Server.
Data security on the server.

I wish to add more so I can present this to my Director, I'm just not comfortable doing non-server stuff on the server period.


texts: How to implement master-master replication in PostgreSQL?

texts:
I am working on a project which requires my database client to scale horizontally for higher availability but it is required to use RDBMS for our solution. Therefore, we want to implement master-master replication for PostgreSQl.

My concern is the usage of paid licensed plugins as we want to refrain from using it. We wish to implement it ourselves for extra credit but if there is any open source plugins we are open to it.

Next concern will be to be aware of the best practices to implement it. It'd be nice to know else self exploration will be the key.


texts: What is the SQL command to get 20 closest points from some center location

texts:
There are 1.6 million rows.

Must not compute distance for all rows.

Spatial index is available

This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added my spatial index. Then what? What's the SQL command?

If you can point me to a web it'll be great.

    BusinessID  varchar(250)    utf8_unicode_ci         No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
    Prominent   double          No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
    LatLong     point           No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
    FullTextSearch  varchar(600)    utf8_bin        No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
With selected: Check All / Uncheck All With selected:
Print viewPrint view Propose table structurePropose table structureDocumentation
Add new fieldAdd field(s) At End of Table At Beginning of Table After
Indexes: Documentation
Action  Keyname Type    Unique  Packed  Field   Cardinality Collation   Null    Comment
Edit    Drop    PRIMARY BTREE   Yes No  BusinessID  1611454 A
Edit    Drop    Prominent   BTREE   No  No  Prominent   0   A
Edit    Drop    LatLong BTREE   No  No  LatLong (25)    0   A
Edit    Drop    sx_mytable_coords   SPATIAL No  No  LatLong (32)    0   A
Edit    Drop    FullTextSearch  FULLTEXT    No  No  FullTextSearch  0

Note:I am not dealing with lines or polygons, only points. I need more direct samples. Also I am not interested in points within a circle. I want 20 closest points. The fact that those points are within a circle or not is irrelevant. (Actually I will use filter, however, that's latter)


texts: PostgreSQL: Improving performance of data insert

texts:
I would like to migrate all data from DB2 into PostgreSQL. How should I configure PostgreSQL for best performance during the initial migration (loading the data with INSERT statements)?
Should/could I desactivate WAL archiving?


texts: Filegroup design for Document Table for BLOB Storage

texts:
I have a table for storing documents as VARBINARY types in my database (I'm not using filestream). My database is setup with only one data file, but I am wondering if there is a benefit to creating a secondary data file/filegroup and moving that table to the new filegroup. Would this provide any speed or efficency benefit for the database? I'm not looking to secondary move the files to a separate disk, so I know that wouldn't benefit me in that area.

Also I'm using SQL Server 2008 R2.


texts: How to use OPENQUERY to properly execute a Stored Procedure that updates a linked server table?

texts:
I'm having a hard time trying to figure this out. I'm using OPENQUERY to execute Stored Procedures on a linked server. I managed to find a way to do it, by using the following:

SELECT *
FROM   OPENQUERY(
[LINKEDSRV\SQLSRV],
'SET FMTONLY OFF; SET NOCOUNT ON; EXEC [Database_Name_Example].Data.usp_GetNames 5,''S''')
GO

The reason I use SET FMTONLY OFF and SET NOCOUNT ON is because when I tried the code above without them, this message appeared:

  Msg 7357, Level 16, State 1, Line 1 Cannot process the object "EXEC
  [Database_Name_Example].Data.usp_GetNames 5,'S'". The OLE DB provider
  "SQLNCLI" for linked server "LINKEDSRV\SQLSRV" indicates that either
  the object has no columns or the current user does not have
  permissions on that object.

Reading a lot online helped me find the answer. This blog for example: http://www.sommarskog.se/share_data.html#OPENQUERY

So far so good, until I stomped into a Stored Procedure that use SELECT and UPDATE some tables on the linked server. When I execute the OPENQUERY I get the results from the SELECT statement but I noticed that it does not update the table.

The Stored Procedure goes like this:

CREATE PROCEDURE Data.usp_GetNames (@year tinyint=NULL, @online char(1)=NULL)
if @online = 'S'
  BEGIN
    select nam_codig, nam_desc from Names
    where nam_status = 'P'
    order by nam_date

    UPDATE Names
    SET nam_status = 'S'
    where nam_status = 'P'
END

When I execute it directly in the linked server it updates the table, so the second time I execute it, there will be no more nam_status = 'P'

But if I execute it with OPENQUERY the results are the same, it doesn't UPDATE it just select the data.

What can I do to allow the permissions?

Thank you!!!


texts: SSMS How do I open a new Query Window using Same Connection Instance of current Window

texts:
Coming from Oracle I have discovered that SSMS connects slightly different than Oracle's SQL Developer.  Oracle you click ONE connection and then all tabs you open then use that Connection.  SSMS creates a completely new Connect/Instance for each window/tab I open.  I don't want this.  TWO pet peeves come to mind with this behavior I am trying to get around.

  View affect of Script before COMMIT in another Window

I want to be able to run a script in one window... and in another Window have the ability to Query the Non-Committed data to simply view the results... Currently I have to use the SAME Window I ran the script in order to simply VIEW the affect. (Commit/Rollback buttons come to mind - Oracle I miss you sometimes).

  View data stored in #TempTable from another Window

I am creating a #TempTable using a script... but want to query it's data from another Window.  Currently when I open another window (Unlike Oracle's SQL Developer) it opens the window using a new connection/instance.  How do I query the data in #TempTable from another window rather than the window I ran the script in?

The above is just simply for organizational reasons... it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran.

If I'm doing something wrong or missing some Point please let me know.


texts: PostgreSQL pgpool-II replication mode doesn't work?

texts:
I'm experimenting with PostgreSQL and pgpool-II and trying to setup native replication mode on pgpool. The pgpool documentation says In the native replication mode, Pgpool-II is responsible for synchronizing databases.. So I didn't change any configuration on PostgreSQL, just set up two nodes via Puppet, made proper firewall configuration, so I can directly connect to nodes from other hosts on the network. pgpool-II logs seem Ok, don't show any obvious errors and pgpool starts normally, it can even connect to one of the nodes Actually it always connects to backend_hostname0. I even don't see any messages about replication itself. And when I stop the backend_hostname0 it seems like it doesn't see my second node and says: DETAIL:  all backend nodes are down, pgpool requires at least one valid node.

My pgpool configuration is like the following (I just copied that standard example config and added my posgresql backends):

replication_mode = on

backend_hostname0 = 'pg2.example.com'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'localhost'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

Maybe I missed something, can't get this thing to work.
Thank you in advance.


texts: How to export Oracle database without data

texts: I want export my Oracle database with its structure such as tables, schema, users, tablespaces, datafiles and everything but without the contained data.


texts:
I have two SQL Server 2016 installations configured in a mirror with a separate server as the witness. We've been running some failover tests and every test has succeeded except the following two scenarios. Does anyone know why these scenarios wouldn't result in a failover?

Dismounting the storage that the database files reside on the primary server will not fail the server over. I thought the witness would notice that the files do not exist anymore and fail over?
Throttling the network down to 1kbps on the primary server disconnects the mirror on both the primary and secondary. I would think that the witness would lose connectivity to the primary because the network is so slow and fail over but instead both servers go to disconnected status.

Has anyone ran into any of these issues?


texts: How to get SQL for DDL commands in Sql developer?

texts:
In Sql developer it is possible to get DDL for an existing table -

However, how do I get SQL for operations performed through UI? For example, how do I see DDL generated when I alter the table to drop a column? Sql developer does the drop operation and does not show me anything.

Edit:
I add a column -

And then switch over to SQL tab -

Not what I want.


texts: Need help with difficult table modeling situation

texts:
Database newbie here with no formal training, trying to train myself.  I was looking for a more difficult case to model and came up with following.
Hypothetical ice cream shop where there are containers (small cone, large cone, small, medium and large cups) each with a price and a number of scoops for each.
There are flavors with a price per scoop, and multiple flavors can be used for containers that can have multiple scoops.
There are toppings where just for simplicity I am limiting to one topping per final product including none.
I cannot understand how to get from these product parts to the Order_Details table.  I am used to looking a single Products table that relates to an Order_Details table that relates to Orders Table, but I cannot comprehend how to get from the Containers, Flavors and Toppings tables I have designed to the Orders_Details table.
I am requesting some ideas on the best way to model from my product parts to the Order_Details table.

Please advise.


texts: Update Column in table on midnight daily

texts:
I am designing a simple 1 table database in mySQL that will have two columns. One column will be the name of a specific restaurant on the webpage I will be using this database with. The second column will be number of reservations at each restaurant.

I'm a mySQL rookie and i was wondering if there is a way to set up a script or cronjob that everyday at midnight would run an UPDATE command against my table to reset all reservation values to 0.


texts: How can I return top 10 rows from each state

texts:
How can I return the top 10 rows for each category?

Say I have a table named xyz. It has a column called state, in the political unit sense of the word.

I want the top 10 rows from each state. Do I use case so that one can see from which state the row is?

I am using SQL Server 2012.

The top 10 would be from FCFS, the order in which they are entered.

Columns are

FirstName, LastName, Address, city, state, zip, phone etc.

For example, I have rows in my table from states AL, CT, NY etc. I want the top 10 rows based on FCFS from each state.


texts:
What is 'SQL Server Database Primary Data File (.mdf)' equivalent for mysql.

I am using 'Toad for mysql' (Vesion 7.7) tool


texts: MySQL server parameters default empty string to null

texts:
How can I set MySQL server parameters to default empty strings to null on insert?

is this possible?


texts: How to change the AES key of a specific column in mysql?

texts:
I have a test table in which some columns are encrypted using an AES key, now I think of changing the AES key of those column?

I used the following query to set an AES key:

UPDATE test_encrypt SET first_name = AES_ENCRYPT(first_name, 'g50l2');

Now, I want the change the AES key, Is there any way?


texts: Run query dynamically in store procedure oracle

texts:
Problem

Select all the tables of database where column match than pass table name to next query using loop. If column name and column values matches than return true and exist for loop

CREATE OR REPLACE PROCEDURE TEST
(
 NAME IN VARCHAR2 ,
 ID IN NUMBER,
 RE OUT SYS_REFCURSOR
) AS

BEGIN
OPEN  RE FOR SELECT A.TABLE_NAME FROM
user_tables A JOIN user_tab_columns C
ON C.TABLE_NAME = A.TABLE_NAME
WHERE C.COLUMN_NAME = NAME;

FOR RE IN LOOP

v_Sql := 'SELECT COUNT(*) FROM '|| LOOP.TABLE_NAME || 'WHERE COLUMN_NAME =
ID';
EXECUTE IMMEDIATE v_Sql
IF v_Sql%ROWCOUNT > 0 THEN
return true;
EXIT

END LOOP;

END TEST;

For more understanding the problem

//Get all the tables of database where campus_id is exist in any table of
 database
 Campus, Class, Section (3 tables found)
 Apply forloop on the records
 Select count(campus_id) as total from (table name using loop) where
 campus_id = 1(value
  pass)
 if(total > 0){

  Exist for loop and return true
 }
  else{
  Again iterate the loop on next value
 }


texts:
I'm a dba new to postgresql. I have Linux RedHat 7 without openshift.

I have to install some DB on 9.4 version with postgis.

My sysadmin has published the following repositories :

RHEL 7 server
RHEL 7 EPEL
yum.postgresql.org for 9.2, 9.3 and 9.4

I managed to install :

Postgres 9.2 from postgresql.org repo with postgis 2.1 for PG9.2
Postgres 9.4 from RH repo

And the issue is when I want to have PostGis for PG9.4, I can't find it anywhere in the repos.

Which repo, which package should I have to get PostGis for PG9.4 ?

Thanks


texts: MariaDB Update Column Values Based on Another Column's Value

texts:
I have a column 'StormID' 'Basin' which both hold text based values.

StormID   Basin
EP012018   NEP
EP022018   NEP
CP012018   NEP <--  I want this value to change to NCP if the first 2 characters are 'CP' in the StormID column.

Thanks for any suggestions.
Regards,
Bryan


texts: Part of parallel execution plan has serial portion

texts:
I have a big linq to sql query which runs in parallel mode.
It is returning results in 8 seconds and it is pretty good.
I noticed in the execution plan that the extreme right part(from where execution plan is read) of the execution plan has a serial portion.
I checked the execution plan and i see that the serial part is calculating some expressions such as the where conditions Start Date and End date is applied and calculated.
But i don't understand why it is being in serial mode.
AS per the screenshot,it says Scalar Operators[Expr1169] and Scalar Operator(Expr1170)

As you can see in the next screenshot, after the expressions are calculated and after from Nested Loops ,the execution goes into Parallel mode.
I am trying to understand the serial part of the query ,because i have optimized the query few times to strictly run in parallel mode.
I have removed some scalar functions earlier and other db objects which can cause serial execution.

Screenshot 1

Screenshot 2

Please find the anonymized execution plan.
Execution PLan

Additional Info:

Statistics are updated
Index Fragmentation is taken care off
96 GB out of 128GB is used by SQL Server.
SQL Server 2016

Statistics are updated.


texts:
Currently, in our project, we use dedicated servers for our PostgreSQL database.

In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to hardware configuration. What we are looking for is a cloud solution with native PostgreSQL support.

Here is the list of desired features:

automatic database configuration adjustment depending on selected resources
replication setup out of the box

So what are the options and the best pick for such a service?


texts: MySQL exception after upgrading from Java 6 to Java 7

texts:
I've been trying, for a long time, to find a solution for the issue.

Recently I've updated my JDK from 6 to 7. Now my grails app doesn't start up, I keep getting the follow exception:

Caused by SQLException: Unable to initialize driver properties due to java.lang.IllegalAccessException: Class org.springsource.loaded.ri.ReflectiveInterceptor can not access a member of class com.mysql.jdbc.ConnectionPropertiesImpl with modifiers "private"
->> 1078 | createSQLException in com.mysql.jdbc.SQLError
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    989 | createSQLException in     ''
|    975 | createSQLException in     ''
|    920 | createSQLException in     ''
|   2819 | initializeProperties in com.mysql.jdbc.ConnectionPropertiesImpl
|   3490 | initializeDriverProperties in com.mysql.jdbc.ConnectionImpl
|    823 | <init> .  in     ''
|     47 | <init>    in com.mysql.jdbc.JDBC4Connection
|    411 | handleNewInstance in com.mysql.jdbc.Util

Any tips?

I am using grails 2.1.4


texts: What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

texts:
Many times I need to write something like the following when dealing with SQL Server.

create table #table_name
(
    column1 int,
    column2 varchar(200)
    ...
)

insert into #table_name
execute some_stored_procedure;

But create a table which has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of sp_helppublication has 48 columns! I want to know whether there is any easy way to do this.

Thanks.


texts: What are the files with the extensions .1, .2 or .3 in the data directory

texts:
Why are there files ending in .1 .2 .3 in my PostgreSQL database folder?

-rw------- 1 postgres postgres 1073741824 Oct 11 09:32 412rw95.2
-rw------- 1 postgres postgres   67100672 Oct 11 09:32 41295.3
-rw------- 1 postgres postgres 1073741824 Oct 11 09:07 41296
-rw------- 1 postgres postgres 1073741824 Oct 11 07:27 41296.1


texts: Create failed for Index 'ClusteredColumnStoreIndex'. Timeout expired

texts:
I am creating Clustered Column Store Index in SQL Server 2014.

I am getting error as

  "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)".

I set

EXEC sp_configure 'remote query timeout', 60000;
reconfigure
EXEC sp_configure

Row Count = 304969603
Data space = 88,812.266 MB


texts: Are iSeries AS/400 (DB2?), Lotus 1-2-3, and Excel date/time formats the same?

texts:
Are the formats for dates/times from AS/400, to Lotus 1-2-3 that carried over into excel...with the date being the number of days since 1900-1-1 when mistaking 1900 for a leap year as an integer, and the decimal portion being the time.

Are they all the same format?


texts: SQL Server 2008: how much RAM memory should SQL Server use in a 8GB RAM server?

texts:
We have SQL Server 2008 and 8GB RAM in a customer, hosted in a Windows 2008 R2 SP1, and consider the following amount of free memory:

and consider the following running programs:

Scenario: Users use a website which saves data in this server in a SQL Server database. No more than 5 users.

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use? Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

  SQL Server version: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0
  (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft
  Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build
  7601: Service Pack 1)

  Windows version: Microsoft Windows [Version 6.1.7601]


texts: Adding LIMIT changes PostgreSQL plan from good to a bad one

texts:
I have a query on a typical EAV schema:

SELECT contacts.id
FROM contacts
         LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id
                          FROM contacts_values
                          WHERE contacts_values.field_id = '\x000000000000000000000000'
                            AND contacts_values.field_name = 'facebook'
                            AND nimble_contact_value_normalize(nimble_skip_long_values(contacts_values.value)) =
                                nimble_contact_value_normalize('http://www.facebook.com/jon.ferrara')) AS anon_1
                         ON anon_1.company_id = contacts.company_id AND anon_1.id = contacts.id
         LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id
                          FROM contacts_values
                          WHERE contacts_values.field_id = '\x000000000000000000000000'
                            AND contacts_values.field_name = 'last_name'
                            AND nimble_contact_value_normalize(nimble_skip_long_values(contacts_values.value)) =
                                nimble_contact_value_normalize('Ferrara')) AS anon_2
                         ON anon_2.company_id = contacts.company_id AND anon_2.id = contacts.id
         JOIN contacts__aggregated AS contacts__aggregated_1
              ON contacts__aggregated_1.company_id = contacts.company_id AND
                 contacts__aggregated_1.contact_id = contacts.id AND contacts__aggregated_1.field_name = 'names'
WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
  AND (anon_1.id IS NOT NULL OR anon_2.id IS NOT NULL)
ORDER BY contacts__aggregated_1.value ASC
LIMIT 30 OFFSET 0;

My problem is that the LIMIT clause in this query makes the planner choose a bad plan with nested loops: https://explain.depesz.com/s/Mute. Running the same query after SET ENABLE_NESTLOOP TO OFF I am getting a much more efficient plan: https://explain.depesz.com/s/b5kn. Removing the LIMIT from the query results in a similar plan: https://explain.depesz.com/s/wDqE.

One thing that concerns me is that the cost of the LIMIT node in the bad nested loop plan is just a fraction of the cost of its subnode. But for the better merge join plan LIMIT node has the same cost as its subnode. How could it be this way? And what can I do to make the planner pick up a better plan?

We are running PostgreSQL 10.10.


texts: TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing Query

texts:
TSQL MSSQL 2008r2

I am trying to tune a poor performing query.

For 3 out of 4 of the largest tables (several millions of rows) being queried (index seeks), the plan has decided that the estimated number of rows = 1. I think this is where the issue is.  The number of logical reads is crazy high which I think is as a result.

I don't know what is making the query engine assume this is the case.  Statistics are updated and I'm not using any user defined functions. The query engine is not trying to "convert_implicit" any columns.  So what do I need to look at, change to make this query more efficient.

The plan is here, https://www.brentozar.com/pastetheplan/?id=S18GOLFig

The variables are missing from Paste the Plan. Here they are:

DECLARE  @1stDay_StartExtract   DATE
        ,@LastDay_EndExtract    DATETIME
        ,@WhenProcessLastTime   DATETIME;

SET @1stDay_StartExtract= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0);
SET @LastDay_EndExtract = DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,0));

DECLARE  @dtStartDate_LT DATETIME= DATEADD( DAY, -1,@1stDay_StartExtract )
        ,@dtEndDate_LT   DATETIME= DATEADD( DAY, 1, @LastDay_EndExtract );

Let me know if you need more information.

Thank you in advance.