texts: mongodb import .... taking very long time

texts:
I have json file of 432.6 MB, which i am uploading to mongodb database, I have mongodb setup on my OS X 10.9.4

mongoimport -d fastqdb -c seqcol --file /Users/loginname/FASTQ.json --jsonArray
connected to: 127.0.0.1
2014-07-29T18:06:54.311+0200        Progress: 31598/432568845   0%
2014-07-29T18:06:54.311+0200            200 66/second
2014-07-29T18:06:58.207+0200        Progress: 78998/432568845   0%
2014-07-29T18:06:58.208+0200            500 71/second
...........................
...............................
2014-07-30T13:42:32.027+0200        Progress: 138486966/432568845   32%
2014-07-30T13:42:32.027+0200            860200  12/second
2014-07-30T13:42:36.004+0200        Progress: 138534798/432568845   32%
2014-07-30T13:42:36.004+0200            860500  12/second

one can check the time, it is almost a day and still it is running
details of my PC
Memory  4 GB 1067 MHz DDR3
Processor Name: Intel Core 2 Duo
Processor Speed:    2.66 GHz

I like to mention, I am very new to mongodb and nosql, actually this is my first interaction. I am not sure. what need to be changed. Actually this is my test run, I have to work with 5TB of data, so wanted to check, is it possible to store such large data with  mongodb. I will be using 10-12 nodes cluster.

After I finished

2014-07-30T16:52:07.143+0200 imported 1407471 objects
> use fastqdb
switched to db fastqdb
> show collections
seqcol
system.indexes
> db.seqcol.count()
1407471
> db.seqcol.find()
>
{ "_id" : ObjectId("53d7c4a2563ff562b85ce94b") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce94c") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce94d") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce94e") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce94f") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce950") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce951") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce952") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce953") }
{ "_id" : ObjectId("53d7c4a4563ff562b85ce954") }

only id's are created , there are no data, and my data consist of

[
    {
        "POS": "*",
        "MAPQ": "0",
        "`SEQ": "CTGGTGGGATACAGAACAGTACAATTCTGATGGTAATCAGTTAACAAATTAAACATATTTATTTTATAACCCTAACCCTAACCCTAACCCTAACCCTAACC"
    },

    {
        "POS": "*",
        "MAPQ": "0",
        "`SEQ": "AGGGTTAGGGTTAGGGTTAGGGCTAGGGTTAGGGTAAGGGTTAGGGTTAGGGTTAGGGTTAGGGTTATGGTTAGGGTTAGTGGTAGGGTTAGGTTTAGGGG"
    },

    {
        "POS": "*",
        "MAPQ": "0",
        "`SEQ": "CTAACCCTAACCCTCTAACCCTAACCCTCTAACCCTAACCCTCTAACCCTAACCCTAACCCTAACCCTAACCCTAACCCTAACCCTCTAACCCTAACCCTA"
    },

Ok I found out my solution with GridFS.
file1.py

import pymongo
import gridfs

db = pymongo.connection.Connection().<databasename>

grid = gridfs.GridFS(db)
x = open("/Users/<name>/"filename")
grid.put(x,filename = "filename")

file2.py .....retrieving data

import pymongo,gridfs

db = pymongo.connection.Connection('localhost',27017).<databasename>
grid = gridfs.GridFS(db)
fp = grid.get_last_version(filename='filename')

with grid.get(fp._id) as fp_read:
    print fp_read.read()

it took me 44 sec. check out [http://www.youtube.com/watch?v=NhWySVTNOA0][1]

I am not sure if this is correct way, one has to read about mongoDB GridFS


texts: Big Size Database and increasing number of users

texts:
Me and my colleagues are using quite big size normalized database.

Basically it's used for warehouse management and daily minimum 100 of the user perform data manipulation operations.

80% of the users access stock information from web.
20% users are from the warehouse operation team.

Most of the time in a day, warehouse operation team performs major operations of stock taking, stock adjustment, sales, purchases, reporting etc.. But at the same time web users get quite slow response from SQL Database.

Keeping in view increasing number of web users, my question is how I could make the web access faster at the same time when major operations/transactions are running in the background?

As web users are accessing subset of the tables which are being used by Operations team, what strategy I could implement to keep the database responsive as well sync at same time?

Please bear with my language skills, I hope this is right place to ask this question, otherwise please guide.


texts: MYSQL: update field with random fixed char

texts:
would like to update all record on a field which i only want it to fill it with random either Y or N

currently using:

  update table1 set field1=( RAND() FROM Y FOR N)

which is not working, any idea?


texts: Should database directory and file access be restricted to only the db user?

texts:
I don't know exactly how to word this question and I haven't been able to find any specifics on it so apologies if it's a duplicate.

I know that it's standard practice to have database server software run as a specific operating system user, with access to nothing but the necessary directories and files. I understand that this is done to prevent intruders that gain access through the software to have priveleges outside the minimum necessary for the db to run.

However, should I also restrict read and/or write permissions to the database directories and files at the operating system level to the db user alone? Is this standard practice? Are there any problems that can arise by other software or the operating system performing read operations on a live db?

I'm interested in both the general case (a live server, like a vps) and the case where it's running on a local desktop, used by only one person. Using postgres at the moment and I'm not experienced.


texts: Finding tables & columns at risk of hitting max value of int

texts:
I just took over a medium-sized postgres database and the team has been surprised with many tables hitting their auto-incrementing id int max limits which triggered unscheduled downtime to fix.
Does anyone know of a simple tool to help check all tables/columns and easily find which are at risk of hitting the int max limits? I'm sure we can come up with a script to check, but I remember reading somewhere of a tool to help me identify potential issues exhaustively.


texts: How to debug when OS kills postgres for high memory usage

texts:
I am running Postgres 9.4 server in a VPS running Ubuntu. It gets killed often (multiple times in a day).
This is the message from dmesg

Out of memory: Kill process 1020 (postgres) score 64 or sacrifice child
Killed process 1020 (postgres) total-vm:445764kB, anon-rss:140640kB, file-rss:136092kB

How to debug what is causing this crash? Is it long running queries or some misconfiguration of the server or lots of idle connections open?


texts:
We occasionally have the problem that, when upgrading some software and database, inexperienced (or overworked, not excluding myself) sysadmins (we don’t have separate DBAs except for Oracle) run the DB upgrade scripts as user postgres instead of the correct application user.

Update: emphasis added. Our normal mode of operation, as documented, involves running those scripts as regular user. This question is asked to clean up for those cases when that procedure is accidentally not followed.

How can I change the ownership of all objects in a (one) database, no matter whom they belong to, to a specific user?

The methods described here don’t work for me, because:

REASSIGN OWNED apparently is per-cluster, not per-database (too broad)
REASSIGN OWNED does not work for objects owned by postgres
the manual enumeration from @AlexSoto’s answer requires one to know the types of the objects involved beforehand

I really want basically the equivalent of the Unix chown -R newowner /path command, just for a whole PostgreSQL database (all schemata, tables, indicēs, etc. in it).

Minimum PostgreSQL version involved is 9.1 (Debian wheezy), maximum whatever Debian unstable has at any given time.


texts: Can I tell Postgres that a new constraint is valid without actually running the check?

texts:
I have a constraint that ensures one of three columns is populated, and I'm adding a new column, so I'm replacing the old constraint with a four-column version. Since I know from the old constraint that one out of three columns must be populated, I can be sure that one out four will be populated and the new constraint is valid.

I know I can add it as NOT VALID and later validate it concurrently, and that's the kosher thing to do, but it's a bit of a waste of time for Postgres to validate all 5 billion valid rows. Can I mark it as valid without checking all the rows again? Alternatively, is there any downside to leaving it invalid indefinitely?


texts:
I've found a lot of strange characters on data rows like this

ÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚Â‚ÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã‚Â‚ÃƒÂƒÃ‚Â‚ÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã

The data inserted by perl script which converting to UTF8 encoding, here is the code parameter

$v = &Encode::encode('UTF-8', $v);

Here is the server information

\d ws_user_address
column          | type                        | Modifiers
address_id      | integer                     | not null
address_1       | text                        | not null

postgres=# show client_encoding;
client_encoding
---------------
UTF8
(1 row)
postgres=# show server_encoding;
server_encoding
---------------
UTF8
(1 row)

select version();
PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit

select address_id,address_1 from ws_user_address where address_id=1535278
1535278;Puri VADS, Jalan HR Rasuna Said Kav H No 1 ÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚Â‚ÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã‚Â‚ÃƒÂƒÃ‚Â‚ÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ‚Â‚ÃƒÂ‚Ã‚Â‚ÃƒÂƒÃ‚ÂƒÃƒÂ‚Ã‚Â‚ÃƒÂƒÃ‚Â‚ÃƒÂ‚Ã‚ÂƒÃƒÂƒÃ...

LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

What i can do to solve this issue on my system?


texts: phpMyAdmin configuration storage is not completely configured message

texts:
Today i have updated my PhpMyAdmin to 4.1.2
Now when i log in to PMA i am getting the message:

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.

When i click on the link:

PMA Database ... not OK [ Documentation ]
General relation features Disabled

Do i need this features? Or i can ignore that message? Thanks in advance


texts:
Does anyone have a solution to this problem?

  SQL Server Assertion: File: , line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Actions that are already taken:

DBCC Checkdb no_infomsgs - reported no problems.
DBCC CHECKDB, report 0 errors, 0 consistency errors.

Environment:

Windows Server 2003 R2 Enterprise Edition 64Bit SP2
Microsoft SQL Server Standard Edition (64-bit) Version 9.00.3042.00


texts: How to change the instance name of SQL Server Express 2008?

texts:
On my development machine I've installed SQL Server Express 2008, but it has the instance name .\SQLEXPRESS. I want it to be the default instance. I'm pretty sure that I selected the "Default Instance" option in the setup, but apparently it didn't work. Is there any way I can change the instance name now?

Added:

It seems that this is a known bug in the installer. It will be fixed when PCU 1 (PubliC Update) comes out, but there's not telling when that's gonna happen. In the mean time there are two workarounds:

When installing, choose the "Named Instance" option and enter MSSQLSERVER.
Install a named instance, then install another instance and this time the "Default Instance" option will work.

Still, the question remains: is there any way to rename an instance WITHOUT reinstalling? Because the setup was a slow pain. XD


texts: My app is launching to a US audience first, but I want to internationalize the database schema for future expansion. Obviously I will convert between the two units as necessary regardless, but are there any benefits to storing one or the other in the database? My initial thought is that it might be helpful to store kilometers since miles are used by less countries worldwide, so long-term, I will be doing less conversions overall. But in the short-term, while the app is still US-only, I will be converting units every time I access the database.


texts: How can I rewrite the following removing the date_format function?

texts:
I have a table with a column when that is of type datetime.
I am doing the following filter which can not use and index:
AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month )
How can I re-write this so that I can get rid of the DATE_FORMAT that forbids index usage?


texts: "Invalid object name" error when executing query

texts:
I connect database with Visual Studio 2017. When I am trying to execute a query then it shows the following error:

The connection string I am using is:

SqlConnection con = new SqlConnection("Data Source=ANUPAM-DESKTOP\ANUPAM;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

My code:

public void exicuteDatabaseQuery(String query)
    {

        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter(query, con);
        sda.SelectCommand.ExecuteNonQuery();
        con.Close();
        MessageBox.Show("Successfull");

    }

    private void Button_Click(object sender, RoutedEventArgs e)
    {
        int id = Convert.ToInt32(___Id_.Text);
        int number = Convert.ToInt32(___Number_.Text);
        String InsertQuery = "INSERT INTO Member (id, number)
        values('"+id+"','"+number+"')";

        exicuteDatabaseQuery(InsertQuery);
    }

Database explorer image:


texts:
I am facing issues with Oracle log files. My application inserts data from xml file into Oracle. I started my application last day and went home. It stopped unexpectedly at 8PM. There is no log in application why the insertion stopped.

I want to check Oracle log file to check what exactly the error was and why the Oracle stopped inserting data.

I have log.xml file and multiple *.trc and *.trm file. I don`t know how to start with this.

I just have to check the error that why the Oracle has stopped.


texts: Ordering by field, constructed by subquery is slow

texts:
My tables (oversimplified for clarity):

documents:  registrations:
|id|        |id|document_id|date|

My simplified query:

SELECT *
FROM (SELECT documents.*,
             (SELECT max(date)
              FROM registrations
              WHERE registrations.document_id = documents.id) AS register_date
      FROM documents) AS my_documents_view -- it's supposed to be a view
ORDER BY register_date desc NULLS LAST
LIMIT 20;

When I try to order by register_date field I get a potato response of ~80 seconds execution.

EXPLAIN ANALYSE:

Limit  (cost=27237727.87..27237727.92 rows=20 width=192) (actual time=85124.599..85124.613 rows=20 loops=1)
  ->  Sort  (cost=27237727.87..27265594.16 rows=11146516 width=192) (actual time=85124.597..85124.600 rows=20 loops=1)
        Sort Key: ((SubPlan 2)) DESC NULLS LAST
        Sort Method: top-N heapsort  Memory: 33kB
        ->  Seq Scan on documents  (cost=0.00..26941123.09 rows=11146516 width=192) (actual time=0.074..77874.947 rows=11153930 loops=1)
              SubPlan 2
                ->  Result  (cost=2.19..2.29 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=11153930)
                      InitPlan 1 (returns $1)
                        ->  Limit  (cost=0.43..2.19 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=11153930)
                              ->  Index Only Scan Backward using registrations_document_id_date_idx on registrations  (cost=0.43..3.95 rows=2 width=4) (actual time=0.004..0.004 rows=1 loops=11153930)
                                    Index Cond: ((document_id = documents.id) AND (date IS NOT NULL))
                                    Heap Fetches: 10337268
Planning Time: 0.381 ms
Execution Time: 85124.722 ms

Complexity and cost are absurd, there are lots of rows in these two tables (millions, actually), but is it really so hard for engine to order it? Are there any workarounds or suggestions to optimize it?

In full query, I have some additional filters so it runs a bit faster, but it's still unacceptable for the project.

I tried playing with joins and indexes without any success.


texts: Is it possible to add a certificate to a database role?

texts:
I have a certificate I am using for inter-DB access to a stored procedure in SQL Server, and am able to provide access to the stored procedure by creating a new user in the database using:

CREATE CERTIFICATE testCertificate
FROM FILE = 'test_certificate6.cer';

CREATE USER testCertificateUser
FROM CERTIFICATE testCertificate;

GRANT AUTHENTICATE TO testCertificateUser;
GRANT EXECUTE ON storedProc1 TO testCertificateUser;

but need to be able to add the inter-DB access to an existing database role (for compatibility with existing systems). How can I go about doing this?


texts: Strange behaviour with Computed Columns in SQL-Server

texts:
Whilst reading through my 70-433 exam book, I thought of something that I can see not working, yet I believe it does. The passage read something like:

  The column must also be marked as PERSISTED, which means that SQL Server physically stores the result of the computed column's expression in the data row instead of calculating it each time it is referenced in a query.

From this I understand two things:

A non-persisted computed column is calculated every time that it is referenced in a query
Because nothing is stored for the computed column, I assume no index can be created for the column.

After reading it, I thought that this was a bit strange as I have managed to create an index on a non-persisted column in a previous project.

How can an index be created for something that is not persisted and is this detrimental in the long run?

To prove this I have run the following SQL statement:

CREATE TABLE testTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    telephone VARCHAR(14),
    c_areaCode AS (SUBSTRING(telephone,0,5)),
    cp_areaCode AS (SUBSTRING(telephone,0,5)) PERSISTED
)

INSERT INTO testTable VALUES('09823 000000');
INSERT INTO testTable VALUES('09824 000000');
INSERT INTO testTable VALUES('09825 000000');

CREATE NONCLUSTERED INDEX IX_NotPersisted ON testTable(c_areaCode);
CREATE NONCLUSTERED INDEX IX_Persisted ON testTable(cp_areaCode);

And then run the following queries:

DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL');
DBCC DROPCLEANBUFFERS
GO
SELECT cp_areaCode FROM testTable;
GO
SELECT c_areaCode FROM testTable;

Having looked at the query plan for the above code, I can see that both select queries are using the non-persisted index. Again, how?


texts:
I have a table like this in MS SQL SERVER 2014:
ID|Race|Lap
1 |21  |11
2 |21  |NULL
3 |21  |NULL
4 |21  |NULL
5 |29  |65
6 |29  |NULL
7 |29  |NULL
8 |29  |NULL

I am trying to fill up the Lap column by adding 1 to it based on the first value. The partition is based on Race column. Something like this would be the end result:
ID|Race|Lap
1 |21  |11
2 |21  |12
3 |21  |13
4 |21  |14
5 |29  |65
6 |29  |66
7 |29  |67
8 |29  |68

There might be other ways of doing this but I would rather stick with recursive CTE. Is there any way to do this?


texts: Sql Server 2008 R2 - Failed to send an environment change notification to a log shipping partner node

texts:
Login failed for user '*****'. Reason: Failed to send an environment
  change notification to a log shipping partner node while revalidating
  the login.

I'm seeing a bunch of these errors on my SQL Server 2008 R2 - SP3 database, on average about every 5 seconds. It seems to be affecting the connection pools as well.

This seems pretty straight forward except that log shipping is NOT enabled on this server for any databases. Log shipping was used for a database which was restored to this server, I'm pretty sure the log shipping wasn't properly broken before the backup/restore. However, the offending login is different from the login used for the restored database, so it doesn't seem that the problem is directly related the restored database.

I've run through all of these procedures: http://msdn.microsoft.com/en-us/library/ms189071.aspx and queried the server to find any traces of log shipping and have found nothing.

I've been trying to resolve this issue for the past week and have hit a dead-end.

Thanks


texts: Why is the transaction log file not truncated though its simple recovery model?

texts: My database is simple recovery model and when I view the free space in log file it shows 99%. Why doesn't my log file truncate the committed data automatically to free space in ldf file? When I shrink it does shrink. Please advice. I saw this question, Why Does the Transaction Log Keep Growing or Run Out of Space?. But unlike that scenario I have a simple recovery model and my transactions seemed to have committed as there are no on going transactions and shirkable size is shown as 99%


texts:
I am rather new to SQL Server administrations, I am running an SSIS that never ends...

A) looking at the processes using

SELECT r.*, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status IN (N'Suspended', N'Running', N'Runnable', N'Pending')

I see the session is SUSPENDED

B) looking at the actual execution plan using

SELECT plan_handle
FROM sys.dm_exec_requests
WHERE session_id = 55

SELECT query_plan
FROM sys.dm_exec_query_plan (0x06000A00FB4E0A2240615FAE010000000000000000000000)

I see 95% of time spent on parallelism

How can I solve this issue?

Thanks in advance,
Assaf


texts: SQL server log shipping - dry-run data modification script on secondary then resume log shipping

texts:
Am log shipping a large, busy DB from server A (primary) to server B (secondary).

Nightly backups are taken, as well as diffs every X hours, and transaction logs are shipped and restored every 15 mins.

Occasionally I need to dry-run a script on server B, to assess it's impact, before running on server B.  Script is often DDL.

I do this by taking server B out of RESTORING mode by manually executing a restore command on a recent transaction log, and testing the script.

However, to then resume log shipping, I have to restore the most recent full db, and diff, before log shipping can resume.  This is very time consuming.

On server B, is there any way to instead effectively rollback to a previous transaction log (before I tried the script), and then resume log shipping?

Many thanks


texts: Limit of Generating Script for Schema Only from SQL Server 2012 to SQL Server 2008R2

texts:
Is there a limit on this method? I've tried a 35GB Database and it works, but my question is how about if my Database is like 100-150GB? Will I encounter some error? Or it will work smoothly but a little bit slow because of a large database size?

I'm using the method given by @Kin here

Thanks!


texts: Why is a new user able to create tables in PostgreSQL?

texts:
I followed two tutorials to create a DB with:

a fully privileged user link
a read-only user link

I then got a tip from CJ Estel's tutorial stating that "you may have inherited the ability to create tables even though we never explicitly gave it to our new user". Sure enough, the read-only user is able to create and own tables!

CJ Estel has pointed the root cause very well, namely a template database. But the ability to create tables undermines most tutorials you get from googling "read only user postgres" including one hosted on postgresql.org. Your user has more than read-only privileges!

Why does a new user have this ability? After revoking this privilege, is the database truly read-only for that user?


texts:
Server:

  Disk: 2X 500 SSD RAID 1, 3 TB HDD   RAM: 256 GB DDR4   CPU: Intel Xeon
  10 core

Database:

  Percona for MySQL 5.7.23

Average number of active connections:

  1700

I have 4 schemas, and in every schema there are 3 tables that are under a lot of actions (read, write and update) every second from those connected users.Two schemas have one procedure with transaction that add row to table after some checks. For three months database grows to 44 GB with deleting and it's not even on its load peak. In the last few days from time to time, bottleneck appears and causes me a lot of problems. Slow log file write:
- COMMIT
- INSERT INTO
- SELECT
Select is ok, because it's going through a lot of rows, but insert and commits are problems.

Parameters that I set at the beginning:
innodb_io_capacity = 3000;
innodb_io_capacity_max = 6000;
innodb_buffer_pool_size = 212 GB;
innodb_buffer_pool_instances = 32;
innodb_read_io_threads = 8;
innodb_write_io_threads = 8;
innodb_flush_log_at_trx_commit = 0;

I'm afraid that I misconfigured something, but I don't know what. Everything I changed is from suggestions from the web.
Also, I left the option that there is something wrong with SSD drives, and they are not capable to handle the load at some point.
Just to mention there isn't replication.

I have a great machine, and I would like to configure it for maximum performance, so any experienced and reliable advice would be great.

  UPDATE:
  Sorry for my late response, but we found that some processes of other applications cause problems to MySQL, even our CPU usage is miserably low, same as our RAM consumption. We started monitoring our thread usage, and found that some applications use full threads(max 5 out of 20 which we have). Adding a new pair of SSD disks(Hardware RAID 1), and switch database on it resolved our problems.  <


texts:
SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line number.

When having SELECT with WHERE clause that ID = 99,001, how it skips 99,000 rows to read only the requested row. How is the process in a flat file?


texts: Oracle Enterprise Manage "CPU utilization" is confusing

texts:
When I display the CPU utilization in the Enterprise Manager (in the Performance tab), there is a big discrepancy between the graph that shows the utilization and the table that shows the operating system processes and how much CPU they take.

The graph shows something around 15% utilization (which would be one core at 100% as this is an 8 core server), but the process list shows 100%. Which in turn would mean that the process uses all cores which is not the case. ProcessExplorer indeed shows only ~10% CPU usage for the process oracle.exe.

So the question is: what does the column "CPU Utilization (%)" in the processlist reflect?


texts: Can't start mongodb server using 'sudo service mongod start'

texts:
I'm trying to set up a dummy mongodb server on my local device so I can play around with it and make mistakes so that I can feel confident editing the actual server. I use the following code to start the mongodb server:

 sudo service mongod start

However executing sudo service mongod status outputs the following:

   ● mongod.service - MongoDB Database Server
       Loaded: loaded (/lib/systemd/system/mongod.service; disabled; vendor preset: enabled)
       Active: failed (Result: exit-code) since Wed 2018-08-29 15:46:44 ACST; 754ms ago
         Docs: https://docs.mongodb.org/manual
      Process: 14604 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=62)
     Main PID: 14604 (code=exited, status=62)
          CPU: 1.889s

    Aug 29 15:46:42 david-Aspire-A515-51G systemd[1]: Started MongoDB Database Server.
    Aug 29 15:46:42 david-Aspire-A515-51G mongod[14604]: 2018-08-29T15:46:42.435+0930 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
    Aug 29 15:46:44 david-Aspire-A515-51G systemd[1]: mongod.service: Main process exited, code=exited, status=62/n/a
    Aug 29 15:46:44 david-Aspire-A515-51G systemd[1]: mongod.service: Unit entered failed state.
    Aug 29 15:46:44 david-Aspire-A515-51G systemd[1]: mongod.service: Failed with result 'exit-code'.

Tried adding sudo service mongod start --sslDisabledProtocols 'none' to no avail.
Any help would be much appreciated


texts: How to apply a regex on a column fetched from DB

texts:
I am using IBM DB2.

So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column:

([1-57-8][0-9]{2}
|0([1-9][0-9]
|[0-9][1-9])
|6([0-57-9][0-9]
|[0-9][0-57-9]))
([1-9][0-9]|[0-9][1-9])([1-9]\d{3}|\d[1-9]\d{2}|\d{2}[1-9]\d|\d{3}[1-9])

Someone suggested to me that I have to write a function/procedure in the query to validate that the SSN picked from the db follows this regex else it should not be stored in the result set of the query.

I don't know how to write a function or a procedure in SQL, can you please help me out here?


texts: Problem deleting detached partition in DB2 LUW V9.7

texts:
Recently in prod environment we are having problems while dropping the detached partitions its giving this error

  """ DB21034E The command was processed as an SQL statement because it
  was not a valid Command Line Processor command. During SQL processing
  it returned: SQL20285N The statement or command is not allowed because
  the table named "schemaname.tablename_JAN1" has detached dependents or
  the asynchronous partition detach operation is not complete. Reason
  code = "2". SQLSTATE=55057"""

I did some research and could find this from IBM saying we need to restart the instance to solve the issue. Link below I think you could have seen many of such incidents of what i described, please help me with this issue thanks in advance

http://www-01.ibm.com/support/docview.wss?uid=swg21515721


texts: High availability SharePoint farm with restricted conditions

texts:
Currently, our SharePoint farm has restricted conditions as below

Shared folder is not allowed
FTP/SFTP is not allowed
SQL FILESTREAM is not allowed
SMB protocol is not allowed
Using Windows Server Failover Clustering (Not SQL AlwaysOn FCI) for SharePoint database servers

I want to setup a DR farm for the existing production.
First, we tried with log shipping but it turned out that log shipping does not co-exist with Failover Clustering, as stated here
https://msdn.microsoft.com/en-us/library/bb500117(v=sql.105).aspx

Then, I came up with idea of using transactional database replication, but still, have considerations that couldn't find elsewhere

Does Transactional Replication require shared folder? (For example, to copy log file)?
Can Transactional Replication co-exist with Failover Clustering?

Please propose if there's any possible Disaster Recovery solution for this requirements

We don't use Failover Cluster at instance level but server level


texts:
We have started naming our stored procedures as [16_TestStoredProc]. Are there any implications naming a stored procedure like this?

I am not going to explain why we are doing this. It is not that I have issues with this, but would to any implications.


texts: What backup/restore strategy can I use for the following scenario

texts:
We have two databases on a server which are being synced using back up and restore on a scheduled basis. Now, we would like to exclude a few tables getting affected by the back up and restore process.

What is the best way of doing this using backup and restore ?

I tried keeping the tables that should not be affected in a secondary file group and performing a file group back up and restore.

I ran into various issues during the restore process.

The following message was appeared while restoring the PRIMARY file group:

The roll forward start point is now at log sequence
number (LSN) 507000000318400006. Additional roll forward past LSN
507000000318400006 is required to complete the restore sequence.
Gone through some online content and tried restoring the last log backup and got the following     message:

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database.

And then I ran into the following article in sqlservercentral

http://www.sqlservercentral.com/blogs/dotnine-sql-server-and-more/2014/02/20/restoring-filegroup-backups-on-another-server-and-why-it-wont-work/

Question 1: How can I exclude few tables from syncing during a backup and restore process?

Question 2: According to article, I cannot achieve this using backup/restore. I that correct?

Thank you...


texts: Services don't have MySQL Server. How to run it?

texts:
I need to run MySQL Server on a new for me laptop. There's Windows 7. And MySQL have been installed on it. In order to verify this, I opened the Programs and Features window and saw it there:

Programs and Features screen

But when I go to the Services to run it, I don't find it. It's absent there:

Services screen

It's very strange for me. Why MySQL is not among the services? How can I run it? It's installed on this station. Any ideas?


texts:
I am trying to extract blob data from a SQL SERVER 2008 R2 database. The data is annotation data that an application stores. I have done some research and I have been partially successful in converting the data. I get about 20-30 first characters of each entry.
This is the SQL statement I used:

select CONVERT(varchar(MAX), CONVERT(varbinary, blob)) from annotation_data;

How can I get all the data?


texts: I have setup MySQL replication, is there a way to skip replicating one column or multiple columns from few tables?


texts:
We recently upgraded our Dell T110 server to a Dell T710 with a 100GB SSD drive.  We were hoping that we would see a tremendous improvement in mysql select performance, but unfortunately the new system is slower than the old.

I have tried tweaking the fstab and the mysql onfiguration, but nothing has seemed to help.  It seems that for some reason the system is unable to use the full speed of the drive or there is something else slowing it down.

According to hdparm the new SSD drive has a similar read speed and surprisingly a slower cached read speed.

When doing a random access data read test using seeker the SSD drive is much faster.

Old System:
Dell T110, Single 2.4GHz Quad core processor
8GB of RAM
250 GB 7.2K SATA Drive
Mysql 5.1
ext2 filesystem

Timing buffered disk reads:  100 MB in  0.87 seconds = 114.99 MB/sec
Timing cached reads:   22636 MB in  1.99 seconds = 11361.70 MB/sec
Results: 81 seeks/second, 12.28 ms random access time

New System
Dell T710, Dual 2.13GHz Quad core processor
16 GB of RAM
100 GB SSD Drive
Percona 5.5
ext4 filesystem

Timing buffered disk reads:  366 MB in  3.01 seconds = 121.47 MB/sec
Timing cached reads:   10960 MB in  2.00 seconds = 5487.23 MB/sec
Results: 3742 seeks/second, 0.27 ms random access time

The new system has the following /etc/fstab entry:
/dev/mapper/vg_katahdin-lv_root /                       ext4    defaults,noatime,discard,data=ordered,errors=remount-ro        1 1


texts: Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.


texts: How to rank the product higher and the accessories lower? (MySql fulltext search)

texts:
I hope this is not a silly question.

I have a table containg the products of an online store. I am using fulltext search in boolean mode.

People search for, let us say, Apple iPhone 11.

I don't want "Cleaning kit for Apple iPhone 11" to come first and the product itself "Apple iPhone 11" to come second.


texts: Recover the disk space after deleting rows from table

texts:
I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the production queries. Is there any other way to recover the space without affecting the production queries.
innodb_file_per_table is ON and there are about 15,221,827 rows in the table.
There are no constraints or foreign keys.


texts: Should I mark a composite index as unique if it contains the primary key?

texts:
Given some table with a primary key, e.g.:

CREATE TABLE Customers (
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Address nvarchar(200),
   Email nvarchar(260)
   --...
)

we have a unique primary key on CustomerID.

Traditionally i might then need some additional covering indexes; for example to quickly find a user by either CustomerID or Email:

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

And these are the kinds of indexes i've created for decades.

It's not required to be unique, but it actually is

The index itself exists to avoid a table scan; it is a covering index in order to aid performance (the index is not there as a constraint to enforce uniqueness).

Today i remembered a tid-bit of information - SQL Server can use the fact that:

a column has a foreign key constraint
a column has a unique index
a constraint is trusted

in order to help it optimize its query execution. In fact, from SQL Server Index Design Guide:

  If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

Given that my multi-column index contains the primary key, this composite index will de facto be unique. It's not a constraint that i particularly need SQL Server to enforce during every insert or update; but the fact is that this non-clustered index is unique.

Is there any advantage in marking this de facto unique index as actually unique?

CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

It seems to me that SQL Server could be smart enough to realize that my index already is unique by virtue of the fact that it contains the primary key.

But perhaps it doesn't know this, and there's an advantage for the optimizer if i declare the index as unique anyway.
Except perhaps that might now lead to slowdowns during inserts and updates, where it must perform uniqueness checks - where before it never had to before.
Unless it knows the index is guaranteed to already be unique, because it contains the primary key.

I cannot find any guidance from Microsoft about what to do when a composite index contains the primary key.

  The benefits of unique indexes include the following:

  Data integrity of the defined columns is ensured.
  Additional information helpful to the query optimizer is provided.

Should i mark a composite index as unique if it already contains the primary key? Or can SQL Server figure out this for itself?


texts: Is it a bad practice to have several mutually exclusive one-to-one relationships?

texts:
Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc.

Is there anything wrong with such design? Some problems that may occur down the road?


texts: What happens when there is no available physical memory left for SQL Server?

texts:
While googling I found some conflicting information.

Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server: Demystifying TempDb and recommendations).

But other sites state that, when there is not enough physical memory left, the operating system can use the PAGE FILE and move data from the physical memory to it (see Page File for SQL Server).

I wonder where SQL Server writes data when it runs out of physical memory? To tempdb or to the OS Page file? Or maybe both of them?


texts:
I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it?

Address
1 Burton Hills Blvd, Suite 250
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Ford Pl
1 Ford Pl, Suite 5B
1 Kish Dr
1 Park Center Dr, Suite 11
1 Park Center Dr


texts:
In a star schema, can a dimension table such as customer_dim have a column that references another dimension?
For example:
customer_dim

customer_id
first_name
last_name
email
primary_billing_address_dim


texts:
I am developing a web app for my client and there is a section which displays the changes made in the application by any of the user. This section is known as log.It basically stores and displays the events triggered by the users. I am storing these logs in an external file for time being. But it seems as the client wants to perform certain operation on those logs too.

Hence, my question is that is it right to store all the log files in a database file instead of external file, because there are some limitations in file operation as we know. Also should we make a new table to store the log or associate it with the user in the user table.


texts:
I am using MS Access 2010 and trying to create a parameterized query on one table.

My table has many latitude/longtitude coordinates. I wish to pass one lat long point (two parameters) into a query and return all table records which are within a given distance.

The distance calculation is quite involved when working in spherical space.

I want to avoid executing the full spherical calc if it is obvious that the point being considered is not a suitable candidate (the vast majority of the table's candidates.)

Given that the MS Access function IIF() will evaluate everything despite suitability, as will switch(), etc. I would like guidance which would help me avoid executing the longer calculation. I can write VBA, but I do not know if calling a VBA function every time a record is considered would be very efficient.

Thanks!


texts: How to troubleshoot db/app latency during transaction log backup

texts:
I have an application that uses a SQL server (2008 R2) database and we are periodically having performance problems with the application and it coincides with our 15-minute transaction log backups.

The application controls latency sensitive industrial machinery and we are finding that when the system has a high transaction volume occurring, the application has queries that run too long (>5 seconds for a brief period of time). These queries only run slow at the same time when the transaction log backups are happening so it appears to be related to the transaction log backups. When the system is performing without problems, our transaction log backups take 2-4 seconds and when we are under a heavier load and we have problems they are taking 6-7 seconds. That apparently is just enough to cause some FIFO message dispatch queues to fill up on the application.

First of all I was under the impression that transaction logs should be pretty transparent to the application, with no locking or anything else going on. Does this point to some kind of IO contention as being invoved if we are seeing database latency when we are doing the transaction log backup. What are the pros and  cons of moving to something like a 5-minute transaction log backup cadence instead of 15-minute?

Disk backend is a NetApp FAS2220 with a bunch of 600 GB 10k SAS drives. The DBA is convinced that this is an application problem and not a database problem so I need to know how to troubleshoot this to either being a problem with the application or the database.

TLDR: Database or application latency seen under heavy load during transaction log backup. How to troubleshoot and resolve?


texts: Postgres SELECT rows where JSON array contains "most" of values

texts:
I have the following table structure:

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    labels json
);

INSERT INTO items (labels) VALUES
('{"labels":[{"value":"apple","score":0.95},{"value":"fruit","score":0.94},{"value":"red","score":0.93}]}'),
('{"labels":[{"value":"apple","score":0.92},{"value":"fruit","score":0.92},{"value":"green","score":0.93}]}'),
('{"labels":[{"value":"orange","score":0.92},{"value":"fruit","score":0.92},{"value":"orange","score":0.90}]}'),
('{"labels":[{"value":"tomato","score":0.98},{"value":"vegetable","score":0.96},{"value":"red","score":0.95}]}'),
('{"labels":[{"value":"carrot","score":0.94},{"value":"vegetable","score":0.93},{"value":"orange","score":0.92}]}')
('{"labels":[{"value":"peach","score":0.92},{"value":"fruit","score":0.92},{"value":"yellow","score":0.91}]}')

I'm trying to find a way to query this table by the labels column, using the "intersection" criteria with a factor of similarity >= 2.

e.g. if I set ["peach", "fruit", "orange"] array as the WHERE clause input, then the result should be:

 id|
---+
 3 |
---+
 6 |
---+


texts:
I apologize, I am not sure how to word this question. It probably has been answered somewhere, but I couldn't find it if so.

I have three tables that I want to combine in one using a common id column, with empty rows here and there.

table1

 id | col1
----|------
  1 | 23.4
  3 | 13.4
  4 |  7.6

table2

 id | col2
----|------
  1 | 12.2
  2 | 17.1

table3

 id | col3
----|------
  1 | 22.2
  2 | 14.1
  5 | 10.8

I want to create a fourth table table4:

 id | col1 | col2 | col3
----|------|------|------
  1 | 23.4 | 12.2 | 22.2
  2 | NULL | 17.2 | 14.1
  3 | 13.4 | NULL | NULL
  4 | 7.6  | NULL | NULL
  5 | NULL | NULL | 10.8

I'm stumped as to how to approach this problem honestly.


texts: SQL Server Diff not restoring from Azure BLOB storage

texts:
I'm setting up a process to back up a SQL Server 2016 database to Azure blob storage and subsequently restore to another server. I'm doing a weekly full back up and hourly differential. The issue I'm having is that the differential will not restore over the full back up.

Back up to Azure:

BACKUP DATABASE [DB_NAME] TO
URL =
N'https://DB_BLOBNAME.blob.core.windows.net/livebackup/DB_NAME_FULL.bak'
WITH  CREDENTIAL = N'BackupCred' , FORMAT, INIT,
NAME = N'DB_NAME_FULL', SKIP, REWIND, NOUNLOAD,  STATS = 10,  COMPRESSION
GO

BACKUP DATABASE [DB_NAME] TO  URL =
N'https://DB_BLOBNAME.blob.core.windows.net/livebackup/DB_NAME_DIFF.bak'
WITH CREDENTIAL = N'BackupCred',  DIFFERENTIAL ,
NOFORMAT, NOINIT,  NAME = N'DB_NAME_DIFF', NOSKIP, NOREWIND, NOUNLOAD,
STATS = 10
GO

I'm using Azcopy to bring the two .bak files down to the local disk, no issues there. Then restoring from Local file system.

DROP DATABASE DB_NAME
GO
RESTORE DATABASE DB_NAME FROM DISK =
'C:\AzSyncFolders\DB_NAME\DB_NAME_FULL.BAK' WITH NORECOVERY
GO

RESTORE DATABASE DB_NAME FROM DISK =
'C:\AzSyncFolders\DB_NAME\DB_NAME_DIFF.BAK' WITH RECOVERY
GO

All pretty straightforward stuff, but I'm consistently getting this message.

    Msg 3136, Level 16, State 1, Line 8
    This differential backup cannot be restored because the database has not been >>restored to the correct earlier state.
    Msg 3013, Level 16, State 1, Line 8
    RESTORE DATABASE is terminating abnormally.

When I do a full and differential back up on the server locally using the same scripts they both restore correctly.

Am I missing anything obvious in my scripts either in the back up or restore?

Definitely no additional full back up between FULL and DIFF

Just using .BAK out of convention, not sure that having the .DIFF extension will make a difference.
Will check to see if there are multiples in the .BAK. No multiples in there.

Changed to use INIT, still getting the error.

**** Update  ****
Apparently there's a third party tool also taking backups to another location. This would seem to be causing my mismatch between the full and diff LSN. Looks like I'm going to have to take more full backups and abandon the use of diffs. Scott Hodgin called it on the first answer. Thanks for all the help.


texts:
SQL Server service restarted/stopped, which cause the production database down n find this errors in System log.

  ERROR5:The Cluster service failed to bring clustered service or
  application 'SQL Server (MSSQLSERVER)' completely online or offline.
  One or more resources may be in a failed state. This may impact the
  availability of the clustered service or application.

  ERROR4:Cluster resource 'SQL Network Name (tcsdb)' in clustered
  service or application 'SQL Server (MSSQLSERVER)' failed.

  ERROR3:Cluster network name resource 'SQL Network Name (tcsdb)' cannot
  be brought online. The computer object associated with the resource
  could not be updated in domain 'ABC.local' for the following reason:
  Unable to get Computer Object using GUID.

  ERROR2:Cluster resource 'SQL Network Name (tcsdb)' in clustered
  service or application 'SQL Server (MSSQLSERVER)' failed.

  ERROR1:Cluster network name resource 'SQL Network Name (tcsdb)' cannot
  be brought online. The computer object associated with the resource
  could not be updated in domain 'ABC.local' for the following reason:
  Unable to get Computer Object using GUID. how we can fix this issue


texts: Mysql: Insert into JSON column type with GROUP_CONCAT

texts:
I'm trying to insert a list of words into a column with the JSON type:

create table my_table (
    ...
    synonyms JSON
    ...
)

My query looks like this:

INSERT into my_table (id, synonyms)
SELECT id, GROUP_CONCAT(DISTINCT synonyms) as synonyms from (
  SELECT id, name1,name2, GROUP_CONCAT(DISTINCT name1) as synonyms
  FROM products
  GROUP BY id

  UNION

  SELECT id, name1,name2, GROUP_CONCAT(DISTINCT name2) as synonyms
  FROM products
  GROUP BY id
) t group by id;

If run without the INSERT in line one, the synonyms column returns a list of comma separated words:

id       name1    name2    synonyms
------------------------------------
1        one      a,b      one,a,b
2        two      c        two,c

I want to insert those synonyms as a JSON_ARRAY.  However, the INSERT fails when the individual words are not double quoted.  If I add JSON_ARRAY in my select:

SELECT name1,name2,id, JSON_ARRAY(GROUP_CONCAT(DISTINCT synonyms)) ...

I get the following result:

name1    name2    synonyms
----------------------------
one      a,b      ["one,a,b"]
two      c        ["two,c"]

When what I want is this (so it can be inserted directly into the synonyms column):

name1    name2    synonyms
----------------------------
one      a,b      ["one","a","b"]
two      c        ["two","c"]

Is there a way to do this with SQL, or is it better handled within application code?


texts: What to do when MySQL is not generating any logs at all on Debian?

texts:
The problem I'm having is that MySQL (5.5.46-0+deb7u1) is not generating log files. This on Debian Wheezy.

The my.cfg file has been updated according to the required config for logging ALL queries (as per http://www.microhowto.info/howto/log_all_queries_to_a_mysql_server.html). It's not writing to the specified location, nor to the default location.

Mysql errors are also not being logged, as far as I can tell.

EDIT: my.cnf is here: http://pastie.org/private/q9vwgihslpenc94mxmyyiw

EDIT: Here's the output of "show variables like '%warn%';":

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
| sql_warnings  | OFF   |
| warning_count | 0     |
+---------------+-------+


texts: postgres 9.3. resets log_min_duration_statement after receiving SIGHUP

texts:
I'm using PostgreSQL-server 9.3.5. After setting log_min_duration_statement to 1000ms I'm sending SIGHUP and see that server resets value to '-1':

-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 -1
(1 row)

postgres=# select set_config('log_min_duration_statement', '1000', 'true');
 set_config
------------
 1s
(1 row)

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 -1
(1 row)

postgres=#

What may be an a root cause?


texts: psql's \set command unable to save json file's content in a variable

texts:
I am trying to below set command inside bash:

#!/bin/bash

psql -X -h localhost -U <dbuser> -d <dbname> <<EOF
CREATE TABLE cardtable(id SERIAL PRIMARY KEY, charcolumn VARCHAR(20) NOT NULL, cardcolumn JSON NOT NULL);

\set var `cat /tmp/jsonfile.json`
INSERT INTO cardtable(charcolumn, cardcolumn) VALUES ('USA', :'var');

EOF

Content of jsonfile.json:

{
    "OT": [
        {
            "CD": 40,
            "ED": "2013-12-23",
            "ID": [
                "daf52501-330a-48a-d40988fa7a-a8",
            ]
         }
     ]
}

for some reason, the content of "var" was just '{' !! As a result INSERT command treats it as if the json data is of in-correct format and throws a syntax error.

Can someone please help me in understanding why "\set" command is behaving like this?

FYI: same command when executed manually at the psql prompt ends successfully!!


texts: How to join SQL tables where value is Between min and max value

texts:
I am currently looking to join two tables together based on an IP and IP range. Both the IP and Range are currently stored as BIGINT.

For example we have a table MasterData with IPs in integer format [IPInteger] and we have another table called IPInformation which has all IP data but also has [start_ip_int] and [end_ip_int] range.

What I would like to to is join all the data in MasterData and All the data in the IPInformation based on the IPInteger being within the range of [start_ip_int] and [end_ip_int] on the IPInformation table.

Thanks in advance for any assistance you can provide.


texts:
Is there anything like MySQL workbench for PostGIS? Something that supports SQL output and geometry types would be nice.

CLARIFICATION:

I am trying to implement the Land Administration Domain Model and all I got are the specs ( UML diagrams). I need a tool that can create the models and output code that would create the tables. It needs to support geometry types as the LADM deals with parcels.


texts:
I have two projects working on different databases. The problem is: I need to replicate changes in the databases only for a few tables. I need it to synchronize the data.
Need an advice how can I solve it.
I have PostgreSQL 9.1 DB servers, the replication schema is master-master. What I found is:

Rubyrep
Bucardo
Postgres-XC


texts: Using Mirroring to upgrade SQL Server 2008 R2 to SQL Server 2012

texts:
I am attempting to mirror a SQL Server 2008 R2 database currently in a fail over with the purpose of doing an in place upgrade. I have installed SQL Server 2012 on another machine to serve as my secondary and SQL Server 2012 Express Edition on a third server to serve as my witness. All of the servers are able to communicate with each other on the specified ports via telnet.

The databases all use mixed mode authentication and when installing the SQL Server the same domain accounts were used for the services the SQL Server uses.(Server Agent and the Server itself) The user I am using for the mirror is on the domain, set up as a local admin to the servers, and sysadmin to the SQL Servers.

I have followed several guides on how to set up the mirror. When I use the wizard to try to do this it completes all of the steps and says success on setting up the endpoints. I click on start mirroring and it states that it is unable to connect to the 2012 installation (the server network address "tcp://2012Installation.domain:port" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational(Microsoft SQL Server, Error: 1418)).

I have copied all of the network addresses and they work with telnet, as well as having the firewalls down. In addition, the 2012 Installation's database cannot be dropped as it is set up for mirroring. I have followed all of the recommendations here to no avail. The server it is saying it cannot communicate with is the 2012 SQL, which I am trying to mirror stuff too. It is also the box I am using SQL Management Studio on.

What I think may be the issue is that the original 2008 SQL Server was part of a failover cluster, so it has two different names.(One for the cluster and one for the server itself) It appears as though the mirroring stuff is trying to use the cluster name. I have tried using an alter statement to change the listening partner to the server name itself, but that did not work either. Does any one have any idea why this would not work?


texts: How to know if a DB transaction is successfully committed in case of timeout?

texts:
Out of curiosity:

For example, I issue commit at the end of a transaction and wait for the response from the database. If for some reason, the waiting times out, how can I know if this transaction has been successfully committed and thus made durable by the DB?

I supposed 3 possible reasons for the timeout:

The commit command is not delivered to the database.
The commit command is well delivered to the database but for some reason not processed by the DB and DB is just stuck there.
The commit command is well delivered to the database and processed by the DB, but for some reason, the response from the database cannot be delivered to my client application.


texts:
I am trying to figure out the disk size a number of rows in various tables take up.

I can define this rows by a query. How can I find out the actual disk size that corresponds to the result set in SQL Server?

The reason why I want to know this, is that I am trying to analyse a multi-tenant system.
I am trying to get the total size each tenant takes up on the system.  So I need the size of all rows pertaining to each tenant and the size each tenant is using in index tables, etc.

The tenants all share the same schema, so it is not easy to do.


texts: SQLite where condition - compare two last rows


texts:
I have a mean-stack website www.myweb.io. I need to upgrade the code and the database. In the previous database, I have a schema:

var UserSchema = new mongoose.Schema({
    username: {type: String, lowercse: true, unique: true},
    ...
})

I decided to change a lot UserSchema, especially to delete username.

I just uploaded the code from dev to prod. Adding the first user worked fine, whereas adding another user gave me:

events.js:182
      throw er; // Unhandled 'error' event
      ^
MongoError: insertDocument :: caused by :: 11000 E11000 duplicate key error index: news.users.$username_1  dup key: { : null }
    at Function.MongoError.create (/opt/funfun/node_modules/mongoose/node_modules/mongodb/node_modules/mongodb-core/lib/error.js:31:11)

I guess it is because both of the two new users don't have username. But why does the system still remember username is the key? In MongoDB, we don't have to change schema, right?

Additionally, I backed up the previous database of production, and copied to development, and restored it. Oddly, unlike production, creating two new users did not give error in development.

Does anyone know if I have to change schema in production? and how?


texts: Is there a PostgreSQL proxy running queries on several servers at once?

texts:
Let's assume we had PostgreSQL version X.Y and decided to give a test-try to version Z.W. Probably, there are some changes between X.Y and Z.W that could cause a little bit of trouble :-)

It would be preferable to run Z.W in parallel with X.Y, checking whether it goes well and using only X.Y's replies for a while. Later on, having Z.W's DB "ok" we could use it as primary, discarding X.Y optionally.

Question: Is there any PostgreSQL proxy that can run queries on several servers at once?


texts: SQL Server query with Linked Server running without Parallelism

texts:
I have a query that includes accessing a table via linked server. When it runs it's doing so without parallelism. But if I comment out the join to the linked server table it runs with parallelism and completes almost instantly.

The row count of the table accessed by the linked server is 2m.

Could the linked server be causing the query to run without parallelism?

Here is the plan.


texts:
I am trying to launch LSNRCTL on my Windows 10 64-bit laptop; Oracle 18 XE
I get this error message:

C:\app\Owner\product\18.0.0\dbhomeXE\bin>lsnrctl
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-APR-2021 13:04:13
Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Message 850 not found; No message file for product=NETWORK, facility=NL
LSNRCTL>

My ORACLE_HOME variable is set to: C:\app\Owner\product\18.0.0\dbhomeXE\bin
Andrew Sayer suggested:

Either unset your oracle_home or set it to c:\ app\Owner\product\18.0.0\dbhomeXE (assuming that’s the real directory)

I followed the suggestions, here is the output:
C:\>lsnrctl status
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-APR 02114:18:42
Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.15)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory

listener.ora file:
DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Owner\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Owner\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.15)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

AFAIK all of the above directories exist.
EdStevens said:

You have "TNS-12541: TNS:no listener ".  Your listener is not started.

When I try to start the listener I get this message:
C:\>lsnrctl start

LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-APR-2021 17:00:21

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Message 1070 not found; No message file for product=NETWORK, facility=TNSTNS-12557: Message 12557 not found; No message file for product=NETWORK, facility=TNS
 TNS-12560: Message 12560 not found; No message file for product=NETWORK, facility=TNS
  TNS-00527: Message 527 not found; No message file for product=NETWORK, facility=TNS


texts: Executing query against Azure SQL db using Linked server takes hours

texts:
I have a linked server to Azure SQL database and I am trying to run the below SQL query which takes hours to run and at one point, I then have to cancel the execution. I have researched everywhere and have tried everything but no result. This query takes about 3-4 minutes when I run it on local, the reason being the DealerMediaRates table has about 7.8 million records. I could probably optimize this query, maybe that probably solves the Azure issue but I find it hard to believe that this query would run for hours when I use Azure linked server that I eventually have to stop. Our SQL server tier in Azure is S2, we even tried switching it to S3 but no luck.

UPDATE  dmr
SET     Quantity = t.[One50K],
        LastModifierRoutineName = @LastUpdatedRoutineName,
        LastModifiedUtc = GETDATE()
FROM    AZUREDB.dbname.dbo.DealerMediaRates dmr
INNER JOIN AZUREDB.dbname.dbo.DealerMedias dm ON dm.DealerMediaId = dmr.DealerMediaId
INNER JOIN AZUREDB.dbname.dbo.Dealers d ON d.DealerId = dm.DealerId
INNER JOIN AZUREDB.dbname.dbo.DealerGroups dg ON dg.DealerGroupId = d.DealerGroupId
INNER JOIN AZUREDB.dbname.dbo.DealerMetadataValues dmdv ON dmdv.DealerId = d.DealerId
INNER JOIN AZUREDB.dbname.dbo.MetadataFields mf ON mf.MetadataFieldId = dmdv.MetadataFieldId AND mf.ClientId = @MetisClientId AND mf.MetadataCode = 'Real Green #'
INNER JOIN SAExMark.dbo.RealGreenZipCodeData t ON t.ParentCompanyID = dmdv.[Value] AND t.DealerID = dm.SubDescription AND t.Zip = dm.[Description] AND dm.MediaCode = 'DM' AND t.[SegmentCode] = 'RA'
WHERE   dmr.MediaRateClassificationId = 13
AND     dmr.MediaSegmentId = 5

THIS TAKES ABOUT 7 MINS to RUN

UPDATE  dm
SET     IsDeleted = 1,
        LastModifierRoutineName = @LastUpdatedRoutineName,
        LastModifiedUtc = GETDATE()
FROM    AZUREDB.dbname.dbo.DealerMedias dm
INNER JOIN AZUREDB.dbname.dbo.Dealers d ON d.DealerId = dm.DealerId
INNER JOIN AZUREDB.dbname.dbo.DealerGroups dg ON dg.DealerGroupId = d.DealerGroupId AND dg.ClientId = @MetisClientId
INNER JOIN AZUREDB.dbname.dbo.DealerMetadataValues dmdv ON dmdv.DealerId = d.DealerId
INNER JOIN AZUREDB.dbname.dbo.MetadataFields mf ON mf.MetadataFieldId = dmdv.MetadataFieldId AND mf.ClientId = @MetisClientId AND mf.MetadataCode = 'Real Green #'
LEFT OUTER JOIN SAExMark.dbo.RealGreenZipCodeData t ON t.ParentCompanyID = dmdv.[Value] AND t.DealerID = dm.SubDescription AND t.Zip = dm.[Description] AND dm.MediaCode = 'DM'
WHERE   t.DealerID IS NULL
AND     dm.MediaCode = 'DM'
AND     d.Name NOT LIKE '%Test%'

]3


texts: frm-92100:your connection to the server was interrupted

texts:
frm-92100: error I use my using internet explorer & JInitiator 1.3.1.13 using oracle 11g and dev9i windows server 2008 R2. The app was running fine for last 2 years but suddenly this happen.

I have tried and read almost every page i found from google search but it doesn't help me.

what I tried
I set NLS_LANG to a valid value like AMERICAN_AMERICA.AR8MSWIN1256 in the default.env file which is being used. then it not allow me to login anymore. Also try to set it on environment/registry the result is same.

Only the login form is not loading but all other form run manually from my app.

Please help me.


texts:
I am creating a database for Job offers website between Employer and Applicants that required sending Email between the two in MSSQL. Let's say I have 3 tables like these:

Employer
ID int idetity(1,1),
Name nvarchar(50),
Email nvarchar(50)

Applicants
ID int identity(1,1),
Name nvarchar(50),
Email nvarchar(50)

Mail
MailID int identity(1,1),
Sender nvarchar(50),
Receiver nvarchar(50),
Title nvarchar(100),
Content nvarchar(1000),
Status nvarchar(50)

In Mail table, the Status indicate whether the Mail is sent, unread, read, or delete. Is this the right way to do this? Should I link the Mail table with Employer and Applicant? If so, should I link what with what? Since I want to get the sent Mail of Employer and Applicant, check whether mail is Read,Unread or deleted.

Any help is appreciate


texts: Vacuum settings for mostly append-only data

texts:
I have a table with the following characteristics:

We INSERT a few 100k rows each day
We never UPDATE the rows
We DELETE "old" data once a week

From my shallow knowledge of Postgres VACUUM, it seems like this table might be a good candidate for a non-standard VACUUM strategy.  A few questions I have...

Apart from the weekly purging of old data, will a VACUUM do anything?  Seems to me there is nothing to compact/clean if we're only doing INSERTs.
Would it be a horrible idea to simply disable AUTOVACUUM on that table and manually VACUUM FULL after our weekly DELETE?


texts: How to query large sets of data using server resources to the max

texts:
I am planning of retrieving large amounts of data (100+ million rows) from SQL Server to Azure Data Lake Store and I need it to be done in shortest time possible. I would like to ensure that when I query the SQL Server database, it's utilizing the CPU, memory, and network resources available to the maximum.

I was wondering: How do I ensure that SQL Server gives my task its top priority and uses all the power the server has to transfer the data as fast it can?

Are there any hints I can give to the database to tell it that this job needs all of the server's resources?
Would it help if instead of a single SELECT, I issue several SELECT statements in parallel, each retrieving just a chunk of the data (e.g. first select retrieving rows 1 to 50000000 and second select retrieving rows 50000001 to 100000000)?
Anything else that can be done to ensure data is transferred as quickly as possible?

The server is in the cloud, so in theory I can size it so all resources are matched perfectly. I understand in reality something will become the bottleneck.


texts: CMD prompt can't display Unicode characters that are stored in oracle database correctly

texts:
I've been meddling with charset/encoding problems of Oracle database, PL/SQL developer and JDBC and these are what I want to achieve:

Input Unicode characters from a browser into the database
Display Unicode characters that are retrieved from the database correctly in the browser, CMD prompt, and PL/SQL developer

And these are what I've done:

Change the server charset to AL32UTF8 by following the steps:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL32UTF8;
SQL>update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';
SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP

Change the client charset to AL32UTF8 by adding system environment variable NLS_LANG with value AMERICAN_AMERICA.AL32UTF8and changing the value of NLS_LANG to AMERICAN_AMERICA.AL32UTF8 under HKEY_LOCAL_MACHINE>SOFTWARE>ORACLE>KEY_OraDB12Home1 in regedit
Check "Always UTF8" under Tool>Preferences>Files>Format>Encoding in P/SD

Eventually if I added the Unicode characters to the database from either the browser or P/SD they can be displayed properly in the browser and P/SD but not in the CMD prompt. If they are added to the database from the CMD prompt they can be displayed correctly everywhere. For now I'm sure that the characters input from the browser and P/SD are Unicode characters, but not for the CMD input, which is also supposed to be Unicode since both the server and client charsets have been set to AL32UTF8. Here's an example of a correct display and an incorrect one:

In P/SD: (Correct)

SQL> select title from posts where id=134;
TITLE
--------------------------------------------------------------------------------
测试

SQL> SELECT DUMP(title, 1016) FROM posts where id=134;
DUMP(TITLE,1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=AL32UTF8: e6,b5,8b,e8,af,95

In command prompt: (Incorrect)

SQL> select title from posts where id=134;

TITLE
--------------------------------------------------------------------------------
娴嬭瘯

SQL> SELECT DUMP(title, 1016) FROM posts where id=134;

DUMP(TITLE,1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=AL32UTF8: e6,b5,8b,e8,af,95

SQL>


texts: SELECT stuck giving results from the query result cache, though table has changed

texts:
A process that runs every two minutes or so started to misbehave. We noticed it was because the cursor fetched repeatedly 0 rows, even if indeed there were new rows satisfying the conditions in the where conditions.

The select of the cursor is like this:

SELECT
       colk, col2
FROM
       a_table
WHERE
       status = 'A' AND
       processed = 'N'
ORDER BY
       mod_date DESC NULLS LAST, ins_date;

For every fetched row, the process does things (not lengthy things), then it updates that row through the unique key colk, like this:

UPDATE
       a_table
SET
       processed = 'Y',
       mod_date = DECODE(:t, 0, mod_date, SYSDATE),
WHERE
       colk = :just_fetched_colk AND
       status ='A';

When there are no other rows, we commit.

Before the process runs, when the process is running and after the process is run, there can be events that call a “task” which inserts or updates some rows (one per event). The update is the same as the one above, except that processed is set to N. New rows are inserted with processed to N and ins_date given by SYSDATE.

At some point, as said, at each run of the process no rows were fetched, even if there were hundreds thousand changes “accumulated” in several days.

“Professional” explanation after analysis: when the result cache is enabled, there are “restrictions” you must comply with. If you don't, you can't expect Oracle surely invalidates the result cache, since it is not necessarily able to track dependencies. Our update uses SYSDATE, while the select does an order by the date-column we update using that SYSDATE. This causes problems.

In my opinion this is a totally wrong and imaginative explanation, and also a wrong interpretation of the limits of the result cache I've read about in Tuning the result cache, in particular in the section Setting the Result Cache Mode, second paragraph.

This apart and “technical details” apart, I can't buy any of that without calling this behaviour a dangerous bug (so, our fix is a workaround for an Oracle bug, not a patch for a mistake of ours). In fact I am convinced that if rows in the table the select “depends” on are updated (no matter how), then Oracle must invalidate the result cache — I am also sure there's nothing hard to track in our select…

If Oracle does not invalidate the cache when it blatantly should, it fails to assure a basic promise software usually relies on: shouldn't a select see the most recent “situation” left by all the committed transactions? (If not the most recent, at least not totally stuck to a certain moment…)

The answer to this question, suggested while picking an explanatory title, seems to confirm I am right (following the spirit and the links to the query result cache). Therefore the question isn't «are their explanations reasonable?» anymore.

It becomes: any idea of what's going on? Similar experiences? (I can't find any). I know, too broad. Unfortunately, I am not able to give more details. In particular I wonder if the suggested workaround can be considered a safe fix, since in fact we can't be sure we identified the problem properly, or rather it will be more safe to keep the NO_RESULT_CACHE hint.

(The exact release is 11.2.0.4.0, 64bit)


texts: Does `WITH XMLNAMESPACES` have the ability to trigger a DDL database trigger?

texts:
I'm having an issue where, intermittently, a stored procedure is causing a database trigger to fire. The trigger is for CREATE_FUNCTION, CREATE_PROCEDURE, CREATE_TABLE, CREATE_VIEW, CREATE_TRIGGER
but the procedure itself does not do any of those things. It's a straightforward write procedure that takes an XML parameter and inserts the values into a set of tables. The one thing that is unusual (for us) is that it's using WITH XMLNAMESPACES but it would seem weird for that to be the issue.

Disclaimer: I'm tackling this problem at once removed. I'm having to trust the original developers that they aren't also trying to create an object as part of their code above the database (they say that's the case). I'm also checking all of the tables being written into right now to make sure they don't have weird triggers that try to do something they shouldn't. So far, I've not been able to duplicate the effect in a test environment, even when impersonating a user with minimum access. This is on a SQL Server 2014 server.

This issue came to light because the point of the database trigger is to only let the "build" login create objects on the database.

So to keep my question narrow, is it possible for WITH XMLNAMESPACES to be the issue here?

UPDATE: As a follow up, I was able to get the necessary information. It looks like the error was being caused because the calling code, after calling the stored procedure in question, also called:

exec sp_describe_first_result_set N' EXEC usp_Problem_procedure @P1 ',N'@P1 xml'

And it looks like this is what's actually causing the error. It also looks like the trigger was ultimately not the only problem. Even fixing or disabling the trigger just caused a later error:

Msg 11528, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'REVERT

    --Check if SSB is enabled in this database' in procedure 'sp_send_dbmail' does not support metadata discovery.

Apparently one of the nested procs has the potential to use sp_send_dbmail (we discourage this, fwiw, as it's destroyed the Exchange server more than once).

So that's it. Once I showed the developers what was happening, they recognized that they just needed to call a method slightly differently, and that avoided the problem.


texts: Is using multiple foreign keys separated by commas wrong, and if so, why?

texts:
There are two tables: Deal and DealCategories. One deal can have many deal categories.

So the proper way should be to make a table called DealCategories with the following structure:

DealCategoryId (PK)
DealId (FK)
DealCategoryId (FK)

However, our outsource team stored the multiple categories in the Deal table this way:

DealId (PK)
DealCategory -- In here they store multiple deal ids separated by commas like this: 18,25,32.

I feel that what they did is wrong, but I don't know how to clearly explain why this is not right.

How should I explain to them that this is wrong? Or maybe I'm the one who's wrong and this is acceptable?


texts: IntelliSense is enabled but not working in SQL Server 2016 with Azure SQL DB

texts:
I have installed SQL server 2016 whose details are as in below screenshot:

My queries are pointing to Azure SQL DB.

Below are the screenshot of some other settings that I have found over the internet and applied:

I have also tried by refreshing the local cache and reconnecting to the database.

Please share your expert opinion here and help me to make this work.


texts:
I'm currently working on a project to convert our Access Apps to store the table data in SQL Server.

Using SSMA to migrate the data results in the foreign keys and Look-ups work as expected in Access when completed. However, if I import & link directly from SQL Server back to Access, the foreign key look-ups do not work.

The only difference I can determine in the links is, the ones SSMA created do not specify a DSN and the ones I manually created do.

I'm hopefully someone might have run into this in the past and might have some insight.

We are migrating from Access 2013 to SQL Server 2012.


texts: Should I index before or after importing a dump?

texts:
I am using mongodb to persist a very big-size (90G), which has nearly 40,000,000 items. I read and parse this file and insert all items into mongodb (my programming language is perl, batch_insert, and I map one item to one mongodb document).

Before I insert, I have already pre-created indexes (about 10 index keys). I find that  insert speed cannot meet my need (200 to 400 items per second). I know that too many index keys will definitely slow down my insert, especially when the size of collection becomes quite big. So I wonder if I can index them after I have dumped all the data into db.

Can anyone tell me if this way is available, and if it can definitely save my time?


texts: Howto use typeperf to store performance monitor values in a custom schema?

texts:
I have setup a performance monitoring of my SQL server system using typeperf.exe to store the values directly to SQL Server.

I went through the following article, that explains howto use typperf.exe with correct parameters and a counter configuration file to store the results into a SQL server database, after a connection to this SQL server has been configured in a local System-DSN:

Collecting Performance Data into a SQL Server Table
20 January 2011 by Feodor Georgiev

I use a command as article explains like to this one:

TYPEPERF -f SQL -s SqlServer1 -cf “C:\Counters.txt” -si 15 -o SQL:SQLServerDSN!log1 -sc 4

After that I have the monitored values stored in my SQL server (as configured in the SystemDSN) and can easily query those (without having to bother with the different counter types and calculation of correct average values because of accumulated values)... e.g. like this:

SELECT  MachineName ,
    CounterName ,
    InstanceName ,
    CounterValue ,
    CounterDateTime ,
    DisplayString
FROM    dbo.CounterDetails cdt
    INNER JOIN dbo.CounterData cd ON cdt.CounterID = cd.CounterID
    INNER JOIN DisplayToID d ON d.GUID = cd.GUID

It works fine - except one detail: the tables are created in the dbo schema. I would like to have them in another schema.
Anyone knows if this is possible and how?

Edit:
I tried to use Windows Authentication because it always fails using SQL Auth and I found this article explaining that SQL Auth is kind of broken in DSN: http://forums.hexus.net/windows/178393-windows-server-2008-perfmon-logging-database.html

So changed to Windows Auth, I figured out the Account SQLAgent is running on, setup a new Job that runs the Batch and to be sure ECHOed out the user name in a log. It's the user the SQL Agent is running with.

This user has only the server role PUBLIC and is mapped to msdb. So I mapped it to the destination DB (the DB that I have defined in the SystemDSN where I want the PerfCtr tables in) with default schema I want the tables to be in and granted him permission to ALTER the schema and assigned datawriter role for the db.
After the JobAgent ran the job, the PerfMon tables successfully have been created -> but now they are in dbo schema ?
- How can this user create objects in dbo, that from my perspective it does not have permission to ?
- Why is the default database setting for this user not used by the connection defined in the system DSN?


texts: What are the mandatory/required oracle 11g tablespaces?

texts:
Ok, what are the mandatory tablespaces in oracle 11g.  My research has lead me to believe there are three of them: System, Sysaux, and Undo.  However since I am taking the OCA and all the questions(and articles) I have read about mandatory spaces and answered only say two spaces are mandatory, and depending on where question came from the answer is different.

System is mandatory.  That is a fact and undisputed.

The Sysaux and Undo tablespaces are the ones in question.  Which one?  Again my research has lead me to believe all three are, but oracles documentation is unhelpful at this point(or maybe I am using the wrong keywords in search?) and the practice questions lead me to believe that the test is looking for two tablespaces.


texts: Getting the information that exists in "Job properties -> Notifications" page?

texts:
I use SQL Server 2008 R2 and I need get some properties from the msdb database.

I need a query to get information that exists in the Job properties -> Notifications page.

In which table I can find this information?


texts: Ignore a particular column on update trigger

texts:
I have a PostgreSQL table with 15 columns. I have an on update trigger on it. I'd like to exclude column10 from it. Meaning when there is any update on column10 only then there should not be any action other than it. If full update or any other column update, trigger should fire as normal.

I don't seem to find any easy way to do this. Can someone suggest please?


texts: How to use case and filter with a group by statement and having an aggregate calculation?

texts:
I would like to do the following

SELECT
  PO.col1,
  PO.col2,
  CASE
       WHEN PO.col3 <> 8 THEN PO.col4
       WHEN PO.col3 = 8
            THEN CASE
                      WHEN (ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2)) > 0
                            AND SUM(CAST(PO.col5 AS double)) > 0
                            THEN ROUND(CAST(PO.col4 AS double) - SUM(CAST(PO.col5 AS double)), 2)
                      END
       END AS Quantity
FROM
  my_table AS PO
GROUP BY
  PO.col1, PO.col2
HAVING
  Quantity > 0

I apologize for not being able to provide sample data/columns. I am not allowed to share my work data.

The logic which I have been told to implement is as follows.

For each unique pair combination of col1, col2, get a constructed attribute called Quantity. Quantity = col4 wherever col3 = 8. If col3 != 8, then check SUM(col5) > 0. If yes then check col4 - SUM(col5) > 0. If yes, then keep the record, else skip.

Out of my own observation I checked that COUNT(DISTINCT col4) for each unique pair of col1 and col2 is 1. I hope that's helpful answering my question.

We are working with Spark SQL so I don't know what tag to use for this in the question.

SAMPLE DATA


texts: Getting the last data update time of a specific table in Oracle

texts:
How can I get the last update time of table, i.e. at what time the last operation (insert/update/delete) was performed on a table in oracle?

I have tried using:

SELECT MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN)) from mytablename;

But this gives me an ORA-08181 error ("specified number is not a valid system change number").


texts: No-SQL Database Design for restaurant app

texts:
I've been working with SQL databases all my life and I am now trying to get into the NoSQL world. I am a looking for a piece of advice on whats the best practice in order to achieve this.

Suppose I have a table that contains all the meals on the menu and another with the the waiters information. I want to create a third table for the orders. What is the best practice to archive this using a NoSQL database?

In traditional SQL I would have made a table called Orders with an ID and foreign key pointing the waiter id. And I would have created another table relating meals id with order id.

What is the recommend approach to do this in a NoSQL database?

You must have into consideration there will be a "client" running in the kitchen showing the orders.

If it's any relevant I will use parse.com as backend.

Greetings!


texts: Create sequence starting with maximum id from a table

texts:
I'm trying to create a sequence in the following way:

create sequence some_seq start with (select max(id) + 1 from some_table);

But I'm getting the following error:

ERROR: syntax error at or near "("

I've also tried this approach:

DO $$
DECLARE
    min_seq_value int8 := (select max(id) + 1 from some_table);
BEGIN
    create sequence some_seq start with min_seq_value;
END; $$;

But it gives me:

ERROR: syntax error at or near "min_seq_value"

It seems to be pretty basic but I can't get it working. Any ideas?

Thank you!


texts: Why are virtual log files not always allocated in order?

texts:
Quarterly I check the VLF on all of my servers through CMS with a query from the tigertoolbox/Fixing-VLFs/ on github.  This includes suggestions (and code) for correcting what is found.  I always try to get a full understanding of what is going before making any adjustments.  90% of the time the VLF solution I apply is not the same as the recommendation, though it is usually close.

Using DBCC LOGINFO I find that several of the VLFs are not used in sequential order.  I am trying to understand why.   This highly voted answer to; DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK
 says it can happen, but not why.

  Because virtual log files are not always allocated in order,

This would seem to conflict with Jonathan Kehayias; An XEvent a Day (23 of 31) – How it Works – Multiple Transaction Log Files

We can see that of the 130 VLF only two (line 51 & 52) are currently used.  The largest FSeqNo is 41808, subtract 130 = 41678.  I don't see any FSeqNo lower then 41678 so presumably all have been used in the last 130 VLF rollovers.

IF we look at lines 108 - 109, we see line 110 is written to first, then line 109, then line 108. Also the parity is off (not sure what this adds to the scenario) The LSN show they are created in the opposite order.

I would expect that once whatever caused the disruption in sequence order has passed, the next time through the VLFs would be written to in order created.  Why are they not?

Notice line 86 is in both images, to show consistency.

The example is from a SQL 2014 server, the database is currently in FUll recovery and has been for a year or more.  The server has is part of 3 node AG, the results above are taken from the secondary server where the t-logs are backed up hourly.  The last 10 grows occurred through auto grow of 1000MB (2014+ creates 1VLF).  The log file currently has a Initial size of 115,000MB and autogrow of 1,000MB.

Edit
Max Vernon Thank you for the link in your answer.  I understood how they got written out of order, but I am making an assumption that they continued to written out of order once the blocking event was cleared. By following FSegNo backwards you can recreate how it occured. Line 52 is the current VFL with FSeqNo 41808.. At line 35 the FSeqNo is 41791. The FSeqNo 41790 is at line 130 (last line). Presumably VFL 130 was created when FSeqNo 41790 was being written.  BUT there have been 40,000 events of writing to the next VLF, with 130 VLF that would be more than 300 cycles through the log.

A couple of hours later the list looks this


texts: SQL Server 2016 SP2 CU2 Availability Group Online but Cluster Offline

texts:
Am I missing something basic here? My AVG is online and working, but the Cluster on which it resides is saying offline. I didn't think this is possible, so I either have a basic misunderstanding of how this should work, or I have something odd going on. I have

  3 Node Multisubnet AOAG. 2 SQL Servers local data center sync, 1 SQL server DR async.

Get-ClusterResource results:

Cluster IP Address:                Failed
Cluster IP Address XX.XX.XXX.XX    Failed  (2nd subnet node)
Cluster name:                      Offline
File Share Witness:                Online
Availability Group Name:           Online
Availability local IP:             Online
Availability 2nd subnet IP:        Offline
Availability Group name:           Online

The databases are all synced and online. Availability group works fine. I get a lot of strange crashes that may or may not be related (CheckDB causes SQL to crash often despite this being a static test cluster with 8CPU and 128GB mem). Doesn't seem related at all. More baffled by the cluster being offline but AVG online. I have this same setup in prod (only diff is storage), and as expected, on that one Cluster name, and local IP are online


texts:
In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes?

If yes - how?


texts:
I saw a couple of questions already submitted 1 or 2 year ago, without a clear answer. I'd like to know if I can connect my private Network, or whatelse to an Azure Sql Server.

Point-to-Site VPN

I'd like to attach a VNET to my Azure Sql Server, Set up a Network Security Group and VPN GATEWAY and connect (with enterprise certificate or self signed) to my DB's directly from Visual Studio or Microsoft Sql Management studio.


texts: How can I get Elastic IP/host from KIbana interface

texts:
Through Kibana interface, how can I get Elastic IP/host?
I mean, the Elastic host which kibana is connected to.
Is that even possible? i'm struggling on that for hours and I got nothing :(


texts: What is the optimal way to select the parent and child rows on a table with a self-relationship?

texts:
Sample data from the genres table:

id   | genre_name     | genre    | parent_id
---------------------------------------------
2    | Blues          | MAIN     | NULL
6    | Folk           | MAIN     | NULL
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10

Why doesn't the statements #1 and #2 (with the reflexive join) NOT return the parent?

-- Statement #1 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id OR (pd.id= NULL AND pg.id = 10)
    WHERE pg.id = 10;
-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10

-- Statement #2 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id
    WHERE pg.id = 10 AND pg.parent_genre_id IS NULL;

-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10

-- Statement #3 returns both parent and child rows.
(SELECT
    id,
    genre_name,
    genre_type,
    parent_genre_id
    FROM `genres`
    WHERE id = 10)
UNION -- Get all sub-genres (children)
(SELECT
    cg.id,
    cg.genre_name,
    cg.genre_type,
    cg.parent_genre_id
    FROM `genres` AS pg
     JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id
    WHERE pg.id = 10);

-- OUTPUT:
id   | genre_name     | genre    | parent_id
---------------------------------------------
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10

The #3 statement seems like a bad use of UNION to me.

Warning, bad pun intended:

Now the term self-relationship sounds like it implies a certain thing people do with their hands below their belt; but in any case I would like to know why the first two selects do not cum up with the intended results. Rather disappointing after the query reached climax. Uh, I mean returns the results.

After the answer given below, I ended up using the UNION (no JOIN of course):

(SELECT
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
    FROM `genres`
    WHERE `id` = 10)
UNION -- Get all sub-genres (children)
    (SELECT
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
        FROM .`genres`
        WHERE `parent_genre_id` = 10);


texts: Relational or non-relational database for a system to pass objects on?

texts:
hopefully this is the right place to ask this kind of question.

I am working on a system that focuses on helping people give away things that they don't use anymore.

So, an object typically has the following attributes:

Object

registered by (user. doesn't change)
type (there could be from 3 to 5 types)
situation (changes every once in a while. mainly about the condition of the object)
localisation (current place where it is. changes frequently)
pictures (one or more)
list of users that possessed the object with the time interval in which the user had the object
list of comments about the object (contains user, date/timestamp, and text)

After thinking about all those requirements, I need to decide whether a relational or non-relational database is more appropriate for this case. My main concern is regarding the fields that need to be updated occasionally. As far as I'm concern, this would be one of the downsides if I decided to use NoSQL.

Also, I was hoping that I could use NodeJS for this project, although I've never seen anyone use Node with relational databases (I know it is possible, but is it recommended?)

If I end up using a relational database, is it correct to say that I would have at least 4 tables?

Object (FK for the user that registered the object)
User
Object_Users (maps Objects to Users)
Comment (FK for object and user)?

How could I do this using MongoDB, for instance?

After some thoughts, I came up with this, but I don't know if it is correct:

{
"object": {
    "type": "dvd",
    "situation": "Well preserved",
    "localization": "Birds Avenue",
    "user_name": "Larissa Leite",
    "user_img": "url",
    "events": [
        {
            "user_name": "Larissa Leite",
            "user_img": "url",
            "text": "Dvd registered",
            "date": "20/04/2014"
        }
    ],
    "comments": [
        {
            "user_name": "John Doe",
            "user_img": "url",
            "text": "Very good dvd",
            "date": "22/04/2014"
        }
    ]
  }
}

If anything is unclear, let me know!
Thanks!