texts: How to set up Oracle 19c and/or RHEL so new files created using UTL_FILE have -rw-rw-r-- (664) permissions on Linux

texts:
Working on getting a 19c installation working on RHEL 7.8. The database works, but we're having a problem with files created using the UTL_FILE package. They're being created with -rw-r--r-- (644) permissions - but we need them to be -rw-rw-r-- (664). This works fine for us on our current system (Oracle 11.2 on HP-UX), but of course no one who currently works for the company was involved in the earlier installs and setups, yada-yada, etc-etc. Sysadmins are playing with umask settings but my understanding (which is shaky) is that umask can be used to turn permission bits off but not on. Just to make it a little more fun, the database server is separate from the file/application server, with the files created by UTL_FILE being made available on the app server via NFS. However, I'm given assurances by our DBA's that the files on the database server have 644 permissions (I do not have access to the database server) which is what we're seeing on the app server side.
Can anyone suggest a way to have 19c UTL_FILE create files with permissions set to 644?


texts: Migration From Oracle to PostgreSQL Encoding Errors

texts:
I'm working on migrating data from an oracle database to a postgresql database. I running into a problem where I export the data from oracle, using sql developer, and importing it into postgresql. When I try to import the data into postgresql I get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcb 0xcf
CONTEXT:  COPY project, line 646

********** Error **********

ERROR: invalid byte sequence for encoding "UTF8": 0xcb 0xcf
SQL state: 22021
Context: COPY project, line 646

The data in the oracle server looks like this in sql developer:

Ã9ÃýÃ0Ã

When sql developer exports the data to a file, it looks like this:

When I try to set the encoding in sql developer when I export the data I get this loading menu item:

and it doesn't change.

What do I need to do to properly export and import the data from oracle and postgresql?


texts: How can I inspect the security settings of a SQL Server Endpoint?

texts:
I am in the process of creating a TEST environment for our SQL Server development staff.

In production we have 3 SQL Servers, SQL01 contains several databases that are mirrored to SQL02.  SQL03 acts as the witness in a "high safety with automatic failover" or synchronous configuration.

I've used VMWare P2V to virtualize all three machines onto separate hardware, reconfigured the SID's of the machines, and blackholed the IP addresses of our production servers from these new machines.

I had initially forgotten to blackhole the production witness machine, so the databases on the TEST machines were still using the SQL03 machine as the witness.  Noticing the issue, I decided to reconfigure the databases on TEST to point to the newly virtualized TEST witness, call it TEST03.

To reconfigure the database to use the new witness, I entered the following command on the Primary server TEST01:

ALTER DATABASE [TestDB] SET WITNESS = 'TCP://TEST03.domain.inet:5022';

The response was unexpected:

The ALTER DATABASE command could not be sent to the remote server instance
'TCP://TEST03.domain.inet:5022'.  The database mirroring configuration was
not changed.  Verify that the server is connected, and try again.

I was very perplexed at this error message since the configuration does work on the production machines, and has not been modified in any way on the test machines.

In order to get this to work I needed to create a LOGIN on the test witness:

CREATE LOGIN [DOMAIN\SQLServiceAccount] FOR WINDOWS WITH DEFAULT_DATABASE [Master];

and GRANT it CONNECT rights on the endpoint in question:

GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\SQLServiceAccount];

I was then able to successfully point the mirrored databases on the TEST environment to the new test witness.

How can I inspect the production witness endpoint to see what security is associated with it?

I assume there must be some system catalog that I can inspect, however Books-on-Line does not seem to have anything specific for Endpoints, and Bing is well, Bingless...

Additional Info:

SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id
WHERE class = '105';

returns:

endpoint_id class_desc  permission_name endpoint_name   principal_name
2           ENDPOINT    CONNECT TSQL    Local Machine   public
3           ENDPOINT    CONNECT TSQL    Named Pipes     public
4           ENDPOINT    CONNECT TSQL    Default TCP     public
5           ENDPOINT    CONNECT TSQL    Default VIA     public

And:

SELECT name, endpoint_id, protocol_desc, type_desc, role_desc
FROM sys.database_mirroring_endpoints;

Returns:

name        endpoint_id  protocol_desc  type_desc           role_desc
Mirroring   65536        TCP            DATABASE_MIRRORING  WITNESS

There appears to be no entry in sys.server_permissions for the mirroring endpoint object.  No major_id and no minor_id matches 65536.  Also, none of the system databases contain any reference to the endpoint.

I'm at a loss.


texts:
I want to save an array in my database friends column.  Actually I want to create friend request system.  My method is a little bit different.  I want to have two columns for this system.

______________________________________________________________________
| Name   | Friends                   |  Requests                     |
| Name1  | array(person1,person2..)  |  array(request1, request2..)  |
| Name2  | array(person1,person2..)  |  array(request1, request2..)  |
|________|___________________________|_______________________________|

When name1 adds name2 the name1 name must be put in requests of name2 when name2 accepts the requests the name1 will be removed from name2 requests and added to both name1 and name2 friends.

Now the problem is that I have no idea how to put an array into a column cell and how to get the name when needed from it by a select query.  I have just worked with simple queries a lot but not this kind of thing.


texts:
I am facing issues related to constantly growing log file due to which I am getting error.
When I checked SQL Log I found below messages (error log is filled of these messages almost 90%)

  SQL Server has encountered 1 occurrence(s) of I/O
          requests taking longer than 15 seconds to complete on file

This is happening almost for all databases, including temdb [.mdf and .ndf files]
along with that I am getting below messages also

  average throughput:   0.34 MB/sec I/O saturation: 196 context switches
  1210

  last target outstanding: 530 avgWriteLatency 2

  FlushCache: cleaned up 6233 bufs with 384 writes in 142370 ms (avoided
  99 new dirty bufs) for db 6:0

My temdb size and other database and log file size is big enough.

History:

Previous developers had done both database shrinking and log shrinking many times.
All database is in compressed mode.

My action plans:

I have found that log file initial size is small with 10% growth. I am planning to increase the initial size by 512 MB with 512 MB growth to have reasonable number of VLF's.

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?

Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?
I am planning to remove all database and log files from antivirus check.
I am planning to change target recovery time to < 1 minute of data base (specifically tempdb and my database)

Question 3: Is this going to impact my database? I mean flushing buffer and writing to disk should improve performance, right?

Question 4: I have three data file for tempdb already have 3 data files. As per Microsoft recommendation, do I have to increase my temdb data files/log files? My current processor configuration is mentioned below

Question 5: Am I going in wrong direction? Is there is something which I am missing that should be done to rectify this issue?

Edit Why I believe this is related to Log file size?

If there is an IO issue then read write ability of SQL will decrease, this will result in long running transaction hence cause large log file size.


texts:
Hello I need to create a constraint that chekc the user is 18< before listenning to certain songs but idk how... I tried this :

ALTER TABLE Historique_ecoute
DROP CONSTRAINT IF EXISTS majorite_utilisateur
GO

ALTER TABLE Historique_ecoute
ADD CONSTRAINT majorite_utilisateur
CHECK (not (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)
       AND REFmusic = ( select IDmusic FROM Catalogue Where censure = 'YES') )

But I get thoses messages :
Msg 1046, Level 15, State 1, Line 244
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 4104, Level 16, State 1, Line 255
The multi-part identifier "Catalogue.annee" could not be bound.


texts: SQL Server takes an hour to fail over to secondary node

texts:
I am at a new job, we have a 2008 R2 (SP1) WSFC which hosts SQL Server 2008 FCI.

Each node has it's own storage, nodes are 32 core with 32 GB memory (don't ask me why it is configured this way).   The storage at each node is a read only copy when the node is secondary. Until today I was told that it was shared storage. I spoke with the sys admin about it because the quorum has a file share witness not a disk witness. He then explained that it does have it's own storage at each site but it is not active on the secondary node.  Blocks of data are replicated between the primary and secondary nodes.

What's happening is that the host is failing over (unclear as to why), the most likely culprit is heartbeat timeout.

When the nodes fail over SQL Logs that it was sent a shutdown command.

Sometimes the second (SQL) node comes up within a minute or so.

Other times it takes an hour for SQL Server to come online on the secondary node.  On the OS Side the failover is quick, it's just SQL Server that takes and hour.

My thought is that something was running.

I have not found any logs which indicate anything like this though.

In this reactionary state is there anything I can look at within SQL such as a system DB log or table (error logs have nothing after the shutdown command issued until the other instance comes online)?

I've not had this issue before and I have been scouring Google to no avail.


texts: Difference between clustered index and partitioning column

texts:
As I understand partitioning, the column that you partition on is how things are divided into partitions.

But does the partitioning column have anything to do with order on the disk? (Aside from which partition the data goes into.)

Say I have a table that looks like this:

CREATE TABLE [dbo].[Something](
    [SomethingId] [bigint] IDENTITY(1,1) NOT NULL,
    [OtherThingId] [bigint] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedWhen] [datetime] NOT NULL,
    [CreatedWhere] [varchar](255) NOT NULL,
     CONSTRAINT [PK_Something] PRIMARY KEY CLUSTERED
    (
        [SomethingId] DESC
        [OtherThingId] DESC,
    )
) ON OtherThingIdPartitionScheme(OtherThingId)

The partition that a new row will be stored on is decided by OtherThingId.

But I setup the clustered index to be first SomethingId, then by OtherThingId.

Does that mean that each partition will be ordered by SomethingId, then by OtherThingId?
(Even though it is partitioned only by the secondary value of OtherThingId.)

(I am setting up a lot of partitioning right now and I want to be sure I fully understand it.)


texts:
I want to alter a table to include modified and created columns. However, I am having trouble with adding the modified column. I get the following error:

  SQL Error: ORA-00907: missing right parenthesis
  00907. 00000 -  "missing right parenthesis"

I was reading this Oracle document (http://docs.oracle.com/cd/E17952_01/refman-5.5-en/timestamp-initialization.html) to get an example on how to properly write the ON UPDATE syntax

Here is my SQL.

ALTER TABLE FOOBAR
ADD (
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Why am I getting a missing right parenthesis error?


texts: MySQL - Can we use the same temporary table name in different stored procedure?

texts:
In our database, we generate statistics by periodically calling a large number of stored procedures in the event.

Considering there are two SP

auto_update_daily_users
auto_update_daily_orders

Is there a conflict when using the same temporary table name (lets say temp1)in both stored procedures?

I checked Q&A on Stack Overflow, they say the temporary table's scope is the current session. I think it means when we connect the db server with clients.

But in my case all the sp are running on the server side. So could anyone explain this?

Will the temporary table be automatically destroyed after one SP is finished? What if two SPs are running at the same time? Aren't they running in the same session?


texts: Table column length is fine in MySQL 5.5 and 5.6, but upgrading to 5.7 results in too many columns

texts:
So I have a CMS that is being upgraded from MySQL 5.5 -> 5.7. One table has a 1000+ columns, but it works fine in 5.5 and 5.6. When reaching 5.7, queries involving this table result in a 1117 error. (all tables in DB are MyISAM).

It works fine in MySQL 5.5 (the related CMS works fine). I upgrade to MySQL 5.6 and everything continues to work fine.

Then when I upgrade to MySQL 5.7(.23), I get an error about too many columns, and even after removing 20+ columns, I still get the error.

What could have changed? I upgraded in order (5.5 -> 5.6 first, tested, then 5.6 -> 5.7)

Is there possibly a setting I'm missing? Does the mysql_upgrade for 5.7 change columns somehow and screws up their limit by adding extra meta data that causes each column to take up more data? I know that column limit is theoretical but I don't know why it works in the previous two versions and not in 5.7.

Any thoughts?

Edit

After removing ~300 columns, the error stops. But this is a show stopper, and I'd really like to know why 5.7 can't handle what 5.5 and 5.6 can, and if it is a configuration issue or something. Thanks for any help.


texts: Help improving SQL Server 2005 query execution time

texts:
One of my stored procedure has long execution time (average around 4 to 7 minutes). I want to make it run faster.

I am looking at the execution plan and two things are contributing most of the cost. First represents 68% of the cost with a "Clustered Index Scan" of one main tables for reporting. This table has primary key of two columns and 2.000.000 records inside.

Second most demanding task represents 26% of the cost with a "Merge join" (left outer join). This is joining table I mentioned above via a foreign key to one out of the two columns of primary key in above table.

I checked Index physical statistic and index for these tables are in good condition.

What can I do to increase performance of this stored procedure? Do I need to make new index on these tables? Would set transaction isolation level read uncommitted statement help to improve performance?


texts: tuning postgresql for large amounts of ram

texts:
I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm etc).

On the one server, I am running sql server 2005, on the second server postgresql 9.1. The difference in performance b/n these 2 servers is staggering, it's so bad on postgresql that I'm regretting my initial "let's use postgresql instead of paying for the sql server license" speech to my boss. We're talking differences of 30 seconds vs 15 mins for the same command, and it's not just this one command, it's any query or command I throw at it. They both have pretty much the same data (records were inserted in different order), and both databases have the exact same structure / indexes etc.

But I'm hoping it's just a matter of performance tuning. The thing is, sql server is pretty much using all 32 gigs of ram on the server, whereas postgresl is using nothing, definitely less than a gig though I haven't actually figured it out in fine detail.

How do I get postgresql to use 20+ gigs of ram? These servers were built specifically for this database stuff, so any ram not in use by the database and supporting processes is wasted in my opinion.


texts: LEFT OUTER JOIN-ing the same table, grouping, summing and finding 0 or NULL

texts:
I have a table where I record searches performed on my site, tbl_search. An id is automatically assigned to entries. If the searched phrase is more than one word, my system splits the words and performs another search on each of them, setting a column - called original - to the original search id.

For instance:

A user searches for 'foo bar'
The system searches for 'foo bar', gets the id that was created when recording that search, let's say the id is 5.
So now we have a record like this

 id | phrase   | results | original
----+----------+---------+----------
  5 | foo bar  | 0       | NULL

The system searches for 'foo' and 'bar' by themselves, and records the original id. So now the table looks like this:

 id | phrase   | results | original
----+----------+---------+----------
  5 | foo bar  | 0       | NULL
  6 | foo      | 0       | 5
  7 | bar      | 0       | 5

Any phrase with an original value was not specifically searched for, and the results should be grouped, summed, and counted as results for the original search.

I want to know all the searched phrases that got ZERO results, and order them based on how many times they were searched. Here's my SQL:

 SELECT
      s1.phrase,
      COUNT(s1.phrase) as count,
      SUM(s2.results) as sub_results
 FROM tbl_search s1
 LEFT OUTER JOIN tbl_search s2 ON s1.id = s2.original
 WHERE s1.results = 0
 GROUP BY s1.phrase, s2.phrase
 HAVING SUM(s2.results) = 0
 OR SUM(s2.results) IS NULL
 ORDER BY count desc

I'm very close I think, but I'm getting something like this:

 phrase  | count | results | sub_results
---------+-------+---------+------------
 foo bar | 14    | 0       | NULL
 map     | 5     | 0       | 4

I would expect any multiple-word searches to return 0 for sub_results, and any single word searches to return NULL, however, it seems like the opposite is happening. Like this:

 phrase  | count | sub_results
---------+-------+-------------
 foo bar | 14    | 0  // multiple-word search with no results, even in the aggregate sum of sub results
 map     | 5     | NULL // single-word searches have no sub searches, so they will be NULL

EDIT:
Sorry, StackExchange is being partially blocked by my company's firewall so I can't add any comments. @ypercube I don't want to count the sub searches (because they're not performed by the user) in the count. The count should only be how many times that phrase was searched by a user.

Basically I want all searches that got no results. If a search had sub searches with results, then I'm not worried about that because to the user, they got results.

Also, I don't want distinct id's because that would not be an accurate count. It should group the s1.phrases together and ignore the ids.

I made an SQLFiddle: http://sqlfiddle.com/#!3/a2913/2


texts:
We have a sharded mongo cluster. We have configured the chunk size to be 256M but they are ~1.5G each. There are no jumbo chunks as our shard key is well balanced (we have also checked for them specifically).
What can be the cause of having bigger than configured chunk sizes? And what can I do to auto split them?

EDIT

Shard Mongo at Mongo/
 data : 416.39GiB docs : 134529720 chunks : 368
 estimated data per chunk : 1.13GiB
 estimated docs per chunk : 365569

Shard db3repl at
 data : 402.97GiB docs : 129491879 chunks : 372
 estimated data per chunk : 1.08GiB
 estimated docs per chunk : 348096

Shard db4repl at
 data : 396.18GiB docs : 127334616 chunks : 368
 estimated data per chunk : 1.07GiB
 estimated docs per chunk : 346017

Shard db5repl at
 data : 43.89GiB docs : 14141333 chunks : 368
 estimated data per chunk : 122.14MiB
 estimated docs per chunk : 38427


texts: Creating a database structure for a quiz

texts:
My app has this menu:

TestMode - make a quiz with 26 rndom questions from the database with a timer of 30 minutes.
LearningMode - make a quiz with all the questions from my db, displaying the correct answers.
Statistics- display % of quiz passed and the questions with wrong answers (displaying the correct answers).

each question have 3 choices(1 choice can be true, 2 choices can be true, or all can be true).
question can have a picture or not.

For now my db structure looks like this:

I don't know if is better if I put answers in questions; and if I do that, how to store the correct answer.


texts: Using a sequence created in the same script in Oracle

texts:
SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective.

I've got a script I've written which:

Checks ALL_SEQUENCES to see if a sequence of a given name exists
If it doesn't creates it using EXECUTE IMMEDIATE
Attempts to use the sequence as part of an insert

The idea is that the script can be run multiple times without modification so the person running it doesn't have to know what's in place and what's not.

My issue is that in SQL Developer if the sequence doesn't exist, the script errors on running claiming that the sequence doesn't exist. Create the sequence and the error goes away but that rather defeats the point.

What's the best way around this?


texts: MySQL 8.0.15 starts normally but any connection hangs

texts:
I dropped a large table (80G) Friday morning and got a crash:

InnoDB: ###### Diagnostic info printed to the standard error stream
2019-04-19T18:48:55.445507Z 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 37617336 seconds. We intentionally crash the server because it appears to be hung.[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2019-04-19T18:48:55.445541Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:625 thread 140252955932416
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:48:55 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

...lots more...

The server came back up and the log indicated it was ready for connections:

...

2019-04-22T12:53:51.792574Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 26956
2019-04-22T12:53:54.631860Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-04-22T12:53:54.639196Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-04-22T12:53:54.708512Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-04-22T12:53:54.754962Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15'  socket: '/data/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

I tried connecting locally and remotely with mysql client, various programs, etc., but cannot complete the connection process - it just hangs. The connection protocol initiates, but then just sits there after negotiation (this is from monitoring with tcpdump):

10:44:02.814237 IP 10.70.250.33.snip-slave > 10.70.250.50.mysql: Flags [S], seq 3534733756, win 14600, options [mss 1460,sackOK,TS val 2565739642 ecr 0,nop,wscale 7], length 0
10:44:02.814273 IP 10.70.250.50.mysql > 10.70.250.33.snip-slave: Flags [S.], seq 4211652446, ack 3534733757, win 28960, options [mss 1460,sackOK,TS val 239668057 ecr 2565739642,nop,wscale 7], length 0

...

10:44:03.042941 IP 10.70.250.50.mysql > 10.70.250.33.snip-slave: Flags [P.], seq 2784:2819, ack 858, win 252, options [nop,nop,TS val 239668286 ecr 2565739871], length 35
10:44:03.082582 IP 10.70.250.33.snip-slave > 10.70.250.50.mysql: Flags [.], ack 2819, win 160, options [nop,nop,TS val 2565739911 ecr 239668286], length 0

The mysqld process has been running at nearly 100% CPU, for over the weekend even, but nothing seems to change. I can't stop the server with systemd, as it'll just sit there too. I can (and have) killed the mysqld process several times today and started it back up, but again nothing seems to change. Most recently I did that to force innodb recovery, which shows in the log, but is no help since I still can't connect with anything.

Looking with iostat, it appears to be beating the daylights out of the general log (/dbdata/var/lib/mysql/data/mysql/general_log.CSV), which is quite large - over half a TB.

I don't really care about the table that was dropped, I just need the rest of the databases to be accessible, and I'm stuck.  Help, please!


texts: postgresql 9.1 failed insert occupies increasing disk space

texts:
I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and a few web interface that show inserted data (refreshing every few minutes).
There is an error in the data reported of a specific table that generates a foreign key violation and overnight i find that all disk space is filled up; looking at the relation size i find that all the space is associated to a table and its primary key but no entries are present in that table (see FK error above).
If i reindex the pkey and vacuum table the space is correctly freed.
But i wonder, what is filling up space on error? is there a way to prevent that?


texts: exec sp_execute followed by an int and other parameters?

texts:
I'm trying to figure out what is running on a SQL Server 2008 machine. When I profile the server the RPC:Starting shows various text data like this:

exec sp_execute 26,147964,122,N'43398.jtk'

exec sp_execute 27,1,147964,158

I would like to see what it's actually doing not these "prepared" statements. I've included the EventClass SQL:StmtStarting and a few others but there is no information other than the various sp_executes.


texts: Tree structure hierarchical query sorting

texts:
I have the following structure of table.

CREATE TABLE table1
(
  serial_num integer NOT NULL,
  parent_num integer,
  parent_key text,
  key_ary_num integer[],
  level integer,
  rank integer,
  tree_no text,
  subject1 text,
  subject2 text,
  ...
);

I need write a query for hierarchical sorting.

In the following sample, "tree_no" is drawn for expected sorting:
The same kind of sorting for all other columns also need to be sorted like parent child separate sorting. I've been trying with recursive.  Any other dynamic solution ?

Here I created SQL Fiddle link: http://sqlfiddle.com/#!12/b93f9/1

Thank you !


texts: SQL - Set a Flag Based on a Conditions Based on Multiple IDs

texts:
Working in SQL Server 2012 and trying to get the output below. I would like the Flag column (doesn't currently exist) to be 1 if the rows with the same primary ID have both Test A and B, and 0 otherwise. I manually entered the desired Flag values as an example. Does anyone know how to do this with SQL?

Any help would be greatly appreciated. Thanks!


texts: PostgreSQL: Storing large volume of photos and attached tags: many-to-many relationship or array of text?

texts:
This is my first post here and I am quite new to PostgreSQL - many apologies if my question may appear a bit unclear.

We set up a database for storing a large volume of photo-metadata with attached Hashtags/tags (about 500 Million photos and about 1 to two Billion Hashtags, about 50 Million of those distinct/unique).

We just started to experiment and we're unclear which path to follow for storing hashtags. Two possible options we examined:

Many-to-Many relationship

as suggested here and here: this would mean creating 3 Tables

  Table: Photos
  Columns: Photo_ID, Photo_Title, Content

  Table: Tags
  Columns: Tag_ID, Tag_Title

  Table: Photo_Tags
  Columns: Photo_ID, Tag_ID

Use an Array-Field and create a GIN index over it, as suggested here, here and here:

--adapted from the link above
CREATE TABLE "Photos"("Column1" text[]);
INSERT INTO "Photos" VALUES ('{tree, street, green}');
INSERT INTO "Photos" VALUES ('{tree, house, red}');

CREATE INDEX idx_photos on "Photos" USING GIN ("Column1");

-- To enforce index usage because we have only 2 records for this test...
SET enable_seqscan TO off;

EXPLAIN ANALYZE
SELECT * FROM "Photos" WHERE "Column1" @> ARRAY[tree];

Which one of the two approaches would be faster for our structure/volume?
We will need to query both
- either for for a single hashtag giving a list of (thousands of) photos
- or querying for thousands of photos with their respective tags attached

Thanks a lot for experience/suggestions!


texts: Convert datetime to europe default without time

texts: I have a datetime field that I need to convert into the format DDMONYY (27Aug12).  I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time.  How can I easily convert a date to the format I need?


texts:
I am evaluating Postgres 9.4 performance on a machine with Intel i7 quad-core 3.6 GHz CPU, 8 GB ram, and 7400 rpm HDD (no RAID) running Linux Mint. The DB schema has the following table:

          Table "public.sensor_readings"
  Column  |           Type           | Modifiers
----------+--------------------------+-----------
 time     | timestamp with time zone | not null
 value    | numeric                  |
 sensor_id| integer                  | not null

Indexes:
      "sensor_readings_pkey" PRIMARY KEY, btree (sensor_id, "time")

This table has 72 million rows and is 35 GB in size (PKEY index is 25 GB). sensor_id ranges from 0 to 5000.
I need to query sensor values for past two weeks:

SELECT
FROM sensor_readings
WHERE sensor_id IN (1,3,8,9,12)
    AND time BETWEEN CURRENT_TIMESTAMP - interval '14 day' AND CURRENT_TIMESTAMP ;

The problem is that the average query execution time is about 5 minutes even after a 2000-execution warm up. This is two orders of magnitude higher that what I want to achieve!

I did not change Postgres default parameters and did no optimization.

Can anyone suggest what can be wrong with my setup or schema? Are there any optimizations that I can do to minimize SELECT execution time? In particular, what is a proper ratio between table (index?) size and RAM?

P.S.

Sample EXPLAIN (ANALYZE, BUFFERS) output:

Bitmap Heap Scan on sensor_readings  (cost=3535.18..405031.07 rows=113904 width=0) (actual time=5190.213..60196.531 rows=103104 loops=1)
  Recheck Cond: ((sensor_id = ANY ('{1509,1504,1503,1500,1502}'::integer[])) AND ("time" >= (('now'::cstring)::date - '14 days'::interval)) AND ("time" <= ('now'::cstring)::date))
  Heap Blocks: exact=47786
  Buffers: shared hit=12 read=48491
  ->  Bitmap Index Scan on sensor_readings_pkey  (cost=0.00..3506.70 rows=113904 width=0) (actual time=5165.932..5165.932 rows=103750 loops=1)
        Index Cond: ((sensor_id = ANY ('{1509,1504,1503,1500,1502}'::integer[])) AND ("time" >= (('now'::cstring)::date - '14 days'::interval)) AND ("time" <= ('now'::cstring)::date))
        Buffers: shared hit=12 read=705

Planning time: 24.887 ms
Execution time: 60205.108 ms


texts:
I want to set up a Master-Slave Replication. Therefore I followed the MySQL manual. Once I was I wanted to test it. I've let me show the slave status with was as following;

Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 326
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'Test'' on query. Default database: 'FOOD'. Query: 'DROP TABLE `Test` /* generated by server */'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1410
              Relay_Log_Space: 2176
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1051
               Last_SQL_Error: Error 'Unknown table 'Test'' on query. Default database: 'FOOD'. Query: 'DROP TABLE `Test` /* generated by server */'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

As you can see, the slave was waiting. That's the reason, why I made a test INSERT on the MASTER. As the INSERT did not show on my slave it made me think, that I was missing something while setting it up. Also if I want to uncomment the bind-address and restart the mysql-service, it fails to start.

Now I'll show you what I have done:

1. Edit my.cnf on Master ->
set server-id = 1
set binlog_do_db = database name
2. Restart mysql service on Master
3. Create user for replication:
CREATE USER 'slave'@'master IPv4' IDENTIFIED BY 'password';
4. Grant replication slave:
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
5. Restart mysql service on Master

Now on to the slave:

1. Edit my.cnf on Slave ->
set server-id = 2
2. Restart mysql service on Slave
3. Stop slave
4. CHANGE MASTER TO MASTER_HOST='Master IPv4', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=827;
5. Start slave

After this configuration the slave was showing: Slave_IO_State: Waiting for master to send event, but id did not receive any changes from the Master. Would be glad if anyone could help me with this.

I use MySQL 5.5.47 and Ubuntu 14.04.1


texts: Dynamic change to innodb_flush_log_at_trx_commit

texts:
This is related to this question. It does help to get better performance for InnoDB tables.

According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change it using SET GLOBAL command and it seems to be working.

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set

But, it did not make the actual MySQL setting changed.
When I updated my.cnf and restarted the MySQL server,  it did work. So, I cannot change the global variable at run time?

I prefer the default value innodb_flush_log_at_trx_commit=1, but I need to change it to 2 before I run a restore process for a large database to get faster.
But when the process done, I want to change the value back to 1.
Is it possible to do this at run time?

I don't have access to my.cnf on my shared hosting server.


texts: NULL out value in table 1, depending on a time-log in table 2

texts:
I have two tables in MS SQL Server 2012. The first is a minute by minute production record which looks like (can't guarantee every single minute gets its own record, but never two records for one minute):

[myDateTime] [Produced]
12:01           100
12:02           110
12:03           115
12:04           100

The second table is a journal-style log of whether to consider the [Produced] column from Table 1 or not.

[myStartDateTime] [myEndDateTime]
     12:02            12:03
     12:47            12:58

I want to be able to look through each row of Table 1 and either take the [Produced] column, or take a NULL value, depending on if that minute falls within a record of Table 2 (ie between myStartDateTime and myEndDateTime INCLUSIVE).

In the example above, that would look like:

[myDateTime] [Produced]
12:01           100
12:02           NULL
12:03           NULL
12:04           100

I can't seem to get this to work (in code, or in my head). In my head I think I should join on myStartDateTime and join on myEndDateTime and make a new column called [Include] and set it to 1 or 0 depending on whether I want to include it or not. Each table might have 300 - 400 thousand rows and everything I am trying is slow.

Any help would be appreciated.


texts: What is the order when SELECT-ing unordered set from an ordered SELECT?

texts:
What does the SQL standard and its mainstream DB implementations (SQL Server, Oracle, DB2, PostgreSQL, Firebird, MySQL, etc.) say about the final order in the following general case:

SELECT * FROM (SELECT * FROM Table t ORDER BY t.Field)

Can I trust that the inner order is respected by the outer projection as well or do I need to state the ORDER BY in the outer statement explicitly, to be sure?

I understand the recommendation to "explicitly list it". However, explicit listing can cause problems in certain cases. PostgreSQL, for one, requires that all fields listed in ORDER BY be listed in SELECT DISTINCT as well. That's no good in some cases, because it changes the expected result set, which could no longer be distinct in light of the single column I'm interested in.


texts: How to pull number code from strings buried in parentheses?

texts:
I have string data (ProductDescription) that includes product names and ID numbers and need to pull the ID numbers for cross-referencing...

Data Samples:

Widget (1234)
Different Widget (123)
Many Widgets (2x) (4567)

I am trying to do this with a rather messy CASE statement:

CASE
    WHEN
        ISNUMERIC(
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE
                WHEN
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )) = 1
    THEN
        SUBSTRING(
            ProductDescription
            ,CHARINDEX('(',ProductDescription)+1
            ,CASE
                WHEN
                    CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1 > 0
                    THEN CHARINDEX(')',ProductDescription)
                        -CHARINDEX('(',ProductDescription)-1
                ELSE 0 END
            )
    ELSE 0 END

In cases where there is a parenthetic insertion in the product name (as in the "Many Widgets (2x)" example), I end up returning the "2x" value, which is not what I want.

How can I look for only numeric values contained in parentheses and discard the contents of others? Is there an easier/cleaner way to do the simple CHARINDEX work I'm currently attempting?

Thanks!


texts: How to optimize a query that includes counts of rows in multiple tables?

texts:
My query looks like this:

EXPLAIN SELECT ...

FROM USER u
LEFT OUTER JOIN
 (SELECT COUNT(*) notified_count, user_id
  FROM user_email_tracking ON u.id = email_tracking
  WHERE email_type_id = 4
  GROUP BY user_id) email_tracking ON u.id = email_tracking.user_id

JOIN ( SELECT user_id, count(*) as subscriptionCount
       FROM user_filter
       GROUP BY user_id) uf on u.id = uf.user_id

WHERE u.stage = 10

My intuition is this is pretty doable.  That u.stage = 10 narrows it down a lot. There's only a few hundred users who it applies to.  If I could narrow down by that first then perform all other work, seems like this query should take a few seconds.

But - user_filter is huge, and user_email_tracking is pretty big. (Both are indexed on their user_id columns). If those have to get performed on everything, it's much slower.  And I also must avoid joining user_filter against user_email_tracking on user_id, since that would be a huge times a huge table.

If I only had one count to do, I could move the group by all the way outside and just have a row for each row of user_filter (or user_email_tracking).

How to get SQL to do these things in the right order?

Engine is InnoDB.


texts: selecting the month day and year from a transaction table

texts:
i want to create two queries for my table which has fields name,surname and amount paid,the first query should select the day,month and the amount paid,the second query should select a month,year in that year and the total amount paid in that month,lets say john paid $200 on 2013-05-01,$400 on 2013-05-03,while peter paid $50 on 2013-04-08, i want the first query to output

month and day   amount
  05-01          200
  05-03          400
  04-08          50

and the second query should output

month and year   total
2013-05           600
2013-04            50

i know i can use the sum aggregate function to select the total but the tricky part is how to select the day and the month in the format above ,thanks in advance


texts:
I am building an app which allows users to do some "tasks" to earn points. The "tasks" are part of a "campaign" which themselves are a part of "brands". I am confused about the schema for storing the response of the users. This is what i have built. All tasks have some activities which a user needs to complete to complete the task.

{ _id : ObjectId,
  user_id : ObjectId,
  campaign : [
        { _id : ObjectId,
          campaign_id : ObjectId,
          brand_id : ObjectId,
          completed : Boolean,
          points : Number,
          tasks : [
             { _id : ObjectId,
               task_id : ObjectId,
               start_time : Date,
               rewards : points,
               responses : [
                     { _id : ObjectId,
                       activity_id : ObjectId,
                       response : Mixed,
                    }...]
             }...]
       }...]
}

Queries:
1. inserting new response and updating how many no. of people doing the campaign of the given task.
2. getting completed tasks and respective rewards of a user.
3. getting completed campaigns of a user.
4. updating a user's response to a task.
5. finding unique responses by all users for a given activity.

Problems :
1.The problem is that this schema contains nested arrays and mongodb doesnt allow me to use positional operators after level 1.

It would be helpful if someone can suggest a design which can perform these queries in a better way.


texts:
I have just created a new SQL Server with SQL Server 2017 (14.0.1000) and SSIS (14.0). I am able to remote connect with the admin user on that server whom I did all of the setup/configuration with, however my client is not able to. I went in to dcomcnfg and explicitly gave their user all of the necessary permissions, still they are unable to connect. I've already verified that we are using the correct version of SSMS (17.X) but I've also tried with SSMS 18 and 15 to no avail.

When trying to make the connection from a remote desktop the error message goes something like "The specified service does not exist as an installed service".
I've given the user sa credentials in SQL and full permission through DCOM for SSIS.

I am relatively new to this so it is possible I completely missed an obvious step here and I'd love some input on this, thank you.


texts: Cannot log in to Mongo from command line - using MongoDB 3.0 and WiredTiger

texts:
NOTE: I have tried not setting bind_ip and also setting it to 0.0.0.0 -- neither one fixed my issue.

This is my mongod.conf file:

systemLog:
    destination: file
    path: "/root/mongodb.log"
    logAppend: true

net:
    port: 25015

processManagement:
    fork: true

setParameter:
    enableLocalhostAuthBypass: true

storage:
    dbPath: /root/mongo_data
    engine: wiredTiger

The first thing I did was SSH in to the DigitalOcean droplet where it is running and do the following:

mongo --port 25019

I was able to connect, so I know it picked up the options in the mongod.conf file.

The second thing I did was issue the following command:

> use admin;
switched to db admin
> db.createUser(
    {
        user: "siteUserAdmin",
        pwd: "eqt-dfts-mad-ghbr-ovd-g",
        roles: [{role: "userAdminAnyDatabase", db: "admin"}]
    }
)
Successfully added user: {
    "user" : "siteUserAdmin",
    "roles" : [
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        }
    ]
}

So that was apparently a success!

Next I exited from the mongo shell and then logged in to the mongo shell again as siteUserAdmin with credential from above.
Then I created a user and a database for the app I'm building:

> use MyDatabase;
switched to db MyDatabase
> db.createUser(
    {
        user: "my_user",
        pwd: "cu-npp-re-jpor-mtw-e-s",
        roles: [
            {role: "readWrite", db: "MyDatabase"},
            {role: "dbAdmin", db: "MyDatabase"}
        ]
    }
    )
Successfully added user: {
    "user" : "vantage",
    "roles" : [
        {
            "role" : "readWrite",
            "db" : "AdVantage"
        },
        {
            "role" : "dbAdmin",
            "db" : "AdVantage"
        }
    ]
}

So that was also apparently a success!

I then exited the mongo shell and then logged in to the mongo shell again as my_user with credentials from (4).  I confirmed that I was able to create a collection called demotest in MyDatabase:

> use MyDatabase;
switched to db MyDatabse
> db.createCollection("demotest")
{ "ok" : 1 }
> show collections;
demotest
> show dbs;
MyDatabase 0.000GB
admin      0.000GB
local      0.000GB
> exit
bye

So far so good.

I then switched to a terminal on my local machine.  My goal is to be able to connect to this MongoDB instance from my local machine and from an app that is running somewhere else on the internet.  So I issued the following command on my local machine:

PROMPT> mongo --host 102.216.73.164 -u my_user -p cu-npp-re-jpor-mtw-e-s --authenticationDatabase MyDatabase --port 25019
MongoDB shell version: 2.6.6
connecting to: 102.216.73.164:25019/test
2015-05-13T21:03:18.963-0400 Error: 18 { ok: 0.0, errmsg: "auth failed", code: 18 } at src/mongo/shell/db.js:1210
exception: login failed

Here is what I don't understand:

why did it try to connect to the test database?
why doesn't it work?


texts:
On postgresql 9.4 I have a table that never has any updates or deletes, only inserts and selects.  It therefore never gets auto vacuumed, but it does still get auto analyzed and when it does it can take over 100 seconds.  For that 100 seconds and for a significant amount of time afterwards DB performance is terrible, with my app reporting db response times in the 40-150 second range instead of the normal 2ms range.

I've always heard that you shouldn't disable auto vacuum but when I set autovaccum_enabled=false for this table the db performs much more reliably. Is there a reason to have it enabled on this table?  Will the query planner eventually suffer from not having auto analyze running on a table that never gets updates or deletes? Is there a better way to solve this?

EDIT: Potentially modifying autovacuum_analyze_threshold and autovacuum_analyze_scale_factor for this table to make analyze happen more frequently would be better?


texts:
I'm trying to set up two PostgreSQL servers on one machine and perform a streaming replication. I have succeeded once, but when i've tried again following exactly the same steps it doesn't work.. Those are the steps:
I have $PGDATA = home/postgresql/9.1/data
and $STANDBY = home/postgresql/9.1/data2

Set up two nodes:
initdb -D $PGDATA
initdb -D $STANDBY
In the master node create a user for replication. I do that in pgAdmin (it does have superuser privileges)
In the master node in pg_hba.conf add the part that allows standby to connect:
host   replication     repuser         127.0.0.1/0            md5
In the master node in postgresql.conf set:max_wal_senders = 1
archive_mode = on
archive_command = 'cp %p ~/postgresql/backup/archivedir/%f'
wal_level = archive
wal_keep_segments = 32
Start the master node and do the base backup:psql -d dellstore2 -c "SELECT pg_start_backup('backup for replication',   true)"
rsync -av ${PGDATA}/ $STANDBY --exclude postmaster.pid
psql -d dellstore2 -c "select pg_stop_backup()"
pg_stop_backup says that everything is fine, all the WAL files were archived
In the standby (data2) node I create recovery.conf with:standby_mode = 'on'
primary_conninfo = 'host=127.0.0.1 port=5432 user=repuser password=haslo'
trigger_file = '/home/michau/postgresql/replication.trigger'
restore_command = 'cp /home/michau/postgresql/backup/archivedir/%f "%p"'
Start the master node, then start the standby node - replication should start and standby should catch up with the master. That was exactly what happened the first time.
Now when I start the standby I get: "Address already in use" error.
Of course both standby and master have the same port specified in postgresql.conf (they have exactly the same postgresql.conf files). If I change the port in standby to let's say 5433 then I get:
LOG:  database system was shut down in recovery at 2012-06-12 19:48:01 CEST
LOG:  entering standby mode
cp: cannot stat /home/michau/postgresql/backup/archivedir/000000010000000000000007: No such file or directory
LOG:  consistent recovery state reached at 0/7000070
LOG:  record with zero length at 0/7000070
cp: cannot stat /home/michau/postgresql/backup/archivedir/000000010000000000000007: No such file or directory
LOG:  streaming replication successfully connected to primary
LOG:  redo starts at 0/7000070

And it just hangs here. Running ps -ef | grep postgresql yields:
michau    2491  1898  0 19:46 pts/0    00:00:00 postgres -D /home/michau/postgresql/9.1/data
michau    2493  2491  0 19:46 ?        00:00:01 postgres: writer process
michau    2494  2491  0 19:46 ?        00:00:00 postgres: wal writer process
michau    2495  2491  0 19:46 ?        00:00:00 postgres: autovacuum launcher process
michau    2496  2491  0 19:46 ?        00:00:00 postgres: archiver process   last was 000000010000000000000008
michau    2497  2491  0 19:46 ?        00:00:00 postgres: stats collector process
michau    2571  2214  0 19:49 pts/1    00:00:00 postgres -D /home/michau/postgresql/9.1/data2
michau    2572  2571  0 19:49 ?        00:00:01 postgres: startup process   recovering 000000010000000000000009
michau    2575  2571  0 19:49 ?        00:00:01 postgres: writer process
michau    2578  2571  0 19:49 ?        00:00:02 postgres: wal receiver process   streaming 0/99782DC
michau    2579  2491  0 19:49 ?        00:00:00 postgres: wal sender process repuser 127.0.0.1(42142) streaming 0/99782DC
michau    2586  2491  0 19:51 ?        00:00:00 postgres: michau postgres ::1(49941) idle
michau    2587  2491  0 19:51 ?        00:00:01 postgres: michau dellstore2 ::1(49942) idle

The recovering 0000000010000009 where changing for a while, but for half an hour it doesn't anymore.

I'm sure there is something I must have done the first time and not written down or something, but I am at a complete loss to say what it was. I would appreciate any help.


texts: SQL Server: how to determine a column algorithm encryption

texts:
I have a table with a column, Column_Password, where I can see the password encrypted.

How can I know what type of algorithm encryption the column is using? How can I find if it's sha1, MD5, sha256 or whatever?

My goal is to check if the password is present in this massive database


texts:
I tried creating a user with all privileges but I am getting this error:

  Microsoft SQL Server Management Studio

  Create failed for User 'Hru'. (Microsoft.SqlServer.Smo)

  Additional information:

  An exception occurred while executing a Transact-SQL statement or batch.
  (Microsoft.SqlServer.ConnectionInfo)

  'Hru' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

Can anybody help me solve this?


texts: Can I have cross database or cross server references through a relative path?

texts:
I have two databases, db1 and db2, running on two different servers, server1 and server2, and db2 has some stored procedures that will modify the table columns on db1.

In these stored procs, we use Linked Servers and fully qualified names to refer to the tables on db1, like [server1].[database].[dbo].[table1].

Now, we decided to migrate db1 from server1 to server2. After migration, db1 and db2 will both run on server2.

Questions

Do I have to modify each single cross server reference to remove the [server1] name in the stored procs?
Could we define cross DB or cross server reference through relative path, so we don't need to modify them every time the two databases change server?

Thank you.


texts: How to get max value of a column for a second column

texts:
I have this code which produces the following result but I want to get the week_day for every customer with highest count. So instead of two or more rows with customer_id, I want one row with week_day with highest count for each customer_id.

SQL QUERY:

SELECT `sales_flat_order`.`customer_id`,
  dayname(created_at) AS `week_day`,
  COUNT(*) AS `cnt`
FROM `sales_flat_order`
group by week_day, `sales_flat_order`.`customer_id`
order by cnt DESC

RESULT:


texts: ORA-01017 connecting to example schemas on Oracle VM appliance from Windows host

texts:
I'm a software developer and am working on getting an Oracle VM Appliance running for testing purposes (the Database App Development VM with Oracle 12c - http://www.oracle.com/technetwork/community/developer-vm/index.html#dbapp). The host OS is Win 8.1 x64. I'm having trouble logging into the example schemas (HR, SH etc) from the host machine, with error ORA-01017. I've set up a tnsnames.ora file on the host and can connect as both SYS (as SYSDBA) and SYSTEM from there, so my basic setup seems fine (e.g. no firewall issues). The service alias is set to ORCL12C (the same as the SID on the VM).

the current situation is as follows:

Port-forwarding and Host-only network both set up in the VM.
From the Win8.1 host I can logon and run queries using both SQL*Plus and SQL Developer as SYS as SYSDBA/SYSTEM using either 0.0.0.0 (port-forwarding) or 192.168.56.101 (host-only) in tnsnames.
When I attempt a login to HR from the host using SQL Developer I get the ORA-01017 error (using "Basic" connection type with default role). This is the case for both SID and Service Name options.
A login attempt to HR from the host using SQL*Plus to HR@orcl12c also gives an ORA-01017 error.
Within the VM I can connect to the HR schema using SQL Developer only if I select the Local/Bequeath connection type, but it fails with the ORA-01017 error if I use the Basic connection type. I can connect to SYSTEM or SYS/SYSDBA using the Basic connection type, however.
Within the VM I can connect to HR, SH etc using SQL*Plus with no problems.
I'm using the instant client on the host.

So there appears to be something I'm missing when using the Basic connection type with standard users in SQL Developer, both within the VM and from the host (possibly related to the SQL*Plus failure from the host?). I've done extensive web searching but can find nothing to solve this. Thanks.


texts: Get the first and last names of all employee who entered the project at the same time as at least one other employee

texts:
SELECT
    EmpFname
    ,EmpLname
FROM
    Employee_m
WHERE
    (
    SELECT
        COUNT (w1.EnterDate)
    FROM
        WorksOn_m w1,WorksOn_m w2
    WHERE
        w1.EnterDate = w2.EnterDate
    ) >= 1

I tried this but the output gives all the names as the same row is compared. Any way around this?

my data:


texts: SQL Server Audit Records Non-Committed Deletes

texts:
I have a table with a trigger as follows:
CREATE TABLE AuditTable
(
    RecordTime DATETIME,
    RecordDetail NVARCHAR(100)
);

-- trigger to block deletes and updates
CREATE TRIGGER TRG_Block_Deletes_and_updates
ON AuditTable
INSTEAD OF DELETE, UPDATE
AS
    SET NOCOUNT OFF;
    RAISERROR ('ERROR - Deletes and Updates are not allowed on this table',16,1);
GO

The idea of the trigger is to prevent accidental modifications to the table (by a sysadmin)
I also have a SQL Audit set up which will log any deletes / updates against the table:
CREATE SERVER AUDIT [MyAuditTest]
TO FILE
(   FILEPATH = N'C:\Test2\Audit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE -- notes here
);
ALTER SERVER AUDIT [MyAuditTest] WITH (STATE = ON);

USE [MyTestAuditDB];
CREATE DATABASE AUDIT SPECIFICATION [MyDatabaseAudit]
FOR SERVER AUDIT [MyAuditTest]
ADD (DELETE ON OBJECT::[dbo].[AuditTable] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[AuditTable] BY [public]);
ALTER DATABASE AUDIT SPECIFICATION [MyDatabaseAudit] WITH (STATE = ON);

If I run a delete against a table: DELETE FROM AuditTable WHERE RecordDetail LIKE 'a%';
I get the expected error message:
Msg 50000, Level 16, State 1, Procedure TRG_Block_Deletes_and_updates, Line 8 [Batch Start Line 0]
ERROR - Deletes and Updates are not allowed on this table

and the data in the table does not change.
however, the SQL audit still logs the DELETE even though it never heappened.
I have also tried a ROLLBACK at the end of the trigger with no effect
Is there a way to change this behaviour so that the audit only audits committed deletes?


texts:
I am using a PostgreSQL 10 server, in which I have some tables containing attributes of type double precision[] intended for storing 1D arrays of data, of length ~1000. My code interacting with the database contained a bug that inserted data as an array of length-1 arrays (see my psycopg bug report where I eventually worked out that this is what was happening), but this was permitted by the DBMS:

  The current implementation does not enforce the declared number of
  dimensions either. Arrays of a particular element type are all
  considered to be of the same type, regardless of size or number of
  dimensions. So, declaring the array size or number of dimensions in
  CREATE TABLE is simply documentation; it does not affect run-time
  behavior.

How can I fix these misshapen arrays within the DBMS (i.e., using SQL rather than updating from external code)? I essentially wish to just flatten each array into a 1D array as originally intended.

A snippet of the array data as returned by psql is:

{{2.20751909662576},{2.20679071024511},{2.20615506273571},{2.2055910715332},{2.
20507756148068},{2.20459435596551},{2.20412336646322},{2.20364958013081},{2.2031
618420891},{2.20265334228114},{2.20212174686242},{2.20156895755458},{2.201000532
12516},{2.20042484573123},{2.19985210697606},{2.19929335742568},{2.1987595765431
},{2.19826098783408},{2.19780662274576},{2.19740415504621},{2.19705997866427},{2
.19677947299938},{2.1965673849541},{2.19642825612809},{2.1963668334181},{2.19638 ...


texts:
I'm running a query that runs in about 2 seconds when the subquery does not include the order by date_sold clause. However, when I add the order by clause, the query runs forever.

I'm confused, since it seems that I'm using a index for both queries, and as I've understood, the subquery should be able to use the index for the subquery. Any ideas where I went wrong?

EXPLAIN Select buyer_id as bid,
              (SELECT seller_id
               from sale USE INDEX(tester)
               where buyer_id = bid
               order by date_sold LIMIT 1)
         from sale where seller_id = 35514335;
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
| id | select_type        | table | type  | possible_keys                     | key    | key_len | ref   | rows  | Extra                    |
+----+--------------------+-------+-------+-----------------------------------+--------+---------+-------+-------+--------------------------+
|  1 | PRIMARY            | sale  | ref   | seller_id,seller_id_index_asc,sub | sub    | 8       | const | 14933 | Using index              |
|  2 | DEPENDENT SUBQUERY | sale  | index | NULL                              | tester | 24      | NULL  |     1 | Using where; Using index |
enter code here

The table structure

| id               | int(11)    | NO   | PRI | NULL    | auto_increment |
| seller_id        | bigint(20) | NO   | MUL | NULL    |                |
| buyer_id         | bigint(20) | NO   | MUL | NULL    |                |
| date_acquired    | bigint(20) | NO   |     | NULL    |                |
| date_sold        | bigint(20) | NO   | MUL | NULL    |                |
| brand            | text       | NO   | MUL | NULL    |                |
| muncipality_code | bigint(20) | NO   | MUL | NULL    |                |
| new              | int(11)    | YES  |     | NULL    |                |
| car_regnumber    | varchar(6) | YES  |     | NULL    |                |
| gender           | varchar(1) | YES  |     | NULL    |                |

Tester index

| sale  |          1 | tester              |            1 | date_sold        | A         |       14840 |     NULL | NULL   |      | BTREE      |         |
| sale  |          1 | tester              |            2 | buyer_id         | A         |    11768564 |     NULL | NULL   |      | BTREE      |         |
| sale  |          1 | tester              |            3 | seller_id        | A         |    11768564 |     NULL | NULL   |      | BTREE      |         |

SHOW CREATE TABLE SALE

| sale  | CREATE TABLE `sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) NOT NULL,
  `buyer_id` bigint(20) NOT NULL,
  `date_acquired` bigint(20) NOT NULL,
  `date_sold` bigint(20) NOT NULL,
  `brand` text NOT NULL,
  `muncipality_code` bigint(20) NOT NULL,
  `new` int(11) DEFAULT NULL,
  `car_regnumber` varchar(6) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `muncipality_code` (`muncipality_code`),
  KEY `brand` (`brand`(10)),
  KEY `seller_id` (`seller_id`),
  KEY `seller_id_index_asc` (`seller_id`),
  KEY `date_sold` (`date_sold`),
  KEY `tester` (`date_sold`,`buyer_id`,`seller_id`)
) ENGINE=MyISAM AUTO_INCREMENT=66390336 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |


texts: Can Oracle Text query return the terms found?

texts:
I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'people OR country ', 1) > 0;

Is there a way to see what terms in the "contains" statement that Oracle found in the documents?


texts: MS SQL - export tables to format compatible with PostgreSQL

texts:
We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing.

MS SQL Server has an export function but the only one that looks like it would work would be to export to Excel files. I'd rather use a SQL script or function that can export to a file and import those files into a PostgreSQL server for testing. I've searched a number of sites for a SQL script but I can't find one that will work.

What would be a good approach to accomplish this?


texts: How to handle update or insert situations

texts:
From time to time my stored procedures looks like

create procedure handle_data
    @fk int,
    @value varchar(10)
as
begin
    if exists (select * from my_table where id = @fk)
    begin
        update my_table
        set value = @value
        where id = @fk
    end
    else
    begin
        insert into my_table (fk, value)
            select @fk, @value
    end
end

Probably there is fault in design of app which calls this stored procedure.
Should I avoid making apps which do same stored procedure and methods for inserting new data and also updating old ones?

Is there better way to achieve updating or inserting data in one approach?

I am using SQL Server 2005 / 2008.


texts: row locking within ACID transaction innodb

texts:
I have a financial transaction that looks something like this (pseudo):

begin
    select record for update
    update record(same record as selected)
    insert another record into another table
commit

If I call SELECT ... FOR UPDATE within the transaction, will the lock be released for the specific rows only when the transaction is committed ?


texts: How to connect an oracle DB with MySQL workbench?

texts:
I am a bit confused:
I want to connect an Oracle DB with the application "MySQL Workbench". In order its from Oracle as well I throught that this is possible. But until now Im not able to do so.

With the "Oracle SQL Developer" is it working, so my credentials have to be correct.

Is it just impossible to connect an oracle db with mysql workbench?

kind regards
mario


texts: Conditional ORDER BY based on NULL values in the column

texts:
I have a table like this.
table(student_name nvarchar(20) not NULL, grades int).
When I write

select * from table
order by grades asc

It shows this

student_name | grades
---------------------
   Ann       |  NULL
   Bob       |  NULL
   Bob       |   10
   Jane      |   25
   Nelly     |   30

when select * from table
      order by grades desc

   student_name | grades
    ---------------------
      Nelly     |   30
      Jane      |   25
      Bob       |   10
      Ann       |  NULL
      Bob       |  NULL

But I need to order by like this. NULLs in the end but those who have grades order by ascending order, like

   student_name | grades
    ---------------------
      Bob       |   10
      Jane      |   25
      Nelly     |   30
      Ann       |  NULL
      Bob       |  NULL

How it is possible to do a conditional order by like this? How to specify NULL values to be at the end?


texts:
I'm trying to take advantage of the new backup to url function in SQL Server.  I have created a valid cert and tried a simple backup, but I get the same error every time no matter what I try.
I have also tried creating a new managed backup.  With the managed backup I see a new storage container but get no new blobs.  When I look through the logs it is getting the same error.

example:

BACKUP DATABASE [mydb]
TO  URL = 'https://mybackups.blob.core.windows.net/sqltest-mssqlserver/backup_2014_08_08.bak'
WITH
CREDENTIAL = N'AzureCreds' ,
STATS = 1
GO

Message:

Msg 3293, Level 16, State 1, Line 1
An error occurred while Backup/Restore to URL was initializing. Error message: Internal engine error, invalid parameters caused fatal error.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


texts: removing sql logins from one server, after migrating to another server, but all the databases were not migra

texts:
Here are the details of the production environment:

I have 2 serevrs, A1 and A2.

I have completely transfered 100 logins from server A1 to A2.

Server A1 contains 5 DATABASES. But I moved only 2 databases from A1 to A2 during the migration of databases from server A1 to Server A2.

I want to drop the users, who are not required on server A2, because of remaining databases which were not migrated on it.

Please help with any scripts or dmvs to identify which users are not required on server A2 and thereby dropping them.

If a user has access to multiple databases, how can we remove the login of that  user whose concerned database was not migrated from server A1 to A2.

Thanks in advance, any valuable suggestions would be helpful for our POC.


texts: LDF file size is very huge even after doing Transaction log backup

texts:
I work on a SQL Server 2016 Database and in our production environment we have primary SQL server and a fail over SQL server.
All the transactions are occurring on the primary SQL server.
In primary , i have Full Backup running every day at night and Transaction log backup running every one hour.
Transaction replication is enabled in Primary to fail-over server.

In fail over server there i didn't set up full backup and transaction log backup.Recently i got alert that the drive in which LDF file is stored in fail over server is full.

That is when it clicked to me,that in fail-over i am not running regular Full back and Transaction log backup.(Recovery Model is Full Recovery) which is causing LDF file to grow and not truncate.

So i did a full backup of the database and then a transaction log backup ,but the LDF file size is stil the same.So i did a database shrink operation(i know that shrinking is not recommended) but still the LDF file size is same.

How can i reduce the LDF file size?
Please help.


texts: How do I model the following requirement in ER-Diagram?

texts:
I'm making a story-composer application which allows users to write stories and other users can contribute to it. A story can have multiple storylines and multiple levels(think like a tree or thread structure). Following are the constraints :

When a user creates a story, they need to give it a title to the story and write a storyline.
Creating user can make the story public or private, if public, anyone can contribute to it, if private, the creator needs to invite specific users to contribute to it.
Bookmark Feature : There will be a checkbox besides each thread(storyline) and title.

If user checks the checkbox besides story title then the entire story is bookmarked or
User can bookmark specific storyline(s) by checking checkbox besides it.


texts: How i Implement Role Based Access Control in Mongodb

texts:
Currently I'm working on an ERP project. I want to set the Roles & Permissions via a RESTful Api. I'm using Mongodb and Nodejs. How can i implement this?

Roles & Permissions are as follows:-

  Level 4 Access - Covers all employees like mechanics, supervisors,
  etc. Can access only certain features.

  Level 3 Access - For Unit managers - Can access all features but
  require permission to edit / change.

  Level 2 Access - Admin - Can Edit / change / add almost all features

  Level 1 Access - Master Admin - Can edit / change / add all features


texts:
I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three tables.

This is my query

SELECT A.Name, B.Quantity, C.Quantity
FROM A
LEFT OUTER JOIN B
    ON A.ID = B.ID
LEFT OUTER JOIN C
    ON B.No = C.No;


texts: Missing indexes dmv count varies every day

texts:
I have a question to the missig index dmv.

BOL says:

  Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.

I basically do this using the following (slihghtly simplified) query:

SELECT <SomeFieldsAndCalculations> FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
INNER JOIN Database1.sys.objects o on d.[object_id] = o.[object_id]
INNER JOIN Database1.sys.schemas sch on sch.[schema_id] = o.[schema_id]

The results are written to a table once per day from ten sql servers (with correct grouping options...) .

Looking at this table I see that the number of missing indexes varies a lot.
These are the total number of missing indexes (over multiple instances) in a row:

  3091 3133 3090 3135 3131 2506 2750 2028 2032 2195 2274 2269 2272 2373

The services have not been restarted.

From the introduction from BOL I could understand a very slight variation from one day to another. Assuming a missing index popping up a day should still be in the dmv the next day, how can the total count ever vary that much? For example decreasing from 3131 to 2506 wihtin 24 hours? That's a change of about 60 per instance... Same with the raising number from 2272 to 2373 - suddenly 10 additional indexes missing per instance wihthin 24 hours...?

No indexes have been created during that period.
But unused indexes have been removed during that period.
I can't see a relation between removing unsused and missing indexes...

Questions:

How long will missing indexes information be returned from the above query if the Sql Service is runnung without interruption?
What could have influenced the number of missing indexes?


texts: The size of the tablespace doesn't change when I import a smaller dump or deleting old data

texts:
i wanna free some disc space used by an oracle database ,

I tried deleting  old data and importing smaller dump but the size of tablespace/datafile

remain unchanged.


texts: Convert the table PROCESSLIST from InnoDB to MYISAM

texts:
I use MySQL. My database's name is "db", and I have a table called "Test".

I want recover the last update, so I used  this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='db'
AND table_name='Test';

But the result is  NULL, so I converted my Table from InnoDB to MYISAM by using this query:

mysql> ALTER TABLE db.Test ENGINE=MYISAM;

and it worked.

But the problem is that when I want to convert the table PROCESSLIST from InnoDB to MYISAM I get this message:

mysql> use information_schema
Database changed
mysql> ALTER TABLE PROCESSLIST ENGINE=MYISAM;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

So, is there another solution to know what is the last update for this table (PROCESSLIST)?


texts:
I know this is not valid syntax, but my select statement below shows what I am trying to accomplish.  What do I need to alter in order to succesfully run this select query?

Declare @Data Table
(
nID varchar(100)
,name varchar(100)
,saletotals float
,saledate date
)

Declare @GiveMeSaleTotals Table
(
nID varchar(100)
,datetocheck date
)

Insert into @Data (nID, name, saletotals ,saledate) Values
('111eee', 'Blue', '13.22', '01/01/2016')
,('111eee', 'Blue', '44.11', '01/03/2016')
,('111eee', 'Blue', '22.11', '01/08/2016')
,('222ffff', 'Red', '11.11', '01/11/2016')
,('222ffff', 'Red', '22.10', '01/22/2016')
,('4444ffff', 'Red', '22.10', '12/05/2016')
,('4444ffff', 'Red', '21.10', '12/05/2016')

Insert Into @GiveMeSaleTotals (nID, datetocheck) Values
('1111eee', GetDate())

Select
*
FROM @Data
WHERE nID IN (Select nID from @GiveMeSaleTotals where @Data.saledate = datetocheck)


texts: What is the modern way to partition PostgreSQL across machines, when the data is "naturally partitionable"

texts:
After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature.
Today I see data stores with quite different eyes than before. Things like Riak have spoiled me in a way that I can no more tolerate single points of failure, "down for maintenance" etc. Of course, (or I hope), I havent' lost my sanity totally. This is a a personal project that doesn't quite (or yet)  have extremely high requirements.

Most of the sharding solutions don't give me what I want (at least on a glimpse), probably because my problem is quite "easy" to solve. At least on conceptual level (ignoring the restraints that RDBMs themselves bring to the table).

I have a small amount of "shared" data, which can be duplicated freely. It doesn't have requirements of hard consistency. This can be stored in a dynamo-like database and will scale infinitely. But I still would like to go with a single database if possible.
I have lots of "per-user" data. That is - lots of users, with every user having data of absolutely reasonable size, really fit to be stored on a single PostgreSQL node. We are talking about 10s of thousands records at maximum.
I never need to query cross-user and I don't need cross-user atomicity.

This sounds extremely easy to achieve. At least when I'm looking at it with my "NoSQL eyes".

Here are my naive starter ideas:

At the very extreme, I could just serialize the whole user as a single Key/Value in Riak. Of course, constant de/serialization of several megabytes of data will be slow and that's why I'm considering using PostgreSQL. Lots of Riak K/Vs is a no-go, as I need atomicity/transactions within each user's data.
I could use an SQLite database per user, and use something like GlusterFS for the redundancy/availability. This is probably the solution I'm going to choose if I can't find something equally good using PostgreSQL. Pros: Can down/up scale really well; Cons: I'd prefer having PostgreSQL's types and strictness over SQLite

So, what I would ideally request from a PostgreSQL sharding solution:

Automatically keep several copies of every user's data around (on different machines). Be able to dynamically switch the master node per user/shard (if the previous master goes down).
Be able to dynamically up/down scale, by adding/removing server nodes. Mostly like Riak is able to do.
Do not require my application to know which nodes to talk to and when.


texts: MariaDB slave replication stopped after slave upgrade to 10.1.41

texts:
After the recent MariaDB 10.1.41 update which happened on 01/Aug/2019, few of our slaves have stopped syncing relay logs from master which is on 10.1.40. Slaves which got automatically updated to 10.1.41 is now having the following status. Slave IO thread is in Preparing state and the logs are not getting written. I did a reset slave and initiated a change master with the positions again but still getting the same. The other slave servers which is in MariaDB 10.1.40, 10.1.33 etc are running as normal. These which got upgraded to 10.1.41 has issue?

Does anyone have any clue on this?

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: NULL
                  Master_Host: a.b.c.d
                  Master_User: xxxxxxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: xxxxxxxxxxxxxx-bin.001135
          Read_Master_Log_Pos: 262316421
               Relay_Log_File: xxxxxxxxxxxxxxxxxxxxx-bin.003410
                Relay_Log_Pos: 4
        Relay_Master_Log_File: xxxxxxxxx-bin.001135
             Slave_IO_Running: Preparing
            Slave_SQL_Running: Yes
              Replicate_Do_DB: xxxxxxxxx
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 262316421
                        Relay_Log_Space: 498
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 111
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative


texts: MySQL innodb_log_file_size causing problems starting MySQL

texts:
I have just restored a backup taken from xtrabackup (only for testing, so not critical luckily), and I am getting the following error:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 125304832 bytes than specified in the .cnf file 0 262144000 bytes!

So I updated my my.cnf file to match this value:

[mysqld]
. . .
innodb_log_file_size = 125304832

But I am stil getting an error:

log file ./ib_logfile0 is of different size 0 125304832 bytes than specified in the .cnf file 0 124780544 bytes!

But my my.cnf file is now set to 125304832 bytes so why does MySQL still think it is only 124780544 bytes?


texts: Concurrent Transactions vs Simultaneous Transactions

texts:
Chapter 6 of David Kroenke's Database Concepts: Database Administration

Is my understanding correct that in a database, simultaneous transactions don't really happen. Instead, it is a concurrent transaction that is happening fast that users think it is a simultaneous transaction?

The reason I ask is because I was thrown off by the question on my assignment asking how many CPUs are required to perform a simultaneous transaction. I answered only one cpu is required since it is really a concurrent transaction that is happening and not a simultaneous one?

Thoughts, anyone?

EDIT: ~ jcolebrand --

@gbn and @Mark,

I appreciate your input, however, I absolutely have no idea about what those terms are because this course I am currently taking is an introductory course to database management and my question was only discussed in the book in 1 paragraph and did not elaborate any further. I might be able to understand your responses in time, but now, i cannot just yet. Thanks though!


texts: How is page data rearranged after clustered index is created?

texts: Can anyone please explain what happens at the page level when clustered indexes are created? How do pages rearrange data once clustered indexes are created?


texts: Slot Time Challenge - Doctor Appointment database schema

texts:
I am trying to make a doctor appointment system where patient online will have two options:

First time visit (this visit should be 20 mins)
Follow up visit (this visit should be 10 mins)

Constraints:

Price will be different based on wither its a first time/follow up
Doctor might have break times between slots
System interface will support both options when booking the slot
we need the least gap time between slots to make max. usage of doctors availability

The model we finished so far was based on creating a Doctor Slot table that's like the below

Consider the below Example
if DoctorSlot is N and
N=10 mins

Case First Time
if(N*2)== Free
Make reservation
block two slots instead of one

Case Follow Up
if(N)==Free
Make Reservation
Block one slot

Example
Doctor: 9:00 to 9:10
Doctor: 9:10 to 9:20
Doctor: 9:20 to 9:30

Case First Time
Show to patient 9:00 to 9:20

Challenges:

Append time from 9:00 to 9:20 (there could be a buffer time (break for doctor) between slots for doctors)
We will have two slot IDs from database instead of one (which SlotID will be used with Order)
How to show user in run time based on his case which time generic model we will use and update prices later on accordingly
If user booked a first time slot, then another user booked a follow up, there will be gaps and how to handle time in SQL Server in database

Questions:

What is the best database schema to achieve a solution that satisfy all possible scenarios?
What is the best method to deal with time entity from SQL Server/ASP.NET POV?


texts: I am relatively new at playing around with this and have been trying to follow a tutorial on setting up in the section named "Prerequisites to Create SQL Notification Requests" within this site http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach However I am not sure what I should be putting in :://the service and there were no instructions or information on the site regarding this.  I was wondering what I should do or what other information I need to complete this section.


texts: PostgreSQL table lock for certain select wheres

texts:
Let's say I have a postgres database that tracks cars purchased by users. Every user can own as many vehicles as she wants, but only one for each make.

-----------------
|     USER      |
|---------------|
| id |   name   |
|---------------|
| 1  |   Adam   |
| 2  |   Julia  |
-----------------

-----------------
|     MAKE      |
|---------------|
| id |   name   |
|---------------|
| 1  |   Volvo  |
| 2  |   Opel   |
-----------------

--------------------------
|       PURCHASE         |
|----|---------|---------|
| id | user_id | make_id |
|----|---------|---------|
| 1  |    1    |    1    |
| 2  |    1    |    2    |
| 3  |    2    |    2    |
--------------------------

Purchase uniqueness is restricted on a database level by a "unique" constraint (ALTER TABLE purchase ADD UNIQUE (user_id, make_id)).

When user tries to purchase a new vehicle my application first checks (in a db transaction) whether the user/make combination is possible, then inserts a new row

SELECT COUNT(1) FROM purchase WHERE user_id = 2 AND make_id = 1;
// assuming the previous query found no records
INSERT INTO purchase (user_id, make_id) VALUES (2, 1);

The thing is, in a rare race-condition situation, when there are two concurrent requests, this opens a possibility that two separate app instances will get the information that the user/make combination does not exist and will try adding a new row to the database. The second one will obviously fail due to uniqueness restraint on the purchase table, however I was wondering if it was possible for a transaction to create a lock on a postgres table that would prevent any other transactions from making selects for a specific WHERE cause (in my situation - WHERE user_id = 2)? I cannot block the whole table as it would have a horrific impact on the app performance. Just lock selects for some (may be non-existing) rows. How do I do that?


texts: How to view databases and tables after 'imp' import using Oracle SQL developer

texts:
Maybe that's a wrong form of answer...
The problem is:

I've got an Oracle database dump file (very old, created with imp utility)
At last, I've imported it. The command was imp file=E:\Downloads\db_dispatcher\disp.dmp full=y
The console shows me there was success.
Now when I open my Oracle SQL developer and create any connection, I can't list my database (there must be at least one): in 'tables' section I only see this system stuff.

I the DB name should be 'meltshop', so I tried to find it in my Windows Services list. But there's none.

Oracle SQL developer 19, Windows 10. DB was created in 2009.

I am a newbie with Oracle(
The last year I worked with MySql database. Maybe here are any similarities?
I also have php interface; the config shows me

$DBNAME = "<ip>/orcl.meltshop";
$DBLOGIN = "ml2_rt";

Instance, version, etc.:

select instance_name, host_name, version from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
VERSION
-----------------
xe
DESKTOP-T6U2873
11.2.0.2.0

The log of my last try:
google disk


texts: SQL Server how to get around the transaction log filling up when updating a column to an int

texts:
I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns:

MarrigeId tinyint,
HusbandName varchar(500),
MarrigeLength int

Now I have another table BRITTNEY_SPEARS_MARRIAGE_STORIES

StoryId int,
MarriageId tinyint,
StoryText nvarchar(max)

The problem is we want to update MarrigeId column to an int from a tinyint. We just feel that Brittney is going to have lots of marriages before everything is said and done.

Now the BRITTNEY_SPEARS_MARRIAGE_STORIES table has 18 million rows in it (hey the girl has some issues) so when we go to do the update the transaction log fills up and our SQL Server box dies.

How can we get around this?

Is there anyway to say "Hey SQL Server I'm going to update this column and make it bigger. Trust me on this SQL Server. Please don't fill up the transaction log while you attempt to validate everything?"


texts: After I pulled these datas what I wanted from a SQL Server Database,I can't update on row.I'm receiving the following the error.


texts: Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

texts:
I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone.

F.e. the date 2018-08-03 should equal 1533254400 (as of calculated here).

I've tried to_timestamp(my_date_column, 'YYYY-MM-DD') but it returns error function to_timestamp(date, unknown) does not exist. I've tried adding different variations of ::timestamp... at the end of the function call, but to no avail.

Currently I do the timestamp conversion in the application layer, but I'd like to dedicate that job to the database.


texts: Partition Key questions in SQL Server 2008

texts:
We are planning to use table partitioning for our database based on an integer column. I have read about it on MSDN at http://msdn.microsoft.com/en-us/library/ms190787.aspx and have few queries.

a) Is it required that partition key be part of primary key? Even if it is not required still would it be of any benefit if it is part of primary key or clustered index?

b) In our case, we would be performing JOIN operation of partitioned tables with non-partitioned master data. Will I loose the performance gained by partition elimination? In general, if any of the tables used in JOIN operation is non-partitioned then will I loose the benefit of Partitioning?

c) Does adding filter criteria in JOIN criteria over Partition key help in any way in partition elimination? Or only the filter criteria in WHERE clause over Partition column is used for Partition elimination?

d) Should partition key be part of non-clustered index?

Thanks in advance.


texts: How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL

texts:
I have many procedures that I use the option WITH ENCRYPTION when I create them.

You can see this on the picture below, as an example:

the problem with this is that when I want to save my previous version of my stored procedure, I cannot get the expected result, as you can see on the picture below.

the way I am backing up the code of the stored procedures is on the link below:
How to backup the current code of a stored procedure and its permissions as well?

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc, 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc
    , *
From sys.procedures as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where ...

is there a way to remove the WITH ENCRYPTION from the code of a stored procedure, before I save it?

Provided I have ownership of the procedure.

There is a nearly duplicated question on the link below
How to view an encrypted view or stored procedure

however, on this question, I own the database. I am sysdba. I can use any resources, but I would like a solution via T-SQL.


texts: How MongoDB allocate new data block when data block is full

texts:
I am using MongoDB in my project. And I have some questions about how Mongo storage allocated.

For example, I have 16MB full data block, and I want to add new document into this data block. - How MongoDB allocate new data block ? - Will MongoDB move all data or only new document into a new data block ?


texts: Keeping postgres connection behind load balancer alive during long-running query (vacuum)

texts:
Background:
I have a postgres instance behind a load balancer (AWS Classic Load Balancer). I want to access the database from another cluster through the load balancer and run queries. I cannot change the database setup (instance behind load balancer).
When executing long-running queries (VACUUM in my case), the connection is dropped by the load balancer after 1 hour. This happens because the connection is inactive and the load balancer terminates it.
tcp_keepalives_idle does not work here and the load balancer checks for actual transmitted data, not just packets (see the update on this question)
Problem:
How can I keep the connection alive while the VACUUM is running?
Is there a way to send data over the connection to indicate that it is still alive?


texts: Ola Hallengren Index Optimize script failed because a deadlock

texts:
to accomplish the nightly maintenance tasks on production servers, every night I have scheduled the script to defrag the indexes. Due to other nightly application maintenance tasks, sometimes the job fails because of a deadlock. At the same time starts the script to defrag an index and on the same table a query is doing an update where the index was created. I would like to find a solution to avoid that job fails. Because I can't have a control on that query, I would find a way to check if the table is locked until the query will be completed and after that, start the defragmentation of the indexes.

Any suggestions would be greatly appreciated.

Thanks.


texts: Can not connect to MySql server remotely

texts:
I can not connect to MySQL server from a remote computer. When I try to connect it gives me the error

  Host '' is not allowed to connect to this MySQL server

I've edited the MySQL server my.ini file by changing the bind line from #bind = "127.0.0.1"  to bind = *. I've turned off the firewall of the client machine trying to access the server. I also gave full privilege to the user of MySQL database through which I am  trying to connect to the server by using the MySQL grant command, but nothing seems to be working.

Please help me.

Thanks


texts: Trying to identify if the query is long running or long waiting

texts:
I have a sql query which has multiple joins and uses a view which has multiple joins.I captured the query in sql profiler and i see few information in there.
I am trying to understand whether the query is a long running or long waiting query.
Details:

query runs in parallel.
Has 40 logical CPU.
CPU column : 156595
Duration column : 45373

I am trying to understand if the query spend more CPU time or not.?Since the query runs parallel ,i am not sure what this high CPU time shows.

Any help.


texts: Get statistics of open connection to linked servers

texts:
How can I get information about connections from my MS SQL 2008 server to linked servers (actually it's an Oracle db)?

Things like number of currently open connections, and history of opened and closed connections, if these informations are stored somewhere. Who used that connection (login name, application, process id, anything)?

Background: there is a web app, and an Oracle linked server, and the Oracle dba says that our MS Sql server is spamming hundred of connections to their server. Currently our app is only accessible for a few users, and only doing simple selects from a view... So I can't really imagine that caused hundreds of connections.

Also what could cause excessive amounts of open connection form the MS Sql db to the Oracle db? Or what could keep the connection open for too long time? Can Sql Server Management Studio go wild, and open unnecessary connections?

Edit:

One thing to add: We are using OpenQuery to query the Oracle db, so the Oracle db does the heavy lifting (selecting data from a pretty big view). The documentation says nothing about having to explicitly close the connection, is there any known issue regarding this?


texts:
I am using SSIS to import a 25,000 row CSV to an empty table.  The table has a primary key that is populated as the data is inserted.  I am using an ADO.Net destination.

What reasons are there why this operation would take around 5 minutes?  At the default value of DefaultBufferMaxRows (10,000), the import fails completely with a timeout after around 5,000 rows.  I dropped the value to 1,000 and now it gets there eventually with no timeout, but takes a full five minutes.

The Flat File data source has explicit column widths set, could that be the issue?


texts: Calculating the median value in a MySQL table with CodeIgniter

texts:
This is my model file in codeigniter. I need to calculate the median for this variable: budget_breakdown.breakdown_grants. How can I do this calculation?

function reports_details1() {
        $this->db->select('budget_breakdown.breakdown_grants');
    //survey_respondent_info.state,survey_respondent_info.survey_id,budgets.budget_id,
        $this->db->from('survey_respondent_info');
        $this->db->join('budgets',
                        'budgets.survey_id=survey_respondent_info.survey_id' , 'left');
        $this->db->join('budget_breakdown',
                            'budgets.budget_id=budget_breakdown.budget_id' , 'left');
        $this->db->where('budgets.budget_option_id', 2);
        $query1 = $this->db->get();
        $result = $query1->result();
        return $result;
    }


texts: Errors while accessing Excel file using openrowset

texts:
I'm seeing the below error message while running the following T-SQL statement.

select * FROM OPENROWSET(
  'Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=123.xls',
  'SELECT * FROM [Sheet1$]')

  Msg 7399, Level 16, State 1, Line 1
  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
  reported an error. The provider did not give any information about the
  error.

  Msg 7303, Level 16, State 1, Line 1
  Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I have two sysadmin accounts; with one account I am able to execute the statement, however with the other account I'm seeing the error message, even with SQL account the same.

I execute the below statements, however it still doesn't work:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
    , N'AllowInProcess'
    , 1;
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
    , N'DynamicParameters'
    , 1;
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO