Data Migration from Oracle to SQL Server If you need to do a data migration from an Oracle database to SQL server, what approaches and technical solutions are the best practice?

Our database has about 100 million rows and a total of 52 tables.

You can do this directly from SQL Server Management Studio.
Within SSMS, just right click the database, select Tasks -> Import Data, select an Oracle typed data source and set up your connection to the Oracle database.

Here's a really good description of the full procedure, including setting up an Oracle client on the machine doing the migration.

http://dbadiary.wordpress.com/2012/10/04/data-migration-from-oracle-to-sql-server-with-ssms/


Use to_timestamp() with an appropriate format template.
For string input, with padding to the left like in your example:
to_timestamp(lpad("STARTDT", 8, '0')
          || lpad("STARTTM", 8, '0'), 'YYYYMMDDHH24MISSMS')

For integer input, this should be most efficient:
to_timestamp(lpad(("STARTDT" * bigint '100000000' + "STARTTM")::text, 16, '0') , 'YYYYMMDDHH24MISSMS')

Produces timestamptz (based on your current timezone setting). A plain cast to timestamp gets the according local timestamp:
to_timestamp( ... )::timestamp


Assistance with: SQL Server detected a logical consistency-based I/O error I'm having a big problem after querying 1 record table, with the following
error information:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: invalid protection option. It occurred during a read of page (1:40607) in database ID 19 at offset 0x00000013d3e000 in file 'D:\database\20001_30082019.mdf'.  Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Can anyone help me?

Error 824 points you to IO subsystem issue. I would highly recommend you to open a support case to Microsoft and they will be able to assist you and pinpoint the root cause. It usually cost you roughly 500USD.
Before you open a case, do due diligence and check your database for corruption. You can find the link here. The link will give you some guidance on the resolution and possible cause.
From my experience, check your firmware updates and filter drivers (antivirus) as a possible cause this error to show up.


MySQL error 1593 on replication between 5.0.92 and 5.5.42 I'm migrating a server to new hardware and trying to set up replication to keep the site current until we switch DNS. The intention is to enable two way replication to make sure that the servers stay in sync during the changeover but I'm concentrating on getting old (MySQL 5.0.92) to new (5.5.42) working at the moment. Both servers have unique IDs but on starting the slave I'm getting

Fatal error: The slave I/O thread stops because master and slave have  equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). The log position given is the start point from SHOW MASTER STATUS on the master.

There are several search results for the issue but the error seems to come up most for MySQL 5.6, which doesn't quite match, and there are even a couple of bug reports. I've tried stopping the slave on the master as suggested in one,I've purged the logs on the slave, the binlogs have the correct server ids in them.

My suspicion is that it's down to some kind of version mismatch and that it hasn't been reported because no-one is ever stupid enough to try replication between two such disparate versions but if anyone has any clues it would be much appreciated. In the meantime I'm going to work with pt-table-sync but it's not ideal.

You didn't setup the server-id variable, or you didn't restart after doing it (it is not a dynamic variable, changes will not be effective until you restart). It may be showing you a good id on both servers, but it will not really work until both servers are restarted, both master and slave.

It could be a bug, but the above has happened too many times on support cases that I would bet it is that.

Regarding circular replication: 5.0 -> 5.5 "should work", 5.5 -> 5.0 is possible, but not recommended. I understand why you want that, but you may encounter many incompatibilities in the queries and format.


Is it possible to backup and restore part of a database in sql-server? We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their web based file transfer application. The database is currently about 10GB however we don't need all the data - most of it is in audit tables and tables that hold calculated values that can be re-generated.

I have looked at creating a filegroup to hold the audit tables and was hoping I could just backup and restore the primary filegroup. I can backup fine but when restoring I get an error saying that I'm not restoring it to the same database. Is it possible to restore part of a database to a different server using filegroups? Is there a better way to do this?

To be honest, this is easiest:

backup/restore a copy locally
remove unwanted data from the copy (with DELETE or TRUNCATE TABLE, not DROP...)
ship the copy

I wouldn't bother with filegroups because of the added complexity you noted...


Resolving high VLF count when database is part of an Availability Group (SQL 2014 Ent Ed.) - do I need to break my Availability Group if I need to reduce the number of VLFs in a database that is participating in an AG?  Does anyone have the process documented?  I am thinking the process goes something like this:

Remove secondary nodes from the AG
Delete the database from the secondary nodes
Remove the database from the primary node of AG
Shrink the log file and regrow to proper size
Add the database back to the AG
Add the secondary nodes back to the AG and allow to sync

Thank you for any direction you can help me with!

I just tested this on a SQL Server 2012 database that is in an availability group, and the log shrink is replicated to the secondary node(s).  Accordingly, you can simply shrink log files and then resize them as you would on a database that is not in an availability group.


restoring a table from sql file in postgresql through Windows Command prompt Im trying to restore an sql file which contains the dumpf a table (with COPY commands) into a table into the database, it would be great if someone could share an example, as the pg_restore doesn't help much in this regard. A link would do as well.

Further information: Through the psql command line client I tried to first access the database and then execute the method presented by @a_horse _with_no_name; but nothing seemed to happen, it just went on to show the next prompt; but there was no change in the backend(data was not inserted into the database). I may be doing something grossly wrong here, just got started with PostgreSQL. :)

If your dump file contains COPY statements, then it's a SQL dump which is restored using psql as explained in the manual.

psql dbname < your_dump_file.sql

More examples are in the manual.


How to allow remote connections to mysql from specific IPs only? I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only?
I am looking for simple and elegant way to do this without doing too much OS related admin and just handle it with mysql.

You can use hostname in grant statements as well, as you are using shared ip then you can limit it with hostname specific grant such as below

grant all on db.* to 'user'@'hostname' identified by 'password';

To allow from all IP's/hosts

grant all on db.* to 'user'@'%' identified by 'password';

For localhost only

grant all on db.* to 'user'@'localhost' identified by 'password';


Need MySQL configuration options like 'first time' When I installed MySQL-5.1.49-win32 for the first time I got a window asking 'root password' and 'confirm' in 'mysql server instance configuration wizard'.

Now I uninstall the software and install the same setup. It always asks me for the current root password even after I remove the 'instance' then configure it.

Seems a file containing password is left behind after uninstallation.

I remember my old password and am able to set new root password to anything of my choice. But I don't want MySQL to ask me for the old password while configuring.

Encrypted passwords are stored in a table.  You apparently kept the data while updating the software?  So the pwd was still there.

You could change the password to nothing.
You could start mysql "skipping the grant tables.
You could live with the brief inconvenience.
You should consider moving forward to 5.5, 5.6, 5.7.  (With the same pwd issues.)


Nearest value for each foreign key I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list.

Unfortunately, the gap from the given timestamp to the first value timestamp for one foreign key may be many times this gap for another FK. I've had a go doing something like this, but it only gives me one row, rather than one row per fk.

SELECT *
FROM   data_item
WHERE  fk_fc_id IN (35246,35247)
AND    di_timestamp > '2013-11-01 00:00:00'
ORDER  BY di_timestamp ASC
LIMIT  1;

It's like I need to take the limit on a per FK basis, but can't work out how to do that.

You can use window functions to achieve your goal.  lag() and lead() are ones which can help you in a query like

SELECT lag(di_timestamp) OVER ordering, lead(di_timestamp) OVER ordering
  FROM data_item
 WHERE fk_fc_id IN (35246,35247)
WINDOW ordering AS (ORDER BY di_timestamp
                    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

This will return the previous and the next timestamps, if there is any.


Assignment of dbms_metadata.get_ddl to pl/sql variable fails I have the followig script

declare
    v_tmp  CLOB;
begin
   v_tmp := dbms_metadata.get_ddl('TABLE', 'FOO', 'BAR');
end;

Where FOO is the table's name and BAR is the name of the current user.
When I run the script I get:

06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints

.
The value returned by dbms_metadata.get_ddl appears to be of type CLOB so why isn't the assignement statement working?

CLOB is not the problem. You may have a special character somewhere in the table definition.

Above code works in general. But below is an exception, to reproduce your error:

begin
  execute immediate 'create table t1 (c1 varchar2(100) default ''' || chr(0) || ''')';
end;
/

desc t1

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(100)

declare
    v_tmp  CLOB;
begin
   v_tmp := dbms_metadata.get_ddl('TABLE', 'T1');
end;
/

Which gives:

ORA-06502: PL/SQL: numeric or value error
LPX-00217: invalid character 0 (U+0000)
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 4

The whole error message may reveal more, and I would check the specific table in the dictionary views (ALL_TABLES, ALL_TAB_COLS), and look for such "anomalies".


How does MySQL manage a delete then select of the same row within a transaction Considering MySQL ver 5.7.
Take the following example:
Begin Transaction
Delete from payments where id = 1
Select from payments where id = 1
End Transaction

In such a scenario should I expect the select statement to return no rows, since I've just deleted that row?  Or will it still return a row because the transaction has not yet been committed?
And if the select does not return a row, then can I assume that any cascade deletes on child tables are also safely deleted before I do my select?
(To answer the inevitable question of "why would you do this?" the answer is simply that I'm hoping to re-use the same script in multiple different scenarios, so I want to know if it's safe to do so or if I have to write separate scripts.  In one scenario the delete will execute, in another it will not.)

As far as the SELECT statement is concerned, the id = 1 row is gone. There might be argument as to whether the physical storage still contains the bits of the data pertaining to the deleted row at the time of the SELECT, but in terms of access to that data, the select will (should) return nothing.


identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared I am using oracle 11g express edition and I am getting the below error when trying to create a change set.
Error report:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I know CDC is desupported in later versions of oracle but I think it is supported here.
Can anyone help please?
I've tried to explicitly grant permission to the SYSTEM user with
grant execute on DBMS_CDC_PUBLISH to SYSTEM;

But I get
ORA-04042: procedure, function, package, or package body does not exist

Verifying Packages
To verify that you indeed do have the required packages in Oracle run the following statement:
set pages 50
set lines 230
column owner format a20
column object_name format a30

SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE
     WHERE Owner IN (
       'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
       'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
       'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
       'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
       'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
       'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
       'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')

     AND Object_Type IN ('PACKAGE')
     AND Object_Name like 'DBMS_CDC%'
     ORDER BY Owner, Object_Type, Object_Name

This should return something like this:

OWNER                OBJECT_TYPE             OBJECT_NAME
-------------------- ----------------------- ------------------------------
SYS                  PACKAGE                 DBMS_CDC_DPUTIL
SYS                  PACKAGE                 DBMS_CDC_EXPDP
SYS                  PACKAGE                 DBMS_CDC_EXPVDP
SYS                  PACKAGE                 DBMS_CDC_IMPDP
SYS                  PACKAGE                 DBMS_CDC_IMPDPV
SYS                  PACKAGE                 DBMS_CDC_IPUBLISH
SYS                  PACKAGE                 DBMS_CDC_ISUBSCRIBE
SYS                  PACKAGE                 DBMS_CDC_PUBLISH
SYS                  PACKAGE                 DBMS_CDC_SUBSCRIBE
SYS                  PACKAGE                 DBMS_CDC_SYS_IPUBLISH
SYS                  PACKAGE                 DBMS_CDC_UTILITY

11 rows selected.

Package Parameters
If all is well, then you can run the following statement to determine the required inputs for DBMS_CDC_PUBLISH.CREATE_CHANGE_SET:
set pages 50
set lines 250
column object_info format a200

select ao.object_type
  ||' '|| ao.owner ||'.'|| ao.object_name
  || case when ap.procedure_name is not null then
    '.'|| ap.procedure_name
    || case when count(aa.argument_name) > 0 then
      '('|| listagg(aa.argument_name, ',')
        within group (order by position) ||')'
    end
  end as object_info
from all_objects ao
left join all_procedures ap
on ap.owner = ao.owner
and ap.object_name = ao.object_name
and ap.object_id = ao.object_id
left join all_arguments aa
on aa.owner = ap.owner
and aa.object_name = ap.procedure_name
and aa.object_id = aa.object_id
and aa.subprogram_id = ap.subprogram_id
where ao.owner in (
  'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
  'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
  'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
  'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
  'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
  'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
  'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
and ao.object_type in ('PACKAGE', 'TYPE')
and ao.object_name like 'DBMS_CDC_PUBLISH%'
and ap.procedure_name like 'CREATE_CHANGE_SET%'

group by ao.object_type, ao.owner, ao.object_name, ap.procedure_name,
  ao.object_id, ap.subprogram_id
order by ao.owner, ao.object_type, ao.object_name, ap.procedure_name;

This should return:

OBJECT_INFO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PACKAGE SYS.DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(CHANGE_SET_NAME,DESCRIPTION,CHANGE_SOURCE_NAME,STOP_ON_DDL,BEGIN_DATE,END_DATE)
1 row selected.

Isues
If you don't get a result, then you are possibly running a version of Oracle that doesn't support the package or you don't have sufficient privileges.
CDC Edition Support
...in synchronous mode is available in Oracle Standard Edition:

Change Data Capture comes packaged with the appropriate Oracle drivers already installed with which you can implement either asynchronous or synchronous data capture. The synchronous mode of Change Data Capture is included with the Standard Edition, but the asynchronous mode requires you have the Enterprise Edition.

Reference Material

How to get a list with description of all dba packages (Stack Overflow)
Introduction to Oracle Supplied PL/SQL Packages & Types  (Oracle | Docs)
DBMS_CDC_PUBLISH (Oracle | Docs)
Change Data Capture (Oracle | Docs)


PostgreSQL splitting one table into three and setting up references between them My title is a little misleading, what I am doing is inserting data from a CSV file where each row has information corresponding to three different tables. You can imagine the schema being something like this:

CREATE TABLE runner (id integer PK, name varchar);
CREATE TABLE race (id integer PK, name varchar);
CREATE TABLE race_result (id integer PK, race_id integer, runner_id ineteger, fp integer);

When I receive the data, it is in a CSV file where each row looks like:

runner_name, race_name, finish_position

Each runner can have multiple race results and so can each race. I am trying to figure out an efficient way to insert this data. I can first insert all runners, then insert all races, then finally insert all race_results and loop up all the ids to fill in the FKs, but this would be awfully slow. Another thing I am trying is:

CREATE TEMP TABLE tmp_x (run_name vc, race_name vc, fp int);
COPY tmp_x FROM 'input.csv' (FORMAT csv);

WITH run_ins AS (
  INSERT INTO runner (name)
    SELECT run_name
    FROM tmp_x
  ON CONFLICT ON some_contraint DO NOTHING
  RETURNING id
), rac_ins AS (
  INSERT INTO race (name)
    SELECT race_name
    FROM tmp_x
  ON CONFLICT ON some_constraint DO NOTHING
  RETURNING id)
INSERT INTO race_result (race_id, runner_id, fp)
  SELECT rc.id, ru.id, t.fp
  FROM rac_ins rc, run_ins ru, tmp_x t;

DROP TABLE tmp_x;

The problem is that this joins the tables creating more results then there actually are. First, is this the best way to go about it? If it is, can I remedy this by explicitly JOINing the tables in some way? I think my problem is that I do not fully understand what happens when I use the WITH statement. Does it do all of the inserts first then creates a temp table/view that has all of the ids of the records that were inserted? If so, does it return an id twice if I tried to insert but there was a conflict on a constraint?

Thank you

Let's assume this is your tables (I've changed naming for clarity and ease of use):

CREATE TABLE runner
(
    runner_id serial PRIMARY KEY,
    runner_name varchar UNIQUE
);

CREATE TABLE race
(
    race_id serial PRIMARY KEY,
    race_name varchar UNIQUE
) ;

CREATE TABLE race_result
(
    race_id integer REFERENCES race(race_id),
    runner_id integer REFERENCES runner(runner_id),
    finish_position integer NOT NULL,
    UNIQUE (race_id,finish_position),
    PRIMARY KEY (runner_id, race_id)
);

CREATE /* TEMP */ TABLE tmp_x
(
    runner_name varchar,
    race_name varchar,
    finish_position integer
);

And this is your data

-- COPY tmp_x FROM 'input.csv' (FORMAT csv);
INSERT INTO tmp_x
VALUES
  ('runner1', 'race1', 1),
  ('runner2', 'race1', 2),
  ('runner3', 'race1', 3),
  ('runner4', 'race1', 4),
  ('runner5', 'race1', 5),
  ('runner1', 'race2', 5),
  ('runner2', 'race2', 4),
  ('runner3', 'race2', 3),
  ('runner4', 'race2', 2),
  ('runner5', 'race2', 1),
  ('runner6', 'race3', 1),
  ('runner2', 'race3', 2),
  ('runner7', 'race3', 3),
  ('runner4', 'race3', 4),
  ('runner9', 'race3', 5) ;

I would insert the data in 3 steps. First, you INSERT in your runners table  the ones you don't yet have. Then you do exactly the same for races.

-- Create new runners
INSERT INTO
   runner (runner_name)
SELECT DISTINCT
   runner_name
FROM
   tmp_x
WHERE
   NOT EXISTS (SELECT *
                 FROM runner r0
                WHERE r0.runner_name = tmp_x.runner_name)
ORDER BY
    runner_name ;

-- Create new races
INSERT INTO
   race (race_name)
SELECT DISTINCT
   race_name
FROM
   tmp_x
WHERE
   NOT EXISTS (SELECT *
                 FROM race r0
                WHERE r0.race_name = tmp_x.race_name)
ORDER BY
    race_name;

NOTE: Using "NOT EXISTS..." works with versions of PostgreSQL older than 9.6, and is (AFAIK) pure standard SQL; so, it should work on other DB as well.

And now that your master tables are filled in, you JOIN your temporary table to them, and add your finish positions. [I assume the combinations are new data, and we do not need to check for already existing data.]

INSERT INTO
    race_result (race_id, runner_id, finish_position)
SELECT
    race_id, runner_id, finish_position
FROM
    tmp_x
    JOIN runner USING(runner_name)
    JOIN race USING(race_name)
ORDER BY
    race_id, runner_id, finish_position ;

At this point, you can query:

SELECT
  race_name, finish_position, runner_name
FROM
  race_result
  JOIN runner USING(runner_id)
  JOIN race USING(race_id)
ORDER BY
  race_name, finish_position, runner_name;

... and get

| race_name | finish_position | runner_name |
|-----------|-----------------|-------------|
|     race1 |               1 |     runner1 |
|     race1 |               2 |     runner2 |
|     race1 |               3 |     runner3 |
|     race1 |               4 |     runner4 |
|     race1 |               5 |     runner5 |
|     race2 |               1 |     runner5 |
|     race2 |               2 |     runner4 |
|     race2 |               3 |     runner3 |
|     race2 |               4 |     runner1 |
|     race2 |               5 |     runner2 |
|     race3 |               1 |     runner6 |
|     race3 |               2 |     runner2 |
|     race3 |               3 |     runner7 |
|     race3 |               4 |     runner4 |
|     race3 |               5 |     runner9 |

Check it all at SQLFiddle

NOTE: Your race_result  table doesn't need a surrogate primary key. There's a natural key (race_id, runner_id) or (race_id, finish_position).


Installing Patches on MS SQL Server Tips What is preventing Agent Service from starting after:

Failing to install CU.
Running a successful repair after disabling HIPS.

Background:
Installing CU's for Microsoft SQL Server I found I have an issue with the security software on my system.
After disabling the offending security software preventing my installation of patches and the repair process afterwards, I now have an issue with restarting the agent service.
Error during repair states:
Microsoft SQL Server #### Setup
The Following error has occured:
The process cannot access the file 'C:\windows\system32\perf-MSSQL13.MSSQLSERVER-sqlagtctr.dll'
because it is being used by another process.
Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

I am thinking I do not need to repair the service just yet, but something will need to be fixed.
New thoughts?

Continuing to troubleshoot I did a search for sql repair perf-mssql13.mssqlserver-sqlagtctr.dll being used by another process and found the following article: Knowledgebase article: File in use by another process
The repair continued after I applied the same advice to the file in question.
When repair completed I was not able to start the agent service. I did a search for the following from the "Event Viewer": failed to initialize sql agent log (reason access is denied) and found the following article: MSSQLTIPS: SQL Server Agent Service Won't Start Due to Access Denied
What I found out: when SQL is installed, it places a security lockdown on the log directory. It changes owners and restricts access to only the agent service account. The DBA that installed the system used default settings to install SQL Server, and then changed the service accounts to domain accounts. In this case, I found it curious the Engine Service account had access to the directory, but not the Agent Log files.
Solution: Check the permissions on the Log Directory and look for errors regarding the files.

Rename the file from the error during repair.
Added the Agent Service Account permission to the log in question and log directory.


Get data faster from databasse My site having a more than 1.5 million records and my question is the how may i fetch record fast.

i am spending so much time to find the way for it finally find out the one way which is to apply the indexing on the field. also try to apply on that it's better result than before.

but still it taking to much time to getting data from the database can any one tell me other technique or trick which i applied on that so it give me much better performance. and my site is built in laravel 4.2.

You can see also my query like below

     $result = DB::table('bussiness')
                ->select(array('bussiness.*', DB::raw('COUNT(complaintreport.id) as reported')))
                ->where('bussiness.is_approved', 1)
                ->where('bussiness.status', 1)
                ->where(function($sql) use ($data) {
                    $sql->where('bussiness.bussiness_name', 'like', '%' . Str::upper($data['searchstring']) . '%');
                    $sql->orWhere('bussiness.trading_name', 'like', '%' . Str::upper($data['searchstring']) . '%');
                })
                ->leftjoin('complaintreport', 'complaintreport.bussiness_id', '=', 'bussiness.id')
                ->groupby('bussiness.id')
                ->get();

Thanks in advance.

What you have is a full table scan with no way to speed it up.

Look into FULLTEXT(bussiness_name, trading_name) indexing as a much faster way to search for words.  You would need MATCH (bussiness_name, trading_name) AGAINST("$data['searchstring']" IN BOOLEAN MODE).

If status is rarely 1 and is_approved is rarely 1, then this would help: INDEX(status, is_approved).

You should also protect yourself from SQL injection by escaping the searchstring.


How database size affects performance: Theory vs reality There is a lot out there that says database size should not affect performance to any great degree. As long as indexes on tables fit in memory the database should remain performant.

However what is the reality? If the database architecture isn't the best, indexes don't fit in memory, and there is potentially a lot of redundant data are there significant gains to be made simply by deleting redundant data? I estimate that 60-80% of data in my database could be deleted.

I believe that reducing the database size and upping the RAM so that indexes can fit in memory would give a significant performance increase which would give some breathing room for a few months to rearchitect the system.

Are there also other factors such as IO, fragmentation, working dataset etc that affect performance based on database size?

It depends entirely on what you are doing with the data.

For basic insert/update/delete transactions that affect just a few rows, then the growth in data size is probably not a big consideration.  The database will use in-memory indexes to access the correct page.  You get more cache misses when the tables no longer fit into memory.  However, the overhead might be slight -- depending on the database, database configurations, and hardware configurations.

If you are doing queries that require full-table scans, then your performance is going to grow linearly or worse with the data size.  Indexes can actually make the situation worse, by randomizing page accesses, which then pretty much guarantee cache misses.

An alternative to more memory is improved disk speed -- solid state disk can provide tremendous improvement.

Just having more data is unlikely to affect performance unless the tables are used in queries.  Is the data redundant within a table or across tables?  Having large tables that never get used is messy, but has minimal impact on performance.  It is imaginable that if you have zillions of unnecessary tables, then then compiling queries could start to take more time.


Does Mysql replicate incrementally? Hi i'm planning to use Mysql replication for a distributed system, the topology will be:

Main DB (Master) --> Bridge DB (Master for Target DB, Slave of Main DB) --> Target DB (Slave of Bridge DB)

I will use ssh tunnels for connect the databases.

I know that replication could be asynchronously, by my concern is i want to know if Mysql replicate incrementally, the main db will be in a site where the internet connection is limited and probably will connect via mobile network only some days of the week, so i want to be sure that mysql does not send ALL data every time the databases synchronize.

Thanks in advance.

i want to be sure that mysql does not send ALL data every time the databases synchronize

In that case, you are in luck.  MySQL native replication always, only, ever sends "incremental" updates.  In fact, it doesn't have a way to automatically send the whole thing.  To initially configure replication, you have to manually copy a snapshot of the data from master to slave.  After that, only the changes are transferred, in an ordered stream of change events referred to as the binary log, sometimes also called the "binlog."

  Replication is based on the master server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log. The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically, SELECT statements are not recorded because they modify neither database structure nor content.

  Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the master.

  http://dev.mysql.com/doc/refman/5.7/en/replication-implementation.html


Extracting dates from PostgreSQL database The db is PostgreSQL 9.6.

I'm creating an app that has events, and the user chooses a date / time that he wants to attend.

So, I created the following structure:

CREATE TABLE public.intervals (
    id            int4 PRIMARY KEY,
    event_id      int4 NULL,
    date_start    date,
    date_end      date,
    configuration jsonb DEFAULT '{}',
)

I've chosen to put data into a jsonb column instead of creating a new table ( configuration ). There, I put an array of objects, like this:

[ { "id": "f53382aa-86b6-e442-8de2-f8c457e5ef11",
    "capacity": 2,
    "duration": 30,
    "end_hour": "12:00",
    "start_hour": "08:00",
    "days_of_week": [ 4, 5 ] },
  { "id": "3d68b5de-7594-fdb9-2848-1190688dd532",
    "capacity": 2,
    "duration": 10,
    "end_hour": "18:00",
    "start_hour": "14:00",
    "days_of_week": [ 1, 2, 3 ] } ]

Now, I want to extract the dates from this data, and I think I'm getting there, but it's getting too hard.

Am I making a mistake using jsonb to store the configuration?

Here's a fiddle of the table and some example data, in case someone wants to take a look: http://sqlfiddle.com/#!17/99d30/3/1

Thanks!

Edit:

I think I got it:

http://sqlfiddle.com/#!17/99d30/44/0

-- select * from intervals;

with days as(
    select
        generate_series(
            date_start,
            date_end,
            '1 day'
        ) as day,
        i.configuration
    from
        intervals i
),
lines as(
    select
        day,
        jsonb_array_elements(configuration) as config,
        ((jsonb_array_elements(configuration)->>'days_of_week')::jsonb) @> ('"' || extract(dow from day)::text || '"')::jsonb as dow_enabled
    from
        days
)
  select
    generate_series(
        (
            day::date::text || ' ' ||(
                config ->> 'start_hour'
            )::text
        )::timestamp,
        (
            day::date::text || ' ' ||(
                config ->> 'end_hour'
            )::text
        )::timestamp,
        (
            l.config ->> 'duration' || ' minutes'
        )::interval
    ) as event_date, l.config ->> 'capacity' as capacity
from
    lines l
    where dow_enabled = true

It works and it looks fast, but I'm open to suggestions.

Am I making a mistake using jsonb to store the configuration?

Yes. You're querying on it. That's usually an indicator to me that you're abusing JSONB.

A few other notes,

On your jsonb formatting. I've cleaned it up. Using JSON, "4" is not the same as 4. You should store numbers as numbers and not as strings
Your default for configuration is {}, but your sample data shows [{},{}]. It's bad practice to store some arrays-of-objects, and some objects in the top level. Pick one.


ETL Issue failing with Timeout error We have a ETL job which is running each day at 02:00 AM.

Until last week the Job ran successfully with no issues.

The ETL runs the extract by connecting through the Linked server to the PROD server.

Checked the access to PROD server all good.

All of a sudden the job started failing with below error.

  Error: Timeout expired.  The timeout period elapsed prior to
  completion of the operation or the server is not responding.

Checked below things :

Exec updatestats on each database of this Database server where the ETL is pointed to.

I also tried setting Remote Query Timeout to 0

  NOTE: But a particular script is causing issue only during extraction.

What else should I check on as the ETL is failing only for specific script.

But the particular script is running good when executed manually (13 seconds)

There are many scripts which run for this ETL job.

But during the extraction it is failing.

P.S. I am very sorry as the code is little big I couldn't post here.

Please see linked server properties.

Kindly help me on the things I am missing.

Regards

While this won't necessarily get to the root of the problem, you may want to see if your job can be set up to retry on failure.

If the problem is a temporary issue, then trying to run the job (or step) 15 seconds or 3 minutes later might work fine.

Note that you need to consider how your job works. If it starts by clearing a work table, then loads the table from a file, then processes the file data, and it's actually failing after the data's loaded, you want to be sure that you still have your file around to reload the data, or you want to break the job into steps, and just rerun the processing step. Also, you need to be sure that shifting the runtime doesn't interfere with other processes (sometimes, if a job takes less than a minute to run normally, you'll have something that has to follow it scheduled to run 15 minutes later; if you schedule up to 10 retries, 3 minutes apart, and the ninth retry works, it's running after that other job has started...).

Sometimes, seeing the length of time the failures take, and having multiple attempts a few minutes apart, will provide more info about the situation, and help you diagnose the solution.

I know that the SQL Server job scheduler allows you to retry job steps on failure; you specify the number of retries, and the time to wait before each retry. Other scheduling mechanisms may or may not have this ability.


Postgres one-liner import & export SQL without login & without asked for password I'm looking for one-liner command for import & export SQL without login into postgres role or psql prompt.

Below is what I got for import SQL, but it asking password every time:

psql -h localhost -d dbname -U postgres -f /var/www/db.sql

Below is equivalent command in MySQL (import & export):

mysql -u root -p password dbname < /var/www/db.sql

mysqldump -u root -p password --single-transaction --skip-extended-insert --quick dbname > /var/www/db.sql

psql doesn't provide a command line option for the password to avoid disclosing it to other local users.

However it accepts the PGPASSWORD environment variable, so your equivalent of mysql's -p password is, in shell syntax:

PGPASSWORD=password psql -h localhost -d dbname -U postgres -f /var/www/db.sql

Alternatively, a client-side password file may be used.


Where can I find scenarios to work through T-SQL I've been working through the exam guides for the new 70-461 MCSE data exams and i'm at a point where I want to practice my T-SQL.  Does anyone know of any good resources whereby scenarios are posed and you have to create a solution through practice.  For example, when I learned C# I would work towards a goal of creating a certain type of system or project like a web app or a console app that did a certain thing.  I'm struggling to come up with test scenarios to improve my T-SQL experience because I don't have a frame of reference for what professionals in SQL do in their day jobs.

You could try to answer query-related questions here or on Stack Overflow. The nice thing about this is that you are solving real problems, not made-up ones, and you can also gain knowledge by seeing how other folks solve those same problems. You're also helping folks, potentially.


MySQL/PHP Returning Blank Error Message I am using the following code to delete a record. It works fine, but MySQL always returns a blank error message. PHP returns "Error: " instead of "1 Record Dropped".

$eventid = intval(mysqli_real_escape_string($dbconnect, $_POST['eventid']));
mysqli_query($dbconnect,"DELETE FROM Events WHERE EventID='$eventid' LIMIT 1");
if (!mysqli_query($dbconnect,$sql)) {
  die('Error: ' . mysqli_error($dbconnect));
}
echo "1 Record Dropped";

When I use the same PHP code and change the SQL to a SELECT or INSERT INTO command instead of a DELETE command, I receive the correct response from PHP.

Doh! @jynus was exactly correct.  Here is the fix:

$eventid = intval(mysqli_real_escape_string($dbconnect, $_POST['eventid']));
$sql="DELETE FROM Events WHERE EventID='$eventid' LIMIT 1";
if (!mysqli_query($dbconnect,$sql)) {
  die('Error: ' . mysqli_error($dbconnect));
}
echo mysqli_affected_rows() . " Record(s) Dropped";


Are foreign keys important while restoring database I dumped out a MySQL database with foreign keys. I want to restore the database, but the restore database is used only for read access. Do I need to index the foreign keys?

If by "index" you truly mean index, then probably yes because the indexes are likely used to speed up read queries as well as lookups for when enforcing the FK constraint.

Of course this may not be true for all the indexes you currently have on FK columns, but that will depend very much upon your application. I would keep them all unless you have tested to make sure some are not needed (in which case those indexes could be dropped to save space).

As for the FK properties themselves, I would leave them in place. Rather than just limiting what gets inserted/updated to maintain referential integrity FKs also act as internal documentation for your DB.


Does Oracle use rowid to recover from redo log and archives? I am learning about supplemental logging and this questios has came into my mind. What I have understood is, Oracle just save an image of the modified column in redo logs, that is why we need to enable supplemental logging to use log miner or golden gate for example, then we will have sufficient information to reconstruct the modified rows using sql apply. But when Oracle does not have any supplemental logging enabled, how oracle recover this images during a recover situation, I mean, for example recover database from RMAN, is it by rowid?

From Oracle Database Utilities - section Supplemental Logging

An application that applies reconstructed SQL statements to a
different database must identify the update statement by a set of
columns that uniquely identify the row (for example, a primary key),
not by the ROWID shown in the reconstructed SQL returned by the
V$LOGMNR_CONTENTS view, because the ROWID of one database will be
different and therefore meaningless in another database

While they don't say it in so many words, the "not by ROWID" (in the context of the sentence) strongly suggests that normal redo recovery is by rowid..


Gotchas converting latin1_swedish_ci to utf8mb4_unicode_ci I have a MySQL 5.5.31 database which has approx 220 tables - of these 220 tables, around half of them are already using utf8mb4_unicode_ci but the "older" tables are still using latin1_swedish_ci. The fields in the tables are a mix of integer, varchar, longtext, date, datetime and decimal and there are no views or stored procedures.

I now have a requirement to support Russian text so I need to convert the remaining tables to UTF8. Based on my reading, I have opted for utf8mb4_unicode_ci as I believe it offers the broadest support including Chinese.

I have tested as much as I can with individual tables in a test environment without any issues and now need to bite the bullet and do this on my production database - I will, of course, take a full backup just before I make any changes.

I have read of some issues with converting TEXT and CHAR fields as well as some considerations on index sizes for VARCHAR fields but nothing else significant.

I know how to do this but I wanted a final sanity check to make sure there is nothing major I am missing before taking the plunge?

I assume you will do this for each table?
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

One potential issue involves the maximum of 767 bytes per column in an INDEX.  If you currently have a VARHAR(255) latin1 field in an index, you will need to rethink it.

Decrease it to VARCHAR(191) if you are sure that 191 will suffice (into the future)
Use a 'prefix' index:  INDEX(foo(191)).  This is often not worth doing.
DROP the index.  (But not if you need the index; let's see the SELECT that needs it.)

Edit

For shrinking to VARCHAR(191), you could write a Stored Procedure to look in information_schema to discover all the VARCHARs, and create and execute SELECT MAX(CHAR_LENGTH(col)) ... to see if any are over 191 (or even dangerously close to 191).  You might be surprised to find a few with max=255, implying that you may already have truncated some text.

The other two options won't damage data, but could have performance problems.


Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table Using Always Encrypted we are attempting to encrypt one column of type varchar(50) in a table that contains 5.5 million rows with the deterministic encryption. After a while the wizard returns failure due to the following error:

Exception of type 'System.OutOfMemoryException' was thrown..

Any ideas on how to prevent this error?

For large tables I would definitely work with T-SQL or Powershell rather than the wizard - clunky UIs that run all kinds of background things and take over-protective locks do not have a great track record. For example, this wizard actually offers an option to generate Powershell:

I would do that. Here is what it produces:

# Generated by SQL Server Management Studio at 9:10 PM on 2/22/17

Import-Module SqlServer
# Load reflected assemblies

[reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null

# Set up connection and database SMO objects

$sqlConnectionString = 'Data Source=SERVER\INSTANCE;Integrated Security=True;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio"'
$sqlConnection = New-Object 'System.Data.SqlClient.SqlConnection' $sqlConnectionString
$serverConnection = New-Object 'Microsoft.SqlServer.Management.Common.ServerConnection' $sqlConnection
$smoServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $serverConnection
$smoDatabase = $smoServer.Databases['DatabaseName']

# If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
#   * Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive

#   * Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# Change encryption schema

$encryptionChanges = @()

# Add changes for table [dbo].[TableName]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.TableName.ColumnName -EncryptionType Deterministic -EncryptionKey ColumnKey

Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase

Now, this still might take a long time; I haven't done thorough benchmarks on encrypting existing data. But you won't run out of memory (this error was because of SSMS limitations, not SQL Server's).

I looked quickly and, while it is easy to generate T-SQL scripts for creating master and column keys, I don't know of a way to run ALTER TABLE. This passes syntax checks:

ALTER TABLE dbo.TableName
ALTER COLUMN ColumnName varchar(11)
        COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ColumnKey,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256');

However it fails at runtime with:

  Msg 206, Level 16, State 2
  Operand type clash: varchar is incompatible with varchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'DatabaseName') collation_name = 'Latin1_General_BIN2'


Reference outer variables in nested select in bigquery vs mysql I run following request in MySQL and it works, but results in an error with bigquery standard SQL.

$sql = <<<EOT
    SELECT
    ads.ad_id,
    (SELECT count(*)
      FROM `PRFXvisits` AS v
      INNER JOIN `PRFXvisit_data` AS vd
      ON vd.visit_id = v.id
      WHERE v.date_time >= @xday AND v.date_time < @xdaytomorrow
      AND ads.id = vd.ad_id
    ) AS clicks
    FROM `PRFXad_reports` AS r
    INNER JOIN `PRFXads` AS ads
    ON r.ad_id = ads.id
    WHERE r.rdate = @xday AND ads.is_archive = 0
    GROUP BY ads.ad_id
    ORDER BY ads.ad_id ASC;
EOT;

Error message:

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "invalidQuery",
    "message": "WHERE clause expression references ads.id which is neither grouped nor aggregated at [8:15]",
    "locationType": "other",
    "location": "query"
   }
  ],
  "code": 400,
  "message": "WHERE clause expression references ads.id which is neither grouped nor aggregated at [8:15]"
 }
}

If I replace ads.id with e.g. 380 no error is produced.

    $sql = <<<EOT
        SELECT
        ads.ad_id,
        (SELECT count(*)
          FROM `PRFXvisits` AS v
          INNER JOIN `PRFXvisit_data` AS vd
          ON vd.visit_id = v.id
          WHERE v.date_time >= @xday AND v.date_time < @xdaytomorrow
          AND 380 = vd.ad_id
        ) AS clicks
        FROM `PRFXad_reports` AS r
        INNER JOIN `PRFXads` AS ads
        ON r.ad_id = ads.id
        WHERE r.rdate = @xday AND ads.is_archive = 0
        GROUP BY ads.ad_id
        ORDER BY ads.ad_id ASC;
EOT;

Why can't I reference ads.id in the inner select from outer FROM clause in bigquery?

It seems mysql select random row to show ads.id during grouping by ads.ad_id.

Try removing the GROUP BY from the main query and converting the join to PRFXad_reports to an EXISTS subquery:

SELECT
    ads.ad_id,
    ( SELECT count(*)
      FROM `PRFXvisits` AS v
      INNER JOIN `PRFXvisit_data` AS vd
      ON vd.visit_id = v.id
      WHERE v.date_time >= @xday AND v.date_time < @xdaytomorrow
      AND ads.id = vd.ad_id
    ) AS clicks
FROM `PRFXads` AS ads
WHERE ads.is_archive = 0
  AND EXISTS
      ( SELECT 1
        FROM `PRFXad_reports` AS r
        WHERE r.ad_id = ads.id
          AND r.rdate = @xday
      )
ORDER BY ads.ad_id ;


Mariadb 10.2/MySQL 5.7 index condition pushdown on partitioned tables In MySQL 5.7.3 index condition pushdown is also working on partitioned tables as stated here https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html.

Previously, index condition pushdown was not supported for partitioned tables. This limitation was removed in MySQL 5.7.3.

Can't find any information about MariaDB 10.2. I know it is in RC stage but maybe somebody knows about it.

In 10.1 without partitioning:
Extra: Using index condition; Using where; Using filesort

In 10.1 with partitioning on:
Extra: Using where; Using filesort

Thx

Support for ICP on partitioned tables was added on this MySQL commit:

https://github.com/mysql/mysql-server/commit/67d2e4ef917e49b0c14cee50e47a498f3f3d95d1

You can check reliably if there is support for in a particular version of MySQL or MariaDB by running the corresponding test.

I do not see such code backported on MariaDB's 10.2-tagged git nor on the offered source downloads.

However, the code doesn't look like insanely crazy, so I would suggest to file a ticket on MariaDB's Jira and ask to backport it. I have searched it briefly and failed to see a similar request. I am a heavy MariaDB and partitioning user and that would be indeed a nice feature to have.


MySQL Slave missing table The skip on mysql slave was set very high and it appears to have skipped a table that was created on the master. I've corrected the skip and now the slave has stopped because new data trying to write to the table that is missing on the slave is generating errors. So doing a skip 1 won't work until the slave creates the missing table from the master.

My question is, how do I force the slave to query the master for all the tables/data which it had skipped?  I effectively want something that will compare the differences between master/slave and update the missing information on the slave. Is this possible? Or will I need to do a stop of the master and just rsync everything to the slave (which might take all day for 350Gigs).

Suggestions?

Create the table manually on the slave. This should allow replication to continue, assuming you are using STATEMENT-based replication. There's a chance replication will break again when the slave processes the 'CREATE TABLE' statement, unless you did 'CREATE IF NOT EXISTS'. If replication breaks here, simply skip it and continue on.

If you are using ROW-based replication (to check, on master: SELECT @@binlog_format), you'll have to dump (using mysqldump) or export (http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html) the table from the master and copy to the slave. Why? Because ROW-based replication transmits a BEFORE image of the row. If the row doesn't exist, then replication breaks.

Once replication has caught up, you can use pt-table-sync to bring over any other missing data for this one table.


mysql query taking differently long I need some enlightenment on a MySQL query issue:

I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me).

To be specific: When I empty the whole table that is being fetched and import only a few entries, the fetch time is how it's supposed to be (0.03 sec). After importing more and more data, the query becomes more and more slow. What's strange is the fact that after deleting entries from the table, it still is running slowly. After deleting ibdata1 and importing all entries, the query suddenly works fine again (0.03 sec).

It probably helps to know that the table is quite big, but not in regards of entries (only 14k), but storage size (all in all the table size is about 2.3 GB because there is a BLOB field). I guess this has something to do with it, since it lets ibdata1 grow pretty fast.

All tables are InnoDB. Any suggestions on why this happens and how to prevent it? I don't want to delete ibdata1 everytime I need to reimport all entries of this table (which sometimes happens because I'm working in a DEV environment).

Here are some more infos. I changed some names (table, field) to not give away too much:

my.cnf:

[client]
port            = 3306
socket          = /home/user/mysql/mysqld.sock

[mysqld]
port            = 3306
#bind-address   = 0.0.0.0
socket          = /home/user/mysql/mysqld.sock
datadir         = /home/user/mysql/data
log-error       = /home/user/mysql/logs/mysqld.err
#skip-locking
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=1

show create table:

 CREATE TABLE `table_name` (
  `ID_0` varchar(255) NOT NULL,
  `VERSION` bigint(20) NOT NULL,
  `PROTOCOL` longblob,
  `START_TIME` datetime NOT NULL,
  `END_TIME` datetime default NULL,
  `STATUS` varchar(255) NOT NULL,
  `COMMENT` varchar(255) default NULL,
  `ID_1` varchar(255) NOT NULL,
  `ID_2` varchar(255) NOT NULL,
  `ID_3` varchar(255) default NULL,
  PRIMARY KEY  (`ID_0`),
  KEY `FK635C17D0AE6C617C` (`ID_3`),
  KEY `FK635C17D022EA191B` (`ID_1`),
  KEY `FK635C17D0109EAE92` (`ID_2`),
  CONSTRAINT `FK635C17D0109EAE92` FOREIGN KEY (`ID_2`) REFERENCES `table_2` (`ID_2`),
  CONSTRAINT `FK635C17D022EA191B` FOREIGN KEY (`ID_1`) REFERENCES `table_1` (`ID_1`),
  CONSTRAINT `FK635C17D0AE6C617C` FOREIGN KEY (`ID_3`) REFERENCES `table_3` (`ID_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

show table status:

| Name                     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                                                                          |
+--------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+
| table_name | InnoDB |      10 | Compact    | 110937 |          23361 |  2591604736 |               0 |      2162688 |         0 |           NULL | 2015-12-30 03:18:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 91136 kB; (`ID_3`) REFER `table_3`(` |

Don't remove ibdata1.  That may be fine for one table, but once you have two tables, you will be in trouble.  Also don't remove an .ibd file if you have innodb_file_per_table.

What is the size of innodb_buffer_pool_size?  Unless you have a small RAM, it should be set to about 70% of RAM.  This is a very important speed setting for InnoDB.

A better way to empty out a table:  TRUNCATE TABLE.  If you will frequently be reloading this table, the have innodb_file_per_table = ON when you create it.

14KB per row, including a BLOB?  That is past one critical threshold.  Please show us SHOW CREATE TABLE and SHOW TABLE STATUS.  There are some important factors to note before discussing this further.

If you ultimate goal is to completely replace the table periodically, then do it this way:

CREATE TABLE new_tbl   (and use either `LIKE real_tbl` or spell out the schema)
Load the data into new_tbl
RENAME TABLE real_tbl TO old_tbl, new_tbl TO real_tbl;
DROP old_tbl;

The RENAME is atomic and instantaneous.  With innodb_file_per_table = ON, you will recoup any free space from the old copy.


Access SQL Server Configuration Manager on a remote server I am trying to add a traceflag as a startup parameter to a distant SQL Server 2016, installed on a Windows 2016 core server and I am following these instructions

At this point in the document:

  In the console tree, expand Services and Applications, and then expand
  SQL Server Configuration Manager to manage the remote computer's
  services.

There is no SQL Server Configuration Manager here.

Is this a permissions issue?
Does anyone know what permission needs to be added and where so I can see SQL
Server Configuration manager under Services and Applications?
Does core have anything to do with this?

Thanks,
Craig

All I wanted to do was add a startup parameter to SQL Server and to do that, I can use this script.

Copy the script to the remote server
Connect to the remote server on powershell

Enter-PSSession -ComputerName some_server_with_sql  -Credential some_user
Add the Traceflag startup parameter

[some_server_with_sql]: PS C:\Scripts> .\AddStartupParameters.ps1  '-Tsome_trace_flag_number'

And you are done,

Then to check, you can run this SQL query

SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%Parameters'

Et Voilà as the French would say.


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

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

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

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

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

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

It's not completely clear what you're trying to do, but this might help:

declare @Data table (
    nID varchar(16)
  , name varchar(16)
  , saletotals float
  , saledate date
)

declare @GiveMeSaleTotals table (
  nID varchar(16)
, datetocheck date
)

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

insert into @GiveMeSaleTotals (nID, datetocheck) values ('4444ffff', convert(date,GetDate()))

select d.*
  from @Data d
    inner join @GiveMeSaleTotals g on d.nid=g.nid and d.saledate=g.datetocheck

results in:

    nID              name             saletotals             saledate
---------------- ---------------- ---------------------- ----------
4444ffff         Red              22.1                   2016-12-05
4444ffff         Red              21.1                   2016-12-05

Rextester link: http://rextester.com/SPJQX46455


Add a suffix to duplicates on PostgreSQL Not sure about the term "undouble", I am just trying to add a unique index on a column but it contains doubloons, so I need to update data first.

Figure this data:

BEFORE update

1;foo
2;foo
3;foo
4;bar
5;bar
6;anyother

AFTER update

1;foo0
2;foo1
3;foo2
4;bar0
5;bar1
6;anyother

Note than values without duplicates are unchanged.

My DB is Postgres 9.5.6

You can use ROW_NUMBER function to add a suffix to each repeated value.

If you'd rather avoid the first one (mostly for those rows without duplicates):

with ct as
(
  select id , row_number() over (partition by data order by id) rn
  from   t
)
update t
set    data = data || case when ct.rn = 1 then '' else (ct.rn-1)::text end
from   ct
where  ct.id = t.id;

select * from t;

id | data
-: | :---
 1 | foo
 2 | foo1
 3 | foo2
 4 | bar
 5 | bar1

db<>fiddle here


SQL Server Audit Changes in user permission Is there any way to know that which privileges user previously had? Previously SQL Server user had rights on some views in a given database. But now these have been revoked, is there any way to get audit logs? Who and when these rights have been revoked?

That is exactly what SQL Auditing is for :-)  Now you can finally know WHO did what with a user's access and many more objects in the server or at the database level.  However, Auditing does need to be setup first before it can record the results.

Specifically speaking of Database level audits:
"The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification. Audit events are the atomic actions that can be audited by the SQL Server engine. Audit action groups are predefined groups of actions. Both are at the SQL Server database scope. These actions are sent to the audit, which records them in the target."

You can read more here https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15


What does the "Edit top 200 Rows" SSMS option actually do? I am new the SQL Server DBA role. I have always been the developer, but due to being the only one at my company with moderate SQL Experience, the role has fallen onto my plate. And today I came face to face with a question I did not have an answer for.

I am familiar with the option in SSMS to "Edit top 200 Rows" for a table, but I never understood the HOW of it. I ask this question because a Contractor who's been working on our system since before I took over was complaining of slow query results. The query he had written pulls in data over a large time period with a final result set in the ballpark of 800,000 rows. A sizable chunk of data.

What surprised me more was that said query took over 9 minutes to run, which did not seem correct to me. I asked him to send me the query. I took his query and ran it and it returned for me in 28 seconds. A sizeable difference compared to the over 9 minutes it took him. We both ran the query directly on the server so I knew that was not the case. I then asked him to show me how he ran it. He then opened up the table with the "edit top 200 rows" option, backspaced the TOP 200, and added his conditionals after the FROM and proceeded to execute it. I told him that was his issue there. He told me it should not be. Now I know that this is a "no no" for large data sets, but I lacked the knowledge to explain why.

I was hoping for a little help from the community here. What does the "Edit Top # Rows" option actually do?

The two types of query here use different grids.

You selecting the data (presumably in Query Analyzer) shows a grid that is optimized for display.
Him selecting the data with "edit top xxx rows" displays a grid that is optimized for editing directly in the grid.  This gives it more overhead and will make it slower.


SSMS Restore Database Wizard questions while restoring to different destination I need to pull some data from an earlier backup of a current prod database, so I'm using the SSMS Restore Wizard to run a point in time restore and put it into a temporary restore database (for example, source DB=MyProdDB, Destination=TemporaryRestoreDB).

In addition to changing the name of the destination DB in the General page of the SSMS Restore wizard to be TemporaryRestoreDB, do I also need to modify the file names/locations located in the Files tab to not be the same as the MyProdDB names?

Essentially, I'm just double checking if SSMS is smart enough to automatically detect that since it's a different destination on the General tab that it needs new .mdf and .ldf files rather than the existing ones. I tried searching, however didn't seem to have much luck with finding an answer.

Edit: Please make sure to see Tibor Karaszi's comment on the answer below, SSMS 2012 has different requirements than other versions of SSMS on restoring to a different database/files.

In my experience, when you change the name of the database during a restore SSMS will automatically update the file names. However, I always double check that it is the case just to be absolutely sure.


Table name starting with @ There is a table in my database that starts with the @ symbol.  I am able to query it successfully using the following query:

select * from [@tablename]

This format works in SSMS and Crystal Reports.

However, I have a C# application where the same query give an 'invalid object name' error on the table name.

Any suggestions?

I do not believe that there is a specific problem with referencing tables, in C#, having names starting with @. I was able to create a table with a name of @TableName and was able to execute SELECT SUM(Col1) FROM [@TableName]; successfully. I used SqlConnection and SqlCommand, not EF or DataSet / DataTable. Are you sure that you are connecting to the database that contains this table?

Most likely the error message is very accurate and you are not in the correct database. Either specify the desired database in the Connection String, or use a fully qualified name: [DatabaseName].[SchemaName].[@TableName].

Of course, if you have a choice, then you should not choose to name any object starting with @ as that is the symbol used to denote local variables, system variables, and input/output parameters.


Database Design for lookup tables I have a transaction table called questionnaire and a question lookup table called question_codes.

questionnaire

qst_id -  number (primary key)
question_type -  number (foreign key)
question_category -  number (foreign key)

question_codes

code_id - number (primary key)
code_name - String
code_desc - String

Both tables are linked with qst_id and question_type,question_category are defined in question_codes lookup tables

I have a requirement to add a new column called product_category in questionnaire table and corresponding id,name and desc will be added to question_codes. product_category has a sub category column called product_type which will be displayed in questionnaire when product category is selected.

My question is do I need to create a new table to map product_category
and product_type? Or can I use question_codes` table as lookup table?

Edit 1

I have table description and a few sample records in SQLFiddle

It really is a question of how your new data fits into the current model.

Option 1 - Same Data, need multiple associations

If your new "product category" data belongs to the same scope then you should put it into the question_codes table. And by scope, I mean would it look OK to see all of the data in question_codes table in a single drop down list in an application?

If you need to associate multiple rows of question_codes to questionnaire you should create an intermediate table between the two tables. This will require a migration of the existing associations into the new table.

    create table question_codes (
      code_id number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number
       /* , question_type number -- remove this column */
    );

    create table questionnaire_codes (
      qst_id number,
      code_id number
    );

    ALTER TABLE question_codes ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE QUESTIONNAIRE ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID)
    );

    ALTER TABLE questionnaire_codes ADD (
      CONSTRAINT questionnaire_codes_PK PRIMARY KEY (qst_id, code_id),
      CONSTRAINT FK_ques_codes_question_codes FOREIGN KEY(code_id)
        REFERENCES question_codes (code_id),
      CONSTRAINT FK_quest_codes_questionnaire FOREIGN KEY(qst_id)
        REFERENCES questionnaire (qst_id)
    );

    insert into question_codes values (1,'QST1','Is Tangible');
    insert into question_codes values (2,'QST2','Is Perishable');
    insert into question_codes values (3,'QST3','Exported?');
    insert into question_codes values (4,'QSC1','Vendor Rating');
    insert into question_codes values (5,'QSC2','Vendor Payment');
    insert into questionnaire values (1);
    insert into questionnaire values (2);
    insert into questionnaire values (3);
    insert into questionnaire_codes values (1, 1);
    insert into questionnaire_codes values (1, 3);
    insert into questionnaire_codes values (2, 2);
    insert into questionnaire_codes values (3, 3);
    insert into questionnaire_codes values (3, 4);
    insert into questionnaire_codes values (3, 5);

    commit;

Option 2 - Different data

If your new "product category" data is a bit different than the existing data in question codes, you should create a new look-up table. Meaning, you would expect to see two separate drop down boxes in an application for questionnaire.

Associate the new look-up table with the questionnaire table.

    create table question_code (
      code_id number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table question_category (
      cat_id number,
      cat_name varchar2(25),
      cat_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number,
       code_id number,
       cat_id number
    );

    ALTER TABLE question_code ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE question_category ADD (
      CONSTRAINT question_category_PK PRIMARY KEY (cat_id)
    );

    ALTER TABLE questionnaire ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID),
      CONSTRAINT FK_quest_question_codes FOREIGN KEY(code_id)
        REFERENCES question_code (code_id),
      CONSTRAINT FK_quest_question_category FOREIGN KEY(cat_id)
        REFERENCES question_category (cat_id)
    );

    insert into question_code values (1,'QST1','Is Tangible');
    insert into question_code values (2,'QST2','Is Perishable');
    insert into question_code values (3,'QST3','Exported?');
    insert into question_category values (1,'QSC1','Vendor Rating');
    insert into question_category values (2,'QSC2','Vendor Payment');
    insert into questionnaire values (1, 1, 2);
    insert into questionnaire values (2, 2, 2);
    insert into questionnaire values (3, 3, 1);

    commit;

Avoid Option 3 - Common lookup table

You can create a common lookup table that many tables can use at the expense of data integrity. If you go this route, you will have more bugs in your data and applications. Avoid this unless you have a time-to-market issue and plan to refactor after you get your angle investors.

    create table code_category (
      code_category number,
      description varchar2(25)
    );

    create table question_code (
      code_id number,
      code_category number,
      code_name varchar2(25),
      code_desc varchar2(25)
    );

    create table questionnaire (
       qst_id number,
       vendor_code_id number,
       type_code_id number
    );

    ALTER TABLE question_code ADD (
      CONSTRAINT question_codes_PK PRIMARY KEY (code_id)
    );

    ALTER TABLE code_category ADD (
      CONSTRAINT code_category_PK PRIMARY KEY (code_category)
    );

    ALTER TABLE questionnaire ADD (
      CONSTRAINT QUESTIONNAIRE_PK PRIMARY KEY (QST_ID),
      CONSTRAINT FK_quest_vendor_codes FOREIGN KEY(vendor_code_id)
        REFERENCES question_code (code_id),
      CONSTRAINT FK_quest_type_code FOREIGN KEY(type_code_id)
        REFERENCES question_code (code_id)
    );

    insert into code_category values (1, 'Question Types');
    insert into code_category values (2, 'Vendor Types');
    insert into question_code values (1, 1, 'QST1','Is Tangible');
    insert into question_code values (2, 1, 'QST2','Is Perishable');
    insert into question_code values (3, 1, 'QST3','Exported?');
    insert into question_code values (4, 2, 'QSC1','Vendor Rating');
    insert into question_code values (5, 2, 'QSC2','Vendor Payment');
    insert into questionnaire values (1, 1, 4);
    insert into questionnaire values (2, 2, 4);
    insert into questionnaire values (3, 3, 5);

    commit;

Excuse me while I take a shower after writing that last example.


MySQL: how to replicate users in master-master configuration I'm new to MySQL and have set up MySQL master-master replication according this tutorial:
https://support.rackspace.com/how-to/mysql-master-master-replication/

I have created some test users on the first DB and would like to know how can I replicated those users to the second master DB.

As far as I understood the administrative database, which is called 'mysql' must be replicated, so the users will be replicated.

Is it enough if I add the line below into my.cnf?

replicate-do-db mysql

Are there any other possibilities to replicated users?

The closest answer I found was mysql-db-in-replication-but-users-created-on-master-are-not-replicating-on-slave

Any help would be appreciated.

replicate-do-db=mysql will definitely work. I tried it on test servers.


Sql server covering index and join I understand that covering index will speed up read operation if all the columns in select list are covered in the index.

But what if I select and filter on columns in other tables as well? Would covering index still help in this case?

Here is my thought: Since the join would force the clustered key to be matched, so the clustered key look up couldn't be avoided. And by finding the clustered key we would have all the information we need thus covering index wouldn't help to speed up the read.

Not sure what you mean by:

  the join would force the clustered key to be matched.

Non-clustered indexes include the clustered index key (needed as the unique row locator).  Consequently, a non-clustered index can cover a query as long as the query references only columns in the clustered index key, non-clustered index key columns, and included columns.

A potentially covering non-clustered index might not be used if other indexes are available to process the query more efficiently depending on the columns specified in WHERE, JOIN, or ORDER BY clauses.


Are PostgreSQL cursor bound to connection? I would like to confirm a point with PostgreSQL cursor.

I have to implement a select factory for huge result set query (more than 1Go of small-weight rows). Thus I have learn about cursor and I execute my query the following way:

I create a cursor:

DECLARE HashedCursorName CURSOR FOR SELECT * FROM BigResultSetQuery;

I repetitively fetch small amount of resultset:

FETCH 1000 FROM HashedCursorName;

When resultset is exhausted I close the cursor:

CLOSE HashedCursorName;

I need to address concurrency, I mean, many users must be able to use the the same query stored in API procedure simultaneously. When a user execute a stored query, he receives a new connection. I first have mangled my cursor name in API because I thought that if all select query are called StaticCursorName I would have a problem with two user tempting to create the same cursor or reading the same. Everything works as expected.

Then I tried to change the cursor name to a static name, and it still work. I am able to run multiple same query simultaneously. Then I wonder, is cursor bound to connection?, and then does PostgreSQL resolve concurrency by assigning cursor to session?

Yes, cursors are not shared across sessions, and their namespace is private to their session as well.

Moreover, if the cursor is not declared "holdable" trough the WITH HOLD clause, it's automatically closed at the end of the transaction.

The relationship between cursor lifespan and session is detailed in the docpage for CLOSE:

  Every non-holdable open cursor is implicitly closed when a transaction
  is terminated by COMMIT or ROLLBACK. A holdable cursor is implicitly
  closed if the transaction that created it aborts via ROLLBACK. If the
  creating transaction successfully commits, the holdable cursor remains
  open until an explicit CLOSE is executed, or the client disconnects.


using SQLCMD to export/extract data withot trailing spaces I am trying to extract data from some of my tables using sqlcmd utility as SSMS does not generate appropriate csv-s for me.
My issue: all csv extract includes trailing spaces according to table definitions (I mean field length) regardless of using char, varchar, nvarchar, etc...
When using sqlcmd utility eg with following options:
sqlcmd -S . -d /*databasename*/ -E -s, -W -Q "SELECT * FROM /*tablename*/" > C:\Test.csv

it is still contains trailing spaces, eg:
,"273                 ",

any hints or tips to do this in SSMS and/or correcting my parameters for SQLCMD?

Despite how the SQLCMD usage screen (SQLCMD -?) describes the -W parameter:

[-W remove trailing spaces]

the actual purpose of this parameter is not to remove existing trailing spaces but rather to skip padding a value with spaces.  If a string value already has trailing spaces of its own, they cannot be removed if you simply specify the -W parameter.
As far as I can see, there is no way to resolve this issue with parameters.  You will have to either manipulate the output with text processing utilities or generate the values without the trailing spaces in the first place.  For the latter you can use the RTRIM() function, but that, of course, means you will have to replace the SELECT * with an actual column list, where some columns would be computed columns:
SELECT
  ...
  RTRIM(SomeStringColumn) AS SomeStringColumn,
  ...
FROM
  ...


Does SQL Server 2016 Standard Edition include replication? I was trying to create a distribution database on an instance of SQL Server 2016 SP1 Standard Edition.

I got an error that the replication component of SQL Server was not installed. We have recently upgraded our server from SQL Server 2012 to 2016 where we had tested replication to a dev machine previously.

It occurred to me that the reason that the replication components aren't installed is that we perhaps aren't paying for them. Looking at the licensing guide found HERE, I can't find information on replication specifically - either because it IS included in the basic SQL Server app or it falls under some generic product name like 'Enterprise Data Management', or something else.

Questions:

Does SQL Server 2016 Standard Edition include replication?
Does the Distribution server count as a separate instance of SQL Server and require its own license?
If we are using this as a testing 'dev' ground, do we still need to purchase licenses for replication from prod to dev, with the distribution server hosted on the same dev machine?

Yes. (See the REPLICATION section in Editions and supported features of SQL Server 2016
Yes. The distributor (Distribution Server) is located on a SQL Server instance and has to be correctly licensed, be this a production server or development server.
Yes. License required. A replication from a production server to another (dev) server is considered a move of productive data and so the target is no longer a DEV server, but a productive server.

The basic premise regarding Microsoft Licenses for SQL Server is: If it contains productive data, it is a productive server and requires (correct) licensing.

IMHO : Do no try to circumvent MS licensing fees by being clever and innovative. It will almost always backfire.

  DISCLAIMER: Please contact a Microsoft SQL Server licensee/specialist/re-seller or Microsoft themselves to obtain information about correct licensing. No guarantee given for above made statements.


Is there a better ticketing system to check PostgreSQL issues? I want to keep track of an issue in PostgreSQL. This mailing list tool that they have is hard.

Is there an easier way to track their issues, open one and track it?

Like a mirror system they have in Github.

There is no alternative.

Many people would like to have a bug tracker, and there are regularly spirited attempts to introduce one. They usually fail because of lack of buy-in by the community.

One of the problems is that it must be possible to continue using e-mail as an interface.
The other problem is that PostgreSQL does not want to become dependent on third-party tools.

PostgreSQL has been here for over twenty years, and we want to stay around for more. Tools come and go, e-mail will remain.

The archives offer a pretty good search functionality.

For example, if you want to search for previous discussions about bug trackers, this search on the Hackers list will bring many results.


Sql Server Ranking Value Not Changing Please Help How to convert this (MySQL Query) to SQL Server?

Or how to get a ranking value not changing while I'm using where in query:

SELECT id,
       name,
       score,
       FIND_IN_SET( score, (SELECT GROUP_CONCAT( DISTINCT score
                            ORDER BY score DESC ) FROM scores)
                  ) AS rank
FROM scores WHERE name = A;

No need for the complex - and expensive - FIND_IN_SET() and GROUP_CONCAT() solution. All you need is the DENSE_RANK() function:

SELECT
    id, name, score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM scores ;

If you want to add a WHERE clause, say WHERE name = 'C' and still get the rank(s) - among all rows of the table - you can use either a CTE or a derived table:

-- derived table
SELECT d.*
FROM
  (
    SELECT
        id, name, score,
        DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
  ) AS d
WHERE d.name = 'C' ; -- arbitrary `WHERE`

-- CTE
WITH d AS
  (
    SELECT
        id, name, score,
        DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
  )
SELECT d.*
FROM d
WHERE d.name = 'C' ; -- arbitrary `WHERE`


How to check for row lock level when FOR UPDATE is used in PostgreSQL? Related to previous question here
From the pg_locks documentation, row level lock information is not available when FOR UPDATE is used in a SELECT statement.
Is there any way to check for row level lock when FOR UPDATE is used in a SELECT statement?
If yes, how can I do so with Postgres 11?
Thank you

Row locks are not permanently stored in the shared lock table, but on the row itself, so you cannot simply query for them.
To figure out which rows in a table are locked by concurrent transactions, you could run
SELECT id FROM mytable
WHERE id NOT IN (SELECT id FROM mytable
                 FOR UPDATE SKIP LOCKED);


Postgres: Preventing objects with same name I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. Someone had, however, added a view in the current schema which has the same name as the table in the other schema. This caused the new view in the current schema to "take over" the reference in the query causing it to reference the view instead of the table. Obviously this caused me to start looking in the wrong place for this bug and getting completely confused. My question is therefore, how can I prevent this from happening? For example by enforcing that all views an tables in the database needs unique names. By enforcing this, I mean that it will cause and error if one attempts to create two objects with identical names.

Because I am using some external tools, I have set up postgres to search for tables in multiple schemas (I have forgot how I did this), which is probably partly to blame for this mistake, but I cannot change this.

That's like asking Microsoft or GNU.org to remove PATH from the command line. Ain't gonna happen.

Use fully qualified names or always run ALTER ROLE <your_login_role> SET search_path TO a,b,c; at the beginning of your session. I'm not sure if you can run this with psqlrc but you could try.

You can view your search_path with show search_path

You could write a cron or pgagent job to run often and check information_schema for views and tables with the same name


Overwrite event - data from SQL Server to MySQL In MySQL is it possible to execute an update query with values selected from external SQL Server database?

I'd like to execute every night a timed query, which overwrites some data in MySQL with data on SQL Server.

Write PHP/Perl/Java/VB/whatever code to SELECT from one db server and INSERT/UPDATE/whatever into the other.

For "timed", use cron (if on *nix).


ERROR 13 (HY000) When Creating Database I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.

Errcode: 13 is a file permission issue.

If your datadir is /var/lib/mysql, do the following:

cd /var/lib/
ls -l

Check the file permissions as make sure /var/lib/mysql is owned by mysql:mysql.

If it is not, then do this:

chown -R mysql:mysql /var/lib/mysql

If you ran sudo, you are probably root. Make sure root can write into /var/lib/mysql

Give it a Try !!!


SQL Server Standard High Availability (HA) with two nodes Is it possible to accomplish High Availability (HA) with two SQL Server Standard nodes ?

We can't use SQL Server 2012 AlwaysOn without Groups as it has single point of failure by using the "single" database file
We can't use SQL Server 2012 AlwaysOn with Groups because it's placed in Enterprise package and we don't have license for that
We can use any other SQL Server version but in Standard packaging level
We can't use mirroring because it requires a third SQL Server used as witness server

Any other suggestions ? I have tried to find an answer all over the internet and didn't find anything.

Thanks

Use Database Mirroring. Witness node is optional.

http://technet.microsoft.com/en-us/library/ms190941.aspx

You may configure a failover cluster with virtual shared storage. There are guys who do the VSAN for Windows (however they call it "for Hyper-V" but don't get confused it works bare metal on Windows as well).

http://www.hotwebclips.com/Home/Show/tGgqG8oEpBQ


Query performance diffrence between equals and between I need to know why the correct answer is D ? what is the diffrence between D and A.

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed.
There is a non-clustered index on the OrderTime column.
The business team wants a report that displays the total number of orders placed on the current day.

You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

A. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE()
C. SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112))
D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct Answer: D

Since the column OrderTime contains the whole time stamp option A would only return orders places exactly on midnight. There is no attempt to extract the date from the stamp.

D doesn’t change the column and can use an index since it’s regular comparisons. C would most likely also work but it includes a CONVERT for each row tested so no index used and every row has to be checked.


Oracle database 19C moving back and forth with time without resetlogs my client have a oracle database 19C on windows server 2016
for a business reason he need to be able to move back in time to specific date/time and modify some data and then move forth to the current date with the same database using archive logs and without opening database with resetlogs
i did check flashback and point in time recovery and it always ends up opening the database with resetlogs
is it doable ? if there is some other way please share

I would say that this is an "X-Y Question" (Asking for one thing, "X", whilst actually wanting to achieve something else, "Y").
Mucking about, spinning the database back and forth "through time" is inherently dangerous and intrusive and, most likely, will not achieve what the client wants.
Being able to Flash-back and Flash-forward the database relies in all the changes involved being in the Database FlashBack Logs.  As soon as you make changes to the "earlier" database, you cannot Flash it "forward" again without losing the changes you made "back then".
It's like every Time-Travel movie you've ever seen, where the villain goes back in time and changes something in the Past, thereby re-writing the Present.  The only way to "rescue" the situation is to undo whenever they did (and that's basically what Flash-"forward" does).
Restate the [actual] problem.
What does the client actually want to achieve?
Databases accumulate data over time so if the client wants to change some "earlier" data, then they need to identify which data, what they want to do to it and then come and talk to you about the best way to do so (which shouldn't involve Database Recovery techniques, just regular insert/update/delete-type stuff).
And if, they've already tried that and something "in" the database actively prevented them from doing so, then there's probably a very Good Reason that it did so ...


How to query for certain text in log messages from Google App Engine that are exported to BigQuery? I have used the 'Create Export' in the Logging section of Google Cloud Platform to export log entries into Big Query for analysis

Here is part of the structure of the table in BigQuery that logs the log entry

I want to search for entries that contains this user id 14994838999176.

In BigQuery, I issue the following query (in standard SQL dialect),

SELECT * FROM gae_log.appengine_googleapis_com_request_log_20180323

where protoPayload.line.logMessage  like '%14994838999176%'

LIMIT 1000

It failed with the error message:

 Query Failed

 Error: Cannot access field logMessage on a value with type
 ARRAY<STRUCT<time TIMESTAMP, severity STRING, logMessage STRING, ...>> at [3:25]

How can I correct my sql statement?

The reason why this query failed is because you have a table with nested repeated field protoPayload.line (datatype RECORD).

You can try implicit unnesting, it would be something like:

SELECT x
FROM `gae_log.appengine_googleapis_com_request_log_20180323` as t,
      t.protoPayload.line AS x
WHERE x.logMessage  like '%14994838999176%'

You can also have a look on how to query STRUCT elements in an ARRAY here.


How to connect DB2 with eclipse on mac? I tried to connect the DB2 Express-C Version 10.1 to Eclipse Mars but it returns an exception when I tried to run the program.

com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException:
Error opening socket to server localhost/127.0.0.1 on port 50,000 with message
Connection refused. ERRORCODE=-4499, SQLSTATE=08001

I think there is an issue with the port number or connection url.

OS: Mac OS X El Capitan

Eclipse: Eclipse Mars

DB: DB2 Express-C 10.1

You may need to follow the Network Access steps from the DB2 on Mac OS X install guide:

  Network access

  The instructions below assume that you installed DB2 with your current
  user account. If you manually created a different account such as
  "db2inst1", replace $(whoami) with db2inst1

  If you need to access this DB2 instance remotely, you should configure
  the service:

db2 update dbm cfg using svcename db2c_$(whoami)

sudo su -
echo "db2c_$(whoami) 50000/tcp" >> /etc/services
exit

db2set -all db2set DB2COMM=TCPIP
db2stop force
db2start

Alternately, instead of keeping the local DB2 Express-C install, you can run DB2 in a Docker container.


How DISCARD the tablespace on mariadb while table is not found? I run the mysql_upgrade against my mariadb 10.3 and get the following error - see here for details.

ERROR 1813 (HY000) at line 85: Tablespace for table 'mysql.transaction_registry' exists. Please DISCARD the tablespace before IMPORT

When doing the DISCARD it does not work as the table does not exist

MariaDB [mysql]> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

MariaDB [mysql]> ALTER TABLE transaction_registry DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'mysql.transaction_registry' doesn't exist

How to proceed in such a case?

After carefully checking again, I've found transaction_registry.ibd on the file system. So I manually deleted the file after stopping mariadb. Here you can see what I did:

sudo systemctl stop mariadb
sudo rm /var/lib/mysql/mysql/transaction_registry.ibd
sudo systemctl startb mariadb
sudo mysql_upgrade -u root --force -pxxxxx
...


Grant privileges in other table than mysql table I have been requested to create a back-end area for an existing website (on a provider's server), consisting of several tables and php pages.
This back-end area should not be accessible/visible to our normal website vistors, so a login system (php) has been developed.
I am also expected to be able to grant privileges (select, insert etc.) for those who CAN access this back-end area. However, I have no access to the mysql db on the provider's server.
Is it possible to create and use a separate table for granting privileges on my tables or can privileges only be set in the mysql db itself ?
Any suggestions/help welcome. Thx, LBox

No. Grants are not defined per database (as in mongodb 2.4). mysql database is a centralized system of records for authentication etc... (just like admin database in mongodb 2.6+).

You might want to refer the document for mysql privilege system.


MySql - Clean ibdata1 I have a database whose entire size is ~44GB, out of which ibdata1 is ~35GB. That doesnt make sense, since the size of the data shouldn't be more than 10GB.

I use the following query to get an esstimation of the data size:

    SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  30;

Any ideas how to clean ibdata1 and why has it grown so much?

BTW, I use innodb_file_per_table

Andreas got this answer first in terms of what to do. +1 for Andreas !!!

I would like clarify why that answer is the only way and how to do it.

By default ibdata1 normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Table Metadata

Running OPTIMIZE TABLE against an InnoDB table stored ibdata1 will make things worse because here is what it does:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. That way, only MVCC and Table MetaData would reside in ibdata1.

If you already use it, then you must have a high-write environment that stores lots of MVCC to support transaction isolation. Once the transactions holding the MVCC is done, the space is simply abandoned for re-use.

To shrink ibdata1 once and for all you must do the following:

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) service mysql stop

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

STEP 05) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) service mysql start

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I would like to suggest a formula for you to query on sizing your InnoDB Buffer Pool from my post on What are the main differences between InnoDB and MyISAM?