texts: Connection to an oracle db from another computer

texts:
I have installed Oracle 11g to an ubuntu. I can connect to DB using sqlplus from the localhost. But I cannot connect from another computer. I have configured my tnsnames.ora as followed:

ELIF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.58)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = ELIF)
)
)

When I make a tnsping I got the following error:

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ELIF)))
TNS-12541: TNS:no listener

Edit:
lsnrctl status and netstat -an | grep 1521 result:

oracle@cms:~$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-JAN-2012 13:26:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                25-JAN-2012 02:33:02
Uptime                    0 days 10 hr. 53 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/cms/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "ELIF" has 1 instance(s).
  Instance "ELIF", status READY, has 1 handler(s) for this service...
Service "ELIFXDB" has 1 instance(s).
  Instance "ELIF", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@cms:~$ netstat -an | grep 1521
tcp        0      0 127.0.0.1:1521          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:32656         127.0.0.1:1521          ESTABLISHED
tcp        0      0 127.0.0.1:1521          127.0.0.1:32656         ESTABLISHED
unix  2      [ ACC ]     STREAM     LISTENING     115265   /var/tmp/.oracle/sEXTPROC1521


texts: How to swap primary key between records in postgres

texts:
I can't seem to find a way to achieve this transactionally (or not)

What I need to achieve is non-standard, hence my difficulty finding a solution.

I need to code a data migration tool to "swap" old records with new records in a table, but I have the following requirements / constraints:

Don't lose the old records
Make all references to old records point to new records (that's not just db foreign keys, it's references out of my control in external services, caches, emails, historic data, bookmarks, you name it)
The migration code needs to be schema agnostic, i.e. shall not need to be updated if a new column is added to the table and independent of which other tables reference it.
I can't lock the table for more than the acceptable amount of time it takes to update two records.

So my ideal solution is, well, brute swap their primary keys...

Question is how can I swap the primary keys between two records in postgresql. I am having difficulty finding an approach that does not fail with duplicate key exception, i.e. an approach that runs the "validation" for the update transactionally.

I have tried

UPDATE table
SET id = (CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE id END)

UPDATE table
SET id = CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE id IN (1, 2);

Both failing on duplicate key constraint

I am using PostgreSQL 11.6

SOLVED

Solution for bulk updates for numeric primary keys, thanks to input from the nice people below:

-- disable foreign key constraint validation
BEGIN;
SET session_replication_role='replica';

-- update the pairs of ids to their negative counterparts
WITH query AS ('query to get pairs of ids to swap')
UPDATE table
SET id = -id
WHERE id in (query.id1, query.id2);

-- update the pairs of negated ids to their positive counterparts swapped
WITH query AS ('query to get pairs of ids to swap')
UPDATE table
SET id = CASE id WHEN query.id1 THEN -query.id2
                 WHEN query.id2 THEN -query.id1
                 END
WHERE id in (query.id1, query.id2);

-- enable foreign key constraint validation
SET session_replication_role='original';
COMMIT;


texts: How to disable annoying MySql update console

texts: I have installed mysql 5.7, and from time to time (once per several days) it looks for updates (as I understand). Console window opens for several seconds, and then dissappear. It doesn't care, that I'm watching a movie in this time, and this console is on the top of my player. It's really annoying, why MySql is updating in this user unfriendly way? Can I configure it to do it silently?


texts: Why does indexing take forever on an Aria table?

texts:
I have a table using the Aria engine on MariaDB 10.3, it stands at 123.5 GiB in size on an SSD server, with plenty of space available (the disk could fit another copy of the unindexed database + more). The structure of this table is just two columns: column 1 is VARCHAR(32) and column 2 is VARCHAR(128).

I am trying to create an index index on column 1. It fulfills stage 1 (copy to tmp table) just fine, and then gets to stage 2, which is repair by sort where it gets to around 70% before stalling and making no further progress. SHOW PROCESSLIST reveals that it has moved onto repair by keycache, which I know is notoriously slow - especially with the large amount of data I am dealing with.

I tried setting myisam_max_sort_file_size = 140G in my my.cnf (which should be plentiful), then restarting the mariadb service and then trying to reindex. It stalls once again at ~70%.

What can I do to speed up this indexing process?

Edit #1 with requested info:

CREATE TABLE `tablename` (
  `colOne` varchar(32) NOT NULL,
  `colTwo` varchar(128) NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 PAGE_CHECKSUM=1

and

key_buffer_size     = 128M

and

CREATE INDEX `indexOnColOne` ON `tableName` (`colOne`);


texts: How can I change the password for pgadmin in Windows?

texts:
I want to know how I can change the password of pgAdmin, the same one that is created when it is installed in Windows
The point is that I have not forgotten, but for personal reasons I want to change the password.
I repeat, I know the password so what I'm looking for is just trying to change it
I am using version 12.4 on Windows 10


texts: Breaking up long running query into batches runs fine for a while and then produces fatal error

texts:
I'm using a variation of Take Care When Scripting Batches by Michael J. Swart, which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results until it hits about the 30MM record mark (30 minutes in) when I get an error:

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.
  The results, if any, should be discarded.

Part of the issue is that each week I get a new feed of records and these records are added to the repository of existing records from previous feeds.  A new record can be received that dictates that an older record be deleted.  The older record can be anywhere in the table all the way back to the oldest feed.

So when this script loops through the table, it might only be looking at 100,000 records per batch on one end of the equation (the "latest" alias), but it still has to search the entire table for the existence of the older record (the "previous" alias) which would need to be deleted, so I'm thinking the performance gains that I might see in a normal delete situation using this approach are eliminated in this scenario.  Should I abandon this approach?

Before I tried this approach I was just using an update statement without the batch, and I was getting a transaction log error.  It would run for ~4 hours and then fail.

DECLARE
@latestFeed INT = 90,
@LargestKeyProcessed INT = -1,
@NextBatchMax INT = 1,
@RC INT = (SELECT MAX(record_id) FROM table_B);

WHILE (@NextBatchMax < @RC)
BEGIN
SELECT TOP (100000) @NextBatchMax = record_id
FROM table_B
WHERE record_id > @LargestKeyProcessed
ORDER BY record_id ASC;

delete previous
output Deleted.*
into table_A
from table_B as previous
inner join table_B as latest on previous.field_1 = latest.field_1
    and previous.field_2 = latest.field_2
    and previous.field_3 = latest.field_3
where previous.feed_id < latest.feed_id
    and previous.condition_1 in ('1', '2')
    and latest.condition_1 = '3'
    and latest.feed_id = 90
    AND latest.record_id > @LargestKeyProcessed
    AND latest.record_id <= @NextBatchMax;

delete previous
output Deleted.*
into table_A
from table_B as previous
inner join table_B as latest on previous.field_1 = latest.field_1
    and previous.field_2 = latest.field_2
    and previous.field_3 = latest.field_3
where previous.feed_id < latest.feed_id
    and previous.condition_1 in ('1', '2')
    and latest.condition_1 = '2'
    and latest.feed_id = 90
    AND latest.record_id > @LargestKeyProcessed
    AND latest.record_id <= @NextBatchMax;
SET @LargestKeyProcessed = @NextBatchMax;
END

Since these aren't my production tables, just mirrors of them, I truncated them and re-inserted all the records again.  I then removed the second DELETE statement shown in my code, so that it was at least more simple.  This time it ran completely without the severe error.

NEXT ATTEMPT

I'm adding a transaction to the while loop so that with each iteration it commits the transaction.  I'm also reducing the batch size to 50,000 to see if that will help at all with the speed of the processing.

It also occurred to me that there's no reason to include the entire table in this.  By that I mean, I know that the only feed that will contain records triggering the delete of a previous record are in the latest feed.  So I'm setting the starting value of @LargestKeyProcessed like so:

@LargestKeyProcessed INT = (SELECT MIN(record_id) - 1
FROM Table_B WHERE [feed_id] = 90),

While the script will still have to look through the rest of the table for the actual records to delete, there's no reason it needs to look through the entire table to find the records triggering a delete.

I ran DBCC CHECKDB. It reported:

  CHECKDB found 0 allocation errors and 0 consistency errors in database


texts:
This will, hopefully, be a nice and quick question for someone out there. It continues from my previous question.
I have a .BAK file that I'm trying to restore. The .BAK was created with the NOINIT argument, to try and save previous version, and so has newer data appended (as I understand it). I was wondering if this meant that - upon restore - restore would try to use an old version of the backup?
I.e. I have backed up the DB, added new tables to it, performed another full back-up and dropped the added tables. When I try to restore, I am not getting the 'new' tables back, is this because the restore is finding a version of the database in the .BAK which was created before the 'new tables'? If so, how can I specify to use the latest data?
EDIT: Apologies for not stating sooner, this is using an SQL Server database


texts: Oracle database connection instant timeout

texts:
I have to check automatically if a connection to another database in oracle is vaild. I want a trigger to perform different actions based on whether the connection is vaild while it's running or not. It takes about 40 seconds every time to return ORA-12170 (Connection timeout) if i try to check the connection by using a query including the database link to the remote database. Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time? I just want to get a quick response if the connection is working. The trigger is called several times every view seconds, so i can't wait for 40 seconds every time it's called when the connection is interrupted.
Im using Oracle XE 11g.


texts: Add a column at a specific position in system versioned temporal table

texts:
I need to add a column to a temporal table. The Microsoft documentation only suggests appending the new column using the following code:
ALTER TABLE dbo.Department
    ADD TempColumn INT;

However, this would cause the column to appear below the audit columns, including the mandatory system versioning columns 'ValidFrom' and 'ValidTo'. Whilst this is not an issue in terms of performance, all of our non-system versioned tables keep the audit columns as the last columns.
I have tried setting system versioning off but can't work out how to make the changes as I would with a normal user table whilst preserving the data in the history table.
SSMS allows you to create the code for modifying tables using the 'Design' option but this is also not available for temporal tables. I would usually use code that is similar to the 'Design' option output for adding a column where a new table is created with the new column and a suffix in the table name, the old table is dropped and the new table is renamed.
How can I do this for temporal tables please?


texts: What operating systems are certified for Oracle 11gr2 RAC?


texts: Use both b-tree and BRIN indexes in PostgreSQL

texts:
I have rather large tables (> 1000 M rows) where I have to do quick lookups. They are typically indexed with composite indexes to enable index-only scans like this CREATE INDEX table (study, analysis, gene, sample) INCLUDE (value) where a study can contain several analyses, which contain many genes, each of which contains measurements for some samples. One type of query would be to get the value for a given gene in all samples in an analysis. This would return ~500 rows. Another type of query would return values for all genes and samples in an analysis. This would return millions of rows. I wonder if it would make sense to also include a BRIN index on study, analysis to speed up such queries. It would then be redundant with the existing b-tree index. Is this bad practice that just adds overhead and complexity, or do you think it will be used by the scheduler like I want?
EDIT: I forgot to mention that the results will typically not be cached, so it would have to read from disk.


texts: Named and Default Instances on single SQL Cluster

texts:
current environment we have Server A and Server B - WFCS with Default instance serving applications(SQL 2012) Now we want move a named instance (SQL 2008) to the above cluster.

What will be the consequences if I create a Named instance on the above existing setup. will the idea work ?

If yes,

do we need separate IP in the same VLAN for named instance and add it to cluster resources ? Also will fail-over happen as before ?

Suggestions pls


texts:
I have the following requirements in SQL server.
I want to get updated/inserted values, edit it save to one column using SQL trigger.

Say I have column named customer_name, when someone updates/inserts value to that column, I want to get that data(the data which is going to be updated/inserted), edit it and save it in different/same column.

I want to rename the values with just to maintain uniqueness in the data. I will append _number if the duplicate data is inserted/updated.

I have column 'name', whenever new duplicate names updated/inserted to the column I want rename it with name_count so that I can maintain uniqueness

I cant go for other solutions since it will take a lot of time. For a time being I have planned to do this.

How can I do it using SQL trigger?


texts: Real-world scenarios for table with DENY SELECT and GRANT [INSERT/UPDATE/DELETE]

texts:
What is real-world scenarios for this type of permissions?

May be it is generally not valid, and made by mistake?

Should this kind of permissions be avoided (or prohibited)?


texts: column must appear in the GROUP BY clause or be used in an aggregate function

texts:
I have a simple table with columns col1, col2, col3. All not nullable.

I want to delete all rows where the tuple (col1, col2) has several entries. Background: a unique constraint for (col1, col2) should be added.

drop table mytable;

create table mytable (
    col1 integer not null,
    col2 integer not null,
    col3 integer not null);

-- rows to delete
insert into mytable values (1, 1, 1);
insert into mytable values (1, 1, 2);

-- rows to keep
insert into mytable values (2, 2, 1);
insert into mytable values (2, 3, 2);

delete from mytable where
(col1, col2) in  (
    select col1, col2 from mytable
    group by (col1, col2) having  count(distinct col3) >1) ;

select * from mytable;

Above works on PostgreSQL 10 but fails on older versions.

Older versions tell me this error message:

  ERROR:  column "mytable.col1" must appear in the GROUP BY clause or be used in an aggregate function

How to get this working on PG 9.3?


texts:
My mongod service crashes within 2-5 seconds of starting. I don't understand why.

Here are the systemctl logs:

  systemctl status mongod.service

mongod.service - High-performance, schema-free document-oriented database
Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Thu 2016-05-05 11:43:57 UTC; 4min 40s ago
Process: 7597 ExecStart=/usr/bin/mongod $OPTIONS run (code=exited, status=0/SUCCESS)
Main PID: 7601 (code=exited, status=14)

May 05 11:43:52 database13.choozon.net systemd[1]: Starting High-performance, schema-free document-oriented database...
May 05 11:43:52 database13.choozon.net mongod[7597]: warning: bind_ip of 0.0.0.0 is unnecessary; listens on all ips by default
May 05 11:43:52 database13.choozon.net mongod[7597]: about to fork child process, waiting until server is ready for connections.
May 05 11:43:52 database13.choozon.net mongod[7597]: forked process: 7601
May 05 11:43:54 database13.choozon.net systemd[1]: Started High-performance, schema-free document-oriented database.
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Main process exited, code=exited, status=14/n/a
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Unit entered failed state.
May 05 11:43:57 database13.choozon.net systemd[1]: mongod.service: Failed with result 'exit-code'.

So far I have:

checked permissions (everything looks good)
checked the service
tried deleting mongo.lock

mongod appears to start without any errors, but then fails after a couple of seconds.

Any ideas ?


texts: SSAS error - invalid attempt to read when reader is closed

texts:
I'm trying to run my SSAS project but I got an error.
I'm using a MySQL database as my data source.
I can connect and build the view, but when I create a dimension and I try to run it get this error:

  Errors in the high-level relational engine. Invalid attempt to Read
  when reader is closed.. The exception was raised by the IDataReader
  interface.

In the connection to MySQL I set SQL SERVER MODE to True, otherwise I get a syntax error. I'm running on localhost.


texts: How to SELECT most common (viral) from a table and LIMIT results for pagination?

texts:
Sorry if it's a dubplicate but I can't find an answer to the question.

Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is also a timestamp column and can be liked by other users where a like is saved in likes table with the user id and secret id.

articles table:

+----+---------+--------------------+
| id | article |       timestamp    |
+----+---------+--------------------+
| 1  | A       |2019-02-10 22:19:02 |
| 2  | B       |2019-02-9 22:20:28  |
| 3  | C       |2019-02-9 22:21:10  |
| 4  | D       |2019-02-8 18:20:10  |
| 5  | E       |2019-02-8 13:25:04  |
+----+---------+--------------------+

likes table:

+-----------+--------+
| articleid | userid |
+-----------+--------+
| 1         | 1      |
| 1         | 3      |
| 1         | 5      |
| 1         | 3      |
| 2         | 1      |
| 3         | 2      |
| 3         | 3      |
| 3         | 4      |
| 3         | 5      |
| 3         | 2      |
| 4         | 4      |
+-----------+--------+

I'm trying to do the pagination of the most liked articles in the shortest time, as for example in this case article A is the top of the order because it got more likes in less time,   How would I do that and at the same time LIMIT the result to do the pagination?
My try was:

SELECT * FROM articles, (SELECT count(*) FROM likes WHERE articleid = id) as likes
GROUP BY articles
OREDER BY likes
LIMIT 10

I would do it in php without LIMIT but at the same time I want to repeat the query to get the next articles for the second page in the same way and I have no idea how to do that.

Thanks in advance!


texts: Why can you specify CASCADE after DROP but not after DELETE?

texts:
What is the reasoning behind the design that you can specify CASCADE after a DROP query (DML) like DROP users CASCADE; but not in a DELETE query (DDL) like DELETE FROM users WHERE user_id = 2 CASCADE;?

Example:

CREATE TABLE users (
 user_id integer PRIMARY KEY
);
CREATE TABLE comments (
  comment_id integer PRIMARY KEY,
  user_id integer REFERENCES users
);
INSERT INTO users (user_id) VALUES (1), (2);
INSERT INTO comments (comment_id, user_id) VALUES (11, 1), (21, 2), (22, 2);

DROP TABLE users;
-- ERROR: cannot drop table users because other objects depend on it
-- DETAIL: constraint comments_user_id_fkey on table comments depends on table users
-- HINT: Use DROP ... CASCADE to drop the dependent objects too.

DROP TABLE users CASCADE;
-- Query executed OK, 0 rows affected.

DELETE FROM users WHERE user_id = 2;
-- ERROR: update or delete on table "users" violates foreign key constraint "comments_user_id_fkey" on table "comments"
-- DETAIL: Key (user_id)=(2) is still referenced from table "comments".

DELETE FROM users WHERE user_id = 2 CASCADE;
-- ERROR: syntax error at or near "CASCADE"
-- LINE 1: DELETE FROM users WHERE user_id = 2 CASCADE

Instead, you have to define the CASCADE in the DML to be able to use it in DDL:

CREATE TABLE comments (
  comment_id integer PRIMARY KEY,
  user_id integer REFERENCES users ON DELETE CASCADE
);

DELETE FROM users WHERE user_id = 2;
-- Query executed OK, 1 row affected.


texts: Which server actually runs the SSIS package when SQL Agent and SSISDB are on 2 different servers

texts:
We have a SQL Server where we deployed the SSIS packages to its Integration Service Catalog (SSIS DB).
And we have another SQL Server where we scheduled SQL Agent to run those SSIS packages.
My question, where does the SSIS package execution actually happens? The SQL Agent server, or the Integration Service Catalog?
A little background of why we are doing/asking this: we have an old SQL Server where we deployed a lot of legacy SSIS packages. Now we have a new SQL Server where we plan to move to, but we don't want to move those legacy SSIS packages. So we are trying to just move some SQL Agent jobs to the new server to call those legacy SSIS. With some research, we thought the execution happens on the SQL Agent server, but the log says it's on the SSIS DB server. However, the execution failed and complain missing ZappySys component, which looks like the execution happened on the SQL Agent server, because we indeed don't have ZappySys component on the SQL Agent server. We are so confused now......


texts: Restrict executeQuery in Java from data manipulation in an Oracle database

texts:
I am developing a query browser in Java where I want to restrict users from manipulating data. So I am using executeQuery as I searched many questions and all are answered as executeQuery is used to select and does not allow data manipulation as this on link.

It's working just fine with MySQL but when its comes to Oracle I am getting the error

  ORA 00900: invalid SQL statement

but the real problem is, in database the statement is updating the record. I am getting the same error for update, delete, insert and drop but all commands are manipulating the data in database.

Now I have the option to check whether my query string start with data manipulation keywords but I am trying to avoid that checking. It's working absolutely fine in MySSQL, so I amnot getting what is the issue with Oracle.

Below is the code sample:

Connection conn = null;
Statement stmt = null;
ResultSet query_set = null;
try {
    String query = "insert into users values(1,'name')";
    Class.forName ("oracleDriver"); //Oracle driver as our database is Oracle.
    conn = DriverManager.getConnection("oracleDbUrl", "dbUsername", "dbchecksum"); //login credentials to the database
    stmt = conn.createStatement();
    query_set = stmt.executeQuery(query);
} catch(Exception e) {
    e.printStackTrace();
}

Kindly suggest how I can restrict users from data manipulation without applying check on the query string and what is the reason behind this Oracle behavior.


texts: Why $group is very slow in aggregation and how can faster it in MongoDB

texts:
My query is taking ~3 seconds to execute with the $group stage, How can I faster this?

My query is:
db.getCollection('employee_data').aggregate([
  { $match: //Filter
  },
  {
   $group:
   {
     _id: {
       employee_id:"$employee_id",
       activity_date:"$activity_date"
     },
     trips: { $sum: 1 }
   }
  }
])
Data size : 4M records


texts: Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

texts:
I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view.

Here's an example:

CREATE VIEW CoolHashedView WITH SCHEMABINDING AS

    SELECT
        KeyId,
        CONVERT
        (
            VARCHAR(34),
            HASHBYTES('MD5', TextColumn1 + '||' + TextColumn2 + '||' + CAST(DateColumn1 AS VARCHAR(50)),
            2
        ) AS HashedData
    FROM dbo.BoringTable;

    CREATE UNIQUE CLUSTERED INDEX IX_CoolHashedView_KeyId_HashedData ON CoolHashedView (KeyId, HashedData);

When I try to create the above index, it fails and the following error is thrown:

  Msg 2729, Level 16, State 1, Line 26 Column 'HashedData' in view
  'CoolHashedView' cannot be used in an index or
  statistics or as a partition key because it is non-deterministic.

When I remove the date field from the HASHBYTEs function, the clustered index then successfully creates.

My guess is it has something to do with the different ways to format a date or different timezones?...am I getting warmer?


texts: Using floats for PKs. Is it a good idea?

texts:
Let's say I have an entity PizzaStore which has a location as part of its attributes
Now a PizzaStore can have N of other PizzaStore that are close by.
So seems to be a self referencing relationship 1-N  (optional)
If we know before-hand which exact PizzaStore are close by each other what would be the best way to represent that?
Since we have a self-referencing 1-N relationship I think another table would be needed <pizza_store_id, other_pizza_store_close_by_id>
In this case we would store e.g.
<1, 22>,
 <1, 23>,
 <1, 78>,
 <2, 102> etc  to show that pizza store with id 1 has 22, 23 and 78 near by etc

Now in order to get these rows back in-order I would need to create a PK and query based on that.
I was wondering would an auto-increment guarantee the insertion order?
Or would I need to use a float representing the distance e.g.  <2.04, 1, 23> (where 2.04 is the distance in miles)
I was also thinking is there a better way than this?
We know that if 1 is close to 22 then 22 is also close to 1 right?
Is there a more efficient way to represent this information?
I think it would suffice to store just the row <1,22> to capture that 1 is close to 22 and that 22 is close to 1. But this way we are losing the order

Update:
All the answers are very useful. One thing though that perhaps is not clear.
I already have the order of the entities based on the distance on insert time.
I.e. I have a set of ids of objects already sorted based on distance and I want to insert this set in the database in a way that I can retrieve the rows back in the order I have inserted them. I want to avoid sorting on distance on retrieve since I already had the order at insert time. These ids would be used to join to another table (they are ids) and I want to retrieve the rows in the insert order


texts: Restoring backup from mysqldump does not remove additions

texts:
I am restoring a backup from a mysqldump and noticing that if I made additions (in the form of new tables) to the database they persist after the restore.  I can go back in and remove those tables to get back to the original state, but as I need to test migrations, I'd like to bring the DB back to its state during the dump without manually going in and removing tables.

I suppose I could do a DROP Database XXX before the restore, but will that hamper my current restoration methods?


texts: How to find the sql jobs that have not run for more than two months?

texts: I would like to get a query to ascertain which sql jobs have not run for more than 60 days?


texts: How to fix indexes to reduce the fragmentation percentage

texts:
I need your guidance and help to understand a SQL topic that I've never considered before but now I'm trying to build tables thinking on performance, one of the topics is Fragmentation but after reading some website data I need more knowledge.
The code below will create a new table , that table will have a clustered index as the primary key  but also it will have a non clustered index for a column called HosttTimeStamp which is basically a DATETIME that storage the transaction date . The problem is that both indexes are using 98% of fragmentation and I'm looking a way to reduce that when new data gets inserted
USE [DB1]

IF OBJECT_ID('dbo.ProductionTable', 'U') IS NULL BEGIN
CREATE TABLE dbo.ProductionTable
(
    [Tier1CustomerAccountID] INT NOT NULL,
    [Tier1CustomerBusinessName] VARCHAR(255) NULL,
    [Businessname]NVARCHAR(50) NULL,
    [AccountID] INT NOT NULL,
    [RetailerAcc]VARCHAR(10) NULL,
    [Product] VARCHAR(10) NULL,
    [Title] NVARCHAR(50) NOT NULL,
    [PlanName] VARCHAR(200) NULL,
    [PlanValue] NUMERIC(18,3) NOT NULL,
    [Hosttimestamp] DATETIME NOT NULL,
    [TerminalID] INT NOT NULL,
    [TxnIDType] CHAR(3) NOT NULL,
    [Region] VARCHAR(255) NULL,
    [State] VARCHAR(255) NULL,
    [RetailerAccTxnID] BIGINT NOT NULL,
    [TxnType] CHAR(3),
    [FirstTxnDate] DATETIME NULL,
    [Classification] VARCHAR(50) NULL
PRIMARY KEY CLUSTERED
(
    [RetailerAccTxnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

DECLARE @tableName varchar(max) = 'dbo.ProductionTable';
DECLARE @indexName varchar(max) = 'IX_ATTActivationsData_Hosttimestamp';
DECLARE @indexDefn varchar(max) = '([Hosttimestamp] ASC)';
IF NOT EXISTS(
    SELECT * FROM sys.indexes
    WHERE Name=@indexName AND OBJECT_ID = OBJECT_ID(@tableName) ) BEGIN

    EXEC('CREATE NONCLUSTERED INDEX ' + @indexName + ' ON ' + @tableName + @indexDefn)

END;

I'm new on the index topic so I was thinking that maybe my non cluster index is wrong, that instead of being " on dbo.ProductionTable (HostTimeStamp) " must be something like  "on dbo.productionTable (RetailerAccTxnID) INCLUDE (HostTimeStamp). The reason is because if I'm not wrong the word "ON" is 100% more for table joins and there will be multiple queries pointing to this table using the HosttimeStamp for the "WHERE" conditions


texts: Is the ADO.NET Entity Framework known for generating poorly performing queries?

texts:
This question is inspired by the comment posted to the latest ServerFault blog post:

  Are you guys still using LINQ to SQL?

I know I can use SQL Server Profiler and/or the ToTraceString method to see the generated queries and analyze them myself. However, I am looking for opinions from people with hands-on experience with administering databases accessed by applications utilizing the Entity Framework.

Are Entity Framework queries a common cause of performance problems?
Can LINQ queries be optimized in such cases or is raw Transact-SQL the only solution?


texts: How to deal with seldom used big database and postgresql?

texts:
I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries.

My problem is :

  This database is queried a few times a day, but because postgres loads
  the data in its buffer on demand, my first query on a particular gps
  point is always slow, and I do only one query per GPS point, so it's
  always slow (like 60sec slow against 100ms when the data is buffered)

.

What could I do here ?

I'm using a A6 Azure instance (4 Cores, 28 GB memory, HDD).
Server is Ubuntu 14.04.4 LTS
Swapping is enabled
There is index on the geometry columns queried.
The shared buffer is 10GB
work mem is 256MB
maintenance work mem is 4GB


texts: Does Postgresql logical replication support hot standby?

texts:
Postgresql has two ways of replication

Physical replication
Logical replication

with physical replication everything is send over and the servers must have the same major release. Which makes upgrading a bit cumbersome.

With logical replication, Postgresql servers can be different versions, which makes the process of upgrading much easier.

I am leaning more towards using logical replication. I want to use Postgresql in high availability. Does logical replication support hot standby?


texts: "History-based" database design approach?

texts:
A few years ago I've stumbled upon a database design approach, but I can't find any information on this for the love of my life.

Its main feature was normalizing tables in a way where every "property" of entities was stored in their own table. E.g. a user entity, normally stored in a single table with an id, email and other fields, was split into tables for each field. So users' email address would have been stored in a table with a field to contain a user's current email address and a datetime (when this email address was set or last updated) with a user id or similar. The model would track every change to a user's email address by creating new records in this table with updated timestamps. (Not sure if this made sense.)

This approach was documented online and it had an online tool to visualize such a model (as a graph) and it had a way to export the visually created model as an SQL schema (with full support for MSSQL).

Anyone have an idea about the name of this design approach?

Edit.: I remember that the author of this approach (or the visual modeller that is/was available online) mentioned that the tool was able to produce database schema (or "optimized JOINs") for MSSQL since it has (had) the ability to efficiently to "prune tables".

Edit.: By "prune tables" I meant "eliminate tables".


texts: Why mongod.service is killed by my system?

texts:
I am using MongoDB 4.4 version on ubuntu 20.4, using 2 mongo instances on the same server. I encountered this problem:
Sep 03 10:45:25 mlnode systemd[1]: Stopping MongoDB Database Server...
Sep 03 10:45:25 mlnode systemd[1]: mongod.service: Succeeded.
Sep 03 10:45:25 mlnode systemd[1]: Stopped MongoDB Database Server.
Sep 03 10:45:25 mlnode systemd[1]: Started MongoDB Database Server.
Sep 03 10:55:46 mlnode systemd[1]: Stopping MongoDB Database Server...
Sep 03 10:55:46 mlnode systemd[1]: mongod.service: Succeeded.
Sep 03 10:55:46 mlnode systemd[1]: Stopped MongoDB Database Server.
Sep 03 10:55:46 mlnode systemd[1]: Started MongoDB Database Server.
Sep 03 11:05:05 mlnode systemd[1]: Stopping MongoDB Database Server...
Sep 03 11:05:05 mlnode systemd[1]: mongod.service: Succeeded.
Sep 03 11:05:05 mlnode systemd[1]: Stopped MongoDB Database Server.
Sep 03 11:05:05 mlnode systemd[1]: Started MongoDB Database Server.
Sep 03 11:13:20 mlnode systemd[1]: /lib/systemd/system/mongod.service:11: PIDFile= references a path below legacy directory /var/run/, updating /var/run/mongodb/mongod.pid → /run/mongodb/mongod.pid; please update the unit file accordingly.
Sep 03 11:25:47 mlnode systemd[1]: /lib/systemd/system/mongod.service:11: PIDFile= references a path below legacy directory /var/run/, updating /var/run/mongodb/mongod.pid → /run/mongodb/mongod.pid; please update the unit file accordingly.
Sep 03 11:56:47 mlnode systemd[1]: mongod.service: Main process exited, code=killed, status=6/ABRT
Sep 03 11:56:47 mlnode systemd[1]: mongod.service: Failed with result 'signal'

Wondering why the system is killing mongod.service, I don't have any clue. Then I checked my mongo logs:
{"t":{"$date":"2020-09-03T11:56:46.379+05:30"},"s":"F",  "c":"-",        "id":23083,   "ctx":"conn10155","msg":"Invariant failure","attr":{"expr":"ret","error":"UnknownError: -31803: WT_NOTFOUND: item not found","file":"src/mongo/db/storage/wiredtiger/wiredtiger_record_store.cpp","line":1598}}
{"t":{"$date":"2020-09-03T11:56:46.379+05:30"},"s":"F",  "c":"-",        "id":23084,   "ctx":"conn10155","msg":"\n\n***aborting after invariant() failure\n\n"}
{"t":{"$date":"2020-09-03T11:56:46.379+05:30"},"s":"F",  "c":"CONTROL",  "id":4757800, "ctx":"conn10155","msg":"Writing fatal message","attr":{"message":"Got signal: 6 (Aborted).\n"}}


texts: Integrate Full cast and crew in a Movie Database

texts:
I'd like to display full cast and crew from my movie database and was wondering if the structure below is OK. Basically, I want something like this https://www.themoviedb.org/movie/284052-doctor-strange/cast

[movies]
id,
name

[people]
id,
name

[jobs]
id,
name (ex: Director, Writer)

[casts]
person_id,
movie_id,
character_name

[crew]
person_id,
movie_id,
job_id


texts:
I run ubuntu 18.10 and I've run into issues with a remote database server. I connect using a private network (by IP). The query in bash mysqlcommand takes about 9ms, while in the PHP software (eg. PhpMyAdmin) it takes around 1+ second. Having no clue what can be on. I have also added skip-name-resolve.

The private network resolves second IP in about 50 ms so I don't see a point in long queries.

Worth noting that the local MySQL ran perfectly before.


texts:
I am trying to output the data from an MySQL table to a file but getting permission errors:

$ pwd
/home/dotancohen
$ mkdir in
$ chmod 777 in/
$ mysql -ugs -p
mysql> USE someDatabase;
mysql> SELECT * FROM data INTO OUTFILE '/home/dotancohen/in/data.csv';
ERROR 1045 (28000): Access denied for user 'gs'@'localhost' (using password: YES)
mysql>

If the directory in question is chmodded to 777, then why cannot the MySQL user write the file? Interestingly enough, I cannot write to /tmp/ either.

EDIT:
It looks like the DB user has the proper MySQL permissions:

mysql> show grants;
+----------------------------------------------------------------------------------+
| Grants for gs@localhost                                                          |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gs'@'localhost' IDENTIFIED BY PASSWORD 'somePassword'     |
| GRANT ALL PRIVILEGES ON `gs\_%`.* TO 'gs'@'localhost'                            |
+----------------------------------------------------------------------------------+
2 rows in set (0.01 sec)


texts: Aggregating results of two database views in one database view

texts:
I have a view with name of UserInfo like this:

And another view with name of UserRoleInfo like this:

I want to aggregate all the records of the two views for one user name like H.F into one view.

I tried to join the two views on user name but does not work!

How can I have all records of these two views in one view with the same columns as in picture 3?


texts: Can spatial index help a "range - order by - limit" query

texts:
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.

We have the following table with a tree structure (Nested Set model) of words and their frequencies:

lexikon
-------
_id   integer  PRIMARY KEY
word  text
frequency integer
lset  integer  UNIQUE KEY
rset  integer  UNIQUE KEY

And the query:

SELECT word
FROM lexikon
WHERE lset BETWEEN @Low AND @High
ORDER BY frequency DESC
LIMIT @N

I suppose a covering index on (lset, frequency, word) would be useful but I feel it may not perform well if there are too many lset values in the (@High, @Low) range.

A simple index on (frequency DESC) may also be sufficient sometimes, when a search using that index yields early the @N rows that match the range condition.

But it seems that performance depends a lot on the parameter values.

Is there a way to make it perform fast, regardless of whether the range (@Low, @High) is wide or narrow and regardless of whether the top frequency words are luckily in the (narrow) selected range?

Would an R-tree/spatial index help?

Adding indexes, rewriting the query, re-designing the table, there is no limitation.


texts: How to use merge hints to isolate complex queries in SQL Server

texts:
I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal.  However when I know the number of rows for one query is very small I think I should be able to use hints to control the joins.

select cj.a, cv.b
  from (select distinct a from complexJoin) cj -- 2 rows
 inner loop join complexView cv
    on cj.a = cv.a
 order by cj.a, cv.b

If cj is <1s and cv <1s expect this to be <~2s but using any hint (merge/hash/loop) it is often > 1min.

I also tried to use CROSS APPLY because the docs claimed that the inner select is executed exactly once for each outer row. The query takes ~100x longer than running the inner query twice manually so perhaps I don't understand the docs.

select cj.a, cv.b
  from (select distinct a from complexJoin) cj -- 2 rows
 cross apply (select * from complexView
               where a = cj.a) cv
 order by cj.a, cv.b

If I populate a temp table with results from "cj" and then join _with_no_hint_ or use the cross apply it is fast but do I really have to resort to that?  If I use the temp table and attempt "any" of the join hints (loop/merge/hash) it is slow so maybe that is a key point.

I don't believe diving into the depths of the query plan (both are complex to begin with) are required to solve this type of problem in general: I just want guaranteed isolation without resorting to a temp table--is that really not possible?


texts: Google CloudSQL - cannot delete created role

texts:
I created user observer using only postgres user on my PostgreSQL 11 instance and I don't need it anymore. But after all I can't delete it - pgAdmin showing me that my user have connect privileges granted by the cloudsqlsuperuser, and I cannot revoke it to delete my earlier created  user account!

somebase=> REVOKE ALL PRIVILEGES ON DATABASE "somebase" FROM observer;
REVOKE
somebase=> DROP ROLE observer;
ERROR:  role "observer" cannot be dropped because some objects depend on it
DETAIL:  privileges for database somebase

I tried to delete it using gcloud cli and CloudSQL console both and failed.
So how to delete it now?


texts: Have x number of rows returned for one record

texts:
I have a Dates table with every single date and a column called weekly, biweekly or monthly . I want to show my data as it would appear if it were scheduled

ex

id    date   weekly   biweekly    monthly
1   01/01/2020  1       0            0
2   01/02/2020  0       1            0
3   01/03/2020  0       0            1

So what I want to do essentially is if:

weekly=1 then show me the current date and the next 6 rows (So all the other information will be the same for this 6 rows but the date will change only)

BIweekly=1 then show me the current date and the next 13 rows

MONTHLY=1 then show me the current date and the next 29 rows

id  date    weekly  biweekly    monthly
1   01/01/2020  1   0   0
1   01/02/2020  1   0   0
1   01/03/2020  1   0   0
1   01/04/2020  1   0   0
1   01/05/2020  1   0   0
1   01/06/2020  1   0   0
2   01/02/2020  0   1   0
2   01/03/2020  0   1   0
2   01/04/2020  0   1   0
2   01/05/2020  0   1   0
2   01/06/2020  0   1   0
2   01/07/2020  0   1   0
2   01/08/2020  0   1   0
2   01/09/2020  0   1   0
2   01/10/2020  0   1   0
2   01/11/2020  0   1   0
2   01/12/2020  0   1   0
2   01/13/2020  0   1   0
2   01/14/2020  0   1   0


texts: SQL Server Standard Basic Availability Group - only 10 Listeners limit?

texts:
Is there a limit to the number of listeners that can be created using SQL Server Standard?

I'm working with a DBA who has set up a 2017 Standard server, with CU3. We have 12 databases, and he was able to set up 12 basic availability groups. He was able to set up 10 listeners, but on the 11th, is getting the error below:

  Create failed for Availability Group Listener 'test12300'.  (Microsoft.SqlServer.Smo)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476

  ADDITIONAL INFORMATION:

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

  The WSFC cluster could not bring the Network Name resource with DNS name 'test12300' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
  The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider. (Microsoft SQL Server, Error: 19471)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3015&EvtSrc=MSSQLServer&EvtID=19471&LinkId=20476

We created 11 empty databases, backed up each, and then through the AG Wizard created an AG and a Listener using DHCP. It worked 10 times, but on the 11th it failed. For the 11th listener, we chose name BAG_L11, there is nothing else that uses that DNS name.


texts:
Is there a way to mysqldump a table without some fields?

Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of table USER, so excluding some fields like mail, ip_login, etc.

How can I do this?


texts: How do loop through a dynamic SQL statement using a cursor?

texts:
I have the code below. When i run it i get the same value for all the rows fetched by the cursor. I want it to display the result fetched by the dynamic SQL and if the value returned is null i want the code to display 'N'. Please help.

ALTER PROCEDURE [dbo].[DIM_MASTER] AS

DECLARE

@V_DIM VARCHAR(80),
@V_STMT nVARCHAR(max),
@result varchar(10),
@res varchar(10),
@CURSOR_DIM cursor

set @CURSOR_DIM = CURSOR
 FOR
 SELECT FieldName FROM desc_user.dim_master

open @CURSOR_DIM

fetch next from @CURSOR_DIM into @V_DIM

WHILE @@FETCH_STATUS = 0
BEGIN

set @V_STMT =
'select  @res = '+'''Y''' +'
        where exists(select * from [desc_user].[ticket_master]
        where @v_dim is not null)'

 EXECUTE sp_executesql @V_STMT, N'@v_dim varchar(80),@res varchar(10)         OUTPUT',@v_dim = @v_dim,@res = @resULT OUTPUT
print @v_stmt

select @res as result

UPDATE desc_user.Dimension_Details set FieldValue = @res
where FieldName = @V_DIM

FETCH NEXT FROM @CURSOR_DIM
                INTO @V_DIM
                END

                CLOSE @CURSOR_DIM
                DEALLOCATE @CURSOR_DIM


texts:
I've already built a double-entry accounting schema on a previous app, and it was very complicated. For a new app I want the simplest and most elegant accounting schema possible (transaction volume is low, and uncomplicated).

People can earn money via ad on their website (like google adsense)
We issue them payments for their earnings at a varying interval (weekly, monthly, etc)
Their account can be debited (eg. their website had fraud robot visits), or it could be credited (signup bonus, or a technical error where we didnt pay them enough)

In its simplest form, I have a transactions table with these core columns (ignoring other fluff columns):

id (string)
type (enum: DEBIT/CREDIT)
description (string)
amount (integer)

All of this is well and good, there is only 2 confusing parts that I would like someone to tell me if my solution is sane or not.

First is the fact that earnings need to be "settled" in some way. Fraud and other hiccups are common, so "live" earnings are constantly adjusted. Without an intermediate settlement step, these price variations disappear into the ether.

So my first idea as to create a transactions record every single day for the previous days "settled" earnings. It might look like this, including one demo "fraud" example:
"CREDIT", "User Earnings - Jan 3, 2021", 12021
"CREDIT", "User Earnings - Jan 4, 2021", 8319
"CREDIT", "User Earnings - Jan 5, 2021", 9130
"DEBIT", "Fraud Adjustment", 771

Question 1 is, "does this seem sane so far?"

The next confusion deals with how to cleanly link a payment (transaction) with other transactions (the 4 rows above).

If I wanted to pay the above 3 earnings rows + 1 adjustment row, it would look like this:
"DEBIT", "User Payment", 28699

This is fine, except that it's not in the database which transactions this transaction is associated with. One solution might be to make a many-to-one relation on transaction to itself, but a transaction being associated with other transactions feels "wrong".
I envisioned when an admin issued a payment, it would show a list of all un-associated transactions with checkboxes next to them you could choose from, which would make those transactions tied to the payment somehow, and then they wouldn't accidentally be paid for again, as they would disappear from the list.
My biggest fear is re-inventing the wheel. I'm OK with doing a simpler solution if its "normal" in the real world. I don't want to be a single developer trying to "invent" a solution that's actually really stupid and is easily solved in a different way.
Thank you!


texts:
I have 2 tables :

Table A

ID int,
Name varchar,
Address varchar

Table B

ID int,
Name varchar,
Address varchar,
Col1...
...
Col100

I want to update A.Name with B.Name based on both table's ID.

UPDATE A
    SET A.NAME = B.NAME
    FROM A JOIN (SELECT ID, Name　from B) AS B ON A.ID = B.ID

UPDATE A
    SET A.NAME = B.NAME
    FROM A JOIN B AS B ON A.ID = B.ID

Because I don't have the permission to set the option for viewing execution plan, I ran 2 queries above many times and see the processed time to guess which one has better performance.

With about 700k records, both queries's result slightly differ.

So my question is, if executed, will both queries have the same execution plan? (I mean will the database engine process them by the same way? )

P/S : I'm using SybaseIQ 16.0.0.429


texts: AWS aurora mysql is restarted unexpectedly

texts:
In our system
one of aurora mysql instances was't available for short period.
because this instance suddenly restarted at 08/14/2019 3:56JST.

we already checked error log for aurora.
but i found restarting log only.
at the same day,connection error happened many times.
because our application couldn't connect aurora due to the above things.

we guess some failed happend at aurora.but we don have any info.

what things happened?
and does anyone know how to investigate this problem?


texts:
This morning, I had the following event:

  200310  9:54:57 [ERROR] Slave SQL: Could not execute Update_rows event on table Call_Center_Infinit.queue_member_table; Corrupted replication event  was detected, Error_code: 1610; Corrupted replication event was detected, Error_code: 1610; handler error No Error!; the event's master log  log TN-TX-DB03-bin.000972, end_log_pos 551613719, Error_code: 1610.

I ran mysqlbinlog --base64-output=DECODE-ROWS -vv  log TN-TX-DB03-bin.000972 and search "end_log_pos 551613719" using vi, I found the following:

#200310  9:54:57 server id 6  end_log_pos 551611999     Query   thread_id=2180761724    exec_time=0     error_code=0
SET TIMESTAMP=1583855697/*!*/;
BEGIN
/*!*/;
# at 551611999
# at 551612137
# at 551612227
# at 551612741
# at 551613719

#200310  9:54:57 server id 6  end_log_pos 551612137     Table_map: `Call_Center_Infinit`.`Agentes` mapped to number 1089
#200310  9:54:57 server id 6  end_log_pos 551612227     Table_map: `Call_Center_Infinit`.`queue_member_table` mapped to number 1140
#200310  9:54:57 server id 6  end_log_pos 551612741     Update_rows: table id 1089
#200310  9:54:57 server id 6  end_log_pos 551613719     Update_rows: table id 1140

### UPDATE `Call_Center_Infinit`.`queue_member_table`
### WHERE
###   @1=189482 /* INT meta=0 nullable=0 is_null=0 */
###   @2=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @3=52984 /* INT meta=0 nullable=1 is_null=0 */
###   @4=53740 /* INT meta=0 nullable=1 is_null=0 */
###   @5='53740' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @6='52984' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @7='Agent/53740' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @8=NULL /* VARSTRING(128) meta=0 nullable=1 is_null=1 */
###   @9=0 /* INT meta=0 nullable=1 is_null=0 */
###   @10='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
### SET
###   @1=189482 /* INT meta=0 nullable=0 is_null=0 */
###   @2=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @3=52984 /* INT meta=0 nullable=1 is_null=0 */
###   @4=53740 /* INT meta=0 nullable=1 is_null=0 */
###   @5='53740' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @6='52984' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @7='Agent/53740' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @8=NULL /* VARSTRING(128) meta=0 nullable=1 is_null=1 */
###   @9=1 /* INT meta=0 nullable=1 is_null=0 */
###   @10='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */

according to me, from here is stopped replica


texts: Selecting a proper data warehouse and/or big data approach

texts: We have about 2 TB of data distributed across literally thousand of tables in PostgreSQL 9.4.9, and we think that we should be moving the historic data to a more report-oriented and efficient solution. Which is the proper solution is what troubles me, as I'd like it to be AWS based (perhaps RedShift, EMR or other) as our platform resides there, but even within that ecosystem, there are several approaches to be used. I'm aware that asking about the choice of a specific engine and/or solution is offtopic, but I'd certainly like a suggestion of what kind of tools to choose from, without necessarily implying the use of single one. As a team, we have no knowledge yet of the data warehouse architecture and, just a bit of the big data technologies, but we certainly have to study on those subjects.


texts: Error SQLCODE=-443, SQLSTATE=38553 after applying fix pack 8 to DB2 v10.5

texts:
We have an utility which retrieves some data from DB2 (v 10.5 for AIX) database. After we had applied Fix Pack 8 to database (client and JDBC driver had been also updated) this utility didn’t work. There was the following error in log:

13:09:48,270 [INFO  ] SMU  {SCSExecute.Execute.java}  13:09:48,258 [ERROR ] org.apache.jackrabbit.core.fs.db.DatabaseFileSystem  failed to initialize file system
13:09:48,277 [INFO  ] SMU  {SCSExecute.Execute.java}  com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-443, SQLSTATE=38553, SQLERRMC=SYSIBM.SQLTABLES;TABLES;SYSIBM:CLI:-514, DRIVER=3.69.66

As recommended here (http://www-01.ibm.com/support/docview.wss?uid=swg21369762) for similar cases we executed the following commands in DB2:

db2 connect to <databasename>
db2 bind db2schema.bnd blocking all grant public sqlerror continue
db2 terminate

But it didn’t solve the issues.

What could we forget to do?
How could we check that bind command executed properly?
How could we try to solve the issue or reveal its root?


texts: Users cannot view tables in non-default schema in SSMS

texts:
I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has permissions set to access & modify the table (SELECT, UPDATE, DELETE, etc) through the dbo_datareader and dbo_datawriter roles. However, they cannot see any of the tables in the SSMS object explorer.

I've tried granting permissions to view definitions:

grant view definition on SCHEMA :: [TestSchema] to [User]

That didn't work. I tried setting the table-level permission:

grant view definition on [TestSchema].[NewTable] to [User]

That also didn't work. Then I tried just a blanket grant:

grant view definition to [User]

And that did work; they can now see TestSchema, as well as other schemas that they shouldn't have access to.

My goal here is to allow the user to view all tables within a given schema. How do I accomplish that? If I should be able to do this by default, what permissions should I be looking at to find why I can't?


texts: Value of an UNSET NEW.Column in Before Update Trigger

texts:
SETUP

A very simple table with 2 columns.  (UserName, Password)

In this table I have a Before Update trigger:

BEGIN
    SET NEW.Password = SHA2(NEW.Password,256);
END

Problem

When ever I update the username AND password, all is great.
BUT when I try to update just the username:

UPDATE `UserCreds` SET `UserName`= 'BOB'
WHERE `UserID`= 156

the password Also changes.

Question

How do I get the password field to update ONLY when the update statement contains

SET `Password` = 'supersafepassword'

What I tried

I tried a few variations of the following but everything still seems to evaluate to true, and the password still changes.

BEGIN
  IF NEW.Password IS NOT NULL THEN
    SET NEW.Password = SHA2(NEW.Password,256);
  END IF;
END

So i guess the real question is, What does the value of NEW.Password equal when the Update statement doesn't set it equal to anything?

In Short

If I don't Set the password in my update statement i don't want my password to change (as in changing username only)... but if I DO set it, I want it to be encrypted before saving.


texts:
I'm trying to ignore duplicates between two providers in my tables while using join.

I'm using this query for now:

SELECT *
FROM A AS a1, A AS a2
WHERE a1.provider <> a2.provider AND a1.etype = a2.etype

Unfortunately, that query gives me duplicates (two rows of the same providers, in opposite sides).

I'm trying to ignore rows that appear twice in the opposite sides, for example:

etype    provider    etype    provider
hello    aaa         hello    bbb
hello    bbb         hello    aaa

This dbfiddle illustrates the desired output as well.


texts:
I would like to perform OPTIMIZE TABLE opearation , this table is a partitioned table.

Which method can be better in this scenario:

  OPTIMIZE TABLE tblname;

or

  ALTER TABLE tblname OPTIMIZE PARTITION p0, p1;


texts: MySQL 5.7 - Calling procedure with dynamic name

texts:
I'm trying to write a MySQL (5.7) procedure that dynamically calls another procedure based on the one of its inputs.

The code I came up with so far is this (procedure_exists works for checking whether or not the function is available):

CREATE PROCEDURE process_event (IN event_type VARCHAR(32), IN aggregate_id CHAR(36), IN payload JSON)
BEGIN
    DECLARE handler_name VARCHAR(38) DEFAULT CONCAT('handle', event_type);

    IF procedure_exists(handler_name) THEN
        SET @handler_sql = CONCAT('CALL ', handler_name, '(?,?)');

        PREPARE prepared_statement FROM @handler_sql;

        SET @aggregate_id = aggregate_id;
        SET @payload = payload;

        EXECUTE prepared_statement USING @aggregate_id, @payload;
    END IF;
END;

The procedure is called from this trigger:

CREATE TRIGGER events_before_insert BEFORE INSERT ON events FOR EACH ROW
BEGIN
    DECLARE next_sequence BIGINT DEFAULT 0;

    SELECT COALESCE(new.sequence, COALESCE(MAX(sequence), 0) + 1) FROM events INTO next_sequence;

    SET new.sequence = next_sequence;
    SET new.recorded_at = COALESCE(new.recorded_at, NOW());

    CALL process_event(new.event_type, new.aggregate_id, new.payload);
END //

This doesn't work however, because I keep getting

[0A000][1336] Dynamic SQL is not allowed in stored function or trigger

From what I understand, disallowing that by default makes perfect sense, but from what I've gathered there seems to be no way of opting in to theoretically less secure behavior, by allowing certain types of statements or disabling the check altogether.

Do you have any suggestions for getting this to work?

In short the question boils down to this:

  Can I somehow have a procedure to which I pass event_type (VARCHAR(32)), aggregate_id (CHAR(36)) and payload (JSON) which will then automatically call handle{event_type}(aggregate_id, payload) if it exists?


texts:
In Cassandra, you can have a partition key to align partitions to particular server nodes.

My understanding is that you can distribute transactional responsibility horizontally in SQL Server 2016 (across horizontal servers) but you always come back to a 'node of truth for the database'.

I'm not sure if you can break that transactional responsibility down to the table or partition level.

For example - suppose I have asset information - I expect to have 1000 million rows in three years. I expect that my server load will increase due to usage, to the point where I'll have to scale my servers (web and database) horizontally.

I want to be able to do reads and writes across three database servers. On the first server I'll partition my assets so that the assets A-I can read and write, on the second I'll have J-R, and the third S-Z.

My question is: Is it possible to have a partition to distribute transaction load horizontally in SQL Server 2016?


texts: Migrating schema from Entity-Attribute-Value (EAV) model to proper tables

texts:
I have an Entity-Attribute-Value (EAV) database in Oracle 11.  For many, many reasons, I would like to model this using actual tables.  Changing all the client code at once is not possible.

Existing Tables

The existing tables look something like this (names changed to protect the guilty):-

entity_template - a master list of the possible entities.  There are 23 of these.
entity_template_value - a master list of possible values which may be stored for a particular entity.  This will become a column in the new solution.  There are about 200 rows here.
entity - an individual instance of an entity. There are 60 million of these.
entity_value - the intersection between entity_template_value and entity which actually holds a value.  There are 166 million of these.

All tables have surrogate primary keys.

Target Tables

I intend to create 23 new tables, one for each entity_template.   For example, foo_entity, bar_entity, etc. Each of these will have the same primary key as entity, which is a foreign key to entity.  The columns in these new tables will represent the rows from entity_template_value.

I'll load these tables up with INSERT /*+ APPEND */ and we'll suffer an outage while this happens.

Maintaining Compatibility

When a row is inserted into entity, a new row will be inserted into the appropriate child table.  All columns besides the primary key will be null.

I'll replace entity_value with a view.  The view will be have about 200 UNIONS, where each one selects a column from one of the 23 new tables.  An INSTEAD OF trigger will be written to translate inserts/updates to updates on one column in one of the new tables.   Deletes on entity_value will become updates to NULL of one column on one of the new tables.

Then progressively, client code can be written to access the new tables directly.  One glorious day, the view can be dropped and we can forget it was ever like this.

Are there any flaws in this approach, or can you suggest any improvements?


texts:
had a problem with a database on a machine that needed to be formatted, and I backed up the MySQL Server folder yesterday containing the .IDB and .FRM data regarding the DB structure and data.

When replacing files of the same structure on another machine and attempting to perform a query, the database is corrupted. After a bit of research I read that after version 5.5 MySQL creates a reference so it does not exchange this data.

How could I recover this data? Is there any other tool for me to read the file?


texts: SQL Server Installation - TempDB not created in the directory specified in ConfigurationFile.ini

texts:
I'm attempting to install SQL Server 2016 SP2 on a newly created EC2 instance. I'm using a configuration file I previously generated. In the configuration file I've specified where I'd like the TempDB files to be created along with the Database Root data and log directories. That looks like this:

; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT="8"

; Specifies the initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILESIZE="88500"
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH="0"

; Specifies the initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILESIZE="88500"
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH="0"

; Directories for Database Engine TempDB files.
SQLTEMPDBDIR="T:\Data"
; Directory for the Database Engine TempDB log files.
SQLTEMPDBLOGDIR="T:\Log"

; The Database Engine root data directory.
INSTALLSQLDATADIR="O:\Data"
; Default directory for the Database Engine user database logs.
SQLUSERDBLOGDIR="O:\Log"

After the installation I get this error message:

MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'O:\Data\MSSQL13.BI\MSSQL\DATA\templog.ldf'.

The log output from the installation has the same directory paths specified in the ConfigurationFile for TempDB and for the the user data/log directories.

For the life of me I cannot understand why the TempDB files are being created in the filepath specified in the error message.


texts: MySQL: Importing databases from older version

texts:
I'm in the process of moving everything from a very old production server to a new one. I'm now trying to copy the MySQL database over to the new system. I have very limited knowledge of MySQL, BTW. First some version info:

Old server:

$ mysql -V
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

New server:

$ mysql -V
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

On the old server, I made a dump of all databases first:

mysqldump -u root -p --all-databases > all_databases.sql

Then on the new server, I imported the databases with:

mysql -u root -p < all_databases.sql

But now, when I try to run mysql, I get an error:

$ mysql
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format

So apparently it has overwritten the user database as well (I really had no idea that would happen, but, you know, limited MySQL knowledge...).

So how can I fix this?


texts: Which field should be the candidate for index in this case?

texts:
You're tracking visits already in another table that contains quite some information about the visit.

In order to block duplicate visits after a certain amount (Let's say 5) you think its best to use a separate table that only contains the IP address and the date of the visit.

Which field should be indexed? IP address or Date? Both will be used in the query when you check for duplicate visits by that IP on a certain date.

Using both the fields will be an overkill? Is there any tool that can help with this estimation?

Table

CREATE TABLE `ip_visit`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL,
`date` date NOT NULL,
 UNIQUE KEY `id` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1

This table is expected to receive 100k entries per day.


texts: Looking for a set-based solution for a rolling 30-day period

texts:
I have a stored procedure (MS SQL Server 2008 R2) that needs to see if 10 points/sales fall within a 30-day rolling window.  So far, I have a solution that works, but it's not a "proper" solution as it isn't set based.  I use while loops to process what's going on.  Looking at an agent's points, I take the date of the first point, and see if 9 more fall within a 30-day window, if so, I mark them as processed and used and proceed to the next one.  I'm thinking there has to be a "better" solution that is set-based.

To see what I have, I set up a SQL Fiddle.  It shows at the end that we have processed 3 agents and there are 4 incentives to be given.  Is there a better way to do this without using while loops that's more set-based?

SQL Fiddle


texts: How to auto-refresh/re-run a query every few seconds?

texts:
I have a script running against a database with no GUI. This simple query lets me see its progress:

select
    *,
    (select count(*) from domains) as count,
    (select 456976-count(*) from domains) as remaining
from domains
order by domain desc
limit 0, 1;

Using MySQL Workbench, how can I automatically refresh/re-run this query every few seconds?

I tried Googling, but didn't come up with anything relevant.

Aside: the magic number 456,976 is 26^4; it has to do with the script.


texts:
mysql> select 'user_url' from wp_users;
mysql> select `user_url` from wp_users;

It can get the same result ,does  character ' and ` equal in mysql command?


texts:
I'm not sure if this is the right place for this, so please feel free to tell me if it is off-topic or there is a better place to post.

I am working on a fantasy sports based project, and the number of tables is getting unruly. I have tables like nba_players, nfl_players, etc, and nba_players_to_teams, nfl_players_to_teams, etc. (NBA and NFL are basketball and american football leagues, respectively). These all share the same table schema and code-base, but have their foreign keys on different tables.

My question is, does using table inheritance make sense for situations like this? The "base" table would sit empty, and would only be used to enforce the schema. Will changes to the "base" table propagate to tables that inherit from it? At this point the schemas are pretty stable, so that's not a huge concern.

Any thoughts or concerns that I haven't addressed? I'm still reading about table inheritance and the pros and cons, but any resources you have would be greatly appreciated.


texts: What is a good way to Merge 2 range sets into a single view

texts:
The real purpose of this has to do with effectivity dates and contains additional keys and fields, but I simplified it for the sake of, well, simplicity.

Sample:

test_Widget Table
theFirst   theLast    Letter
1          5          A
7          10         (null)
11         15         C

test_Default Table
theFirst   theLast    Letter
1          8          T
9          12         U
13         20         V

Expected Output:
theFirst   theLast    Letter
1          5          A
7          8          T
9          10         U
11         15         C

Here's what I'm trying to do:

Use the Widget and Override table to craft a query that has for every range of numbers the proper Letter chosen.

Use Widget.Letter for all Letters, but in the case of a NULL in Widget use the Default.Letter. Only include ranges that are inclusive to the Widget table

I have working code that does it, but I'm wondering if there's something a bit more elegant, built-in, etc to handle this.

My code:

select distinct
  case when w.letter is null then greatest(w.thefirst, d.thefirst) else w.thefirst end as tfirst,
  case when w.letter is null then least(w.thelast, d.thelast) else w.thelast end as tlast,
  case when w.letter is null then d.letter else w.letter end as letter
from test_widget w
inner join test_default d
on
w.thefirst <= d.thelast and
w.thelast >= d.thefirst


texts: What type of SQL Server Replication fits our scenario? Merge or Transactional?

texts:
Background

In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server.  We additionally have another (non-MSSQL) database which we use for our POS system, which automatically updates the web server when things (such as product information) changes.  This has two problems:

Connections can be slow, so we can't easily work with local copies of the code
Sometimes we can't reach the web database (even though the site still works), which causes the POS system to hang.

The solution I'm working towards is setting up a second SQL Server instance (2005 or 2008) locally at the corporate headquarters, directing the POS system at it, and using some form of Replication to sync changes between them.  However, I can't tell whether we should use merge replication or transactional.

Question

Will Merge or Transactional replication serve us better?

Our requirements are:

Update product data at HQ and push it to the website as read-only.
Update order data on the website and push it to HQ

Sync small changes from HQ back to website (order status)

No disruption in availability on either end when communication is impossible

i.e. The HQ can still access and update our copy, and the website can update its, and when communication is restored, changes will be synced.

(Optional) An easy way to make a copy of the HQ database with the replication removed, for code testing purposes.

Which one is the better fit for our requirements?


texts: mysql pirate users logged in, what should I do

texts:
I'm using Mysql Server 5.1.73 and when I do a show processlists I can see
some connections which shouldn't be there. In theory only the web app should be connected to this database. The connection comes from foreign country and with user which even don't exists. They are all connected to 'NULL' db, and I haven't noticed any damaged yet. What shoud I do ?


texts: How to concatenate three columns data and insert it in to another column when inserting the data into the table everytime

texts:
I have a SQlite3 table with six columns and their names are "title", "first_name", "last_name", "mobile", "designation" and "person". I want to combine the string "title", "first_name" and "last_name" and update in to the column as "person". How to do. I tried below execution but not working. How to do.
Below is the example:
import sqlite3
conn = sqlite3.connect('employee.db')
cur = conn.cursor()

cur.execute("CREATE TABLE IF NOT EXISTS details(title TEXT, first_name TEXT, last_name TEXT, mobile TEXT, designation TEXT, person TEXT)")

cur.execute("""INSERT INTO details(title, first_name, last_name, mobile, designation, person) VALUES( "Mr.", "Fname", "Lname", 123, "TL", '')""")

cur.execute("SELECT title || ' ' || first_name || ' ' || last_name AS person FROM details ORDER BY person")

conn.commit()


texts: How do you count the occurrences of an anchored string using PostgreSQL?

texts:
If I have a string in a column on a row in a table like this

1 2 2 2 2 2 2

How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ".

For the purposes of this, let's treat the numbers as substrings

Sample data

CREATE TABLE foo
  AS
    SELECT 1 AS id, '1 2 2 2 2 2 2'::text AS data;

TABLE foo
 id |     data
----+---------------
  1 | 1 2 2 2 2 2 2


texts: Can postgresql WAL be regenerated or resent to the standby server?

texts:
Does anyone know if there's a way to resend or regenerate a WAL file that was missed by the standby server? During my testing I've gotten into a situation where the main server fails to send a file or 2 due to some issue on the standby server. Once I correct the problem then the main server is able to send files just fine except it does not attempt to resend the files it missed and so the standby server gets "stuck" waiting for file x. In the mean time WAL files are stacking up in my wal archive directory on the standby server, they don't get processed because postgresql is waiting for a specific file.

So what I'd like to know is if there's a way to resend and/or regenerate this file on the primary server. If so, how? Or is that data lost? and if so, how do I tell the standby server to stop waiting for that file and move on to the next?

I'm using omnipitr-archive and omnipitr-restore commands for my archiving and restoring.


texts: SQL Server Maintenance Plan TSQL query to log the backup databases

texts:
Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up?

Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3.

I wanted a way to do

INSERT INTO LOG VALUES (BD1,...)
INSERT INTO LOG VALUES (BD2,...)
INSERT INTO LOG VALUES (BD3,...)

But I did not want to statically type this, but have a generic SQL that knows which DBs where backup and insert those.

Thanks


texts: How can I select a field value in PostgreSQL based to the date computation within the table?

texts:
I have a PostgreSQL table that have these values:

dollar_rate; end_date
------
1. 46.77; 2015-09-16 11:03:03.4082
2. 46.70; 2015-09-17 11:27:00.2446
3. 46.67; 2015-09-17 15:09:15.0354

Now I have this query:

  SELECT dollar_value,end_date-now() from dollar_rate;

The results:

1. 46.77; -04:48:34.945704
2. 46.70; 19:35:21.890696
3. 46.67; 23:17:36.681496

As you can see at the third row, I would like to select this row, that is, the row that contains the highest value with the computation end_date-now(). How can I do this in PostgreSQL?


texts: SQL Server 2012 Express SP1 install still not 11.1

texts:
I run the SQL Server 2012 Express SP1 install and it succeeds. After I have rebooted my computer, I do a

select @@version

But still I only have version 11.0.21600.64. I have tried this on two computers now, still the same result.

The file I am running is: SQLEXPR_x64_ENU.exe downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=35579


texts: mysql how to copy foreign key data from one table to another

texts:
I have 3 tables.
Each table has its own primary key "id"
table 2 has a foreign key that references table 1's primary key
table 3 has a foreign key that references table 2's primary key.
I want to add to t.3 the foreign key that links t.2 - t.1 so that t.3 is also linked to t.1. I cant figure out how to copy the relevant column from t.2 to t.3.


texts: Sanitizing a form's database input while retaining basic HTML markup

texts: Is there a way to sanitize database input (say, for a comment form), which also allows for the end user to use basic HTML styling markup? As in, if a user wraps their text in <i>/<em> or <strong>/<bold>, the text gets styled, all while preventing users from entering malicious PHP and SQL commands?


texts:
I got a rails api server along with a PostgreSQL server on a Digital Ocean machine. On Digital Ocean's dashboard, I see that the CPU has it's average on 50%, although this machine has really low usage. Using top, I found out that the cause was Postgres server:

Using top -c -b -n 1 -u postgres:

This is basically the default installation of pgsql 9.6 on an Ubuntu 16.04 box. What might be happening? What the heck is ./x4226170084 process using all my CPU?


texts: Why ndb data memory is 94% if i m using disk storage cluster

texts:
I have following structure

2 data node
1 management node
2 sql node

i have create one log file of initial size 200M

then created table space and added 3 data file
data1.dat (initial size: 10GB)
data2.dat (initial size:20GB)
data3.dat (initial size:50GB)

but after inster 10 million record of size around 15 GB its is throwing error of like

table space is full

why this is happening if i am using disk storage for ndb cluster

plz help


texts:
Whilst I can just about script to pull data via queries, I'm finding it very difficult to figure out how to export the results of a query to a .csv file and save in a network storage location (novice when it comes to the more technical side!).  I've tried various things like the bcp command or using the export wizard or sqlcmd but I can't get anything to work!  I also want to be able to automate the export i.e. run an agent job to create/run this export weekly.
An ex-colleague had set this up but couldn't get it to work.  When I run this:

USE [DataInsight]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

BEGIN

sqlcmd -S londata01\raisers -d DataInsight -E -s, -W -w 65535 -Q "SET NOCOUNT on; select * from dbo.All_Email_Addresses" -s "," -o "C:\Users\scarsen\Documents\all_emails.csv"

END

whereby londata01\raisers is the server name, datainsight the DB and all_email addresses a table.  I get

  Incorrect syntax near 'sqlcmd' when run it.

I turned on SQLCMDMode in SSMS and still get this error.

Tried this:

bcp "select * from datainsight.dbo.All_Email_Addresses" queryout 'C:\Users\scarsen.csv' –c –t , –S londata01\raisers –T

but get Incorrect syntax near 'queryout'.

Because I want to automate for a weekly output, I can use an agent job so need to be able to create export within SSMS (I think)

I also tried something that I can't remember using master..xp_cmdshell but a message came up saying I didn't have security settings enabled (or similar)

I also tried saving an export via the wizard to an SSIS but get a message saying storing or modifying packages requires runtime and db to the the same version.

In a nutshell, tried lots of things with my limited knowledge but wondered if anyone might be able to help?


texts: Full, incremental or differential backup?

texts:
Using RMAN my database is backed up once a day using a command similar to this

backup incremental level 1 cumulative
[...]
database
include current controlfile spfile

In the recovery catalog database I can see one of these backup jobs like this

    DB_KEY DB_NAME  SESSION_KEY COMMAND_ID              START_TIME          END_TIME            INPUT_BYTES OUTPUT_DEVICE_TYP STATUS        INPUT_TYPE    ELAPSED_SECONDS
---------- -------- ----------- ----------------------- ------------------- ------------------- ----------- ----------------- ------------- ------------- ---------------
   xxxxxxx dbname       xxxxxxx 2016-11-12T05:03:21     12.11.2016 05:02:28 12.11.2016 05:03:21   862159354 SBT_TAPE          COMPLETED     DB INCR                    53

In the table rc_backup_piece_details there are five entries with this backup job's session id

SESSION_KEY DB_NAME      DB_KEY     BP_KEY B INCREMENTAL_LEVEL DEVICE_TYPE          START_TIME          COMPLETION_TIME     ELAPSED_SECONDS S      BYTES
----------- -------- ---------- ---------- - ----------------- -------------------- ------------------- ------------------- --------------- - ----------
    xxxxxxx dbname      xxxxxxx    1000001 I                 1 SBT_TAPE             12.11.2016 05:03:14 14.11.2016 05:03:15               1 A     423549
    xxxxxxx dbname      xxxxxxx    1000002 I                 1 SBT_TAPE             12.11.2016 05:03:05 14.11.2016 05:03:11               6 A  862156486
    xxxxxxx dbname      xxxxxxx    1000003 D                   SBT_TAPE             12.11.2016 05:03:16 14.11.2016 05:03:17               1 A   45324586
    xxxxxxx dbname      xxxxxxx    1000004 I                 1 SBT_TAPE             12.11.2016 05:02:30 14.11.2016 05:03:01              31 A  256422458
    xxxxxxx dbname      xxxxxxx    1000005 I                 1 SBT_TAPE             12.11.2016 05:03:13 14.11.2016 05:03:14               1 A   45624595

As you can see, there is one backup piece wich D as backup type. In the Oracle documentation I found the following:

  The type of backup. Possible values are D for data file or control file backups, I for incremental backups, and L for archived log file backups

Does this mean, that among these backup pieces there is one incremental backup of a data file or control file?

If this is the case, then I am wondering, why this only happens with this particular database. All other databases are backed up with include current controlfile spfile too.

The difference between this and other databases is that most other databases are backed up incremental level=0. So might there be the difference?

Edit: So, if backup type D is not incremental backup, then why is this backup piece contained in a backob job with input_type = 'DB INCR'? AND could  what part of the rman command triggers this datafile backup and if it is include current controlfile spfile then why is there a backup piece with backup type D for any other database?


texts: Problem with Czech collation Czech_CI_AI in MS SQL Server 2008 R2

texts:
I have the following issue with accent insensitive search in Czech language. My database is set to Czech_CI_AI collation. For some diacritics system works correctly (ie. I), but for some does not (ie. R). In my database there are several 'DVORA*' records:

SELECT contact_name
FROM   CONTCTSM1
WHERE  CONTACT_NAME LIKE 'DVO%'

Results:

  DVOŘÁČKOVÁ,
    IVETA DVOŘÁČKOVÁ,
    JIŘINA DVOŘÁK


texts: How to restore several databases with binlogs?

texts: I need to restore 3 databases but I don't have the backups of the, I investigate that it can be done with the binlog files, but how does that work when you have several databases that need to be restored?


texts:
I have installed mysql5.5 via secure installation procedure by using:

sudo mysql_secure_installation

Now when I am trying to login in MySQL from the terminal by using:

sudo -u root -p

The following error is displayed:

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have also used:

sudo -u root

Which I got the following error:

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

What should I do for this error?


texts:
Mysql before executing a query with multiple joins. It creates multiple query plan and find the lowest cost plan to execute the query(approx).

My questions are

How the query plan is generated on what basis(like no of fields in the where condition for a particular table)
Is there any formula(or steps) to generate query plan by ourself


texts: How do I order by NULLS LAST if my database doesn't support it?

texts:
Select      FullName, Login
FROM        [User]
ORDER BY    FullName desc, login asc

Results

FullName                      Login
----------------------------- ------------------------------
M...... H.........            mh..@...com
A.... H.....                  ad..@...com
NULL                          and..@...com
NULL                          ben..@...com
NULL                          roc..@...com

What I want

FullName                      Login
----------------------------- ------------------------------
A.... H.....                  ad..@...com
M...... H.........            mh..@...com
NULL                          and..@...com
NULL                          ben..@...com
NULL                          roc..@...com

I want the full name in abc order desc and same with login, but I want all nulls to go to the bottom.


texts: python ibm_db driver and decimal values?

texts:
It appears as if decimal values are treated as strings by ibm_db. Small example:

Python 2.7.14 (default, Mar 14 2018, 13:36:31)
[GCC 7.3.1 20180303 (Red Hat 7.3.1-5)] on linux2

]$ cat aa.py
#!/usr/bin/python

import sys
import ibm_db

conn = ibm_db.connect("dsn=sample","********","********")
s1 = ibm_db.prepare(conn, "values (cast(1 as int),cast(0.1 as decimal(2,1)))")
ibm_db.execute(s1)
d1 = ibm_db.fetch_both(s1)
print d1[0], type(d1[0]), d1[1], type(d1[1])

Output:

1 <type 'long'> 0.1 <type 'str'>

I'm dumping the dict as JSON to a file (for filebeat -> logstash), so I really need numeric types to stay numeric. I guess I can check the prepared statement for the type (the type is correct there) and massage the data before dumping, but that seems terribly wrong.

Any thoughts on how to get numeric data into the JSON object in the simplest possible way?

I found the following 3 year old mention:

https://github.com/ibmdb/python-ibmdb/issues/129

is this limitation still in play?


texts: Data History Table vs Using a Current Record Flag

texts:
There are two strategies when dealing with data records which contain multiple versions. One is to have current records in one table, and their past versions in a history table.

The other is to have all versions in the same table with a flag on the current version.

I've seen arguments for each, and I've seen community agree with both strategies in different posts.

If there is no right answer, how does one decide which strategy to take? I can see the advantage of keeping all the records in the same table for query simplicity (all the records in one table) and less tables in general.

The flip side is you have these huge tables with a ton of records depending on how many versions and complicated relationships between tables.

Pro History Table Database Design for Revisions? Database history changes, same or different table?

Pro Current Record flag https://softwareengineering.stackexchange.com/questions/94489/handling-deleted-users-separate-or-same-table

Other Articles https://softwareengineering.stackexchange.com/questions/94489/handling-deleted-users-separate-or-same-table


texts:
I have a MySQL database table named items with 10,000+ records. Of these records, I would like to export only records where item_isbn IS NULL.

SELECT * FROM Items WHERE item_isbn IS NULL produces a table with 500 records, a sample of the data is below;

+---------+------------+-----------+
| item_id | item_title | item_isbn |
+---------+------------+-----------+
| 1       | Jo         | NULL      |
+---------+------------+-----------+
| 24      | Cars       | NULL      |
+---------+------------+-----------+
| 315     | Fun        | NULL      |
+---------+------------+-----------+
| etc     | etc        | etc       |
+---------+------------+-----------+

I have exported this table as a CSV file, and have populated it with the necessary ISBNs via MS Excel. I'm now ready to re-import the data into the itens table.

How can I re-import these 500 records into the items table, whilst overwriting only the same 500 records, and maintaining the same item_id.

Is there a more efficient way of doing this?

I am using PhpMyAdmin.

Any help appreciated.


texts: Is there any point to SQL partitioning if I index properly and avoid full-table scans?

texts:
General overview of what I'm building:

Storing time-series data so I expect millions of rows added per month.
Table has 4 columns, 2 of which are indexed, 1 is a numeric foreign key and the 4th contains a non-searchable JSON blob.
4th column is indexed in Elasticsearch for full-text search needs.
For every 10000 rows inserted, ~1000 queries are immediately run to retrieve the last 60 days worth of data for each distinct entity. On occasion 10 users have access to a frontend to manually run queries for data of any age; UX is important so queries must return within seconds.
I only need to keep a rolling year's worth of data.

The only upsides I'm seeing to partitioning is the ease of dropping entire partitions at once when purging, like with ES indexes. It is also suggested queries limited to recent data perform better when partitioned by temporal grouping.

But if I maintain proper indexes and only query on those, at what point do I need to consider partitioning?

Thanks!


texts: I mistakenly updated the null values in my table to a string similar to an already present string. For e.g. I changed the NULL values to 'WL:5' and there were records for 'WL:5' already present. I want to change that string back to null values now, but if I update the table now, the other values which have the same name will also change. Any ideas?


texts: Does Management Studio let me change the format of new script names?

texts:
I use SQL Server Mangagement Studio to examine the definition of objects in my database.

For example, to examine the definition of the table dbo.MSreplication_options in the master database, I would right-click on the table in the Object Explorer and choose 'Script Table as... > CREATE to > New Query Editor Window...'.

As you can see from the screenshot of the current state of my Management Studio, a new tab is created for a new script called "SQLQuery9.sql", which contains the table definition.

By this time I have already examined a few other objects in this way, and have forgotten what most of the other scripts called "SQLQuery1.sql", "SQLQuery2.sql" and so on contain.

How can I configure management studio give the script a more descriptive name, perhaps containing the name of the object, like "dbo.MSreplication_options.sql", for example.

My Management Studio version is 10.0.5500.0.


texts: Nvarchar to varchar to nvarchar characters conversion

texts:
I had a table filled with some greek text rows as nvarchar(2000).

Lately, I altered the type of the column to varchar(4000) but I realized that some greek characters were shown as question marks.

So, I tried to change back to nvarchar(4000) in order to fix it as I suppose that the characters' unicode still remains the same.

I am just wondering, is there any way to fix this instead of restoring the backup I created before altering the table?


texts: Result on execution of any SQL is number 2

texts:
After successful execution of any sql in oracle sql command line it shows 2
like i want to see the table or description of any table it show 2 instead of showing the table.

SQL> select * from dual
  2
SQL> select * from user_objects
  2
SQL>


texts:
I have the following query that tells me the status of queries in a database I'm responsible for (although I am not a DBA).

SELECT
     T3.FullStatement as FullSQLStatement
    ,T3.ExecutingStatement
    ,req.session_id as SessionId
    ,T2.login_name as LoginName
    ,command as SQLCommand
    ,start_time as StartTime
    ,DateDiff(MINUTE,start_time,GetDate()) as ElapsedTimeMinutes
    ,req.status as QueryStatus
    ,req.wait_type as WaitType
    ,req.wait_time as WaitTimeMs
    ,blocking_session_id as BlockingSessionId
    ,req.row_count as [RowCount]
    ,req.cpu_time as CpuTimeMs
    ,req.total_elapsed_time as TotalElapsedTimeMs
    ,SubString(sqltext.TEXT,req.statement_start_offset,req.statement_end_offset-req.statement_start_offset)
FROM sys.dm_exec_requests req
    Inner Join sys.dm_exec_sessions T2 ON T2.session_id = req.session_id
    Cross Apply dbo.GetExecutingSQLStatement (req.session_id) T3
    Cross Apply sys.dm_exec_sql_text(sql_handle) AS sqltext
where req.database_id = 5
Order By 6 Desc

It generates output that looks, partly, as follows:

Even though it doesn't show very well in the example I've included, the start_time is the start time of the full statement and not the executing portion of the statement. Is there somewhere I can get the start time of the executing portion along with the start time of the full statement. This is important to me because, obviously, a full statement could have many individual queries.


texts: What are the implications of constraint locality in Postgres?

texts:
I'm a bit confused as to how constraints are managed in Postgres.

The source of this confusion is a diff between the output from two runs of pg_dump (on different boxes) that should be identical. The lone difference between the dumps is a change in default tablespace prior to setting up a number of constraints. To be extra clear here, no tables are created after the change in tablespace, only constraints.

I'm trying to determine if this actually affects anything about the constraints themselves. My main question:

pg_constraint has a conislocal boolean attribute. Does this indicate whether the constraint is stored in the same tablespace as the table with which it is associated?

Some followup questions:

Assuming the prior question is true, if all constraints are local, and the affected dumpfile only changes the tablespace prior to creating the constraints (ie all of the actual tables are in the 'correct' tablespace) will there be any difference between the layout of the database when restoring the two dumpfiles?
Any hazardous guesses as to what could account for the change in default tablespace between the two instances?


texts: How to track changes to a record that spans multipe tables

texts:
I have the challenge to design a new database using a RDBMS, Oracle, to be precise. In this database there will be one specific record/document which may span multiple tables.

With as little duplication as possible, I want to keep track of the changes made to this record, and offer a simple way to create altered copies of that record.

I don't want a history table that will sum up the changes in text, because I want to be able to fetch document x exactly the way it looked at time t. But I don't want to have a full copy for every history entry either.

Are there best practices or patterns how to achieve this?

I may have an idea how to solve this using a strategy mimicking git, but I wanted some opinions first before I go that road.


texts: Can I change the default settings for SQL Server Replication

texts: Is there a way to change the default setting in SQL Server replication? Example when I create an new article I want to have the create Non-Cluster set to true all the time.


texts: What does autogrow run in the background (Is it a DDL statement?)

texts:
I am currently running into the following intermittent error from my applications logs

  Commit - ERROR: Code=0x80004005 Source=Microsoft OLE DB Provider for SQL ServerDescription=Insert bulk failed due to a schema change of the target table.

I am unable to find out what DDL statement would be causing this and have found nothing in the SQLServer logs themselves. (I assumed offline indexing, but that is not happening)

My hypothesis is that the autogrow event is set too low and the intermittent problem is caused when it gets triggered.

Is this a valid hypothesis and is there a method of testing it?