texts: Can you update a database with transaction logs only, without having to do a full restore?

texts:
Initial note I am not a DBA, I am learning on the job.
Where I work we have an antiquated third-party system that sends us a .bak file once a week and log files every hour. To get the previous day's data in the database we have a process that runs a python script which builds the SQL to to do the full restore. The restore takes about 2 hours every night. We have a request from our end users to get the data as "live" as possible, which is theoretically possible as we receive the log file every hour. However I haven't found a way to restore log files only, as it appears a full restore has to be done before you can restore transaction log files.
I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this? Say by only restoring the latest log file or by running a partial restore so we don't have to restore all the files every time?


texts: Grant read access to my SQL Server database to all future SQL users

texts: Can I configure my SQL Server 2014 database so that any login that will be created in the future will have read access to it automatically?


texts: SQL Server dbo permissions for specified databases

texts: Can I assign SQL Server dbo permissions to a user, so it applies only for specified databases on a server?  Currently using SQL Server 2005.


texts:
Assume I have the following data:

| f1 | f2 | f3 |
|----|----|----|
|  1 |  1 |  1 |
|  1 |  1 |  5 |
|  1 |  2 |  3 |
|  1 |  2 |  6 |
|  1 |  3 |  4 |
|  1 |  3 |  7 |
|  2 |  1 |  2 |
|  2 |  1 | 22 |
|  2 |  2 |  3 |
|  2 |  2 |  4 |

There are two f3 values maximum per each f1,f2 combination.

For this specific example, I want to get top-2 minimum values per f1 and range (f2 - max(f2) per f1).

Example output:

| f1 | RNG| f3 |
|----|----|----|
|  1 |1 -3|  1 |
|  1 |1 -3|  3 |
|  1 |2 -3|  3 |
|  1 |2 -3|  4 |
|  1 |3 -3|  4 |
|  1 |3 -3|  7 |
|  2 |1- 2|  2 |
|  2 |1- 2|  3 |
|  2 |2- 2|  3 |
|  2 |2- 2|  4 |

To create the field RNG is not required. I only added it to show that for f1=1, there are 3 ranges: 1-3, 2-3, 3-3 created by the distinct values of f2 for f1=1. For each such range I want to calculate the top-k minimum values per f1 and range.

SQL Fiddle here:

http://sqlfiddle.com/#!15/9ddbb/1

Building the Ranges may be done by:

SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2;

Is there any way to achieve that, without building intermediate DB tables?


texts:
I am getting the error below, while restoring a dump to RDS MySQL 5.6. Dump is taken from MySQL 5.5 originally. I tried restoring twice but same error. Can anyone help

ERROR 1064 (42000) at line 125596: 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
''{\"cc_session_id\":\"6c4kst3dcgn7od7lmlu6o5lio5\",\"cc_action\":\"cca_editroste' at line 1


texts: SQL Server 2005 - Best way to constraint a Datetime field as Date

texts:
Whereas:

  date field type starting with SQL Server 2008

Given a table:

CREATE TABLE dbo.MyTable
(
    Code int NOT NULL,
    DateCode datetime NOT NULL
)
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Code] ASC, [DateCode] DESC
);

Which is the best way to CONSTRAINT that DateCode is only a Date?

It must allow dates without time part.

2017-01-02
2017-01-02 00:00:00.000
20170102

By now I'm using:

ALTER TABLE dbo.MyTable ADD CONSTRAINT [CHK_MyTable_DateCode]
    CHECK ([DateCode] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateCode])));

But, I wonder if there is a best/short way to accomplish it.


texts: How to see resumable timeout of another session in oracle?

texts: How I see the setting of timeout about resumable of another session? I've found the function dbms_resumable.get_timeout, but there is no way of inform some parameters about a specified session.


texts: MySQL Synchronous Replication on Ubuntu 12.04 with DRBD

texts:
I have been looking around for some easy and open-source ways to handle database synchronous replication.

DRDB on a Linux server seems to be one of the fastest and easiest methods to handle database synchronization for DR purposes and HA.

I want to set up the above fact between two ubuntu 12.04 Machines treating one machine as active and another as passive.

I need some handy guidance and useful steps to setup this fact.


texts: Is using strings as keys for reference to other tables bad in terms of memory usage?

texts:
So i have to deal with the following scenario:
I have a table , users , which holds all of the users of my application .
Each user has an email which is unique and thus could be the primary key of said table .
Let's now say i have a second table that associates some data with a user . What pops into my mind is to have that second table hold an email column which i can then use to associate a row with a user .Is this approach bad in terms of memory? Will MySQL store said email repeatedly for each extra table that holds an email column to reference users table? Should i instead just use an auto-increment id for each user and use that for the whole reference thing since an integer is way lighter than a string big enough to hold an email?


texts: MySQL master/slave replication - replicate all tables

texts: I am doing a master/slave replication, but getting errors about my tables does not exist. Is there not a way where i can make the whole table structure replicate aswell? Thought it did on default tbh.


texts: Do Log Backups depend on Differential Backups?

texts:
I have the following backup strategy set up:

My understanding so far, was:
Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both could co-exist in parallel without affecting each other.
Based on this understanding, I thought that I would have two alternatives to perform a complete restore to the most recent state.
Alternative #1:

This alternative #1 works as expected with no issues.
Alternative #2:

This alternative #2 shows the following error.

Msg 4305, Level 16, State 1, Line 53 The log in this backup set begins
at LSN 144000000122400001, which is too recent to apply to the
database. An earlier log backup that includes LSN 73000000188800001
can be restored.

After analyzing the backup history, I can conclude that if there are Differential backups prior to Log Backups, the latest Differential backup MUST be restored before restoring backups.

So far, I have always thought that since Full/Diff backups do not affect the chain log, Differential backups were mostly meant to shorten recovery time by reducing the number of log backups to be restored.
However, as shown here, perform a complete restoration based only in log backups is not being allowed if DIFF backup hast not been restored, so, my question is:
What is the reason behind DIFF backup being required to allow LOG restores in Restore Sequence #2?


texts: Permissions Error on Member that doesn't exist

texts:
My team built an SSAS cube a year or two ago; it was the second cube we've built. We rolled it out to a few teams who have been using it without any issues. Today, I sat down with a new user to set up the data connection in Excel and do a little training, and the user received a strange error:

  DimensionPermission (1, 2) The '[J]' member was not found in the cube when the string, [Measures].[J], was parsed.

What's strange is that the member in the error message does not exist in this particular cube. It exists in the first cube our team built, but none of the calculations or queries in the second cube use members from the first cube. We used the first cube as a template for the second cube, but all the original measures/dimensions were overwritten or deleted.

As far as I know, no other users have seen this error including users in the same AD group. The security is pretty basic on the cube, AD groups are used in the database roles, nothing is dynamic and we aren't using MDX to specify security.

I've combed through the SSAS project in data tools, and I can't find any reference to this member. Does anyone have an idea of what might be going on here? This and this were the two common themes I found in my searches, but as I said before, everything from the first cube was overwritten or deleted, so I can't find anything in the cube that references the missing member.


texts: SQL Server 2008 - How did I solve the need to check the column definitions quickly?

texts:
Another tool I developed for myself, with regards to making the creation of various DML operations easier in a large database of hundreds of tables, is a nifty user defined function to easily show almost all relevant information about a column. Running the function will display the type, length, potential key constraints (FK or PK), potential indexes as well as potential triggers tied to the column.

Code posted in the answer below. There may be situations where the function doesn't work, depending on some database settings or features that don't exist in our project. But it should be relatively dynamic.

Again, if you have any similar useful tips or advice, don't hesitate to share!


texts:
First of all I'm acknowledging you that I am a completely newbie in this field. The field I'm referring to is the summation of these things:

Accessing files on different computers or over the internet programmatically or this PHP + MySQL
Upload some files through the internet hosted for free
Uploading files using FTP
Other common internet protocols

(I don't know what to call to them)

I have already uploaded some files in the free web host I chose and created a database there using phpMyAdmin v2.11.4

Also, I have Googled first this question before posting this here but I don't understand some rules and I need a clarification.

The exact error I get from viewing my uploaded file(index.php) is this:

Warning: mysqli_connect() [function.mysqli-connect]: (HY000/1130): Host '31.170.160.98' is not allowed to connect to this MySQL server in /home/a6400350/public_html/subdomains/index.php on line 7

Domain name of free web host I'm using is www.000webhost.com

And my php code for connecting to host is:

<?php

$host = "sql10.000webhost.com"; //or "31.170.160.98"
$username = "a6400350xxxxxx";
$password = "••••••••";

$con = mysqli_connect($host, $username, $password);

?>

I also check if the server is down here or it's just me
There was also no previlege section in phpMyAdmin page as other
suggest

Don't I have any permission to connect programmatically to this host as I am just a free user?

Edit

  I'm also thinking of uploading the whole XAMPP in the server so I
  could do the things I'm imagining.


texts: SSRS report expression FormatDateTime changes Nulls to '1/1/0001'

texts:
I have a report where I need to remove the time from a datetime column.
I've found this expression to work but it changes all the null values to '1/1/0001'

=FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value,DateFormat.ShortDate)

I have tried the below expression to look for nulls but it doesn't appear to work.

=IIF(Fields!Temperature_Control_Inspection_Date is nothing, nothing, FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value,DateFormat.ShortDate))

How can I show just the Date from a DateTime column without also having all the nulls replaced by '1/1/0001'?


texts: Migrating from Oracle to MySQL via MySQL workbench: Table list is empty

texts:
MySQL workbench offers a migration tool that I'm using to migrate an Oracle database to MySQL.
I'm using the migration wizard to migrate the complete Oracle schema to MySQL. I used that for the migration wizard and a RDBMS source thru odbc to connect the oracle source.
Step 1 : Source is configured and tested
Step 3 : Introspection is OK

Step 4 : Table list is empty

Unfortunately, the object list is empty, so I can't get the next step.
A short test of the odbc driver through excel confirmed that the odbc is correctly configured and displays the table list.

mysql workbench 6.3
odbc driver oracle 11.2
java 1.8
Oracle database 10g


texts:
How does SSDT handle batching of Data Flow tasks?

I am parsing a CSV that is about 1GB into SQL Server using SSDT.

In the Data Flow there is a Flat File source, that goes to an OLE DB destination (a staging table). Then an SP is executed using a SQL Task.

The CSV being parsed contains a summary table and a child table with a foreign key reference to the summary table. As such the CSV contains duplicated IDs of the summary table (so there is one line per child row). If a single summary ID were to be split across two batches, then I would lose data - the SP does something like "delete from child table where ID in staging, then re-insert from staging into child table". Previously we had to do this because the vendor wasn't exporting a unique ID for the child data. They are now, so I can use a merge statement.

But. I would still like to know if Data Flow tasks are batched, and if so, how?


texts:
Is there a way to recover data from partially damaged Postgres DB? My HDD was damaged in transport, I was able to recover most of the files but I cannot connect to the DB or use dump: I'm getting error:

psql: FATAL:  could not read block 0 in file "base/18819/11700": read only 0 of 8192 bytes.

This is one of the missing files though most of them are fine. Is there a way?

Thanks


texts:
I am creating a database for a DVD rental shop, I have various entities that are related to this question, such as Film, FilmStar.

  For each film, you record its unique number, title, the year in which it was made, its category (action adventure, science fiction, horror, romance, comedy, classic, children's), its director, and all stars that appeared in it. For each film, you also want to store the type of DVD hire (new release, classics, other).

I am mostly unsure about "all the stars that appeared in it". I first thought just having an attribute in the 'Film' Entity, for example filmStar and then each star would be inserted into that attribute, for example: "John Doe, Jane Doe" for each film. But then I realised that this wouldn't be 1NF as : "the domains of attributes must include only atomic values, the value of an attribute must be a single value from the domain of that attribute", as it contains more than one value and isn't atomic.

I then thought about having a separate entity that contains certain attributes such as: filmID, filmStarID. So John Doe would have the filmStarID of '0001' (all of this would be in the FilmStar entity, which is a separate entity). But then the same problem would occur, for example the filmID attribute would have all of the filmID's that the filmStar has starred in, for example: John Doe would have "101, 115, 009". Which again wouldn't be 1NF.

I was just wondering what your thoughts are on this?


texts: Changing number of rows when importing table

texts:
I'm currently importing a table with about 17 million rows in MySQL. What catches my attention is, the number of row seems to be fluctuate.

I used the command show table status while my importing process was running. One time, I got 1.5 million of rows, then I ran that command again and got lower than 1.5 million. How is that possible?


texts: Standard Edition of SQL Server and cores

texts:
Looking for confirmation that we are overpaying for our VM :)

We have a SQL Server 2012 Standard Edition on a 7 socket (2 cores/socket) VM. From error log:

SQL Server detected 7 sockets with 2 cores per socket and 2 logical processors per socket, 14 total logical processors; using 8 logical processors based on SQL Server licensing.
Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 1: CPU mask: 0x000000000000000c:0 Active CPU mask: 0x000000000000000c:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 2: CPU mask: 0x0000000000000030:0 Active CPU mask: 0x0000000000000030:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 3: CPU mask: 0x00000000000000c0:0 Active CPU mask: 0x00000000000000c0:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 4: CPU mask: 0x0000000000000300:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 5: CPU mask: 0x0000000000000c00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 6: CPU mask: 0x0000000000003000:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.

As I understand it, Standard Edition supports the lesser of of 4 sockets or 24 cores, thus I conclude we are paying extra for 3 sockets/6 cores that are not being used (this is a dedicated SQL box).

I am thinking we have two choices:

Update VM and remove the 3 unused sockets, so we have 4 sockets/8 cores.
Update VM and remove the 3 unused sockets, update number of cores per socket from 2 to 4, so we have 16 processors.  I need to check if this is even possible.

If #2 is possible, would this help with performance, assuming we are hitting CPU pressure at times?


texts: MySQL Union All Causing date field to lose format

texts:
I have 3 subqueries that work fine individually. I am attempting to join them with Union All. The query runs and gives the correct number of results but the format of the date field is messed up; for example '2016-11-01' is showing as '2,016, -11, -01'. This is the same for every row.

I've had a search and have found some threads concerning aliases but they do not seem to be the problem here as the query is running fine, with the exception of the date format.

Any help would be appreciated, thanks.

Here is the code:

    # offers_8a
    SELECT advisor, ats_id, offer_date
    FROM max_offers
    WHERE offer_date > (CURDATE()- INTERVAL 135 DAY)
    GROUP BY advisor, ats_id, offer_date

    UNION ALL

    # offers_8b
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN source_tables.dates as dt
            ON ic.intake = dt.current_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY)

    UNION ALL

    # offers_8c
    SELECT mo.advisor, mo.ats_id, mo.offer_date

    FROM max_offers as mo
        INNER JOIN source_tables.ats as ats
            ON mo.ats_id = ats.ats_id

        INNER JOIN source_tables.intake_corrections as ic
            ON ats.intake = ic.sugar_name

        INNER JOIN source_tables.intakes as it
            ON ic.intake = it.intake

        INNER JOIN

            #s_prev_intake
            (SELECT current_next_intakes.current_intake, assign,         forecast_intake, forecast_intake_sugar

                FROM source_tables.dates
                    INNER JOIN source_tables.current_next_intakes
                        ON source_tables.dates.current_intake =  source_tables.current_next_intakes.current_intake

                WHERE assign = 'Prev') AS prv

            ON ic.intake = prv.forecast_intake

    GROUP BY mo.advisor, mo.ats_id, mo.offer_date, it.adms_intake_start

    HAVING offer_date > (it.adms_intake_start - INTERVAL 135 DAY);


texts:
I am need to select the date stored in a column transactionDate datatype date and time stored in column transactionTime datatype varchar from a table transactionTable in mysql. My query is like

select concat( CAST( transactionDate as char), transactionTime) as transaction
from transactionTable
where id = 123

There are values in both the columns but the above returns me a null. How can I concat date and varchar columns?


texts: Using WITH TIES to retrieve multiple rows with same value

texts:
I am relatively new to SQLite. I have a database called "Clothes Catalog" that contains info on each data point's item type (shirts, pants, etc), catalog ID, and price.

I want to retrieve the maximum price of an item of each type, the item type, and the catalog ID. If two items within a type have the same price, I want both catalog ID's to be included.

I tried this query but it does not run, any ideas why?

  SELECT TOP (1) WITH TIES itemtype, catalogID, price
  from clothes_catalog
  group by itemtype
  order by catalogID ASC;

Lets say my table looks like this:

 shirt, 20456, $16
 shirt, 23456, $18
 pant, 2222, $20
 pant, 4444, $20
 pant, 5656, $15

I want the following to show, and ALSO have catalogID in ascending order:

 pant, 2222, $20
 pant, 4444, $20
 shirt, 23456, $18

any suggestions?


texts: Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?

texts:
Let's say we have a big table with hundreds of million of rows (or more).

On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level:

SELECT * FROM bigtable;

On a different connection, we perform an update to a row in the table with auto commit on, slightly after the SELECT statement started executing in the other connection:

UPDATE bigtable SET value_col = <new_value> WHERE pk_col = <key>;

Is the SELECT statement, which is executed under READ COMMITTED isolation level, guaranteed to read all rows as they are in the table when the SELECT statement starts executing? Or could the SELECT statement read the value as updated by the second connection?


texts: I have a table in mysql which has a many columns,one of them being time.I want HOUR(time) as unique key for the table,that is there should only be one record for every hour.How can i do it?


texts:
I need to create a pivot table of all customers and all products and the price that the customer gets charged but also falling back to a standard customer when the customer has no price set up (the customer 999999 is the standard customer that has a standard price for all products).

I wrote this query;

Select KUST_ADR.KU_NAME As Customer,
  GLAS_DATEN_BASIS.GL_BEZ As Product,
  NVL(OS_PRZU.ZUM2, (Select OS_PRZU.ZUM2 From OS_PRZU
  Where OS_PRZU.IDNR = GLAS_DATEN_BASIS.IDNR And OS_PRZU.KUNR = 999999)) As
  Price
From GLAS_DATEN_BASIS
  Inner Join OS_PRZU On GLAS_DATEN_BASIS.IDNR = OS_PRZU.IDNR
  Inner Join KUST_ADR On KUST_ADR.KU_NR = OS_PRZU.KUNR
Where KUST_ADR.KU_ADR_ART = 0

but still lots of the fields are empty, so NVL() is not working? What am I doing wrong?


texts: (Solved) Sql Server Failover Clustering Error

texts:
I want to install always on on Sql server 2019. I have two windows server 2016. I will not use active directory. I do the installation as described on the site where I will leave the link below. When I add node2 to the failover cluster while installing, I get the "you do not have administrative privileges on the server node2" error. How can I resolve this error? Thanks.
Guide site: https://www.emreozanmemis.com/windows-server-failover-cluster-uzerine-ms-sql-alwayson-kurulumu/
--Solved--
I solved the problem by running the following command on Powershell.
New-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
--Solved--


texts: Unable to backup to a UNC path with a gMSA

texts:
I'm trying to automate backups using Ola Hallengren's backup scripts.

I'm testing this out on a SQL express instance 2008 R2 (I know it's EOL today).  I've setup the jobs and I created a scheduled task to run a batch script with SQLCMD.  The scheduled task is running as a gMSA

Everything seems to be groovy except for some darn reason I can't reach the UNC path I'm trying to backup to.  The error I'm getting is as follows:

Msg 50000, Level 16, State 1, Server servername\instancename, Procedure DatabaseBackup, Line 878
The directory \\servername\sharename does not exist.

This has to have something to do with the gMSA not having proper permission to the share or something but I've explicitly granted this account full access at the share and NTFS levels (out of desperation) and still no dice.  Obviously the share does exist, and I can connect to it just fine using other accounts.

If anyone has any experience running SQL server with gMSA's who could shed some light on this I would greatly appreciate it.

Thanks,

UPDATE - Just for kicks I gave the service account sysadmin priviledges on the SQL server and now it works.  I have no idea why that would fix problems with access to a file share so the error is definitley misleading.  What are the minimum rights required to run a backup for databases?  Initially I gave the service account the "public" server role and "db_backupoperator" on each database.  Looks like I'll need more than that?  The target here is least priviledge.

Thanks


texts: Will MongoDB exceed the 64000 ulimit threshold?

texts:
We are using MongoDB as a newbie and today we have exceeded the ulimit of the system causing chaos and panic all around.

However I have fixed it by increasing the hard and soft limits like below.

mongouser soft nofile 65535
mongouser hard nofile 65535

From what I understand, these are the limits for opened files.

My question is, will mongodb ever exceed this limit? If so how do I prevent it?
Does MongoDB have a mechanism to open/close files or will they always stay open and the journal files or db files always increase in size or do I have to implement some mechanism for it.

P.S. We are also using MMS to monitor the health of the DB.


texts: How to create and fill multiple session temp tables with dynamic SQL?

texts:
I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON keys and their types such that I can construct a strings that contain the text to create a temp table and pivot the results of OPENJSON to INSERT into that temp table.

However, I am having issues dynamically creating the temp tables with EXECUTE( @stmt ), EXEC sp_executesql @stmt and the OPENROWSET adhoc distributed query trick.

DECLARE @dynamicCreateStmt NVARCHAR(MAX),
        @dynamicPivotInsertStmt NVARCHAR(MAX);

--CREATE statement for #temp table
SET @dynamicCreateStmt = '< some combination of string constants and results of STRING_AGG>'

--PIVOT - INSERT statement for #temp table
SET @dynamicPivotInsertStmt = '< some combination of string constants and results of STRING_AGG>'

--First try
EXEC (@dynamicCreateStmt);
EXEC (@dynamicPivotInsertStmt);
SELECT * FROM #temp into #Working1

<repeat for various "working" tables>

--Second try
EXEC sp_executesql @dynamicCreateStmt;
EXEC sp_executesql @dynamicPivotInsertStmt;
SELECT * FROM #temp into #Working1

<repeat for various "working" tables>

--Third try; tries to skip intermediate table.
DECLARE @dynamicPivotStmt NVARCHAR(MAX);

--PIVOT statement for #temp table
SET @dynamicPivotStmt = '< some combination of string constants and results of STRING_AGG>'

SELECT * INTO #Working1 FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;Database=MyDB;',
     @dynamicPivotStmt)

<repeat for various "working" tables>

The first 2 will execute but the temp table created is not in the current scope. The last wont run because a variable cannot be supplied as the query parameter.

EXECUTE ... INTO is not an option as that is limited to one occurrence per  run and I need to dynamically create additional tables as the run progresses.

Any ideas of how to dynamically create and fill multiple session temp tables
 at different locations in the same run?


texts: How to auto replicate specific columns only from a table in MySQL

texts:
I want to make one master DB which replicates specific user data into other databases such as:

username
pass (hashed)
email
pin (hashed)
timezone (int)

They are all in one table and running on MySQL 5.5.37 (possible to update to 5.6).

The reason I want to do this is because I have multiple sites using different databases and they all have different logins. Once I get this done I can enable SSO across them and save more than 50 % of the users a big hassle to not signup more that once on the different sites.

Where should I start? Is there any good info to read? I've been looking in the MySQL help files but could not find any info on how to do this for specific rows.


texts: How to view phppgadmin as the application name in PostgreSQL's log files?

texts:
I am using PostgreSQL 9.2.2 (Windows) with phpPgAdmin 5.1.

I need to monitor who connects to the server, and with which application.

In the logging parameters of postgresql.conf, I use the following log line prefix:

log_line_prefix = '%t\t%a\t%d\t%h\t%u\t'

where %a specifies the application connected to the database.

Once a user is connected to the server using psql and executes a command, it shows in the log as follows:

2016-03-14 14:55:11 EDT psql    phenotree_development   000.000.000.00  cleseb  LOG:  duration: 32.000 ms  statement: select * from organisms limit 10;

However, when PhpPgAdmin is used, the application name never shows, for example:

2016-03-17 09:30:30 EDT [unknown]   phenotree_development   ::1 cleseb  LOG:  duration: 0.000 ms  statement: SELECT * FROM (select * from organisms) AS sub  LIMIT 30 OFFSET 0

How to have the application name appear in the log for PhpPgAdmin?


texts: Inserting data into table in server B whenever table in server A gets updated

texts:
I want to synchronize data from table 1 in server A to tables 2 and 3 in server B, whenever table 1 gets updated.

Tables from server A are not related to tables from server B, so I'd have to specify which columns in table 1 correspond to which columns in tables 2 and 3, and perhaps do some sort of operation on that data.

Server A, Table Y

Name  | Country | Sales
Oscar | Peru    | 5000

Server B, Table X

Name  | Region | Amount | Currency | Multiplier | TotalAmount
Oscar | Peru   | 5000   | €        | 5          | 25000 (=5000*5)

The goal here is to insert the record from server A to the table X in server B.

The 'Name' field maps to the 'Name' field
The 'Country' field maps to the 'Region' field
The 'Sales' field maps to the 'Amount' field
The 'Currency' field in server B will always be €
The 'Multiplier' field would be retrieved from another table in server B
The 'TotalAmount' field would be the result of Amount * Multiplier

Would this be possible to automate? Is using triggers the way to go?

I'm using SQL Server (and SMSS). The sync doesn't need to be instantaneous, it could take up to 10 or 20 minutes. The two servers are already linked. Server B is not used just for read only queries.


texts:
In SQL Server 2008, I have a table called Info.Case, which lists all the cases we have, Info.Person which lists all the people in the database, and a table called Links.Case_Person, which lists how each person relates to each case.

Here is what I am trying to do:

Find all the cases John Smith (Person_ID = 123) is listed as Primary (Role_ID = 5) or Secondary (Role_ID = 11).

So far this is not an issue:

SELECT * FROM Info.Case IC
INNER JOIN Links.Case_Person LC
ON IC.Case_ID = LC.Case_ID
INNER JOIN Info.Person IP
ON LC.Person_ID = IP.Person_ID
WHERE LC.Person_ID = 123 AND LC.Role_ID IN (5, 11)

This generates 100 Cases.

Now I want know know if there was also an assistant (Role_ID = 22) on any of these specific 100 John Smith cases. So I want to search just the 100 cases for any Role_ID = 22.

How do I do this?


texts: Log shipping to fix unintended deletion?

texts:
I am reading the book 'Pro SQL Server 2019 Administration' from Carter:

In a scenario in which a user accidentaly deletes data; he mentions that we can recover the data (from the DR server) and then "repopulate the production server".
Then he adds "This means that you do not need to restore a backup to recover the data."
What he means by 'repopulate'? and how is this better/different than a backup/restore?


texts: Reasons for a session got executing the same SQL for hours

texts: I've found a situation where some sessions got executing the same sql_id (select) for hours (the most was 32 hours). all sessions was executing the same sql_id.. Looking in the database I didn't find any wait event holding the execution. Is this a application issue? or most likely in database side?


texts: Table update is causing front end SQL statement to fail

texts:
We were in a situation where we needed to add many rows of data to our database and decided the most efficient method was to use an SQL INSERT INTO statement. The table was very simplistic. A primary key column plus an additional seven columns, three being date columns. The table doesn't have any constraints or columns with the IDENTITY property. We used the MAX function to determine the highest primary key id assigned and then added 1 to that value for the new row(s) to be added.

Everything appeared to go well on the back end and the front end. However, now when end users enter a new a record via the front end they are getting the error message "SQL Statement failed, this may be caused by network or database issues". This is relevant because it is only happening on a field that is directly associated with the table we updated.

Can anyone offer any insight to what may be happening?


texts: Is BulkCopy available for SQL-Server 2012

texts: Is BulkCopy still supported by SQL-Server 2012?


texts: Postgresql extension pageinspect - mean bt_metap

texts:
I am using Postgresql extension pageinspect.

Could someone tell me the meaning of these columns: magic, version, root, level, fastroot, fastlevel of the bt_metap function.

This information is not presents in the documentation 1[https://www.postgresql.org/docs/9.6/static/pageinspect.html]

The height of the b-tree (position of node farthest from root to leaf), is the column Level?

See below a return query that I ran on an index called idx_l_shipmodelineitem000

------------------------------------------------------------------
postgres # SELECT * FROM bt_metap ('idx_l_shipmodelineitem000');
postgres # magic    | version   | root     | level | fastroot | fastlevel
postgres # 340322   | 2         | 41827 | 3       | 41827   | 3

Best regards


texts: Large scale, hard to model design advice needed

texts:
I would love to hear some opinions or thoughts on a MySQL database design.

Basically, I have a tomcat server which receives different types of data from about 1000 systems out in the field. Each of these systems is unique, and will be reporting unique data.

The data sent can be categorized as frequent, and infrequent data. The infrequent data is only sent about once a day and doesn't change much; it is basically just configuration based data.

Frequent data is sent every 2-3 minutes while the system is turned on, and represents the current state of the system.

This data needs to be stored in a database for each system, and be accessible at any given time from a PHP page. Essentially for any system in the field, a PHP page needs to be able to access all the data on that client system and display it. In other words, the database needs to show the state of the system.

The information itself is all text-based, and there is a lot of it. The config data (that doesn't change much) is key-value pairs and there are currently about 100 of them.

My idea for the design was to have 100+ columns, and 1 row for each system to hold the config data. But I am worried about having that many columns, mainly because it isn't too future proof if I need to add columns in the future. I am also worried about insert speed if I do it that way. This might blow out to a 2000 row x 200 column table that gets accessed about 100 times a second so I need to cater for this in my initial design.

I am also wondering, if there are any design philosophies out there that cater for frequently- and seldom-changing data based on the engine. This would make sense as I want to keep INSERT/UPDATE time low, and I don't care too much about the SELECT time from PHP.

I would also love to know how to split up data. If frequently changing data can be categorised in a few different ways should I have a bunch of tables, representing the data and join them on selects? I am worried about this because I will probably have to make a report to show common properties between all systems (i.e. show all systems with a certain condition).

I hope I have provided enough information here for someone to point me in the right direction, any help on the matter would be great. Or if someone has done something similar and can offer advice I would be very appreciative.


texts: How does Log shipping knows to keep track

texts:
I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012),

At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping,

The databases first backup-restore took a few hours and i wish to know:

how does the log shipping process knows from when to start the logs
it is shipping? does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
Can i be sure that no information was lost in these few hours ?

Thanks


texts: The number of copies for each table stored in physical storage

texts:
I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for each table. If we only have one copy for each table, then it will result in one point failure if that storage device is broken. Is my understanding correct?

Thanks,
Zack


texts:
Is it possible to limit the number of rows returned when a condition is met?
I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per subject. I don't want all classes, because any class past the number of credits needed can be used for electives.

EDIT: I forgot to mention that this is SQL 2008 R2

I was hoping to be able to do something like this (which I know doesn't work)

select top(sum(credits) > 3.0) c.name, c.subject, c.credits, c.pass
from classes as c
where c.Subject = 'Math' and c.passed = 1

Any help would be great

Data

Subject        |  Name             | Grade | Credit | Pass
Social Studies | DL Psychology     | 83    | 0.50   | 1
Social Studies | Global Studies 10 | 82    | 1.00   | 1
Social Studies | Global Studies 9  | 83    | 1.00   | 1
Social Studies | Part. In Govern   | 84    | 0.50   | 1
Social Studies | US History 11     | 87    | 1.00   | 1

Query

select c.Subject,
           c.Name,
           c.credits,
           c.pass,
           c.score
    from @classes as c
    where (c.Subject = 'Social Studies' and c.pass = 1 and c.Name like '%Econ%')
    or    (c.Subject = 'Social Studies' and c.pass = 1 and c.Name like '%Gov%')
    or    (c.Subject = 'Social Studies' and c.pass = 1)
    group by c.Subject, c.Name, c.credits, c.pass, c.score
    having Sum(credits) <= 2.0

Im exprecting to see these rows returned

Expected Results

Subject        | Name              | Grade | Credit | Pass
Social Studies | Part. In Govern   | 84    | 0.50   | 1
Social Studies | DL Psychology     | 83    | 0.50   | 1
Social Studies | Global Studies 10 | 82    | 1.00   | 1


texts: Can two transactions share the same SCN?

texts:
Do all transactions have a unique SCN?  Can two transactions share an SCN?

I'm confused by this statement in the Oracle Golden Gate manual:

  The CSN is cross-checked with the transaction ID (displayed as XID in Oracle GoldenGate informational output). The XID-CSN combination uniquely identifies a transaction even in cases where there are multiple transactions that commit at the same time, and thus have the same CSN. For example, this can happen in an Oracle RAC environment, where there is parallelism and high transaction concurrency.

https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_csn.htm#GWUAD752

Table D-1 in that document identifies the GoldenGate CSN as being the Oracle DB SCN:

Oracle GoldenGate CSN Values Per Database

Oracle:  system_change_number
Where:   system_change number is the Oracle SCN value.


texts: How to Increment duplicate strings in a column

texts:
I have a table similar to what is below.  I have listed only one column since that's what I'm interested in.

| column
---------
| foo
| foo
| foo
| bar
| bar
| bar

Below is the output that I desire but I can't seem to figure out how to make it happen.  This column is not a primary key or serial key.

| column
---------
| foo 1
| foo 2
| foo 3
| bar 1
| bar 2
| bar 3

I want to increment the string if there are duplicates.


texts: Separate month and year columns, or date with day always set to 1?

texts:
I'm building a database with Postgres where there's going to be a lot of grouping of things by month and year, but never by the date.

I could create integer month and year columns and use those.
Or I could have a month_year column and always set the day to 1.

The former seems a bit simpler and clearer if someone is looking at the data, but the latter is nice in that it uses a proper type.


texts: How to block pg_stat_reset() statement in DB

texts:
Team,

Recently we have found one of our application is triggering pg_stat_reset() executing everyday which is causing performance issue . to disable this statement thru application level will take more time.

Is there any method we can disable in DB even though it trigger from application we should block it.
Please help me to resolve this.


texts: Oracle data obfuscation on specific columns with set field sizes

texts:
Would like to obfuscate data held in Oracle tables, but:

Requirements

  only apply to specific columns e.g. personal data
  obfuscated value should not be longer then column size
  possibly only obfuscate after the 1-3 characters

Notes - still doing analysis:

  Could use translate, but easy to hack unless random used to
  Use DBMS_CRYPTO, but may blow column widths...
  Use DBMS_OBFUSCATION_TOOLKIT, but looks legacy and replaced by DBMS_CRYPTO
  Use Oracle Wallet to encrypt data files which would still give data in unencrypted form in case tablespaces stollen
  Possibly being able to reverse the decryption using a key, but think not possible due to some column field lengths being small


texts: How to identify what SSIS package(s) use a given Environment Variable?

texts:
I suspect my Google skills are just insufficient today, but I'm looking for a quick TSQL script that will identify all SSIS packages in the Package Store that are using a given Environment Variable Name.

For instance, when I Configure a package and look at the Paramaters page, I can choose a given Environment Variable.  In the following screen shot, the environment variable I'm concerned with is named SMTP.  I want to find all packages using this environment variable so I can perform some investigation/testing before making any en masse changes.

If someone has a script readily available, I'd be grateful if you could post it, otherwise I'll post something after I code it up.

Thanks!


texts: linked servers between availability group to a server

texts:
I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables from acceptance to production. Unfortunately, due to some reasons the only option left with me is running the script by linking the acceptance server with production.

1.How can I link the "acceptance" with "Production"?

2.Is it same as just linking 2 servers or is there anything different to link a server with the listener of the availability group?

3.If we copy the tables by linking server with listener, is these tables gonna save in all the three servers which are in availability group?


texts: What should be the rule for excluding an FD from consideration while doing BCNF or any other Normalization?

texts:
Let us consider this example:

http://www.mathcs.emory.edu/~cheung/Courses/377/Syllabus/9-NormalForms/examples.html

At the first decomposition step, it is using A -> B C D E.

On the next step, the same FD is not considered any more.

Is it because,

A -> B C D E has already been used up in a decomposition.

Or,

A -> B C D E is irrelevant to the new calculation.

Note: So, the answer is,  " A -> B C D E has already been used up in a decomposition."


texts: How to search a MySQL database with encrypted fields

texts:
Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt.

How would one search those fields anyway?

Decrypting each record step by step is no option: Suppose I have multiple of thousands of records. It would take too much time and space to decrypt each record and check if each single record matches the search.

UPDATE 2012-09-07

Adding further details to the database schema would be OK, since I'm about to implement a new application. Furthermore, I need to extend applications currently running in production. But even for those application, adding further details would be OK.

UPDATE 2012-09-08

Encryption is the kernel of this question.

Access restrictions, as proposed by some answers, already apply - but do not fit the formal requirement to encrypt data.

This formal requirement is not Payment Card Industry Data Security Standard [PCI].


texts: BUILTIN\Administrators allowing access via linked server but not locally

texts:
We have two servers:

LYNDB01 & LYNDB02

On LYNDB01 we have a database called db_ExampleDatabase

We have created a Linked Server on LYNDB02 to LYNDB01 using no mappings and the "Be made using the login's current security context" option for authentication:

EXEC master.dbo.sp_addlinkedserver @server = N'LYNDB01', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LYNDB01',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

We have a windows user on LYNDB02 for DOMAIN\USER1 - this windows user does not have a login on LYNDB01 at all but is a domain admin and thus is a member of BUILTIN\Administrators on both LYNDB01 & LYNDB02

I have created a view in the LYNDB01.db_ExampleDatabase database to confirm what user is running the transaction:

CREATE VIEW [dbo].[vw_CurrentUser]
AS
    SELECT  SYSTEM_USER AS CurrentUser

For testing we gave DOMAIN\USER1 RDP access to LYNDB01 & LYNDB02.

If we RDP to LYNDB01 as DOMAIN\USER1 and open up SSMS with windows authentication - we get a logon error as DOMAIN\USER1 does not have a login on LYNDB01, so that's fine.

If we RDP to LYNDB02 as DOMAIN\USER1 and open up SSMS with windows authentication it connects OK (Which is fine as DOMAIN\USER1 has a login on LYNDB02). Here is our problem, we can access LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser via the Linked Server!

SELECT * FROM LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser

How is this possible when LYNDB01 does not have a login on LYNDB01 (Which is confirmed by the error we get when connecting locally!)

And yes, the remote query confirms that DOMAIN\USER1 is the current user??

VERY confused?!

Edit: Theroy 1

DOMAIN\USER1 is a domain admin and is therefore a local admin on both LYNDB01 & LYNDB02 - could this be causing it? if so, why would i get access denied on LYNDB01 as USER1 has no logon but work via a linked server?

OK, if i delete BUILTIN\Administrators logon from LYNDB01 this problem goes away and the query from LYNDB02 fails - why is BUILTIN\Administrators letting USER1 authenticate remotely via a linked server but not locally & what can i do about it?


texts: How to delete duplicate records in my database?

texts:
I joined about 12 databases into 1 db and in each database I have some duplicate records.

How can I delete all duplicate records in my joined database?

For example, I want to delete duplicate records when data in column "phone" is the same.


texts: Update column with the value of two columns multiplied

texts:
I want to update ColumnA with the value of ColumnB x ColumnC. Can someone please help? I have tried the following:

UPDATE database.table
SET ColumnA = ColumnB * ColumnC;

Error message as follows:

  ERROR:  operator does not exist: double precision * character varying
  LINE 2: SET ColumnA = ColumnB * ColumnC;
                                  ^ HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
  SQL state: 42883 Character: 58


texts: Foreign key with additional constraints?

texts:
There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order.

How to impose an additional constraint in DB that says Item should be "Available" (at that point of time) if it needs be to added as item_id in the Order table? An item is manually determined as "Available" and can't be derived from other fields in the database in this scenario.

One schema design (I prefer) is to add a Type column which would have "Available" and "Unavailable" fields. But how can I check the Foreign Key constraint item_id should not only be a Primary Key in Item table, its Type should be "Available" as well?

This Stack Overflow answer using check constraints seems close, but is that the only way? I feel this is a trivial thing for RDBMS, or is this is not a normalized data?

The other schema design (I don't prefer) is to have a Table called "Menu" which could have only the Items Available. The problem is that this table is going to very dynamic in nature and it keeps changing depending on the availability of items. And, I am just creating a subset table out of Items depending on its state which doesn't seem to be a nice idea.

It's easy to do this programmatically; however, how do I achieve this in RDBMS? I like the idea of Database being intelligent enough to handle this.


texts: Changing stored procedure parameter results slow stored procedure performance

texts:
I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to certain sites).

The only parameter that changes between executions is the 'PortalId'.
Here is an execution plan that returns results in about 1 second
However, when changing the PortalId to a different value it takes about 21 seconds. As seen here. The slower query is for a PortalId of a newer portal so the data is presumably further towards the end of the table. Does this matter?

The interesting difference in the execution plans is that for the slower query, all rows in the Users table are scanned, however this is not the case for the faster one.

Are there any ideas on how to make the slower query faster? I assume it is an indexing problem.

Note: I have tried adding the recommended index and it actually resulted in worse performance.


texts: How to Measure data consumed by an user in Azure sql server database?

texts:
For an Azure sql server MI, we have multiple external users consuming data,
I would like to know how much data an user is consuming/extracting from the DB?

Can this be checked from,
1.) From SSMS using queries or UI?
2.) Azure portal?


texts: Normalizing/combing mutliple tables with similar data

texts:
I'm working on setting up a database of vehicle records based on data from a third party vendor. There are 3 models: Vehicles, Transmissions, and Adaptors.

Both Vehicles and Transmissions are 1:n Adaptors. Technically Transmissions are just Vehicles with additional transmission columns, while Vehicles contain fuel system columns in addition to the vehicle columns. I am having trouble determining the best way of combining my data into one normalized set.

Here are some samples of my data:

Transmissions table

Vehicles Table

Pivot Tables

As you can see, Transmissions and Vehicles are basically the same up to the trans_type/fuel_system columns (respectively) and beyond. For example, the Vehicle I have highlighted is the same as the 2nd Transmission shown with engine_code LML.

Ideally, I am hoping to end up with just one table of vehicle data. For instance, if I were to merge the highlighted records, I should end up with only 2 records for a 2013 GMC Sierra 3500 HD:
One with a 6.0L V8 engine and the other with a 6.6L V8 engine, along with the respective fuel system and transmission columns for each.

The pivot tables would also be combined as well which means I need to replace the old IDs with new IDs from the combined data, somehow.

Here are my thoughts on how to approach this:

Normalize the similar columns (makes, models, engine, transmission)
Simply split off the fuel_system and trans_type columns into their own tables (but this does nothing to help me combine, it just makes things a bit more manageable until I can figure out how to combine)
Make a new table containing all the columns from both tables, and insert the data from Vehicles and Transmissions, fill in the blanks (i.e. update fuel_system where records match and vice versa for transmission columns), then begin to clean up duplicates.

I apologize for the lengthy post, but I have yet to find anything in my searches that really outlines what this process may look like. Any advice or suggestions are welcome, and thank you in advance.


texts: Is it possible to select from procedure\function when the PDB is an argument without connect?

texts:
I have the following function in oracle 11g.
Create or Replace function get_col_value(
schema# in varchar2,
table# in varchar2,
column# in varchar2,
rowid# in varchar2)
Return varchar2
Is
 v_cmd varchar2(2000)
 v_retval varchar2(32767)
BEGIN
Begin
 v_cmd := 'select "'||column#||'" from "'||schema#||'"."'||table#||'" Where "ROWID"='''||rowid#||'''';
 execute immediate v_cmd into v_retval;
EXCEPTION When others THEN
  NULL;
End
return ''''||v_retval||'''';
END

Now I'm trying to make it work on 19c (on CDB) with additional PDB parameter:
Create or Replace function get_col_value(
pdb#  in  varchar2,
schema# in varchar2,
table# in varchar2,
column# in varchar2,
rowid# in varchar2)
Return varchar2
Is
 v_cmd varchar2(2000)
 v_retval varchar2(32767)
BEGIN
Begin
 -- v_cmd := 'select "'||column#||'" from "'||schema#||'"."'||table#||'" Where "ROWID"='''||rowid#||'''';
 execute immediate v_cmd into v_retval;
EXCEPTION When others THEN
  NULL;
End
return ''''||v_retval||'''';
END

Can I do it without a 'connect' command?
I don't want to put the DBA password in a procedure/function, and I don't want to change the script every time I change the password.


texts:
How would i verify that whatever the backup i have taken is correctly restored or not.

EG:

I am taking backup on live system where inserts are going on.backup size is around 50 GB.I have started the restore of the backup but for some reason i have not enable the log and also my session end.So how i will verify that restore was done successfully.

Before taking the backup i have even not calculated the data_lenght,index_lenght and tables row.So is there any way so that i can confirm that restore is successful

regards
Suraj


texts: Return text from PostgreSQL (9.6) to SQL Server from linked server query

texts:
I am trying to get text (15002 characters long) from PostgreSQL using a linked server from SQL Server 2012, using the official PostgreSQL ODBC driver in Windows.
I try to pull data from Postgres using the following query:
create table #temp
(id int, g nvarchar(max) )
insert into #temp
exec ('select id ,st_astext(geom) from spatialdata') at pg96odbc

This returns correct length data, but the text is truncated to ~4000 characters; the rest of the row contains null values. I see this using the following query (on SQL Server):
select id, g from #temp for xml auto

This works correctly in the other direction (Postgres pulling from SQL Server) using odbc_fdw and the SQL Server native driver.
Question: Is this a bug, or is there an ODBC/SQL Server option to get it to return all data, or a way to return varbinary(max) from the Postgres server?
I'm using BigSQL PostgreSQL 9.6, the latest ODBC driver for PostgreSQL 9.6, and the latest odbc_fdw.
FOR XML AUTO returns data containing this: (&\#x0; = null). Length of string is more or less correct, source geom is linestring.
MaxVarcharsize = -4:

Cannot get the current row value of column "[STREAM].geos" from OLE DB provider "STREAM" for linked server "(null)". [COLUMN_NAME= geos  STATUS=Unknown DBBINDSTATUS]

MaxLongVarcharSize = 32k or 8k
(first "working character string is 4000 after it comes null values up to "real" length of text) example output below:
LINESTRING(2.773577 3.849574,2.773598 ... ,2.603447 3.847&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x4;&#x0;&#x0;&#x0;ꪝ&#xF;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;⎮&#x0;&#x0;&#x0;
LINESTRING(2.905497 0.547610999999996,2.905621 0.547591999999995,...,2.894462 0.5&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x1;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;&#x4;&#x0;&#x0;&#x0;竿&#x10;&#x0;&#x0;&#x0;&#x0;&#x0;&#x0;ᔺ&#x0;&#x0;&#x0;
LINESTRING(3.608147 2.496786,3.607717 2.496774,3.607176 2.496804,3.605301 2.497387,3.603758 2.49786999999999,3.602421 2.49828599999999,3.600669 2.49880499999999,3.59832 2.49952,3.596655 2.500024,3.594704 2.500643,3.592416 2.50134899999999,3.590898 2.50182599999999,3.590145 2.50205199999999,3.589244 2.502335,3.588036 2.502623,3.586846 2.50291299999999,3.584997 2.503305,3.583173 2.503676,3.581646 2.503925,3.580145 2.50402699999999,3.578866 2.504097,3.576313 2.504255,3.574554 2.504361,3.572587 2.50447699999999,3.57084 2.504583,3.568731 2.504699,3.566653 2.50480999999999,3.565399 2.504848,3.564418 2.50494399999999,3.562436 2.50506,3.561114 2.505138,3.559385 2.50509199999999,3.558475 2.50496699999999,3.5580

Next thing to try is to change driver from 9.06.01.00 to something else.


texts: How to get one complete column in mysql?

texts:
I have a script that will fetch Last_Error in mysql slave in a particular interval of time and write that to a file.

To fetch Last_Error, currently I am using below command in the script.

mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 | grep -v "Warning: Using a password" |grep "Last_Error"

The challenge I am facing is that grep "Last_Error" will only show that particular line and not complete error. For example, one of my Last_Error look like below::

Last_Error: Error 'TRIGGER command denied to user 'user'@'xx.xx.xx.xx' for table 'table_name'' on query. Default database: 'Db_name'. Query: 'UPDATE
                    table
                 SET
                    user_status=0
                 WHERE
                    contractid ='id.000.000''

and one another look like ::

Last_Error: Error 'Duplicate entry '117583' for key 'PRIMARY'' on query. Default database: 'labs'. Query: 'insert into User (a,b,c,d) values ("Other","",now(),"")

So basically I can't use A4 kind of switch with grep. Is it possible to get one complete column in mysql or any other work around?


texts: I am getting an error while trying to download binary for mysql 5.5.11

texts:
I am trying to upgrade MySQL 5.1 to 5.5.11 on Ubuntu 10.10. Everytime I try the wget command I get this error:

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-i686.tar.gz/from/http://mysql.llarian.net/
--2012-01-27 16:16:08--  http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-i686.tar.gz/from/http://mysql.llarian.net/
Resolving dev.mysql.com... 156.151.63.11
Connecting to dev.mysql.com|156.151.63.11|:80... connected.
HTTP request sent, awaiting response... 404 Not found
2012-01-27 16:16:08 ERROR 404: Not found.


texts: Should I include as much information as possbile to help the optimiser?

texts:
Reading the article "Dynamic Search Conditions in T‑SQL" I saw the following statement in the "Using sp_executesql" section.

  Double Feature

  You may wonder why there are two conditions on the order id, customer
  id and product id (lines 32-33, 48-49 and 64-65). This is because I
  once learnt that you should always specify all conditions you know of
  to help the optimizer out. That was very long ago, and the rule may no
  longer apply. But I have included it here nevertheless.

SELECT @sql =                                                      -- 19
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,     -- 20
            c.CustomerID, c.CompanyName, c.Address, c.City,        -- 21
            c.Region,  c.PostalCode, c.Country, c.Phone,           -- 22
            p.ProductID, p.ProductName, p.UnitsInStock,            -- 23
            p.UnitsOnOrder                                         -- 24
     FROM   dbo.Orders o                                           -- 25
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 26
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 27
     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 28
     WHERE  1 = 1'                                                 -- 29
                                                                   -- 30
IF @orderid IS NOT NULL                                            -- 31
   SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +             -- 32
                        ' AND od.OrderID = @xorderid'              -- 33
                                                                   -- 34
--(snip)
                                                                   -- 46
IF @custid IS NOT NULL                                             -- 47
   SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +           -- 48
                        ' AND c.CustomerID = @xcustid'             -- 49
                                                                   -- 50
--(snip)
                                                                   -- 62
IF @prodid IS NOT NULL                                             -- 63
   SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +           -- 64
                        ' AND p.ProductID = @xprodid'              -- 65
                                                                   -- 66

Is that pattern of including the search parameter on both joined tables still correct in modern versions of SQL Server? (Most interested in SQL Server 2008 R2 SP2 and newer)


texts: Archive Logs filling up Flash Recovery Area (FRA) space

texts:
I got hired to fill a position of DBA that I wasn't quite ready to fill, and while the training is getting me there, I am running close to a potential issue.

My oracle 11g database's Flash Recovery Area (FRA) is getting very close to being full(2.5% left). It is in ARCHIVELOG mode since we do live backups(at least that is the reason I was told). We have cleared up all unneeded backups through the enterprise manager, but the Archive logs are still taking up over 50% of the allocated FRA space. Is it possible to delete some of the older archive logs to free up space? The server holds logs from several months ago still, and we don't really need backups that far back.

If I can delete them, what would be the best way to do this?


texts: MySQLRouter on Windows 10 Unable to connect to the metadata server

texts:
I have set up a set of three MySQL 8.0.17 databases using group replication. They are all on my Windows 10 PC, using ports 3306,3307,3308.

I am now trying to set up mysqlrouter. I have installed it in c:/mysqlrouter/.

But when I run:

mysqlrouter --bootstrap root@localhost:3306

I simply get:

Error: Unable to connect to the metadata server: Error connecting to MySQL server at 127.0.0.1:3306:
Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060 (2013)

This is the same on all three ports, and using each of the different users (localhost, 127.0.0.1 etc)

I have confirmed that the MySQL user exists (I have root @ localhost / 127.0.0.1 / ::1 / % all set up) and I can login to MySQL using each of them.

Is there something I need to configure? I can't see anything obvous in the documentation?


texts: Union from Multiple Tables in many databases and group results in batches of n

texts:
First post in dba, so please be gentle.

I have a project I am working through and have 4+ SQL Server databases to work with.

These DBs contain 2 (or more tables) I wish to UNION the results from.

The data I am interested in contains Windows Server file paths (SrcFilename, DestFilename for reference) and looks something like this:

  srcfilename001.dwg|\\srcServer1\path\to\file\|destfilename001.dwg|\\destserver\path\to\file\
  srcfilename002.dwg|\\srcServer2\path\to\file\|destfilename002.dwg|\\destserver\path\to\file\
  srcfilename003.dwg|\\srcServer3\path\to\file\|destfilename003.dwg|\\destserver\random\path\
  srcfilename004.dwg|\\srcServer4\path\to\file\|destfilename004.dwg|\\destserver\random\path\

I have been able to UNION the data using a query like this:

    SELECT * FROM (
SELECT * FROM [Database_015].[dbo].[CAD_DATA] Where [Database_015].[dbo].[CAD_DATA].[UniqueValue] = 1
UNION ALL
SELECT * FROM [Database_016].[dbo].[CAD_DATA] Where [Database_016].[dbo].[CAD_DATA].[UniqueValue] = 1
UNION ALL
SELECT * FROM [Database_017].[dbo].[CAD_DATA] Where [Database_017].[dbo].[CAD_DATA].[UniqueValue] = 1
UNION ALL
SELECT * FROM [Database_020].[dbo].[CAD_DATA] Where [Database_020].[dbo].[CAD_DATA].[UniqueValue] = 1
) A
ORDER BY NEWID()

Which works great...

I get the data I need in a random order rather than a bunch of files from \\server1, \\server2, \\server3 etc.

Obviously the randomness is because of the ORDER BY NEWID().

The reason for gathering the data is because we're migrating millions of files and ultimately need to hash both the source and destination files to see if they match.

Ultimately I will use Powershell to hash the files from the resultant data and I think that having each Powershell Start-ThreadJob 'Job' point at each server in turn for n files will be faster than having it work on a "stripe" of data across multiple servers.

Thanks for your time. :)

Edit: The first comment asked for more explanation so here goes:

I don't know how to combine both UNION ALL, GROUP BY and ORDER BY to produce a dataset from multiple different database tables that has n batches of files grouped by server in a random order.


texts:
We are getting the following error when trying to use BULK INSERT.

  Msg 4861, Level 16, State 1, Line 10 Cannot bulk load because the file
  "\filer\share\loadfile.txt" could not be opened. Operating system
  error code 5(Access is denied.).

When we check the security on the directory everyone is listed as having full access and in fact we have confirmed this by trying to open the file manually as myself, another DBA, one of the devs, and the service account running the instance.  We also get the error when attempting to run the BULK INSERT myself, the other DBA, the dev and a sysadmin SQL login I created to run the test.

The instance is SQL Server 2008 R2

The code is:

   CREATE TABLE #Temp1 (CodeNum VarChar(10), Cost Money)

   BULK INSERT #Temp1
   FROM '\\filer\share\loadfile.txt'
   WITH (
           FIELDTERMINATOR = ',',
           ROWTERMINATOR = ';\n'
           );

   GO

Can anyone give me some direction on where my security issue is?


texts: How to estimate the disk space for 1 million rows if I have a table of fewer rows in InnoDB MySQL

texts:
I want to estimate the disk space for 1 million rows.

Currently, I have 8527959 rows, Data Length of 744 MB and Index Length 989.4 MB.

Please let me know if my calculations are correct.

Total Data Length for 1 million rows

Data of 744MB consists of 46500 pages of 16KB blocks (InnoDB page size)

If 46500 pages consist of 8527959 rows, 1 page consists an average of 183 rows.

So, 1 million rows need (1,000,000/183) pages= 5465 pages of 16KB

So, 1 million rows of data need 87.4MB.

Total Index Length for 1 million rows

Indexes of of 989.4MB consists of 61837 pages of 16KB blocks (InnoDB page size)

If 61837 pages consist of 8527959 rows, 1 page consists an average of 138 rows.

So, 1 million rows need (1,000,000/138) pages= 7247 pages of 16KB

So, 1 million rows of data need 115.9MB.

Total disk space (approximate) for 1 million rows

Total data length+Total Index Length = 87.4MB + 115.9 MB = 203.3 MB

Is this correct?


texts: Airport Database Design guidance required

texts:
I am trying to make a generic database for airport management and DBMS would be oracle 11g xe. Since, i am amateur and still learning so i have tried to kept the problem statement simple with limited entities involved:

Airport serves a city but every city may not have an airport. An airline has office(ticketing booth) at airports they cover and hence an airport may have many airline offices. Airport accommodates airplanes and airplanes may or may not belong to a certain airline(considering private jets). Airports may have one runway or more than one. Runway has a name and length. Every airport has a manager. Manager can cross view the other data but he can alter data for airport under his belt only- this i think will be applied via front-end application using 'APEX'.

Here is the relational schema i have worked so far:

City (CityID{PK}, CityName, CountryName)

Airport (AirportID{PK},AirportName, ICAO, IATA,ManagerID{FK},CityID{FK})

AirportRunway (AirportID{FK}, RunwayID{FK}, RunwayName, length)

Airline (AirlineID{PK}, AirlineName, IATA, ICAO)

AirportAirlineOffice (AirlineID{FK}, AirportID{FK}) both foreign keys make a primary key.

Airplane (AirplaneID, Model, Make, AirlineID{FK})

Manager (ManagerID{PK}, username, password, ManagerName)

I am confused over manager relation considering what i am trying to implement. Do i need to make relationsship of manager with other entities as well? Do i need to make AirlineOffice as entity or it is good as it is. And what changes do i need to do so that i can convert this schema to 3rd normalized form!

edit: here is my ERD:


texts: Problem with partitioning non-clustered indexes

texts:
I have a database with partition function defined. Over this partition function there are 2 schemes defined, one for Data files and one for Indexes files.
Data and index files are divided quaterly (2017Q4, 2018Q1, 2018Q2, 2018Q3, 2018Q4, 2019Q1, 2019Q2, Future)

Lately I have noticed that my index file Future is growing when there should not be any data in it.

Using Kendra Little script:

Which Filegroup is that Partition Using? How Many Rows Does It Have?

I've checked how many rows are in each file/filegroup.

And up to 2018Q2 everything seems to be working fine.
There are the same number of rows in 2017Q4Data and 2017Q4Index file, 2018Q1Data and 2018Q1Index file, 2018Q2Data and 2018Q2Index file.

However starting with 2018Q3 - data files are filled up correctly, but all index data is going to FutureIndex file.

What could be the problem here?

CREATE PARTITION FUNCTION [PFEReceipt](int)
AS RANGE LEFT FOR VALUES (
520171231, 520180331, 520180630, 520180930, 520181231, 520190331, 520190630, 599999999)
GO

CREATE PARTITION SCHEME [PSEReceiptData] AS PARTITION [PFEReceipt] TO (
[FG_Data2017Q4], [FG_Data2018Q1], [FG_Data2018Q2], [FG_Data2018Q3], [FG_Data2018Q4], [FG_Data2019Q1], [FG_Data2019Q2], [FG_DataFuture],
[FG_UpperDataNone])
GO

CREATE PARTITION SCHEME [PSEReceiptIndex] AS PARTITION [PFEReceipt] TO (
[FG_Index2017Q4], [FG_Index2018Q1], [FG_Index2018Q2], [FG_Index2018Q3], [FG_Index2018Q4], [FG_Index2019Q1], [FG_Index2019Q2], [FG_IndexFuture],
[FG_UpperIndexNone])
GO


texts: How to group by continuous days in mysql

texts:
I have table where I am storing some sequential data that indicate occurrences of some event given day.

id     from_id        to_id   date
1      2              1       2014-08-01
2      2              1       2014-08-02

3      2              1       2014-08-04
4      2              1       2014-08-05
6      2              1       2014-08-06

Sometimes event hasn't got place. Then I would like to get min and max from every uninterrupted sequences of records by data. According to example data it should return

from_id        to_id   min            max
2              1       2014-08-01     2014-08-02
2              1       2014-08-04     2014-08-06

If I would do simple group by with min and max

Select from_id, to_id, min(date), max(date) from events group by from_id, to_id

it would return

from_id        to_id   min            max
2              1       2014-08-01     2014-08-06

How can I do that in MySQL?


texts: Return 2 lines from a table if one line contains a column > 0 and the other is NULL

texts:
How to return 2 lines even if one line is null but the other > 0

 item_id  | location    | quantity
-------------------------------------
    14    |    1        |    10
    14    |    2        |   <null>
    21    |    1        |   <null>
    21    |    2        |   <null>

The result should return:

 item_id  | location    | quantity
-------------------------------------
    14    |    1        |    10
    14    |    2        |   <null>

I need a query with a clause where quantity > 0. If quantity is null on location 1 and 2 do not return, if quantity > 0 in one location return both lines.
I have 10 locations (1 to 10) and I need to look quantity where location in (1,2).


texts: `cube` query performance issues with ordering

texts:
I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the
But I am having trouble with query performance. It takes ~20 seconds on this query (~5MM rows on a 32GB machine):

select id
from documents
where embedding <-> cube('(0.08470847,...,0.06106149)') < 0.25
order by embedding <-> cube('(0.08470847,...,0.06106149)') asc
limit 25

The same query without the order by performs within milliseconds.

I am not sure how to improve the ordering performance.

I ran explain analyze on the query and this is the result:

Limit  (cost=0.54..323.63 rows=25 width=12) (actual time=18032.104..18704.827 rows=25 loops=1)
  ->  Index Scan using ix_100 on documents  (cost=0.54..22895274.16 rows=1771566 width=12) (actual time=18032.101..18704.797 rows=25 loops=1)
        Order By: (embedding <-> '(0.084708469999999994,... , 0.061061490000000003)'::cube)
        Filter: ((embedding <-> '(0.084708469999999994,... , 0.061061490000000003)'::cube) < '0.25'::double precision)
Planning Time: 1.575 ms
Execution Time: 18728.073 ms

I am at a loss how to proceed from here, I wish to avoid sorting after fetching the results in the application layer and ideally should work within the database.

Any ideas?

Edit: adding the explain(analyze,buffers) for the query with limit

query:

explain (analyze, buffers)
select id
from documents
where embedding <-> cube('(0.08470847,..,0.06106149)') < 0.25
limit 10;

with this output:

Limit  (cost=0.00..7.73 rows=10 width=4) (actual time=0.036..0.076 rows=10 loops=1)
  Buffers: shared hit=5
  ->  Seq Scan on documents  (cost=0.00..1370989.16 rows=1772915 width=4) (actual time=0.034..0.072 rows=10 loops=1)
        Filter: ((embedding <-> '(0.084708469999999994..., 0.061061490000000003)'::cube) < '0.25'::double precision)
        Rows Removed by Filter: 10
        Buffers: shared hit=5
Planning Time: 0.107 ms
Execution Time: 0.098 ms

Edit -2 :

modified query per last update and results are back to ~20 secs per query

Limit  (cost=0.54..323.56 rows=25 width=12) (actual time=727.488..21603.571 rows=25 loops=1)
  Buffers: shared read=1352076
  ->  Index Scan using ix_100 on documents  (cost=0.54..22910761.65 rows=1773159 width=12) (actual time=727.485..21603.535 rows=25 loops=1)
        Order By: (embedding <-> '(0.0665496899999999947, ... 0.063358020000000001)'::cube)
        Filter: ((embedding <-> '(0.0665496899999999947, ... 0.063358020000000001)'::cube) < '0.25'::double precision)
        Buffers: shared read=1352076
Planning Time: 0.164 ms
Execution Time: 21644.516 ms


texts: Using Powershell Variables in a File-Stored Query with SQL Server

texts:
I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell script, which would make what I'm trying to do trivial. What I need is to be able to have SQL Server do is to access the Powershell variable and use it in the query I'm sending. xp_cmdshell is coming up as a potential option to do this with, but I'd sooner not expose myself to the security vulnerabilities there if I don't have to.

Here's some pseudocode to get my idea across easier:

PSScript.ps1:

$var = 1
$query = query.sql
$con = connection(server_connection_params)
$command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
$command.ExecuteScalar()

Query.sql:

SELECT * FROM table WHERE field = $var;

Is there any way I could access $var in this fashion given how I'm calling the SQL in Powershell?


texts:
I think it is my group by in this syntax that is skewing my desired result set.  I ONLY want to see the data I am requesting, however my query returns all the data like this:

salesman    Q1 2015 Q1 2016
Blue    NULL    NULL
Blue    NULL    NULL
Blue    200     NULL
Blue    300     NULL
Blue    NULL    400
Blue    NULL    600

What I (thought) my syntax would return would ONLY be the below

salesman    [Q1 2015]   [Q1 2016]
Blue          500          1000

This is my DDL (well garbage data, but gets point across)

Create Table Barnacles
(
  warrantypurchasedate date
  ,salesman varchar(500)
  ,itemprice float
)
Insert Into Barnacles (warrantypurchasedate, salesman, itemprice) Values
('01/01/2014', 'Blue', 100)
,('02/01/2014', 'Blue', 300)
,('01/01/2015', 'Blue', 200)
,('02/01/2015', 'Blue', 300)
,('01/01/2016', 'Blue', 400)
,('02/01/2016', 'Blue', 600)

This is the query syntax I attempted:

Select
salesman
,case when DATEPART(Year, warrantypurchasedate) = '2015' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2015]
,case when DATEPART(Year, warrantypurchasedate) = '2016' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2016]
FROM Barnacles
GROUP BY salesman, warrantypurchasedate

What should I alter in my query so that I only see the SUM() for Q1 15 and the SUM() for Q1 16 not a row returned for each entry in the table?


texts:
I'm trying to get the pages used by a clustered index on table1 in a SQL2008 R2 DEV box using the below query.

dbcc traceon(3604, -1)
go
dbcc ind (0, 'dbo.table1', 1)
go

but it still displays the below output and doesn't display the results.What am i doing wrong here?

Also the below message is logged in the SQL server log

  Message DBCC TRACEON 3604, server process ID (SPID) 59. This is an
  informational message only; no user action is required.


texts:
I have to create some scripts to create a new and empty DB ready for a new install of our apps on a new server.

I am easily getting the tables, views etc info from SQL Developers Export facility (without the data of course), but I have a problem where I need to define the userid and the TableSpace.

I realise these are quite straight forward, but what I would like to know is how the original ones are defined on our current DB, as the original scripts (and info) are no longer available, and I need to make sure I create them correctly with the new scripts.

So how do I display all the required info for a userid and for a TableSpace so that I can use this info in the create commands in the scripts ?

It may be helpful for you to know this is my first attempt at scripting and only my 2nd attempt at and interfacing with Oracle, so I have limited knowledge in this field, hence the simple request. :)

Any help or advice would be most appreciated.

Cheers, George.


texts: Writing SQL query equivalent to relational division of relational algebra?

texts:
These are the list of 3 tables

EMPLOYEE (Ssn#, Fname, Lname, Bdate, Address, Sex, Salary, Super_ssn)

WORKS_ON (Essn#, Pno, Hours)

PROJECT (Pnumber#, Pname, Plocation)

Question 1: Write SQL query (equivalent to relational division of relational algebra) to retrieve the project name which is contributed by all employees

Question 2: Write SQL query (equivalent to relational division of relational algebra) to retrieve the first name and last name of all employees who work on every project

Then there are list of 6 tables

ITEM (Item#, Item_Name, Unit_Price)

ORDER (Order#, Ord_Date, Cust#, Ord_Amt)

ORDER_ITEM (Order#, Item#, Qty)

WAREHOUSE (Warehouse#, Warehouse_City)

SHIPMENT (Order#, Warehouse#, Ship_Date)

WAREHOUSE_ITEM (Item#, Warehouse#, Qty)

Question 3: Write SQL query (equivalent to relational division of relational algebra) to list Item_Name and Quantity that are stored in all warehouses in Sydney

All these 3 questions are assignment questions.
However, I have tried and answered them and which is shown below:

Answer for Question 1:

SELECT Pname
FROM PROJECT
WHERE EXISTS (SELECT Pname
              FROM PROJECT, EMPLOYEE
              WHERE PROJECT.Pnumber = WORKS_ON.Pno);

Answer for Question 2:

SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS (SELECT Fname, Lname
                            FROM EMPLOYEE, WORKS_ON, PROJECT
                            WHERE EMPLOYEE.Ssn = WORKS_ON.Essn
                              AND WORKS_ON.Pno = PROJECT.Pnumber);

Answer for Question 3:

SELECT Item_Name, Qty
FROM ITEM, ORDER_ITEM
WHERE ITEM.Item# = ORDER_ITEM.Item#
  AND EXISTS(SELECT *
             FROM WAREHOUSE
             WHERE Warehouse_City = 'Sydney');

I just want to confirm that if my SQL query logic for these 3 questions are correct or not.

If it is correct, then I will able to proceed and convert them in relational algebra with relational division which I can do it by myself if my SQL query logic is correct.

If it is not correct, then kindly correct the mistakes in my SQL query logic wherever applicable which would be very helpful for me.


texts: SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value

texts:
Please help as this issue is driving me nuts...

Tempdb has four data files & one log file. They are all located on the same dedicated drive.

When I right click on tempdb in SQL Server Management Studio (SSMS) to view Properties --> General...I'm seeing "Size" as ~65000 MB with "Space Available" as ~64000 MB. However, when I try to shrink the files via the GUI, I see that the "currently allocated space" hasn't changed & is still showing the initial size.

This is also where the "Available free space" is shown as a negative number. The only work around I have found so far is to restart SQL Server to flush out tempdb, but I can't keep on doing this.

Has anyone ever come across this & know the root cause/permanent solution for it please?

Thanks in advance!


texts:
Use this ansible playbook to install PostgreSQL and create a user:

---
- name: Download PostgreSQL 10
  get_url:
    url: https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    dest: /usr/local/src/pgdg-redhat-repo-latest.noarch.rpm

- name: Install PostgreSQL 10 rpm
  yum:
    name: /usr/local/src/pgdg-redhat-repo-latest.noarch.rpm
    state: present

- name: Install epel-release rpm
  yum:
    name: epel-release
    state: present

- name: Install PostgreSQL 10
  yum:
    name: ['postgresql10-server', 'postgresql10']
    state: present
    update_cache: yes

- name: Update .bash_profile
  copy:
    src: bash_profile
    dest: /var/lib/pgsql/.bash_profile
    owner: postgres

- name: Source bash_profile
  shell: source /var/lib/pgsql/.bash_profile
  become: true

- name: Initialize the Database
  command: /usr/pgsql-10/bin/postgresql-10-setup initdb
  become: true
  ignore_errors: yes

- name: Set postgresql.conf
  copy:
    src: postgresql.conf
    dest: /var/lib/pgsql/10/data/postgresql.conf
    owner: postgres

- name: Set pg_hba.conf
  copy:
    src: pg_hba.conf
    dest: /var/lib/pgsql/10/data/pg_hba.conf
    owner: postgres

- name: Install repmgr repository
  get_url:
    url: https://dl.2ndquadrant.com/default/release/get/10/rpm
    dest: /var/lib/pgsql/repmgr

- name: Install repmgr rpm
  shell: bash /var/lib/pgsql/repmgr

- name: Install repmgr
  yum:
    name: repmgr10
    state: present

- name: Enable and Start PostgreSQL
  systemd:
    name: postgresql-10
    state: started
    enabled: yes

- name: Create repmgr user
  command: createuser -s repmgr
  become: true
  become_user: postgres
  ignore_errors: yes

It pending for a longtime, no response. What's the reason?

Even I login with psql try to create user by:

CREATE USER testuser;

The same it got pending without any response.


texts: Updating column in underlying table causes massive transaction log growth

texts:
I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry on the clustered index when associated with a large amount of records (~2.5 million in this case), the indexed view which in turn creates a massive entry in the transaction log. I'm wondering what I could try to avoid this massive entry into the transaction log, without disabling the index on the view? I know the field that is being updated is not a part of any index, it is just part of this view. My google-fu is failing me at present, so wanted to get this up while I continue searching to see if anything pops up here.

CREATE TABLE Big ( Id INT IDENTITY, SmallId VARCHAR(3) NOT NULL ); -- 11 million rows, 2.5 million where SmallId = 'xxx'
CREATE TABLE Small ( Id VARCHAR(3) NOT NULL, [Description] VARCHAR(128) NOT NULL ); -- 12k rows
CREATE VIEW [View] WITH SCHEMABINDING AS
    SELECT Id, b.SmallId, s.Description AS SmallDescription
    FROM Big b
    INNER JOIN Small s
        ON b.SmallId = s.Id;
CREATE CLUSTERED INDEX [PK_View] ON [View]([Id]);
UPDATE Small SET [Description] = 'x' WHERE Id = 'xxx'; --- massive amount of transaction log entries for PK_View


texts: Is it ok to create many logins in SQL Server

texts:
I created a register module for my web app.
The user that have access on the web app should also be able to access the data in autocad using fdo and input their credentials. To do that, I also create a login and user for SQL Server.
The web app will have many users in the future.
Is there a problem creating many login in SQL server?
The application will be use by different users. Already attached role to login. But every user have different login.


texts: Understanding two types of mongodb log entries for bulk inserts

texts:
I have an app which does regular (every 2 minutes) bulk inserts to two identical instances of 2.6.4 mongodb on two servers. Both the instances are hosted on 2 Core, 14GB machine and have only one collection with 6 million documents with 7 GB of total index size. There's a text index and a TTL index along with 2 other sparse indices. Journaling is off on both instances.
I have noticed a strange behaviour with bulk inserts. Sometimes, the bulk inserts (20K new documents) happens in less than 10 seconds. When this happens, the entries in the log files having following pattern:

2

2014-09-12T22:06:38.656+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:9 reslen:40 309ms
2014-09-12T22:06:38.984+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:8 reslen:40 269ms
2014-09-12T22:06:39.452+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:11 reslen:40 398ms
2014-09-12T22:06:39.922+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:9 reslen:40 394ms
2014-09-12T22:06:40.377+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:10 reslen:40 389ms
2014-09-12T22:06:40.834+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:8 reslen:40 375ms
2014-09-12T22:06:41.299+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:8 reslen:40 394ms
2014-09-12T22:06:41.438+0000 [conn57] command day_raw_tweet_db.$cmd command: insert { $msg: "query not recording (too large)" } keyUpdates:0 numYields:0 locks(micros) w:8 reslen:40 112ms

As mentioned when log entries are as above the inserts are super fast.
However, in a different scenario (and this happens automatically without any significant change in payload on either instances) the log entries show each document getting inserted

2014-09-12T22:11:29.450+0000 [conn156] insert week_raw_tweet_db.tweets query: { _id: "510498671730110464" ...
In this case the inserts are super slow.
The worst part is that this switching between both these modes (fast vs. slow) happens automatically and quite intermittently for no apparent change in workload on mongo instance.

I need urgent help on this issue.

I've tried to direct some instructors of official mongodb courses to this thread. Hope they or anyone else has an explanation and some mechanism to solve this problem.


texts: postgresql + pgbouncer, idle server and high cl_waiting

texts:
I have such a setup:

OpenSUSE 13.1 64-bit
Linux kernel 3.11.10
PostgreSQL 9.0 + PgBouncer 1.5.4 (libevent-2.0)
16 core AMD, 128 GB RAM

PostgreSQL config:

max_connections = 1500
shared_buffers = 4GB
work_mem = 20MB

PgBouncer config:

max_client_conn = 15000
default_pool_size = 275

I try to saturate the server using pgbouncer.

I was running a Select-only test, with 1000 clients for 5 minutes.
(pgbench -p 6433 -n -S  -c 1000 -T 300). PgBouncer was initializet with scale=100 (but a SELECT-only test should not suffer on it).

During the test:

pgbench takes 70% of one CPU core and pgbouncer takes 30% of another core. Total core usage is 8-10%
SHOW POOLS tells that cl_active is only about 50,
and all the rest is cl_waiting.
number of non-idle server queries (according to pg_stat_activity) is very low (2-3).
server load is only about 2.5

So it looks like pgbouncer cannot spawn enough parallel server sessions?

I'm not sure how that can happen while it looks that no basic resources are
saturated. (CPU, mem, # of connections)

I also tried with 5000 connections (using 10 parallel pgbenches) -
same effect. Only about 50 cl_active.

When I run pgbench connecting directly to postgres (port 5432) - it spawns hundreds of sessions without problem.

I'd be grateful for any hints. Can this be pgbouncer, postgres or kernel parameters issue?


texts: Database/cloud solution for very wide time series data (i.e. spectra with up to 8196 columns)?

texts:
I'm dealing with data from a science project which comes from a multi-channel analyzer: once per minute I get 2048 integer values from one device and 8192 integer values from another device. Both devices operate continuously 24/7.

I want to store each of these vectors with a timestamp in a dedicated table which I can later query to select subsets of time ranges and/or columns (which represent energy ranges).

So far I'm using CSV files to store the data. I have one file per device per day and add a row each time I get a new vector. When I analyze such data, I read the relevant CSV files in as data frames in R or Python and merge them into one large data frame. Now I'm looking for a better/faster solution.

Any recommendations on what kind of database or service I could use for this kind of very wide (but not very long) time series data?


texts:
I had a deadlock issue which was easy to fix by changing application logic, but I was under impression Oracle would behave differently, and the deadlock would never happen. I have two tables, for example

table1( table1_id (PK), num1, num2);
table2(table2_id(PK), table1_id(FK to table1,indexed), low_cardinality_column, num3, num4);

Table2 also has row level after update trigger which updates table1.num (table1.num1 = table1.num1 + :NEW.num3 where table1.table1_id = :NEW.table1_id).

First process executes UPDATE table2 set num3 =1 where low_cardinality_column =:bind_var (no index on low_cardinality_column , typically couple thousands records affected).
Second process updates table2 and table1 in one transaction with

UPDATE table2
   SET num4 = :bind_var4
WHERE table2_id = :bind_var_id
RETURNING table1_id INTO :out_var

UPDATE table1
  SET num2 = :new_num2_val
WHERE table1_id = :out_var

Trace showed quite a few deadlocks between those 2 processes if they happen to run at the same time, and I'm a bit confused by that. I understand deadlocks must have happened in case second process updated tables in reverse order (table1, then table2), but in this particular case I thought the engine won't actually begin update until it gets RX lock on each record which has to be updated (thus one process would wait for another to finish) . If it's not guaranteed, then explanation of the deadlock is obvious : Process1 locks records in undefined order, and  Process 2 happened to update the row in table2 not yet locked and tries to update the row in table1 already updated by Process 1. In case of SQLServer I would be 100% sure that's the case, but I'm still quite new to Oracle...

I wonder if someone could clarify the issue. Thank you.
I use 10g if that matters.


texts: Modeling discounts - Product specific and Customer specific discounts

texts:
I have a reasonably simple database but have the following problem:

I'm modeling a store + a lot of non-relevant stuff database. I have the following problem. I want to allow for specific products to be discounted, but also provide discounts to all purchases by a specific customer (I have a table that holds customers). Now when I think of it, I might even want for specific stores to have discounts or maybe discount specific products to specific customers or any combination of these.

Normally, the sane way to approach this would be to create a table with sales, and maybe another one with sale types. But I really can't wrap my head around it. How would it actually look like? Any help?

I think that my actual DB model isn't relevant, but I'll update the post with it if it's needed.


texts: SQL Server Instance How to recover memory used

texts:
I know that may be this question has been answered a lot of times, but I need a better way to solve it or to demonstrate to my boss that this is a normal behaviour or there is something that I'm overlooking.
I'm not a DBA, I'm a developer, and like my previous job, I've to take the DBA Mantle because we don't have a formal DBA on site or no one want's to take this thing seriously.
I have three instances of SQL Server, one with SQL 2008 R2 and two with SQL 2016. All are from production environment.
The SQL 2008 R2 is from our main System, it is hosted in a Virtual Server in Hyper-V and is a Dedicated Database Server. No other apps are allocated on that server, it is a pure database server.
The issue is that the server is configured with 30 GB of RAM, and SQL Server is configured to use from 4 GB minimum to 22 GB maximum.

If I do a restart, obviously the server memory usage goes to a flat line at server performance manager, releasing memory from memory used to the minimum configured.

I need help understanding this situation, since all documentation point to an irrefutable fact that this is normal in SQL Server, that I've already documented myself about it, but my boss and colleagues state that this is not normal behaviour for the database. If I do numbers I think the server is requesting more memory. They say this is not a normal behaviour of SQL Server and that there is something we are overlooking (Jobs, and other stuff). To monitor or try to determine the Server Behaviour, I started sending Database Health Report every two hours, to try to check what's going on. For them, the expected behaviour is that SQL Server RAM should rise up and down in the performance graph and not stay at the top all the time, and as I understand this is the expected behaviour or SQL Server by design.

But in different points in time, jobs rise to execute, and they finish normally, some time long running transactions are running, but when I run queries for them after the report is generated, they are gone.
Is there something I'm overlooking? Is this Normal?  Is there a way to release memory without rebooting the instance?  I've already tried a bunch of solutions


texts: Trigger to prevent insertion of duplicate data

texts:
I have a table named dbo.Inventory in SQL Server 2016 like this:

ProdCode     UPCCode
   1          11111
   2          11112
   3          11113

When adding a new row, I need to write a trigger that will check for a duplicate in that column. If it is a duplicate I need it to give an error and not change the column. Otherwise, I need it to insert like normal.

I can't use a unique index because I have a lot of rows with a value of NULL.


texts:
I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, each in a transaction.

Because there may be clashes, I need to make a decision for each row:

INSERT INTO my_table (a, b, c)
values ('a', 'b', 3)
ON DUPLICATE KEY UPDATE
a = IF(c<VALUES(c), a, VALUES(a)),
b = IF(c<VALUES(c), b, VALUES(b)),
c = IF(c<VALUES(c), c, VALUES(c));

If I run a few (e.g. 10) parallel insertion threads, is this a good idea? Will it cause me deadlocks due to contention on the index?