texts:
I need to create a database which will have below modules:

Items [Food items, stationary]
Items Stock Management [stock in out]
Customers
Items Sale Management

Issue:

The system will have different types of items & so can have different 'Unit of Measurement'. Possible Units of Measurement can be bags, pieces, Kilograms.
Another things is that certain items can be sold as a whole 'unit of measurement' OR 'part of unit of measurement', for example tomato can be sold 2 kilograms or 450 grams.

Questions:

How should i handle the 'Unit of measurement'?
What is the best way to keep the stock in out?

EDIT:

For stock in out, i am thinking about to have a separate stock transaction table that will store any in/out transaction.
I am still not clear about the 'Unit of measurement'.

Please forgive me if my question is not clear.

Thanks for your valuable time & help.


texts:
Name     Null     Type
-------- -------- ---------
ID       NOT NULL NUMBER(4)
GROUP_ID          NUMBER(4)
TEXT              CLOB

There is a btree index on group_id. Here's how many rows each group_id has and the corresponding percentage:

GROUP_ID               COUNT                  PCT
---------------------- ---------------------- ----------------------
1                      1                      1
2                      2                      1
3                      4                      3
4                      8                      6
5                      16                     12
6                      32                     24
7                      64                     47
8                      9                      7

I ran this

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST', cascade=>true);

Which, if I understand correctly, will gather stats for the optimizer.

Now, I remember seeing that Oracle will not use the index and perform a full table scan instead if it's retrieving more than 5% or so of all rows. However, when I ran this query, it only started performing a FTS when group_id was 7, which has 47% of all rows.

Is this the way it's supposed to be?


texts: How to update Column related to Row Number

texts:
Is there any easy way to update Column by Row number not a PK
ex:

UPDATE contact m
SET    ContactNumber = sub.rn + 500
FROM  (SELECT Id, row_number() OVER (ORDER BY Id) AS rn FROM contact) sub
WHERE  m.Id = sub.Id;


texts: "Too many connections" when running MySQLDump

texts:
MySQL 5.5.x on Windows, 4GB RAM.

I have a Wordpress Multi-Site install.  The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now)

I am running mysqldump early mornings, and from my monitoring tools, I am seeing that my other db sites on the server, along with the WPMU site is going down for about 10-15 min during the time when the WPMU db is being dumped.  The error is "Too many connections"

MySQL is currently set to 100 connections.

What is my best path forward to mitigate the downtime? Up the connections, and throw more RAM at the box?


texts:
Definition:

table_A                table_B
id,description         id,description,id_table_A

Column id_table_A is a foreign key to the primary key of table_A and is not mandatory.

Sample data:

table_A
1,some_desc_1
2,some_desc_2

table_B
1,some_desc_1,1
2,some_desc_2,2
3,some_desc_aaa,null
4,some_desc_bbb,null

I need to write a query that shows me all the records, including the records with the foreign key set to null.  What type of join is this?  I'm not able to show the records with null foreign key using an INNER JOIN.


texts: Failure with ongoing replication from SQL Server RDS using AWS DMS

texts:
I'm trying to replicate from an SQL Server RDS database using DMS, and running into problems.

I am able to load all data from one table into the destination (~1M rows) as a single 'Migrate existing data' Task. When I run a second 'Replicate data changes only' Task, it runs for a few minutes and then fails.

No rows are copied into the destination by the second task before it fails.

These lines in the error log seem relevant:

  [SOURCE_CAPTURE ]W: Capture functionalities could not be set. RetCode:
  SQL_ERROR SqlState: 42S02 NativeError: 208 Message: [Microsoft][ODBC
  Driver 13 for SQL Server][SQL Server]Invalid object name
  'sysarticles'. Line: 1 Column: -1 (sqlserver_log_utils.c:1712)

  [SOURCE_CAPTURE ]E: Bad Envelope : , Lsn=00050649:000a3cb1:0166,operation=5,TxnId=0000:34915a92,Tablename=COMMIT,PageId=,slotId=0,timeStamp=2018-08-01T08:15:30.233,dataLen=0,
  LCX=99, , >Invalid data context / LCX Code encountered for TXN
  operation. [1020203] (sqlserver_log_processor.c:401)

The source database is Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)

I've tested this against a number of destination databases (MySQL, PostgreSQL, SQL Server) but always get the same result.

How can I get ongoing replication to work? Is there anywhere else I should look?


texts: Migrating DB, SSIS packages, SSRS Reports from 2008 R2 to 2012

texts:
I have been given the task of planning our BI department's infrastructure upgrade from SQL 2008 R2 to SQL 2012. This process will include updating our data warehouse (database), reporting services/reports (native mode), and SSIS packages. This is my first ever attempt at planning/executing a SQL migration or upgrade. I have found some information regarding upgrading/migrating each of these individually, but not specifically in a context where all three are dependent on each other and all three will be upgraded.
Goals are:

Minimal down time.
Testability and fall back in the event of a failure or the unforeseen.
Ideally we’d like to be able to handle this migration in manageable chunks over time, with some parts going live before others.
When everything is successfully migrated/tested/live a final cutover occurs
and then the old environment goes away.

We have opted to migrate everything rather than doing any in place upgrades. A new server has been setup for us with SQL 2012 installed, incl. a fresh instance of Reporting Services in native mode, SSDT, etc.
My first question is: Given the goals above, what is the correct order to approach the migration of each for example Database > Reporting services reports > Integration services packages?
My second question is: Can I use Mirroring to facilitate this process, is it worth the extra complications? I saw it mentioned here (https://dba.stackexchange.com/a/58696/45140) as part of an answer, but the OP already had mirroring setup, we don’t. Also if this is a good idea, does anyone have a link to a good guide of setting up mirroring specifically to assist with a migration?


texts: use SUM on foreign keys where it matches rows id?

texts:
I have this table:

CREATE TABLE buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);

then this view:

CREATE VIEW v_rooms AS
SELECT buildings.id AS building_id,
    buildings.name AS building_name,
((jsonb_array_elements(jsonb_array_elements(buildings.document -> 'levels'::text) -> 'rooms'::text) -> 'meta'::text) ->> 'sqft'::text)::numeric AS sqft
   FROM buildings
  GROUP BY buildings.id;

finally this view:

CREATE VIEW v_buildings AS
SELECT buildings.id AS building_id,
    (buildings.document -> 'meta'::text) ->> 'name'::text AS name,
    sum(v_rooms.sqft) AS sqft
   FROM buildings,
    v_rooms
  GROUP BY buildings.id
  ORDER BY buildings.id;

All is well, except 'sqft' on v_buildings is returning the SUM of all the rows in v_rooms. I want to calculate SUM() where v_building.building_id  == v_rooms.building_id.


texts: Recover data from .mdf file, and no correct version of SQL Server

texts:
I have an 80gb .mdf file that I need to open and get the data out of.

It was on a computer running SQL Server 2008 R2 Express that died, but I was able to pull the file off the half dead HDD. I don't know how this file passed the 10gb limit imposed by the express edition of SQL Server, but it did.

So far I've installed trial versions of SQL Server 2008 and SQL Server 2012, but I was not able to attach the .mdf file to either instances.

Does anyone know where I can find a copy of 2008R2? Microsoft provides downloads for the other two versions, but I cannot find an R2 version anywhere.

Alternatively, is there another, better, way to get the data out of this database?

I have the log file as well.

Edit:

I have a full version of SQL Server 2012. Run 2012 Management studio as administrator and when I run this:

USE [master]
GO
CREATE DATABASE [Orange] ON
( FILENAME = N'C:\Users\Public\Documents\Orange.mdf'),
( FILENAME = N'C:\Users\Public\Documents\Orange.ldf')
FOR ATTACH
GO

I get:

Converting database 'Orange' from version 661 to the current version 706.
Database 'Orange' running the upgrade step from version 661 to version 668.
Database 'Orange' running the upgrade step from version 668 to version 669.
Database 'Orange' running the upgrade step from version 669 to version 670.
Database 'Orange' running the upgrade step from version 670 to version 671.
Database 'Orange' running the upgrade step from version 671 to version 672.
Database 'Orange' running the upgrade step from version 672 to version 673.
Database 'Orange' running the upgrade step from version 673 to version 674.
Database 'Orange' running the upgrade step from version 674 to version 675.
Database 'Orange' running the upgrade step from version 675 to version 676.
Database 'Orange' running the upgrade step from version 676 to version 677.
Database 'Orange' running the upgrade step from version 677 to version 679.
Msg 5570, Level 23, State 3, Line 1
FILESTREAM Failed to find the garbage collection table.

It created the database (shows up in Management studio) and now says "Recovery Pending".


texts: Query notification equivalent for SQL Server 2016

texts:
I was looking for query notification to get notified on multiple client for a specific table changes. It seems that this feature has been removed in SQL Server 2008 R2. Documentation is not very clear about that.

When exactly has this been removed (which version exactly)?

Is there any other way to manage that without additional components?


texts: Oracle database installation directory using SQL

texts:
I have server that has had Oracle installed by someone else. I am able to connect to it using SQL developer and do all the operations, but I want to know the location where Oracle is installed.

I need this details to execute exp, imp, expdp, impdp, etc. commands using putty to perform import/export and data dumps.

Is there any way I can get the Oracle installation directory details using SQL command? Does Oracle store the installation path in a database?


texts: Increasing mysql ram usage to decrease disk activity

texts:
I would like to know if the increasing the following parameters will improve the mysql performance.
| query_cache_size               | 16777216             |
| query_cache_limit              | 1048576              |
| table_open_cache               | 2000                 |
| table_open_cache_instances     | 16                   |
| thread_cache_size              | 8                    |
| host_cache_size                | 279                  |

Right now, the innodb_buffer_pool_size is 2G.
UPDATE:
The output of all variables is available at pastebin. I would like to decrease IO tasks as I have 32GB of memory and it is find to set 16 GB to mysql.


texts: Do I need a separate Id column for this "mapping" table?

texts:
I have a table of Producers and a table of Products, both of which are of the form:

Id - int, Primary key
Name - nvarchar

A Producer can carry multiple Products, so I was going to create a table called ProducerDetails that would have:

ProducerId - int, Foreign key to Producers.Id
ProductId - int, Foreign key to Products.Id

Then I started to question myself, so I thought I'd ask the experts.  Would it be better database design to have an additional Id (int, Primary key) column in my ProducerDetails table?  Or is that unnecessary?

I'm using SQL-Server 2008 R2 if that make any difference at all.

EDIT - The relationship between these tables would be many-to-many I believe, sorry I didn't make that clear.  A producer can carry multiple types of products, and the same product could be produced by multiple different producers.

I apologize if this question is overly simple, referential integrity / database design is not my strongsuit (although I'm trying to improve that).


texts: Pulling autogrowth events by file name from default trace

texts:
I use below query to get auto-growth event occurred to databases in a sql server.

DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1
SELECT COUNT(*)as no_of_autogrowths,
sum(duration/(1000*60)) as time_in_min,
Filename
FROM ::fn_trace_gettable(@trcfilename, default)
WHERE (EventClass = 92 OR EventClass = 93)
GROUP BY  Filename

It outputs number of auto-growths, time taken for auto-growth and logical name of the file.
But I want physical name of the file (mdf and ldf file name) instead of logical name.I  don't know whether from sys.traces I will get physical name or please help me with an alternate way to do it.


texts: Can a slave with a dynamic ip update the master

texts:
How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP.

The slave will not be always online.


texts: how to grant a user permission to create databases?

texts:
I use phpMyAdmin powered by Mamp (free version) to manage Mysql databases.

I need to know how to grant a user permission to create databases.


texts: Reducing database size to restore to SQL Server Express

texts:
I have a SQL Server 2008 R2 database, about 15 GB.

I want to copy it for a partner, who is using SQL Server 2008 R2 Express.

I deleted many tables and rows, and now I am sure that the data is smaller than 2 GB.

I make a backup, send to the partner, he tries to restore it but he receives an error:

  CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database

What did I do wrong?


texts: Can I create index on a table in a MySQL database while in use

texts: And if I can, any reason (performance/stability) why I shouldn't?


texts: Easiest way of porting data into a database

texts:
Recently, I received a lot of information from one of my friends who made a quiz type program.  He asked me to help port it into an Access database.

His program places all the info in a grid; around 3000 fields full of info, and we need to port it over to Access.

Is there a program that will read field-by-field and insert the data into Access, and then automatically continue to the next field?

The grid also has a copy button, but it copyies everything without grid, so every line looks kinda like this:

Info1:herestheinfo Info2:herestheinfo, and so on, is there any easy way of porting this to an Access database?


texts: Differences between estimated and actual execution plans

texts:
I'm new to SQL Server and I want to tune my first query.

In SQL Server 20xx, what is the difference between "display estimated execution plan"  and "include actual execution plan" ?


texts: SQL import data when both source and destination has data and identity column

texts:
I am using Import and export data option to import data from one database to another database.
Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.

Now when I import data I get error like Failure inserting into the read-only column

So I enabled enable identity insert check-box and tried to import data. In this case I got error as shown below

Copying to [dbo].[Misc_Data] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_Misc_Data'. Cannot insert duplicate key in object 'dbo.Misc_Data'. The duplicate key value is (1).".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (50)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (50)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Misc_Data" (37) failed with error code 0xC0209029 while processing input "Destination Input" (50). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

I think when i enable identity insert on SQL try to insert rows with values same as in souce table for identity column and gives error.

So how can I import data in this case. When having data in both source and destination and identity column is set as primary key.

EDIT
The query I used to import table except one column(identity column) is shown below

SELECT [Name]
  ,[Status]
  ,[Last_Checked]
  ,[RecievedDate]
  ,[RecptNo]
  ,[Date2]
  ,[processedDate]
  ,[ind_num]
  ,[AcNo]
  ,[returnType]
  ,[sign]
  ,[oldStatus]
  ,[Acno_old]
  ,[Remarks]
  ,[FileName]
  ,[Status_checked]
  ,[Date_Time]
  ,[By]

FROM [dbname].[dbo].[mytable]

In this case i get error like

- Pre-execute (Error)
Messages
Error 0xc0202004: Data Flow Task 1: The number of columns is incorrect.

(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
 (SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task 1: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

Both tables have same structure and same datatypes. Still i am getting error.


texts: Efficient approach to Query DB with data in text file

texts:
I've got an oracle DB set up and want to Query it after I receive data in a text file. Number of product-names present in text file are around a million.

After Searching a lot I've found 2 ways of doing same but I'm not sure which'll be more efficient.

Use SQL loader to load data in a table then query using where in or a join
Query DB for each product name in loop.

The second option is a consideration just because I think insert(using loader)+select will be more expensive than only selects.

P.S.: I would prefer less load on DB server rather than less time.

File contents are something like:

ABC
BCD
FGS
LTB
.
.
NHL

I've to query db as

Option 1:

Run a sqlloader on file products.txt and load contents into TMPTABLE.
SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN (SELECT PRODUCT_NAME FROM TMPTABLE);

Option 2:

Foreach $productname in products.txt:
    SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN=$productname


texts: Difference between Archive destination & db_recovery_file_dest

texts:
What is exact difference between Archive destination & db_recovery_file_dest on Oracle Database?

SQL> show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      C:\app\Azar\fast_recovery_area
db_recovery_file_dest_size           big integer 6930M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            C:\app\Azar\Arch
Oldest online log sequence     320
Current log sequence           322


texts: How to issue a DML with WHERE clause comparing a value which is trigger generated in table?

texts:
Suppose I have a table with CREATE,

CREATE TABLE TEST (
    year int,
    month int,
    date int,
    hr int,
    min int,
    sec int,
    timestamp timestamp,
    value double
);

CREATE FUNCTION timestamp_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$

    DECLARE datestr TEXT ;
    DECLARE timestr TEXT ;
    begin
        datestr :=  new.year || '-' || new.month || '-' || new.date || ' ' ||  new.hour || ':' || new.min || ':' || new.sec;+
        new.timestamp := datestr :: TIMESTAMP
        return new;
    end;
$$
CREATE TRIGGER timestamp_insert BEFORE INSERT OR UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE timestamp_insert();

Now I want to update a certain row based on the timestamp currently on the table and that from a new set of data. Something like,

UPDATE TEST SET value = ? WHERE timestamp < "generated timestamp from a new set of (year, month, date, ...)"

Is it possible to do something like this in SQL or do I need to programmatically genrate the timestamp and then simply pass that value in SQL?


texts:
This is what happens when I try to query as DBA:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 10 22:28:00 2014

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

Enter user-name: sys as sysdba
Enter password:

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

SQL> select count(*) from dba_users;

select count(*) from dba_users
                     *
ERROR at line 1:   ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database open;

alter database open
*
ERROR at line 1:   ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [824], [95], [98], [], [], [], [],
> [], [], []

please help to restore my db...


texts:
I'm a huge fan of a lot of the Redgate tools, however at my current workplace they are very paranoid over anything interacting with live servers (in particular affecting performance).

So my question is specifically on their SQL Search tool, what impact does it have on a server? I'm assuming that there will be:

Initial indexing (what does it interact with, idea on impact on server, can it be customised to not index live servers)
Subsequent connections/refreshes (how is it determining if there is a change to be re-indexed and what performance/load impact that will have on a server)?


texts: MySQL: ERROR 126 (HY000): Incorrect key file for table + slow logical backups

texts:
I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error:

mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')
    -> ;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it

-

# df -h /tmp/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G   12K  2.0G   1% /tmp
# df -i /tmp/
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
tmpfs                2041621       7 2041614    1% /tmp
# mount | grep /tmp
tmpfs on /tmp type tmpfs (rw,size=2048M)

Please note that the same query works fine when '/tmp' dir is mounted with ext4 file system.

EDIT:

Server_01

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
1876765

but this also happened on server with much less tables:

Server_02

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
49514

I was using this query to list all databases but as it didn't work with tmpfs I've just replaced it with simpler one (SHOW DATABASES...).

I was watching disk space on /tmp mounted with tmpfs and there still was a plenty of space so I'm not sure how it could run out of space?

Basically I've got a problem with logical backups on the server with ~8000 DBs - it takes many hours (~24) to complete this task. I've created a simple BASH script (please see below) and instead of mysqldump I'm using mydumper as initial tests showed that it's much faster.

Backups running very fast initially and then slowing down dramatically:

# ./backup.sh
Backing up database number: 1
Completed in: 0.016
Backing up database number: 2
Completed in: 0.078
Backing up database number: 3
Completed in: 0.074
Backing up database number: 4
Completed in: 0.068
Backing up database number: 5
Completed in: 0.071
Backing up database number: 6
Completed in: 0.060
Backing up database number: 7
Completed in: 0.067
Backing up database number: 8
Completed in: 0.070
Backing up database number: 9
Completed in: 0.065
.....
Backing up database number: 107
Completed in: 10.749
Backing up database number: 108
Completed in: 12.125
Backing up database number: 109
Completed in: 11.313
Backing up database number: 110
Completed in: 11.572
Backing up database number: 111
Completed in: 11.371
.....

Script:

#!/usr/bin/env bash

DATA_DIR="/tmp/mysqlbackup"
LOCKFILE=/tmp/backup.lock
NOW=$(date +%Y%m%d)
COUNT=1

if [ -f "$LOCKFILE" ]; then
        echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"
        exit 0

else

        echo $$ > $LOCKFILE

        if [ ! -d $DATA_DIR/$NOW ]; then
                mkdir -m 700 -p $DATA_DIR/$NOW
        fi

        while read DB; do

                (( COUNT++ ))

                echo "Backing up database number: $COUNT"

                START=$(date +%s.%N)

                mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"

                ELAPSED=$(date +%s.%N)

                printf "Completed in: %.3F\n" $(echo $ELAPSED - $START | bc)

        done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"

        echo "Removing backup dir...";

        rm -rf $DATA_DIR/$NOW

        rm -f $LOCKFILE

        exit 0

fi


texts:
I've recently began learning T-SQL and have found that there are some rules to using single table queries. For instance:

GROUP BY

If aggregate and non-aggregate expressions are used together in a SELECT statement, the GROUP BY clause must be used.

HAVING

The HAVING clause is generally used with GROUP BY

TOP

The TOP statement relies on the ORDER BY clause for logical precedence among rows

ORDER BY

Data returned when using an ORDER BY clause results in a cursor, whereas a table result is returned when not using the ORDER BY clause

I was curious if anyone else had any tips for learning the relationships among the different DML statements.

Thanks in advance!


texts: Possible to use a field within a query in PostgreSQL?

texts:
I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL '2 hours' AND at <= NOW()

This assumes the event is 2 hours long. But I don't know that. The actual length is stored in the minutes field. How can I use that field in my query? Or is there a better mechanism for me? I've unsuccessfully tried things like this:

SELECT * FROM events WHERE at >= NOW() - INTERVAL minutes ' minutes' AND at <= NOW()

One little caveat is that performance does matter, somewhat. There is a large number of events in our database and that is growing fast.


texts: SSIS package version GUID does not stay stable between runs

texts:
We have a large quantity of SSIS packages created in visual studio 2008. Currently running on hundreds of servers.
We are logging the details of each package before it runs.

One of the things we are logging is the PackageVersionGUID to verify if the correct/latest stable version was run each day.
However when we look at our logging, each run shows a different PackageVersionGUID.

According to the Microsoft documentation :

  the version GUID generated when the package is first created.

Why then do we see different GUID's each time, and in fact don't have a single recurrence of the GUID?

The most blatant example of this is when we run our package in a loop to connect to several servers in succession.
Each time the loop starts, it logs its PackageVersionGUID, and each time the ID is different.

To be clear, we have since replaced the logging with VersionMajor.VersionMinor.VersionBuild which serves our purposes. So I am not asking for a different way to log our package versions.
But I would like to know why the GUID was not a correct approach, and why it changes so frequently (or perhaps a confirmation that the MSDN documentation is simply wrong).


texts:
How to identify the tables needs to de-fragmented by doing a VACCUMM Full or ReIndex (for indexes)
Is there any query to find the free space in between the tuples to find the right candidates to perform the VACCUUM FULL


texts: How Can I clone a database in Microsoft SQL server using PHP?

texts:
I am creating a portal where user can create the b2c or b2b store itself, so I need to clone the sample database to perform the task. It's not looking simple to clone the database in MSSQL. Please help if someone knows the solution.

$sql = "RESTORE DATABASE test2 FROM DISK=N'/var/pathto/TEST.bak'
        WITH REPLACE, RECOVERY
        , MOVE 'TEST' TO '/var/pathto/TEST.mdf'
        , MOVE 'TEST_log' TO '/var/pathto/TEST_log.ldf'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    exit('executedd.');
}

What's wrong with my script?

/**************************************/

After hours of research below script is working but still showing restoring in SQL Server Management studio, check the attached screenshot.

$sql = "CREATE DATABASE test9";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}

$sql = "USE test9";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}

$sql = "BACKUP DATABASE test9 TO DISK = '/var/opt/mssql/data/test9.bak'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}

$sql = "RESTORE DATABASE test9 FROM DISK='/var/opt/mssql/data/test4.bak'
        WITH REPLACE, RECOVERY
        , MOVE 'test4' TO '/var/opt/mssql/data/test9.mdf'
        , MOVE 'test4_log' TO '/var/opt/mssql/data/test9_log.ldf'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}
exit;


texts: What is the best way to add optional update in procedure in transaction?

texts:
Currently I have a procedure that performs 4 operations in the Transaction:

Update record in table A
Update record in table B
Insert record into table C
Delete record from table D

The first operation is performed only when one of the parameters of procedure (@IsUpdateA) is equal to 1.
So from business perspective it looks If @IsUpdateA = 1, the update of table A has to be performed, while step 2, 3 and 4 are always performed.
The question is whether it is better (in terms of efficiency/execution plan) to split such procedure into two, i.e.

First procedure will always perform steps 1,2,3,4
Second procedure will always perform steps 2,3,4

And @IsUpdateA will be checked in application.
I can not use procedure in procedure (internal requirement).


texts: When inserting or editing rows ("Edit Top 200 Rows") for a table with a FK constraint, can the editor be configured to select the foreign row, rather than me having to look up the FK?


texts: Is this a good way to store XML data in SQL SERVER 2008?

texts:
I'm doing a feasibility study for storing data in the following structure with SQL SERVER 2008:

-MYTABLE
|ID|RECORDS|BRANCH_OFFICE|MONTH|YEAR

The RECORDS column has the XML data type and it will look like:

<visits>
  <visit id="000112233">
    <costumer>Mr. One Costumer</costumer>
    <date>2011-02-10</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112234">
    <costumer>Mr. Another Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01235</employee>
  </visit>
  <visit id="000112235">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-12</date>
    <employee>MAT01234</employee>
  </visit>
  <visit id="000112236">
    <costumer>Mr. Some Costumer</costumer>
    <date>2011-02-15</date>
    <employee>MAT01235</employee>
  </visit>
</visits>

I'm intending to query the xml column with xquery, is this a good way to store these data?
If i want to get the list of visits occurring in 2011-10, adding a tag with the difference of days between the "date" tag and now, how could it be?


texts: Should I use two databases or two schemas?

texts:
We are currently having one Oracle instance on RAC with one database created. A new requirement came to our database team to temporarily (1-2 months) host a new application that is independent of existing one and after that we will migrate it to a new RAC.

Our technical teams discuss two options: the first suggestion is to create a new database (instance) for that application and the other is to have another schema on the same database.

From performance (how much is the overhead when creating a new instance compared to having the database on the same instance), administration (security, troubleshooting, backup, etc..) and migration perspectives, which option is better considering that we want to seamlessly migrate to the new server without complications or long outage.


texts: Unable to connect to Amazon RDS instance

texts:
I recently created an oracle instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance using Oracle SQL Developer.

The (relevant) information I have from Amazon;

Endpoint - The DNS address of the DB Instance: xxx.yyy.eu-west-1.rds.amazonaws.com

DB Name - The definition of the term Database Name depends on the database engine in use. For the MySQL database engine, the Database Name is the name of a database hosted in your Amazon DB Instance. An Amazon DB Instance can host multiple databases. Databases hosted by the same DB Instance must have a unique name within that instance. For the Oracle database engine, Database Name is used to set the value of ORACLE_SID, which must be supplied when connecting to the Oracle RDS instance: ZZZ

Master Username - Name of master user for your DB Instance: org

Port - Port number on which the database accepts connections: 1521

From this information, the connection settings in SQL Developer are pretty obvious, so I don't really see what I could be missing...


texts: need suggestions to improve view performance

texts:
I am looking to improve the performance of a view in SQL Server 2008.  This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these attributes of a person.

It's a really complicated, long running view.  We have over 19 million people and there is a lot of logic that goes into each column.  For example, there is an indicator as to whether or not a person is deceased and that relies on three CTEs (common table expressions) and a case statement.

Basically, it's a nightmare.

I need to figure out a way to improve the performance.  Changing it to a table is not possible - data must be up-to-the-second accurate.  Changing it to an indexed view is right out - it uses data from multiple databases.  I can't really modify the column structure as it would break a number of existing reports.

Are there any tools in the toolbox that might help?  I'm wondering if stored procedures or functions might help.  Maybe a table with computed columns?  I would be able to pull the person's identifying information on a nightly basis and store that into a table, but the vast majority of columns rely on live data.


texts: Equivalent of include clause while creating non clustered index in sybase?

texts: I searched on creating non clustered index with included columns for sybase database. But I don't think "include" is allowed while creating index. Is there any equivalent in sybase?


texts: Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB

texts: As far as I know in Microsoft SQL Server it is possible to use CURSOR to "scan" database and get all tables/entries where specified string is mentioned. Can I do this in Oracle 11g DB? Any caveats/limits for such scenario?


texts: What would be the most efficient indexes to make to speed up these 4 queries?

texts:
I have a few queries that I want to speed up, but the columns they filter on are all different, but related, and I'm having trouble figuring out the best way to make indexes for them.

So the first query is something like this

1: select * from TABLE where A=? AND B=? and C=?

Then, the next three queries are all on the same table, but they each use one of the possible combinations of 2 elements from A, B and C.

2: select * from TABLE where A=? AND B=?

3: select * from TABLE where A=? and C=?

4: select * from TABLE where B=? and C=?

So I figure if I make an index on (A,B,C) that will speed up query 1 and 2, but I don't think it will do anything for query 3 or 4. So then would I need two more indexes on (A, C) and (B, C), or is there a more efficient way to do this?

I'm using Oracle 12c btw.


texts: How to select rows which are mapped to a junction table in a many to many relationship?

texts:
I have three tables:

locations (has zipcode, location name);
professionals (has name);
location_professional.

I want to select only those rows from locations table where professional_id is 4. What's the best way to do this?


texts:
I have a table witrh 3 columns:

id price created_at
1    0      ts
2    5      ts
3    0      ts
4    3      ts

I'd like to have a result table that contains 2 rows, like this:

id timestamp
4  ts
2  ts

To get the two rows above, I am using two queries.
Here is how I am currently selecting the first row:

select id, to_timestamp(created_at)::timestamp as timestamp
from (select * from web.orders where total > 0) as q
order by id desc limit 1

Here is how I am selecting the second:

select id, to_timestamp(created_at)::timestamp as timestamp
from (select * from web.orders where total = 0) as q
order by id desc limit 1

I tried to create the desired result table with UNION ALL of the above queries but it is not working. Any ideas?


texts: What is the usage of performance_schema in MySQL 5.5?

texts:
I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list.

What is the usage of this new schema..?
How can we monitor using this schema..?
What are the advantages of using this schema..?
Is there any overhead by enabling of this schema..?


texts: I can't restore my database on SQL Server 2012. Exceeds size

texts:
I'm trying to restore a database from a .bak file. The file is only 89.3 mb large. I'm using the restore with replace option, so that all other data in the database is removed. We are just trying to get the latest data from out clients.

But I get an error saying that I will exceed my limit of 10GB. My server instance is, using

select @@version:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Could someone please tell me why I can't get my database to restore. Do I have to get the full version of 2012? We are using the Express version


texts:
I have developed an Access 2010 database.

It has three main tables:

HOUSES
id,
address,
number_stories,
garden,
parking,
imageAttachments (~1-4 per record),
etc...,
about 60 fields total

CONTACTS
id,
houses.id(fk),
contactType(owner, manager, etc),
contactEmail,
contactPhone

VISITS
id,
Contacts.id(fk),
Houses.id(fk),
visitDate,
visitNotes

It also has a bunch of small lookup tables for some of the  fields in the houses table.

Right now I have about 100 houses listed and only about 5-10 contacts and visits.
I'm expecting at its peak this database will hold 300-500 houses, with 3-5 contacts and visits for each.

Right now the database size is about 50 MB.  I realize I didn't compact/repair it while I was building it or initially populating it which might be one problem.  It seems to run very slow.  Has this database grown too much?  Is this a typical size for a database of this scope?

(I'm on  windows 7 with 8 gig RAM so I don't think my own computer is the issue)


texts: How can I configure PostgreSQL 9.0 to start automatically under Windows XP?

texts:
Yesterday I installed PostgreSQL 9.0 on Windows XP, and after the installation the DB was running. After booting the system this morning, the data base server did not start automatically.

How do I configure Windows XP to start the postgresql demon after each boot/reboot? (I'm a Unix guy, and guess that's easy for someone knowing Windows.)


texts: PostgreSQL IN operator with lots of values in an external file

texts: I have a file with a lot of identifiers, one in each line. I also have a DB with a superset of those id's. I would like to query the DB only with the id's of interest. Is there a way to "import" an external file or do I have to copy all 300 values into the IN expression? This is a large db.


texts: Using intermediate result in backend with node-postgres

texts:
Firstly, I am using the node-postgres module to access database in a node application.
I have this SQL code that pulls up data from a table data of which is pulled from another table.
SELECT dwa_id
FROM _tasks2dwa
WHERE task_id IN
(
    SELECT task_id
    FROM _tasks
    WHERE onetsoc_code IN
    (
    SELECT onetsoc_code
    FROM _alternate_titles
    WHERE alternate_title
    ILIKE '%Beta Tester%'
    )
)

I would like to use this query like client.query(query)(Please refer to the docs).
I would like to use the intermediate result (the table that is comprised of values of task_id) in a backend application. However I also need the end result, the table that is comprised of values of dwa_id in that backend application. I have a solution which is writing a separate query for each result, which seems inefficient. Is there another more efficient approach to this problem?


texts: Does pg_dump flag -j give performance improvement up to -j 16 or -j 32 or even -j 64?

texts:
I am backing up and restoring a large DB (7+TB) ... I am using

pg_dump -j 8 -Fd

(8 parallel dump threads with directory formatting).

I just wondered if anybody has tested with a higher -j value like 16 or 32 or even 64?

Please advise ... Thanks much!


texts:
We have a PostgreSQL 8.3.7 database suffering severe bloat after an algorithmic change. Unfortunately upgrading isn't an option at this time.

For a particular group of partitioned count roll-up tables, we used to update them by selecting then either inserting for new counts or updating existing counts. To avoid network saturation we switched instead to updating, checking for failed updates and then inserting.

I've read this is a bad scenario for PostgreSQL (at least circa 8.3.7) where the dead tuples from the updates are in the middle of the table rather than the end (as previously) and so are not being reclaimed by the autovacuum which works from the back of the table.

It seems to me that autovacuum_vacuum_cost_limit is the most likely setting I should change.  Currently it is set to the default of 200 - perhaps I should start at 2,000 and go up from there?

I have a small window to make production changes to use trial and error, and no test database of equivalent size.


texts:
There are two schemas in Oracle11g. For example db1 and db2.

They have same structure of tables and different data to each other.

Now I want to merge data on db1 with data on db2 with no duplication.

But I can’t, duplication is happened.

I tried two different ways like bellow:

Import from dumpFile.db1 into db1 then import from dumpFile.db2 into db1.
After import data into both schemas and executo direct path insert.

insert/*+ APPEND */ into db1.myTable (SELECT * FROM db2,myTable);.

Are there good way to merge without duplication?


texts: I have create oracle 12c instance with 2 PDBs, I am able to connect to CDB from python using cx_Oracle module but not sure how to connect to PDB.


texts: Mysql replication from existing slave to new slave

texts:
I'm hoping you experts can help advise me, I've got this topology:

Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB

Backup server is a slave of Live DB, and Test DB is a slave of Backup server

The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).

Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)

Maybe this is by design?
Maybe it's not possible to chain slaves together?

I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).

Here are what the related settings are on the backup server:
(/etc/mysql/my.cnf)

log_bin                 = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days        = 7
max_binlog_size         = 100M
binlog_cache_size       = 16M
binlog-format           = MIXED

Specs:

mysql 5.5
Debian Squeeze

Thanks, Your advice would be much appreciated


texts: Is it possible to create a database with a user in a single transaction on Postgres 12?

texts:
I would like to create a database with a user in a single, atomic SQL statement on Postgres 12. Is this possible?
I see from this answer - https://stackoverflow.com/questions/26482777/create-database-cannot-run-inside-a-transaction-block, that that it might not be possible.


texts: Can you Identify the sql server name from sql backup file(.bak)?

texts: I have a backup file from an SQL 2016 server and I need to identify which SQL server the file was created on. Is there a way to identify the original SQL server where the file originated at?  Thank you.


texts:
Is there anyway to update a linked server provider without dropping and recreating the linked server?

The linked servers' provider need to be updated from SQLNCLI10 to SQLNCLI11

A coworker has provided this script, but I'd like a sanity check on this from the community.

update master..sysservers set providername = 'SQLNCLI11' where providername = 'SQLNCLI10'

An update is necessary after upgrading these servers from 2008 R2 to 2014.

This is the error some of our stored procedures are getting when using openquery which is why we must update the provider.

  Operand type clash: datetime2 is incompatible with numeric


texts: I would like to upgrade Mongodb version 1.8.2 with replica set available ( 1 primary, 2 secondary) with same version to version 4.4 with no downtime. What are the steps do I need to follow in order to achieve the upgrade without any downtime?


texts:
I'm trying to use the variable "key" inside my "COPY FROM" command below to populate a Redshift table. When I use the code below I get a syntax error as the "COPY FROM" is looking for a string with quotes around it. Works fine when I change to '[somestring]'. Tried a few different things but haven't been able to get this to work, any suggestions?
CREATE OR REPLACE PROCEDURE TransformOppHistory(key varchar)
AS $$
BEGIN

COPY TempOppHistory FROM key
iam_role '[xxx]'
DATEFORMAT AS 'MM/DD/YYYY'
TIMEFORMAT AS 'MM/DD/YYYY HH:MI:SS'
CSV
IGNOREHEADER 1;

END;
$$ LANGUAGE plpgsql


texts: SQL Injection penn testing from the queries only

texts:
Is there an established method or tool available to perform pen testing on an application by only testing queries it sends to the database?

For example, if I have a bunch of SQL Servers hosting various websites and a query came through that wasn't parameterised, is there a way I can detect these?

Example query that probably isn't secure:

SELECT x,y,z FROM logins WHERE username = 'xx' and password = 'yyy'

...instead I would expect a secure application to be probably be using sp_executesql.


texts: XML Parsing Illegal XML character error in SQL 2008

texts:
I was running this query in SQL Server 2008 (10.0.5890),
Collation: SQL_Latin1_General_CP1_CI_AS

SELECT
   CAST(event_data.value('(event/data/value)[1]',
   'NVARCHAR(max)') AS XML) AS DeadlockGraph
FROM
   ( SELECT
      XEvent.query('.') AS event_data
   FROM
      ( -- Cast the target_data to XML SELECT
         CAST(target_data AS XML) AS TargetData
      FROM
         sys.dm_xe_session_targets st
      JOIN
         sys.dm_xe_sessions s
            ON s.address = st.event_session_address
      WHERE
         name = 'system_health'
         AND target_name = 'ring_buffer' ) AS Data -- Split out the Event Nodes CROSS APPLY TargetData.nodes('RingBufferTarget/ event[@name="xml_deadlock_report"]') AS XEventData ( XEvent ) ) AS tab ( event_data )

But it was always ended with the following error:

Msg 9420, Level 16, State 1, Line 1
XML parsing: line 22203, character 102, illegal xml character

Even if I changed it to VARCHAR(max) it remains the same.
Though this same script was running fine and without any issue in another server with the same version & collation.

Sample XML result from the working database:

Proc [Database Id = 5 Object Id = 2033194789]

    </process>
    <process id="process55d288" taskpriority="0" logused="584" waitresource="KEY: 5:72057594332250112 (6b0281c999d7)" waittime="1281" ownerId="1679" transactionname="EventGetQueueTopSp" lasttranstarted="2016-07-02T20:08:56.350" XDES="0x8209f990" lockMode="U" schedulerid="4" kpid="2880" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-07-02T20:09:12.370" lastbatchcompleted="2016-07-02T20:08:59.090" lastattention="1900-01-01T00:00:00.090" clientapp="EventService Queue Check" hostname="WVERPDB" hostpid="3148" loginname="sa" isolationlevel="read uncommitted (1)" xactid="1679" currentdb="5" lockTimeout="4294967295" clientoption1="671221856" clientoption2="128056">
      <executionStack>
        <frame procname="" line="96" stmtstart="8066" stmtend="8714" sqlhandle="0x0300050009a8007d9eadc300449e00000100000000000000" />
      </executionStack>
      <inputbuf>
</deadlock>

Here is the SQL version for both DB

Microsoft SQL Server 2008 (SP3) - 10.0.5890.0 (X64)   Apr  2 2015 16:07:19   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)


texts: why have to write c## for user creation in oracle 12c

texts:
I have tried to create user accounts in oracle 12c. But it has shown the error:

  ORA-65096: invalid common user or role name.

I have tried the following two user name:

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> CREATE USER A3 IDENTIFIED BY p3;
CREATE USER A3 IDENTIFIED BY p3
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE USER testUser IDENTIFIED BY pass;
CREATE USER testUser IDENTIFIED BY pass
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> CREATE USER c##testUser IDENTIFIED BY pass;

User created.

SQL>

Then I have googled and found a solution that I have to write c## before the user name.

The reason is not clear to me. Can you please clear it to me?


texts:
On our production db there is a table 180 GB,  around 2 millions records, I want to re-create it as a new empty table in a new filegroup and delete the existing table. What is the best practice to perform this task without affecting the DB performance?

My time concern will be in renaming the table and dropping it.

I want to add this is a very high load DB so I don't want to get any locks or long running queries.


texts:
I manage a PXC 5.6 cluster and noticed that the tmpdir variable in /etc/my.cnf isn't set.

I was thinking to set it to /tmp (which is tmpfs) to explicitly make the cluster store temporary files in RAM.  Without this setting, MySQL could choose /var/tmp or /usr/tmp (which, on my system, are on-disk) to store temporary files.

1) Is there any drawback to do this on PXC? I ask because on a MySQL replication slave one must not use a RAM disk to store temporary files, since the slave will need some of the files when the machine restarts.

2) Is this really going to improve performances, or it looks like a unnecessary twiddling? The InnoDB buffer size is already large enough, and 95% of temporary tables are stocked in memory.


texts:
I have a very basic database for keeping track of point scores for 6 different events. Each event has 4 events within it.

My Current SQL query is very basic, and looks very similar to what is below;

Select Name,
       SUM(ISNULL(p.e1_e1_points, 0)+ISNULL(p.e1_e2_points, 0)+ISNULL(p.e1_e3_points, 0)+ISNULL(p.e1_e4_points, 0)) AS Event1,
       SUM(ISNULL(p.e2_e1_points, 0)+ISNULL(p.e2_e2_points, 0)+ISNULL(p.e2_e3_points, 0)+ISNULL(p.e2_e4_points, 0)) AS Event2,
       SUM(ISNULL(p.e3_e1_points, 0)+ISNULL(p.e3_e2_points, 0)+ISNULL(p.e3_e3_points, 0)+ISNULL(p.e3_e4_points, 0)) AS Event3,
       SUM(ISNULL(p.e4_e1_points, 0)+ISNULL(p.e4_e2_points, 0)+ISNULL(p.e4_e3_points, 0)+ISNULL(p.e4_e4_points, 0)) AS Event4,
       SUM(ISNULL(p.e5_e1_points, 0)+ISNULL(p.e5_e2_points, 0)+ISNULL(p.e5_e3_points, 0)+ISNULL(p.e5_e4_points, 0)) AS Event5,
       SUM(ISNULL(p.e6_e1_points, 0)+ISNULL(p.e6_e2_points, 0)+ISNULL(p.e6_e3_points, 0)+ISNULL(p.e6_e4_points, 0)) AS Event6
       from points

I need to be able to add an additional column, which is the lowest value from columns (being Event1, Event2, Event3, etc).


texts:
I have 3 "CentOs 7" VM's in AWS environment in which I have setup MongoDB Repication Set using below command.

mongod --fork --logpath "/var/rs1/1.log" --replSet yogi --dbpath "/var/rs1/" --port 27017

Replication works fine, but when I restart any one of VM. Restarted VM comes out of replication group(I observed that mongo started without --replSet)

I placed above command in /etc/rc.local after giving +x permission and restarted the VM, I got the same issue.

If I run the same command after killing mongodb process it works fine.

What can be the issue here, how can I get VM to get added in Replica Set automatically after restart?

When I add the command in rs.local file I get below messages in logs

 Nov 17 10:20:09 13 rc.local: forked process: 889
 Nov 17 10:20:09 13 rc.local: ERROR: child process failed, exited with error number 1


texts:
In a timesheets data model, suppose I have an employee table, and a job table. Then, I also have an employeeInJob table to store an employee's rate for a given job e.g.

employeeInJob (
    employeeId,
    jobId,
    rate,
    effectiveFrom )

A given employee may have multiple entries in the employeeInJob table for a given job since his/her rate may vary for that position over time.

Now I have a timesheetEntry table:

timesheetEntry (
    employeeId,
    startTime,
    endTime,
    ???JobId )

My question is whether the timesheetEntry table should reference the job table or the employeeInJob table. I think having it point to the core job provides a more flexible design? Is this the case?


texts: Dynamically define a RETURN table (column type, name) for subsequent loop

texts:
I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.

https://postgis.net/docs/ST_Intersection.html

https://postgis.net/docs/ST_Intersects.html

POSTGIS intersection does not support the processing of multiple geometries natively, differently from ST_Intersects() and I had to design a function (returning a table) that selects rows intersecting with my clipper_geom using ST_Intersects(), loop through the result set and compute each intersection with ST_Intersection(). The geom field and clipped_geom_wkt are the ones recording the clipped geometry.

The function works, but I need a different one for every table we want to produce the clipping. I would like to read the input table dynamically (column name and type) and define both in the RETURN statement.

All field names and types are the same, only geom is updated and clipped_geom_wkt is added.

I tried to search Stack Overflow and I found examples on how to create dynamic table structure but none performs a subsequent LOOP over the first results, where the column names have to be matched to insert / update the new data.

This is what I have come up so far, but I am unsure about how to perform the LOOP part, to add the clipped_geom_wkt field and update the geom field. Some responses advise to use RETURNS TABLE (...) if more fields are added to SETOF ...

https://stackoverflow.com/questions/17821482/easy-way-to-have-return-type-be-setof-table-plus-additional-fields

But then it seems that dynamically generated columns are only supported with RETURNS SETOF ...

https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557

https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/8611675#8611675

CREATE OR REPLACE FUNCTION clip_palin_polygon_complete(clipped_table text,clipper_geom text, age_sequence VARCHAR)
RETURNS TABLE (rec clipped_table, clipped_geom_wkt text)) AS $$ --not sure if this is the right way to do it...
DECLARE var_r record;
BEGIN
    FOR var_r IN (
        SELECT * FROM clipped_table
        WHERE clipped_table.seq IN (age_sequence)
        AND ST_Intersects(ST_GeomFromText(clipper_geom,4326), clipped_table.geom)
    )
    LOOP
        /*
        these are the original table fields that I would like to keep and match
        dynamically with any table I have as input (clipped_table)
        objectid := var_r.objectid;
        seq := var_r.seq;
        -- etc.
        */

        --below there are the only two fields that need modifying
        geom := (
            SELECT ST_Intersection(ST_GeomFromText(clipper_geom, 4326), var_r.geom) AS geom);
        clipped_geom_wkt := (
            SELECT
            ST_AsText(ST_Intersection(ST_GeomFromText(clipper_geom,4326), var_r.geom)) AS clipped_geom_wkt);
        RETURN NEXT;
    END LOOP;
END; $$
LANGUAGE 'plpgsql'


texts: Specify a frequency range for a SQL agent schedule

texts:
I want to create a SQL agent schedule that occurs during a date range. I can't use "recurs every n weeks" because the process that it will fire off will change with each month. I need to set the frequency to be akin to occurs monthly, 1st Monday through the 2nd Friday of every 1 month. That way I am assured to be only running the process starting with the first full week of every month

Am I crazy for thinking of a schedule like this? Shirley(sic) I can't be the only one. I have looked at sp_add_schedule for any hints, none to be found there. I have tried Googling, but I can't come up with the right query syntax thus keep coming up empty.

Perhaps if one could help me come up with the correct question, that could lead me in the right direction.

::Update1: I am going to add date logic to the script.


texts:
I have these 3 tables:

ConstructorEmployee

EID (employee ID)  as primary key and other key.

Project

PID as primary key and other keys.

ProjectConstructorEmployee

PID and EID as foreign keys to the other tables.

Each ConstructorEmployee can work on several Projects. I need to create a trigger that if project has been deleted, I need to delete all ConstructorEmployee  that worked only on this project. I need to delete them from the ConstructorEmployee table.

I'm working on SQL Server 2017.


texts: In what order does PostgreSQL check object permissions?

texts:
Given a database role, user1, a function something() defined as a stored procedure and a view created like:

CREATE VIEW view1 AS select * from something()

And, given this permissions:

REVOKE ALL ON FUNCTION something FROM user1
REVOKE SELECT ON view1 FROM user1

When I run SELECT * FROM view1, I get an error permission denied for function something().

My question is, if I revoke the select permissions on the view, why the function is called? I was expecting to receive something like:

permission denied for relation view1

Thank you!


texts: Disable all constraints and table checks while restoring a dump

texts:
I've obtained a dump of my PostgreSQL database with:

pg_dump -U user-name -d db-name -f dumpfile

which I then proceed to restore in another database with:

psql X -U postgres  -d db-name-b -f dumpfile

My problem is that the database contains referential constraints, checks and triggers and some of these (checks it would seem in particular) fail during restoration since the information is not loaded in the order that would cause those checks to be honored. For instance, inserting a row in a table may be associated with a CHECK that calls a plpgsql function that checks whether a condition holds in some other unrelated table. If that latter table is not loaded by psql before the former, an error occurs.

The following is a SSCCE that produces such a database that once dumped with pg_dump cannot be restored:

CREATE OR REPLACE FUNCTION fail_if_b_empty () RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

CREATE TABLE IF NOT EXISTS a (
     i              INTEGER                    NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);
CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);
INSERT INTO b(i) VALUES (0);

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty());

Is there a way to disable (from the command line) all such constraints during dump restoration and enable them back again afterwards? I am running PostgreSQL 9.1.


texts:
In order for me to create space in the database is this all I need to do?
alter tablespace USER_DATA_TBS add datafile '/oradata/bosv/user_data04.dbf' size 10m;

Or are there other steps I need to take?
I looked at this guide.
I would appreciate if you could point me in the correct direction.
This is what I have after I created  a datafile:


texts:
I am using SQL Server 2012,

I have a table with a format such as :

MyOrders :
 ID -> PK int
 Cost -> float
 Category -> int

I want to get the following :

Input :

category (lets say 2).
sum_limit (lets say 500).

I need to get :

Top X (need to find x) rows from MyOrders, filtered by category (lets say where category = 2)
having sum of cost smaller than the sum limit (lets say 500) but the largest that is smaller than the limit, ordered by ID.

In other words, go over the rows one by one (ordered by ID) who has category 2, and summarize the cost until i pass the cost limit, than return all those rows except the last one (that caused me to pass the limit).

Also i want to avoid using loops.

I thought of creating a query that selects a number (from 1 to the number of rows in MyOrders after filtering the category) and its sum of cost as the first stage,

I created a numbers table (https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table)
And than tried some queries but could not get it exactly.

Thanks for any help.

EDIT:

OK I used the following to get what i wanted :

select top 1 Number from
 NumbersTest
 where Number <= (select count(1) from MyOrders where category = 2) and
 (select sum(cost) from MyOrders where ID in (select top(Number) ID from MyOrders where category = 2 order by ID desc)) < cost_limit
 order by Number desc

Where NumbersTest is a table created as mentioned before (https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table)


texts: How can I speed up an sql query that round trips between two tables

texts:
I first posted this question on stackoverflow, and now I see this site, seems more relevant.

These tables are not that big, but the time taken to process this query is long.

What can I do with the following queries to speed up the process taken to return results?

$chat_alg = mysqli_query($con, "
    SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time > NOW() - INTERVAL 3 DAY && sid != ''
    GROUP BY sid
    ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));

while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
    $chart_songs[] = $row_chat_alg['sid'];
}

$imploded_songs = implode($chart_songs, ',');

$projects = $dbh->query("
       SELECT * FROM music
       WHERE perms != 'c' && sid IN($imploded_songs)
       ORDER BY FIELD(sid,$imploded_songs)
       limit 50
");

...

This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.


texts: Selecting random rows from database initially while preserving that order later

texts:
I am building a quiz app, in which the student can login and can select the option of give exam. I want to show multiple choice questions to students randomly.

I stored all the questions in one table. For example, I stored 20 questions for a chemistry exam. Now I want to show 10 random questions to students. I don't want to select questions randomly every time when user refresh the page. Currently I am doing:

SELECT * from table-name order by rand() limit 5;

The problem is this query selects random rows every time the user refreshes the page. This is my code:

<?php
$query="SELECT * from question_bank order by rand() limit 10";
$result= mysqli_query($connection,$query);
confirm_query($result);

?>

<form action="result.php" name="form1" method="post" id="quiz">
<?php
while ($read_all_data = mysqli_fetch_assoc($result))
{
    $_SESSION['id']=1;

$id=$read_all_data['question_id'];
$a=$read_all_data['a'];
$b=$read_all_data['b'];
$c=$read_all_data['c'];
$d=$read_all_data['d'];
 echo $read_all_data['question']."
";
 echo "A:<input type ='radio' value ='a'  name='$id' >".$read_all_data['a']."
";
 echo "B:<input type ='radio' value ='b'  name='$id' >".$read_all_data['b']."
";
 echo "C:<input type ='radio' value ='c'  name='$id' >".$read_all_data['c']."
";
 echo "D:<input type ='radio' value ='d'  name='$id' >".$read_all_data['d']."
";

}


texts:
I was able to configure and Replicate between master and Slave ,so when we insert anything on master db it will automatically replicate to slave,

Now,requirement is that if we shutdown master can we able to proceds with slave as master.

i was followed this link for replication

http://searchadmin.org/Thread-ha-postgresql-cluster-by-streaming-replication-pgpool-ii/


texts:
I prepared a script to create a table with 800 columns but SQLPlus and SQL Developer are not reading the whole script as they probably read only 7499/2499 characters max. As an alternative I am thinking to divide the script into an initial create script with 200 columns and then alter table to add remaining columns in 200 chunks. My question is that what is the best practice to achieve this? Are there any cleaner alternatives. I searched google but could not find the relevant answers and most of them got closed by suggestion that the table should at first place not have these many columns and it is a design issue.

Note: I am trying to replicate a scenario in a legacy system by creating a local database. So I don't have the option to denormalize.

What is the best way to create a table with 800 columns in a single script?


texts:
I deleted the test database, and now I want to restore it back, when I try to recover the database I get this error.
Everything I had at the database is there and everything works fine.

How can I fix this error?

unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2019 11:49:40
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 422743

SQL> select sequence# from v$archived_log;

 SEQUENCE#
----------
    11
    12
     1
    11
     1
    12


texts: Issue with Scripting out Subscriptions using Powershell

texts:
I am trying to script out the replication objects via Powershell using Microsoft.SqlServer.Rmo.dll. The replication type is transactional with push subscriptions.
I have been able to script out publication, articles, PALs but not able to script out publisher side subscriptions.
$scriptargs =[Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeCreateLogreaderAgent `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeCreateMergeAgent `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeCreateQueuereaderAgent `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludePublicationAccesses `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeArticles `
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludePublisherSideSubscriptions`   #one way tried to get the subscriptions
-bor  [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeGo

foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
    if ($replicateddatabase.TransPublications.Count -gt 0)
    {
        foreach($tranpub in $replicateddatabase.TransPublications)
        {
            **[string] $myscript=$tranpub.script($scriptargs)**   #Errors out here
            writetofile $myscript $filename 0
        }
    }
}

The other way I tried is exclude IncludePublisherSideSubscriptions from Scriptoptions and tried to script out directly using the following statement
foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
    if ($replicateddatabase.TransPublications.Count -gt 0)
    {
        foreach($tranpub in $replicateddatabase.TransPublications)
        {
            [string] $subs=$tranpub.TransSubscriptions.script($scriptargs)   #another way but same error
            writetofile $subs $filename 0
        }
    }
}

The third way I tried:
$repsvr.ReplicationDatabases.TransPublications.TransSubscriptions.Script($scriptargs)

Of all, I found the following link to be very helpful where my code is mostly based on but just got stuck in the scripting out of publisher side subscriptions. I appreciate your help.


texts: Postgresql: How to select records by column and jsonb array column

texts:
I have table that look like this:
id           device_name      user_id     device_subname
(varchar)    (varchar)        (int)       (jsonb)
---          -------------    ---------   -----------------
X2           wall lamp        1235        [{"name": "rear light", "commands":"light_v1"}]
Z6           rear light       1235        []
H4           rear light       1235        []
1C           speakers         1476        []
V4           wall socket      1523        [{"name": "socket", "commands":"socket_v2"}]

How to get records with user_id = 1235 AND (device_name = 'rear light' OR device_subname->>name = 'rear light')?
I've come to this solution:
SELECT DISTINCT
  device.id, device.device_name, device.device_subname
FROM
  devices AS device,
  jsonb_array_elements(device.device_subname) AS "deviceSubname"
WHERE
  device.user_id = '1235'
  AND (
    device.device_name = 'rear light'
    OR
    "deviceSubname"->>'name' = 'rear light'
  )

Above query is resulting:
id      device_name      user_id     device_subname
---     -------------    ---------   -----------------
X2      wall lamp        1235        [{"name": "rear light", "commands":"light_v1"}]

What i want is to get this:
id      device_name      user_id     device_subname
---     -------------    ---------   -----------------
X2      wall lamp        1235        [{"name": "rear light", "commands":"light_v1"}]
Z6      rear light       1235        []
H4      rear light       1235        []

It's look like jsonb_array_elemenst() is skip records if the device_subname column is an empty array. How to do this query?


texts: Log Shipping always fails, after a while

texts:
I have a problem with Transaction Log Shipping on the SQL Server 2014 edition.
I have to log ship 4 databases, 2 of them are fine and the other 2 ALWAYS fail but not at first. When it fails, SQL reports that one of the log backups in the chain "could not be verified" thus breaking the chain.
These 2 databases in question will also end up in a "Restoring" state. All the articles that I have read about this issue report that I should put the database in emergency mode and run a DBCC CheckDB, but I can't since the database is in restoring state...

Additionally, I have verified that the broken log backup in question is valid by running a RESTORE with VERIFYONLY, and no errors are reported.

I have set up log shipping on these two databases up at least 7 times now, always to have it break in a few hours. What can cause this behavior?

Here are some of the logs. This first one is the last successful restore after setting log shipping up again:

  2016-11-23 15:41:53.66 Restored log backup file. Secondary DB: 'XXX', File: 'XXX.trn'

And then the very next message is

2016-11-23 15:41:55.29    *** Error: Could not apply log backup file 'XXX.trn' to
secondary database 'XXX'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: An error occurred while processing the log for database
'text.staging.fsintel.com'.
If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Both instances are on SP2 GDR.


texts: I'm not understanding the concept of login/user on Azure

texts:
So as a SQL Server DBA, I can normally create a user, then inside the database U just create a login pointing to that login and then I give the necessary privileges.

I'm trying to understand the concept of logins and users on Azure. Official documentation has some confusing concepts about then and I think I just need to understand the basics to go on.

So, To create a user for a database, do I need to have a Login as we have in a SQL Server instance? I can only find information about AD users.

The original problem I'm having here is that, there's a user inside the database and I just can't change it's Password ( Dev people lost the pass ).

When I use (Inside the database I want to change its passowrd):

ALTER LOGIN TheUser WITH PASSWORD = 'SomePassword'

SQL Server Management Studio says:

  Msg 5001, Level 16, State 3, Line 139 User must be in the master
  database.

But there's no user in the master database. how was this user created here without beign created first on master? Do I need a user inside master and then transfer it to the database I want it to access?

This is not helping me

EDIT:

So I could connect with a different login ( for some reason im not sysadmin I guess) and I could change the user  password with the command above, but I still would like som esimple explanation about how logins and users works on azure.


texts:
Is it possible in MySQL / MariaDB to calculate row size like in PostgreSQL ?
I'm looking to calculate in a multi tenant application how much space did the user use for the database but I couldn't find anything relevant about this.


texts: MySQL: After inserting data into a table, the table remains empty

texts:
i'm trying to run this simple query:
insert into diff_set_values(value)
select value
from diff_sets
group by value;

but after it is finished, there is no data in the destination table diff_set_values. Unfortunately, there's so much data in the source table diff_sets, that the query runs a little more than an hour. Too long for MySQL Workbench to keep the connection to the MySQL server. It says "Error Code: 2013. Lost connection to MySQL server during query" after 30 seconds, but doesn't tell me if any other error occured.
information about the tables and server
The source table diff_sets contains about 186 million records with 4 columns (ID1 int, ID2 int, diffType int, value varchar(64)). Primary Key: ID1, ID2, diffType. The column value also has an index idx_diff_sets_value.
Example data:
ID1     ID2     diffType  value
25151   24978   15        13ddfb5fa96ffebffbdfd3ff7dd7cabd7
66675   66670   14        13ddfb5fbb5cdefe7bffffbd359775ffe
66701   66675   18        13ddfb5fbb5cdefe7bffffbd359775ffe
25789   25750   14        13ddfb5fbf7fdeff7fabff3edbfcefadf
25789   25778   17        13ddfb5fbf7fdeff7fabff3edbfcefadf

The destination table diff_set_values is empty and contains a column value varchar(64) and 6 other int columns that will be filled with null. Those columns will be updated later.
I'm running MySQL Community Server 8.0.16 on my Notebook. I'm accessing it with MySQL Workbench 8.0 and I have exclusive access to the database.
What I tried
I've created a stored procedure and inserted all error handlers I could think of.
CREATE DEFINER=`root`@`localhost` PROCEDURE `fillInterDiffSets`()
BEGIN
    declare continue handler for sqlexception
    begin
        get diagnostics condition 1 @p1 = returned_sqlstate, @p2 = message_text;
        insert into sp_error(error_code, error_description, error_time)
        select @p1, @p2, now();
    end;

    declare continue handler for sqlwarning
    begin
        get diagnostics condition 1 @p1 = returned_sqlstate, @p2 = message_text;
        insert into sp_error(error_code, error_description, error_time)
        select @p1, @p2, now();
    end;

    declare continue handler for not found
    begin
        get diagnostics condition 1 @p1 = returned_sqlstate, @p2 = message_text;
        insert into sp_error(error_code, error_description, error_time)
        select @p1, @p2, now();
    end;

    insert into diff_set_values(value)
    select value
    from diff_sets
    group by value;
END

When I run that procedure, I can watch the size of the diff_set_values.ibd file increasing. After a bit more than an hour the increasing stops at about 5.2 GiB. When I run show processlist, the process is still active for a few minutes.
When the process disappears from the show processlist, there are no records in the diff_set_values table and yet no error messages in the sp_errors table.
I tried, if the error handlers work by removing the group by part of the query, which immediately results in a "duplicate key" message in the sp errors table.
I tried to use select distinct value from diff_sets instead of select value from diff_sets group by value. Doesn't help.
I tried select sql_big_result distinct value from diff_sets. The only difference is that the file size of the ibd file doesn't increase and show processlist only tells me "creating sort index", but eventually disappears from the processlist.
I limited the query by appending limit 0, 1000000 to it. That works as expected.
A few months ago, the diff_sets table was smaller and the query worked properly.
I'm running out of ideas what to do. Is there a way to see all errors that occured on the server? Do you have any idea how to solve the problem?


texts: How can I get SQL Server restore start and end time information?

texts:
SQL Server error log reports only the end of the restore (ah doc restore)
How can I get the start time? Is it stored somewhere?


texts: SQL Server Replication: "ALTER TABLE ALTER COLUMN" is not propagated to subscribers

texts:
We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for years, including frequent schema changes (new column, new constraints, etc).

The following instruction was sent on the publisher:

use myDatabase
alter table Tbl_TypeLignePaye
      alter column natureTypeLignePaye nvarchar(12)
go

where field natureTypeLignePaye was originally nvarchar(3) null

The query ran without errors on the main database. The result is the following:

The field natureTypeLignePaye still appears as nvarchar(3) in the object browser
The column properties screen indicates a nvarchar type with a length of 12
The change was not propagated to the subscribers

Any idea on what is going on with this database?

Publisher: object browser window vs property window give incoherent data


texts:
So I recently realized my /var directory was growing but couldn't find anything using du, then find out about lsof and found the following:

lsof -nP | grep '(deleted)'
mongod    23184 23205          mongodb    5w      REG              202,1 10765518018     284593 /var/log/mongodb.asd/mongod.log (deleted)

Quite a few lines of those, but I understand they won't just dissapear until the mongod process is restarted?

Anything I can do about it?

This is under Ubuntu 16.04 and MongoDB 3.6.13.


texts: mySQL subqueries calculating winning percentage

texts:
I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table:

id
result
dice1
dice2
player_id

1
LOSE
4
4
9

2
LOSE
4
5
7

3
WIN
4
3
9

4
LOSE
2
3
9

5
WIN
6
1
7

6
WIN
1
6
7

7
LOSE
1
3
7

I need to display the percentage of win of each player and this is the query right now but it's not working, it gives me back the same percentage for every player:
SELECT DISTINCT player_id,
ROUND(((SELECT COUNT(id_game) FROM game WHERE result = 'WIN' ) /
(SELECT COUNT(id_game) FROM game )) * 100 ) AS percentage
FROM game
group by player_id

Do you think it's even possible to do that? I'm starting to doubt it


texts: Generic way to determine if the table needs index (PostgreSQL)

texts:
I saw advices like Indexes should not be used on small tables or Index on small table may decrease performance`. Can anybody provide magnitude of table size when creating indices makes sense, besides trial and error?

Background: I want to add indices to several tables in database and looking for a general rule to start with.


texts: Create fast-refresh MV over dblink on table without PK?

texts:
I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink.

The MV would refresh on a 30-second schedule (since on-commit is not possible over a dblink).
The table in the external system does not have a primary key.

I'm a novice. Here's what I've tried:
--in the external system:
create materialized view log on external_system.workorder with rowid;

--in the primary system:
create materialized view primary_system.workorder_mv
build immediate
refresh fast
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder@gistomax

When I try the above, I get a litany of errors, starting with one that says I need a primary key.
I found a page that suggests that it's possible to do a fast refresh over a dblink. But the example uses a primary key, not a rowid.
And the docs say:

Rowid materialized views are not eligible for fast refresh after a
master table reorganization until a complete refresh has been
performed.

But I don't really know what that means. And to be honest, I'm so new at this, that I know I'm barking up the wrong tree, so I'll stop there.

Question:
Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?


texts:
I was going through the weak entity concepts and was trying to model the relation Hotel has many rooms. I have come up with the partial solution presented below but i'm having a problem to uniquely identify the tuples in the room entity related to the specific hotel.
My Assumption

Hotel(hotel_number, name) is a strong entity.
Rooms(room_number, bed) is a weak entity whose existence depends on the existence of the hotel.

1. Hotel entity's table

hotel_id
name

125
n1

132
n2

173
n3

103
n4

193
n5

2. Room entity's table

room_number
beds

101
3

101
2

102
1

103
6

There are two hotels with hotel_id 125 and 132. Hotel with hotel_id 125 has room numbered 101 with 3 beds and another hotel has room_number 101 with 2 beds. So how can i model the given relation in such condition as there's no pk for the weak entity i.e. room ??


texts: Query to select 'Customer_Num' with digit characters using regular expression

texts:
I have a table with below structure:
Example_table(Customer_Num    varchar2(50))

Some Example data in this table :
        Example_table
----------------------------
        Customer_Num
           12445
           12345
           12ttd
           2376y
           23%%*
           23467

I want to select custoemr_num records with digit characters , so these records 12ttd , 2376y , 23%%*
should not appear in the final result . Considering the fact that I can not change the table structure , which one is the correct query ?
Q-1:
  select customer_num
  from Example_table
  where REGEXP_LIKE(customer_num, '[[:digit:]]')

Q-2:
  select customer_num
  from Example_table
  where REGEXP_LIKE(customer_num, '[0-9]')

Are there any special condition in which these queries might end up producing wrong results?
Thanks in advance.


texts: Convert Date format into bigint format in postgresql

texts:
How can I convert date format into bigint format in postgresql?
for eg: '2018-08-20' to '20180820'


texts: Inserting a 30 million row table from a linked server

texts:
I have a large table that needs to be updated on a nightly basis from a linked server.  Initially I had simply truncated the table and inserted all of the records but this was taking too long.  I'm now using a merge statement to copy over only the records that need updating.  However this appears to still take too long, i.e. 10+ hours  What other methods or techniques can I use to get this table refreshed from the linked server on a daily basis?

BEGIN
SET NOCOUNT ON
MERGE dbo.[ANNOUNCEMENTS] AS Target
USING (SELECT [ANN_ID]
      ,[ANNOUNCEMENT]
      ,[SCH_OR_DIST_NUM]
      ,[START_DATE]
      ,[END_DATE]
      ,[CREATED_BY]
      ,[CREATION_DATE]
      ,[UPDATED_BY]
      ,[LAST_UPDATE_DATE]
    FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS])
            AS source ([ANN_ID]
                        ,[ANNOUNCEMENT]
                        ,[SCH_OR_DIST_NUM]
                        ,[START_DATE]
                        ,[END_DATE]
                        ,[CREATED_BY]
                        ,[CREATION_DATE]
                        ,[UPDATED_BY]
                        ,[LAST_UPDATE_DATE])
    ON (Target.ANN_ID = source.ANN_ID)
    WHEN NOT MATCHED THEN
        INSERT ([ANN_ID]
              ,[ANNOUNCEMENT]
              ,[SCH_OR_DIST_NUM]
              ,[START_DATE]
              ,[END_DATE]
              ,[CREATED_BY]
              ,[CREATION_DATE]
              ,[UPDATED_BY]
              ,[LAST_UPDATE_DATE])
        VALUES (source.[ANN_ID]
              ,source.[ANNOUNCEMENT]
              ,source.[SCH_OR_DIST_NUM]
              ,source.[START_DATE]
              ,source.[END_DATE]
              ,source.[CREATED_BY]
              ,source.[CREATION_DATE]
              ,source.[UPDATED_BY]
              ,source.[LAST_UPDATE_DATE]);
END


texts: Return sample row if result set is empty

texts:
What would be the most practical way to write a query, that returns single row in case of empty result set.

Something like (pseudosql)

(SELECT 1 AS c1, 2 AS c2 FROM ... WHERE ...) OR_ELSE(SELECT 0 AS c1, 0 AS c2)


texts:
Right now I am creating counts for each hour for the whole week. I am using python to connect to the database, and I am looping through each hour so str(i) is the current hour that is being updated.

UPDATE table1 p SET population_count_hour_' + str(i) + '=sub.popcount
FROM (SELECT state, count(*) as pop_count
     FROM table2
     WHERE start_time < ' + str(i*3600) + '
     AND end_time < ' + str((i-1)*3600) + ' GROUP BY state) sub
WHERE p.state=sub.state AND p.population_count_hour_' + str(i) + ' IS DISTINCT
FROM sub.pop_count;'

I have to do several queries in a similar fashion with different variables. Is there way to do this faster because right now it is a very slow process?


texts: Does my PostgreSQL instance support kerberos/gssapi authentication?

texts:
I installed it following instructions from digital ocean how-to-install-postgresql-on-ubuntu-20-04-quickstart:
% sudo apt install postgresql postgresql-contrib

I also did a 'show all' in psql and didn't see anything that suggested it was other than the krb_server_keyfile = /etc/krb5.keytab that I configured in postgresql.conf
These are the errors I see in the log:
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres LOG:  accepting GSS security context failed
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres DETAIL:  Unspecified GSS failure.  Minor code may provide more information: Key table entry not found
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres FATAL:  GSSAPI authentication failed for user "postgres"
2021-05-07 21:58:49.990 UTC [1434] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 114: "host postgres     postgres           172.31.93.176/24                              gs\
s include_realm=0"
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres LOG:  accepting GSS security context failed
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres DETAIL:  Unspecified GSS failure.  Minor code may provide more information: Key table entry not found
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres FATAL:  GSSAPI authentication failed for user "postgres"
2021-05-07 21:58:49.993 UTC [1435] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 114: "host postgres     postgres           172.31.93.176/24                              gs\
s include_realm=0"


texts: too many connection error mysql: how to calculate max_connections

texts:
how to calculate this values to get the best results to resolve this error "too many connection error mysql" and make my API faster
       in mysql

   wait_timeout = ??
   max_connections = ??
   max_user_connections = ??
   key_buffer = ??
   query_cache_size = ??
   query_cache_limit = ??
   tmp_table_size = ??
   table_cache = ??
   max_concurrent_transactions ??
   max_concurrent_queries ??

and in apache 2

   ServerLimit ??
   StartServers ??
   MaxClients ??
   MinSpareThreads ??
   MaxSpareThreads ??
   ThreadsPerChild ??

what values should i set to this variable to get the best performance

system specification

amazon RDS 100GB DB STORAGE db.t1.micro

1 amazon LBS having two node attached to it each with

   amazon EC2 m1.small 1.7 GB RAM and 8 GB MAGNETIC HARD DISK
   DEBAIN 7.1 WHEZZY O.S


texts: Optimised way to search over 2 milllion poi data in mysql

texts:
We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4

select *, ( 3959 * acos( cos( radians(
-- latitude
@my_lat) ) * cos( radians(
destination.latitude ) ) * cos( radians(
destination.longitude ) - radians(
-- longitude
@my_lon) ) + sin( radians(
-- latitude
@my_lat) ) * sin( radians(
destination.latitude ) ) ) ) AS distance
-- table containing targets to compare distance
from destination
order by distance limit 1
;

The problem we find this taking too long. Some forum suggest to limit the search based on +1 and -1 the lat and long value. The problem now we need to provide minimal one poi. So any optimisation help on this or better way of doing this?


texts: PL/SQL function to receive a number and return its binary format

texts:
I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for other numbers.
I could not find the problem and I was wondering if you could find the problem?

create or replace function Show_Binary(i_Number in Number) Return Varchar2
  AS

     V_Binary varchar2(50) := '';
     i_Number2 Number := i_Number;
       Begin
         While i_Number2>=2 LOOP

          V_Binary := V_Binary || Remainder(i_Number2, 2);
          i_Number2 := i_Number2 / 2;

       End LOOP;
          V_Binary := V_Binary || TO_CHAR(i_Number2);
          select reverse (V_Binary) into V_Binary from dual;
      return (V_Binary);
   End;


texts: Connection failed: No such file or directory Mysql - WSL

texts:
Sorry if i posted incorrect section.
I have Windows with this WSL:

PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian

On my WSL i installed php7.3-fpm and Nginx, i've tested and both were working. I can't installed mysql on WSL i've got much errors then i installed Mysql on my Window and created a simple database.

The problem is when i try to connect my database with this code:

<?php
$servername = "localhost";
$username = "root";
$password = "root";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed ttt: " . $conn->connect_error);
}
echo "Connected successfully";
?>

And I got this message:

  Connection failed: No such file or directory

I think it's because my Mysql is on Windows and php interpreter is on WSL and can't comunicate.
Someone can help me please.


texts: Allowing users to compile / run PLSQL and SQL

texts:
As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code.
We have an MI database environment where the data has undergone ETL.
The tables contain a large numbers of rows but not necessarily large rows in size. The analysis - will be - I assume of calculus in nature.
The users are wanting to run their own code on the data for the purposes of ad-hoc analysis - within their own schemas.
They come from a SAS background which from my understanding has allowed them to write and run their own analysis of data. Albeit, SAS runs on its own dataset - extracted from Oracle or elsewhere.

In my opinion this is a dangerous set-up - but without answering my own question - what are the pitfalls?


texts:
We are looking for a solution for the following problem:

We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage periodically.

I have looked at:

WAL-e which seemed to suit us perfectly, except that it does not support multiple tablespaces, which is a deal breaker.
Barman supports this but does not support S3, which is also a deal breaker, although it may be possible with pre- and post-backup hooks which I am currently looking into.
OmniPITR doesn't support S3 either.

Is there an application that does all these things already, or will I have to do some scripting?


texts: what is better in terms of allocated space in Oracle Tables: NUMBER or BINARY_FLOAT?

texts: I have a 10 billion records table so I need to improve space allocation. My table has only fields of type floating point (single-precision). What is the best choice, NUMBER or BINARY_FLOAT or something else ?


texts: Mysql query group by to be combined with order by desc

texts:
I have the following query as below.
SELECT Day(vehicleTrip.startDateTime) As day,
       Month(vehicleTrip.startDateTime) As month,
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime )) As totalDuration,
       count(vehicleTrip.startDateTime) As totalTrip,
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip
WHERE vehicleTrip.vehicleID=:vehicleID
  AND vehicleTrip.vehicleEndCodeID!=1
  AND vehicleTrip.startDateTime BETWEEN :startDateTime And :endDateTime
Group By Day(vehicleTrip.startDateTime),Month(vehicleTrip.startDateTime)

Currently it works fine cause it can group by the daily date the sum of duration, totalTrip, totalMileage etc. But only issue I want the date to be arrange by desc. I have tried putting the order by startDateTime desc it gives me error that its not in GROUP By clause and containts nonaggregated column. I have tried many method like even putting the Max(vehicleTrip.startDateTime) still the same. How best can I resolve this ?
Below is my table design with its index.
CREATE TABLE `vehicleTrip` (
  `vehicleEndCodeID` tinyint NOT NULL DEFAULT '1',
  `vehicleID` mediumint NOT NULL,
  `startDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `endDateTime` timestamp NULL DEFAULT NULL,
  `startMileage` float(11,3) DEFAULT NULL,
  `endMileage` float(11,3) DEFAULT NULL,
  `startLatitude` float(10,8) NOT NULL,
  `startLongitude` float(11,8) NOT NULL,
  `endLatitude` float(10,8) DEFAULT NULL,
  `endLongitude` float(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `vehicleTrip`
  ADD PRIMARY KEY (`vehicleID`,`startDateTime`) USING BTREE;
COMMIT;


texts: ORA-01403 _sometimes_ while reading a file on Oracle 18c

texts:
Edit:
We just checked: it also happens in on-premise Oracle 18c, latest patchset and everything.
In Oracle PL/SQL we are opening and reading a file. The file exists, we are getting data, but then sometimes while reading, the process gets killed with an ORA-01403: no data found.
Funny thing is: we restart the exact same procedure with the exact same file and it runs flawlessly, just in 1 out of maybe 25 runs, we're getting the error.
This problem first occured after we migrated from on-premise Oracle to Amazon RDS Oracle 18c, never before.
Is this some strange Oracle 18c memory/buffer problem? We can't wrap our heads around this issue...