texts:
I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has the following fields:

|ID|Plate|Date_in|Time_in|Date_out|time_out|

There are also two other tables, called "tblin_buffer" and "tblout_buffer" which have the following fields respectively:

|Plate|Date_in/out|Time_in/out|

There are two Number-plate reading cameras pointed at the entrance and exit that produce a file when a plate is read. There is a program running on the database server machine that detects when a file is created then loads the data into these buffer tables

The intention is for data in these tables to be transferred into the main table with an Insert command for the IN data and an Update command for the OUT data. These commands should function for the most recent entries in the  buffer tables and ignore the others that have already been transferred into the main tbl_stay.

I have tried a few methods suggested in the past that have come close to what I have been aiming for, but haven't been quite right. most of the time this is because days and/or times have not been matching up properly.

This insert/update method must be used because of an active query that gets run on the table that requests a list of "vehicles on site", indicated by vehicles that have an Entrance time but do not have an exit time.

Additional information:

Previous Insert ideas for IN data:

Original idea:

insert into tbl_stay (Plate,Date_in,Time_in)
select Plate,Date_in,Time_in
from tblin_buffer
where not exists (
select * from tbl_stay
where (tblin_buffer.Plate=tbl_stay.plate
and tblin_buffer.Date_in=tbl_stay.Date_in
and tblin_buffer.Time_in=tbl_stay.Time_in))

second idea, following from some guidance from another question. this was good, but it was copying the entire contents of tblin_buffer each time. i got around this by adding the "Where" clause at the bottom.:

insert into tbl_stay (Plate,date_in,time_in,rank_in)
SELECT
a.plate, a.date_in, a.time_in,rank
FROM (
SELECT
x.plate,
x.date_in,
x.time_in,
COUNT(*) rank
FROM tblin_buffer x
JOIN tblin_buffer y
ON y.plate = x.plate
AND ((y.date_in < x.date_in)
OR (y.date_in = x.date_in
AND y.time_in <= x.time_in))
GROUP BY x.plate, x.date_in, x.time_in) a
where a.plate = "<PLATE VALUE>" and a.time_in like"%<TIME VALUE>%"

previous Update ideas for OUT data

Original idea, this ended up copying the first exit time into all following entries of the same plate exiting:

update tbl_stay
join tblout_buffer
on tblout_buffer.plate=tbl_stay.plate
set tbl_stay.date_out=tblout_buffer.Date_out,
tbl_stay.time_out=tblout_buffer.time_out
where tbl_stay.date_in=tblout_buffer.date_out

second idea, following on from guidance from another question. this solution came close, but some cases return incorrect dates, if the plate gets read multiple times in a row.

update tbl_stay
JOIN
( SELECT distinct x.plate, x.date_out, x.time_out, COUNT(*) rank
FROM tblout_buffer x
JOIN tblout_buffer y
ON y.plate = x.plate
 AND ((y.date_out < x.date_out)
OR (y.date_out = x.date_out
AND y.time_out <= x.time_out))
GROUP BY x.plate, x.date_out , x.time_out ) b
ON b.plate = tbl_stay.plate
AND b.rank = tbl_stay.rank_in
set tbl_stay.date_out=b.date_out,
tbl_stay.time_out=b.time_out, tbl_stay.rank_out=b.rank;

SQL command for inserting contents of tblout_buffer

SQL command for inserting contents of tblin_buffer

What do you recommend as a solution to this? Your input is greatly appreciated.


texts: How to retrieve all the rows where primary key value found in the other column in the row in MySql

texts:
Following is MySql Table in the snapshot:

column sno could be found in the other rows in column p_sno. i.e.

sno = 5   linked through p_sno = 5 with sno = 6 (two rows)

sno = 8   linked through p_sno = 8 with sno = 9 while
sno = 9  linked through p_sno = 9 with sno = 10 (three rows)

Similarly

sno = 13 linked to sno = 14 by p_sno = 13 (two rows)

Now, what i need from this table is to return

sno = 5 and sno = 6 if someone search by temp_no = 2017-05 or temp_no = 2017-06

Same thing with other rows for example if somebody search with temp_no = 2017-08 it should return three rows i.e. sno = 8, sno = 9 and sno = 10 and so on so forth.

help appreciated in advance.


texts:
Possible Duplicate:
  Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

I have two tables, Users and People, both of which share a common attribute, email address, of which they should be allowed to have many email addresses. A User has many People, a Person belongs to a User.

I can see three options myself:

One link table with redundant columns:

Users [id,email_id] and People [id,email_id]
EmailAddress [user_id,person_id,email_id]
Emails [id,address,type]

Two link tables without redundancies:

Users [id,email_id] and People [id,email_id]
PersonEmail [person_id,email_id]
UserEmail [user_id,email_id]
Emails [id,address,type]

No link tables with redundant columns:

Users [id] and People [id]
Emails [id,address,type,user_id,person_id]

Does anyone have any idea what would be the best option, or if there is any other ways?

UPDATE: I figured that it may be a good idea to use inheritance. In the system, the User is conceptually different as they have permission to login and use the system and they retain information on people, much like having their own address book. Yes, the users and people will share many attributes, over name, email, phone, address, etc.


texts: MySQL - Log data with Inner Join 12 Tables or Create View?

texts:
I'm using MySQL (phpmyadmin) with engine InnoDB for my e-commerce website.

Now, I need to get the data as Log but It's need to joining more than 12 tables for logging all data we need to see, only last 30 data submitted (done / reject) will show in pagination (Log page 1) but still have pagination for next last 30.

9 INNER JOIN
3 LEFT JOIN (It's Admin Order Form when customer Ordering via Offline (friend))
WHERE order_status = 1 OR order_status = 2 (done & reject)
ORDER BY order_id DESC
LIMIT 30 (limiting 30 per page with pagination)

The order data is more than 2.000.000 rows for 4 tables in INNER JOIN connected in PK & FK.

Any problem if I'm joining 12 tables OR I should create table view or something ?

Sometime we need to make sure storage is saved with normalization, sometime we need the performance faster. (but still not wanted to use denormalization data for log).

Any solution ? Thank you.


texts: Create Log Shipping Step Failed After ServerName Changed

texts:
I was doing some test on server name change and encounter some errors.

Here is the original setup: Server name - ServerA, SQL Server default instance - ServerA

Changes: Server name - ServerB

Before I change the SQL Server default instance 'servername'

SELECT @@SERVERNAME, SERVERPROPERTY('SERVERNAME')

Return ServerA, ServerB (since I have changed the server name)

exec sp_add_log_shipping_primary_database completed successfully. However, there was error when running the log shipping backup job,

  Error: A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections.

As expected. So I change the SQL Server default instance 'servername',

EXEC sp_dropserver 'ServerB'
EXEC sp_addserver 'ServerA', 'local'

Restart the SQL Server service.

SELECT @@SERVERNAME, SERVERPROPERTY('SERVERNAME')

Return ServerB, ServerB. OK, I delete the previous log shipping backup job. Run the sp_add_log_shipping_primary_database but receive this error,

  Msg 14234, Level 16, State 1, Procedure sp_verify_jobstep, Line 214
  The specified '@server' is invalid (valid values are returned by
  sp_helpserver).

sp_helpserver return ServerB under name and network_name column with id 0

So question is, am I missing some steps over changing servername or is this a bug?

Version - SQL Server 2012 SP1


texts: How to improve performance for table JOINS involving millions of rows

texts:
I am dealing with a performance issue for a very simple structure involving simple queries.

Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary key for TBL01 is called CONFIGURATION_ID. TBL02 has CONFIGURATION_ID as a foreign key to TBL01 which is also indexed. TBL01 has 2.8 million rows and TBL02 has maybe 10 million rows.

A simple INNER JOIN on TBL01.CONFIGURATION_ID = TBL02.CONFIGURATION_ID is kinda slow. By slow I mean it takes a second to return 10 rows with 15 columns in total from both TBL01 and TBL02. If I start using sorting etc, things get worse. I have dealt with very big tables in the past but never had an issue. Could there be something missing?

What should my next steps be in order to improve the performance? BITMAP index with JOIN on CONFIGURATION_ID? Would that help? Any other tips?

Note 1: I am using ORACLE 12.

Note 2: This might be obvious, but if I add a filter after the join things are better.


texts: Do databases optimize repeated similar query params on the same column?

texts:
I have the following query:
SELECT * FROM table t
WHERE t.id IN :ids
AND t.id IN :allowedIds

(where the query params are later replaced)
Are these query params optimized by the database itself? I would like to avoid having to merge the two sets of ids together in code because it makes for poor readability. The first :ids constraint is what the user themselves passes on as a filter and the second :allowedIds constraint is what the user has access to. So I am hoping most databases, but Postgres in particular, would optimize this sort of thing.


texts:
Environment:

MongoDb: 3.0 Enterprise Edition

Linux: Red Hat Enterprise Linux Server release 6.4 (Santiago)

Use case:

We need to have the above three MongoDb users which are not present in LDAP (they need to be available for technical modifications)
We will have ~60 users which will be authenticated via LDAP

We need to be able to run MongoDb authentication and LDAP authentication in parallel.

We have installed MongoDb and created three different users on MongoDb:

admin
repAdmin
ROOT

Afterwards we have enabled LDAP configuration as per documentation on
http://docs.mongodb.org/manual/tutorial/configure-ldap-sasl-activedirectory/

After that we've added two LDAP Users to the MongoDb ("$external") for testing purposes.
Then we enabled the auth option in the MongoDb Configuration file and restarted MongoDb.
After we've enabled the auth option, all authentication is failing, LDAP as well as MongoDb.

Only the if the auth option is disabled the users which are present in LDAP are getting authenticated successfully.

Problem:
How can we authenticate the three users (admin, repAdmin and ROOT), which are not
available in LDAP, over the MongoDb Shell and all other users via LDAP?

Many thanks for your support :)


texts:
I have configured my SSRS 2016 weburl after installation.When opening weburl it is given error ..
HTTP Error 503. The service is unavailable.
Tried all the possible options given on internet.
Could anyone give suggestion that worked for them.

Thanks in advance!!


texts: Converting multiple SQL implicit joins into explicit joins

texts:
I am trying to convert several queries that use implicit joins into explicit joins, as per the standard ANSI/ISO Entry SQL-92 [1].
But after a lot of googling I could not find any examples or guides on how to proceed with the same. Could someone point me in the right direction..? Thanks!

Sample code:

    SELECT *
    FROM a,b,c,d,e,f,g
    WHERE
01:     e.p_id(+)   = a.p_id
02: AND e.o_id(+)   = a.o_id
03: AND c.c_id      = h.c_id(+)
04: AND c.b_id      = h.d_id(+)
05: AND g.d_id (+)  = h.d_id
06: AND g.o_id (+)  = h.o_id
07: AND a.p_id      = b.p_id
08: AND c.p_id      = a.p_id
09: AND d.c_id      = c.c_id
10: AND a.o_id      = f.e_id
11: AND g.o_id      = a.o_id

UPDATE

Based on my understanding of Ted Elliot's answer, I have tried to reproduce the algorithm:

But not clear on the steps 9, 10.

1.List all tables alphabetically: a,b,c,d,e,f,g

2.Start with first table 'a'. Identify all join conditions: 'a' joins with b/c/e/f/g. Pick any one, say 'e'. Keep 'a' to the right.

e
RJ a ON
e.p_id = a.p_id AND
e.o_id = a.o_id

3.Next is 'b'. Is 'b' joined with a/e above? Yes -> Continue.
Identify all join conditions: 'b' joins with a. Keep 'b' to the right.

...
INNER JOIN b ON
a.p_id = b._pid

4.Next is 'c'. Is c joined with a/b/e from above? Yes -> Continue.
Identify all join conditions: 'c' joins with h/a/d. Selected 'a'. Keep 'c' to the right.

...
INNER JOIN c on
c.p_id = a.p_id

5.Next is 'd'. Is d joined with a/b/c/e from above? Yes -> Continue.
Identify all join conditions: 'd' joins with c. Keep 'd' to the right.

...
INNER JOIN d on
d.c_id = c.c_id

6.Next is 'e'. Is e joined with a/b/c/d/e from above? e already exist in list -> Skip.

7.Next is 'f'. Is f joined with a/b/c/d/e from above? Yes -> Continue.
Identify all join conditions: 'f' joins with a. Keep 'f' to the right.

...
INNER JOIN f on
a.o_id = f.e_id

8.Next is 'g'. Is g joined with a/b/c/d/e/f from above? Yes -> Continue.
Identify all join conditions: 'g' joins with a/h. Selected 'a'. Keep 'g' to the right.

...
INNER JOIN g on
g.o_id = a.o_id

9.Next is 'h'. Is h joined with a/b/c/d/e/f/g from above? Yes -> Continue.
Identify all join conditions: 'h' joins with c/g. Selected 'c'. Keep 'h' to the right.
Note that the conditions require all records of 'c' to be joined with 'h': "c.c_id = h.c_id(+) AND c.b_id = h.d_id(+)". How can we ensure this as 'c' has been joined with a/d above so not all records of 'c' are present in the temp. table of above joins?

...
INNER JOIN h on
c.c_id      = h.c_id AND
c.b_id      = h.d_id

10.Table list completed, But there are two conditions remaining: "g.d_id (+)  = h.d_id AND g.o_id (+)  = h.o_id". Identify all join conditions: 'g' joins with h. Keep 'g' to the right. Note that the conditions require all records of 'h' to be joined with 'g'. But again h is joined above so not all records are available.

...
INNER JOIN g on
g.d_id = h.d_id AND
g.o_id = h.o_id


texts: Copy real time data from Azure SQL into Azure Data Warehouse

texts:
I would like to copy our data from Azure SQL (OLTP) into Azure Datawarehouse. I want the path to be close to real time.
The Microsoft system looks it takes two steps copying data from SQL Server into Azure Data Warehouse.

First into Blob Storage with Azure Data Factory

and then with Polybase into Azure Datawarehouse.

It looks like a two step ETL and not close to real time.
Does Microsoft have any simpler way of copying data from Azure SQL into DW for analytics?


texts:
I am using the CodePlex project SQL Server PowerShell Extensions (SQLPSX) to generate script files for replication.  Everything is working as I want except for one thing.  I have looked through the PowerShell code to see if I could suppress this and could not find it.  I have tried to create secondary process to remove this item, but my PowerShell skills are not that great.

The output has this line that I do not want.

    exec [DatabaseName].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
    GO

We do not use qreader and I don't want this in my output files.

My question is two fold.
1.)  Does anyone know how I can modify the SQLPSX code to not write this output. (Preferred)
2.)  Add to PowerShell script below to loop through all output files created and remove the two lines listed above.

PowerShell script I found that loops through and generates output files.

param ($sqlServer,$path,[switch]$scriptPerPublication)
Import-Module Repl

if ($sqlServer -eq "")
{
$sqlserver = Read-Host -Prompt "Please provide a value for -sqlServer"
}

if ($path -eq "")
{
$path = Read-Host -Prompt "Please provide a value for output directory path"
}

$scriptOptions = New-ReplScriptOptions
$scriptOptions.IncludeArticles = $true
$scriptOptions.IncludePublisherSideSubscriptions = $true
$scriptOptions.IncludeCreateSnapshotAgent = $true
$scriptOptions.IncludeGo = $true
$scriptOptions.EnableReplicationDB = $true
$scriptOptions.IncludePublicationAccesses = $true
$scriptOptions.IncludeCreateLogreaderAgent = $true
$scriptOptions.IncludeCreateQueuereaderAgent = $true
$scriptOptions.IncludeSubscriberSideSubscriptions = $true

$distributor = Get-ReplServer $sqlserver

if($distributor.DistributionServer -eq $distributor.SqlServerName)
{
$distributor.DistributionPublishers | ForEach-Object {
    $distributionPublisher = $_
    if($distributionPublisher.PublisherType -eq "MSSQLSERVER")
    {
        $outPath =  "{0}\from_{1}\{2}\"  -f    $path,$distributionPublisher.Name.Replace("\","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
        New-Item $outPath -ItemType Directory | Out-Null
        Get-ReplPublication $distributionPublisher.Name | ForEach-Object {
            $publication = $_
            $fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", "")
            if($scriptPerPublication)
            {
                $fileName = "{0}{1}_{2}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
            }
            Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
            Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName
        }
    }
}
}
else
{
$outPath =  "{0}\from_{1}\{2}\"  -f     $path,$distributor.SqlServerName.Replace("\","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
New-Item $outpath -ItemType Directory | Out-Null
Get-ReplPublication $distributor.SqlServerName | ForEach-Object {
    $publication = $_
    $fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ",  "")
    if($scriptPerPublication)
    {
        $fileName = "{0}{1}_{2}.sql" -f  $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
    }
    Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
    Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName
}
}


texts: A confusing thing said in the PostgreSQL manual:

texts:
For some releases, manual changes may be required to complete the upgrade, so always read the release notes before upgrading.

Source: https://www.postgresql.org/support/versioning/

It's talking about some minor releases in the quoted sentence.

Doesn't this mean that it's impossible to automate updating of PostgreSQL, even for minor versions?
What kind of "manual steps" are required, and how often is that the case?

This is exactly the kind of thing which makes me scared of and unwilling to update software, even crucial core software like PostgreSQL. I'm 100% sure that I'm not the only one who just mentally blocks out the need to update software when we read remarks like this. The thought of manually having to keep track of every damn changelog is literally Hell to most of us.


texts: What does this error mean in IBM Netezza Administrator? - ERROR: Permission denied on "_VT_DISK_PARTITION"

texts:
What does this error mean when opening IBM Netezza Administrator?

ERROR: Permission denied on "_VT_DISK_PARTITION"


texts: is it possible to run postgres with no WAL files being produced?

texts:
I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced.

We have a end of day batch process which generates a large number of WAL files (and has filled the disk a couple of times). We also take a pg_dump backup both before the start of the end of day process and also at the end. We are happy that should there be a problem during the batch process to restore the pre pg_dump backup.

When i set the wal_level to minimal we are still getting a large number of WAL files due to the type of database updates which occur. So is it possible to have no WAL files as all (basically a wal_level=none)?

thanks
dave


texts: Will frequent DML operations on a table with indexes affect performance of Select Query?

texts:
If I have frequent DML operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where condition on index columns,
Or will the performance remain same as when there are no DML operations?
This Link says UpdateStatistics does block Select queries. So will DML operations cause UpdateStatistics.
It says " The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.".
So is changing these values the best solution?
Note: I will be using default isolation level (Read Uncommitted) for the SELECT query.


texts: Overwriting old data in Azure SQL database

texts:
I`m currently recieving data from sensor and storing it to Azure SQL database through Azure Stream Analytics job.
My question is: how can I overwrite old data from sensor with new data? Sensor has its own ID, so is it possible to overwrite data by sensor ID.


texts:
Good morning all
I am working on a Biztalk migration project On an alwayson SQL environment
Who has an idea if BIztalk is compatible with ALWAYSON if YES it is from Which version?
another question for the activation of the MSDTSC function should you activate this option in Always on
thank you for your help


texts: Is it possible to query list of package local types from system catalogue?

texts:
Trying to identify a way how to extract from system catalogue a list of package local types (i.e. types that are defined inside of  a package).

For example, I have defined following package specification (package body is omitted for now):

CREATE PACKAGE test_package AS
    TYPE TimeRec IS RECORD(
        Minutes SMALLINT,
        Hours SMALLINT);
    PROCEDURE test_subprogram(date VARCHAR2);
END;

I am able to get a list of subprograms that belong to test_package by querying DBA_PROCEDURE system view.

Would like to do the same but for package types but not able to find corresponding view. Can anybody suggest one, if exists (I hardly believe though that this info is not stored in catalogue)?

I for sure can extract package sources and look through it in order to identify which types are defined in there but this is not exactly what I need.

Limitations:

access rights are restricted to SELECT only

Thanks for a help


texts: How to resynchronize replica in Postgresql

texts: I have a server with Centos6 and Postgresql 9.2, I have set up a replica in postgresql to another server of the same characteristics, today in the morning I have an error in the archiver process on the primary server, the replica does not configure it I, I really do not have a lot of knowledge about this topic, yesterday I had to delete some archived files in the primary for lack of space but later I checked the replica creating some tables and they replicated to the secondary, I thought it was all right ... I would like to know how to verify that the replica is working and if not how I can resynchronize the secondary. Thank you very much in advance


texts: Find all version of SQL Server on a network?

texts:
There seems to be a bit of SQL Server sprawl at a client and I'd like to document all of them.

What is the best way to find all installed versions of SQL Server on a network?


texts: Should fields that are set to Not Null also have a constraint refusing empty strings?

texts:
Any field that I would expect a value (Like an orderNumber or customerNumber) I would set as not nullable.

If I'm expecting the value of the field to be not null, shouldn't I also always have a constraint that does not allow for zero length strings or space only strings?


texts: Explicitly granting permissions to update the sequence for a serial column necessary?

texts:
Recently I did create a table as a superuser including a serial id column, e.g.,

create table my_table
(
    id serial primary key,
    data integer
);

As I wanted my non-superuser user to have write access to that table, I granted it permissions:

grant select, update, insert, delete on table my_table to writer;

At a random point in time after doing so, the insertions made by that user started to fail because the user lacked permission to modify the sequence my_table_id_seq associated to the serial column. Unfortunately I can't reproduce that on my current database.

I worked around this by giving the user the required permission, like this:

grant all on table my_table_id_seq to writer;

Can someone help me understand

why, at some point, the previously sufficient permissions might start to fail?
what is the proper way to grant write permission for a table with a serial column?


texts: This sql select must be optimized...how?

texts:
Here is my query

SELECT DISTINCT
    SM.sales_date,
    (SELECT
            SUM(gross_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
    (SELECT
            SUM(net_sale)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TNET,
    (SELECT
            SUM(cash)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCASH,
    (SELECT
            SUM(cheque)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCHEQUE,
    (SELECT
            SUM(credit)
        FROM
            sales_main AS INTERNAL
        WHERE
            INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TCREDIT,
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '4%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '4%'
            ,
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '6%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '6%',
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = '10%' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS '10%',
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = 'M/R' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'M/R',
    (SELECT
            SUM((SELECT
                        amount
                    FROM
                        sales_main_deduction AS INTERNAL
                    WHERE
                        INTERNAL.instance = 'S/R' AND INTERNAL.idsales_main = SMinternal.idsales_main))
        FROM
            sales_main AS SMinternal
        WHERE
            SMinternal.sales_date = SM.sales_date AND SMinternal.vehicle_name=SM.vehicle_name) AS 'S/R'
FROM
    sales_main AS SM
WHERE
    (SM.sales_date >= '2013-06-01' AND SM.sales_date <= '2013-06-05') AND SM.vehicle_name='Lorry1'

and this is the result..

2013-06-01  59582.03  58336.59  32096.96  11141.02  15098.59  1245.44
2013-06-03  132334.45  126461.84  33138.93  11046.63  82276.28  1774.46 557.02  3541.13  2013-06-04  72184.69  71882.93  27840.74  12410.16  31632.03   152.16    149.6
2013-06-05  93478.20  86721.32  34461.47  24971.24  27288.6  934.48  3553.32  2269.08

But this if this queryextends to 31 days of month it takes 10 mins to return a results from 2000 rows table ....please help..


texts: How to isolate client data on the same application using MySQL

texts:
I have made an application which has a companies table at the moment. It would work fine for a few clients (~100 companies, according to my scaling calculations). The application is a simple CRUD app accessed on the web using REST protocols. Very few (if none) concurrent transactions take place at the same time.

At the moment the companies table is the most independent table of all. All others like employees, benefits, time_off, etc. are related to each other via the companies table.

Someone suggested that I could use MySql's schemas to isolate one company from another, and clients would see the same exact app. Essentially, the moment I sign up a new company, a new schema would fire up and create the same tables as it has for any other company. How does this work? Could I have multiple schemas on the same database? I'm working with AWS RDS so I don't wish to pay for multiple databases here as the total size of the data even after half a year's use has not exceeded half a GB.


texts: Duplicate delete query in binary log of MySQL Master

texts:
We are facing a strange problem, while replicating data from master to slave. Same data is being deleted twice in log (however, only one row is deleted and one time), due to which we are getting error while replicating:

# at 28651234
#150423 12:21:03 server id 5170  end_log_pos 28651303 CRC32 0x0b496431  Delete_rows: table id 73 flags: STMT_END_F
### DELETE FROM `oauth`.`AccessToken_scopes`
### WHERE
###   @1=1040103520
###   @2='wallet'
### DELETE FROM `oauth`.`AccessToken_scopes`
### WHERE
###   @1=1040103520
###   @2='wallet'

To avoid this,we currently have added below parameter in my.cnf file:

slave-skip-errors


texts:
I have a table name: my_table

Column: ID, name, department (department is a drop down list as below:

AOD
CRM
RMD

Now I want to that when auto increment will generated it will depend on department as like below.

If department AOD then ID will generated as below:

AOD00001
AOD00002
AOD00003

If department CRM then ID will generated as below:

CRM00001
CRM00002
CRM00003

If department RMD then ID will generated as below:

RMD00001
RMD00002
RMD00003

or

AOD00001
CRM00002
AOD00003
RMD00004
CRM00005


texts:
We have two Sql database servers (windows 2008 datacenter r2) with same name but with different IP. can it be possible?

we are not able to connect second one which we have created after. Both are on amazon aws

I have checked log and found this error .

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).

the Ip of second server is private . we have set remote settings.Ports are also opened in the firewal. We are able to ping to the server but cannot connect the databases


texts: Can't start mysqld service after changing datadir

texts:
I have an AWS EC2 instance with RHEL 7 on which I installed mysql-5.5 on xvda. Its datadir is under /var/lib/mysql (as is the default).

I am importing dumps from a database on version 5.5.29 and the currently installed version is 5.5.60

A larger volume xvdf has been attached since I was running out of space and mounted it at /data_drive

Then followed these steps to change the data directory:

Stop mysqld and verify if it had stopped
used rsync -av /var/lib/mysql /data_drive to move folder with permissions
moved the older mysql data directory to a backup file on the xvda (original volume)
Edited /etc/my.cnf to change the [mysqld] block to reflect the new location in both datadir and socket

datadir=/data_drive/mysql
 socket=/data_drive/mysql/mysql.sock
added the client block at the end of the file

[client]
 port=3306
 socket=/data_drive/mysql/mysql.sock
saved the file and then tried to restart the mysqld service as root user and non-root user but it won't start.

Here is the /var/log/mysqld.log output from the relevant timestamps:

180713 19:27:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180713 20:20:37 mysqld_safe Logging to '/var/log/mysqld.log'.
180713 20:20:37 mysqld_safe Starting mysqld daemon with databases from /data_drive/mysql
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Note] /usr/sbin/mysqld (mysqld 5.5.60) starting as process 18669 ...
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Warning] Can't create test file /data_drive/mysql/ip-10-0-0-180.lower-test
180713 20:20:37 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
180713 20:20:37 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180713 20:20:37 InnoDB: The InnoDB memory heap is disabled
180713 20:20:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180713 20:20:37 InnoDB: Compressed tables use zlib 1.2.3
180713 20:20:37 InnoDB: Using Linux native AIO
180713 20:20:37 InnoDB: Initializing buffer pool, size = 128.0M
180713 20:20:37 InnoDB: Completed initialization of buffer pool
180713 20:20:37  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

When I ran sudo mysql_upgrade it gave the following output:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

This is how the /data_drive/mysql directory looks at the moment:

[ec2-user@ip-10-0-0-180 mysql]$ ls -al /data_drive/mysql/
total 5921812
drwxr-xr-x. 4 mysql    mysql       4096 Jul 13 18:45 .
drwxr-xr-x. 5 ec2-user root        4096 Jul 13 18:46 ..
-rw-rw----. 1 mysql    mysql 6053429248 Jul 13 18:45 ibdata1
-rw-rw----. 1 mysql    mysql    5242880 Jul 13 18:45 ib_logfile0
-rw-rw----. 1 mysql    mysql    5242880 Jul 13 18:35 ib_logfile1
drwx------. 2 mysql    root        4096 Jul 12 19:42 mysql
drwx------. 2 mysql    mysql       4096 Jul 12 19:42 performance_schema


texts: How does PostgreSQL's citext type affect string comparisons made using LOWER()?

texts:
The impetus for my question is that I had hoped that PostgreSQL would behave consistently when selecting from citext columns, regardless of whether or not the string to be matched is wrapped in one or more instances of lower() (any such wrapping is beyond my control). That appears not to be the case. (Of course, it is entirely possible that my tests are invalid or I am misunderstanding fundamental concepts.)

Steps to Reproduce Testing Scenario

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (id int, email citext);
INSERT INTO users(id, email) VALUES
  (1, 'USER@example.com');

Tests

As expected when using the citext type, the lowercase variant yields a result:

# select * from users where email = 'user@example.com';
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

Changing the = operator to like yields a result:

select * from users where email like lower('user@example.com');
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

As does the "inverse":

# select * from users where lower(email) = 'user@example.com';
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

As does wrapping both values in lower():

# select * from users where lower(email) = lower('user@example.com');
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

My Question

Why then does the following query not return a result in this instance?

# select * from users where email = lower('user@example.com');
 id | email
----+-------
(0 rows)

The manual says of the citext type:

  Essentially, it internally calls lower when comparing values.

The operative word seems to be "essentially"; this statement implies the following, which does yield a result:

select * from users where lower(email) = lower(lower('user@example.com'));
 id |      email
----+------------------
  1 | USER@example.com
(1 row)

Might this be related to the following caveat in the Limitations section of the above-cited document?

  citext's case-folding behavior depends on the LC_CTYPE setting of your database.

# SHOW LC_CTYPE;
  lc_ctype
-------------
 en_US.UTF-8
(1 row)

Any explanation in this regard is much appreciated.


texts:
this is first time i am try mapping table in mysql  i write this procedure  but not work ? whats wrong ?

        DELIMITER $$

       CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_GetInformation`(
                    IN cos_ID INT
                    )
      BEGIN
      SELECT
                     students.ID as 'StudentID',
                     course.ID as 'CourseID',
                     students.name,
                     course.course_name,
                     course.cost_fee,
                     students_Course.net_cost
            FROM
                     students INNER JOIN  students_course on students.ID     = students_course.students_ID
                     inner join course on course_ID = students_course.course_ID
            where
                     course.ID = cos_ID;

END


texts:
I'm working on a creating a database schema for a legacy app that currently saves its data to XML files.

I'm having trouble representing this relationship:

Task Group 1 contains
|-- Simple Task 1
|-- Simple task 2
|-- Task Group 1a contains
    |-- Simple Task 3
    |-- Simple Task 4
    |-- Task Group 1b contains
        |--Simple task 5

Basically, a task group may contain one or more tasks.  These may be simple tasks or other task groups.   So the nested groups is what I'm having trouble with.

This is what I have so far:

CREATE TABLE `task_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `processing_type` varchar(45) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_parent_idx` (`parent_id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `task_group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: Return the uniqueidentifier generated by a default on insert

texts:
Goal
Retrieve the latest guid value in real time after you have inserted the value in the table
Problem
Don't know how to do it
Info

The code should only specify new values for address and zipcode
There can be lots of data in the table

Table
CREATE TABLE [AddressBook]
(
    [testID] [uniqueidentifier] NOT NULL default newid(),
    [address] [nvarchar](50) NULL,
    [zipcode] [nvarchar](50) NULL
)


texts: MySQL Computed Column counting other rows with attributes relative to this one

texts:
I have a table in a MySQL database that has information about passenger flights. I want to do some analysis of flight delays. I would like to add a calculated attribute to each row that indicates how many flights arriving at the current row's departure airport, by the same carrier, were delayed in the past 24 hours.

I haven't had any luck writing this query. I think that I can express my intent with:

-- Find a count of all rows
    count(*) where
-- Of the same carrier
    a.FlightCarrier = thisRow.FlightCarrier
-- that arrived at our departure airport
    a.DestinationAirport = thisRow.OriginAirport
-- scheduled to arrive before ScheduledDepartureTimeUTC
    a.ScheduledArrivalTimeUTC < thisRow.ScheduledDepartureTimeUTC
-- scheduled to arrive after (ScheduledDepartureTimeUTC - 24h)
    a.ScheduledArrivalTimeUTC > (thisRow.ScheduledDepartureTimeUTC - 24h)
-- had a delayed arrival
    a.DelayedArrival = 1

I attempted to write this as a SQL statement:

alter table flight add column DelayedCarrierArrivals24H int as
(select count(*) from flight a where
 a.FlightCarrier = FlightCarrier
 and a.DestinationAirport = OriginAirport
 and a.ScheduledArrivalTimeUTC < ScheduledDepartureTimeUTC
 and a.ScheduledArrivalTimeUTC > (ScheduledDepartureTimeUTC - (60 * 60 * 24))
 and a.DelayedArrival = 1) STORED;

However, MySQL balks at this with "You have an error in your SQL syntax... near 'select count(*) from flight a where a.FlightCarrier = FlightCarrier"

What's the 'right' way to create this column in MySQL? I had originally thought to use windowing functions but couldn't divine the right way to express my intent with those features, either.


texts: MongoDB insert not replicating after db.dropDatabase()

texts:
I'm new to MongoDB (inherited a project using it), so this is especially confusing.

I have a sharded replicaset between 3 ec2 instances. Everything works fine until I drop a database. The database drop replicates just fine - I can verify that - However, all subsequent inserts becomes out of sync, and only happen on the particular mongos instance the insert was performed on.

So here's the full process:

Connect to mongos at primary:27017
use someDatabase;
db.dropDatabase();
Connect to other 2 mongos and verify DB is dropped.
Connect back to primary:27017 and insert a new document in that database
Connect to other 2 mongos and realize the insert has not been replicated.

Everything was staying in sync until I dropped the database.

Should I not be performing these commands from from the mongos, and instead the primary replica member? I haven't figured out how to clean everything up yet to try and test that.

Thanks in advance!

Edit:

More details: I have three ec2 instances that are complete mirrors of each other. Each server has the running app, a single config, a mongos, and three mongod's. I load balance requests between the three of them. I have three replicasets, and each replicaset has a mongod from each server. So a 3x3 array. And each mongos is configured to know about all three config instances.

So. The main issue seems to be that the mongos instances aren't sharing routing information. Each mongos instance only seems to be aware of collections that were inserted while connected to that particular mongos instance. In other words, I'll connect to a mongos instance, insert a document, it ends up on, say, rs0, it replicates fine to the secondaries. Then - I'll connect to a different mongos instance, insert a document, it will instead end up on rs1, replicate fine - but I end up in a situation where the mongos instances are only aware of inserts that happened through themselves. So, in essence, each app server ends up with a seeing a different set of data - which isn't what I want.

Is this expected behavior? I'm thinking the dropDatabase issue is actually just a red herring?


texts: what are the prerequisite for installing oracle 11gr2

texts: I have windows 2008 R2 sever and I want install oracle 11gr2 on this server. So any one tell me what are the prerequisite tools for install oracle 11gr2 like as a jdk and ect...


texts: Out of operation records in local data manager (increase MaxNoOfLocalOperations) from NDBCLUSTER

texts:
MaxNoOfConcurrentOperations=150000
MaxNoOfConcurrentTransactions=150000
MaxNoOfLocalOperations=150000

I have above parameters in config.ini, when i try to delete rows in a table i get below error

mysql> delete from radacct where acctinputoctets=0 and acctoutputoctets=0;
ERROR 1297 (HY000): Got temporary error 1217 'Out of operation records in local data manager (increase MaxNoOfLocalOperations)' from NDBCLUSTER.

mysql> explain delete from radacct where acctinputoctets=0 and acctoutputoctets=0;
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | radacct | ALL  | NULL          | NULL | NULL    | NULL | 1209607 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+

How to determine the optimum value for maxnooflocaloperations ?


texts: PostGIS "could not open extension control file" when creating extension

texts:
I've followed the instructions here to install PostGIS
http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt but when I went to create the extension I got the following error. I'm using PostgreSQL 9.3.2 and Ubuntu 12.04. The extension doesn't appear in the dropdown list on pgadmin3 either.

# create extension postgis;
ERROR:  could not open extension control file "/opt/PostgreSQL/9.3/share/postgresql/extension/postgis.control": No such file or directory


texts: DB Design. Tracking history without losing referential integrity

texts:
To make it simple, let’s say we have these 2 tables:

CREATE TABLE `tbl_companies` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tbl_workers` (
  `id` int(11) NOT NULL,
  `name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

And another 2 tables to store the relationship between companies and workers

CREATE TABLE `tbl_companies_workers` (
  `company_id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  PRIMARY KEY (`company_id `, `worker_id `)
);

CREATE TABLE `tbl_companies_workers_history` (
  `company_id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date DEFAULT NULL,
  PRIMARY KEY (`company_id `, `worker_id `)
)

With this schema it’s easy to display something like: “John Smith worked at MyComp from 2010 to 2012”. But if MyComp is deleted from de DB, then that information is deleted too. I’d like to be able to display the same info even though the company is deleted.

Is it possible to achieve this without losing referential integrity? What would be the best approach? I've been looking for some info and I found this and this, but still it’s not clear to me.

Thanks in advance.


texts:
I do not really understand when it is necessary to set database offline .

I have googled and I did find any information or examples where and why should I set database offline.

I will be perfect if you will give me some example and cases when I need to make my database offline.


texts: Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

texts:
I installed Sql*PLUS on a cloud Oracle Linux Environment in order to access an remote database. I used the installation instructions provided as an answer here: https://stackoverflow.com/questions/23488394/how-to-install-sql-plus-client-in-linux

My connection string is correct, but I get the error

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

I tried resolving this issue by attempting to looking at my tnsnames.ora, but it did not exist and the path connected to my $ORACLE_HOME did not have the subfolders "/network/admin/". In order to resolve this, I created both folders and created a tnsnames.ora file in the new admin/ containing my connection string. Unfortunately, I still get the same error.

The path to my new tnsnames.ora file being:
/usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora

For reference my connection string is structured the following way:

(DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

And my command I am putting into my terminal is:
sqlplus "userName/myPassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))"

What should I do to resolve this error?


texts:
I have 3 years of SQL Server development work experience. Now I am moving to a new company (first jump). I heard in my new company they deal with too many tables.

What is the best way to understand table design in a database? How do you SQL gurus analyse the table design when you move to a new company?

Is ER diagram the best way? Please share your experience on this.


texts: How to get rid of a Bitmap Heap Scan if the table already has the proper indices

texts:
I am running PostgreSQL 9.6. These are the relevant definitions:

CREATE TABLE IF NOT EXISTS instagram.profiles_1000 (
    id                          SERIAL PRIMARY KEY,
    username                    VARCHAR(255) NOT NULL UNIQUE,
    followers                   BIGINT,
    tsv                         TSVECTOR
);

CREATE UNIQUE INDEX IF NOT EXISTS instagram_username_index
    ON instagram.profiles_1000(username);
CREATE INDEX IF NOT EXISTS instagram_followers_index
    ON instagram.profiles_1000(followers);
CREATE INDEX IF NOT EXISTS instagram_textsearch_index
    ON instagram.profiles_1000 USING GIN(tsv);

And the text vector is updated by a trigger:

CREATE FUNCTION instagram_documents_search_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
        setweight(to_tsvector(COALESCE(new.username, '')), 'D') || ' ' ||
        setweight(to_tsvector(COALESCE(new.full_name, '')), 'C') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_country, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_region, '')), 'B') || ' ' ||
        setweight(to_tsvector(COALESCE(new.biography, '')), 'A') || ' ' ||
        setweight(to_tsvector(COALESCE(new.location_city, '')), 'A');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER instagram_tsvectorupdate BEFORE INSERT OR UPDATE
    ON instagram.profiles_1000 FOR EACH ROW
    EXECUTE PROCEDURE instagram_documents_search_trigger();

This is the query:

select instagram.profiles_1000.*, categories, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
left outer join instagram.profile_categories_agg on instagram.profiles_1000.username = instagram.profile_categories_agg.username
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

This is the output of EXPLAIN (ANALYZE, BUFFERS):

https://explain.depesz.com/s/ceCd

The culprit is the Bitmap Heap Scan which makes up the bulk of the total execution time. Frankly I don't understand why it's needed, especially since the Bitmap Index Scan on instagram_textsearch_index already filters the rows according to the search term.

Can someone shed some light?

EDIT It was pointed out that I misread the explain output. Indeed, the left outer join was taking a lot of time. I tried to remove it as follows:

select instagram.profiles_1000.*, followers as rank
from instagram.profiles_1000
join plainto_tsquery('arts') as q on q @@ tsv
where followers is not null and followers > 0
order by (followers, -id) desc
limit 50;

But the query still takes 13 seconds! This is the EXPLAIN (ANALYZE, BUFFERS) output:

https://explain.depesz.com/s/awfH

Now the bottleneck seems to be the full-text search. Is it really that slow? The table has just 5 million rows, and the tsv (which has type TSVECTOR) is indexed by the following index:

CREATE INDEX IF NOT EXISTS instagram_textsearch_index_1000
    ON instagram.profiles_1000 USING GIN(tsv);

EDIT 2 I realized that I can write a leaner query if I only process the profiles that match the search (which are always 50 at most). Using this query:

select p.*, categories
from
    (select id
    from instagram.profiles_1000, plainto_tsquery('arts') as q
    where q @@ tsv and followers is not null and followers > 0
    order by (followers, -id) desc
    limit 50) as ids
inner join instagram.profiles_1000 as p on
    p.id = ids.id
left outer join instagram.profile_categories_agg as c on
    c.username = p.username;

I am able to obtain this result:
https://explain.depesz.com/s/OvG

Which puts the search at ~3 seconds. It would be nicer to reach 1 second at least though.


texts: Can I use a timeseries DBMS to store video?

texts:
From a research study involving participants with a camera, I have chunks of video recording. I have to split them as part of the preprocessing into short sequences of around 5 min long. Each file will be named after its time-stamp in the sequence. I can store them as binary blobs in a database.

In this study, I also have other sensors. It's clear that I should Use a time series DBMS to store these data (IoT sensing data). This will facilitate queries based on time. Do you see a problem with me storing the file paths and indexing them into a TimeseriesDB (like InfluxDB) based on their file blob's sequential time-stamps to facilitate time-driven queries?

The Q & A Should binary files be stored in the database? partially answers my question - from looking at that answer is sounds like storing video as a blob is a good solution. But my question is more about indexing video by the file's time-stamps.

The video is recorded in large (time) chunks at a time. as part of the cleaning/pre-processing, we split them into 5-minute long segments. Each segment is saved to a file that retains/maintains a date-time-stamp (a 50 min long video is split into 10). Putting it this way, it seems natural to store these file paths per their date-time-stamp. I just want to run my logic by others.


texts: how to create custom id in sql server with sequence

texts: How can I create a table with custom ID like BK-0001001 with a sequence. Sorry I'm a newbie with databases. All help will be appreciated thank you!


texts: Sql Server - Get database constraints of all tables ( default and check constraints)

texts:
I need to get constraints of all columns, tables in my database result like this :

DatabaseName | SchemaName | TableName | ColumnName | ColumnType | ConstraintName |

-

ConstraintType | Definition | CreateDate

I already used this answer but it shows just TableName, ColumnName, ConstraintName and definition.

Answer that I used:

SELECT
    TableName = t.Name,
    ColumnName = c.Name,
    dc.Name,
    dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
ORDER BY t.Name

Thank you for your help.


texts: Correct case for each reserved word in SQL Server?

texts:
Is the most correct spelling for int upper case INT or lower case int?

There are many other reserved words in SQL Server.

How to find out the best case for each word?

For example the picture below is a random piece of code, with many different words in it, how can I verify that those words have the, so to say, 'right' case spelling?

Would these things matter in case I have a CS (case sensitive) database?
Would any interfere in the performance?

Let's say select 1 from table1 where 1=1 and SELECT 1 FROM table1 WHERE 1=1?

How to Convert Data to Proper Case In SQL Server


texts: How do I change name format in TSQL Query Result?

texts:
I have a simple tsql select query that returns a user's name in this format:
e.g. Doe, John

How do I update the output so that it is in the format John Doe?

I have tried using a split string function but it returns multiple rows rather than 2 columns... https://stackoverflow.com/questions/10914576/tsql-split-string


texts: After upgrading from Postgres 9.2.4 to Postgres 10.5 query is 170x slower

texts:
We recently upgraded our Postgres database from 9.2 to 10.5.
We noticed a somewhat odd behavior with a query that under the same circumstances became 170x slower.
The query does a join between 2 tables on 'id' column.
SELECT A.id, A.session
FROM A
JOIN B on A.id = B.f_id
WHERE A.session = 0;

Size of A is ~43K rows.
Size of B is varying from 0 to several thousands.
The execution plan generated by PG 10.5:
Nested Loop (cost=0.54..12.83 rows=1 width=18) (actual time:883.156...883.157 rows=0 loops=1)
  Join Filter: (A.id = B.f_id)
  -> Index Scan using A_session_index on A (cost=0.29..4.55 rows=1 width=18) (actual time=0.032..2.251 rows=1600 loops=1)
    Index Cond: (session > 0)
  -> Index Only Scan using B_f_id_index on B (cost=0.25..8.27 rows=1 width=16) (actual time:0.549..0.549 width=0 loops=1600)
    Heap Fetches: 3074334
Planning time: 0.537 ms
Execution time: 883.189 ms

While the execution plan generated on 9.2 looks like:
Nested Loop (cost=4.28..18.30 rows=1 width=18) (actual time:5.484...5.484 rows=0 loops=1)
  -> Index Scan using A_session_index on A (cost=0.00..4.48 rows=1 width=18) (actual time=0.013..0.918 rows=1600 loops=1)
       Index Cond: (session > 0)
  -> Bitmap Heap Scan on B (cost=4.28..13.78 rows=3 width=16) (actual time:0.002..0.002 width=0 loops=1600)
       Recheck Cond: (f_id = A.id)
       -> Bitmap Index Scan on f_id_index (cost=0.00..4.27 rows=3 width=0) (actual time=0.001..0.001 rows=1 loops=1600)
            Index Cond: (f_id = A.id)
Total runtime: 5.516 ms

We have application flows where this query could execute thousands of times.
Resulting a severe degradation in our application.
At first glance it looks like a simple "Analyze" operation would solve the bad performance by changing plan.
However, this code runs in a context where we can't just execute proactive "Analyze" each time before it happens.
Also we noticed that trying to "time" the Analyze operations is becoming more and more difficult and we have many false negatives.
We didn't change the default auto vacuum/analyze settings.
Although we considered it seriously, we didn't change the auto analyze config.
In order to suit it for this behavior we have to set it to run every ~1000 modification to the table.
Since size of table A could grow to 10 mil, having auto analyze run this much is a bit disturbing.
We didn't change any of the other configurations in postgresql.conf we had in PG 9.2.
I would like to know if this is a known bug and perhaps was fixed in later version.
Or if there is a configuration to tune in order for the planner to behave as it used to be.


texts: How to determine cost/benefit of adding an index?

texts:
According to Craig Ringer:

  While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT, UPDATE or DELETE. If the index is rarely used it may not be worth having.

How do you determine if the benefit of adding an index surpasses its cost?

Do you profile unit tests before/after adding an index and check for an overall performance gain? Or is there a better way?


texts: How to design a database for query builder project?

texts:
I have this project in which user can create queries using web based UI in which there will be list of columns and applicable operators. Later users can edit these queries, too. So I need to store this in database.

I can store query in table as simple string but then editing will be not possible so I need to store it some other way. So I somehow managed to design it following way.

So let's say I have to store this query:

C1 > 8 AND (C2 <= 7 OR (C4 LIKE '%all%' AND (C1 > 15 OR C2 <= 3)))

where: C denotes some column

If I have to store it in DB as shown in image,

I would group each condition and store it in sub_operand table
then there will be recursive mapping entry in op_master table for each entry in sub_operand table
finally there will be master entry in op_master

But it seems too much complicated to handle insert and update. Can someone help me with this? I am very much stuck here.

UPDATE: I think I am missing something here in schema. It won't work as I have thought. I will update question as soon as I can correct it.


texts: Does update lock the row or the entire table?

texts:
Basic question: When I update a row in a database table, does it lock the current row or the entire table?
I am using MySQL. When I update column which is not unique.
Is the locking different from one database engine to another? I have read this article but I am still confused.


texts: How is this SQL Server PK violation possible?

texts:
I'm getting an error that I thought was impossible. I have a SQL Server (ver 11.0.6594) table that looks like this:

CREATE TABLE [IDMaster](
  [ID] [int] NOT NULL,
 CONSTRAINT [PK_IDMaster] PRIMARY KEY CLUSTERED
([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]

It's basically supposed to do what the IDENTITY does now (it's been around a while) – it is used to get new unique integer values, that are used in a few other tables.

Now I have a SQL statement, which selects the MAX value, adds to it, and INSERTs that value into the same table – all inside one statement. So in theory, it's not possible to get a PK violation on this statement (although I think could possibly get a deadlock). But, somehow, it is getting one.

  Violation of PRIMARY KEY constraint 'PK_IDMaster'. Cannot insert duplicate key in object 'IDMaster'. The duplicate key value is (25309587).

I don't know how this is possible, but apparently it is; because it just happened.

Imagine any crazy, odd mixture of other ways to try generating a new ID value from this same table, and they probably aren't far off from what exists in this system. But I can't think of any way that they could cause that particular error. It was my understanding that the transaction locking used by SQL Server enforces that, within one SQL statement that doesn't have (NOLOCK) on it or anything, there can't be any interference with the rows that it is referencing.

The only other factor, which I don't think could be related but who knows, is that this SQL is wrapped in a TRY, with some options set. Here's the SQL that got that error:

SET XACT_ABORT ON;
SET IMPLICIT_TRANSACTIONS ON;
BEGIN TRY

INSERT INTO IDMaster (ID)
 SELECT COALESCE(
  -- make sure the new value is odd
  CASE WHEN MAX(COALESCE(ID,0)) % 2 = 0
   THEN MAX(COALESCE(ID,0)) + 1
   ELSE MAX(COALESCE(ID,0)) + 2
   END,1)
 FROM IDMaster;

END TRY
BEGIN CATCH
  WHILE (@@TRANCOUNT > 0) ROLLBACK;
  THROW;
END CATCH
WHILE (@@TRANCOUNT > 0) COMMIT;

Please explain:

How is it possible to get that error?
If possible, how can I prevent it from ever happening (without altering the table)?


texts:
I need to store search keywords in a database to analyse user needs.
How efficient should my schema be?

I'm thinking like this:

CREATE TABLE SearchKeyword (
 ID         INTEGER        PRIMARY KEY AUTOINCREMENT NOT NULL,
 UserID     INTEGER,
 SearchKey  VARCHAR( 255 ) NOT NULL,
 Date       TIMESTAMP      NOT NULL
);

But with this, the table will grow. Any suggestions for improvements?


texts: Storing external databases data into one

texts:
I need to store different datasets coming from a provider where each dataset has it's own release path.
These datasets can be combined together to get the full picture of the data available from the provider.
I know from the doc of the publisher the combination of versions that are allowed.
My pain point is that I need to keep track of the version for each dataset.
Example of data:
"Main" dataset from publisher "ABC" has version "1.0".
"Ext" dataset from publisher "ABC" has version "release_3"
My schema is as follow:
Provider
ProviderId
Dataset
DatasetId
ProviderId
Version
VersionId
DatasetId
Main
MainId
VersionId
Ext
ExtId
MainId
VersionId
Based on that, is it a problem that FK "VersionId" in tables "Main" and "Ext" references a different record in table "Version" ?
I'm afraid that any user querying the DB will not expect to have diverging Version (as the FK name is identical in both tables).
Unfortunately that's the reality of the data provided by the publisher.
Is there a better design to accomplish the same result ?
NB: It is possible that in the future, datasets from different providers need to be combined.
Thanks


texts: how to create ambari database backup without enter password

texts:
We configure the file .pgpass as follows in order to create database backups without entering a password:

-bash-4.2$ more .pgpass
localhost:5432:postgres:ambari:bigdata
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ whoami
postgres
-bash-4.2$ more .pgpass
localhost:5432:postgres:ambari:bigdata
-bash-4.2$

But after that still pg_dump insists to enter the password - why?

Where am I wrong?

pg_dump -U  ambari  -f ambari.sql
Password:


texts: SQL Server 2000 to SQL Server 2008 linked server issue

texts:
Here's my scenario: I've got a linked server going from a SQL Server 2000 machine to a SQL Server 2008 machine. To aid in moving databases with a minimum of config changes, most of our connections point to [dbname.database.organization.com]. That is also the name of the linked server on the 2000 box.

Most of the tables work fine. There is one exception: the biggest table of the bunch (450285 rows) gives this error (the values in the curly brackets have been changed from our real values):

  An error occurred while executing batch.
  Error message is: Processing of results from SQL Server failed because
  of an invalid multipart name
  "{DBNAME}.DATABASE.{ORGANIZATION}.COM.{DBNAME}.dbo.{TableName}", the
  current limit of "4" is insufficient.

I would expect that behavior if didn't have square brackets around the linked server name (which the error message is just omitting). The rest of the tables I need work just fine the exact same way.

I'm wondering if there is a maximum size setting I'm missing, etc.

This application currently depends on a job that copies the entire table from the SQL Server 2008 machine to the SQL Server 2000 machine, nightly. I would really like to do away with that.

Thanks!


texts: string search in table with partial text and without all field names

texts:
I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. And string could be like just half . For examaple search string can ve IT   and I should return record of ITALY .
my table is something like

currently using this query but it is not working for half text. and don't want to user fields name in   IN like (1st field ,2nd filed, 3rd filed ) is there something * like generic there ?
currently using this
select *
from the_table
where 'Italy' IN (name, native, place);


texts: Is this a correct understanding of transactions?

texts:
I have just noticed that one of my TRUNCATE queries takes 16 seconds to complete. I always assumed that TRUNCATE queries were basically-instant.
I currently do this:
TRUNCATE TABLE bigtable RESTART IDENTITY;
INSERT INTO bigtable (col) VALUES ($1); -- This one is run countless times.

Question 1: Since it takes 16 seconds for the TRUNCATE to do its thing, does that mean that for 16 seconds, my applications might be querying the bigtable and get nothing returned?
Question 2: If I do the following, will my applications continue to get the old values until the entire new dataset has been loaded in? Or will something else happen?
BEGIN TRANSACTION;
TRUNCATE TABLE bigtable RESTART IDENTITY;
INSERT INTO bigtable (col) VALUES ($1); -- This one is run countless times.
COMMIT TRANSACTION;

As I understand it, the BEGIN ... COMMIT "block" will basically not "go live" until the COMMIT, at which point both the TRUNCATE and all the INSERTS have taken place, so at that moment, the applications will get the new data, but until then, they will get the old data rather than NO or PARTIAL data?
Correct?


texts: MySQL table with 100,000 records queried often

texts:
I have a single database of about 100 tables to store various kinds of information.

The most important table is our order table which is used to store customers orders and is over 100000 records as of now and growing.

This table is the most queried table in our database, for various parts of information needed from real time order dashboards, statistics, analytics etc.

I monitor the database on a regular basis and have slow queries enabled on the database to track issues.

I use scripts like mysqltuner to spit out query on a daily basis.

I also use mysqlsla to gather info about the top 10 slowest queries in our database.

sample stat
Count         : 11.48k  (30.66%)
Time          : 19.623758 s total, 1.709 ms avg, 239 µs to 2.475017 s max  (18.64%)
  95% of Time : 5.246833 s total, 481 µs avg, 239 µs to 1.095 ms max
Lock Time (s) : 14.460071 s total, 1.259 ms avg, 53 µs to 2.462555 s max  (41.38%)
  95% of Lock : 806.43 ms total, 74 µs avg, 53 µs to 137 µs max
Rows sent     : 1 avg, 0 to 9 max  (0.99%)
Rows examined : 6 avg, 1 to 28 max  (0.15%)

Most of the slowest queried involve the order table mentioned above. I use MyISAM as my storage engine so possible issues could be:

Table locking
Indexing issues

How could I improve these stats, I have indexing in place for these tables and have kept tweaking them to improve read queries.

Table schema

`orderid` int(11) NOT NULL AUTO_INCREMENT,
`cityid` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `model_type` tinyint(1) unsigned DEFAULT '1',
`userid` int(11) DEFAULT NULL,
`usertype` char(1) DEFAULT NULL,
`time` time DEFAULT NULL,
`ordercode` char(8) DEFAULT NULL,
`restid` smallint(3) unsigned NOT NULL,
`areaid` smallint(3) unsigned DEFAULT NULL,
`restname` varchar(50) DEFAULT NULL,
`date` date NOT NULL,
`del_time` time NOT NULL,
`status` tinyint(3) unsigned NOT NULL,
`amount` float NOT NULL,
`deliverycharge` smallint(4) unsigned DEFAULT '0',
`tax` float NOT NULL,
`total` float NOT NULL,
`extras` varchar(255) DEFAULT NULL,
`requests` varchar(255) DEFAULT NULL,
`discount` float DEFAULT NULL,
`rdiscount` float DEFAULT NULL,
`reason` varchar(255) DEFAULT NULL,
`rest_order` tinyint(1) unsigned DEFAULT NULL,
`admin_user` varchar(25) DEFAULT NULL,
`mode` char(1) NOT NULL,
`priority_order` tinyint(1) unsigned DEFAULT '0',
`payment_mode` tinyint(1) unsigned DEFAULT '0',
`km` tinyint(3) unsigned DEFAULT NULL,
`order_type` tinyint(1) NOT NULL DEFAULT '1',
`coupon_discount` smallint(3) DEFAULT '0',
`pickup_time` time NOT NULL,
PRIMARY KEY (`orderid`),
KEY `cityid` (`cityid`),
KEY `date_3` (`date`,`status`,`mode`),
KEY `orderid` (`orderid`),
KEY `time` (`time`),
KEY `userid` (`userid`,`usertype`),
KEY `restid` (`restid`,`date`,`status`)

slow log query

SELECT `a`.`orderid`, `a`.`date`, `a`.`status`, `a`.`restname`, `a`.`admin_user`, `a`.`model_type`, `b`.`name` as cityname
FROM `tk_order_queue` AS a
INNER JOIN `tk_cities` AS b ON `a`.`cityid` = `b`.`id`
WHERE `a`.`date` =  '2012-06-30'
AND `a`.`status` =  0
AND `a`.`mode` =  1
ORDER BY `a`.`orderid` desc;


texts: Is logon trigger supported in sql server 2014 express edition?

texts:
I'm using sql server express for developing a multiuser app. I have the following request to implement:
Access to sql server  shall be allowed only for the c# app and a specific whitelist of logins including SA and certain system admin.I know that logon triggers are tricky to use if they are not well designed and I can encounter issues if I don't have DAC enabled. Also the app name can be easily spoof.
So I have a  double question: Is it even possible to implement logon trigger using express 2014? Is there any other option to Block the ssms, sqlcmd access for certain logins without login trigger?


texts: PostgreSQL query slow when using tsvector and ORDER BY despite having index

texts:
Running on a large database (around 1.5G) The following query takes about 70 seconds to run,

SELECT x
FROM DBText x
WHERE x.status = 2
  AND x.publishedTime < 4574590406397
  AND x.service = 'twitter'
  AND (NOT EXISTS
         (SELECT 1
          FROM DBTag t,
               DBEquiv oeqv
          WHERE (x.oxid=t.txid
                 OR (x.oxid=oeqv.id1
                     AND t.txid=oeqv.id2))
        AND ((t.tag='ignore-person'
              AND t.grp='socrash')
             OR (t.tag='lost'
                 AND t.grp='!cross-project'))))
  AND (NOT EXISTS
     (SELECT 1
      FROM DBTag t
      WHERE t.txid=x.xid
        AND ((t.tag='ignore'
              AND t.grp='socrash'))))
  AND (x.group_id = 347804
       OR x.group_id IS NULL)
  AND (EXISTS
         (SELECT 1
          FROM DBTag wst
          WHERE wst.txid=x.xid
            AND wst.grp='socrash'))
  AND length(x.contents)<100000
  AND to_tsvector('english',coalesce(x.name,'')||' '||coalesce(x.contents,'')) @@ (to_tsquery ('english','apple*') && to_tsquery('english','pie*'))
ORDER BY x.publishedTime DESC LIMIT 10;

However, if the to_tsvector area is removed, or the ORDER BY statement is removed the search becomes a almost instant. I have tried this using LIKE rather than to_tsvector to search the contents and once again, almost instant.

Is there any way to optimize this search so that it still uses ts_vector text searching, but works at a similar speed to the LIKE search?

Thanks


texts: Ingredients for SQL Server Version decision

texts:
We are going the create our Sql-Servers new on new hardware / VM's and think about which Sql-Server Version to go with. We are actually using Sql Server 2008 R2. We have already done a project for testing everything for any compatibility issues between 2008 and 2014 - and have fixed all issues in application code as well as in the db schemas to be ready for 2014.

Now the question for simply dismissing 2014 and directly go to 2016 came up. We are not sure.

I have ran SQL 2016 Upgrade Advisor - it said "ready for upgrade (2016)" and raised around 500 warnings for deprecated ntext, text and image columns.

Are there any special additional downsides of going to 2016 instead of 2014?
Are there new compatibility issues? AFAIKS there have been no new deprecations in 2016 that are not yet in place of 2014...
What do we have to consider?


texts: SQL server is getting stuck all of a sudden

texts:
I manage a virtual SQL server that holds several databases for some applications that run in a front-end server. The issue I'm going through is that, at a specific moment during the day, the server gets stuck and the instance becomes unreachable. I can RDP the server but I cannot connect neither from SSMS nor from the application. I have searched into the OS logs and into the SQL server error logs but I find nothing relevant that could give a hint about what is causing the SQL server to become unresponsive. As a consequence, I have to do a stop-and-start process with the SQL server service and after that the instance will become available once again.

Anyone who might have experienced this in the past? Thanks.


texts:
I'm trying to change the value in the XML metadata using SQL Server.
The XML file looks like this:

<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MyOldValue">
<SomeTag></SomeTag>

What I want to do is to change xmlns to a new value. I tried casting the xml as nvarchar(max), using replace and then casting back to xml but it didn't work (string truncation).

I tried using XQuery but I kept failing. Can you recommend a solution?


texts:
I am trying to open a symmetric key I created in a database, but it doesn't seem to be working. I am executing the query as dbo and it even says that it is completed successfully. However, the value for the key being open is still set to false and I cannot move forward. I am not sure how to proceed.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Subject';
go

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Certificate1;
go

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
go

I can create the master key, certificate, and symmetric key just fine. That OPEN statement just doesn't work. It doesn't return any errors like I said, it just completes successfully without actually doing anything. This is for a class and I am totally lost. I feel like I am doing it correctly and am not sure what to change. I have tried recreating everything and even deleted the database itself.

Any advice would be welcome.


texts:
We have Scenario where we need to modify input string while fetching data from SQL Server.
"TEST RATE CODE DESC TEST RATE CODE DESCTST TEST" and C is 47th character in an "DESCTST" then push delimiter at the beginning of a word. like "\DESCTST"
For Example:

Current => TEST RATE CODE DESC TEST RATE CODE DESCTST TEST\
Required =>
TEST RATE CODE DESC TEST RATE CODE DESCTST \TEST

We have created an SQL function for achieving this and everything working fine except the backward operation as mentioned in the example.
Here is my SQL function:
ALTER  FUNCTION [dbo].[DescFormatter_New]
(
    @InputString nvarchar(max),
    @MaxLength int=0,
    @CharPerLine int=0,
    @NoOfLines int=0
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @ResultText nvarchar(max)
    DECLARE @I INT
    DECLARE @COUNT INT

    IF(LEN(@InputString)<@CharPerLine)
        BEGIN
            SET @ResultText=@InputString
        END
    ELSE
        BEGIN
            set @InputString=LEFT(@InputString,@MaxLength)
            SET @I = LEN(@InputString)/@CharPerLine

            if((@I*@CharPerLine)<LEN(@InputString))
                BEGIN
                    SET @I=@I+1
                END

            SET @COUNT =1
            SET @ResultText= LEFT((@InputString),@CharPerLine)
            if(@I>@NoOfLines)
                Begin
                    set @I=@NoOfLines
                End
            WHILE(@I>1 AND @I<=@NoOfLines)
                BEGIN
                    SET @ResultText=@ResultText+'\'+SUBSTRING(@InputString,(@COUNT*@CharPerLine)+1,@CharPerLine)
                    SET @COUNT=@COUNT+1
                    SET @I=@I-1
                END
        END
    RETURN @ResultText
END

We call this function like here:
[dbo].[DescFormatter_New](LText,94,47,2) AS [Short Description]

Note that we will need multiple line delimiters for the string which contains more than 100 or 200 characters.
here are the few examples :

1)
Input String = Deluxe and spacious 1 King Bed NS in room Whirlpool
Computer Wet Bar Refrigerator Mini Bar Desk Safe WIFI Warm Decor
Expected Output = Deluxe and spacious 1 King Bed NS in room \Whirlpool
Computer Wet Bar Refrigerator Mini \Bar Desk Safe WIFI Warm Decor
2)
Input String = Spacious 1 Queen Bed Non Smoking featuring flat screen TV with cable wifi coffeemaker mini frig microwave hairdryer iron with board test test
Expected Output = Spacious 1 Queen Bed Non Smoking featuring flat\ screen TV with cable wifi coffeemaker mini \frig microwave hairdryer iron with board test \test

In the above expected output string the first deliminator come after 47 charecters i.e between Whirl\pool (diaplayed as \Whirlpool) and then the loop will start from count 1 again after Whirl in the Whirlpool, So the second deliminator should come like here Des\k (Displayed as \Desk).


texts: Does updating a row with the same value actually update the row?

texts:
I have a performance-related question. Let's say I have a user with first name Michael. Take the following query:

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123

Will the query actually execute the update, even though it is being updated to the same value? If so, how do I prevent it from happening?


texts: Can we configure logins to only connect to read-only replicas in Azure SQL Database automatically?

texts:
For Read-Only routing, rather than explicitly using "ApplicationIntent=ReadOnly" into our application connection string or for individual users to specify it in additional properties, can this be configured while creating a login?
For Example: Any user trying to connect to the instance should be routed to read-only replica, even if they don not provide "ApplicationIntent=ReadOnly" in their connection string.
Is this possible?


texts: MySQL Export not possible to import due to ERROR 1064

texts:
we are currently in the progress of moving from MySQL to PG. To do so we are in the progress of setting up a test environment for our database operations.

The process is rather simple:
We have exported the structure of our latest stable production environment and want to import it in the dev environment. Unfortunately, the structure export fails to be imported:

ERROR 1064 (42000) at line 78: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL,
  `traceback` longtext,
  `hidden` tinyint(1) NOT NULL,
  `meta` l' at line 6

Which corresponds to the following lines:

DROP TABLE IF EXISTS `celery_taskmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `celery_taskmeta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` varchar(255) NOT NULL,
  `status` varchar(50) NOT NULL,
  `result` longtext,
  `date_done` datetime(6) NOT NULL,
  `traceback` longtext,
  `hidden` tinyint(1) NOT NULL,
  `meta` longtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_id` (`task_id`),
  KEY `celery_taskmeta_662f707d` (`hidden`)
) ENGINE=MyISAM AUTO_INCREMENT=34383 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

How is it possible for a MySQL export not to be able to be imported? It seems that DATETIME(6) throws the error ? However, http://dev.mysql.com/doc/refman/5.7/en/datetime.html does not give any further clues about the possibilities of the DATETIME field.

Current version:

mysql --version
mysql  Ver 14.14 Distrib 5.5.42, for debian-linux-gnu (x86_64) using readline 6.3

Production Version:

MySQL 5.6.23 on RDS


texts:
I have this table structure. id is primary key:

 id  post_id  email  telegram twitter  address  refer_link  referrer

Here is show create table:

CREATE TABLE `wpkx_airdrop_claim` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) NOT NULL,
 `email` varchar(100) NOT NULL,
 `telegram` varchar(60) NOT NULL,
 `twitter` varchar(60) NOT NULL,
 `facebook` varchar(250) NOT NULL,
 `discord` varchar(60) NOT NULL,
 `address` varchar(120) NOT NULL,
 `refer_link` varchar(8) NOT NULL,
 `referrer` varchar(8) NOT NULL,
 `ip` varchar(15) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `post_id_index` (`post_id`)
)

Table is now relatively big. When I run this query (it has around 9k rows) it takes more than 30 seconds to execute:

SELECT o.email
    ,telegram
    ,twitter
    ,address
    ,IFNULL((
            SELECT email
            FROM wpkx_airdrop_claim
            WHERE o.referrer = refer_link
            ), '') AS refer_email
    ,(
        SELECT count(referrer)
        FROM wpkx_airdrop_claim
        WHERE referrer = o.refer_link
        ) AS refer_count
FROM wpkx_airdrop_claim AS o
WHERE post_id = 8037
ORDER BY refer_count DESC

This query counts number of same values in referrer column, and group it with the value of refer_link column. It gets how many referrals user have.
It is explained in this post:
https://stackoverflow.com/questions/52538612/mysql-create-view-from-columns-from-same-table

Any idea how to optimize this query to execute it faster?

Thank you.


texts: Is the runtime of checking for duplicate primary keys in SQL Server O(n)?

texts:
I was curious and I couldn't find too much info on this. Is it also O(n) for checking for duplicates among primary keys?

Is it the same for most other SQL databases?

Is there a place that lists this info?


texts: SSMS execution time queries besides SET STATISTICS

texts:
When I run my query, it prints multiple execution times instead of just one. I only want one so what should I use instead of SET STATISTICS?

SET STATISTICS TIME ON
DECLARE @firstNum INT, @secondNum INT, @thirdNum INT, @evenSum INT
SET @firstNum = 1
SET @secondNum = 2
set @thirdNum = 2
SET @evenSum = 2
WHILE (@thirdNum <= 4000000)
BEGIN
    SET @thirdNum = @firstNum + @secondNum
    SET @firstNum = @secondNum
    SET @secondNum = @thirdNum
    IF (@thirdNum % 2) = 0
    SET @evenSum += @thirdNum
END
PRINT 'Answer = ' + CONVERT(VARCHAR, @evenSum)
SET STATISTICS TIME OFF

It prints this like a hundred times:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


texts:
We are having trouble with simple updates on a single table taking a long time. The table contains ~5 Million rows.

Here is the table:

CREATE TABLE Documents (
    GeneratedKey varchar(32) NOT NULL,
    SourceId int(11) NOT NULL,
    Uri longtext,
    ModifiedDateUtc datetime NOT NULL,
    OperationId varchar(36) DEFAULT NULL,
    RowModifiedDateUtc datetime NOT NULL,
    ParentKey varchar(32) NOT NULL,
    PRIMARY KEY (SourceId, GeneratedKey),
    KEY IX_RowModifiedDateUtc (RowModifiedDateUtc),
    KEY IX_ParentKey (ParentKey),
    KEY IX_OperationId (OperationId(36))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is the update query:

UPDATE Documents
SET OperationId = 'xxxx'
WHERE SourceId = 12345
AND ParentKey = '0965b3983ceb0e8e41ab47b53e37d0f3';

This query updates ~80k rows and takes around 60 seconds to complete, and the more rows updated the longer it takes which results in timeouts. The index IX_ParentKey cardinality is ~830k. Note that a select with the same WHERE clause returns very quickly (< 1s).

Query profiling:

starting                0.000072
checking permissions    0.000006
Opening tables          0.000019
init                    0.000023
System lock             0.000266
updating                50.415424
end                     0.000039
query end               0.024398
closing tables          0.000037
freeing items           0.000051
cleaning up             0.000022

When switching to MyISAM, the query took only 2 seconds. What could be slowing down the updates, I assume the index needs to be rebuilt? Is there any way we can optimize this?


texts: How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL

texts:
In my application I would need to calculate total ON hrs and OFF hrs in a day of motors in one tank. Every time he/she turn ON/OFF motors, data will go and store in motor_status_log table of my database.

While I searched for same thing in google I got following URL: calculating total login-logout time of a particular user in mysql

My rearranged query is:

"SELECT ilv.motor_id,SUM(UNIX_TIMESTAMP(offtime)-UNIX_TIMESTAMP(ontime)) FROM (SELECT a.motor_id, a.timestamp AS offtime,(SELECT MAX(b.timestamp) FROM motor_status_log b WHERE b.motor_id=a.motor_id AND b.timestamp<a.timestamp AND b.runningstatus='on') as ontime FROM motor_status_log a WHERE a.runningstatus='off' AND a.timestamp BETWEEN '2013-06-26 00:00:00' AND '2013-06-26 23:59:59' AND motor_id='$motor_id') ilv GROUP BY ilv.motor_id"

and by using the given SQL Query in the above link it is not working.

And table in my database is like:

+--------+-------+---------+--------------------+-----------------+
|user_id |Tank_id|motor_id |  timestamp         | runningstatus   |
+--------+-------+---------+--------------------+-----------------+
|    1   |    1  |    1    |2013-06-26 09:53:29 | off             |
|    1   |    1  |    1    |2013-06-26 10:45:42 | on              |
|    1   |    1  |    1    |2013-06-26 10:55:29 | off             |
|    1   |    1  |    1    |2013-06-26 17:55:39 | on              |
|    1   |    2  |    2    |2013-06-26 17:56:39 | off             |
|    1   |    2  |    2    |2013-06-26 08:53:29 | off             |
|    1   |    2  |    2    |2013-06-26 10:45:42 | on              |
|    1   |    2  |    2    |2013-06-26 12:55:29 | off             |
|    1   |    2  |    2    |2013-06-26 14:55:39 | on              |
|    1   |    3  |    3    |2013-06-26 17:56:39 | off             |
+----------------+---------+--------------------+-----------------+

Please help me. Thanks in advance


texts: Optimize join - group by Oracle SQL filter

texts:
I have a few tables:

CHEST:
    id  fk_id_rap   fk_id_ses_rap
    1   1           1
    2   2           1
    3   3           1

SES_RAP:
id  an
1   2015
2   2016

RAP:
id  name
1   rap1
2   rap2
3   rap3

anexa10
id  fk_id_cht
10  1
11  1
12  2
13  2

anexa10_rows
id  cod         cant  tip   fk_id_anexa10
1   01 01 01    45    r2    10
2   01 01 01    34    r3    11
3   00 00 00    10    r5    12
4   01 03 04    70    r6    13

If i try something like this:

SELECT RAP.name, ses_rap.an, A10.cod,
            (SELECT SUM (COALESCE (A10.CANT, 0))
               FROM ANEXA10_ROWS A10B
              WHERE A10B.FK_ID_ANEXA10 = A10.ID AND
                    A10B.COD = A10T3.COD)
               AS CANT,
        MIN(A10R.TIP),
       FROM CHEST cht
            INNER JOIN rap rap
               ON cht.fk_id_rap = rap.id
            INNER JOIN SES_RAP ses_rap
               ON ses_rap.id = CHT.FK_ID_SES_RAP
            FULL OUTER JOIN ANEXA10 A10
               ON A10R.FK_ID_CHT = CHT.ID
            FULL OUTER JOIN ANEXA10_ROWS A10R
               ON A10R.FK_ID_ANEXA10 = A10.ID
      WHERE SES_RAP.AN = '2015' AND A10.COD IS NOT NULL
   GROUP BY RAP.NAME,
            ses_rap.an,
            CHT.ID,
            A10R.COD,
            A10R.CANT,
            A10R.TIP
   ORDER BY RAP.NAME

The result is:

NAME    AN      COD        CANT TIP
rap1    2015    01 01 01   45   r2
rap1    2015    01 01 01   34   r3
rap2    2015    00 00 00   10   r5
rap2    2015    01 03 04   70   r6

How can I get the result?

NAME    AN      COD        CANT TIP
rap1    2015    01 01 01    79  r2r3
rap2    2015    00 00 00    10  r5
rap2    2015    01 03 04    70  r6

----------------EDIT1:
if I remove A10R.CANT from GROUP BY it gives me that it's not a GROUP BY expression on

(SELECT SUM (COALESCE (A10.CANT, 0))
                   FROM ANEXA10_ROWS A10B
                  WHERE A10B.FK_ID_ANEXA10 = A10.ID AND
                        A10B.COD = A10T3.COD)
                   AS CANT,


texts:
I need to Import a large data-dump.

The structure is already created. But the data-dump is 16GB large.

If I try it simple with

psql -d "DATABASE" < "datadump.sql"

I get a out of memory Message on Ubuntu DB.

i have no idead how to import.

The DB-Server has 16GB Ram, 50GB Disk-Space free (its a developer vm)

Does anyone has a idea, how to import this file?

UPDATE

My postgresql.conf

# - Memory -

shared_buffers = 4GB            # min 128kB
                    # (change requires restart)
work_mem = 1048kB           # min 64kB
maintenance_work_mem = 2GB      # min 1MB

checkpoint_segments = 128       # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min      # range 30s-1h
checkpoint_completion_target = 0.9  # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s       # 0 disables

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Cost Constants -
effective_cache_size = 12GB

wal_buffers = 16MB

The inserts are single-row and all for one table.
The Table has 71 columns.

I dont know, whats in line 1447540. No Texteditor can open this file -.-
(But the content of the file is correct. After spliting the file into much smaller files, the imports run successful.)

The first screenshot shows the free -ht output before importing. The second screenshot was taken 2 seconds before the out of memory messeage comes up.


texts: Database Performance issue after moving from windows to Synology

texts:
I have moved the test database server from Windows to Synology. The size of the database is 5.5 GB and the innodb_buffer_pool_size = 6G. This Synology server is a dedicated database server.
After this change, end users are facing a performance issue in a web application.


texts: SQL Server 2012 Transparent Data Encryption and indexes

texts:
Does SQL Server TDE allow a query like this:

     select * from Patient where lastname = 'Smith'

to occur without a full-table scan if the lastname column is indexed?


texts: Oracle: changing a global temporary table to a normal table and vice versa

texts:
Our code make use of a global temporary table which the option 'ON COMMIT DELETE ROWS'.  Works well and does the job as expected.  But when running my automated tests, i would like the rows to persist, during the session and why not, after the session ended.

Is it possible to alter the table to become a normal table before my test starts and revert it back to a temporary table after the test?

My test suite could drop and recreate the table, but that requires my test suite to know the DDL of the table.  I want to avoid this.  My testing code only needs to fetch one column.  If with time, columns are added, options are changed, my testing code will endure the changes if it can only alter the nature of the table.


texts: cannot connect to sql server in single user mode

texts:
I have started my SQL Server 2008 sqlcst02\inst01 in Single User Mode by appending the ;-m to the end of the startup parameters for this instance in SQL Server Configuration Manager. I have stopped SQL Server Agent for the inst01 instance. My account is a member of the Domain Admin group and I have attempted to login using sqlcmd -S sqlcst02\inst01 and keep getting an error that the service is in Single User Mode and only one administrator can be logged in. I tried adding ;-m"sqlcmd" to the startup parameters to limit the connection to just sqlcmd but I get the same results. I have tried this logged in as my own account and logged in as the local administrator account. I have started the command prompt as myself and as administrator but I get the same results. I have tried connecting using ADMIN:sqlcst02\inst01 in SSMS. No luck.

I'm attempting to reset the sa password as none of this information was left for me by the previous admin. This is maddening. I've done this before on other machines with no issues.


texts: Temporal tables extension error with array columns

texts:
I'm using the temporal tables Postgres extension (http://pgxn.org/dist/temporal_tables/) on Postgres 9.5.4 on Windows. This works well for most cases, but I'm running into a weird issue with array columns.

I've created some minimal steps to reproduce this, the setup of the table and the temporal tables extension is the following:

CREATE EXTENSION IF NOT EXISTS temporal_tables;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_history;

CREATE TABLE test
(
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

CREATE TABLE test_history (LIKE test);
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'test_history', true);

And in separate transactions the following two commands:

INSERT INTO test(a) VALUES (1);

UPDATE test SET a = 5 WHERE id = 1;

I get the following error:

  ERROR:  column "directories" of relation "test" is of type text[] but column "directories" of history relation "test_history" is of type text[]

The error is nonsensical to me, both columns have the same type and the error even states this. This only happens when a text[] column is present in the table. It is not necessary to write to that column, it just has to be present. If I don't create the array column, there is no error.

The extension does not mention anything about incompatibility with arrays, and I'd expect that kind of restriction to be large enough to be mentioned. I'm wondering if there is anything wrong with the way I set up the tables, or if I'm missing anything else here. Is there any underlying reason why two text[] array columns would be of different type?

Any idea what actually causes this error and how I could get rid of it?


texts: Finding all functions/stored procedures that reference a certain procedure/function?

texts:
I have been given the task to move reports from SSRS to our internal ASP.Net application and this has involved me re-writing a few functions and a stored procedure to both improve performance, make them output in forms better consumed by the application and make any requested additions to the reports.

What I also need to do is remove the old functions and procedures from the system as I go, before I do this though I want to make sure it will not break anything else. Is there a way I can go though all the precedures and functions to see if any of them contain references to the ones I will be removing?

There are thousands of these to go though and I would rather not have to open each one at a time in SSMS to look. So if there is some table I can query or even if there is a .sql file that SQL server is hiding that I could dig though, that would be great.


texts: Query without having to specify table's schema

texts:
I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.

In the table properties it lists 'erpadmin' as the db schema. When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing.

Current result:

select *
from erpadmin.tablename

Desired result:

select *
from  tablename