texts: Using output value of column from function for another column in SQL*Loader

texts:
I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here.  Please let me know, if this is inappropriate to do so.

==============================

I am trying to define a SQL*Loader control file where col1 is determined by using a user function which takes a couple of parameters with values from the input file. This part is working fine.

I want to use the value returned by above function as a parameter for another user defined function and that does not work. The value sent to the second function is not the calculated value of the first column.

Is this possble? Can someone give me a sample?

Here is my control file

( col1  BOUNDFILLER         POSITION(1:8),
  col2  INTEGER EXTERNAL    "schema_own.get_col2_val(:col1, :col6)",
  col3              POSITION(29:33) CHAR,
  col4              "sysdate",
  col5              constant " ",
  col6              POSITION(9:11)  CHAR,
  col7              "sysdate",
  col8              POSITION(12:18) CHAR,
  col9              POSITION(19:28) CHAR,
  col10             POSITION(29:33) CHAR,
  col11             POSITION(52:63) CHAR,
  col12             "(col2)", --constant    "TEST",
  col13             "schema_own.get_col13_value(:col1, :col2)",
  LAST_UPDATE_TS            "sysdate",
  LAST_UPDATE_USER_I        constant "SQLLDR"
)

What seems to be happening is that the the second parameter in get_col13_value seems to be containing the FIRST character of the col6 instead of the number that col2 should have from the call to get_col2_val.

I tried col12 with both (col2) and (:col2) - same outcome.

Am I missing something?

Can someone also help me understand when to use col2 vs. :col2 in sql loader?


texts: Oracle 11g XE 64bits installation problem on Windows 7

texts:
I had to install Oracle 11g XE on my laptop under Windows 7.
So I follow this Tutorial. The installation didn't even fail once, and seem to be OK.
But every time i tried to connect to it with SQLPLUS, it failed with ORA-12638 error...

SQL> connect
Enter user-name: system
Enter password:
ERROR:
ORA-12638: Credential retrieval failed

After some search I found this Web Site and attempt to resolve my problem with the second solution. But then I get another kind of error : ORA-01034 and ORA-27101...

SQL> connect
Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

It seem that my database didn't even exist to begin with...
And if looking in [...]\oraclexe\app\oracle\oradata\XE, there is no files.

When looking the logs at [...]\oraclexe\app\oracle\product\11.2.0\server\config\log, I found that during the installation process, the application wasn't even able to connect to the database created.

Example in the file : postDBCreation.log

SQL> connect "SYS"/"&&sysPassword" as SYSDBA
ERROR:
ORA-12638: Credential retrieval failed
[...]
SQL> startup ;
ORA-12638: Credential retrieval failed
SQL> select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
SP2-0640: Not connected
SQL> execute utl_recomp.recomp_serial();
SP2-0640: Not connected

Another file in the same directory : CloneRmanRestore.log

SQL> startup nomount pfile="D:\Applications\oraclexe\app\oracle\product\11.2.0\server\config\scripts\init.ora";
ORA-12638: Credential retrieval failed
SQL> @D:\Applications\oraclexe\app\oracle\product\11.2.0\server\config\scripts\rmanRestoreDatafiles.sql;
SQL> set echo off;
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
ERROR:
ORA-12638: Credential retrieval failed

I am log as a domain user with administration right on the laptop, so why does the installation fail without putting an explicit error ? And how to ensure that the installation of Oracle 11g XE on my laptop work ?


texts: Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL

texts:
I am much more familiar with SQL Server, but I am doing a project now in Oracle. There is a concept from SQL Server that I am not sure how to accomplish in Oracle.

In SQL Server if I create a procedure

CREATE PROCEDURE usp_GetSomeStuff
(
    @start_date DATE
    @end_date DATE
)
AS
BEGIN
SELECT
  Customer
, SUM( WidgetsPurchased ) AS WidgetsPurchased
FROM WidgetSales
WHERE TransactionDate >= @start_date
  AND TransactionDate <= @end_date
GROUP BY
  Customer
END

(Imagine a more complex query is involved). This gives application code a clean interface for 'selecting' this data without getting involved in a bunch of SQL. The application can just run usp_GetSomeStuff '2018-01-01', 2018-12-31' and get the results.

Oracle, on the other hand has a separation between the procedural language and the database language, and this is quite foreign to me.

When you are inside of a PL/SQL block you cannot perform DQL (unless assigning to cursors or variable).

For example, this is not legal:

create or replace procedure get_some_stuff
(
      in_start_date date
    , in_end_date date
)
is
begin
    select
      customer
    , sum( widgets_purchased ) as widgets_purchased
    from widgetsales
    where transactiondate >= in_start_date
      and transactiondate <= in_end_date
    group by
      customer
    ;
end;
/

PLS-00428: an INTO clause is expected in this SELECT statement

What is the 'Oracle Way' way of 'wrapping' a complex query for a caller that wants the results as if the caller had executed the select statement itself?

Note that we could also do this in a table valued function in SQL Server, which is even more convenient sometimes because we can call it as

select
 only
,these
,columns
from GetSomeStuff('2018-01-01', '2018-12-31')
where xyz = 123

With Oracle I just don't 'get it'. What's the pattern for doing this kind of thing in Oracle?

I am using Oracle 12c.


texts: correct security and user setup for SQL Server database

texts:
I am currently on setting a sql server database on a database server. A front end web application running off a web server communicates to the database.

What is the best approach for setting up users to access the database etc.?

My plan was:

On web server, create a local user which the application pool runs under and assign it the necessary rights
On database server, create a SQL user that can log in to the database, have necessary permissions on tables/DBs etc. This user is what is included in the connection string from the MVC web application to connect to the database.

Is this the best approach - I have read in to mixed mode SQL Server with SSPI etc. and I am confused a little as to the best secure setup. Any tips or further information would be great.

I have read that SSPI is a means of providing an integrated secure trust relationship between the web servers and the back end database servers, without having to transmit sensitive user and password information. Buy I am wondering if what I have outlined sufficent. Note sensitive information such as SSN, credit card numbers etc may be stored in the SQL Server 2012 database.


texts: On-prem Availability Group secondary replica for Azure SQL Managed Instance

texts:
I am investigating migrating an on-premises SQL Server availability group to an Azure SQL Managed Instance (2 databases, 600 tables, 3TB, of which 2TB is blobs).

A significant amount of on-premises reporting occurs that would be costly to change, and pulls large portions of the database on a nightly basis.

I would like to be able to add an on-premises read-only secondary to the Managed Instance's availability group. This would prevent existing reporting from flooding our ExpressRoute and costing a lot of money in data transfer.

I know Azure Data Sync (preview) exists, but the simplicity of just-another-secondary allures me. I know I could do this manually with IaaS options, but am looking to avoid the operational overheads. Does anyone have good news for me?

Update 22/6/2018:

I took this question to my local Microsoft cloud architects, and they agree that an on-prem replica of a Managed Instance availability group isn't possible at the moment. The docs explicitly state that a Managed Instance can't be the publisher in a transactional replication setup either. For now our design will do reporting over the ExpressRoute.

It's worth noting that the MS chaps thought I was overly concerned about moving data over the ExpressRoute, citing larger clients that move around 5TB / week, every week, without a problem (we can get a 10Gbps ExpressRoute if need be).


texts: How MySQL Queries and Questions status variables are calculated?

texts:
I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions.

 show global status like 'Queries%';
| Queries                 | 28058738 |

mysql> show global status like 'Questions%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Questions     | 9458886 |
+---------------+---------+

The server is executing so many preparing statements and as per MySQL manual I got an assumption that Questions will contain Com_stmt_execute count. So I found my counts as,

mysql> show global status like 'Com_stmt_execute';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Com_stmt_execute | 9356066 |
+------------------+---------+

So what are the other counters which dominates to the total Questions status value?

Please see my Select and Query cache hits and I have very less write on this server.

mysql> show global status like '%Com_select%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 2720855 |
+---------------+---------+

mysql> show global status like 'Qcache_hits';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Qcache_hits   | 2316541 |
+---------------+---------+

Also the mysqladmin status show a reverse status if we consider the variable names.

$ mysqladmin status
 Questions: 28079556


texts: Is dbo the default schema when querying if it is not specified?

texts:
If I query a table which exists in two different schemas but has the same name what determines which table is used?

For example I have table Bar which exists in both dbo and foo, if I execute the statement below which one is returned:

SELECT * FROM Bar

I thought this was determined by the users default schema but doesn't appear to be the case.

I ask because I have found queries written by someone else who is not specifying the schema name yet the database being queried has the same tables in different schemas for ETL.


texts: Can an AlwaysOn Availability Group be used to mitigate downtime when there's replication syncing issues on the primary server?

texts:
Once in a while I'll run into syncing issues with my subscriber server in a transactional replication setup.
Taking a new snapshot of the publisher causes significant downtime on my subscriber server.

If my subscriber server was part of an AlwaysOn AG as the primary, could I failover to the secondary server while the primary is being synced up to a new snapshot on the publisher server, and mitigate the downtime? (Then I can switch back to the primary once it's finished syncing with the publisher server?)

E.g. Server Z is my publisher in the transactional replication, Server A is my subscriber. Then I create an AlwaysOn AG on Server A and a new server called Server B. If Server A falls out of sync with Server Z and I need to take a new snapshot on Server Z to re-sync Server A to it, can I failover to Server B in the meantime?


texts: Does the 'ORDER BY' clause of a subquery pass through to the main results (POSTGRES)

texts:
I've got a query written something like this.....

with subquery as
(
  SELECT A, B, C, D,
  row_number() OVER(PARTITION BY X ORDER BY Y) as RN
  FROM blah blah
  ORDER BY A,B
  LIMIT 100
)
SELECT C,D
FROM subquery
WHERE RN=1

As you can see, the subquery is sorted, but the top-level query isn't.

The top level query doesn't do anything complicated, it just selects based on the partition by clause.

Will the sort order from the subquery be maintained in the final results?

Observation of the various data-sets I've tested against indicates the sort order IS maintained, but I'm not sure if this is just a lucky coincidence or not.


texts: How safe is to use BACKUP LOG WITH TRUNCATE_ONLY

texts:
As per the title, how much is it safe to use BACKUP LOG db_name WITH TRUNCATE_ONLY in last STEP of any SQL-job execution? Randomly found on google that this Code line can be very harmful in use. But how?

I am using my code as below in one of my SQL-job's last STEP.

BACKUP LOG  my_database with Truncate_Only
DBCC SHRINKFILE (N'my_database_Data')
DBCC SHRINKFILE (N'my_database_Log')

Let me simplify the question with below aspects:

What actually doing this code in the SQL-Job? Does it really help?
Is there anything related performance of the DB-Server?
If I remove first line of code (BACKUP LOG), how it can get effect?
Currently I am not able see SQL-Job history of same SQL-Job, is this the reason that it clears all logs?

This could be opinion based question but still let's discuss if you can share some Ideas on it.


texts: Optimize MySQL Configuration - MySQL crash and after restart works

texts:
My WAMP MySQL has crashed 2 times the last week and I really wonder why! Once I restart the server everything works fine! Below I attach the error log of MySQL and the my.ini file! Can anyone suggest anything??

MySQL Error LOG:

2018-02-23 21:45:52 2000 [Note] Plugin 'FEDERATED' is disabled.
2018-02-23 21:45:52 2000 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-23 21:45:52 2000 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2018-02-23 21:45:52 2000 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-02-23 21:45:52 2000 [Note] InnoDB: Not using CPU crc32 instructions
2018-02-23 21:45:53 2000 [Note] InnoDB: Initializing buffer pool, size = 67.0G
2018-02-23 21:45:55 2000 [Note] InnoDB: Completed initialization of buffer pool
2018-02-23 21:45:55 2000 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-23 21:47:37 2000 [Note] InnoDB: 128 rollback segment(s) are active.
2018-02-23 21:47:37 2000 [Note] InnoDB: Waiting for purge to start
2018-02-23 21:47:38 2000 [Note] InnoDB: 5.6.12 started; log sequence number 321531472808
2018-02-23 21:48:16 2000 [Note] Server hostname (bind-address): '*'; port: 3306
2018-02-23 21:48:16 2000 [Note] IPv6 is available.
2018-02-23 21:48:16 2000 [Note]   - '::' resolves to '::';
2018-02-23 21:48:16 2000 [Note] Server socket created on IP: '::'.
2018-02-23 21:48:18 2000 [Note] Event Scheduler: Loaded 0 events
2018-02-23 21:48:18 2000 [Note] wampmysqld: ready for connections.
Version: '5.6.12-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

My.ini Configuration File:

[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
key_buffer = 64M
max_allowed_packet = 1000M

sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.6.12
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.6.12/data
#innodb_thread_concurrency = 8
innodb_read_io_threads = 12
innodb_buffer_pool_size = 67G
max_connections = 4000
max_user_connections = 0

 # Disable Federated by default
skip-federated

[mysqldump]
 quick
max_allowed_packet = 1000M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

 [mysqld]
 port=3306
 table_definition_cache = 800

My Server Details:

  RAM: 128GB RAM CPU: Intel XEON 3,5GHZ HD: 2TB HDD

Any recommendation??


texts: View operation timestamps for long running operations in PSQL console

texts:
When I do long running queries, I tend to use tmux and a psql console. It will not show me any output of when some operation was started or completed.
How can I get the psql console to show some timestamps?

Clarification:
I'm hoping for a non-scripting solution, just in the psql console. (Or does nobody use that console?) I'm maybe naive but still trying to do most of the database stuff using the database itself and its tools (using procedures, etc.)


texts: how to calculate difference of first row and last row fields value in each group


texts:
I've seen Aaron Bertrand's extensive post, and I've asked on Twitter #sqlhelp, but have not gotten an answer for what this error with state 43 means:

Error: 18456, Severity: 14, State: 43
Login failed for user 'Domain\svcacct'.
Reason: Failed to send an environment change notification to a
log shipping partner node. [CLIENT: #.#.#.#]

This is a SQL 2016 Standard set up, with 18 Basic Availability Groups.

This Login is valid, except for about 30 minutes last Tuesday when all sorts of weirdness happened.  I wish I could define weirdness better, but I am brand new to the environment and still leaning names :)


texts:
Background:  SQL Server Version 2014 and the design is an invoice order system.

I have a few questions to confirm my thoughts on SQL Server concurrency.  As a brief background, the developer who created this model, built every order inventory table (items that are part of an order) and order table (the orders) with an InUse field.  What happens is that when a sales person tries to update an order, by changing the name of an item ordered (say a book named Harry Poter needed to be renamed to Harry Potter), if someone else is reading that data, they can't access the object.

The actual database design works like this: the SalesOrder table would have a SalesPersonId with an InUse column set to 1 where no one could access that OrderId.  This means that for every read, update, insert, or delete, there are three transactions happening: first the update to set InUse to 1 so that no one could access the specific OrderId, then the actual transaction, then a final update to set InUse to 0 to allow people to access that OrderId.  That means multiple reads and writes rather than fewer - hello performance nightmare as it scales!

The developer explained that this solves concurrency issues if two users try to write to, or update items in an order.  Huh?

From my knowledge of SQL Server, if two users try to write to the same order - like add two new items, they will both be added because these will be inserts in the log as metadata and then will be added on disk.  In addition, if the inserts are exactly the same, we should design tables with unique constraints to prevent this (to be fair, this developer uses no constraints and no foreign keys at all), so the second insert wouldn't happen.  This means that two users can write inserts to the same table and generally inserts occur quickly.

In addition, if two users are updating the same record - say an OrderItemId of 1 with the name of "Harry Poter" to update to "Harry Potter" (which is an item of a specific OrderId), even two different updates will occur, but the second won't matter because it's the same update.  Only when two separate updates to the same object occurs that are different like update one being "Harry Potter" and update two being "Hary Potter", would there be an issue because these differ.  However, not allowing user access to objects doesn't solve this problem (his design), as user one's update may be the wrong update, which locks out user two (or vice versa).

I don't get this design; to me, it reflects a complete misunderstanding of SQL Server concurrency, along with how SQL databases handle transactions, and most of what he's created is already handled on the back-end by SQL Server (in addition to not solving the problem of wrong updates).  However, maybe I'm missing something and someone else can enlighten me that this is a standard practice with SQL Server (I've never seen this design before).


texts: How to change the mount point for a column in postgresql table?

texts:
This is my scenario

I have a table t with following columns

id  int
link  varchar(500)
name  varchar(20)
sal   text

In the link column we will store the location for the pdf files(mount point)

Ex: link  /ABC/ABC_DATA/Mydata/Test/a1.pdf

there will be one link for every id

Application Users have changed their pdf file location from "/ABC/ABC_DATA/Test/a1.pdf" to "/CBF/CBF_DATA/Documents/Test_DATA/a1.pdf"

Now how can i update the table with new location for all the rows. Table contains nearly 10,000 rows

Note:id and link are related every id contains unique link.


texts: AlwaysOn - Running SQL Agent Jobs on Secondary read-only node writing audit information to database

texts:
I am setting up an AlwaysOn Availability Group with 3 nodes. 1 Primary, 1 Secondary Read-only node and 1 secondary non-readable node.
I want to offload my maintenance jobs to my secondary read-only node which is fine. I can do this by adding a step to all my maintenance jobs to check the node. If it's the Primary node, the job will stop before moving on to the next step.

I also have a maintenance database where I record all my maintenance job information such as failures, start times, end time, durations, index/statistic names etc etc. I want to keep this database within my AG group rather than have a different maintenance DB on each node.
So my question is..

IF my maintenance jobs are running on my secondary read-only node and I need this to record audit information in a maintenance database within the AG Group, will this automatically write the data to the Primary Node DB or will it try and write this to the Secondary Node database. If it tries to write to the secondary node will it fail? If it does, is there a good workaround that doesn't involve having a separate maintenanceDB on each node or keeping my maintenanceDB within it's own separate Availability Group?


texts:
I've installed Postgresql 11 and pgAdmin4 (version 4.5) and while I'm able to create tables in a new database, if I attempt to SELECT or INSERT into those tables I always get a message 'Not connected to the server or the connection to the server has been closed.' in the "Messages" tab in pgAdmin 4.

If, however, I open the SQL Shell I can execute a SELECT on a table that I've attempted to insert into from pgAdmin and it shows that the rows have been inserted.


texts: How to work around Sequelize not supporting composite foreign keys

texts:
I am developing an advanced query with Sequelize (hitting a PostgreSQL database). It's not difficult in pure SQL, but I can't get it work in Sequelize, in part because they don't support composite foreign keys.

The best way to describe my problem is with code, so I made a repository with a minimal reproduction of my problem. You should be able to clone it and follow the instructions in the README to reproduce the problem locally. Basically, if you can get the query to run without errors and correctly return an empty array of rows, you've solved my problem :)

For convenience I will copy here the query and output themselves, but you can check the repo to look at the migrations, models and Sequelize configuration.

The Query

const performQuery = (db) => {
  const { usersRoles, invitations } = db; // eslint-disable-line max-len

  const sql = {
    include: {
      model: invitations,
      on: {
        user: Sequelize.where(
          Sequelize.col('usersRoles.userUid'),
          '=',
          Sequelize.col('invitations.guestUid')),
        propertyManager: Sequelize.where(
          Sequelize.col('usersRoles.propertyManagerUid'),
          '=',
          Sequelize.col('invitations.propertyManagerUid')),
        tenant: Sequelize.where(
          Sequelize.col('usersRoles.tenantUid'),
          '=',
          Sequelize.col('invitations.tenantUid')),
      },
    },
  };
  return usersRoles.findAll(sql);
};

Output (truncated)

TypeError: Cannot read property 'toUpperCase' of undefined
    at str.trim.replace (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:108:60)
    at RegExp.[Symbol.replace] (native)
    at RegExp.[Symbol.replace] (native)
    at String.replace (native)
    at camelize (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:108:21)
    at Object.camelizeIf (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:32:14)
    at new HasMany (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/associations/has-many.js:67:31)
    at Function.hasMany (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/associations/mixin.js:26:25)
    at Function.usersRoles.associate (/home/shawn/git/waltzapp/sequelize-troubleshooting/sequelize/models/usersRoles.js:71:23)
    at Object.keys.forEach (/home/shawn/git/waltzapp/sequelize-troubleshooting/index.js:62:29)
    at Array.forEach (native)
    at Promise.map.then (/home/shawn/git/waltzapp/sequelize-troubleshooting/index.js:60:27)


texts: Stored procedure and prepared statements

texts:
I have 2 stored procedures:

CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count
  FROM foodmaster d inner join user_details u
  on d.user_id=u.user_id WHERE (', param_CRITERIA ,') group by u.user_id;');
PREPARE stmt FROM @QUERY1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END IF;

CREATE PROCEDURE `sample`(param_CRITERIA VARCHAR(500))
BEGIN
SELECT user_name,count(*) as count FROM foodmaster d
  inner join user_details u on d.user_id=u.user_id
  WHERE ( param_CRITERIA ) group by u.user_id;
END;

I tried with same param_criteria in both SPs. In the first procedure, i am getting results. But not in second. Why?


texts:
Please consider the following stored procedure I have:

ORIGINAL Stored Procedure:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `myProc`()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING

BEGIN
  -- ERROR
ROLLBACK;

END;

START TRANSACTION;

INSERT INTO mydb.mytable(some values)

SELECT (some values)

FROM   mydb.mytable

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 60;

DELETE FROM   mydb.mytable

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 60;

COMMIT;

END

Since m_dt datatype is datetime, I want to modify my BETWEEN condition. I want to add 00:00:00  before the AND and 23:59:59 after the AND condition. For example something like the following:

WHERE m_dt BETWEEN  CURDATE()  - INTERVAL 60 DAY 00:00:00 AND CURDATE() - INTERVAL 60 23:59:59;

By any chance it is possible in MySQL?


texts: Why do the MySQL bin log files still exist after a purge or flush?

texts:
I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files:

mysql-bin.000025
mysql-bin.000024
mysql-bin.000023
mysql-bin.000022
mysql-bin.000021
mysql-bin.000020
mysql-bin.000019
mysql-bin.index

Is there a reason why using the commands are not working? These files are taking a lot of space. I would like to get rid of them safely.


texts:
I've got a bunch of ids from table messages and I want to update the table activities. This is how I imagined my query would work (so you get better idea what I'm trying to accomplish):

update activities
set activities.created_at = messages.created_at,
set activities.updated_at = messages.updated_at
from messages
where messages.id in (1,2,3)
activities.trackable_id in (1,2,3)

In activities, the trackable_id is foreign key to the messages table. So I need to set the right created_at and updated_at time in activities table for some messages.

So basically the activity table is the copy of messages table but only in another format.

How can I update created_at and updated_at in the activities table for some message ids, but using the data from messages table (data being updated_at and created_at)?


texts: mySQL Data loading: Better to column map or INSERT SELECT INTO ETL?

texts:
I've got 125 csv files to upload into a database. The files are typically ~200K rows and 30 columns.

In order to make the data more relational I was going to column map during LOAD DATA INFILE process (specific columns into specific tables) via bash script. But now I'm wondering if it makes more sense to LOAD all the files and use INSERT SELECT INTO to do my ETL'ing.

Any recommendations?


texts: Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

texts:
As an example, let's say I have a fact table with two dimensions and one measure

FactMoney table

ProjectKey int

PersonKey int

CashAmount money

The two dimensions are defined like this:

DimProject (a type 0 dimension - i.e. static)

ProjectKey int

ProjectName varchar(50)

DimPerson (a type 2 slowly changing dimension)

PersonKey int

PersonNaturalKey int

PersonName varchar(50)

EffectiveStartDate datetime

EffectiveEndDate datetime

IsCurrent bit

Pretty straightforward so far. Now I'll introduce a Person Category concept.

DimCategory

CategoryKey int

CategoryName varchar(50)

And build an M2M relationship between DimPerson and DimCategory

BridgePersonCategory

PersonKey int

CategoryKey int

So - people can have 1..n categories.

My problem is - as Person is a slowly changing dimension, when a person's name changes, we add a new person row and update our effective dates and is current flags, no big deal.

But what do we do with the person's categories? Do we need to add more rows to the bridge table every time a new person version pops up?

And as a corollary, if a person's categories change, does that mean we need to create a new row in the person table?

(please note I've also asked this question at stack-O and as directed by the first commenter, now posted this question here.)


texts: How does a memcached bucket behave when fully loaded?

texts:
What is the default ttl of a key in memcached bucket?

Also, what happens when a bucket is full?

I created a 256MB RAM Bucket and tried to overload it by sending infinite set queries till error is thrown.
But to my surprise, it keep accepting inputs, but total items now fixed to 2.8M with 204M RAM used.

Meanwhile, when I tried to fetch the first key it says key not found. Moreover, I keep calling a specific key infinitely it doesnt get expired.

So how is it exactly working? Please guide.


texts: Generically get all related records from SQL Server Database based off of Primary Key of one table

texts:
In Sql Server it is possible to visually see all relations a table has by creating a diagram of one table and right clicking and selecting 'Add Related Tables.'

Is this also possible to do for one record of the table? For example, I have table A and B are related by foreign key ab and I have table B and C are connected by FK bc. With only knowing the fk of one record from A can I get all related ab relations and bc relations generically and without knowing that the FK bc exists ahead of time?

My reason for doing this is we want to be able to pull a singular set of data from our production database to our development database so that we can debug on live data without risking changing live data. Please ask questions if this is not clear.


texts:
I am looking to update several users on READ_ONLY databases.
As the databases are used 24/7 globally, and maintenance windows have just past, I would like to do this during a low-activity moment (to be able to adhere to deadlines).

I'm currently considering using:

USE MASTER
GO
ALTER DATABASE SQL SET READ_WRITE
GO
USE SQL
GO
EXEC sp_addrolemember N'db_datareader', N'USER'
GO
USE MASTER
GO
ALTER DATABASE SQL SET READ_ONLY
GO

However I want to:

Guarantee this doesn't hang indefinitely
Be sure nothing can be written to the database during this action

As I can't put these commands in a transaction, and I don't have an application server handy on the DEV or STG environments to test the changes beforehand, is there any documentation about changing read_only states for single actions? (I did test the T-SQL statements, and they work)

Also, as this requires an exclusive lock on the database, am I even likely to get my statement through?

I'm inclined to just put forward that this will have to wait until the next maintenance window, period. But I'm hoping someone here can prove me wrong.


texts: Why null is getting compared with null in the join condition in SQL Server 2016?

texts:
It's a simple self join I am trying to do on below data

and the O/P I am getting after running following query (This is not the complete output, just showing the null part of it)

select a.col1, a.col2, a.Length1, b.*
from [dbo].[Combine] a
  left join [dbo].[Combine] b on a.col1 =b.col1

Why Null is getting compared and coming 9 times?


texts: Postgresql two tablespaces one on SSD and one on SATA III HD

texts: Continuing my previous question PostgreSQL maximize performance SSD the DBA SE community has given me efficient advice on optimal settings for PostgreSQL on SSD. The problem is that I will define two separate tablespaces, the first one is already on a SATA III Seagate Baracuda and one that I will set on the SSD (once the SSD arrives from the store). In that case, postgresql.conf should have two separate configurations (one for the SSD and one for the HDD). Is there any way to make settings separate per tablespace or per database, without having to restart the server and reload configurations? I am benchmarking for scientific research and basically I want to set two identical databases on the two tablespaces and compare query read performance between the SSD and HDD for my methods.


texts: Multilingual Article/ Category database design

texts:
I wasn't getting any answers over at Programmers and was advised to go over here instead...

I wanted to make a design for a multilingual database and after having drawn it up have arrived at 5 tables, some of them very short.

The idea is that is used for an 'About Us' sort of area and consists of articles sorted into categories, with tracking for changes.

Categories
    id              # Unique ID
    parent_id       # To create heirarchy in categories.

Category_Language
    id              # Unique ID
    category_key    # Link to Category table
    language        # ISO Language code
    name            # Name of category in that language

Article
    id              # Unique ID
    category_key    # foreign key to link to the category.

Article_Language
    id              # Unique ID
    article_key     # foreign key to link to the article.
    name            # name of the article in that language.
    content         # html content in that language.

Article_Revisions
    id              # Unique ID
    article_key     # Foreign key to link to article_language
    content         # markup of content in language.
    date_created    # date created
    revision_number # increments for every change.
    revision_user   # who changed it?
    revision_date   # when was it changed?
    size            # size of the revision

So now I'm wondering, could I have done this simpler? Would it be better to create a big table than two small ones from a computational perspective?

Categories with an additional language column and skip Category_Language table?
Article with an additional language column and skip Article_Language table?

I could even have squished together the list of articles and categories into one table with languages and type column to query which one I want, but that does not seem efficient and hardly pedagogical.


texts: question about using tokuDB or InnoDB for financial data

texts:
we want to use Percona xtraDBcluster for clustering 4 nodes (Master-Master) and we have a finance project which should have a rather large database. The number of tables is not more than 40, but we have about 2 tables which store financial transaction data and the estimated amount of data in these tables is going to grow at 350,000 records per day, and we should keep them at least 10 years to be able to do various reports.

Most of the operation (98%) in these tables is Insert/read but we can separate updatable fields in a separate table.

My questions are the following:

Should I use a tokuDB storage engine for such large amounts of data or InnoDB?
What is the best solution for such large and sensitive database?

Best Regards

Ali


texts:
I have this query:

    SELECT TOP (20) table1.Vit_Codigo
    ,table1.Vit_Nome
    ,table1.Vit_Apelido
    ,table1.Vit_codigoStatus
    ,table1.Vit_Numero
    ,table1.Vit_Ano
    ,table1.Vit_CodigoTiposExames
    ,table3.Esp_Setor
    ,table2.TEs_Descricao
FROM table1
LEFT JOIN table2 ON tb_Vitima.Vit_CodigoTiposExames = table2.TEs_Codigo
LEFT JOIN table3 ON table3 .Esp_Codigo = table1.Vit_CodigoEspecialidade
WHERE (rtrim(ltrim(lower(tb_Vitima.Vit_Rg))) = 'n/a')
    AND (
        table1.Vit_codigoStatus IN (
            3
            ,4
            )
        )
ORDER BY table1.Vit_DataAberturaReal DESC

And these plans:

I made this index:

create index IX_SELECTTOP20_INCLUDE
    on table1
    ( Vit_Rg ,Vit_codigoStatus )
include (
Vit_Codigo , Vit_Nome , Vit_Apelido , Vit_Numero ,
               Vit_Ano , Vit_CodigoTiposExames )

...but it seems the code is using the old index.

So, Why is my index still using key lookup?


texts:
I'm building a lot of PL/pgSQL functions and a debugger would really come in handy because RAISE NOTICE is not enough.

I read about a pgAdmin plugin you can install but it was only on Windows and I'm running Mac OS X.


texts: Issue with diagrams in sql management studio 2014

texts:
I have this issue with sql management studio 2014.

When creating a new database diagram and when I add existing table to a new diagram, the data inside a table is obsolete. I tried both dragging a table from list of tables and refreshing a table listing when using "Add table" function, but the result is the same.
I have noticed this when I expand column list in object explorer, the actual columns are different then the columns of same table inside a diagram.

Did anyone experience the same problem, and how do I avoid this issue since it is very annoying and caused me a lot of unnecessary problems? If nothing else, I will have to try with a new version.


texts: SSMS Registered Servers with ReadOnly Application Intent

texts:
We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. This way they don't have to type in the alias name every time they need to access the ReadOnly replica.

Unfortunately there is no option within registered servers to add the ApplicationIntent Option unlike the regular object explorer.

I came across this article from Microsoft about changing the connection string in RegSrvr.xml.

https://connect.microsoft.com/SQLServer/feedback/details/786323/ssms-sql-server-management-studio-2012-missing-connection-properties-for-availability-groups

I tried their suggestion and it did not connect to the right replica node when connecting via the local server in the registered servers.

The ReadOnly option works fine from object explorer when using the options in the connect window > Additional Connection Parameters. But it does not save the changes made to the connection.

Does anyone know of any alternate solutions on saving the configurations with ReadOnly Intent property with SSMS? Thanks in advance for your help.


texts: "Binlog has bad magic number" - recoverying InnoDB tables

texts:
A system crash raised the error in the title.

I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution probably messes with table index.

I was going to comment there to avoid duplicate, but I need 50 reputation for that.

I have a backup. So, how can I restore all InnoDB tables if I can't start mysql, dump and import?


texts: Which first: upgrade mysql version or convert storage engine?

texts:
I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets.

I am planning on:

upgrading to 5.5
converting existing databases to innodb

I will only do one at a time:

upgrade --> wait --> convert

or

convert --> wait --> upgrade

What is best to do first?

Upgrade to 5.5
Convert to InnoDB?


texts: No results for match...against in boolean mode search with fulltext index (MySQL)

texts:
We have a column with a fulltext index, and are matching 'asp.net' against the column in boolean mode. We know there are records where this column contains the value 'asp.net', but the query is returning no records.

We have not modified any MySQL configuration directives pertaining to fulltext indexes. I believe the default minimum word length for fulltext indexes is 3 or 4 (certainly less than 7), so this query should still return at least one record.

Is there something else we're missing? Any help is appreciated!


texts: PostgreSQL : change the type of a domain

texts:
I have a PostgreSQL DOMAIN "mydomain" based on NUMERIC(8,2). I need to change it to be based on NUMERIC (12, 2). That DOMAIN is used by about 50 tables.
ALTER DOMAIN doesn't allow to modify the domain's base type. It would be great if we could simply do something like ALTER DOMAIN domain SET TYPE new_type. Alas, it's not supported...
What I'm about to do :

Create a new domain "mydomain2", based on NUMERIC(12,2)
ALTER the columns of the tables using mydomain, and set the type of those columns to mydomain2
Drop mydomain
Rename mydomain2 to mydomain

I see no reason this won't work, but it's a bit cumbersome (I'll have to ALTER about 50 tables).
Is there a faster way to do this?


texts: Restoring Database to a smaller test envrionment

texts:
I don't believe this is possible but wanted to check.

I've got a bi-weekly job running to restore the full backup to a test environment so the devs have something fresh to destroy without damaging the actual environment. The problem is cropping up that the test environment has less space than the production environment (it's stuck on one drive instead of a number of drives).

When copied across the database is now using over 95% of the hard disk allocation that it has. Is there any way along side the Restore Database WITH MOVE to resize those files? There is free space in a number of the files that could be shrunk and the file size is never going to change from the backup as it doesn't have data getting thrown at it so technically they can get cut all the way back to their data size.

The alternative, which I don't really want to do, is shrink the files on the production environment that have a bit too much space available at the minute.

Any tips or ideas of anything to do? Adding a new hard disk unfortunately isn't an option as the drive bay attached is full.

Ste


texts: Remove "almost" duplicate rows (may or may not start with plus sign)

texts:
To remove duplicated rows from Oracle, I tend to use the following:
DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

(taken from SO)
However, I'd like to remove the same rows in a slightly different scenario. Image there is a table with rows: name, surname. Duplicate row is when

name is the same, and
surname is the same, or starts with + (plus sign) and then is the same.

For example, the following is to be considered the same:

name: John, surname Doe
name: John, surname +Doe

How do I remove those duplicated rows?


texts: Does 'update global indexes' not work if the index is already unusable?

texts:
I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with

ALTER TABLE tbl1 TRUNCATE PARTITION p1;

the global index has the status UNUSABLE.

If I use

ALTER TABLE tbl1 TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;

the global index is correctly rebuild and has the status VALID.

But:
It seems, that the index is not rebuild if it already is in an unsuable state. Is that correct?

To verify:

Create a table with 3 range partitions and a global index
Insert data into all 3 partitions
truncate partition WITHOUT updating global indexes
index is in state unusable
truncate a second partition WITH updating global indexes
index is still unusable

From my understanding, after the second truncate partition, the index should be usable again.


texts: Filter Stored Procedure Result (Informix)

texts:
IBM Informix Dynamic Server Version 11.50.UC5XA.
I have a Informix database. This database contain a stored procedure with name of 'sp_agent_details' that get two DateTime Parameter. This stored procedure results about 27 columns and I want only few columns of it for reporting. I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from
       TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))

This Result such Error:

java.sql.SQLException: Illegal SQL statement in SPL routine.

Next I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from
       TABLE(MULTISET{sp_agent_call_summary
    ('2014-02-04 04:00:00', '2014-02-04 23:00:00')})

Result:

 java.sql.SQLException: Function (informix.sp_agent_call_summary)
returns too many values.

then I tried this:

select AGENT_NAME, AGENT_LOGIN_ID from
   TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))
   (AGENT_NAME, AGENT_LOGIN_ID, other columns that are returned by SP)

Result:

java.sql.SQLException: Illegal SQL statement in SPL routine.


texts: SQL Server Impersonation is just NOT working

texts:
I simply would like a very limited user, in its own database, to call an update procedure on our database to tell us that it has updated a table, which it has access to.

I have allowed the user login ("Doc") the "Impersonate any login" permission.  Doc also has impersonate rights on the "App" login, which exists despite what the error messages seem to say.

And I inserted the following statement near the beginning of the proc

EXECUTE AS LOGIN = 'App';

"App" is in the db_owner of the database that I want to update. I was running into an issue with the WITH EXECUTE AS 'App' permission on the proc I was using, because our canned audit procedure calls procedures in another database, and I had read that such a permission was confined to the current database.

But for some reason when I try the EXECUTE AS LOGIN statement it tells me that this very much used login cannot be used.

Cannot execute as the server principal because the principal "App" does not exist,
this type of principal cannot be impersonated, or you do not have permission.

So, if this is telling me right,

logins aren't the type of principal that can be impersonated, or
despite the check boxes on the UI, "Doc" does not have the right to impersonate the user "App", or
The login that my application uses 700,00 times a day, "does not exist".

Or this is just broken.

I'd prefer to give "sudo" type rights to limited users, but if I have to, I'll start traversing through the tree giving every single right the "limited" user needs for 24-hour access to table modifications which are kind of like the updates I want him to make. Despite that all I want him to do is call a proc that I created, update a particular column in a table in the Application database and log the result if it goes sideways.

UPDATE: Base on what srutzky says below, I tried turning Trustworthy on in a restricted database with a new user and a new login and the rights to impersonate, and it worked. I guess I have to weigh the risk between marking a database as trustworthy and creating users in every database that login will touch.


texts: SQL to combine records only if data is available

texts:
I have a table with the following format:

id      description1       description2
123     null               abcd
123     zyxw               abcd
124     null               efgh
124     vuts               efgh
125     null               ijkl
126     null               mnop

Basically, I want SQL to return a single record for 123 and 124 that contains description1, instead of displaying two records for each, one with a null description1. But if there is no description1 in any record of the same id and description2, simply display null. So that the data displayed would be:

id    description1    description2
123   zyxw            abcd
124   vuts            efgh
125   null            ijkl
126   null            mnop


texts: Importing a .bacpac from SQL Azure into a local SQL Server 2016 server gives errors

texts:
I'm trying to import a .bacpac created from SQL Azure into a SQL Server 2016 server that has no existing databases beforehand. It will run but run into issues such as:

  Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data
  plan execution failed with message One or more errors occurred. --->
  System.AggregateException: One or more errors occurred. --->
  System.AggregateException: One or more errors occurred. --->
  System.AggregateException: One or more errors occurred. --->
  System.Data.SqlClient.SqlException: Violation of PRIMARY KEY
  constraint 'PK_ITIssue'. Cannot insert duplicate key in object
  'dbo.ITIssue'.

I have verified the primary key is unique on the source database. It will also throw errors for some foreign key relationships. It stops importing the table it's on and continues to the next one.


texts: Would SQL Server in a Cluster failover if it is running out of RAM

texts:
I have a SQL Server 2008 R2 in a Cluster in an active-passive mode.
The server is running out of RAM (both physical and pagefile).
In the instance level settings there is not Max RAM set (set to the maximum number).

Would the instance failover?


texts: SQL Server 2005 collation issue during installation

texts:
Every time I try to install SQL Server 2005 on to a Windows 7 machine it installs properly but the collation installed is sql_latin1_general_as instead of sql_latin1_general_cp1_ci_as. I have read somewhere that this might be due to the fact that the regional setting for that machine is set to English - United Kingdom, so I double checked it to make sure that regional settings is set to English - United States. Still, after the installation I checked the server properties from Management Studio and saw the collation installed is sql_latin1_general_ci_as instead of sql_latin1_general_cp1_ci_as.

What am I doing wrong here? Are there any other diagnostics steps to find out why this is happening?


texts: How to find reason for rollback during rollback works in Oracle?

texts:
Working on Oracle 12.c, I have a situation, that a big transaction performs a rollback, which takes several hours. I see the status of the session e.g. in V$SESSION.MESSAGE "Transaction Rollback: xid:0x0011.017.001a889d : 862496 out of 1640941 Blocks done".
However, I would like to know the reason (SQL-Errorcode) before the rollback is finished to be able to work on the solution.
Right now, I get the errorcode as result after rollback has finished.
Is there a chance to see it in advance?


texts: Which isolation levels can cause a serialization failure?

texts:
The quote below from Wikipedia (here) says that READ COMMITTED doesn't cause serialization failure! Can you please explain the reason? Which isolation levels cause serialization failure?

Under multiversion concurrency control, at the SERIALIZABLE isolation
level, both SELECT queries see a snapshot of the database taken at the
start of Transaction 1. Therefore, they return the same data. However,
if Transaction 2 then attempted to UPDATE that row as well, a
serialization failure would occur and Transaction 1 would be forced to
roll back.
At the READ COMMITTED isolation level, each query sees a snapshot of
the database taken at the start of each query. Therefore, they each
see different data for the updated row. No serialization failure is
possible in this mode (because no promise of serializability is made),
and Transaction 1 will not have to be retried.


texts:
For a particular customer, I have data returned by a query that looks like this:

OrderDate   OrderCount
2020-01-12  0
2020-01-13  17
2020-01-14  22
2020-01-15  21
2020-01-16  30
2020-01-17  12
2020-01-18  0
2020-01-20  0
2020-01-21  49
2020-01-22  28
2020-01-23  4

These are the number of orders for each business day for that customer.  How do I efficiently determine their current "Streak" of orders?  For instance, if today was 2020-01-24, the answer would be 3, since they have ordered 3 days without missing a day.

Note that the days may not always be consecutive.  There are some non-business days that aren't returned by the query (ie 2020-01-19 in example).

Can anyone tell me how to do this?  Thanks in advance!


texts: Multi language full text search using postgresql

texts:
I am trying to implement full text search using postgresql for some images. I am storing some information about my images into a json field in my table. This json has a tags key where I have multiple languages, each one with tags (keywords), something like this:

"tags": {
    "en": ["blue female", "red female"],
    "es": ["hembra azul", "hembra roja"]
}

At this moment I don't have a clear idea how to store the tsvector considering that I have more languages.

One initial idea was to concatenate all those tsvectors into a single one and store it on a column in my table.

The second idea would be to make a different column for each language and store the corespondent vector into that column.

Which on would be the better one?
There is maybe another better approach?


texts: Hi why do we use Database as a Service such as Mlab instead of a local db that resides on the same computer / building as our codes, wouldn't a remote db slows down request and response?


texts:
SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON?

The following query:

SET STATISTICS IO ON;

SELECT  *
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
        JOIN Production.Product AS p ON sod.ProductID = p.ProductID;

Generates this plan:

And this STATISTICS IO output:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 3, read-ahead reads 1277, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 1, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 1, read-ahead reads 14, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, I reiterate: what gives? Is there a meaningful ordering to STATISTICS IO output or is some arbitrary order used?


texts: Errors with trying to run a stored procedure with local file output

texts:
I'm trying to run a stored procedure that I'm passing some variables to.

The output is being writted to a CSV file on a local file share.

The CSV file gets created, but I'm hitting some errors that I'm not sure how to resolve.  Any help would be appreciated.

SQL

DECLARE @tmp1 DATETIME
SET @tmp1 = DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)

DECLARE @tmp2 DATETIME
SET @tmp2 = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

DECLARE @fac_ids NVARCHAR(500)
SET @fac_ids = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,30,32,33,35,36,37,40,41,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,79,80,81,83,84,85,86,87,89,90,91,92,93,94,95,97,98,135,39,29,31,34'

DECLARE @start_date DATETIME
SET @start_date = @tmp1

DECLARE @end_date DATETIME
SET @end_date = @tmp2

DECLARE @sql VARCHAR(max)
SET @sql = 'Exec [dbo].[sproc_ar_reconciliation] @fac_ids, @start_date, @end_date'

DECLARE @CsvFile NVARCHAR(500)
SET @CsvFile = 'C:\DOMO\AROS\ar_recon.csv'

DECLARE @cmd NVARCHAR(4000)
SET @cmd =
'bcp '+CHAR(34)+@sql+CHAR(34)+' queryout '+CHAR(34)+@CsvFile+CHAR(34)+' -S '+@@servername
+' -c -t'+CHAR(34)+','+CHAR(34)+' -r'+CHAR(34)+'\n'+CHAR(34)+' -T'

exec master.dbo.xp_cmdshell @cmd

Errors

  NULLStarting copy... SQLState = 37000, NativeError = 137 Error =
  [Microsoft][SQL Server Native Client 11.0][SQL Server]

Must declare the scalar variable "@fac_ids".

  SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server
  Native Client 11.0]`

Unable to resolve column level collations NULL BCP copy out failed NULL


texts: Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

texts:
CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at all.

Setup

This is the content of our my.cnf files:

  Replication Setup A

Where: (master) Mysql 5.5.40 > (slave) MariaDB 10.0.19

Master:

server-id = 1002
binlog-do-db = db_1
binlog-do-db = db_2
binlog-do-db = db_3
sync_binlog = 1
expire_logs_days = 30
innodb_flush_log_at_trx_commit = 1

Slave:

server-id = 1005
replicate-do-db = db_1
replicate-do-db = db_2
replicate-do-db = db_3
slave-parallel-threads=4
slave_compressed_protocol = ON
slave-skip-errors = all
log-slave-updates = ON

Strangely, DB structure altering commands works just fine on a different server pair with an *almost similar replication setup:

  Replication Setup B

Where: (master) MariaDB 10.0.17 > (slave) MariaDB 5.5.41

Master:

server-id = 1004
bind-address = "0.0.0.0"
binlog-do-db = db_a
binlog-do-db = db_b
binlog-do-db = db_c
binlog-do-db = db_d
binlog-do-db = db_e
binlog-do-db = db_f
binlog-do-db = db_g
sync_binlog = 1
expire_logs_days = 30
innodb_flush_log_at_trx_commit = 1

Slave:

server-id = 1003
bind-address = "0.0.0.0"
replicate-do-db = db_a
replicate-do-db = db_b
replicate-do-db = db_c
replicate-do-db = db_d
replicate-do-db = db_e
replicate-do-db = db_f
replicate-do-db = db_g
slave_compressed_protocol = ON
slave-skip-errors = all

  Notes for both replication pairs:

  Names of the databases were obscured.
  Parts of the my.cnf file where i think replication is configured are only posted.

Questions

Is this a bug or an undocumented feature?
Did i do something wrong on my setup?
What could be other factors that might effect to this behaviour?
If there really are (query)commands that aren't supported for
replication, what are these?

4.a) Mysql/MariaDB version setups that has the same effect?

I tried looking at the error logs of the slave for any hints and used structure altering query commands as search keywords but to no avail. Maybe I'm not looking in the right direction?

Update (2015-06-04)

Further testing ensued. As suggested by this SO post, I might have to remove or comment out replicate-do-db from my.cnf; So I did and I found out that:

Creating a new table is replicated.
Altering a table is not replicated.
CRUD transactions were not replicated.

Also, things just got stranger for Replication Setup B where everything was just supposed to work.

One DB failed to replicate an ALTER TABLE command just yesterday and we only found out about it now.
After the incident, I tried testing executing ALTER TABLE commands to all other DBs of the same server and it replicated just fine. In this test, I am unable to remove replicate-do-db from my.cnf as restarting the server is currently not an option.


texts: How to fix `The table '/tmp/mysql/#sql_xxxxx' is full`?

texts:
Ubuntu server 16.04, php7, nginx 1.10, mysql 5.7 in Hyper-V on Windows 10 host

How to fix  The table '/tmp/mysql/#sql_xxxxx' is full ?

In mysql-error.log

  [ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please
  add another file or use  'autoextend' for the last file in setting
  innodb_temp_data_file_path

  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full
  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

  [ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please
  add another file or use  'autoextend' for the last file in setting
  innodb_temp_data_file_path

  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full
  [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full

mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart

But in /tmp/mysql/ - nothing.

In mysqld.cnf

Секция InnoDB

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 5M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1G
innodb_log_buffer_size         = 8M
innodb_max_undo_log_size       = 50M
innodb_temp_data_file_path     = ibtmp1:12M:autoextend:max:512M

If you remove the limit to 512 MB, the file fills all the available space in a few minutes.

innodb_temp_data_file_path     = ibdata1:10M:autoextend:max:512M
innodb_thread_concurrency      = 8
innodb_buffer_pool_instances   = 8
innodb_buffer_pool_dump_pct    = 75
innodb_flush_log_at_trx_commit = 0
sync_binlog                    = 0
innodb_io_capacity             = 600
innodb_io_capacity_max         = 1000
innodb_read_io_threads         = 32
innodb_write_io_threads        = 16
innodb_fast_shutdown           = 0
innodb_max_dirty_pages_pct     = 0

# df -h

udev             5,0G            0  5,0G            0% /dev
tmpfs            1,0G         110M  915M           11% /run
/dev/sda2         35G          22G   12G           65% /
tmpfs            5,0G            0  5,0G            0% /dev/shm
tmpfs            5,0M            0  5,0M            0% /run/lock
tmpfs            5,0G            0  5,0G            0% /sys/fs/cgroup
/dev/sda1        511M         3,4M  508M            1% /boot/efi
tmpfs            1,0G            0  1,0G            0% /run/user/1000


texts: How to find similar records using multidimensional array?

texts:
I have table that describes a movie release. This table consists of columns:

movie_id int
release_day int
showtime_count int
ticket_sold_count int

e.g. (1, 1, 100, 200) tuple describes movie ID 1, on the first day of the movie release when it had 100 showtimes and sold 200 tickets.

I have a movie that is 5 days into the release (e.g. (2,1,100,200), (2,2,150,300), (2,3,100,250), (2,4,150,300), (2,5,100,250)) and I want to find previous releases that have performed similarly up to this day into their release.

I know that I can calculate a distance between two points using <->. But how would I index and search the entire database for a movie where distance for days 1, 2, 3, 4 and 5 is the lowest?


texts: Pgbouncer getting auth failing when trying to connect using psql

texts:
I'm trying to configure the latest pgbouncer to work with postgres 9. I can connect to my db using psql with the right password but when I use psql -p 6432 I can't connect with the error message of psql: ERROR:  auth failed

This seems like it might be caused by my userlist.txt file, but I'm not sure of that. I checked and all required files are owned fully by Postgres system user

pgbouncer.ini

[databases]
postgres = host=localhost port=5433 auth_user=postgres dbname=postgres

[pgbouncer]
pidfile = /usr/local/pgbouncer-1.9.0/pgbouncer.pid
logfile = /usr/local/pgbouncer-1.9.0/log/pgbouncer.log

user = postgres

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /usr/local/pgbouncer-1.9.0/etc/userlist.txt

Userlist.txt

"postgres" "md5<MD5 SUM>"

Command used to start pgbouncer

./bin/pgbouncer -d etc/pgbouncer.ini

Log output showing failure

2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 closing because: client unexpected eof (age=0)
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 closing because: auth failed (age=0)
2019-08-20 13:46:06.980 16446 WARNING C-0x1028ce0: postgres/postgres@127.0.0.1:43414 pooler error: auth failed


texts:
Is it possible to use SQL Server Profiler to trace a running SSIS package? I tried to do that, but all I can see in the trace is executing dp_sqlagent_... and the other internal commands! None of the tables/script that the package is working on displayed in the trace.

I tested different trace templates but had not much lock. Have you got any experience in this area?

Thank you.

Cheers,
Nazila


texts:
I use PG Admin 4 with PostgreSQL 11 for my data. After some kind of crash yesterday, I am not able to start postgresql anymore. I've done some research, but it doesn't look like a localhost, IP address, or port issue.

The log file states (in bold what I think is relevant):
2021-03-21 06:49:36.682 AEDT [11796] LOG:  database system was shut down at 2021-03-20 14:56:05 AEDT
2021-03-21 06:49:36.682 AEDT [11796] LOG:  invalid record length at 5B1/CA7F5900: wanted 24, got 0
2021-03-21 06:49:36.682 AEDT [11796] **LOG:  invalid primary checkpoint record**
2021-03-21 06:49:36.682 AEDT [11796] **PANIC:  could not locate a valid checkpoint record**
2021-03-21 06:49:36.890 AEDT [13136] LOG:  startup process (PID 11796) was terminated by exception 0xC0000409
2021-03-21 06:49:36.890 AEDT [13136] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2021-03-21 06:49:36.890 AEDT [13136] LOG:  aborting startup due to startup process failure
2021-03-21 06:49:36.898 AEDT [13136] LOG:  database system is shut down

I have checked the postgresql.conf and pg_hba.conf files. They look fine and unchanged (allowing all users, having the right IP address, and port number).
postgresql.conf
# - Connection Settings -

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to '*'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = ''   # comma-separated list of directories
                    # (change requires restart)
#unix_socket_group = ''         # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off              # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''          # defaults to the computer name
                    # (change requires restart)

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0       # TCP_KEEPCNT;
                    # 0 selects the system default

# - Authentication -

#authentication_timeout = 1min      # 1s-600s
#password_encryption = md5      # md5 or scram-sha-256
#db_user_namespace = off

**pg_hba.conf**
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

My SSD hard drive, which contains the data directory of postgres seems fine (i.e. I can read / write and access all files). In an effort to fix my starting issue, I was trying to run pg_resetwal but unsuccessful because the program did not open although the postgres service stopped.
From the Windows logs:

Waiting for server startup...
LOG:  listening on IPv6 address "::", port 5432
LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "log".

pgAdmin gives me the following (obviouldy I can't start postgresql...):

could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP
connections on port 5432?

Do you have any idea what could be wrong here? Many thanks!


texts: Running materialized view refresh in parallel

texts:
I have a large materialized view that is refreshed periodically (complete refresh) and I would like to do it in parallel. I can already run the query part in parallel but the problem is (much slower) delete in the beginning of refresh.

I have done the following:

set the table and query degree to 6
alter session enable parallel dml;
run refresh with option "parallelism => 6"

But delete still runs serially. Is there some option I have missed?


texts: I want to unlock the locks obtained on a table. I tried killing the session but i dont have the dba privilege. Is there any way to do it? Executing COMMIT or ROLLBACK afterward didn't help, because since I started Oracle SQL Developer I got a new session ID. After a while Oracle removed the inactive sessions and locks, but I would like to have a way to remove the locks as a non-admin user


texts:
I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity constraint)
When the data is distributed across multiple servers (ex
1 master- n slaves configuration)

Do relational databases ensure consistency in reads? I mean a committed write is immediately available to be read by other transactions? I suspect not given network is involved, making Relational DBs as eventual consistent DBs.
Do relational databases ensure referential integrity constraint well?


texts: Inconsistent ResultSet from Procedure with Variables

texts:
I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.

In this question, there are in actual fact two questions. Number 1 is the most important in this context. Number 2 is less important.

1. Inconsistency

Data Sets

    [tbl] employees (PRIM)           [tbl] loc_offices (SEC)        [tbl] emp_types (SEC)
+-----------------+-----------+  +-----------------+-----------+  +-----------+-----------+
|     COLUMNS     | DATATYPE  |  |     COLUMNS     | DATATYPE  |  |  COLUMNS  | DATATYPE  |
+-----------------+-----------+  +-----------------+-----------+  +-----------+-----------+
| emp_id          | <VARCHAR> |  | office_id       |   <INT>   |  | emp_type  | <VARCHAR> |
| emp_lastname    | <VARCHAR> |  | office_name     | <VARCHAR> |  | type_desc | <VARCHAR> |
| emp_firstname   | <VARCHAR> |  | office_town     | <VARCHAR> |  +-----------+-----------+
| emp_replocation | <VARCHAR> |  | office_province |   <INT>   |
| emp_type        | <VARCHAR> |  +-----------------+-----------+
| emp_status      |  <INT>    |
| emp_image       |  <BLOB>   |
+-----------------+-----------+

Procedure

CREATE PROCEDURE `getEmployees`(
    IN location INT(1),
    IN stafftype VARCHAR(1)
)
BEGIN
    SELECT
        emp_id,
        emp_lastname,
        emp_firstname,
        office_name,
        type_desc,
        emp_status
    FROM
        employees t1
    INNER JOIN loc_offices t2
        ON t1.emp_replocation = t2.office_id
    INNER JOIN emp_types t3
        ON t1.emp_type = t3.emp_type
    WHERE
        IF (
            location = '%' AND stafftype = '%',
            (
                t1.emp_replocation = location
                AND t1.emp_type = stafftype
            ),
            IF (
                location = '%' AND stafftype != '%',
                t1.emp_type = stafftype,
                IF (
                    location != '%' AND stafftype = '%',
                    t1.emp_replocation = stafftype,
                    '%'
                )
            )
        )
    ORDER BY
        emp_id ASC
    ;
END

Test Calls

CALL getEmployees();

• Expected Outcome:
Return all records in data set

• Outcome:
"Incorrect number of arguments for PROCEDURE db.getEmployees; expected 2, got 0"
CALL getEmployees(NULL, NULL);

• Expected Outcome:
Return all records in data set

• Outcome:
Empty Result-set
CALL getEmployees(1, NULL);

• Expected Outcome:
Return records in data set filtered by emp_replocation only

• Outcome:
Empty Result-set
CALL getEmployees(NULL, "C");

• Expected Outcome:
Return records in data set filtered by emp_type only

• Outcome:
All records, with correct emp_type
CALL getEmployees(1, "C");

• Expected Outcome:
Return records in data set filtered by emp_type and emp_replocation

• Outcome:
Empty Result-set

Changes Attempted

Replacing (!)= '%' with IS (NOT) NULL
Replacing '%' with FALSE

The Question

Based on this information, why is the location parameter ignored, and what is the correction recommended in this situation?

2. The second question

I've researched this, but unsure of the correct approach. How can I handle empty Procedure Calls?

In the case of getEmployees(), it currently results in an incomplete call error, which is expected, instead of selecting all records without filters.


texts: Composite Primary Key in multi-tenant SQL Server database

texts:
I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will have TenantId (Guid / UNIQUEIDENTIFIER) field. Right now, I use single field Primary Key which is Id (Guid). But by using just the Id field, I have to check if the data supplied by the user is from / for the right tenant. For example, I have a SalesOrder table which has a CustomerId field. Every time users post/update a sales order, I have to check if the CustomerId is from the same tenant. It gets worse because each tenant might have several outlets. Then I have to check TenantId and OutletId. It's really a maintenance nightmare and bad for performance.

I'm thinking to add TenantId to the Primary Key along with Id. And possibly add OutletId, too. So the Primary Key in the SalesOrder table will be: Id, TenantId, and OutletId. What is the downside of this approach? Would the performance hurt badly using a composite key? Does the composite key order matter? Are there better solutions for my problem?


texts: How to see the list of the Postgres databases via the linux terminal?

texts:
I have connected a database using the command
sudo -i -u postgres

And after I have tried \l  and \d too, to view the list of all available databases, it is not working. Instead, I get the following output:
d:command not found and
l:command not found error is showing

What to do, how to get the list of the postgresql databases?


texts: What's the biggest bottleneck (time) in writing data to a SQL database?

texts:
I'm running an ETL process that is writing about 2 million rows to a SQL Server database.

I'm trying to optimize the time it takes for pure inserts (I guess updates is a different story).

I'm wondering what the biggest bottleneck is, or best way to reduce time, for basic inserts into a SQL database.

I mean, the first thing, probably, is the size of the data, right? Both the number of rows, the number of columns, and the data size in each column. Some of these may not be able to be minimized, the KB/ footprint of each row is one thing that can be potentially optimized, right?

What else can be optimized or is the largest factor? Is it the transmission medium? I mean, how much magnitude of difference is there between writing to a database that's on the same computer, vs writing across a web connection (that is robust, fast, and has a ping of 1 ms?).

Finally --- why is it that multiple parallel connections to the database seem to speed up the process to a point? I mean, when I have 20 connections making inserts round-robin style, it's about 6-7x faster than one connection writing ALL the data. I'm curious why this is.

Right now I have 2.2 million rows totaling 2.7 GB. That's 1.23 kb per row.

Right now inserting 1000 rows at at time (1.23 MB) using 14 connections takes 6.7 seconds. That's a snail-paced 10.66 rows per second. Even assuming 1 connection would be just as fast (it isn't) that's 150 rows/ second at best, which is not exactly "fast" either.  I'm writing over a super-fast, robust web connection b/c we can't have the ETL process on the same space as the data warehouse.

So .. how can I optimize for speed here?

The reason for 1000 rows at a time is because the data comes from pages of 1000 - but optimizing the parsing is a separate issue for now.

I do have one primary index I believe, but nothing too write-expensive. Right now I've simply been doing Monte Carlo like testing (try it and see what works) but I need something more focused.


texts: Bulk change Agent Jobs' definition to "Quit the job reporting failure" from "Go to the next step" On Failure

texts:
There are agent jobs with multiple steps in our servers and all their steps are defined as "go to the next step" "On Failure". So, even a job step fails, operator doesn't get notified because eventually the job succeeds.
How can I modify all steps in all jobs so that job fails reporting failure if a step fails?
edit: best way, I guess, would be to use msdb.dbo.sp_update_jobstep dynamically to update all steps where @on_fail_action !=2
Thanks all.


texts: Get position of mysqlbinlog error. I have the error's line number

texts:
If I use mysqlbinlog to load a binlog into my database like this:

mysqlbinlog --defaults-file=/path/mysqld.cnf bin.000011| mysql -u root

and get an error like this:

ERROR 2006 at line 290: MySQL server has gone away

How do I convert line 290 into a binlog position that I can use to rerun the log, starting at the failed line?

 mysqlbinlog --defaults-file=/path/mysqld.cnf --start-position=<WANT THIS> bin.000011| mysql -u root

I'm using binlog-format=ROW

Or, how can I log the binlog position of the error, and not just the line number?

It seems that there must be a way to recover from an error while updating a database with mysqlbinlog, otherwise the whole database is corrupted.


texts: How can I restore a 10.91G table in Oracle Express?

texts:
I've received a .dmp file of an enterprise database, but currently only have access to a free version of Oracle. I would ultimately like to import the data to PostgreSQL. But currently am wondering if there's a way to restore the largest table (10.91G) in chunks if need be, so I can export its contents to csvs to import into PostgreSQL. Currently if I try to selectively restore the 10.91GB table with

impdp system/pw tables=TEST.BIG_TABLE directory=test_data dumpfile=test_data.dmp log=log.log;

I get

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-31694: master table "SYSTEM"."SYS_IMPORT_TABLE_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-39776: fatal Direct Path API error loading table "SYSTEM"."SYS_IMPORT_TABLE_
01"
ORA-12953: The request exceeds the maximum allowed database size of 11 GB

The largest Tablespace I've been able to create so far was of SIZE 10500M, the command:
CREATE TABLESPACE test DATAFILE 'test.dbf' SIZE 10600M ONLINE; produced error ORA-12953: The request exceeds the maximum allowed database size of 11 GB

The results of select tablespace_name, sum(bytes) from dba_data_files group by tablespace_name are:

tablespace | sum(bytes)/1000000
-----------+-------------------
SYSAUX     | 692.06016
UNDOTBS1   | 398.45888
USERS      | 104.8576
SYSTEM     | 524.288
TEST       | 11010.048


texts: Prevent row from being inserted with a trigger

texts:
I'm trying to prevent a row being inserted if a certain condition is met using a trigger. I'm using a user defined exception to do this, however the row is still being inserted, despite the exception being thrown. I'm not sure why this is happening, I though when an exception was raised the transaction was rolled back and nothing would be inserted. The code is below.

create or replace TRIGGER
  CHECK_OPTION
BEFORE INSERT
ON lot_options
FOR EACH ROW

DECLARE
    v_allowed_stage NUMBER;
    v_actual_completion_date DATE;
    option_change_invalid EXCEPTION;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting the begin');
    SELECT stage_stage INTO v_allowed_stage
    FROM "TEAM3"."Option" o
    WHERE o.option_number = :new.option_number;
    DBMS_OUTPUT.PUT_LINE(v_allowed_stage);

    DBMS_OUTPUT.PUT('Stage in a cell');

    SELECT actual_completion INTO v_actual_completion_date
    FROM progress
    WHERE stage_stage = v_allowed_stage
    AND Lot_lot_number = :new.lot_lot_number;
    DBMS_OUTPUT.PUT_LINE('completion date in storage');

    IF (v_actual_completion_date IS NOT NULL)
    --DBMS_OUTPUT.PUT('condition holds true')
    THEN RAISE option_change_invalid;
    END IF;

    EXCEPTION
    WHEN option_change_invalid THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Option Change: Option change is not allowed in this stage of progress.');
END;


texts: Pull Subscription: process cannot read file due to OS error 5

texts:
I am trying to migrate a working pull subscription for transactional replication from one subscribing server to a new one. The subscribing server is at another site and is connected via a VPN tunnel. The serverName is resolvable via the hosts file.

I am trying to capture the existing configuration precisely, but clearly am missing something.

The error is The process could not read file '\\[server]\repldata\unc\[folder]\[folder]\[file].pre' due to OS error 5. I can RDP into the subscribing server with the distributor connection account and can access the file on the UNC share.

Everyone has permissions to the UNC share and we haven't seen this problem with other subscriptions.

The distribution process account is the SQL Server Agent, which I know is not best practice but matches the configuration of the existing working replication. I temporarily tried using a (local) Windows account

Again, we have tried to configure the subscribing server exactly as the working server. What are we missing? We never saw this error when setting up the previous subscription.

One note: the old subscription is still up and functioning, and uses the same accounts to connect to the distributor. I wonder if Access is Denied could be thrown due to a sharing conflict.


texts:
Given a table like:

object    subject    ID      parentID
------    -------   -----    --------
  A         NULL      1          0
  B         NULL      2          0
  C         NULL      3          0
  D         NULL      4          0
  E         NULL      5          0
 Null        CC       6          1
 Null        DD       7          2
 Null        EE       8          2

I need a query with good performance that returns the following result:

result
------
  A
  CC
  B
  DD
  EE


texts: Delete duplicate keys with relation algebra without count

texts:
Hi I am new to relational algebra. I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same Value  occurring in multiple columns or same column multiple times (This comparison is only done on the Value1 and Value2 columns and not on Key1 or Key2 columns ).

e.g. I want to keep only [4, 401,4,501] and [6, 701,6,801] but remove everything else. But I want to achieve this without using count.

Key1   | Value1 |Key2   | Value2
-------|--------|-------|-------
 1     | 101    |  1    | 101
 2     | 101    |  2    | 201
 3     | 101    |  3    | 301
 4     | 401    |  4    | 501
 5     | 201    |  5    | 901
 6     | 701    |  6    | 801

Thanks.


texts: Should I use VACUUM on a SQLite db with a log-structured filesystem?

texts:
I'm runnig a SQLite database on nilfs (which is a log-structured filesystem). Every now and then I delete old records, so that the size of the database never surpass a certain amount.
But since the filesystem is log-structured (also, I'm running from flash memory) I am uncertain if I should also run VACUUM command or not.


texts:
I have a table with rates.  each rate has an effective starting date.  (the ending date of any rate is implied by the existence of a record with a newer effective starting date.)
I also have a table with activities.  Each activity occurs on a date.

What is the best way to match an activity to the rate that was effective on that date?

(unfortunately I don't have the ability to modify the data structure, so adding an explicit ending date for a rate is out.)

rates:
StartDate Rate
9/1/2010    17.00
10/1/2010   18.70
11/1/2010   20.00

Activities:
WorkCenter  ActionDate Hours
WC1         9/30/2010   10
WC1         10/1/2010   5
WC2         10/30/2010  8
WC2         11/3/2010   9

Desired result:
Workcenter ActionDate   Hours   Rate    Cost(=rate*hours)
WC1         9/30/2010   10      17.00   170.00
WC1         10/1/2010   5       18.70    93.50
WC2         10/30/2010  8       18.70   149.60
WC2         11/3/2010   9       20.00   180.00


texts:
i am trying to connect my database to XAMP . I got  this error

Warning: Use of undefined constant ID - assumed 'ID' (this will throw an Error in a future version of PHP) in C:\xampp\htdocs\Thairis\login.php on line 8

Fatal error: Uncaught Error: Call to undefined function mysql_connect() in C:\xampp\htdocs\Thairis\connectdb.php:27 Stack trace: #0 C:\xampp\htdocs\Thairis\login.php(11): include() #1 {main} thrown in C:\xampp\htdocs\Thairis\connectdb.php on line 27


texts:
I've a SQL Server 2012 SP2 with max memory set to 13GB but there is 8GB of stolen memory (SQLServer:Memory Manager - Stolen Server Memory (KB)). I know stolen memory is memory taken from the bufferpool to do sorting and hashing operations.

But how can I identify what process/request is using all that stolen memory, or what query is doing a lot of hashing/sorting?

Update
Further research showed that MEMORYCLERK_XE was using 4.3GB. This could be fixed by SP3. We will schedule this update and monitor if the problem is fixed.


texts: table or view does not exist when using insert into

texts:
I'm trying to insert into but getting the "table or view does not exist" error.

It tells me the specific line where the error occurs but I can't figure out what the problem is:

INSERT INTO c_ord_cur (ordid, total)
          VALUES (v_ord_rec.ordid, v_ord_rec.total);

I have already created the Table and it shows it on the left panel of oracle:

I'm using oracle developer sql, any ideas what is causing this problem?

Whole code:

Set serveroutput on

DECLARE
    CURSOR c_ord_cur IS
    SELECT
 *
    FROM
      shoba.ord;

    v_ord_rec   c_ord_cur%ROWTYPE;

BEGIN
    OPEN c_ord_cur;
    LOOP
        FETCH c_ord_cur into v_ord_rec;
        EXIT WHEN c_ord_cur%NOTFOUND;

        IF v_ord_rec.orderdate between '01/01/2009' and '31/12/2009' THEN
                INSERT INTO c_ord_cur (ordid, total)
              VALUES (v_ord_rec.ordid, v_ord_rec.total);
            dbms_output.put_line(v_ord_rec.orderdate||' is in ords86');
    END IF;

    IF v_ord_rec.orderdate between '01/01/2010' and '31/12/2010' THEN
                INSERT INTO c_ord_cur (ordid, total)
          VALUES (v_ord_rec.ordid, v_ord_rec.total);
        dbms_output.put_line(v_ord_rec.orderdate||' is in ords87');
        END IF;

    END LOOP;
    CLOSE c_ord_cur;

    COMMIT;

END;


texts:
I have a standby database that is being restored in standby mode using log-shipped transaction files downloaded from a vendor's sFTP site on a daily basis.

Now, I am required to give a read-only access on this database to someone else, outside our domain. Since it is on standby, I can not create a user and grant read access. I also asked the vendor if they can do this for me on primary database but they said no.

So I am asking here if there is any way round to solve this problem?

If I change the database to online status and created a mapped user to the database, would that be okay to bring it back to a standby again and continue the restoring process without hassle?


texts: Retrieve information about a recent copy only backup?

texts:
I have taken a copy only full backup using the query

BACKUP DATABASE abc
TO DISK = N'E:\Backup\abc.bak'
WITH COPY_ONLY,COMPRESSION

And when I try to check this information via the query,

SELECT  top 100 *
FROM msdb.dbo.backupset a with(nolock)
INNER JOIN msdb.dbo.backupmediafamily b with(nolock)
ON a.media_set_id = b.media_set_id
WHERE is_copy_only = 1
ORDER BY  a.database_name desc,
a.backup_finish_date desc

...the name of the backup I just took can't be found in the name column, instead NULL is being returned. However the path to the backup is listed in the physical_device_name column. I am unable to figure out why name of the backupset is being returned as NULL.


texts:
I have multiple transaction records in table A like this:

TXN#  DATE        CUSTOMER
1001  10/05/2015  Cust 1
1002  02/03/2016  Cust 2
1003  02/06/2016  Cust 1
1004  03/25/2016  Cust 3
1005  03/26/2016  Cust 1
1006  03/26/2016  Cust 2
1007  04/06/2016  Cust 3

I would like to create a query or a new table that contains the total number (count) of transactions for each month by CUSTOMER like this:

CUSTOMER  Jan   Feb   Mar   Apr   May ... etc.
Cust 1      0     1     1     0     0 ... etc.
Cust 2      0     1     1     0     0 ... etc.
Cust 3      0     0     2     0     0 ... etc.

I understand a query that gives me counts for a specific time range, but I don't know how to do it for multiple months in one nice result grid.

Is this possible?

I was thinking of just updating the total counts into a table, and manipulating the second table, but I need to bone up on that.

This is the code I'm using to do the current search:

/* Declarations */
DECLARE @SearchStartDate varchar(10);
DECLARE @SearchEndDate   varchar(10);
SET @SearchStartDate = '2016-01-01';
SET @SearchEndDate   = '2016-12-31';

/* Count by customer */
select e.custaccountid as "Company", count (e.custaccountid) as "Orders"
from oriediorder as e  WITH (NOLOCK)
join salestable as s  WITH (NOLOCK)
on (e.dataareaid = s.dataareaid) and (e.salesid = s.salesid) and (e.PONUMBER = s.PONUMBER)
group by e.custaccountid
order by Orders desc;

It requires joins and groupings.

Suggestions?


texts: Finding count of objects within a specified time window in SQL (Postgresql DB)

texts:
I need to find out all the users who registered for my Postgresql 9.3 backed website in the 24 hour window 2 days ago. Currently, I'm doing that via running the following queries, and then manually subtracting the difference:

select count (*) from auth_user where date_joined > now() - interval'24 hours';
select count (*) from auth_user where date_joined > now() - interval'48 hours';

How do I do everything in the same SQL query, including the subtraction? Thanks in advance!

If I do select count (*) from auth_user where date_joined > (now() - interval'48 hours') - (now() - interval'24 hours');, I get:

  No operator matches the given name and argument type(s). You might
  need to add explicit type casts.


texts: Determine the currently executing statement in a long running PL/pgSQL stored procedure

texts: I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?


texts:
We have a large database, with a size of approx 400GB. We are using bulk insert (from upstream system) with the following parameters:

`Driver=/IIS/Appl/Server/branded_odbc/lib/VMsqls24.so`
`Description=DataDirect 6.0 SQL Server Native Wire Protocol`
`Address=SQLLPB2GBRxxx.xxxxx.COM,14331`
`AnsiNPW=Yes`
`AuthenticationMethod=4`
`Database=DB_PROD`
`QuotedId=No`
`EnableBulkLoad=1`
`BulkBinaryThreshold=32`
`BulkCharacterThreshold=-1`
`BulkLoadBatchSize=1024`
`BulkLoadOptions=0`

The delta changes in the DB represent approx 70GB at month end. The transaction log file is falling over at 800GB (twice the size of the actual DB, where 800GB is the current size of the allocated transaction log drive). Autogrow is enabled on the transaction log, but we are unsure WHY it is becoming so large. Simple recovery is set.

Are there any parameters (either upstream on the bulk insert, or on the transaction log db ) we can fine tune?


texts: Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime

texts:
I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime.
I thought I could :

on the old Postgres, activate archive_command to copy WAL to a NFS directory mounted on both the old and new server
pg_dumpall the old Postgres
install a Postgres 9.6 on the new server
import the SQL dump
restart the new server in recovery mode, pointing to the NFS directory to get the WAL from the old server
So when the old and new Postgres are in sync, I can stop the old, verify that the new is up-to-date, change applications configuration to the new Postgres, and restart the applications.

I tested this with some test databases, and it does not work: on the new postgres the latest checkpoint's redo wal file has a number which is higher than the WAL number on the old. When I import the sql dump some WAL are generated, so the WAL number get incremented. So when I restart the new in recovery mode it does not read the old Postgres WAL.

I'm not sure this method is possible at all.

Could someone advise me another method if this one is not possible?


texts: SQL Question: Selecting different language versions based on availability

texts:
I have the following situation: I have a table that contains entries with language versions (German and English) as part of the key (the other part being a "name" field). However, not always both versions are available - sometimes, e.g. only a German version exists. I want to build a select statement so that I get all English ones that are available, and all the German ones for the entries that have no English version. The resulting table should not contain any duplicates in the "name" field.

Currently, I've come up with this query:

SELECT * FROM table WHERE lang = 'E'
UNION
SELECT * FROM table WHERE lang = 'D'
                      AND name NOT IN (SELECT name FROM table WHERE lang = 'E')

This works, but it seems very complicated and I'm afraid it doesn't perform very well. Also, if I want additional WHERE clauses on the result I have to build another SELECT * around it, using the above statement as a temp table.

Is there a better way to do this? Thanks for your help in advance!


texts: Should the database service be restarted after performing a restore?

texts:
Given:

a database backup of >100G uncompressed disk space
SQL Server 2012 Instance
Dev/Test system host (Non-production)

Is it better to restart the MS SQL Server, Agent, etc services after restoring the DB? Why or why not?


texts:
I am a newbie to the sql world :)
Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ?

Here is the code :

DELIMITER //
CREATE TRIGGER trigger_vote_question_after_insert AFTER INSERT ON vote_question
FOR EACH ROW
BEGIN
IF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) > 0) AND NEW.vote_question_type = '1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus + 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) > 0) AND NEW.vote_question_type = '-1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus - 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 1, 0, 1)
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '-1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 0, 1, 1)
ENDIF
END//

Also i might try this trigger with postgresql should i change something ? (ELSEIF to ELSE IF I guess)


texts: Backup on both secondaries or a dedicated backup secondary

texts:
In my current replica set setup, I have a a primary and 2 secondaries (both a normal secondaries - priority 1 on each, no delay, no hidden). Note that reads and writes are only against the primary.

Backups are occurring at regular intervals using LVM on both secondaries. This seems like overkill to me.

Is it more common practice to have one dedicated backup secondary rather than backup on both secondaries? And set this dedicated backup secondary to have priority 0?


texts: Use data in a tablespace to populate another postgres database instance without actually inserting

texts:
I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there a way to use this existing data. Or can I create another database instance (may be on a separate server) and create empty bigTable and point to the existing tablespace to populate the data in the existing bigTable?

Can I use the existing data in a tablespace to be populated in another postgres database instance without actually inserting the data, as reinserting the whole data takes so much time.

I tried updating the pg_class table to point to the existing reltablespace and relfilenode. But it didn't help me fully since I was not able to change the database OID. Is there a way so that we can create a database instance on another database server with he same database OID?


texts: Migration Wizard Offline SQL Server to Oracle, before Move Data, directory Data is empty

texts:
I'm migrating data that be in SQL Server to Oracle

I'm doing it Offline, I generated .ocp, I ran the OMWB_OFFLINE_CAPTURE.bat, I passed the parameters and generated two directories, one called Master and other with name of my Database in SQL Server, in that case called db_GFA and I granted privileges to user

After I executed the Migration Wizard Offline, I catched the sqlserver2008.ocp file, I Converted types data and didn't traslate the Objects, because I want just the data, and I executed the Move Data e generated the directory called DATAMOVE, but the directory "Data", in DATAMOVE, is empty, didn't brought the data

Someone can help me? I need that data