texts: How to design a table/ unique index where uniqueness is temporal?

texts:
I have a table that records observed movies in a cinema:

CREATE TABLE cinema_movie_name (
  id integer DEFAULT nextval('cinema_movie_name_id_seq'::regclass) PRIMARY KEY,
  cinema_id integer NOT NULL REFERENCES cinema(id) ON DELETE CASCADE ON UPDATE RESTRICT,
  name text NOT NULL,
  movie_id integer REFERENCES movie(id) ON DELETE SET NULL ON UPDATE RESTRICT,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

Every time we see a new movie name in the cinema, we record it in this table and map it to movie_id in our database.

The current uniqueness is enforced on (cinema_id, name), i.e.

CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx ON cinema_movie_name(cinema_id, name);

The problem is that over a long time, there can be multiple different movies with the same name, i.e. after some arbitrary time duration (id, cinema_id, name, movie_id) becomes invalid.

Therefore, I need to create an index that enforces a unique constraint on (cinema_id, name) and created_at ±3 month.

How to create a unique index with a temporal column?


texts: Need an Audit Log. Should I create an Audit Log Table or Audit Fields in each table?

texts:
By no means am I a DB expert, but I do have experience with creating DBs. Not sure about the following though.

I want everything that happens in the system to be trackable. My client is dealing with lots of money and if mistakes are made, they need to be tracked. From both a DB and a developer perspective, what's easier: 1)

created_by
created_date
updated_by
updated_date

in 7-10 tables or

2) a dedicated table with

action_done
action_done_by
action_date

and then a secondary table with what the actions are.
id
action_type

Pros to 1: Easier to make sure you don't miss anything.
Cons to 1: Annoying to maintain, report on, etc. and so not clean.

Pros to 2: Oh so clean, easy to report on.
Cons to 2: Easier to miss something you want to log (though that's possibly true for 1, it's less likely because the fields are in each table and they need to be used for something.)

Is that it and I just need to make a decision one way or the other? Or is there a DB performance concern I should have or something else I'm missing?


texts: RIGHT() function in postgresql 9.1 not behaving as anticipated?

texts:
The following is with regard to PostgresQL 9.1 :

I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing something along the lines of right(field1,1)||left(field2,-1) to get correct values and such.  In the process, I encountered an unexpected result on a char(2) column.

If I do SELECT RIGHT('A ',1) it gives me the expected ' ' returned.

However, if I do SELECT RIGHT('A '::CHAR(2),1) it gives me 'A'.

Why is it doing this?  Is it a bug?  I tried SELECT RIGHT('A  '::CHAR(3),1) and it also gives 'A' as a result.


texts: Use CASE to select columns in UPDATE query?

texts:
I can use CASE to choose which columns to display in a SELECT query (Postgres), like so:

SELECT CASE WHEN val = 0 THEN column_x
            WHEN val = 1 THEN column_y
            ELSE 0
       END AS update, ...

Is something similar at all possible when performing an UPDATE query in Postgres (i.e. choose which columns should be updated)? I assume not since I couldn't find anything about this, but maybe someone has a clever alternative (besides using a procedure or updating each column using a CASE to determine if the value of the column should be assigned a new value or simply reassigned the existing value). If there is no easy alternative, I'll of course accept that as an answer as well.

Extra info: In my case I have 14 potential columns that may be updated, with only one being updated per matching row (the table to be updated is joined with another in the query). The amount of rows to update will most likely vary, could be dozens or hundreds. I believe indexes are in place for the joining conditions.


texts: ORA-12541 error while connecting to oracle express edition 11g

texts:
My oracle express edition was working some months earlier.

I dont know what i changed in the system, now I could not connect to it. I tried logging in from sqlplus as sysdba and altered the accounts to unlock, grant connect and resource privileges but I couldn't connect to database through sqltool.

I checked the listener.ora file where the host was Rajan. Ping on Rajan is working(pointing to localhost).


texts: Replicate/Sync Production database with Development database (no Master/Slave)

texts:
I currently use ClearDB MySQL DaaS for my production database, and a local MySQL database on my dev server for development. I need to have the production database replicated/copied/synced to the dev database so the data my dev app is working with is the same as the production data.

Because ClearDB is a DaaS implementation, I have no ability to set up and sort of master slave replication. I am struggling to find the best solution to keep my dev database either

A) Constantly in sync with my production database
or
B) Synced once a day either manually or automatically

At the moment I am doing a complete dump of the production, and inserting in to my dev database. This is already quite time consuming, but as the production database grows, this will not be an option very soon.

Any suggestions of a way to accomplish either A or B above without a master slave configuration.


texts: Corrupt InnoDB tables after every mysql restart. What's wrong?

texts:
Recently I changed a few settings in my mysql configuration. I did the following:
- stop mysql
- save the changed settings
- delete all ibdata and ib_logfiles
- start mysql

Normally this works fine but recently, everytime I do it, all my InnoDB tables get currupted. What am I doing wrong?

My settings:

# The MySQL server
[mysqld]
port        = 3306
socket      = /var/lib/mysql/mysql.sock
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
thread_cache = 16
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

#skip-networking
server-id   = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Uncomment the following if you are NOT using BDB tables
skip-bdb

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 400M
innodb_additional_mem_pool_size = 30M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 400M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout


texts: Is it recommended to monthly planned restart MongoDB production server to clean non-mapped virtual memory?

texts:
We have a replica set of production mongoDB 3.6.10 , large volume of 5.5 TB , over 10,000 connections anytime , applications read both from primary and secondary servers , we see almost every month a crash in the primary server while every month we see increase in the non-mapped virtual memory.
Is it recommended to do a planned restart every month in order to clean the non-mapped virtual memory and to avoid server crashes?
Thanks
alon


texts:
I am this view which I am trying to compile. Given below is the view.

  CREATE OR REPLACE FORCE VIEW "My_View" ("my_ORDER",

"REQUESTOR", "DEPARTMENT", "HOD", "SHIP_TO", "SOLD_TO", "PRODUCT", "GOODS_TYPE", "GOODS_CATEGORY", "ADDITIONAL_TEXT", "EXP_RETURN_DATE", "RETURN_STATUS", "VMONTH", "VYEAR", "TOTAL", "CURRENCY", "REMARKS", "FLIGHT_INFO")
    AS
      SELECT DISTINCT a.location
        || DECODE(LENGTH(a.financial_year), 1, '0'
        || a.financial_year , a.financial_year)
        || '-'
        || a.order_no My_ORDER,

    CASE
      WHEN a.ORDS_INITIATOR_USRS_ID != a.ORDS_CREATED_USRS_ID
      THEN v_users.ADUS_GIVEN_NAME
        || ' '
        || v_users.ADUS_SURE_NAME
        || '/'
        || v_users1.ADUS_GIVEN_NAME
        || ' '
        || v_users1.ADUS_SURE_NAME
      ELSE v_users.ADUS_GIVEN_NAME
        || ' '
        || v_users.ADUS_SURE_NAME
    END as Requestor,
    dept.DEPT_NAME Department ,
    c.display_name HOD,

            oadd1.OADD_CUSTOMERNO SHIP_TO,

            oadd2.OADD_CUSTOMERNO SOLD_TO,
    b.type Product,
    d.goods_type_sap Goods_type,
    e.goods_category,
    b.addtext Additional_Text,
    b.exp_return_date,
    'OVERDUE' return_status,
    TO_CHAR(b.exp_return_date, 'MON') vMonth,
    TO_CHAR(b.exp_return_date, 'yyyy') vYear,
    ROUND(DECODE(price_unit,1,price * quantity,9,price,price * quantity / power (10,price_unit -1)),2) Total,
    b.currency,
    a.remarks,
    shipping_details Flight_info
  FROM orders a,

    (SELECT a.STSL_ORDS_ID,

            v_users.ADUS_DISPLAY_NAME    as user,

              v_users1.ADUS_DISPLAY_NAME AS initialUser,
              LAG (a.STSL_FROM_STAS_ID) OVER (PARTITION BY a.STSL_ORDS_ID ORDER BY STSL_CHANGED_DATE) AS lag_status_id

              FROM ORDERS d

              INNER JOIN STATUS_LOG a ON d.ORDS_ID = a.STSL_ORDS_ID
              INNER JOIN USERS c ON a.STSL_USRS_ID=c.USRS_ID
              -------------------
              INNER JOIN v_uif_ad_users v_users
               ON c.usrs_id = v_users.ADUS_SAM_ACCOUNT_NAME and a.STSL_USRS_ID=c.USRS_ID
  ---------------------
              INNER JOIN v_uif_ad_users v_users1
              ON c.usrs_id = v_users1.ADUS_SAM_ACCOUNT_NAME and c.USRS_ID=d.ORDS_INITIATOR_USRS_ID
  ------------------------
              INNER JOIN ORDERS d on d.ORDS_ID=a.STSL_ORDS_ID
              inner join status b on a.STSL_FROM_STAS_ID=b.STAS_ID

            where
                        d.location    = 'MAL'
    AND d.ORDS_STAS_ID   = 100
    ORDER BY ORDS_ID DESC,
      lag_status_id
    ) c
  INNER JOIN departments dept on a.ORDS_DEPT_ID=dep.DEPT_ID
  inner join ORDERS_DETAIL b on a.ords_id=b.ORDL_ORDS_ID
  inner join orders_address oadd1 on a.ORDS_ID=oadd1.oadd_ords_id and oadd1.oadd_atyp_id=2
  inner join orders_address oadd2 on a.ORDS_ID=oadd2.oadd_ords_id and oadd2.oadd_atyp_id=3
  inner join goods_type d on b.ORDL_GTYP_ID = d.GTYP_ID
  inner join goods_category e on b.ORDL_GCAT_ID = e.GCAT_ID
  inner join users f on f.users_id =a.ORDS_INITIATOR_USRS_ID
  inner join users g on g.users_id = a.ORDS_CREATED_USRS_ID
  WHERE --a.ORDS_ID         = b.ORDL_ORDS_ID

   a.ORDS_ID=c.STSL_ORDS_ID

  AND a.ORDS_STAS_ID           = 100
  AND a.location            ='MAL'
  AND c.lag_status_id       = 10

  AND b.ORDL_TO_BE_RETURNED      = 1
  AND b.ORDL_EXP_RETURN_DATE    <= sysdate
  AND ( b.ORDL_RETURN_STATUS    IS NULL
  OR upper(b.ORDL_RETURN_STATUS) = 'OUTSTANDING'
  OR upper(b.ORDL_RETURN_STATUS) = 'OVERDUE' )
 ORDER BY my_Order;

The error which I get is
    Error at command Line:45 Column:44
    Error Report:
    ORA-00923:From keyword not found where expected

Please see what I can do about this.
Thanks in advance.


texts: What to do after deleting a million records

texts:
We are implementing a new feature in our system that will cause about a million records (each record is tiny, basically a GUID, a date, and four smallint fields) to be purged from a table every night. Basically it's a caching table, and once the data is 7 days old we do:

DELETE FROM scheduleCache WHERE schDateCreated < '2013-08-26

This will run every night at 1am, and will purge about a million records every time it runs.

Is there anything I should be noting or doing for a table like this? Any properties I should put on the table, or any routines I should run regularly to "clean up"? I've never dealt with a table like this before.

The table has a single clustered index (GUID + one of the smallint fields), and we have a weekly index rebuild that runs Sunday mornings.


texts:
I have been struggling trying to find a way to properly and reliably maintain a balance for accounts in an order/payment system.

Currently, the process consists of three tables. Below example is very simplified but gets the point across.

Table one contains the account ID, name, total paid, total orders, balance etc.

Table two contains payments, date, the account ID (FK) and amount.

Table three contains orders, date, the account ID (FK) and amount.

Calculating the balance is of course as easy as

SELECT SUM(payments.AMOUNT) FROM payments WHERE ID = [account]
SELECT SUM(orders.AMOUNT) FROM orders WHERE ID = [account]

Followed by some simple addition/subtraction math:

UPDATE accounts SET BALANCE = [balance] WHERE ID = [account]

All good - the problem is - how do I maintain this over let's assume years of activity and many, many transactions?

Currently the accounts table is updated every single time a transaction is made, meaning the above three statements run every time. There is of course an index on the ID column in all three tables, and everything is fast and neat for now, but I work with less than 100,000 transactions, meaning everything fits into the RAM and basically all I throw at the database gets executed immediately. Over time this will change, of course - so how do I handle this?

Is it better to mark the accounts as "dirty" and process balancing in a batch job later, or can I keep updating the balance real-time even when I reach the point where the orders and payments tables will not fit into the RAM?

Do I need to segment the orders and payments and "lock" everything before a given time, such as before last calculation? (I would rather not). I ask because then I could do a SUM WHERE DATE > [last lock timestamp] and update with UPDATE BALANCE = [balance] + [value for last lock] and prevent the problem from outgrowing resources.

I don't need a running balance for each transaction. That's all I can find results for when I try to research this problem. I just care about the total amount paid, the total amount ordered and the balance of accounts.

Why don't I calculate on the fly when I need the balance, you ask? Because one must be able to query the system and ask "Which of my (let's say) 50.000 accounts have a balance below 0?" - something we cannot do easily if we do not store the balance with every account.


texts:
I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the correct value. These functions have a common prefix 'fn_condition_' and receive an object of type 'my_table' as parameter.

As the number of functions that make the check is unknown, I decided to consult the Postgres catalog, from the table pg_catalog.pg_proc searching for functions with the prefix 'fn_condition_' and dynamically execute them with EXECUTE.

My problem is the correct form to pass the parameter for EXECUTE.

create or replace function test_conditions()
returns void as
$$
declare
    v_record my_table%rowtype;
    v_function pg_proc%rowtype;
begin
    set search_path = 'pg_catalog';

    for v_record in (select * from my_table where id in (1,2,3)) loop
        for v_function in (
            SELECT  p.proname
            FROM    pg_namespace n
            JOIN    pg_proc p
            ON      p.pronamespace = n.oid
            WHERE   n.nspname = 'operacional'
            and p.proname like ('fn_condition\\_%')
            order by p.proname)
        loop
         -- execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???
        end loop;
    end loop;
end;
$$
language plpgsql;

How to pass v_record properly in the commented EXECUTE command in the function above?

execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???

Example function:

create or replace function fn_condition_1(p_record my_table)
returns bigint as
$$
begin
    if ($1.atributo1 > $1.atributo2) then
        return 1;
    end if;
    return null;
end;
$$
language plpgsql;


texts: How to obtain row counts for all tables in Netezza?

texts: Is there a system table or view in Netezza that shows the number of records for each table?


texts: What would be the best way to store one valuer per hour per day per customer?

texts:
We have a table that stores a value per each hour per each per day per customer. A simplification of the table would be like this (removed some extra fields and the indexes):

CREATE table #timevalues (
dateinsert date,
customerid int,
hour0 int,
hour1 int,
....
hour23 int
)

The problem is that this table is huge, and altough we have an index with the dateinsert and customerid that allows us for a fast seek of the record, retrieving the record also takes time. Our issue is more with the speed and being future proof than with the space.
What would be a recomendation to improve this?
We were thinking about replacing the column format to store a json instead of the fixed columns, but the result would be less optimal I guess.
We regularly need to get stuff like:

Daily totals customer XXX in last month
Average per hour per day


texts:
I have a certain table in my OLTP database, which is bulk-updated by several users.
There is no way to know when they will update the table (somewhere between 5 times a day and once in a week).

The problem is, that the update does not cause the statistics to be automatically updated,
but it is a big enough update to cause SQL Server to use a poor query plan.
(the table has ~500k rows and the operation inserts/updates between 5k and 20k rows)

My question is how should i trigger a statistics update?
My thoughts:

A job which will run every 30 minutes and check for changes using rowmodctr. The job will then update statistics if necessary
A DML trigger which will check for changes and start a job when necessary. The job will update statistics

Of course the solution has to be server side, application changes are not welcome :)

One more thing is that I might need the same or similiar for more than just this one table so the solution has to be generic (use a modified-sp_updatestats with parameters for... everything?).


texts: Search every column in every table in Sybase Database

texts:
I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is very convoluted. I've been able to figure out most of the schema to gain access to the data I need, however, there are a few things still left to find. I know what they are called in the actual POS, but I can't seem to find them anywhere in the database (although I haven't actually looked through all 200+ tables).

I'm wondering if there's any way to run a query to search for a specific string in all the columns in all the tables. BTW, I'm using the "Interactive SQL" application that comes with the Sybase Database Client software to connect to the database.


texts:
why this is not work?? i want if i'm insert to this table the table in before i'm insert get decrement....

CREATE TRIGGER prosesqc2
AFTER INSERT ON quality2
FOR EACH ROW
BEGIN
INSERT INTO stock_barang
SET  idbarang = NEW.idbarang,
     lolos_qc_sfl=New.terima,
     dalam_proses=new.terima
ON DUPLICATE KEY
UPDATE lolos_qc_sfl=lolos_qc_sfl+New.terima
   and dalam_proses=dalam_proses-new.terima;
END


texts: How to use prepared statements within MySQL stored procedures?

texts:
I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable to the LIMIT clause. I have this stored procedure here:

DROP PROCEDURE IF EXISTS fixbalance;
CREATE PROCEDURE fixbalance (userid INT)
  BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE balance INT DEFAULT 0;
  DECLARE idcnt INT;

  SET idcnt = (SELECT COALESCE(COUNT(id), 0)
               FROM coupon_operations
               WHERE user_id = userid);
  IF idcnt <> 0 THEN
    WHILE i <= idcnt DO
      BEGIN
        SET @iter = i;
        SET @user_id = userid;
        SET @sql = CONCAT('SELECT id AS curid
                           FROM coupon_operations
                           WHERE user_id = ?
                           ORDER BY id ASC
                           LIMIT ?, 1');
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @user_id, @iter;
        DEALLOCATE PREPARE stmt;
        SET balance = balance + (SELECT points
                                 FROM coupon_operations
                                 WHERE user_id = @user_id
                                 AND id = @curid);
        UPDATE coupon_operations SET balance = balance;
        SET i = i + 1;
      END;
    END WHILE;
  END IF;
END;
|

This does not work - I'm not sure how to pass the curid.


texts: I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes into memory when it's possible. Am I right? Is there any method to tell postgres don't load indexes into memory.


texts: What encryption options are available in SQL server standard edition?

texts:
I'm trying to look into what kind of encryption I can do with SQL server standard edition from 2008 and later.

So far, I know that I can't use Transparent Data Encryption (TDE) as that's part of Extensible Key Management (EKM).

When I look up this topic I'm getting the result that column encryption is possible, but then I also find that cell level encryption is sometimes referred to as the same thing and that it is also part of EKM.

These are the sources from which I'm getting the contradictory information:

http://web.townsendsecurity.com/bid/72311/Encryption-Options-for-Microsoft-SQL-Server

http://www.experts-exchange.com/questions/27949443/Encryption-feature-of-SQL-server-2008-R2-standard-edition.html

Any clarity on this subject would be very helpful.


texts:
We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place,

Each table and column name should be meaningful. It should give information about the table or the column's purpose.
Table names should not exceed 27 characters and column names should not exceed 30 characters.
Table names are plural. For example: COMPANIES, EMPLOYEES, PERSONS, GENDERS, RELIGIONS, COUNTRIES, ...
Each table has a primary key as table_name_ID which is NUMBER(15).
table name is used as singular in the Primary Key column. Example:
the USER_REQUESTS table has a primary key as USER_REQUEST_ID.
If the table or column name exceeds the limits, a suitable abbreviation is used. Example: CMS_EXEMPTIONS is the table to keep content management system exemptions.
Tables that keep transactional data should end with _TRANS. Example: COMPANY_TRANS is the table that keeps company related transaction data.
Each table has 4 WHO column as :

CREATE_USER        NUMBER(15)
CREATE_DATE        DATE
MODIFY_USER        NUMBER(15)
MODIFY_DATE        DATE

Columns which are a foreign key relation are named as the primary key of the referenced table. Example: if a table has a reference to the COUNTRIES table, the referencing column is also named as COUNTRY_ID as the primary key of the COUNTRIES table.
If the meaning of the referencing column should be other than the 8th rule because of the first rule (meaningful names), meaningful name rule is applied. Ex: Both nationality and country columns have a relation to COUNTRIES table if it is used to keep a person nationality than it is named as NATIONALITY_ID.
If the table has more than one related to the same table they are given different names like BIRTH_COUNTRY_ID, PASSPORT_ISSUE_COUNTRY_ID
If a table is a relation table between to tables, the table name contains both table names.

To give meaningful names were always our first rule in naming.


texts:
mytable is an InnoDB table with (a, b) as the primary key.

Does this query check all table?

Select *
from    mytable
where   ( b='2' and c='3')

I want to find out if 'cluster' structure helps this query not check all table. (when I don't have all part of primary key in my where clause)

Would it be faster if I used MyISAM and a secondary index? Because In full table scan, It seems that Innodb caches a page for every record and so its not fast.
I've read about designing composite primary keys in an  article:
".....For the sake of example let’s assume two rows were added for a user whose user_id is 50 and they have been assigned with id=10000000 and id=10001000, where id is the primary key in this table. What sort of work the following query has to perform in order to return results?

SELECT * FROM messages WHERE user_id = 50

It finds index entires where user_id is 50. With the primary key values from the secondary index, it can then locate the physical rows. So InnoDB first seeks a row where id is 10000000. But since it does not operate on individual rows at this point, but rather on the blocks of records called pages, it locates the proper page on disk and caches it in the buffer pool. Only then it can extract the actual row, return it, and move on to the next one where id is 10001000. Since the distance between the two records is significant, it turns out that it is not on the same page that was just pulled into the cache and which holds the first row. InnoDB has to go back to disk to fetch another page, cache it, extract the row and return it.

In the above example at least two I/Os were issued. In a pessimistic case for a few hundred matching rows, when a user has that many messages, that could result in a few hundred I/O operations. Random I/O. Assuming as little as a few milliseconds for each operation, the query may need as much as one second to execute. It would use index properly, it would only pull a few hundred rows, but would still perform poorly. With the exception for flash technology any typical storage is rather slow, and also very difficult to scale, so such scenario should be avoided. ......"


texts: How to solve Error 927 'can not open database' while configuring DB mirroring

texts:
Steps I followed:

Create DB on primary.
Took full backup and log backup.
Restored full and log on secondary in NORECOVERY mode
(now tb shows restoring satus on secondary I guess this is because NORECOVERY mode).
Tried configuring mirror from primary.

I was able to configure it successfully with windows 2012 and SQL server 2012 using SSMS 17.6 but it's giving above error with Win2k16 and SQl server 2016


texts: Service Broker to Send Messages Specific Database bases based on critiera

texts: In Message Broker can you configure to use one queue but based on criteria send to a different Databases or is it a one to one relationship? I am tryibg to figure out if I should use Message broker or SSIS package.


texts: How to identify and remove the SQL table row which contains UTF 8 BOM characters

texts: I recently identified that some of the rows in our table contains UTF 8 BOM (Byte Order Marking) characters (ï»¿).. Is there a way to identify those rows and removing the BOM characters from those rows?


texts: MySQL Master-Slave replication foreign key constraint error

texts:
This is the cluster setup:

------       ------
| m1 |  ---  | m2 |
------       ------
  ||           ||
------       ------
| s1 |       | s2 |
------       ------

m1 and m2 run mariadb-server-10.1 version 10.1.18+maria-1~jessie.

m1 and s2 run mariadb-server-10.1 version 10.1.18+maria-1~wheezy.

Nodes m1 and m2 are in a Galera Cluster, and replication between these nodes works fine. There is also an arbitrator node running on a separate server.

s1 is a slave node to m1, and s2 is a slave node to m2. The replication to slave nodes is using classic MySQL replication.

Now, the issue here is that the slave nodes needed to be started from fresh state. This was the process to start the replication:

service mysql stop on slave node.
Empty /var/lib/mysql on slave node.
Run innobackupex -u user -p password on master node.
Run innobackupex -u user -p password --apply-log /path/to/backup on the master node.
Copy database backup files to slave server.
Run innobackupex --copy-back /path/to/backup on slave server.
Run chown -r mysql.mysql /var/lib/mysql/* on slave server to fix permissions.
service mysql start on slave server.

After this, I started mysql -u root -p password, and issued this command:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='m1', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.NNNNNN', MASTER_LOG_POS=nnn;
START SLAVE;

Values for NNNNNN and nnn were retrieved from xtrabackup_binlog_info file from the backup files.

Now, this process worked perfectly between m1 and s1. However, with m2 and s2, it generates an error message (I split the error message to multiple lines for better readability):

Could not execute Write_rows_v1 event on table db.table;
Cannot add or update a child row: a foreign key constraint fails
(`db`.`table`, CONSTRAINT `FK3EBA76781F6AACF1` FOREIGN KEY (`job`)
REFERENCES `table2` (`id`)), Error_code: 1452; handler error
HA_ERR_NO_REFERENCED_ROW; the event's master log mariadb-bin.000710, end_log_pos 1523526

I have tried to repeat the process again a couple of times without any success.

I have also issued RESET MASTER on m2 to reset the replication log file in case it had some corruption.

I am running out of ideas what is happening here and how I can restart replication correctly. Any ideas?


texts: postgresql select error: could not read block, Invalid argument SQL state: XX000

texts:
Postgres 9.6 on Windows 10

I'm creating a table filtered_table1 via a simple select operation on another table table1 (around 1.8 billion rows) and filtering it by some field

create table filtered_table1  as (
 select *
    from table1
    where epoch_time >= 1510227000000 and epoch_time <= 1510230600000
);

I get the error pasted below on running this query after sometime.

ERROR:  could not read block 4585496 in file "base/16393/1949151.34": Invalid argument
SQL state: XX000

I followed some suggestions especially the one given here where it was suggested to try setting zero_damaged_pages = on in postgresql.conf file. I continue to get this error even after doing this. Request help on finding and fixing this issue. Note: I have other tables in the same database and do not see such error while working with those.


texts:
Can anyone help me resolve the syntax in the following script? Here @Name and @DBname are declared above.

exec master..xp_cmdshell 'echo 'alter login QUOTENAME(select @Name) with default_database =  QUOTENAME(select @DBname) >> C:\test\test_script.txt''

Thanks


texts: Deadlock while updating rows and inserting new ones

texts:
I'm working with SQL Server 2012 Express Edition and I getting this error:
Transaction (Process ID 53) was deadlocked on lock resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.

I've added these two commands to show more information about it:
DBCC TRACEON(1204, -1)
DBCC TRACEON(1222, -1)

With these two commands I get this log. But I don't know how to find where the problem is.
Executing sp_lock I have identified that the problem is on table TRZIC.dbo.CODES and it has a PAG type.
On this table I'm updating rows and inserting new ones at the same time.
Any advice? How can I solve this deadlock?


texts: SQL Server, two instances, identical data structure and content, identical query plans, but higher reads and duration in one instance

texts:
The problem is following.
I have 2 SQL servers 2012 instances in two different virtual machines, let's call it DEV and PROD.
When running a very simple parameterized query returning one row from one table with identical data structure, content, and query execution plans, the profiler in PROD shows much higher reads (like 15 times) and duration (5 times) than in DEV. Note, that PROD machine is 2 times more powerful than DEV.

Some days ago, it was the same, but then something happened, and it all went like this. No changes to data structure has been done. Indexes and statistics are up to date in both. Execution plans are identical in DEV and PROD.

So, i exclude SQL server as a reason. Apart from it, what can cause such behavior?


texts: MySQL MyISAM REPAIR TABLE lost connection to MySQL server during query

texts:
So I've been building up over time a database, and unfortunately I was using MyISAM which didn't have any problems until the database became huge.

After massive CPU spikes, I created a script to OPTIMIZE all the tables, and ran it. And everything was fixed and working fine and not using nearly as much CPU. This was done through PHP. I also have a jailed shell that I can use to access the database.

The next thing I did was really stupid. I took this working database and decided to run a REPAIR operation on all the tables. For most tables the operation was successful and returned a success status, however it timed out and broke several of the tables. The remaining tables that weren't working show as 'in use' in PHPMyAdmin and when I try to perform any operation on those tables it says 'table is marked as crashed and last (automatic?) repair failed'.

A couple of the tables recovered fully by simply repeating the REPAIR operation. Over time by running REPAIR operations over and over I managed to recover most of those tables. However, there is one table which is still timing out always. I have access to run queries via PHP, PHPMyAdmin, or the jailed shell, all of which time out, as well as cPanel. The CHECK operation also times out. I am certain that the actual data is fine, and that the operations are simply being killed before they can finish by some higher-level restriction. (All of which I do not have access to change.) I'm pretty sure the data is probably even just fine and it's an in-use flag somewhere which was set because the operation didn't finish. Obviously the export function of PHPMyAdmin does not work in this scenario. What can I do to get this data off the server and attempt a proper recovery?


texts: MySQL help do I use Where not exist or Join? - Laravel

texts:
I have two tables as follows:

1) urls

    id    domain             created_at
------------------------------------------------
    1     google.com           2016-11-30 00:00:00
    2     test.com             2016-11-29 00:00:00
    3     example.com      2016-11-26 00:00:00

etc..
2) links

     id  urls_id   end_date              created_at            status
---------------------------------------------------------------------------
     1     2       2016-11-30 00:00:00   2016-11-30 00:00:00     Approved
     2     2       2016-12-01 00:00:00   2016-11-30 00:00:00     Approved
     3     2       NULL                  2016-11-30 00:00:00     Approved
     4     2       2017-01-01 00:00:00   2016-12-01 00:00:00     Approved
     5     2       2016-01-01 00:00:00   2016-12-01 00:00:00     Pending
     6     3       2016-11-24 00:00:00   2016-12-01 00:00:00     Pending
     7     3       2016-01-01 00:00:00   2016-12-01 00:00:00     Approved
     8     3       NUll                  2016-12-02 00:00:00     Pending
     9     3       2016-01-08 00:00:00   2016-12-01 00:00:00     Approved
     10    3       NUll                  2016-12-02 00:00:00     Pending

I would like to write a MySQL Query to return with these conditions:
one row per domain that follows these conditions:

1). end_date NOT NULL  AND
2). end_date > Carbon::now()   (I am using carbon) not expired AND
3). status is "Approved" AND
4). return latest end_date closer to present or now.

So, for example, it will look and find all "urls_id"= 2 and select ALL status "Approved" and look for end_date that is NOT NULL and not expired, but since there is one NULL then it won't return it, then move on to "urls_id"= 3 and we have 2 approved and both expired and there is no end_date NULL or not expired so we only will return that row of the latest end_date.

Here is an output

  id     domain          end_date_max
  ------------------------------
  9      example.com     2016-01-08 00:00:00

Here is what I have tried but it's selecting all domains (using Where not exists)

select * from urls
where not exists (
    select *
    from links
    where urls.id = links.urls_id
    and (links.status = "Approved"
        and links.end_date is not null)
    and (links.status = "Approved"
        and links.end_date > 2016-12-08 23:46:16)
)
group by urls.domain

How we can do this in one SQL query? In Laravel or just plain Raw query.

Thanks


texts: How do I know my Oracle database is correctly configured and running?

texts:
I've installed a fresh Oracle 19.3 on Linux CentOS 8.3. I used the install steps (manual install) from this official Oracle guide.
I tried to start the DB using the standard script:
[oracle@localhost admin]$ /home/oracle/scripts/start_all.sh
The Oracle base remains unchanged with value /opt/u01/app/oracle
[oracle@localhost admin]$ ps -aux | grep oracle
root        3309  0.0  0.1 137504  7532 pts/1    S    Apr19   0:00 su oracle
oracle      3314  0.0  0.0  25680  5632 pts/1    S    Apr19   0:00 bash
oracle      8756  0.6  0.5 224100 28768 ?        Ssl  01:59   0:00 /opt/u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle      8892  0.0  0.0  61604  4024 pts/1    R+   01:59   0:00 ps -aux
oracle      8893  0.0  0.0  12112  1052 pts/1    S+   01:59   0:00 grep --color=auto oracle

I see only one oracle process, the listner. I don't see a process named cdb1*, which is the name of the DB I installed.
I have a Basic client and a SqlPlus client installed. When I try to connect via SqlPlus I get this:
[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 01:12:36 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name: oracle
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4376
Additional information: 1804505411
Process ID: 0
Session ID: 0 Serial number: 0

I put tnsnames.ora at $ORACLE_HOME/network/admin:
[oracle@localhost admin]$ ls -l /opt/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/
total 8
drwxrwxr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rwxrwxr-x. 1 oracle oinstall 1536 Feb 13  2018 shrept.lst
-rwxr-xr-x. 1 root   root      391 Apr 20 01:29 tnsnames.ora

Here is the content of my tnsnames.ora (I'm really not sure about this, don't know what service name to use - I understood it is not the SID):
[oracle@localhost admin]$ cat /opt/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/network/admin/tnsnames.or
a
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.139.129)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.139.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

tnsping gives this:
[oracle@localhost admin]$ tnsping orcl 5

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-APR-2021 02:25:13

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.139.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (10 msec)
OK (10 msec)

So, I don't know if my DB is really running. I don't know if the tnsnames.ora is correct. I'm not a dba, I need this Oracle DB for a testing environment. Could someone guide me through this please?


texts:
I'm currently designing a SSIS package, which will do following:

Clear unwanted records in destination
Do ETL using stored procedures and put everything into a table prepared for export in source
Inserts data from source to destination

So the final step is pretty much to transfer whole table data from Source into Destination and I've got two options now:

SSIS Data Flow
Linked Server

Ideally I'd like to use Data Flow task in SSIS, but looking at performance comparison, it looks like Linked Server wins. I'm doing it that way:
In SSIS I do the following

Run a select statement using OLE DB Source
Insert into destination using OLE DB Destination
Takes about 4 seconds when deployed, about 10 seconds on my machine

Using Linked Server I do the following

Insert records into #Table querying remote server
Insert into actual table
Takes about 2-4 seconds

Here's query I'd use:
SELECT *
INTO #Table
FROM RemoteServer.MyDB.dbo.MyTable;

INSERT INTO dbo.MyTable
SELECT *
FROM #Table AS T;

I'm testing with relatively small data set, just about 14k rows (75 columns in total, most of them are FLOATs).
Both servers are under the same network.
I've configured connection managers in SSIS to use Microsoft OLE DB Provider for SQL Server with Packet Size of 32767 as suggested here:
https://gqbi.wordpress.com/2013/06/19/ssis-fastest-data-flow-task-item-for-transferring-data-over-the-network/
Am I doing something wrong, because I'd expect SSIS to run faster than Linked Server?
Is there another way to speed performance up in SSIS package and win against Linked Server?


texts: Want to timeout or skip SQL Server index rebuild statements if taking too long

texts:
When running index rebuild scripts overnight on large and highly fragmented databases we frequently come across indexes that stop the job because they seemingly never complete their rebuild. The job is halted and in the morning we have to manually cancel the statement, waiting forever for the rollback, causing downtime. We can remedy these occasional indexes my manually dropping and recreating.

Is there a way to automatically timeout an index rebuild statement if there is little progress, so the script can carry on to the next rebuild statement?

Our script is of the form:

ALTER INDEX ALL ON [Table1] REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON [Table2] REBUILD WITH (FILLFACTOR = 90)
ALTER INDEX ALL ON [Table3] REBUILD WITH (FILLFACTOR = 90)..


texts:
Please take a look at this ER Diagram that I started.
I just don't know if I've set it up correctly (I have not made any relationships yet). I just want to know if I have the appropriate "Tables" or do I need to delete or add new ones...

Read the directions/requirements in the "Grey Box."


texts: Does using a view over standard queries and table indexes improve performance on large databases?

texts:
I recently had a discussion with someone who said that using a view on a database with a large number of tables and (>1,000000) rows would allow for much better performance.

I don't see how this would be the case as a view is just a stored query definition as far as I am aware.

This was in relation to a web based app which allows for the filtering of products based on type, brand, spec, etc... and needs to be very performant.

Given that queries are effectively hard coded into the app, its not like a person needs to create each query manually so I'm thinking that there should be no difference between using a view or running a standard query againts the needed tables that uses joins...

I had suggested that using indexes on the tables would allow for the performance needed but they said that when tables have more than around 200k rows, indexes are useless in terms of speeding things up...

Is this correct about table size removing the effectiveness of indexes and are there any performance increases to be had from using views over standard queries?

Any help will be much appreciated.


texts: How to select unique records in PostgreSQL without using Distinct clause

texts:
I have a query which selects number of records for each house.

SELECT
   -- 3 rows
   a.house,
   -- 12 rows (for each house)
   a.id,
   -- for each ID (there are 4 repeating records)
   -- out of these 4 records, only 1 is unique while remaining are
   -- repeated records
   a.points
FROM records a
group by a.house
order by a.house, a.id

Above query returns 144 rows (Here it is how: 4 repeatation for each number in ID column thus 12 IDs of one house returns 48, so 48 x 3 = 144 rows) . The third column contains 4 records (for each number in ID column) in which there is only 1 unique point for each house just like ID of the house. Thus, the output should give 12 (IDs and points) x 3 (houses) = 36 rows. The desired sample output would be:

house rows points
1      1    1
1      2    2
1      3    3
1      4    4
1      5    5
1      6    6
1      7    7
1      8    8
1      9    9
1      10   10
1      11   11
1      12   12
2      1    1
2      2    2
2      3    3
2      4    4
2      5    5
2      6    6
2      7    7
2      8    8
2      9    9
2      10   10
2      11   11
2      12   12

How can I remove these repeating records from third column without using PostgreSQL Distinct clause?


texts: mongorestore failure: error writing file "/var/lib/mongodb"

texts:
I want sync my server db with my local db;

dump db

  mongodump --db [db name]

copy dump folder into my local
restore dump on my local

  mongorestore --db [db name] [dump folder path]

I have 4 dbs I can do this successfully for 3 of them but I got

mongorestore error: failure writing file /var/lib/mongodb

when I restore 4th db

There is enough room on the file system. I restarted the server and repeated the process and I don't get this error anymore. However, sometimes I get:

failed: restore error: cohortV5.orders:
error restoring from dump/cohortV5/orders.bson: insertion error: EOF

and sometimes I get:

Failed: restore error: cohortV5.orders: error creating indexes for cohortV5.orders:
createIndex error: no reachable servers


texts: How to take grade letter from marks range from grade table?

texts:
Grade Table is:

create table Grade
{
grade_letter char(1)
lowerlimit int(20)
upperlimit int(20)
};

insert into Grade (grade_letter, lowerlimit, upperlimit)
    values (
      ('A', 85, 100),
      ('B', 70,  84),
      ('C', 50,  69),
      ('F',  0,  49));

Suppose some another query returns marks.

Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hard-coded and calculates the grade based on the values given in the table only.


texts: Postgresql 9.3: Add Unique constraint using an existing unique index

texts:
I would like to add a unique constraint to a DB field in one of my production databases. Unfortunately, the following statement is blocking:

ALTER TABLE package ADD CONSTRAINT unique_package_id UNIQUE (package_id);

Reading the relevant part from the PostgreSQL manual:

  Note: Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time. To do that, create the index using CREATE INDEX CONCURRENTLY, and then install it as an official constraint using this syntax. See the example below.

I tried:

CREATE UNIQUE INDEX CONCURRENTLY package_tmp_id_idx ON answer (package_id);

Followed by:

prod_db=> ALTER TABLE package ADD CONSTRAINT
          unique_package_id UNIQUE (package) USING INDEX package_tmp_id_idx;

Which failed with:

ERROR:  syntax error at or near "package_tmp_id_idx"
LINE 1: ...T unique_package_id UNIQUE (answer_id) USING INDEX package_tmp_id_idx...
                                                             ^

I've tried playing with the syntax, but could not get it to work.

What's the right syntax to create a UNIQUE constraint using an existing UNIQUE index?


texts: Best way to store country and city information

texts:
I always wondered what is more efficient between two. There is one "main" table where I store all user related informations. Also, there are two more tables, country and city tables. city table contains column country_id so I can relate to which country this city belongs.

Now, the main question : should I store only city_id in user table and query for country name using country_id from city table or to store both, city_id and country_id ?

======

Other thing is related to counting. If have some fourth table, like message, and I store ID of user who send message, ID of user who receive message, text, etc. Now, if I want to know how many messages some user sent, I can query for messages where sender=ID and count rows, but I used another approach. I created column count in user table and whenever user send message, I increase this number. Is this approach good and if no, is the first one efficient since that table can have billions of rows.


texts: SQL Server Licensing for training, small scale development

texts:
I am currently undertaking SQL SERVER 2012 Training for the exam 70-461. I currently have SQL SERVER 2008 running on my laptop but I'm looking to upgrade to 2012 and I'm having trouble trying to get together the licencing information needed to purchase what I need. I'm running Windows 7 (64 bit) and want to run the database on the laptop itself.
They have asked me:
·        SQL Server Edition. Standard or Enterprise? Standard
·        Licencing Model. Core or processor?
·        If ‘Processor’ do you need CAL licenses quoted? (CAL =Client Access License)
·        If ‘Core’ how many cores?
Thanks


texts: if col1 has the same data, copy col2 data


texts: On 03/19 one user had deleted the data from 3 tables from one of the databases that we have. Now we have the full backup of that database each day. What we want to do is to restore the deleted data and then get all the data entered after 03/19 as well for those tables. Any suggestion on how to do that?


texts:
i have two table i.e tbl_Contactgroup(id,name) and tble_contactlist(id,name,address,telephoneno,mobileno,groupid)
my question is how to retrieve all the contact list value from the tble_contactgroup by using extracting last inserted value from the tbl_Contactgroup where id in tbl_Contactgroup and groupid in tble_Contactlist are related to each other ie foreign key


texts:
I know about the exists feature,  but that does not help me with the given situation. What I want is:

Check if some row exists, and if exists then check for an another row if    row exists.

Here's the sqlFiddle.

Description:

A location table:

| ID |   NAME | ADDRESS |
|----|--------|---------|
|  1 | India1 |  (null) |
|  2 | India2 |  (null) |
|  3 | India3 |  (null) |
|  4 | India4 |  (null) |
|  5 | India5 |  (null) |

Then a location_flag table:

| ID | LOCATION_ID | FLAG_ID | VALUE | PARENT_ID | DELETED |
|----|-------------|---------|-------|-----------|---------|
|  1 |           1 |       1 |   YES |    (null) |  (null) |
|  2 |           1 |       2 |   YES |    (null) |  (null) |
|  3 |           2 |       1 |   YES |    (null) |  (null) |
|  4 |           2 |       2 |    NO |    (null) |  (null) |

What I want is to get all locations except location with id=2.

In procedure, it'd be like:
if(locationWithFlag1='YES'){
 if(locationWithFlag2='YES'){
//make this row to be appear in result
}
}
else{
//make this row appear in result
}

Is it possible without bringing in plpgsql?


texts: How to pass output of one stored procedure to the input of another

texts:
I have a stored procedure which outputs @billingInvoiceIDOut. I want to use it as input in my next stored procedure. Can I just do in my second stored procedure

INSERT INTO table ( billingID ) VALUE (@billingidout)

My first stored procedure:

BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        SET NOCOUNT ON;
        IF NOT EXISTS(SELECT billingInvoiceID
                      FROM tblBillingInvoice
                      WHERE billingInvoiceID = @billinginvoiceID)
        --  IF @billinginvoiceID = NULL
        BEGIN
            INSERT INTO [dbo].[tblBillingInvoice
                (invoicenumber,versionnumber,patientID,date,
                officeID,userID,[tblBillingInvoice].status)
            VALUE ((SELECT IDENT_CURRENT('tblbillingInvoice')),1,@patientID,GETDATE(),
                    @officeID,@userID,1)
            SET @billingInvoiceIDOut =  (SELECT IDENT_CURRENT('tblbillingInvoice'))
        END
    END TRY

or do I have to pass this parameter in exec statement as input, as I am running both stored procedures in one master stored procedure?


texts: How to load balance mongodb replica cluster

texts:
I have created a mongod replica cluster of 2 nodes , mongo1 and mongo2. to load balance I have added following rule in my haproxy

listen mongo_replica_cluster
        bind *:27017
        mode tcp
        balance roundrobin
        server mongo1 10.2.0.12:27017 check inter 10s fall 3 rise 99999999
        server mongo2 10.2.0.11:27017 check backup

but if the mongo1 goes down mongo2 becomes the primary but even if mongo1 comes up mongo2 remains primary but ha redirect all traffic to mongo1 which is slave now. I want to redirect all my traffic to only the primary node.


texts: How to insert data in table with constraint on column? - in parallel queries

texts:
I have an Oracle database with a table with a unique field in it:

create table t(n number, CONSTRAINT n_unq UNIQUE (n));

Then from a session-1 someone is trying to insert some data:

insert into t(n) values(1);
-- no commit yet

and in session-2 someone is trying to do the same:

insert into t(n) values(1);
-- hangs here, wating for commit/rollback in session-1

What is the correct way to overcome such pity situations?
Session-2 seemes trapped, it may hangs for a vary long time 'being caught' by session-1.

I know the way with sequences, but what if the task is more complicated, say: to write a function which checks if a value exists and return it, and if not exists then INSERT (and here is the trap just inroduced, when running the same code in parallel) and then return it.

-- Ex:
create or replace function f(pn number) return number
is
  pret number;
begin
  select n into pret from t where n = pn;
  return pret;
exception when no_data_found then
  insert into t(n) values (pn);
  -- hangs here in parallel calls f(<new_value>)
  return pn;
end;


texts: How SQL Server knows when to implicitly convert the values

texts:
Following my previous question: Implicit conversion does not affect performance

I used below simple query

select count(*)
from fpc
where SKey in ('201701', '201702A')

SKey is of type int and I have non clustered columnstore index on it.

obviously I cannot run this because the second value is not a number. so I hit Ctrl+L to see estimated execution plan and I see an interesting thing in Predicate property:

[mydb].[dbo].[fpc].[SKey]=CONVERT_IMPLICIT(int,'201702A',0) OR
[mydb].[dbo].[fpc].[SKey]=(201701)

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A'

what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not?


texts:
Can I create a HA postreges cluster using two different nodes via NFS?

According to the postgresql 9.4 official doc this isn't a good idea but I don't find any other info on the web.


texts: Multiple SQL Server installations on ONE server?

texts:
Across our business we have several low-volume SQL Server installations; added as we have developed/acquired new products for the business. Each one is located on a virtual server and for argument sake let's say there are 5 such servers and each has been allocated 8Gb RAM and 4 CPUs ... in my mind, I think that it would make more sense to collated these onto ONE server, beefed up with memory/CPU.

I see the PROs of this being that it makes it easier to keep track of the servers, to maintain them, upgrade (when required), assigning permissions would be easier and the performance may be increased (by adding additional RAM/CPU). Presumably, we could also have the server replicated/load balanced to give additional resilience.

Am I correct to think that?

What would be the considerations AGAINST doing this?


texts: Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

texts:
I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date):

| COLUMN_NAME   | TYPE        |
|---------------|-------------|
| serial        | varchar     |
| lot           | varchar     |
| model         | varchar     |
| line          | varchar     |
| machine       | varchar     |
| datetimestamp | timestamptz |

Example values:

| serial | lot        | model  | line | machine | datetimestamp       |
|--------|------------|--------|------|---------|---------------------|
| 1      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:39 |
| 2      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:42 |
| 3      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:45 |

I have created a function to get data from table model201707 and insert it into table tb_moldsummary:

| COLUMN_NAME   | TYPE      |    |    |     |
|---------------|-----------|----|----|-----|
| machine_name  | varchar   | NN | PK |     |
| model         | varchar   | NN | PK | UNQ |
| mold          | varchar   | NN | PK | UNQ |
| cavity        | varchar   | NN | PK | UNQ |
| totalshots    | int4      |    |    |     |
| datetimestamp | timestamp |    |    |     |

Example values:

| machine | model  | m_id | cav_id  | totalshots | datetimestamp       |
|---------|--------|------|---------|------------|---------------------|
| 1       | sample | 16   | F22_F23 | 3          | 2017-02-28 07:22:45 |

Function:

CREATE OR REPLACE FUNCTION insert_data_func()
RETURNS TRIGGER AS
$BODY$

EXECUTE 'INSERT INTO tb_moldsummary '
    ||  'SELECT NEW.machine, NEW.model, split_part(NEW.lot, ''_'', 1), '
    ||  'SUBSTRING(NEW.lot FROM (POSITION(''_'' in NEW.lot)+1) FOR LENGTH(NEW.lot)), COUNT(lot), NEW.datetimestamp '
    ||  'FROM model' || to_char(CURRENT_TIMESTAMP, 'YYYYMM')
    ||  ' WHERE lot = NEW.lot AND machine = NEW.machine GROUP BY machine, model, lot '
    ||  'ON CONFLICT ON CONSTRAINT tb_summary_unique '
    ||  'DO UPDATE SET '
    ||  'machine = NEW.machine, '
    ||  'totalshots = tb_moldsummary.totalshots + 1, '
    ||  'datetimestamp = NEW.datetimestamp ';

RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insert_data_trigger
AFTER INSERT ON model201707
FOR EACH ROW
EXECUTE PROCEDURE insert_data_func();

Error:

[Err] ERROR:  missing FROM-clause entry for table "new"
LINE 1: INSERT INTO tb_moldsummary SELECT NEW.machine, NEW.model, sp...

I already checked the following links, but I'm confused:

PLPGSQL STATEMENTS EXECUTING DYNAMIC SQL

insert-record-into-a-table-in-a-trigger-function

What's the correct syntax for this dynamic UPSERT?


texts: Calculate row size and max row size for a table

texts:
Problem:

Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate enough.

What actually required is the number of bytes according to the definition of the table for innodb only and collation could also be considered as utf-8-general-ci

For example a table test is as following

  create table test
  (
  col1 varchar(25),
  col2 int,
  col3 varchar(3),
  col4 char(15),
  col5 datetime
  );

Now would require to know the total row size that can be accumulated in one row according to the types of columns in the table.

Found a some sort of similar solution in MSSQL but need its MySQL version

Script to estimate row sizes for any table

Any help is much appreciated.


texts: Interactive INSERT / UPDATE function to implement UPSERT

texts:
I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 record successfully added" or " Record with PK [wherever] was successfully updated" or "An error occurred, field X cannot be null" or "any error that it gets".

I want to avoid an expensive CURSOR and an even more expensive EXCEPTION clause. I also want it to be optimized for speed. The function should consider concurrency because there will be more than 50 clients connected to the database at the same time, each updating/inserting (calling the same function). This is what I have currently:

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test)
  RETURNS text AS
$BODY$
DECLARE
myoutput text :='Nothing has occured';
BEGIN
    update netcen.test set
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
        myoutput:= 'Record with PK[' || myobj.testkey || '] successfully updated';
        RETURN myoutput;
    END IF;
    BEGIN
        INSERT INTO netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);
    myoutput:= 'Record successfully added';
    EXCEPTION WHEN null_value_not_allowed THEN
        RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    WHEN not_null_violation THEN
        RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    WHEN unique_violation THEN
    RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    END;
    RETURN myoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I know this function can be simplified to a simple and shorter writable CTE function of SQL language. But those have a race condition. And I don't know how to capture errors and report the operation done as output from it.

Can this function be simplified or complicated but handle all my concerns?


texts: How can a connection for one DB block a connection to another DB in SQL Server?

texts:
We have an interesting scenario.  For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's living on that server.  When looking at SQL Server for answers -- we found that we had many SUSPENDED connections and lots of BLOCKING, via the BLKBY column from sp_who2.  To fix the issue, we would first save the sp_who2 output for later analysis, then kill all the blocked/suspended PID's.  This apparently freed things up and transactions were able to continue flowing normally.

After analysis, we found 2 things:

We were experiencing DEADLOCKS.  Connection 1 was blocked by Connection 2, but Connection 2 was also blocked by Connection 1.  SQL Server seemed unable to choose a victim so presumably queries started queuing up, waiting.
The PID's for the deadlocked queries were from DIFFERENT DB's!

Not sure if this matters, but -- the issues were solved after looking at performance counters and seeing that we essentially were suffering from severe MEMORY PRESSURE (PLE, Lazy Writes, etc..) so we doubled the RAM and issues went away.

We are at a loss in understanding how queries from seemingly UNRELATED DB's can block each other.  One theory is that both unrelated queries were waiting for the same PAGE in memory (cache)??  Any insight is welcome!


texts: Why doesn't \COPY work when called from a PL/pqSQL function?

texts:
I am trying to use the \COPY function to fill the database from text files on the client side. I am using the Postgres interactive terminal (psql) to do this task.

I wrote the following script and saved it to test.sql:

CREATE FUNCTION copy_stuff_to_db(path text, encoding text) RETURNS void
AS $$
BEGIN
    \COPY table1 FROM path || 'table1.asc' WITH ENCODING encoding;
END;
$$ LANGUAGE plpgsql;

PERFORM copy_stuff_to_db('/home/martin', 'ISO 8859-1')

I execute it like this:

psql --port 5432 --host localhost -d mydb --username postgres -f test.sql

But I get an error:

psql:test.sql:12: ERROR:  syntax error at or near "\"
LINE 4: \COPY table1 FROM path || 'table1.asc' WITH DE...
        ^

At first I thought that the terminal doesn't do PL/pgSQL and that I will have to put my files and run the script serverside. However, when I call the function directly (i.e. I modify the previous script to):

\COPY table1 FROM '/home/martin/table1.asc' WITH ENCODING 'ISO 8859-1';

Then everything works. It seems that the \COPY function does not work when called from another function for some reason.

What am I doing wrong?


texts:
Cross Database Views:

Hi,
We have two databases

Database A: Contains dbo.Customerid table,

Database B: Contains dbo.Customerdescription table,

Database Reporting: has a schema for consultant vendors (vdr), so they can join, but not see security information, (SSN and driverlicenses, etc)

I am seeing a lot of information: What is the optimal, secure way to give access to the consultants for cross-database views?

I am seeing error messages,
"The SELECT permission was denied on the object DatabaseA.dbo.customer, etc"

create table DatabaseA.dbo.Customer
(
       Customerid int primary key,
       Customersalescode varchar(25),
       Membersince datetime
)
create table DatabaseB.dbo.CustomerDescription
(
       CustomerId int primary key,
       Firstname varchar(255),
       LastName varchar(255),
       SSN int,
       Driverlicensenumber varchar(30)
 )

create view vdr.CustomerReport
as
select
    cust.CustomerId,
    cust.Membersince,
    cds.FirstName,
    cds.LastName
from DatabaseA.dbo.Customer cust
inner join DatabaseB.dbo.Customerdescription cds
    on cust.Customerid = cds.customerid


texts: Schedule a job - Mongodb - Run a view every day

texts:
how is MongoDB not able to schedule queries?

I would like to know if it's possible to run a single insert every day:

use Database

var documents = db.Viewname.find({"modifiedOn":{"$gte": new Date(ISODate().getTime() -1000*86400*1)}})
use Second_Database_to_insert
documents.forEach(function(x){db.Collection_inserted.insert(x)})

is there a way to run a script connecting on mongo? I don't want to use more 3rd party tools because this database (not the database itself, but the support we have) is already very expensive.

With sql server we can easily create a job and schedule it to run every day.

Mongo 4.0 on windows ( actually I'm running noSQLBooster on my windows. mongo itself is on the cloud on a linux machine. we dont have access directly to this linux machine, only via nosqlbooster. and we have ATLAS)


texts: How to model a car (year, make, model, trim)?

texts:
I'm trying to design a database to represent cars (specifically: year, make, model, trim) for a car dealership application. Based on my understanding of the data, I've come up with the two database designs below:

Example data:

Year:  2013
Make:  Nissan
Model: Murano
Trim:  SL

I think the top design is more correct than the bottom design - but I'm not 100% sure.

The plan is that for every vehicle added into the car dealership's inventory, I would assign that inventory item a trim_id (which would have details about the vehicle's standard features, options, etc.). The inventory item would then just need to select which options apply to it, specify odometer reading, condition, price, etc.

The model would also need to be able to support queries like:

List all make for a particular year
List all model for a particular make
List all trim for a particular model

Is the design in the right direction? Normalized?

Database Design Cars-Models / Car Classifieds - this puts the year column with the model. I'm not a car buff, but I don't think this is right. I think the trim is associated with the year?


texts:
I have a query that is working but I figure I could enlist some help to make it more efficient. However before I show the query I would like to get a few answer on how others would do this.

What the query is supposed to do is to select data from a table, however some of the data is calculated within the SELECT statement. One of those calculations is the retreival of data from the next row. For example, I select a date from the current record, but I also want to know the next row's date as well.

Sample data:

=======================
date        |   comment
=======================
2013-01-01  |   comment
2013-03-03  |   comment

Desired output:

======================================
date        |   leadDate   |   comment
======================================
2013-01-01  |   2013-03-03 |   comment
2013-03-03  |     null     |   comment

Right now  I am using a stored function and a cursor to get this info and I tried using a LEFT JOIN which messed up my variables I am using.

I just wanted to get input from others on how they would do this.


texts: Inserting a list of emails into multiple rows on the same column

texts:
I have a list of emails of staff members and would like to:

insert them into multiple rows on the same column (single column only for emails)
the same question above but would like to replace them with old ones.


texts:
I have converted a SSIS package built in VS2008 to open in VS2010.

I have a Derived Column component that fails validation. It works fine in VS2008.
Here is the Expression used in Derived Column...

FINDSTRING(
    SecurityDescription,"\n",1) > 0
    ? SUBSTRING(SecurityDescription, 1,
                FINDSTRING(SecurityDescription,"\n",1) - 1)
) : SecurityDescription

Below are the errors when I run the package...

Error is:

  [Derived Column [36]] Error:
  Attempt to parse the expression
  "[FINDSTRING](#102,"\n",1) > 0 ? [SUBSTRING](#11898,1,[FINDSTRING](#11898,"\n",1) - 1) : #102" failed and returned error code 0xC00470A3.
  The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

I have double checked and SecurityDescription is a valid column. It works fine if I just add SecurityDescription. Not sure what is wrong with the FINDSTRING code.


texts: How do I create a table with a column that uses a sequence?

texts:
I have the following

CREATE TABLE [MyTable]
(
    [ID] [bigint] PRIMARY KEY NOT NULL,
    [Title] [nvarchar](64) NOT NULL
)

CREATE SEQUENCE MyTableID
    START WITH 1
    INCREMENT BY 1
    NO CACHE
    ;
GO

I want to insert new records on MyTable and set the ID to the next value of the sequence. How can I do it? A trigger perhaps, or is there some other way? How?

As I am using SQL Server 2012, I don't want to use Identity because of the gap bug.


texts: Azure Data Factory 'Failed Validation' with Folder does not exist

texts:
I uploaded whole set of .txt files in my blob storage with folder structure Year/Month/day.

I circled the folder structure.

Then in the pipeline I have following dataset created.

 "type": "AzureBlob",
        "linkedServiceName": "blob-store",
        "typeProperties": {
            "fileName": "{Day}.csv",
            "folderPath": "adf-data/sales/{Year}/{Month}",
            "format": {
                "type": "TextFormat",
                "columnDelimiter": "\t",
                "firstRowAsHeader": true
            },
            "partitionedBy": [
                {
                    "name": "Year",
                    "value": {
                        "type": "DateTime",
                        "date": "SliceStart",
                        "format": "yyyy"
                    }
                },
                {
                    "name": "Month",
                    "value": {
                        "type": "DateTime",
                        "date": "SliceStart",
                        "format": "MM"
                    }
                },
                {
                    "name": "Day",
                    "value": {
                        "type": "DateTime",
                        "date": "SliceStart",
                        "format": "dd"
                    }
                }
            ]
        },
        "availability": {
            "frequency": "Day",
            "interval": 1
        },
        "external": true,
        "policy": {}
    }

When I run the pipeline I am getting an error with folder does not exist.

Question:
What can I do to troubleshoot this further or fix?


texts: Is it unwise to run replication on same physical server?

texts:
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into is that we're already maxed out on power at our datacenter. So adding another physical server is not an option.

Our existing database server is fairly under-utilized as far as cpu (load averages never really get above 1 on a quad-core). So the leading idea is to toss in some new drives and double the memory (from 8GB to 16) and run a second mysql instance on the same physical machine. Each instance would have separate disks for the database.

Is there anything wrong with this idea?

Edit (more info):
I've (luckily) never had anything bad enough happen to take down the server, but am trying to plan ahead. We of course have nightly backups that we could recover from. But I figured having the redundant data on separate disks would provide a quicker solution if the master server's drives failed (obviously not if the entire machine goes out).

As for the reporting aspect, any tables we would report off of are MyIsam. So doing expensive reads on the same tables that are being written to can bog down the server. My assumption was having a slave server to report off of wouldn't affect the main server as long as we threw enough RAM at it (since cpu load hasn't been an issue yet).


texts: Getting the database from synonym base object

texts:
Risking asking something idiotic, but not easily got by Google-fu, but how to query the database id (and other information) from the base object of an synonym?

The synonym in question is an remote one (pointing to a table on another DB on the same server). The base object itself is not fixed: is the same table on different DB each execution of procedure.

I would like an non-specific method (example: don't want to rely on parsing the base_object_name column on sys.synonyms), but I'll understand if I'm told that's the only choice.

And since it begs the question: concurrency is not an problem, only one client will call that procedure.


texts:
I'm writing a system that allows users to store values against each week in October and November and each day in December - starting from the first Monday. I'm holding the raw data entered by the users in a transactional set of tables (3NF I believe). Administrators will be pulling regular reports of that data. As a result, each time the user makes a save I want to extract the data into a denormalised MI table, to make the reporting faster, and help reduce locking on the transactional tables.

Bearing in mind that different years have different numbers of weeks in Oct and Nov, and different number of days in Dec (because we start from first Monday), what is a good design for the MI table? So, something like:

OctW1
OctW2
..
NovW4
NovW5
DecMon1
DecTues2
...
DecMon5
DecTue5

I've considered:

creating a separate table for each year (tblMI_2012, tblMI_2013 etc), so the schema for each year is only defined once, and all the fields will be populated on each row.
creating a column for each possible value (so 31 fields for all the December dates) and only populating them when saving values for the respective calendar year.

Which pattern is the best?

Also, should I be also saving the date with the value (effectively doubling the column count)

OctW1
OctW1Date
OctW2
OctW2Date
etc

Apologies if this is really obvious, my first foray into creating an reporting table.


texts: Does ordering by auto-incrementing PK ensure chronological order?

texts: Is it safe to assume that ordering by an auto-incrementing PK in Postgres will order the records chronologically, from a creation standpoint? I have a many to many relationship, from which I don't need to track anything else except the relationship itself and some form of order of creation. I am trying to decide whether I need to incorporate a timestamp column for this, or whether I can reutilize the existing auto-incrementing PK column for that same purpose.


texts:
I have a table employee in this table i have id company_name employee_id

1 abc  2015_acb_1
2 abc  2015_acb_2
3 abc  2015_acb_3
4 abc  2015_acb_4
5 xyz  2016_xyz_9
5 xyz  2017_xyz_2
5 xyz  2016_xyz_3
5 xyz  2019_xyz_5

I want to select unique string such as from employee table

_abc_
_xyz_

i have try select employee_id where employee_id like %_%; its not giving as i expected


texts:
Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general?

mysql structure as follows:

inventory
    -inventory_id
    -inventory_type_id
    -inventory_name

inventory_type
    -inventory_type_id
    -inventory_type_name

warehouses
    -warehouse_id
    -warehouse_name

aquirings
    -aquirig_id
    -date
    -warehouse_id

aquiring_items
    -id
    -aquiring_id
    -inventory_id
    -quantity

deliveries
    -delivery_id
    -date
    -warehouse_id
    -warehouse_from_id

delivery_items
    -id
    -delivery_id
    -inventory_id
    -quantity

invoices
    -invoice_id
    -date
    -client_id
    -warehouse_id

invoice_items
    -id
    -invoice_id
    -inventory_id
    -quantity


texts: node-mongodb-native driver only reads from primary, regardless of URI readPreference

texts:
I have a mongodb replica set of 3 nodes, with the primary and one secondary located in the US, and another secondary in Western Europe. I'd like my nodejs application to read from whichever replica is nearest. I'm using the following connection URI to do this:

mongodb://<user>:<pass>@<host1>,<host2>,<host3>/<db>?replicaSet=rs0&readPreference=nearest

However, the application always ends up reading from the primary node in the set. Almost all of the queries are aggregations and I'm not doing any writes at all. All of the nodes in the replica are healthy and reachable according to rs.status().

I've also tried using readPreferences of 'secondary' and 'secondaryPreferred' but all have shown the same behaviour.

Am I missing something here?


texts:
I'm using workbench to create a table. This script is giving me a problem. It is saying that there is a syntax error new ').

DROP TABLE IF EXISTS `waitronmain`.`managers` ;

CREATE  TABLE IF NOT EXISTS `waitronmain`.`managers` (
  `manager_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `restaurant` VARCHAR(45) NOT NULL ,
  `location` VARCHAR(45) NULL ,
  PRIMARY KEY (`manager_id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `waitronmain`.`waiters`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waitronmain`.`waiters` ;

CREATE  TABLE IF NOT EXISTS `waitronmain`.`waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`waiter_id`) ,
  INDEX `manager_id` () ,
  CONSTRAINT `manager_id`
    FOREIGN KEY ()
    REFERENCES `waitronmain`.`managers` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The line causing the problem is this:

INDEX `manager_id` () ,

Any ideas? I'm at a loss, the code was generated from an ERD model I made within Workbench so I'm suprised it has a syntax error at all. I got this error code when executing:

  Error Code: 1064 You have an error in your SQL syntax


texts: Performance in replicating table between servers

texts:
I'm using SQL Server 2014 and I have two databases, in different region of the world. One in Brazil and the another in China. The databases are exactly the same, except by the ID column, with they have different ranges.

These two databases, they need to communicate to each other the records that have been inserted, deleted and updated. For that, I created a column named UpdatedDate in each table, and after each operation this column is updated with the GetUtcDate(), this way, I can know with record I need to update.

Each minute, I run a task in each server that compared both databases.

The problem is, some tables have many records (Around 500k), and when the server is updating those tables, the application gets terribly slow.

The databases are totally separated and have no VPN connection, because of that that I'm using SQL Server tasks.

Here it's a example of the task that runs the update:

Update
    LocalTable
Set
    LocalTable.RecordA = RemoteTable.RecordA,
    LocalTable.RecordB = RemoteTable.RecordB,
    LocalTable.UpdatedDate = RemoteTable.UpdatedDate
From
    MyTable LocalTable
    Inner Join [RemoteServer].MyDatabase.dbo.MyTable RemoteTable On LocalTable.ID = RemoteTable.ID
Where
    LocalTable.UpdatedDate < RemoteTable.UpdatedDate;

It's there a way to improve the performance? I've thought in limiting the RemoteTableUpdatedDate to get only the changes from the last three days.

I've also though in creating an index with the UpdatedDate and the ID, including the rest columns... Bu I don't have very much expertise in SQL Server.

Thanks for the help!


texts: SQL TDE and Certificate Handling Recommendations

texts:
I've seen the information on this post: But I don't think it answers this particular question/concern:

I have multiple SQL servers with multiple databases. We want to use TDE on a few of them.

Should I use one certificate for all TDE use, or one certificate for each database? Or maybe another way?
What would be the reasons for choosing one over the other?

If using one cert for all databases, if the cert is 'compromised' then all databases that use it could be decrypted by an attacker? And if we use one certificate per database, would that gives us a level of isolation?


texts: At which point does SQL Server validate data for insertion

texts:
I have a query to insert data from one table into another. One of the source columns is larger than the destination table's, although the actual data selected in the query isn't. So e.g. the source table is varchar(25), the destination table is varchar(10), and the query selects records that do not exceed 10 characters for that specific column.

My initial query failed with a truncation error, but when I rewrote it selecting the same records but changing the where clause i.e. to use WHERE IN and filtering on a cte it ran successfully.

i.e instead of doing

select * from tbl where col1 = 1

I did:

with cte as (select 1) select * from tbl where col1 in (select 1 from cte)

I understand that this is a bug in SQL Server, still I am wondering at which point does SQL Server validate the data for insertion. At which point is the error thrown?


texts: SQL Server on linux unable to export to bacpac

texts:
I'm trying to export my database to a .bacpac file.  I get the following error:

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The command I'm using is:

/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:localhost /SourceDatabaseName:MyDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac'

The database I'm trying to back up is on a different ssd than my mssql installation.  I don't know if that makes a difference.

I've been googling around for how to fix this and most people using windows had their similar problem solved by making sure that various windows services were running and starting them if they weren't.  But I'm not sure how to check for the same thing in linux, but I think all the services I need are currently running because I can connect to the database via sqlcmd as well as through JetBrains' DataGrip application.  So I know that a connection is possible.  I just don't know why sqlpackage is having trouble connecting...

I've also tried variations of this command on the SourceServerName parameter:

/opt/mssql/bin/sqlpackage /action:Export /SourceServerName:tcp:localhost,1433 /SourceDatabaseName:MyDB /targetfile:'/media/jake/1F171B4C75D06418/MyDB.bacpac'

And the error is slightly different:

(provider: TCP Provider, error: 0 - No such host is known.)


texts:
I have isolated MySQL 5.5 with a huge table inside ( 3.5TB of data and yes, it is only 1 table ).

The goal is to copy a half of this table into another one on the same server. How to do that better and quicker? :

1) Just INSERT INTO new_table SELECT * FROM old_table WHERE ....
What will be MySQL behavior in this case? I expect that connection to MySQL will be broken by time out, however the query itself should run until all data are copied.
Or the MySQL will be just crashed with such query?

2) Using the same INSERT ... SELECT but selecting by chunks ( for example by 10000 rows). This should be definitely safer but much slower I guess.

What is the best way? Thank you in advance!

P.S. Before the insertion I will drop FK and indexes on new table + disable sql_log_bin and


texts:
Goal:

I want to retrieve the value of [data].[Mobiles] when you use a query document that is for the database [test] (use [test])

Problem:

Based on picture two, I cannot retrieve the value from the database. Don't know what is the problem? The database [wwtest] does exist.

Picture 1:

Picture 2:


texts: weird postgresql query runnning on aws postgres db

texts:
got this weird query on my postgres aws server

SELECT
    ix.relname AS index_name,
    upper(am.amname) AS index_algorithm,
    indisunique AS is_unique,
    REPLACE(regexp_replace(regexp_replace(pg_get_indexdef(indexrelid), ' WHERE .+', ''), '.*\((.*)\)', '\1'), ' ', '') AS column_name,
    CASE WHEN position(' WHERE ' IN pg_get_indexdef(indexrelid)) > 0 THEN
        regexp_replace(pg_get_indexdef(indexrelid), '.+WHERE ', '')
    ELSE
        ''
    END AS condition
FROM
    pg_index i
    JOIN pg_class t ON t.oid = i.indrelid
    JOIN pg_class ix ON ix.oid = i.indexrelid
    JOIN pg_namespace n ON t.relnamespace = n.oid
    JOIN pg_am AS am ON ix.relam = am.oid
WHERE
    t.relname = 'sms_verif'
    AND n.nspname = 'public'
    AND indisprimary = FALSE;

anyone ever got this weird query running ? is this somekind of malicious query ? thanks for the help in advance


texts: Using "OR" against two indexed columns doesn't use indexes

texts:
In the query below, from and tid are indexes of the replies table.

SELECT * FROM `replies`
WHERE `from`="<userId>"
OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="<userId>")

By using "OR", it seems that it does a full table scan (~3 million rows).
The EXPLAIN says that a possible key would be from, but then it doesn't use any.

However, in the query below, frid_lt and frid_gt are indexed. The two columns are in a complex index (frid_lt, frid_gt), but frid_gt has also its own index.

SELECT `mid` FROM `messages`
WHERE `frid_lt`="<userId>" OR `frid_gt`="<userId>"

And this query DOES use two indexes. The EXPLAIN says "index_merge" and "Using sort_union(frid_lt,frid_gt); Using where".

Why does the first query not use an index merge?
Is there any improvement I can make to make the engine use an index merge as well?


texts: TLS 1.0 disabled causing errors when requests come from SQL Server to a Webserver

texts:
The issue that I have at the moment is that when I disable TLS 1.0 (due to PCI Compliance) on a web server that hosts our external application, a stored procedure is called from a different server which hosts the database, which creates a HTTP GET Request via sp_OAMethod to call the application on the web server. It returns the following error:

  The connection with the server was terminated abnormally

Using Wireshark we can see that the request that comes from the database server defaults to TLS 1.0 even though we have applied all the cumulative updates so I am now at a loss as to how I can get this resolved.

This is our server setup:

Webserver:

OS: Windows Server 2012 R2 Standard Edition
Processor: Intel(R) Xeon(R) CPU E5-2609 0 @ 2,40GHz
RAM: 7.91GB
System Type: 64-bit OS

Database Server:

OS: Windows Server 2008 R2 Enterprise
Processor: Intel(R) Xeon(R) CPU E5-2609 0 @ 2,40GHz
RAM: 16GB
System Type: 64-bit OS

RDBMS:

SQL Server Version: Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
    May  4 2015 19:11:32
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

I have read up on the following articles:

SQL Server compatibility with New TLS Standards
https://support.microsoft.com/en-gb/kb/3135244
http://blogs.sqlsentry.com/aaronbertrand/tls-1-2-support-read-first/

but still not able to get anywhere. Any assistance from the community is greatly appreciated.


texts: SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

texts:
I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State.  This process blocks our main data entry pipeline and brings the entire database to a screeching halt.

We put a queue table with service broker in front of this proc (as it was initially the data entry proc) and we were losing data as the systems ahead ran out of space in their queues.  This has prevented us from losing data, but hasn't fixed the issue, its simply a band aid.

Is there any way in a procedure to say this proc must be given a higher priority on the CPU, or must be low on the list to take CPU time away from it, it took 7 minutes for the processor to get back around to finishing the task today (has been 12 minutes before, seems to be the higher load we're under the longer it takes as there is more data coming in so there is more processes fighting for CPU time)


texts: Can you use ODBC to extract data directly from SAP HANA S/4 1610?

texts:
Trying to get a handle on what's possible and what's not with SAP Hana S/4 1610.

My understanding is that one can access the underlying SAP HANA DB via ODBC simply by utilzing the SAP HANA Client which installs the ODBC Driver required. Once installed one can, from what I read, access the vast data stores that power the SAP ERP via standard ODBC calls using tools such as SSIS.

However I'm being told conflicting information from other people on if this is even possible and that what is really required is for the vendor to be in control of that process and for them to write apps in ABAP that will interrogate the underlying DB and deliver unto us flat files which our ETL process will then use.

We'll be using a locally hosted SAP HANA installation so, not on prem but no cloudy either.

Clarification: All I need here is a yes, you can connect to SAP HANA S/4 1610 via ODBC or no, you can't and another method is required.

TBH not really interested in the how either since everything else I've read online tells me that. However when you've $10bn a hour consultants saying no, one has to question reality.


texts: How to automate the changes of parameters in ssis package run by a job?

texts:
I have a job that runs a package with specific parameters as you can see on the picture below:

I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27.

I need to automate this as I have a few of those packages to update.

How can I get this done via scripting?


texts: InfoBright LOAD DATA INFILE Wrong data or column definition

texts:
I'm using BRIGHTHOUSE engine.

I have a column defined as varchar(100). When loading data into the table, if the column data exceeds 100 characters, the engine will throw 'Wrong data or column definition' error, and fail to complete the loading process.

I'm wondering wether I can change the behaviour, simply discard the extra characters, just like what MySQL will do.

I've checked out the reject file feature, but it's not what I want.

The only way I can think of is to truncate the column data when generating the data file. It's workable, but not as easy as in MySQL.

Thanks in advanced.


texts: How to improve performance if index is not Most SELECTIVE?

texts:
As per suggestion index must be most selective to improve the performance.
As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows
The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.

Example with good selectivity:

A table having 100K records and one of its indexed column has 88K distinct values, then the selectivity of this index is 88K / 100K = 0.88.

Now I come to the point.
I have one table having 180K records.
Fields that are being frequently used in search criteria are:

Search record using Name of user.
Field Type :-> Not null , nvarchar(32).
Unique records are 627
Search record using Active_date.
Field Type :-> DateTime ,Null.
Unique records are 85627
Search records using  Current_state
Field Type :-> Not Null , nvarchar(32).
Unique records are only 2  that are "Pending" and "Closed".

Currently all above fields are indexed.
In terms of selectivity, case (1) and (3) are not the most selectives. What should I do with them in order to improve performance?


texts: Restricting the # of Rows a User can return in a single query

texts:
A concern has come up recently about employees who have read access to our internal databases being able to download large amounts of data for nefarious purposes.

My question is whether it is possible to restrict the number of rows that a user can return in a query? None of the users who have read access to the db server ever need to return more than a few dozen rows at a time.

Alternatively, how do others here deal with security concerns about the people in their organization who have direct access to run queries on their databases?


texts: Can scheduled and continous replication configurations exist side-by-side on the same primary/secondary servers?

texts:
Environment
We have a core sql server cluster. This cluster contains some databases that get replicated to a load-balanced sql cluster of currently 3 servers. These databases are replicated each 12 hours but will eventually be replicated every 4 hours.
Requirement
On this cluster a new database is created and we need this database to be replicated asap to the load-balanced sql cluster. A delay of seconds or minutes is allowed and writes to this database are currently and in the future low (a few per hour).
Questions
Can two different replication plans coexist side-by-side on the same environment?
Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?
Does this create a high risk for a large existing scheduled replication job?
Our DBA says that this replication scenario creates a high risk for the existing replication configuration (2x a day).
My brainwaves
I can't imagine that this minor write activity with continuous transaction replication can create issues for the large existing replication job. I can imagine the other way around that our continuous replication will suffer twice a day due to the large replication job. We are perfectly fine with that as replication is required ASAP during regular operation.


texts: Permissions for a MySQL user which will monitor replication status?

texts:
I'd like to use a script to monitor the replication status of a MySQL database like in this bash script: https://gist.github.com/wesdeboer/1791632

I'd like to create a user which will only be used to query the status of the replication. So basically this user would just need to run the command:

"SHOW SLAVE STATUS \G"

What is the minimum set of permissions I need to grant a user to allow this?

(Is this even something which can be GRANTed?


texts: HOW does ROWNUM work with this "optimization"

texts:
I'm trying to optimize a query in which I have some requirements where I need to be able to arbitrarily select a row ROWX from a query.  Why is it that

SELECT XML_DATA FROM
    (Select ROWNUM AS MYROWNUM , EXTRACT(COL1,'expresion').getStringVal() AS XML_DATA
    From "Table1" Where Col2 = 'val' AND ROWNUM < ROWX+1)
WHERE MYROWNUM = ROWX ;

Runs much faster than

SELECT XML_DATA FROM (Select ROWNUM AS MYROWNUM , EXTRACT(COL1,'expresion').getStringVal() AS XML_DATA
    From "Table1" Where Col2 = 'val') AND MYROWNUM = ROWX

Obviously as ROWX grows there is some performance degradation with the first query but the second query has a constant run time (outside of the case where ROWX=1) of about 4 minutes where the base runtime for the first query is non-existent and growth is more acceptable than having to wait 4 minutes per iteration.

Version:
Oracle Database 11g Enterprise Edition  11.2.0.3.0  64bit Production

Sorry I'm a software developer (not a DBA!) that primarily works with MySQL/SQL Server so I'm not familiar with Oracle's internal workings feel free to point me in an entirely different direction,

Thanks.

Based on Justin Cave's answer I wrote the query to be deterministic and a bit cleaner (less hacky)

SELECT EXTRACT(COL1,'expresion').getStringVal() AS XML_DATA
FROM (Select ROWNUM AS MYROWNUM , CLUSTERED_ID From "Table1" Where Col2 = 'val' ORDER BY CLUSTERED_ID) t1
JOIN table1 ON (t1.CLUSTERED_ID = table1.CLUSTERED_ID)
WHERE MYROWNUM = ROWX

This has the benifit of running in constant time (where n = rowx rather than count()) and is much easier to understand.


texts: Non-clustered index with all columns for "select * from"?

texts:
I have a simple SELECT * FROM MyTable WHERE DataDate = '18-AUG-2013' query on a table that contains 340 columns and 3.4M rows.

Running estimated Execution plan in SSMS (Ctrl-L) suggests I create a non-clustered index on the DataDate and include every other column?

Is that a sensible thing to do (in general terms)?
Seems to me that this would vastly increase the indexing space and the indexing time on inserts etc. ?


texts: How to confirm that chunk transfer is completed on a DB

texts:
I have one case where i create db for Daily data. I have some collections in this db which are sharded.
My requirement is as follows:
Create every day DB
If There is a DB which is more than 7 days old take its dump in some location and delete it from live DB.

I have one question how can i check if chunk transfer/balancing was completed for this DB before taking its dump and deleting it.
I am concern if i delete the DB whose chinks were not transferred completed will it corrupt my system

Regards
Virendra Agarwal