texts: What are the best ways to thrash SQL Server?

texts:
I have an issue where a software driven replication system will fail to fully replicate when the main DB is under heavy load. For example if we execute a sproc 500 times, maybe 450 will actually stick. BUT only when we are under crazy DB load (one example is the SAN admins updating firmware during business hours). I'm concerned creating transactions for each message, and waiting for them to timeout, and rollback as there is the potential for the destination SQL server to be on WAN in another datacenter.

Inquiry: I want to thrash my DEV SQL server so hard to replicate massive locking/cxpacket waits in development. But not enough to totally deadlock all access for hours, just maybe to simualate packet loss.

Scenario:
Server B (utility machine) has a local SQL Express instance. It is used for transient data.
It fires off a series of sprocs against the destination database to insert new rows, and then insert new rows into child tables linked by PK's. So on and so on.

Usually it works just dandy. Except the production DBA's don't let the subject matter experts on this system have any control, or visibility into lock stats. I have full control and can profile the system that is propagating data to the destination, but no analysis as to what happens at the other end, other than waiting.. waiting.. waiting... Am I locked? I dunno!

I want to simulate this in the lab and try to tune the sprocs that are used. I'm thinking of a heartbeat update anx xp_cmdshell update to a bookkeeping table and also using ping analysis using xp_cmdshell to query /refine/tune transactions, and rollback transactions if necessary, in the event that all are not committed. Then try again, maybe on another distributed host.

Problem:
The last time this happened was due to the fact that TEMPDB was HUGE (massive temp tables being utilized) and had to page. DBA's said there was nothing wrong, but also reported disk queue lengths obove 50+ for hours at a time. Gotta love it.


texts: Can an Amazon RDS SQL Server link to an RDS MySQL server?

texts:
Amazon RDS for SQL Server supports linked servers, but the doc page only describes linking to other SQL Servers.  I would like to link to an RDS MySQL instance, but this normally involves installing the relevant ODBC driver and/or creating a DSN - and I believe both are impossible on RDS.

Is there any way to create a linked server on an RDS SQL Server, which points to an RDS MySQL server?  I'll be a little surprised if this is really not available; presumably a fix would work for Aurora as well as MySQL, and encourage adoption of Amazon's preferred platform.


texts: Importing CSV into multiple tables using PostgreSQL

texts:
I have a very big CSV file with the following format:

TAG,TIME,VALUE

as an example row:

footag,2014-06-25 08:00:00.0,3400.0

I used to import it easily inside PostgreSQL 9.3 using the following:
COPY datapoints FROM '/home/foo/my.csv' DELIMITER ',' CSV; where datapoints is a table with the three corresponding columns.

Now, the database into which I need to import has changed. It has two tables. Here are \d+ outputs:

                                          Table "public.tags"
 Column |       Type        |                     Modifiers                     | Storage  | Stats target | Description
--------+-------------------+---------------------------------------------------+----------+--------------+-------------
 id     | integer           | not null default nextval('tags_id_seq'::regclass) | plain    |              |
 tag    | character varying | not null                                          | extended |              |
Indexes:
    "tags_pkey" PRIMARY KEY, btree (id)
    "tags_tag_idx" btree (tag)
Referenced by:
    TABLE "tag_values" CONSTRAINT "tag_values_tag_fkey" FOREIGN KEY (tag_id) REFERENCES tags(id)
Has OIDs: no

                                Table "public.tag_values"
 Column |            Type             | Modifiers | Storage | Stats target | Description
--------+-----------------------------+-----------+---------+--------------+-------------
 tag_id | integer                     | not null  | plain   |              |
 time   | timestamp without time zone | not null  | plain   |              |
 value  | double precision            |           | plain   |              |
Indexes:
    "tag_values_tag_time_idx" btree (tag_id, "time")
Foreign-key constraints:
    "tag_values_tag_fkey" FOREIGN KEY (tag_id) REFERENCES tags(id)
Has OIDs: no

So essentially, the original table is split into two. As you see, there's a foreign key constraint now (between tags(id) and tag_valued(tag_id)) Also, tags(tag) are supposed to be distinct. What is the easiest way to ingest the same .CSV files into such structure?

Thanks!


texts: Locking on database from SELECT INTO #TempTable

texts:
We had an issue on our live DB, where a query executed from the test instance, that referenced the live instance caused blocks due to it showing as 'suspended', which caused user requests to back up, all due to this one query.

Looking at the query, there is one request at the start that references the live DB:

SELECT      [Columns]
INTO        #TempTable
FROM        [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Users                      AS  sv
RIGHT JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company                    AS  co
INNER JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company_Marketing          AS  cm  ON co.co_ref        = cm.cmk_co_ref
INNER JOIN  [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Property                   AS  pr  ON co.co_pr_ref     = pr.pr_ref
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Company_Types              AS  ct  ON co.co_cot_id     = ct.cot_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Users                      AS  cb  ON cm.cmk_called_by = cb.us_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].Marketing_Disciplines      AS  md  ON cm.cmk_mdi_id    = md.mdi_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].REGIONS                    AS  rg  ON sv.us_reg_id     = rg.reg_id
LEFT JOIN   [LIVESERVER\LIVEINSTANCE].[LIVEDB].[dbo].REGIONS                    AS  rg1 ON CB.us_reg_id     = rg1.reg_id
WHERE       cm.cmk_appointment > '2017-01-01'
AND         md.mdi_mdg_id <> 2
ORDER BY    cm.cmk_appointment

This was being run against a test instance, but referencing live (there is a linked server). Changing it to run on the correct live instance means that it ran in a matter of seconds, and no blocking issues.

We are tidying up the query, as it is far from efficient anyway, but, what I am wanting to understand is, how does SELECT INTO behave in terms of blocks/locks? Would the query above mean that blocks are present on the tables in the query whilst #TempTable is populated?

Thanks for your assistance.


texts: Is it possible to reduce a group to a row based on some criteria?

texts:
I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows based on some criteria. For example, the maximum date_added. However, there are other fields of data that could be different among the grouped rows. I want all of those columns to resolve to the row with the max date_added as well.

I realize to get the max date_added I could simply SELECT MAX(date_added), but that is just a column-level aggregate function. Is there any way I can resolve the entire row in a group?

Conceptually, if you imagine each group as a separate table, I want to SELECT * WHERE date_added=(SELECT MAX(date_added)) from that group table.


texts: Why has mongorestore stopped recognizing the --ssl option

texts:
I've been using a mongorestore command to move data into our production db. But it's recently stopped working. The command is

mongorestore --db import --collection intent --host Cluster0-shard-0/cluster0-shard-00-00-wr3bj.mongodb.net:27017,cluster0-shard-00-01-wr3bj.mongodb.net:27017,cluster0-shard-00-02-wr3bj.mongodb.net:27017 --username usr --password pswd --ssl --authenticationDatabase admin intent.bson

And I'm getting a strange message saying

error parsing command line options: unknown option "ssl"

This doesn't make much sense as the option is listed in the docs

I think I may have upgraded from 3.2 to 3.4 since I last ran the command.

I'm running mongo on OS X and I installed using the instructions here (with curl not brew).

Ewen-Macbook:~ aidan$ mongorestore --version
mongorestore version: r3.4.2
git version: 3f76e40c105fc223b3e5aac3e20dcd026b83b38b
Go version: go1.7
   os: darwin
   arch: amd64
   compiler: gc

What am I missing?


texts: Netezza Error Record size 120000 exceeds internal limit of 65535 bytes

texts:
I have a column PARAM that has JSON data that looks like this:

JSON:{"Name":"RES_InformationalIssues","StepName":"Page_1","StepData":"f601e9fe-d68a-4ed7-bbaa-f40c9cbc4d12=General Product Information","SessionId":"ab04f2c4-ddge-4555-c792-01d9518e7148"}

I would like to have multiple columns (Name, StepName, StepData, SessionID) with their data in them.

SELECT substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21) as Name, substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) as StepName FROM Table

That query won't work exactly right since I have changed the names but I think you get the picture. When I run the query for either of these columns I get the data I am looking for. But when they are run together as I have above, I get the error:

ERROR [HY000] ERROR:  Record size 120000 exceeds internal limit of 65535 bytes

It feels like this means that I shouldn't assign the text of PARAM as the column name but that isn't what I am trying to do. Any suggestions or clarifications needed?


texts:
I have a table called users with a column firstname and with collation utf8_bin

I want to know what happens under the hood when I execute an query like:

SELECT * FROM `users` `a` WHERE `a`.`firstname` = 'sander' COLLATE utf8_general_ci

The column firstname isn't an index, what happens with the performance when the command executed?

And what if the default collation was utf8_general_ci and the query is executed without COLLATE?

I want to know the impact it has on a big table.


texts: Use or include external schema in search_path on Redshift

texts:
Narrative

I have a sql script that creates a bunch of tables in a temporary schema name in Redshift. I don't want to repeat the schema name a bunch of times, so I would like to do something like the following at the top of the script:

use long_external_schema_name;

My understanding is that Redshift (inheriting from Postgres), you would do:

set search_path to '$user', public, long_external_schema_name;

However, I get the following error:

ERROR:  External schema "long_external_schema_name" cannot be set in search_path

Because, it is an external schema.

Question

Is there any equivalent way that I could stay DRY and write the external schema name only once while I create a bunch of tables in it?

More Context

Note, I know I have lots of options in bash (arguments, sed, vim, etc) to replace the schema name in the script, but I'm trying to do something more native to Redshift / psql.


texts:
We are planning to run the following space management command:

alter table XXX shrink space cascade

on a production database (Oracle 10g). The XXX table is a transactional table heavily used  for updates/inserts/deletes by many applications.

I am wondering if running the shrink command during the peak time (online) will cause a severe performance degradation, or may lock the table and so block all the applications?

Can you clarify the impact of the shrink command on the access to the databese table XXX?


texts: pgAdmin - "cannot open logfile!" (WINDOWS 10; Postgres 9.4)

texts:
Our Windows 10 clients have both PostgreSQL 9.4 and pgAdmin3 (v1.22) installed.
I keep receiving the FATAL: "Cannot open the logfile!" error by invoking pgAdmin3 in either
of the following EXE locations.

C:\Program Files(x86)\pgAdmin III\1.22\pgAdmin3.exe
C:\Program Files\PostgreSQL\9.4\bin\pgAdmin3.exe

The problem is that when pgAdmin3 starts the pgAdmin splash screen will not dismiss and you cannot gain
access to configure the postgresql.conf file.  I'm assuming the log file its looking for is pgAdmin.log but if I navigate to C:\Users\<user_id>\Documents\postgresql I cannot find such a log file.  I tied to create an empty pgAdmin.log file whith not luck of gaining proper entry into pgAdmin3.


texts: Difference ReadOnly connections through sqlcmd vs SSMS

texts:
I configured my HA SQL Server to have a readonly.  When I entered the following command

sqlcmd -S servername -E -d db_name -K ReadOnly

It points correctly to the secondary server.

Then in SSMS I have my applicationintent=ReadOnly. However, when I run a trace all the queries point to the listener instead of the secondary HA server.

Is this a normal behaviour or am I missing something in my readonly configuration?


texts: Connect to sql plus from command line using connection string

texts:
Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c.

So how do I need to connect correctly? I used many options like:

sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))'

sqlplus (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=b)))

Submitting these command lines usually gives me back error messages like:

  ORA-12560: TNS:protocol adapter error
  ORA-12514: TNS:listener does not currently know of service

Many thanks in advance!!


texts: MongoDB - temporarily turn shard to standalone for local user creation

texts:
Following question Mongodb sharded cluster backup user issue
I have a sharded mongodb cluster, with 4 shards, and I'm required to stop the mongod service on each shard, and start it not as a part of a shared cluster, but as a standalone with no authentication.

Today, each shard is configured with the options:

shardsvr=true
keyFile=/etc/mongodb.key

If i comment out these 2 options, will I be able to start the service as standalone with no auth?

Moreover, is there any possible impact on the data/configuration once i finish what I need to do and uncomment the both configuration options?

Thanks in advance


texts: Rename a schema that contains an extension used by another schema

texts:
How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)?

I'm trying to temporarily rename a schema "dest" to "public" in order to save a backup with the name "dest. In order to do this:

Rename public to public_old
Rename dest to public
pg_dump ...

However, step 1 causes the geometry type in public to become public_old.geometry:

\d dest.geo_choice
                                       Table "dest.geo_choice"
           Column           |   Type   |                          Modifiers
----------------------------+----------+--------------------------------------------------------------
 geom                       | geometry |

ALTER SCHEMA public RENAME TO public_old;

                                             Table "dest.geo_choice"
           Column           |        Type         |                          Modifiers
----------------------------+---------------------+--------------------------------------------------------------
 geom                       | public_old.geometry |

select GeometryType(geom) from dest.geo_choice;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How can I avoid this breakage so we can carry out pg_dump?


texts: SQL Server 2005 Whole-Migration, which databases should I recreate?

texts:
In relation to my question about SQL Server 2005 migration procedure, I am re-creating the databases on the new server.

I noted that the old server has the following databases (outside of System Databases):

 1. Analysis Server Repository
 2. Archive
 3. BCI
 4. BDD Data Center
 5. Nemo
 6. Fixing
 7. Neutron
 8. ReportServer
 9. ReportServerTempDB
10. Sharepoint
11. STS_ho_whdata_1
12. TVS

Now, some of the databases are clearly custom-made, e.g., #5, #6, #7, #11, etc. However there are some databases that -- from their name -- doesn't seem like databases created by the (previous) dev/maintainer, e.g., #1 and #8.

Is it safe for me to re-create all databases willy-nilly, and just 'copy' the data from the old server to the new server?


texts: Create a Database locally and run it on a cluster?

texts:
I have a very basic question. I have to create a database locally on my computer, that includes tables, stored procedures and so on...
I am confused as to how I would place this database on a cluster.
The database is a part of a distributed messaging system, it will run on 1 machine, and the client on another. The client stores message in the database and other clients can retrieve the message. I am using command line to create tables write procedures and all.
I have created tables and everything but I don't know how I would run it on another machine.
I am using PostgreSQL on OS X along with Java.
Any help would be appreciated, I googled but I dint find what I needed.

If you think my question is not clear enough leave a comment.


texts: If user owns a database fixed role are they the admin of the role?

texts:
I have SQL Server 2012. When I create a user in "Owned schemas" screen one of the option is fixed database role. If I own a fixed database role:

What permissions am I granted?
I am the admin of this role?


texts: Numerical data with lots of NULLs: 6NF or document database?

texts:
I have a source of numerical data mostly comprised of sensor readings, physical quantities and flags. Each associated with a datetime.

|  id  |        timetag           |   sensor1   |      sensor2     |  flag1  |  ...  |
--------------------------------------------------------------------------------------
| 943  | 2016-12-08 10:27:00.000  |  1.2323523  |  -23123.5346234  |    0    |  ...  |
| 944  | 2016-12-08 10:27:00.038  |     NULL    |     163.6151345  |    1    |  ...  |
| 945  | 2016-12-08 10:27:02.000  |  1.2477801  |       NULL       |    3    |  ...  |
                                       ...

The data has the following characteristics:

There are hundreds of fields, and new unexpected fields may pop up at any time. We need to record those too.
For every row, only a small subset (<10%) of the fields are available.
Sometimes a field is available but corrupted, i.e. NaN, and we need to record that.
Data is written once, updated almost never and read relatively often.
We need to do range queries on the data, both on the date and on the actual numbers.
We don't want to lose any data.
It doesn't need to be a distributed system.

I'm wondering what the best way to store these data would be.

Solution 1: One big table

A single huge table would be a bad idea, because it would be full of NULLS and inconvenient. Moreover, it would be impossible to distinguish between "recorded but NaN" NULLs and "not recorded" NULLs.

Solution 2: 6th normal form

The first solution that comes to mind is to use 6NF, creating one table for each field.

This is clean in theory, but

queries would require many many JOINs at the same time;
new tables would have to be created on the fly when a new field arrives for the first time;
fields that are always paired, like for example longitude and latitude would have to be automatically detected and their tables merged for efficiency;
if at some point one of the columns in a "merged" table (e.g. lon in the lon-lat example above) starts to be obtained independently (without lat), to keep the table in 6NF we would have to detect that and automatically split the table into separate tables.

The above makes for a rather complex solution.

Solution 3: Document database

I've been considering PostgreSQL with JSONB to mimic a document database.
This would allow us to only store the data we have in each row, but still have a single table.

However, as detailed in my (unanswered) question on Stack Overflow, JSONB doesn't seem appropriate for the numeric operations we need, like range queries.
On the other hand, NoSQL technologies like MongoDB, in which I have no experience, don't guarantee the consistency we are looking for.

Question

Of the solutions above, which would you favor? Are there any other options I'm missing?


texts: SSIS File System Task Copy File Could not find part of the path error

texts:
I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package into our SQL Server and Create a Job to run the Package, the package fails and I see the following error in the log:

Started:  3:20:01 PM
Error: 2013-03-27 15:20:01.70
Code: 0xC002F304
Source: copy template File System Task
Description:
An error occurred with the following error message:
    "Could not find a part of the path
    'P:\IT\PROVIDERS\PROVIDERS AUDIT\File
    Template\QnxtProviderAuditList_yyyymmdd.xlsx'."
End Error
DTExec:
The package execution returned DTSER_FAILURE (1).
Started:  3:20:01 PM
Finished: 3:20:01 PM
Elapsed:  0.452 seconds.
The package execution failed.  The step failed.

The file path in the error is the file that needs to be copied (sourceconnection).

I can copy the file path directly from the error and go to it (via windows explorer) on the physical SQL Server machine so I do not understand why the package is saying it could not find the Path.

Does anyone have any idea why this package would run fine on my local machine but fails with the above error when run from the SQL Server? P:\ is a shared drive which the SQL Server has access to so I doubt it's an access/privileges issue.


texts:
I am under a really specific scenario. I have a database on a customer's site and a website on Azure cloud that connects to that database.

User is on a 50mbit fiber but this is obviously slow. So I am thinking of implementing replication with SQL Server on the local Azure server.

Is there a way of implementing the reads from the replica but the writes on the main database (I am thinking about this so that I don't have to deal with merges and I don't really care about the write speed)? Moving everything to cloud is not an option as on site there are a lot of clients that are already connected.


texts: Syntax error, Hello World example. Begin ... End

texts:
I borrow an example hello world function for mysql, but it throws me an error, I don't know where is the syntax error in my query.

DROP FUNCTION IF EXISTS helloWorld;

CREATE FUNCTION helloWorld( s char(20))
RETURNS VARCHAR(50)
BEGIN
    RETURN CONCAT("Hello ", s);
END;

SELECT helloWorld("Albert");

MYSQL Version: 5.7.21

  ERROR: [ERROR en la consulta 2] 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 '' at line 4

If I do the query without BEGIN ... END It works, but I can't do the query without more than 1 line of body.


texts: How to get the row number where its ID is 'n' and rows are ordered by some column?

texts:
I'm trying to get the row number, knowing the row ID, smomething like this:

Get the row number (position) from Table1 order by Col1, where this row ID is 263

 ID |     Col0 |  v Col1 |    Col2
----|----------|---------|---------
258 |        R |       a |       7
262 |        A |       a |       8
277 |        C |       a |      29
263 |        R |       a |      30
202 |        A |       b |      13
219 |        D |       b |      14
  4 |        A |       c |      15
220 |        G |       e |      16
278 |        B |       e |       7
  9 |        P |       f |      18

Is there any way to achieve this


texts: using postgresql foreign wrapper and replication at the same time for oracle external table

texts:
We need to replicate an oracle table into a postgresql database, now we are doing that with etl jobs and programatically but the frequence of the updates are to high for this approach. I was thinking to change the strategy doing:

1) build a fdw connecting this external oracle table to the postgresql db target server

2) setup a postgresql native ( streaming or logical, whichever works ) replication pointing to this fdw oracle connection.

Is that possible?


texts: Database Design Question for a weird use case

texts:
I am having trouble figuring out a good database design for an app I am developing. I have included a description of the related use case in the image below.

How would you design the Work Package in the database? A Project is made up of many Work Packages, which is fine, but a Work Package can also be made up of smaller Work Packages, which can be made up of even smaller Work Packages?  How do you design such a 'nested self' relation in the database?

Thanks for the help!


texts: InfluxDB - How can I limit the measures that a user can read by their tags?

texts:
I have some data that is collected by several sensors. Some users may own many of these sensors. I want to keep all the measures in a single database in InfluxDB. To differentiate the sensors I pretend to add some tags (such as 'sensor_id=aaa', 'sensor_id=bbb', etc).

I have read the documentation and it seems that there is no security option nor anything similar that allows me to restrict any given user to the sensors that he owns or has permissions to read.

Version 0.8 has this option that seems to do what I need. In version 0.9 (which I'm using) I can't find anything similar.

I want to use Grafana to allow people to create some dashboards, that's why I need InfluxDB, but I'm stuck on this part.

Does some configuration/feature exist that helps me to do this? Should I try a different approach? Any suggestions? Is there some other tool that would be more helpful in my case?

Thanks.


texts: compound index not being used on simple query

texts:
I'm running this query, and it seems to be doing a table scan , rather than using the relevant index..

select count(*) from pages where charity_id=447 and provider_id=1

this is the explain:

Aggregate  (cost=509163.46..509163.47 rows=1 width=0) (actual time=384314.621..384314.622 rows=1 loops=1)",
"  ->  Bitmap Heap Scan on pages  (cost=32850.60..505318.05 rows=1538163 width=0) (actual time=14952.277..379428.537 rows=1876399 loops=1)"
"        Recheck Cond: ((charity_id = 447) AND (provider_id = 1))"
"        Rows Removed by Index Recheck: 2053204"
"        ->  Bitmap Index Scan on index_pages_on_charity_id_and_provider_id  (cost=0.00..32466.06 rows=1538163 width=0) (actual time=14697.258..14697.258 rows=1910571 loops=1)"
"              Index Cond: ((charity_id = 447) AND (provider_id = 1))"
"Total runtime: 384318.977 ms"]

The indexes are:

"pages", ["category_id"], name: "index_pages_on_category_id", using: :btree
"pages", ["charity_id", "first_donation_at"], name: "index_pages_on_charity_id_and_first_donation_at", using: :btree
"pages", ["charity_id", "provider_id"], name: "index_pages_on_charity_id_and_provider_id", using: :btree
"pages", ["charity_id", "total_online_cents"], name: "index_pages_on_charity_id_and_total_online_cents", using: :btree
"pages", ["charity_id", "updated_at"], name: "index_pages_on_charity_id_and_updated_at", using: :btree
"pages", ["charity_id"], name: "index_pages_on_charity_id", using: :btree
... some irrelevant ones clipped ...
"pages", ["provider_id"], name: "index_pages_on_provider_id", using: :btree
"pages", ["provider_uid"], name: "index_pages_on_provider_uid", using: :btree

Any thoughts as to why this seems to be doing a table scan, and not using the compound index?

edit, a couple of examples:

Page Load (2.4ms)

SELECT  pages.*
FROM pages
WHERE pages.charity_id = 447 AND pages.provider_id = 1
ORDER BY pages.id ASC
LIMIT 1

Page Load (271604.8ms)

SELECT  pages.*
FROM pages
WHERE pages.charity_id = 447 AND pages.provider_id = 2
ORDER BY pages.id ASC
LIMIT 1

Not sure why the first one is fast, second one is slow, despite being the same filter options?


texts: Different "Master" Tables and Same "Child" Tables

texts:
I am working on Student Life Cycle management system which is in the design stage. Right now I am creating the database structure.

My requirement is as follows: Every year a student faces three term tests, and I want to store all marks related to these three terms. These marks are represented in digits.

According to the requirement above, the table structure can be configured in the following way:

term-test-master

-------------------------------------------
 exam-id | exam-year | batch-number | term
-------------------------------------------

term-test-marks

----------------------------------------------
 subject-id | exam-id | student-index | marks
----------------------------------------------

In the second scenario, all students face to end school examination and their marks are represented as a letter. Both "master" tables are a little bit the same but not exactly. But required column of end-exam-marks all most same to -test-marks.  But marks value is represented by a letter.

end-school-master

----------------------------------------
 exam-id | exam-year | exam-name | term
----------------------------------------

end-exam-marks

----------------------------------------------
 subject-id | exam-id | student-index | marks
----------------------------------------------

Note: I believe one of the applicable functional dependencies is { exam-year } → { exam-id }.

Can I use the same term-test-marks table in both situations with marks with VARCHAR datatype, or should I use two separate "child" tables for separate "master" tables?


texts: PostgreSQL: Switching schema name in a loop

texts:
I am trying to switch the schema name dynamically to do some data manipulation.
I am running the following.
DO $$
DECLARE schema_name TEXT;
BEGIN
FOR schema_name in
        select "name" from public.tenants
    Loop
        RAISE NOTICE 'The current schema is %', schema_name;
        INSERT INTO schema_name.campuses (select * from public.campuses where code = schema_name);
    End Loop;
END $$;

However I am presented with the following

NOTICE:  The current schema is NTU
ERROR:  relation "schema_name.campuses" does not exist LINE 1: INSERT
INTO schema_name.campuses (select * from public.campu...
^ QUERY:  INSERT INTO schema_name.campuses (select * from public.campuses where code = schema_name) CONTEXT:  PL/pgSQL function inline_code_block line 8 at SQL statement SQL state: 42P01

I have tried wrapping the schema_name in "" as well as [].
Is there a solution to this as my googling is not helping me at present.


texts: There seems to be a postmaster servicing the old cluster. | Upgrade from postgres 9.3

texts:
I am working on upgrading postgres from version 9.3 to 10. I have encountered a lot of issues along the way since the install is way more custom done than it should be (People here LOVE complex and custom/security through obscurity).

Far as I can tell it has something to do with pg_pool but am not too sure on that one.

Upgrade command:

time ${PGBINNEW}/pg_upgrade --old-bindir=${PGBINOLD} --new-bindir=${PGBINNEW} --old-datadir=${PGDATAOLD} --new-datadir=${PGDATANEW} --old-port=${PGPORT} --new-port=${PGPORTNEW} --jobs=$(nproc | awk '{ {print ($1 > 4) ? 4 : ($1 == 1 ? 1 : $1 - 1)} }') --link --username=postgres

Error:

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting


texts: SQL Server 2005 replication for a data warehouse

texts:
We are trying to implement some kind of Data Warehouse at my current employer.

Most of our data is in a SQL Server 2005, but we might also integrate some Access databases and some databases in a SQL Server 2008 Runtime version (it might matter because of the functionality it lacks). We would like the data to be replicated around once every hour. I wonder if there are people out that already have worked out a solution for this.

Our users would like to make queries to the warehouse from Excel or other apps using ODBC.

Also our amount of data is around 150 GB.


texts: Postgresql performed a "fast shutdown" by itself. Why? How was it able to do that?

texts:
I've already googled and manual read as much as I could before posting this here.

Version info:  Postgres8.4 on Ubuntu10.x

I have a personal laptop that I run a postgres server on, and a Java script using JDBC was running for 1600~ mintues (that I have had running for weeks prior) before dying saying an administrator restarted the server.  Here is what I found in the pgsql logs:

Feb 29 07:46:55 krufe-laptop postgres[3578]: [2-1] 2012-02-29 07:46:55 EST LOG:  incomplete startup packet
Feb 29 07:46:56 krufe-laptop postgres[1113]: [2-1] 2012-02-29 07:46:56 EST LOG:  received fast shutdown request
Feb 29 07:46:56 krufe-laptop postgres[1113]: [3-1] 2012-02-29 07:46:56 EST LOG:  aborting any active transactions
Feb 29 07:46:56 krufe-laptop postgres[24095]: [2-1] 2012-02-29 07:46:56 EST FATAL:  terminating connection due to administrator command
Feb 29 07:46:56 krufe-laptop postgres[24095]: [2-2] 2012-02-29 07:46:56 EST STATEMENT:  SELECT high FROM onemin_interval WHERE ticker = 'QQQ' AND interval_end <= 1329926940013 ORDER BY interval_start DESC LIMIT 46
Feb 29 07:46:56 krufe-laptop postgres[22214]: [2-1] 2012-02-29 07:46:56 EST FATAL:  terminating connection due to administrator command
Feb 29 07:46:56 krufe-laptop postgres[1252]: [2-1] 2012-02-29 07:46:56 EST LOG:  autovacuum launcher shutting down
Feb 29 07:46:56 krufe-laptop postgres[1250]: [1-1] 2012-02-29 07:46:56 EST LOG:  shutting down
Feb 29 07:46:56 krufe-laptop postgres[3582]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[3583]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[3584]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[3585]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[1250]: [2-1] 2012-02-29 07:46:56 EST LOG:  database system is shut down
Feb 29 07:47:10 krufe-laptop postgres[4318]: [1-1] 2012-02-29 07:47:10 EST LOG:  database system was shut down at 2012-02-29 07:46:56 EST
Feb 29 07:47:10 krufe-laptop postgres[4319]: [1-1] 2012-02-29 07:47:10 EST LOG:  incomplete startup packet
Feb 29 07:47:10 krufe-laptop postgres[4309]: [1-1] 2012-02-29 07:47:10 EST LOG:  database system is ready to accept connections
Feb 29 07:47:10 krufe-laptop postgres[4322]: [1-1] 2012-02-29 07:47:10 EST LOG:  autovacuum launcher started

I live by myself and noone else could ssh in from the outside to do this.  I was not home at the time this happened.  I viewed the auth logs for commands that were run during this time, nothing outside of normal cron commands.  Why would this happen?  What would cause this?  Can it be fixed/prevented?


texts: Query for fetching records from SQLite database on Blackberry

texts:
I want to fetch data from SQLite database for Blackberry.

There is one table with four columns: ID, FROM, TO, TIME

I want to fetch record on the basis of FROM, and TO values.

Where condition 1:

FROM = [from value] AND TO = [to value]

2nd condition is:

FROM = TO AND TO = FROM

It should return the last 20 records.


texts:
I'm using SQL Server 2014. There are some users who can query the tables. The problem is, that most of the time these queries use a lot of memory. I want to know if there is any way to limit resources per user?

There is not a specific query. I just want to control users. Sometimes they do crazy things.


texts: Mysql Right join with mutiple condtion not working

texts:
i have two table table1 and table2 and when right joining the table2 with table1 and i am not getting the row. here i have attached the fiddle link also

CREATE TABLE `table1` (
 `orderid` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`orderid`)
);
CREATE TABLE `table2` ( `fields_id` INT NOT NULL AUTO_INCREMENT , `table1_order_id` INT(11) NOT NULL , `field_value` VARCHAR(100) NOT NULL , `fname` VARCHAR(100) NOT NULL , PRIMARY KEY (`fields_id`)) ENGINE = InnoDB;

INSERT INTO `table1` (`orderid`) VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '1', 'karthi', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '2', 'karthi', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '3', 'selva', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '1', 'salem', 'city');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '2', 'chennai', 'city');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '3', 'mumbai', 'city');

select table1.*
from table1
right JOIN table2 ON (
        table2.table1_order_id = table1.orderid
        AND
            ( table2.field_value LIKE '%karthi%'
            AND table2.fname = 'name' )
        AND (
            table2.field_value LIKE '%salem%'
            AND table2.fname = 'city'
        )

    )

    where 1 group by table1.orderid

Above i wrote select query but it returns null result, but i am expecting output like below,

orderid 1


texts: sqlcmd to run large SQL file causes high CPU usage

texts:
I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. This does not look normal to me.

Using SQL Server Management Studio to run the script is not an option, since it won't run very large files.

Note: I know the SQL script is good, since I was able to run it on my local machine with another tool (xSQL data). Unfortunately, I don't have access to that tool on the server.


texts:
I'm wanting the best way to add a column to an existing table in my database.  I'm using SQL Server 2012. I don't want the data that is in the table to be affected, I just want to add a new column that has a default value of NULL.  The only ways I'm aware of for adding a new column is by using:

ALTER TABLE dbo.myTable ADD newColumn VARCHAR(20) NULL;

and the other way of adding a new column is by going into SQL Server Management Studio, bringing up the Design for the table I want to update, and adding the new column's information to the design.


texts: Prevent duplicate rows with many columns

texts:
Here is the shortened version of my table:

   CREATE TABLE [dbo].[Ability_Main](
    [Ability_ID] [smallint] NOT NULL,
    [Ability_Text] [nvarchar](150) NOT NULL,
    [Flag_Conditional] [bit] NOT NULL,
    [Flag_Choose] [tinyint] NOT NULL
--around 20 flag columns, bit type
 CONSTRAINT [PK_Ability_Main] PRIMARY KEY CLUSTERED
(
    [Ability_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Ability_Main]  WITH CHECK ADD  CONSTRAINT [CK_Ability_Main_Choose] CHECK  (([Flag_Choose]>=(0) AND [Flag_Choose]<=(2)))
GO

ALTER TABLE [dbo].[Ability_Main] CHECK CONSTRAINT [CK_Ability_Main_Choose]
GO

My question is - I want to prevent adding a duplicate entry that has the same flags - do I need to add a unique constraint on every single column (so 20+ constraints) or is there an easier way?


texts: Will Installation of Service Pack 3 on SQL Server 2012 Web Edition (existing production) require database restore activity?

texts:
We are currently running our Database Server on a RTM version:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

We learnt that we need to install the latest Service Pack for SQL Server 2012, which is SP3

I had the following doubts based on this subject:

Will installation of SQL Server 2012 Web Edition SP3 affect any of our production databases?
Do we need to restore the databases, after installation of SP3?
Are there any particular prerequisites we need to follow before the installation of SP3?
Do we need to perform a database backup and backup of the data files before the installation of SP3?

Please advise. Appreciate your help on this. Thanks.


texts: How to determine the name of a process that caused a trigger to fire

texts:
Short Version:
Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected?

Long Version
I have a customized synchronization routine that moves data back and forth between dis-similar database schemas.

When this process grabs a modified record from Database A, it needs to transform it into a record that goes into Database B. The database are radically different, but share some of the same data such as user accounts and user activity (however even these tables are structurally different).

When data is modified in one of the pertinent tables, a trigger fires which writes the PK of that record to a "sync" table.  This "sync" table is monitored by a process (a stored proc) which will grab the PK's in sequence, and copy over the related data from database A to database B, making transformations as necessary.

Both databases have triggers that fire and copy the PK to the sync table, however these triggers must ignore the sync process itself so as not to enter into "endless" loop (or less, depending on nesting limits).

In SQL 2005 and up, I use the following code in the Sync process to identify itself:

SET CONTEXT_INFO 0xHexValueOfProcName

Each trigger has the following code at the beginning, to see if the process that modified the data is the sync process itself:

IF (CONTEXT_INFO() = 0xHexValueOfProcName)
BEGIN
--  print '## Process Sync Queue detected. This trigger is exiting! ##'
  return
END

This system works great, keep chugging along, keeps the data in sync.  The problem now however is that a SQL2000 server wants to join the party.

Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected?

Thanks guys!


texts:
I want to grant select permission on a specific table to a remote ip address I had done it before in Mysql with this:

grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

but I can't do it in Oracle 11G


texts:
I've started a new database architecture using pgpool-II 3.4.5, I have 3 servers by now, 1 master and 2 slaves. They are configured to use Master/Slave conf and LoadBalancing.

The problem is: When I try to add a third slave node, pgpool show the status 0 or 3 for this new host. And even waiting for too long, it doesn't change.

If I change one of the well working hosts to the third node, it's goes down and the host that was down, starts working. I don't know if there is a limit, but pgpool documentation say that the limit is up to 127 slaves (0 slaves is allowed too).

Trying to add a third node :

Changing hosts:


texts:
I have a collection similar to the below, of say 5,000,000 records.

{ "_id" : ObjectId("56d18f95d2961d1ddcb7244f"),
  "rec_id" : 0,
  "status" : "open",
  "updated by" : "A"
}

User B is trying to mark all status to "closed", with below command:

db.prod.update({},{'$set': {'status':'closed', 'updated by':'B'}}, false, true)

Let's say user B is able to update closed status to 100,000 records and the connection is lost, or some problem occurs, and the remaining docs are not updated. So the data is inconsistent, is there any way in which we can fix this kind of problem?

I need to mark all status to closed or nothing to closed.


texts: very poor cardinality estimate on a geometry intersection

texts:
I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results in a bad query plan.

In particular the right most nodes of the query plan seem to think that the STPointFromText function will return a cardinality of 1000, and that the intersection of this point set with the geometry index returns 30% of the 54k rows.
(ran 1 million points through the table without finding a counter-example that actually returned more than 1 row)

The result isn't horrible in this abbreviated query, but when i join the output of this onto anything else, the high cardinality estimate forces the upstream table to be a tablescan+hashmap, even though the overall query returns 1 row. This extended query is running a few times per second, so i'm wondering how i can optimise this.

The spatial index is HHHH, for a highest resolution (over the approx 4000km longest side of the domain) of approx 80x50m, there are 56k polygons in the index, with expected minimum size of ~100m.

Note the difference between the est rows and the actual.

Estimated query plan.


texts:
I am new to DBMSs and I am still learning the theory.

I am getting really confused with this key business and after googling I have narrowed it down to just 2 keys I don't get (primary and super key).

I have a few questions on DBMS. I would be grateful if you can answer them for me.

1) What is the difference between Primary key and Super key in DBMS?
Highly appreciate it if you can use a comprehensive example to explain properly

2) Can Primary key and Super key both have multiple columns combined to form Primary key and Super key?

3) Is Primary key a subset of Super key or vice versa?


texts: How to extend oracle database to a newly added hard disk

texts: I'm a begineer to Oracle database admin role. Recently I have added a new hard drive to extend the capacity of an oracle database which runs on Linux. How to extend the oracle database to utilize new hard disk storage. Please explain the steps in point form.


texts: Is it possible to get execution call stack in a trigger?

texts:
I have 10 stored procedures and each of them does INSERTs into one tableX.

Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored  proc1 or sp2 or....) ?

Thank you.


texts: SQL Server - Do automatically created column statistics get dropped automatically after time?

texts:
The retired SQL Server 2000 documentation https://www.microsoft.com/en-us/download/details.aspx?id=51958 states:

  "The statistics generated for a column can be deleted if you no longer
  want to retain and maintain them. Statistics created on columns by SQL
  Server (when the AUTO_CREATE_STATISTICS database option is set to ON)
  are aged and dropped automatically."

Obviously this is old documentation related to SQL Server 2000, but I can't find anything official that is more current and says otherwise or that the behaviour is different now.  Can anyone answer definitively?  Thanks..


texts: Deleting large amounts (4 million rows) of mysql data efficiently, on a very regular basis

texts:
We have a mysql table that at any given time has about 12 million rows. We need to delete old data to keep the size of the table somewhat manageable.

Right now we are running this query daily, at midnight, using a cron job:

DELETE FROM table WHERE endTime < '1393632001'

The last time the query ran it examined 4,602,400, took over 3 minutes and CPU went through the roof.

What can we do to keep the CPU, synchronous db connections, disk cue depth etc from spiking unreasonably while still clearing old data?

PS: You will notice that the query is actually happening at a fairly inopportune time in our usage cycle. Assume that we have already shifted the query's timing to occur at the lowest point of usage each day. Also, there is no index on "endTime" and I would prefer to keep it that way if possible because there is a ton of data being inserted very regularly, and not much lookup.


texts: Postgres: Recreate TRIGGER after replacing function?

texts:
Given a function like this:

CREATE OR REPLACE FUNCTION foo()
RETURNS TRIGGER AS $$
BEGIN
    -- do something fancy
END;
$$
LANGUAGE plpgsql;

which is used in a TRIGGER:

CREATE TRIGGER bar
    BEFORE INSERT
    ON test
    FOR each ROW
    EXECUTE PROCEDURE foo();

Does the TRIGGER need to be re-created (DROP + CREATE) if the function foo() is changed (using REPLACE)?

Was coming across this while reading about partitioning.


texts: Sqlserver IPV6 handling function or numeric conversion


texts: Multiple versions of PostGIS and PgRouting on CentOS 7

texts:
I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5.

I've been fighting with dependencies and shared libraries, to make all working.

So the question is, which is the best way to manage de dependencies versions? If I install a major dependency version, old Postgis version still working?

Some dependencies are: GDAL, GEOS, Proj4, etc.

I've been organizing them in folders, with contains subfolders with the version of the dependency, using --prefix=path/v3.0.0 on ./configure script during the depency installation.

Example: Folder -> GEOS - Subfolders: v3.0.0, v3.4.0, etc.

After that, when i want to install any PostGIS version, I use ./configure to use any dependency absolute path.

Example: ./configure --with-pgconfig=path --with-gdal=path/version --with-proj4=path/version ...

But I'm not sure that is a right way.

Any suggestion is welcome to clear my mind.


texts: MongoDB -- Failed global initialization: Failed to open "var/log/mongodb/mongod.log"

texts:
I am getting errors starting Mongod v3.0.15 in Debian linux.  I had changed the /etc/mongod.conf file to use smallFiles by adding mmapv1: smallFiles: true to the storage options.
I now get the following error when I execute $mongod --config /etc/mongod.conf:

ib1:/ 514 $ mongod --config /etc/mongod.conf
2017-10-03T12:14:21.183-0500 F CONTROL  Failed global initialization: FileNotOpen Failed to open "/var/log/mongodb/mongod.log"

The mongod.log file exists, and it is also defined in the mongod.conf file as in that directory.
I am able to open the /var/log/mongodb/mongod.log file myself in the shell.  It hasn't been written to in a long time (months) curiously.
Any ideas?
Thanks very much,
Karen


texts:
My SYSTEM tablespace is getting filled up enormously within a few days. I've added another dbf file when it was filled up completely earlier. Now, the second .dbf is also about to fill. The only process that's happening on daily basis is an export database for logical backup. Also, tablespace auto extent is ON. What might be the reason for this tablespace fill up? How can I tackle this situation?
I'm using Oracle 11g r2 in archivelog mode.

Attaching result for below query:

SELECT owner, segment_name, segment_type, extents, blocks, bytes
FROM dba_segments
WHERE tablespace_name='SYSTEM'
ORDER BY bytes;


texts: Postgresql script doesn't work as I expect

texts:
DO $$
DECLARE companyNrs text[] = ARRAY(SELECT LPAD(companynr::text, 5, '0') FROM s_users);
        companyNr text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    raise notice 'notice message %', companyNr;
  END LOOP;
END; $$

The postgresql script above works perfectly on DataGrip. But if I add a DECLARE statement into the loop(as below), it gives multiple syntax errors. What is the rule here ?


texts: Zero Maintenance self-hosted off-line databases?

texts:
I'm looking for a "Zero Maintenance" database engine that can be installed on Windows. We have a lot of customers that want our software installed on their local computers. Although we have a cloud offering, many of our customers do not use it, for various reasons.

By "Zero Maintenance", I mean:

Auto-tuning - the db doesn't lose performance over time
Auto-compacting - the database doesn't bloat over time
Resistant to corruption - in many crash scenarios, should automatically recover to a logically consistent state without human intervention
Auto-patching - nice, but not essential.

We want this because our customers don't have the expertise or budget to maintain something like SQL Server on their own systems. And we don't have the staff to do it for them. So the idea of having a database that is stable for long periods of time is very attractive.

I have found three potential candidates:

Mimer - Claims "Zero Maintenance", and is a full SQL relational db
Amazon Aurora - excluded from my consideration because it is Cloud only
InterBase - Claims "Admin free" "Rapid crash recovery" -- they also say they are fully-featured SQL database.

Our largest customer has under 2GB of data. Rather than write an abstraction layer, I would prefer coding to a single db platform that would have very low maintenance requirements on a local machine, and then scale well to the cloud. Postgres certainly works well at scale, but I'd have to research the local story more. Mimer claims to do this.

Have any of you heard of others? Any experience with any of these packages? I welcome any feedback.


texts: Users mostly care about recently inserted rows. But the table has a high number of rows. What is the best way to improve performance?

texts:
I have a huge table with rows > 1000000. However, people will only care about rows that were ordered by updated_at and created recently. There are updated_at and created_at columns in the table. By default, there is a filter for the last 30 days of created_at and there is an order by updated_at desc and a Limit of 30 in the end. The values of created_at goes over 2 years but the users mostly care about the last month.

I have created an index on updated_at and created_at using BTree each.

I have explored partition and it is really what I am looking for where you can divide the tables by created_at. However, there is no way to create a unique index on multiple child tables.

Is there any other way to increase performance? Very few users care about very old data and it should be accessible even though it will slow down.


texts: Has the ID column configured as PK, NN and AI, be the first column on my table definition?

texts:
I have a .csv file with a bunch of data which I am using for load all the data into a DB table. The file lacks the ID column (because that's is an internal column managed through our software and they don't care about it).

To avoid issues with the data my "solution" was to move the column id to a position where doesn't interfere with the data from the file and that's right after ActiveFlag column or just at the end.

Check the following image for a graphical representation of the table:

Currently the id is my first column and I haven't any problem but I am trying to move to the end because the issue I am having with the data but I got the following error:

Operation failed:
Executing:
ALTER TABLE `sedb`.`volume_pricing_agreement`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT AFTER `ActiveFlag`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`AgreementNumber`,`CustomerSiteID`, `CFProgramLevelID`, `Source`, `AgreementTypeID`, `id`);

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

I don't know if the column has to be the first one on the table definition and if there is anyway to let it be wherever I want to or the solution maybe would be import the data by skipping the id column (supposing it's the first one).

I was checking the syntax here but I don't have a clue in how to skip a column during load:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

This is the output of the command SHOW CREATE TABLE sedb.volume_pricing_agreement;:

volume_pricing_agreement, CREATE TABLE `volume_pricing_agreement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `AgreementNumber` int(11) DEFAULT NULL,
  `AgreementName` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
  `CustomerSiteID` int(11) NOT NULL,
  `CFProgramLevelID` int(11) DEFAULT NULL,
  `Discount` decimal(5,4) NOT NULL DEFAULT '0.0000',
  `Source` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `ActiveFlag` int(1) NOT NULL,
  `AgreementTypeID` int(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `AgreementNumber` (`AgreementNumber`),
  KEY `CustomerSiteID` (`CustomerSiteID`),
  KEY `ActiveFlag` (`ActiveFlag`)
) ENGINE=InnoDB AUTO_INCREMENT=2763 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Any help? How should I deal with this?


texts: Database management tool for compact edition (.sdf) database

texts: What is the best database management tool for remotely managing a compact edition (.sdf) database created in WebMatrix?


texts:
I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump:

sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name

This command generates a valid sql file that starts with droping any existing database objects, then generates all tables, indizes, sequences and so on, and finally inserts data.

When I try to restore the generated backup file with: (line breaks added for display purposes only)

sudo pg_restore
    -d database_name -h 127.0.0.1 -U postgres
    --format=c --clean --create out.sql

it fails and prints:

pg_restore: [archiver] did not find magic string in file header

What is the reason for that?


texts: How to add entries in a table column as the attributes for another table in MySQL?

texts:
I am designing an Attendance Management System, so for the table which contains the attendance status(Present or not) of all the students for all the days and all courses, following are the details:

Date and Lecture ID are primary keys (Assuming that only one lecture of particular course per day)
Every student is represented as an attribute.

Is this a good design?
How can I add students from the students table as attributes to the attendance table?


texts:
Here is the definition of the relevant table:

CREATE TABLE IF NOT EXISTS pricehistory (
        id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        price INTEGER NOT NULL,
        region VARCHAR(20) NOT NULL,
        timestamp TIMESTAMP NOT NULL,
        is_from_api INTEGER DEFAULT 0)

Here is the query at issue:

SELECT MAX(price) AS price
FROM pricehistory
WHERE UNIX_TIMESTAMP(CONVERT_TZ(timestamp, '+00:00', 'SYSTEM'))
      >
      UNIX_TIMESTAMP(CONVERT_TZ(DATE_SUB(NOW(), INTERVAL 1000 DAY), '+00:00', 'SYSTEM'))
  AND region='us';

Here is the Explain

id: 1
select_type: SIMPLE
table: pricehistory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 368040
Extra: Using where

Ok so this query runs really slow. I think it takes about 500ms to run for only 300k-400k rows.

Roughly 1/5 of all the rows have a region='us'. If I remove the UNIX_TIMESTAMP() function it drops the run time by about 100ms so I'll probably do that.

I've tried indexing, but I'm new to indexing and have never done it before. All the indexing I've done has either had no effect or slowed the query down even more.

I need the max price in the last x days, of only 1/5 of the rows (region='us') in the whole table. I can't think of a way to build an index out of that.

Note: I also have a query that gets the minimum price.

Server version: 10.1.35-MariaDB - mariadb.org binary distribution.


texts: SQL Server Replication and Recovery Model

texts:
In SQL Server for Replication when recovery model is not very important then how it saves the transactions and also keeps the secondary instance in sync?

I tried to check on MSDN and found the following:

  Replication functions properly using any of the recovery models:
  simple, bulk-logged, or full. Merge replication tracks change by
  storing information in metadata tables. Transactional replication
  tracks changes by marking the transaction log, but this marking
  process is not affected by the recovery model

but I didn't get the concept for SIMPLE Recovery Model. And if the T-logs gets copied before checkpoint then what if someone manually executes checkpoint?


texts: How to put this particular constraint on this link table in MySQL?

texts:
I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table has the following fields:

+------------+----------------------------------+------+-----+---------+----------------+
| Field      | Type                             | Null | Key | Default | Extra          |
+------------+----------------------------------+------+-----+---------+----------------+
| id         | int(11)                          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)                     | YES  | UNI | NULL    |                |
| category   | enum('Area','Level','Temp')      | YES  |     | NULL    |                |
| expireDate | datetime                         | YES  |     | NULL    |                |
+------------+----------------------------------+------+-----+---------+----------------+

The link table StoreGroup that records each store's groups has the following fields:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| storeID | varchar(16) | NO   | PRI | NULL    |       |
| groupID | int(11)     | NO   | PRI | 0       |       |
+---------+-------------+------+-----+---------+-------+

However, even though a store can belong to several groups, it has a constraint that it can belong to only one group in one group category. For example, a store with storeID ABC can belong to both groups with groupID 1001 and 1002 since 1001 is from 'Area' category and 1002 is from 'Level' category. But it cannot be assigned to groupID 1003 because both group 1001 and 1003 are from the same group category 'Area' as shown below:

SELECT * FROM Groups;

+------+--------------+----------+---------------------+
| id   | name         | category | expireDate          |
+------+--------------+----------+---------------------+
| 1001 | City A       | Area     | NULL                |
| 1002 | Branch       | Level    | NULL                |
| 1003 | City B       | Area     | NULL                |
+------+--------------+----------+---------------------+

So when one wants to insert the record into StoreGroup table, the following is OK:

INSERT INTO StoreGroup VALUES('ABC', 1001);
INSERT INTO StoreGroup VALUES('ABC', 1002);

But the following should fail because storeID 'ABC' cannot belong to both groups 1001 and 1003 since 1001 and 1003 both are from category 'Area':

INSERT INTO StoreGroup VALUES('ABC', 1003);    --This should fail.

So how to modify the table of StoreGroup or Groups to add this constraint so that assigning store to groups with same category will fail?


texts: Count and Group data based on consecutive occurrence

texts:
Tricky for me to define the problem, maybe you understand it through my dummy data.

I have this data:

PK, TaskPK
1, 1
2, 1
3, 2
4, 2
5, 5
6, 1
7, 1
8, 2
9, 2
10, 5
11, 5

Now I have to count TaskPK so, I make this query

Select PK, TaskPK, Count(*)
From tbl
Group by TaskPK

It brought this result

TaskPK, Count(*)
1, 4
2, 4
5, 3

But I want slight different result

Like this

TaskPK, Count(*)
1, 2
2, 2
5, 1
1, 2
2, 2
5, 2

The above result based on consecutive data occurrence, as TaskPK start with 1 (it group together), then it change it 2 (it group together), then 5 (it group together) taskPK. But as TaskPK again shifts to  1, then it should group seperatly not link with previous occurrence of 1, this task seperately count.

Is this possible?


texts: Extract all of the keys, including nested, from postgres JSONB column

texts:
I have a table, my_table, in postgres with a column, d, that is of a jsonb type.  An example of the data in that column is:

{
"logo": "a",
"name": "b",
"status": "c",
"address": {
    "city": "d",
    "state": "e",
    "street1": "f",
    "street2": "",
    "postal_code": "g",
    "country_code": "h"
},
"industry": "i",
"logo_url": "j",
"followers": 2,
"twitter_id": "",
"description": "k",
"status_code": "l",
"website_url": "m",
"headquarters": {},
"employees_count": 5,
"employees_range": "1-10",
"current_jobs_posted": 0

}

and I would like to have the sql return:

logo
name
status
address
city
state
street1
street2
postal_code
country_code
industry
logo_url
followers
twitter_id
description
status_code
website_url
headquarters
employees_count
employees_range
current_jobs_posted

The current SQL

SELECT    jsonb_object_keys(data) as key_name
FROM      profiles
WHERE     jsonb_typeof(data) = 'object'
and       id = 5;

returns

logo
name
status
address
industry
logo_url
followers
twitter_id
description
status_code
website_url
headquarters
employees_count
employees_range
current_jobs_posted

leaving out the keys nested under address.  I need to have those broken out as well.  It can either return them as rows, or if necessary, it can be flattened and returned as columns.  I just need to be able to return all of the keys, including any nested keys.  There may be other keys nested in other entries in the table.

Thank you


texts: Extended Events not displaying today's deadlock information

texts:
I am using the following query to retrieve deadlock xml.

select create_time,XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select s.create_time,CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health' ***AND s.create_time > GETDATE()-3***) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

query from Retrieving Deadlock Graphs with SQL Server 2008 Extended Events.

The only thing I have modified is adding create_time field and filtering it for past 3 days

The query does not give me the data for a week. It gives me data for 5/4/2017 but not for today 5/12/2017.

I can see deadlock information logged in Error log.

How do I retrieve deadlock xml for today? I can read the error log. However, I am interested in getting it in xml.

SQL Server Version: 2008 R2 SP2


texts: Postgres bypassing index with IN clause with 100+ values?

texts:
I came across this on another forum:

  PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK will be turned into a full table scan if the IN list exceeds 100.

I wasn't able to find anything about this.  Does PG have such a limit (I imagine so), and if so, what is the limit?

I know there are times where having a large subselect would be better used in a temp table, but it would be helpful to have knowledge of where the cutoff is.


texts: Diagnose slow script executions on specific server

texts:
I work with an unmanaged server in a remote datacenter to host web based applications. When we run the SQL scripts to create and populate an application database the whole process is painfully slow. A set of scripts that in my underpowered local PC runs in 5 seconds, on the remote server can take up to 20 minutes.
When I check the running processes I can see it isn't stuck in any specific statement, it's simply that each one takes several seconds, specially ALTER TABLE statements:
ALTER TABLE foo_bar
ADD CONSTRAINT FK_foo_bar_foo_id
FOREIGN KEY (foo_id)
REFERENCES foo (id)

It's a brand new app, so most tables are empty or have at most a few hundred rows. But slowness already happens much before the INSERT INTO statements. All tables are InnoDB.
I run the scripts through command-line PHP if that matters. There's no antivirus I'm aware of. MySQL/8.0 was installed with Windows MySQL Installer and runs as service with this configuration:
[client]
port=3306
[mysql]
no-beep
[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="FOO.log"
slow-query-log=1
slow_query_log_file="FOO-slow.log"
long_query_time=10
log-error="FOO.err"
server-id=1
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections=151
table_open_cache=2000
tmp_table_size=16M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=0
read_rnd_buffer_size=0
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060
character-set-server = utf8
collation-server = utf8_unicode_ci
log-bin = OFF
skip-bin-log

Where can I start looking at this? What diagnostics can I try?


texts:
I try to start Slony replication. I have PostgreSQL 9.3.4, PgAdmin III 1.18.1 and Slony-I 2.2.0-1. I have set the path to Slony to D:\GIS\PostgreSQL\9.3\share where I have the Slony files. Whenever I try to create new Slony-I cluster, there is following message on the bottom of the window: Slony-I creation scripts not available; only joining possible.

When looking for solution, I found a helpful maillist. Especially first post by Neel Patel was helpful:

  Sorry for the late replay, we have checked and it is a bug in pgAdmin
  because the name of the sql files got changed in new slony version (
  v2.2.0 ) which is used by the pgAdmin.

  Issue :-

  When we install the slony ( version less than 2.2.0 ) then it will
  install below sql files in C:\Program Files\PostgreSQL\9.3\share
  folder.

  slony1_base.sql slony1_base.v83.sql slony1_base.v84.sql
  slony1_funcs.sql slony1_funcs.v83.sql slony1_funcs.v84.sql

  Now in New version of slony 2.2.0 onwards the name of the above sql
  files got changed as below.

  slony1_base.2.2.0.sql slony1_base.v83.2.2.0.sql
  slony1_base.v84.2.2.0.sql slony1_funcs.2.2.0.sql
  slony1_funcs.v83.2.2.0.sql slony1_funcs.v84.2.2.0.sql

  As we are using above sql files names in pgAdmin to create the new
  slony clusters so we need to change in pgAdmin also.

Later in the discussion a patch fixing it appeared, but I don't know how to apply it - see my previous question. See that question for the ways I tried.

Is there any way to solve this problem other than to install the patch? Help with installing the patch would be also appreciated, but I would prefer it to appear in the linked answer than here, this question should focus on other ways to solve it.

EDIT: I tested different versions of PostgreSQL and Slony-I (Slony 2.0.7 on PostgreSQL 9.1 and Slony 2.1.3 on PostgreSQL 9.2) and also pgAdmin 1.16. The same problems occurs everywhere.


texts: Find all ancestor nodes of a HierarchyId using a single SQL statement?

texts:
I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID.  Every solution I've seen using HierarchyID seems to use either a CTE or a variable.  Is there a way to do this using a single select statement?

To make things simpler:

CREATE TABLE Employee
(
    EmpId INT PRIMARY KEY IDENTITY,
    EmpName VARCHAR(100) NOT NULL,
    Position HierarchyID NOT NULL
)

INSERT INTO Employee (EmpName, Position)
VALUES ('CEO', '/'),
    ('COO', '/1/'),
    ('CIO', '/2/'),
    ('CFO', '/3/'),
    ('VP Financing', '/3/1/'),
    ('Accounts Receivable', '/3/1/1/'),
    ('Accountant 1', '/3/1/1/1/'),
    ('Accountant 2', '/3/1/1/2/'),
    ('Accountant 3', '/3/1/1/3/'),
    ('Accounts Payable', '/3/1/2/'),
    ('Accountant 4', '/3/1/2/1/'),
    ('Accountant 5', '/3/1/2/2/'),
    ('DBA', '/2/1/'),
    ('VP of Operations', '/1/1/')


texts: Well after learning DBMS as a subject i got so many questions in mind. Normalization is one of them. As i learnt it there was a lot more confusion and i found that whatever we do in normalization process we can do it by general common sense also. Even while making projects also people are not used to follow it. So is it really needed? Is it followed in the companies? I am asking this question because probably it might consume more time to normalize the database. We can directly normalize it using just common sense therefore i don't think there is any need of following the standard normalization procedure. Correct me if i am wrong.


texts: Need help with this sql query for Postgres DB for a Python flask application

texts:
@app.route("/home/search/details/<string:isbn_result_isbn>")
def book_search_results(isbn_result_isbn):
    book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {"g": isbn_result_isbn}).fetchone()
    book_id = db.execute("SELECT * FROM book_reviews WHERE book_id=:v",{"v": book_details.id}).fetchall()
    return (f"{book_id}")

Unfortunately book_id is returned as blank.
However if the query is hard coded:
(book_id = db.execute("SELECT * FROM book_reviews WHERE book_id=89).fetchall())

I get a result. I know the problem is with book_details.id (that being an integer but is cast as a string in the sql query & hence I get no result), but can't seem to figure a way out of this.
Kindly help


texts: Poor Performance When Calling Query From Another DB on same Server

texts:
I have 2 databases - both on the same server. Both databases also have their files on the same drives.

When I run a query on it's local DB, it runs in 3 secs.

However, when I run it from the second DB, it takes about 20mins! The only difference here is the Database name added to the FROM clause.

What can I look for to find why this performs so badly? Would statistics play a part?

The query is a SELECT one on a TVF. Unfortunately I cannot view the TVF as it is 3rd party query and I do not have access.


texts: Update specific records of table based on subquery's output (sql server)

texts:
have this query which produce below output :

select sec.site,sec.Name,sec.Freq from
( select Name from sec group by name having count(Freq) > 3)as dt
inner join sec on dt.Name = sec.Name
where sec.Freq like '%MUF2900%' ;

site    Name            Freq
S003   sct003a       MUF2900_26
S003   sct003a       MUF2900_26
S003   sct003a       MUF2900_26
S034   sct034a       MUF2900_26
S034   sct034a       MUF2900_26
S034   sct034a       MUF2900_26

Now i have to update my another table name Test based on the output of above query.
Table Test currently contains following records (before Update) :

 site      cell       ID
 S003      09M00011   1019581
 S003      09M00012   1019582
 S003      09M00013   1019583
 S003      29M00011   1019581
 S003      29M00012   1019582
 S003      29M00013   1019583
 S034      09M00011   1016581
 S034      09M00011   1016582
 S034      09M00011   1016583

 now for site S003 , ID for for cell starting from 09 and 29 are same ,so i need to update the ID for 29  i.e the unit place of ID should be incremented by 10 , like following..

REQUIRED :

  Site      cell       ID
 S003      09M00011   1019581
 S003      09M00012   1019582
 S003      09M00013   1019583
 S003      29M00011   10195811
 S003      29M00012   10195812
 S003      29M00013   10195813
 S034      09M00011   1016581
 S034      09M00011   1016582
 S034      09M00011   1016583

I have many records like this.

Note : S034 has no cell starting from  29 , so its ID dont need to update.

what i tried and where im getting confuse is  :

 update Test set ID = (how to do this ??)
  where Test.site = (select sec.site,sec.Name,sec.Freq from
( select Name from sec group by Name having count(Freq) > 3)as dt
inner join sec on dt.Name = Sec.Name
where sec.Freq like '%MUF2900%') where cell like '29M%';

How should i do this. Do i need to make cursor for this ??


texts:
In the process of finding duplicate indexes, I've been using the following query to identify them:

SELECT
indrelid::regclass AS TableName
,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
GROUP BY
indrelid
,indkey
HAVING COUNT(*) > 1;

From which I get an entry with the following data:

tablename   indexes
distribucion.asignacion {distribucion.distribucion_unique_asignacion_imei,distribucion.pk_distribucion_asignacion}

Too check that the index are actually duplicate, I manually see the columns involving each reported index, with this query:

select * from pg_indexes where tablename='<table_name>';

But when I search for the above indexes for ''distribucion.asignacion'' table I get no results.

What could be wrong? Have these indexes been left "orphan", if so, may be there are other orphan structures too in my database, so how can I find such?


texts: Is it ever useful to SELECT DISTINCT on the primary key column(s)?

texts: From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?


texts: Downgrade database from SQL Server 2012 to 2008

texts:
Hi guys got a problem with a database.

I've been asked to perform some indexing and delete queries on a live database, so first of I wanted to get the database to our dev server so I can test out some things before I commit to live.

Unfortunately the dev is SQL Server 2008 and the live is SQL Server 2012. So I've had to try and downgrade the database.

Database is a lovely 33gig. and I've started to use the generate script wizard to create a 2008 version.

Problem is it gets so far then errors on random tables. So far I have no idea what to do to solve the issue. In the advanced settings of the wizard I select schema and data (yup going to be huge)

Error is due to my pc running out of disk space. I have a problem, how do I recreate this DB in 2008 that's both fast and easy? generating the script like that hasn't worked.

Open to ideas guys!


texts: Subquery to return multiple columns or a JSON object

texts:
Is there any way for me to return multiple columns from a subquery or at least return a column that is a JSON object of all of the columns I need?

This is a working query that works:

select r.*,
       (select status
        from builds
        where repo = r.id
          and branch = 'master'
        order by id desc
        limit 1)
from repos r
where id = any($1)

Where $1 is an array of ids

But I would like to return more than just the status column from the builds table:

select r.*,
       (select status, start_time, end_time
        from builds
        where repo = r.id
          and branch = 'master'
        order by id desc
        limit 1)
from repos r
where id = any($1)

After looking around it seems that row_to_json should work for me. However, I'm not sure how it should work in this given case:

select r.*,
       row_to_json(select status,
                   start_time, end_time
                   from builds
                   where repo = r.id
                     and branch = 'master'
                   order by id desc
                   limit 1) as build
from repos r
where id = any($1)

I am getting

syntax error at or near "select"


texts:
How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies?

Let's say I have Product table, this product has a price, but what currency should the price be in? Should it always be the same or should I also have Currency  column with currency code or CurrencyID pointing to specific currency?  Should I set currency per product or globally per database?

What is your experience with that?


texts: Is it best practice to install client tools (like Oracle SQL Developer) on the database server or another machine?

texts: A company doing a code review asked for credentials to an Oracle database server. They logged in but soon came back asking where SQL Developer was located, stating that they couldn't find it in the "usual location". My DBA told them we never install client tools on the DB server and that they needed to install it on their own client. Which is best practice?


texts:
2 tables are given

Table T_PRICE

P_Price    P_Type    P_Route
15         red       long
10                   long
5                    small

Table T_ROUTE

R_Type     R_Route
blue       long
red        long
black      small

I need to display all Routes with prices.
When there is no price for type, price should be from table T_PRICE without type.

My approach:

Select * from T_ROUTE
left outer join T_PRICE on P_Route = R_Route
and case when P_Type <> R_Type then '' else R_Type end = P_Type

Wrong Result:

R_Type R_Route  P_Price P_Type P_Route
blue   long     NULL    NULL   NULL
red    long     15      red    long
black  small    NULL    NULL   NULL

Right result should be:

R_Type R_Route  P_Price P_Type P_Route
blue   long     10             long
red    long     15      red    long
black  small    5              small

How do I get the conditional join to work?
Thank you!..


texts: Why does sqlexpress run on .\sqlexpress while mssqlserver runs on .\

texts:
I have two computers.

One machine runs sqlexpress and the instance is named SQLEXPRESS

The other machine run mssqlserver and the instance is MSSQLSERVER.

Why is it that sqlexpress runs on .\sqlexpress while mssqlserver just runs on .\?


texts:
For an assignment, I had to design a database from a given problem (based on a fictional health service organisation with 3 medical centres).

Three of the entities I had (among many others) were:

Branch (the actual medical centres; primary key branchNo)
BranchSection (the sections within each medical centre; primary key sectionNo; includes branchNo as foreign key to Branch table.)
Staff (the people working at the medical centres; primary key staffNo; includes sectionNo as foreign key to BranchSection)

One of the requirements of the given problem was that the staff entity hold information about the section and branch of each staff member, and so I added the SectionNo attribute as a foreign key in the staff entity (see below). I intentionally didn't put the BranchNo attribute into the staff entity because the SectionNo determines the BranchNo attribute and so I thought there would be a transitive dependency if I had both SectionNo and BranchNo in the staff entity. However, when I got my result back I was marked down for not having BranchNo in the staff entity. Could someone please clarify whether I was right in assuming this would cause a transitive dependency or whether my tutor was right to mark me down.

Here is a visual representation of these 3 entities (to make it easier to understand what I'm saying).

Branch (BranchNo, address, telephoneNo)
BranchSection (sectionNo, sectionName, branchNo)
Staff (staffNo, firstName, lastName, position, birthdate, telephoneNo, sectionNo)

Note: sectionNo is a true primary key; it is unique across all Branches.


texts: Execution plan contains a 'sort' even though the data is sorted in index

texts:
I have a table which has a clustered index as below:

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
ON Machinery (MachineryId, DateRecorded)

I'm selecting based on the fields in the clustered index, plus one more:

SELECT DateRecorded, Latitude, Longitude
FROM MachineryReading
WHERE MachineryId = 2127        -- First key in the index
AND DateRecorded > '2017-01-10' -- Second key in the index
AND DateRecorded < '2017-10-16' -- Second key in the index
AND FixStatus >= 2              -- Not a key, resulting in a scan
ORDER BY DateRecorded

I would have expected this to result in a simple clustered index scan. However, looking at the live query statistics, and the actual execution plan, the majority of the query's execution time comes from sorting the results after the index scan. Why is the ordered data being sorted again?

https://www.brentozar.com/pastetheplan/?id=S10DvjZpb


texts:
Currently I'm using:

SELECT t.CustomerLastName
FROM (
    SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName
    FROM OrderLine, Product, Orders, Customer
    WHERE OrderLine.ProductId = Product.Id
    AND Orders.Id = OrderLine.OrderId
    AND Customer.Id = Orders.CustomerId
    GROUP BY Orders.Id, Customer.Lastname
    ) AS t
WHERE t.OrderLinePrice =
(
    SELECT MAX(s.OrderLinePrice) AS MaxOrderPrice
    FROM (
        SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName
        FROM OrderLine, Product, Orders, Customer
        WHERE OrderLine.ProductId = Product.Id
        AND Orders.Id = OrderLine.OrderId
        AND Customer.Id = Orders.CustomerId
        GROUP BY Orders.Id, Customer.Lastname
        ) AS s
)
ORDER BY CustomerLastName

As my query to fetch the list of names for which the price of their order is equal to the price of the most expensive order. Now having 2x more or less the same subquery in one query seems horribly inefficient, so I'm wondering if, how and where to start with optimising this. Any tips?


texts: Trigger firing despite no rows being affected

texts:
This is more of a general question but the motivation for this question was a problem I faced while using SQL Server.

I have this trigger attached to an Insert event on a table that contains some logic that, as a side effect, would raise error if no rows were inserted. Upon further investigation, I discovered that the trigger was firing despite no rows being inserted.

The language used in the Microsoft Docs on DML Triggers seems to contradict this behavior:

  DML triggers is a special type of stored procedure that automatically takes effect when a DML event takes place that affects the table or view defined in the trigger.

Is this a default behavior across DBMSs? Is there a particular reason to fire a trigger when no rows are affected?


texts: Data warehouse vs Data mart (sql server)

texts:
I have a very basic question. My understanding is Data mart is essentially a database for a business segment per say and data warehouse is a warehouse of multiple data marts and other sources of data combined in a way that allows ease of analysis and reporting.

My question is: In case of a single database/data mart. Is it a good idea to create a data warehouse with a different schema (e.g. star or snowflake), just to enhance analysis efficiency?
If not, should the current database be modeled as a data warehouse or a data mart?


texts: Show recent select queries on Firebird database file

texts: Is there a way to log or query the n recent SELECT queries to an FDB database file? I have already tried using the MON$ and RDB$ tables but I didn't find anything useful.


texts: How SQL Server detects a record internally?

texts:
I am following this SO post where the author (stack programmer) states

  SQL server uses the identity column as the key value to refer to a
  particular row. So only a single identity column can be created.

He also mentioned that

  Also if no identity columns are explicitly stated, Sql server
  internally stores a separate column which contains key value for each
  row.

Could anyone explain the two points(specially the second one) in  more details?

Thanks


texts:
What is failsafe way to migrate ?
Both database are on CentOS 7 different servers and latest versions of database.


texts: SQL Server 2008 R2 index rebuild fails with severity 17

texts:
Occasionally during our index maintenance, the job will fail with a SEV 17 error where enough space can not be allocated for the object it is rebuilding. The database is laid out as such:

Data_file1    PRIMARY    0 growth         0% free                Max Size UNLIMITED
Data_file2    PRIMARY    0 growth         0% free                Max Size UNLIMITED
Data_file3    PRIMARY    0 growth         Less than 1% free      Max Size UNLIMITED
Data_file4    PRIMARY    250 MB growth    Less than 1% free      Max Size UNLIMITED

Essentially, 3 of the 4 data files are full and not allowed to grow, the fourth is full and allowed to grow. The files are spread out across different LUNs (and the reason for why is messy). So when the online index rebuild starts, it is my understanding that if any additional space is needed, it will grow into Data_file4 and be fine, but it is apparently trying to grow into a different file where growth is not allowed and failing. I am unable to reproduce this error, but I was wondering if anyone had insight into why this happens.

Full SQL Server version is 2008 R2 Enterprise, SP2 CU 4 (10.50.4270). We use Ola Hallengren's rebuild scripts, where we rebuild online but no sort in tempdb.


texts:
I am getting an ORA error (08102 - Index Key Not Found) with several customers, and I have been unable to reproduce or explain it.  They have all been on the same index on the same table.

This error is tied to two tables - ALPHA and BRAVO.  ALPHA has a primary key on alpha_id. BRAVO links to ALPHA by a foreign key reference to ALPHA.alpha_id.  For these customers, all BRAVO rows point to a single APLHA row on the BRAVO table. We have an index on the BRAVO table whose only column is BRAVO.alpha_id.

That index, ALPHA_ID_FK, is generating the error when we go to delete rows. As I said, I have been unable to replicate this myself, but I'm getting reports of the same problem by several different customers.

We are sometimes able to rebuild the index to correct this issue, but even rebuilding the index is not working for all customers.

What is causing this error, and how can I better prevent it?


texts: Computed Column Update when Table Value changes

texts:
I have environmental sensors collecting some raw data from environmental stations. In addition to the sensors, there is a physical gauge measuring water height (staff gauge).

I'm trying to set up the staff gauge calculation, so that it will take the most recent offset reading from Table 2 and apply it to any new measurements coming in, without changing the previously calculated values.

Table 1

station_time
Air_temp_celcius
Rainfall_mm
pressure_mH20
water_temp_celcius
sensor_depth_mH20
staff_gauge_height_m (calculated, sensor_depth_mH20 + Offset from Table2)

Table 2

Observed_time
Offset_m
comments

My thinking was to have a function to pull the offset

SELECT TOP 1(Offset)
FROM Table2
ORDER BY Observed_time DESC

Is there a way to have it only update new values coming into Table 1 without updating previous values when a new offset is entered?  New offsets are entered into Table 2 very sporadically as field crews visit the site.

Additional information from comments:

There is no direct relationship between the two tables, other than the latest offset is used to calculate the staff gauge value. It should always use the most recent value.

I have specific stations and specific station offset tables. I generalized the table structure: table1_stream1 and table2_stream1_offset. As for the offset storage on the table, redundancy. Always was taught you normalize as much as possible and don't duplicate data in multiple tables if you can avoid it. I no longer work on coding and databases as much as I'd like, but some stuff sticks.

  Are you saying if you have a station called "Antartica" and a station called "Africa" then you have the following tables: Antartica_stream1, Antartica_stream1_offset, Africa_stream1, and Africa_stream1_offset, etc.?

Correct. Given stations are polling hourly and not all locations carry the exact same sensor package (or sensors are in different order). Also individual stations may go offline due to technical or environmental issues.


texts: SLEEP_TASK Wait Type in SQL Server - What does it indicate?

texts:
I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them.

I'm not the official DBA, just a SQL Server developer who knows some DBA stuff.  We upgraded our servers last weekend to 10.52.2500.0 - R2SP1 I think.

All the info I could find online indicates that SLEEP_TASK means the server is waiting on some sort of internal process to  complete.  I don't have any blocking or any background processes like checkpoints or ghost cleanup running, so I'm a bit stumped.

Has anyone seen this wait type before, and if so can you give me an idea of what is causing it?


texts: The Target Principal Name Is Incorrect. Cannot Generate SSPI Context (SQL or AD Issue)?

texts:
We had a power outage last night and when the server came back up our domain users could not access our SQL Server 2000 instance name: MASSQL.

We can access using SQL Authentication.

We tried making sure the user that runs the service has SSPI read and Write
We tried creating a new user
We tried running the service using NETWORK SERVICE account
We tried running as Local System Account
We tried creating a new Domain Admin account and run service as the new Domain Admin account

I also ran the following command against the problem SQL Server instance:

setspn -L xsql2

Results

Registered ServicePrincipalNames for CN=MASSQL,CN=Computers,DC=ABC,DC=com:
        HOST/MASSQL
        HOST/MASSQL.ABC.COM

I ran the following command against a non-problem SQL instance:

setspn -L xensql1

Results

Registered ServicePrincipalNames for CN=XENSQL1,CN=Computers,DC=ABC,DC=com:
        WSMAN/XENSQL1
        WSMAN/XENSQL1.ABC.com
        RestrictedKrbHost/XENSQL1
        HOST/XENSQL1
        RestrictedKrbHost/XENSQL1.ABC.COM
        HOST/XENSQL1.ABC.COM

It seems the results for the non-problem instance have more data.  I do not know if this has to do with the fact one is 2000 and the other is 2012.

What likely happened?  How do I allow domain users login again?


texts:
I'm using php and the exec() function to run 2 MySQL script files one after another

The first file has approx 2k update statements (see below) in and is 114kb in size.

UPDATE table1 SET is_active = 1 WHERE id = 43;

The second has approx 15k update statements (see below) in and is 817kb in size.

UPDATE table2 SET is_active = 1 WHERE id = 12345;

The id column in table1 and table2 is a primary key so I would've thought this should be quite a quick update.

When I watch the MySQL Workbench dashboard for number of updates per second the first file runs at approx 80/s and the second 5k/s.

This means the smaller file with less updates is running much slower than the larger one.

Does anybody know if there is a logical reason for this?


texts: Convert SQL Server results to a text file that PowerShell can access and use

texts:
The query below is used to create a text file with a list of server names.

The problem I have is that the text file gets created like

Server, Server, Server etc.

but I need it as follows

Server
Server
Server

The query:

BEGIN
    CREATE TABLE [TempPSWmiObjectQuery]
                (QueryText nvarchar(max))
END

INSERT INTO [TempPSWmiObjectQuery]

SELECT STUFF((SELECT char(13)+char(10),
             SUBSTRING(ServerName, 1,
             CASE WHEN CHARINDEX(',', ServerName) > 0
                  THEN CHARINDEX(',', ServerName)-1
                  ELSE LEN(ServerName)
             END)
FROM MasterList
FOR XML PATH('')), 1, 1, '')

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"'

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

DROP TABLE TempPSWmiObjectQuery

The PowerShell Script:

        $StagingServer = "ServerName\InstanceName"
        $StagingServerDatabase = "SQLEnv"

    foreach($line in
    [System.IO.File]::ReadLines("\\ServerName\Data\temp\testlist.txt"))
    {
        $line
        Get-WmiObject win32_Service -Computer $line  | where {$_.DisplayName
    -match "SQL Server"} | select SystemName, DisplayName, Name, State,
    Status, StartMode, StartName | ConvertTo-DbaDataTable | Write-
    DbaDataTable -SqlInstance $StagingServer -Database
    $StagingServerDatabase -Table _SQLServicesInfo -AutoCreateTable -Confirm

    }


texts: Combine indexes to optimize "OR" queries

texts:
I know that since version 8.1 Postgres can use multiple indexes for queries with or at query time.

I wonder if it is possible to combine multiple columns into one index column at index time. That is if I have a query:

select *
from contacts
where firstname = 'Cassidy'
or lastname = 'Cassidy'
or nickname = 'Cassidy'

Can I create an index that unions all the values from firstname, lastname and nickname so that this search (possibly with an adapted query) becomes a single index lookup?


texts: Using parameters in stored procedure with plpgsql

texts:
I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs:

Error is "SQL state: 42601", syntax error at "$1".

Why is this? I'm a total beginner here and I can't find the solution online, other than that parameters is referenced to with $ sign.
CREATE OR REPLACE FUNCTION any_overlap (x text)
RETURNS integer AS $$
DECLARE amount INTEGER;
BEGIN
    SELECT COUNT(*) INTO amount FROM $1 a
    INNER JOIN $1 b ON
    (a.polygon && b.polygon AND ST_Relate(a.polygon, b.polygon, '2********'))
    WHERE a.ctid != b.ctid;
    RETURN amount AS id;
END; $$ LANGUAGE plpgsql;


texts: Write lock during mysqldump even using --lock-tables=false

texts:
When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?

As I am using MyISAM, does --single-transaction for InnoDB help?

Also, is Master-Slave architecture the ultimate method to handle backing up online DB?

Thanks!