texts: Performance issue after reorganizing table in MYSQL

texts:
There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based application. We have daily schedule for "Analyze Table <>" to avoid stale statistics because of added records everyday. Besides Analyze table, I was intended to execute "Optimize Table <>" to reorganize the physical storage
of table and index data for reducing space and improving I/O efficiency while access data by the application, but never successed to do in production due to time it consumes.

As an alternative, I applied "ALTER TABLE <> ENGINE=INNODB;" command to all tables as it could acheive the advantage of OPTIMIZE command. It significantly contribute to reorganize physical storage of the table and indexes in the databases as I have shown following metrics for big three tables:

            data_length             index_length                Size_MB
            Before  After           Before  After           Before  After
BigTable1   9042919424  8312061952  8148451328  6464471040  16394.97    14092
BigTable2   2877292544  2425356288  3173138432  2286764032  5770.14 4493.83
BigTable3   1534066688  1232076800  2261762048  1955594240  3619.98 3040

I was assuming that selection on the tables will have better execution time by above maintenance.

Unfortunately, it's back fired during SELECT statements. 5Seconds report query taking more than  a Minute, I have verified that before and after execution time is same and data growth is as usual. What could be the reason in this context?

Update1-01/26/2015:

Query:

SELECT
pu.Name ,
s.name ,
COUNT(st.id),
SUM(st.amount)
FROM
s
JOIN pu
ON (
s.priviligedUser_id = pu.id
AND pu.priviligedUserType = 4
)
JOIN st
ON (
st.service_id = s.id
AND st.transactionStatus=1
)
WHERE st.created BETWEEN '2015-01-24 00:00:00' AND '2015-01-24 23:59:59'
GROUP BY s.id
ORDER BY 1;

Execution plan(same execution before and after maintenance):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  pu  ALL PRIMARY \N  \N  \N  177 Using where; Using temporary; Using filesort
1   SIMPLE  s   ref PRIMARY,FKD97C5E95CF9FA894,FKD97C5E9594186A14   FKD97C5E95CF9FA894  9   pu.id   1   \N
1   SIMPLE  st  ref FK392C9D09F59FDDE0,ServiceTransaction_transactionStatus,idx_servicetransaction_id_status_created,idx_st_created idx_servicetransaction_id_status_created    14  s.id,const  616 Using index condition

Note:
I didn't see any changes in the execution plan and correct index(idx_servicetransaction_id_status_created) is picked up for one of the biggest st table(11M Rows) as shown in the 3rd row in the execution plan. Other tables mentioned in the 1st and 2nd row in the execution plan would have records as listed below:

pu = 0.2M Rows
s = 200 Rows


texts: pgadmin / postgresql - no pg_hba.conf entry for host

texts:
I am trying to use pgAdmin to connect to a Postgresql server hosted by Elephantsql. However, I keep getting the error no pg_hba.conf entry for host:

no pg_hba.conf entry for host "173.18.54.553", user "db_user", database "db_name", SSL on FATAL: no pg_hba.conf entry for host "xxx.xx.xxx.xxx", user "hcexhbhx", database "postgres", SSL off

After doing some research, I edited my pg_hba.conf file:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
    host    all             all             173.66.143.244/24       md5 (new entry)
    host    all             all             0.0.0.0/0               md5 (new entry)
    # IPv6 local connections:
    host    all             all             ::1/128                 trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     uzrrrrd                                trust
    #host    replication     uzrrrrd        127.0.0.1/32            trust
    #host    replication     uzrrrrd        ::1/128                 trust

And I added a line to my postgres.conf file:

listen_addresses='*'

And I restarted postgresql before attempting to connect again.
However, I'm still getting the no pg_hba.conf entry for host error.

Does anyone know what I might be doing wrong?

Thank in advance!


texts: Mariadb - Error declaring variable in trigger

texts:
I'm trying to declare a variable to use later in my trigger, but I'm getting this error.

ERROR 1064 (42000)
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END' at line 7

DELIMITER $$
$$
CREATE TRIGGER clients_before_update
BEFORE UPDATE ON clients FOR EACH ROW
BEGIN

    declare var_test INT;

END;

$$


texts:
I have an application, which contains two Models Question and Option

Each Question has many Options
Each Option may have many Questions (and this Question may have many sub Options) so on and so forth.

Please help in how to achieve this relationship in database.
Thank You.


texts:
So I have a table tbl where I'm trying to pull data that increments at a variable rate.  I need to identify when the data resets so I can make a cumulative value.  Here's an example table:

ID    VAL
 1     12
 2      2
 3     14
 4     20
 5     24
 6     25
 7      2
 8     11
 9     14
10     15
11     18
12      0
13      5

How do I identify in a SQL query which rows have reset data?

In this table example, I'd want to identify row IDs 2, 7, and 12 as the points where the data resets.

The end result is so I can add rows 1, 6, and 11 (with other data) to the current value for a total cumulative value.


texts: Oracle 11g r2: Cannot create partition on timestamp field

texts:
I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this.

CREATE TABLE t (id NUMBER , tstamp timestamp )
  PARTITION BY RANGE (tstamp)  (
    PARTITION t_jan_2009 VALUES LESS THAN (to_timestamp('2009-02-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2')),
    PARTITION t_feb_2009 VALUES LESS THAN (to_timestamp('2009-03-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2'))
);

INSERT INTO t SELECT 1, '2009-02-10 12:34:45.56' from dual;

This is not the actual data but an equivalent sample. It gives the same error.
SQL Error: ORA-01843: not a valid month

Why am I getting this error? Do I need to do something more?


texts: SQLite return sum of duplicate rows based on array

texts:
If I have a simple table fruits that looks like this:

+---+---------+------+
|   | name    | lbs  |
+---+---------+------+
| 1 | apple   | 0.25 |
| 2 | melon   | 3.00 |
+---+---------+------+

In a query, I need to return the sum of the lbs based on an array. For example, I could have the array ('melon', 'melon', 'apple') to return the value 6.25.

A query like this won't work:

SELECT SUM(lbs) FROM fruits WHERE name IN ('melon', 'melon', 'apple');

It returns 3.25 because even though melon is listed twice, it only counts it a single time.

How can I get the select statement to sum duplicate rows as dictated by the given array? I've done a lot of searching of StackExchange, but maybe I'm not using the right keywords to find the answer. Thanks in advance for you help/patience.


texts:
I am having problems with a website with 600MB of MySQL database. The website is way too slow. I noticed the bigger the MySQL database gets, the slower it goes. When it was 5MB, the website was very fast. When it started getting bigger it started to get slower and slower and now, at 600MB, it is really slow, taking like 10 seconds to load pages.

I checked the top processes and it is nothing related to high load or anything. It is not even related to IOPS as I tested on HDD 7.2k rpm drives and it gave same problem now with testing with Intel 320 SSD drives, so I don't think it is about high queries either.

The website is using Wordpress and there are like 9 plugins active. People said it might be the plugins...well maybe...but right now I just want to cache the whole database in memory and would like to get help and direction on where to start and how to do it.

I have 16GB RAM and i5-2400 4 cores @ 3.1 GHz. OS is centos 5.7

top - 07:23:57 up 9 days, 12:15, 0 users, load average: 0.09, 0.04, 0.05
Tasks: 162 total, 1 running, 161 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.2%us, 1.0%sy, 0.0%ni, 90.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16367532k total, 3641628k used, 12725904k free, 612140k buffers
Swap: 1046520k total, 0k used, 1046520k free, 1538896k cached


texts: Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7

texts:
I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code below but it failed:
ALTER TABLE db.video_info MODIFY COLUMN created_time bigint(20)
  DEFAULT (ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)) NULL;


texts: How can we add BOOLEAN datatype in mysql query browser?

texts:
I am trying to add a BOOLEAN column using EDIT TABLE in mysql workbench, but it automatically changes to TINYINT on applying changes.

How can I get the column to be BOOLEAN?


texts: Recommended Approach to Programmatically Backing Up/Restoring Oracle DB

texts:
Supposing I have an ever-growing Oracle DB on one server, and I want to duplicate this schema and the data on another server - what would be the best approach to achieving this as part of a bespoke .net app?

What I've Tried/Researched:

RMAN - seems to be the most appropriate technology available for writing batch files to do a backup but I don't feel as though I'd have as much control over it as if it were developed from scratch in C#.
PL/SQL - I could use stored procs to export data to csv and combine it with C#?
DataReaders and official Oracle API's - Seems to me like the most favourable approach as it is what I'm most familiar with - but am I reinventing the wheel given the fact that the above two technologies have the power to create these backups?
'OOTB' oracle executables - https://stackoverflow.com/questions/12524284/taking-oracle-database-dump-via-c-sharp-net http://rockingtechnology.blogspot.co.uk/2011/06/oracle-backup-and-restore-code-in-cnet.html


texts:
I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was gone.

Sample:

CREATE NONCLUSTERED INDEX test3
/*
 Created on ...
 Reason: ...
 Improvement: 95 - 99,8% ...
*/
ON [dbo].[T]
(
/* equal */
[ID], [Nr], [D],
/* inequal */
[S], [OD])
INCLUDE
([A], [C] )

After selection SCRIPT => CREATE SCRIPT in SSMS all comments are gone.

Does anyone know a solution to keep them alive?


texts: Update from PHP 5 to PHP 7.0 - PHPmyAdmin will show two server logins: localhost / localhost:3306

texts:
I updated my Linux 14.04 LTS to 18.04 and had to downgrade the PHP 7.2 to 7.0 as I need to run some software which is not capable of the preinstalled PHP 7.2. I used PHP 5 so far.

After I got everything runnning now, MySQL, Apache and PHP I created a new database and now I get a Notification that the control user can't be connected and that 'pmp'@localhost has denied access.

This appears when I log into server @ localhost.

If I choose localhost:3306 everything works fine. But I do not know why I have two server logins?

I'm somehow confused, that I get two servers to choose from (localhost and localhost:3066) which I would not have expected. Can I somehow delete the localhost-access for PHPmyAdmin and only enable localhost:3306?

Any idea what I have to do? I'm a database newbie. Best


texts: Solutions for Introducing a new Entity In Between An Existing Association

texts:
I'm looking for best practices in regards to a specific change in schema. I'm using sql.

I had the following entities: Project and batches - a project had many batches. The batches table had a project_id.

As time went on, we decided that a Project would have orders which in turn would house those same batches. So now a project has many orders and orders have many batches. With that said, batches now have an order_id and project_id. My issue is this - the app depends on the presence of both fields, older batches don't have order_id and new batches don't have project_id so I'm getting exceptions throughout the application. I was thinking about getting rid of the project_id field on batches, but we need to keep the reference. We cannot create orders for each of those batches and migrate the field with a script since those orders will not be able to be completed/invoiced/etc (they already were in the real world).

Old Schema

create table Company
(
  id int not null primary key
)

create table Project
(
  id int not null primary key,
  company_id int not null references Company
)

create table Batch
(
  id int not null,
  project_id int not null references Project
)

My current schema:

create table Company
(
  id int not null primary key
)

create table Project
(
  id int not null primary key,
  company_id int not null references Company
)

create table Orders
(
  id int not null,
  project_id int not null references Project,
  po_number int
)

create table Batch
(
  id int not null,
  order_id int not null references Order
  project_id int references Project
)

I would have to add a lot of conditional logic in the application in order to handle all of the cases, which I certainly don't want to do.

What are best practices or good solutions for situations like this? Perhaps adding application logic is the only way to go.
Should I find a way to remove project_id entirely? Then how would I make sense of getting order updated appropriately?

Thanks!


texts:
I am using SQL server, and the person creating our database added lots of indexes on our biggest table. These indexes only include one field each.

To me it seems that these indexes are mostly useless, and that this was a bad idea.

My concern is removing some or all of these indexes and affecting performance on our live system. There is no way to test this on our test system because our test system doesn't have the same load or amount of data.

Just wondering...

Is there a way to work out which indexes are never used?
Is there some kind of analysis that will show which indexes are used for what queries over a period of time for instance?


texts: How to observe output from query in Workbench 6.3?

texts:
I am just starting to use MySQL Workbench 6.3, and I want to just run simple queries.  I've created a table with the appropriate schema and everything, but I can't seem to observe the output from my script.  Here's what it looks like:

I click on "run script file" in the Scripting drop down menu, but it doesn't seem to do anything at all?  Even if there's a syntax error, I'd like to just see the output and debug my script.  Am I missing something obvious?


texts: Secure database access from particular applications

texts:
I have an C#.NET application and a SQL Server database (SQL Server 2008 R2) and would like to restrict the access to some of the Tables when it has been accessed from some other application rather than my application.

We tried to use a CLR trigger for this. But we couldn't make it. Please give some immediate suggestions. It would be much appreciated!


texts: MariaDB Cluster - data consistency for updates and auto_increment

texts:
I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication - which MariaDB Cluster use and have some questions. And I not use transactions, foreign keys, etc...

1) If I understand it well - unlike of MySQL Cluster on Galera I do not have to worry about auto increment values. Config auto_increment_offset is not needed with Galera. Is this true?

2) I read many articles and discussions about data consistency and replication but still have questions. If application not use transaction and for example run 10x query "UPDATE a SET b=b+1 WHERE c=1 LIMIT 1". Can I be 100% sure that "SELECT b FROM a WHERE c=1 LIMIT 1" return right value? On one slideshare presentation I read - "Data does not belong to a node - Node belong to data". So, it's true that Galera replicator take all care about consistency and no changes in application is needed?


texts:
I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless there's already something built-in that it uses automatically. Whatever I use is going to be handling huge amounts of data.

The SP-GiST operator family "text_ops" indexes using a trie. Because UUIDs are quite long and very dissimilar, these sound appealing even though I would only ever do full match searches.

There's also a hash option. Hashing is O(1), and I won't need to do any comparisons besides equality of course, but because UUIDs are quite long, I'm afraid that generating hashes from them would waste a lot of time.

Or is this something that depends too much on system and use specifics?

I'd rather use bigserial in most cases, but I've been told to use uuid for this. We need uuid because we might have multiple servers using different databases, so there isn't a guarantee that we'll have unique bigints. We could use a different sequence (and seed) for each server, but it's still not as flexible as UUIDs. For example, we wouldn't be able to migrate database entries from one server to another without converting the IDs and their references everywhere.


texts: Update multiple rows with different columns

texts:
I am trying to perform a bulk MySQL update where I know only certain columns need to be updated for each row, e.g. row A only "name" changed to "Sue", row B "name" and "address" changed to "Joe" and "Evergreen Terrace", etc. All the columns and data may be different.

Most multi-row examples typically update the same columns for each row, but is there a way for the SQL command to specify only what's changed per row?


texts: In terms of relational databases is a Relvar the equivalent to a Schema?

texts:
I've been reading up on relational databases and the term Relvar and schema keeps coming up. From my understanding:

A relvar is a variable holding a relation.

A schema is a logical definition of a table.

so I tried to imagine this in programming terms and I came up with this:

Relvar a = [Table 1 - Attributes a,b,c etc etc]
Schema b = [Table 1 - Attributes a,b,c...]

Is there any real difference?


texts: auto set value of a specific column after 24 hours and to delete record after one week: Oracle SQL

texts:
Here is my table:
create table passwordResetLink(
link_id number(5) primary key,
link_date timestamp(0) default sysdate NOT NULL,
isExpired number(2)
email varchar2(110) UNIQUE NOT NULL,
linkUsed number(2)
);

How can I auto update isExpired column? I mean when record is inserted its value should auto set to 0 and when 24 Hours passed the 0 should be updated as 1
link_date is timestamp of inserted record. The record that has passed one week should auto be deleted from the table

Thanks


texts: How can I connect MapGuide Maestro to PostgreSQL/PostGIS?

texts:
I am trying to connect my database PostgreSQL/PostGIS to MapGuide Maestro but it sends me an error message:

Do you have some idea about this or can you help me about how I can connect it with my database postgis?

I am using PostgreSQL 9.4, MapGuide open source 2.6.1 and MapGuide Maestro 6.0


texts: Improve select rows from table1 in table2 but not in table3

texts:
I have a SQL Server 2012 database and I'm trying to improve the following sentence:

Select c.CodeId from Code c
where c.AggregationLevelId = 2 and CodeId in
    (select AggregationId from Aggregation where AggregationId not in
    (select AggregationChildrenId from AggregationChildren))

I want to get the rows in Code table where its CodeId is in Aggregation table but it isn't in table AggregationChildren.

It works but it is very slow.

The relationships between the tables are: Aggregation.AggregationId and AggregationChildren.AggregationChildrenId are FK to Code.CodeId.

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    [ ... ]
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [ ... ],
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [ ... ],
    CONSTRAINT [PK_AGGREGATION_CHILDREN] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    [ ... ]
)

Is there a better way to improve that select?

This is the execution plan for the select:


texts:
I want to be able to Lock a row, select it, increment its value, and then release the lock. (without lockin the other rows, so that other connections can work with the rest of the table)

I've found this

BEGIN TRAN SELECT * FROM tablename WITH (HOLDLOCK, ROWLOCK)
WHERE ID = 1

My problem is I cant do

UPDATE tablename
SET columnName = -1
WHERE ID = 2

until I commit my previous transaction, why is rowlock locking the whole table?

edit:

Does this code guarantees that the data of the selected row is not update during this update command?

UPDATE [tablename] WITH (ROWLOCK)
SET columnName = columnName + 5
WHERE ID = 1


texts: Intentionally corrupting an index in PostgreSQL

texts:
I need to know if there is any method for intentionally corrupting an index in PostgreSQL.

I have been searching for it for a long time but never seen anyone mentioning such an act.


texts: Data that are sometimes entered and sometimes computed

texts:
Suppose I have a database of StorageComplex objects. StorageComplex has a guid Id, plus two data columns: Capacity (a number), and Location (a string).
My data also contain a table of StorageTank objects. A StorageTank also has an Id, Capacity and a Location. Application code will frequently treat a collection of StorageTanks at the same Location as an aggregated StorageFacility. The Capacity is the sum of the capacities of the individual StorageTanks.
It is guaranteed that there will never be a StorageTank at a location that also has a non-aggregated StorageFacility.
If it's relevant, the applications in question access the database via EntityFramework.
My question is about the advantages and disadvantages of different strategies to manage this situation. One could imagine maintaining aggregated StorageFacility objects in the StorageFacility table. Whenever a StorageTank at a location is added, updated, or deleted, one would have to apply a corresponding change to the aggregated StorageFacility at that location. Alternatively, one could imagine not maintaining such entries. Then every query for StorageFacilities would also need to check the StorageTank table.
In the real-life applications, everything is more complicated than in this toy example. There are a lot of fields that have to match in order for StorageTanks be aggregated together, there are multiple fields that need to be aggregated, and the rules for the actual aggregation are more complicated than just adding up some numbers. The StorageTanks have fields that StorageFacilities lack. Additionally, there are a lot of different queries that access the StorageFacilities. For some purposes, an aggregated StorageFacility should be treated the same as a non-aggregated one. For other purposes, they are different.


texts:
I have a column AddressIdentifiers of JSON type in my MySQL table Customers and the data sample looks like:

[
  {
    "code": "123",
    "identifier": "0219d5780f6b",
    "type": "BILLING",
    "info": null
  },
  {
    "code": "240",
    "identifier": "c81aaf2c5a1f",
    "type": "DELIVERY",
    "info": null
  }
]

I am using query:

SELECT JSON_EXTRACT(AddressIdentifiers, '$**.identifier') As Identifier, JSON_EXTRACT(AddressIdentifiers, '$**.type') As AddressType FROM Customers WHERE CustomerId = 10003;

to get output like:

Identifier   AddressType
------------------------
0219d5780f6b  BILLING
c81aaf2c5a1f  DELIVERY

But it does not give output like table rows instead gives concatenated values. How can we get the output from the JSON array like rows (as above)? Please advise.

I am using MySQL version 5.7.17.

Thanks,
Prabhat

EDIT-1:
I think if we join this Customers table with Address table (having columns: Id, identifier, Address, City, Pincode) that has the identifier as key then may be we can pull these values as row for each address?

So output will be something like:

 Identifier   AddressType   Address  City
    -----------------------------------------
    0219d5780f6b  BILLING      Address-1  Hyderabad
    c81aaf2c5a1f  DELIVERY     Address-2  Delhi


texts:
The function below returns a single row of data, and I have not been able to  modify it to  return all 2 rows.  Ive added a ROWS parameter at the bottom, then tried returning a SETOF, TABLE, and a few others - and all return errors. How can I return (using the date below) 3 rows, each formatted as json?  Thanks!

-- drop table mytable;
create table mytable (
retailerid integer not null,
retailername character varying(200),
createdate timestamp default current_timestamp,
constraint pkretailerid primary key (retailerid)
);
insert into mytable (retailerid, retailername) values (1000, 'Johns Burgers');
insert into mytable (retailerid, retailername) values (1001, 'Joses Tex-Mex');
insert into mytable (retailerid, retailername) values (1002, 'Anns BBQ');
-- drop view myview;
create view myview as select retailerid, retailername from mytable;
-- select * from myview;

-- with SETOF below returns "RETURN cannot have a parameter in function returning set"

-- drop function myfunc();
create or replace function myfunc()
  returns **SETOF** json as
$body$
 declare jrow myview%rowtype;
 begin
select retailerid,retailername into jrow from myview;
return row_to_json(jrow);
end
 $body$
  language plpgsql volatile
  cost 100;
--  select myfunc()


texts: MySQL: What happen when an executing sql's client is terminated?

texts:
Executing long-time sql like

UPDATE ACC SET A = 'EE' WHERE A IS NULL

then I found I made a mistake, so I kill my client. When I restarted client and executed another sql, like

UDPATE ACC SET A = 'EE' WHERE ID = 1888

but I got error:

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

I searched this error and found it was caused by uncommitted transaction holding locks. My question is:

Can close client connection stop sql execution?
If quesion 1's answer is NO, when sql query finished but client disconnected, will MySQL commit the result?


texts: Set Order of top 10 Product manually in database

texts:
I am creating a database for a mall. Client wants to set top 10 brands or stores manually. He wants to set order manually any time, for example:

store_Name     priority
Puma             1
Reebok           2
Dominos          3
Nike             4
NumeroUno        5

Product will be shows to user ordered by priority. Priority 1 will be shown at top.

My question is when I want to change the priority of store names like I want Nike to be shown at the top of search.

When I set the priority of Nike to 1 then automatically existing 1 position becomes 2 and 2 position becomes 3 and so on. How do I do this mechanism in SQL Server (stored procedure or Trigger)?

I am using SQL Server 2014 and my table has thousands of stores. I want 10 selectively store which is shown  at the top of mobile app.


texts:
Please find the below query:
select count(*) from v$session;

This has to be run on the database every 5 minutes. What is the right way to achieve this?
The query has to be run every 5 minutes on the same sql*plus screen. Instead of pressing "/" every minute, I wanted to understand if there is a way.
Database/Server details:
SQL> select * from v$version;
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

SQL> !uname -a
HP-UX ap12121 B.11.11 U 9000/800 ap12121 unlimited-user license


texts: Ordering groups/partition windows by other column values

texts:
I have a sample from a database I'm using in SQL server 2014 you can use here http://sqlfiddle.com/#!6/0d98e/1
It looks like this

+--------------+--------------------------+-----------------------------+------------+----+
| Event_number |          Venue           |            Date             | Attendance | rn |
+--------------+--------------------------+-----------------------------+------------+----+
|          247 | Air Canada Centre        | September, 21 2013 00:00:00 |      15504 |  1 |
|          215 | Air Canada Centre        | September, 22 2012 00:00:00 |      16800 |  2 |
|          296 | Allphones Arena          | November, 08 2014 00:00:00  |       9904 |  1 |
|          200 | Allphones Arena          | March, 03 2012 00:00:00     |      14537 |  2 |
|          311 | American Airlines Center | March, 14 2015 00:00:00     |      17160 |  1 |
|          266 | American Airlines Center | March, 15 2014 00:00:00     |      19324 |  2 |
|          344 | Amway Center             | December, 19 2015 00:00:00  |      14459 |  1 |
|          270 | Amway Center             | April, 19 2014 00:00:00     |      17000 |  2 |
|          322 | Arena Ciudad de México   | June, 13 2015 00:00:00      |      21036 |  1 |
|          298 | Arena Ciudad de México   | November, 15 2014 00:00:00  |      21000 |  2 |
+--------------+--------------------------+-----------------------------+------------+----+

The table is about sporting events that have returned to the same venue. I found the two most recent times an event took place at each Venue from another table and partitioned the results by date desc and then I selected the first two rows to produce this sample data.

What I want to do is order this information by Venue pairs (rn 1 and 2), with the Date desc where rn = 1 without splitting up the pairs.

So the result I am looking for would look like this:

+--------------+--------------------------+-----------------------------+------------+----+
| Event_number |          Venue           |            Date             | Attendance | rn |
+--------------+--------------------------+-----------------------------+------------+----+
|          344 | Amway Center             | December, 19 2015 00:00:00  |      14459 |  1 |
|          270 | Amway Center             | April, 19 2014 00:00:00     |      17000 |  2 |
|          322 | Arena Ciudad de México   | June, 13 2015 00:00:00      |      21036 |  1 |
|          298 | Arena Ciudad de México   | November, 15 2014 00:00:00  |      21000 |  2 |
|          311 | American Airlines Center | March, 14 2015 00:00:00     |      17160 |  1 |
|          266 | American Airlines Center | March, 15 2014 00:00:00     |      19324 |  2 |
|          296 | Allphones Arena          | November, 08 2014 00:00:00  |       9904 |  1 |
|          200 | Allphones Arena          | March, 03 2012 00:00:00     |      14537 |  2 |
|          247 | Air Canada Centre        | September, 21 2013 00:00:00 |      15504 |  1 |
|          215 | Air Canada Centre        | September, 22 2012 00:00:00 |      16800 |  2 |
+--------------+--------------------------+-----------------------------+------------+----+

I came up with a pseudo solution which was to basically concatenate each pair of rows into a single row, which I could then order by one of the chosen date columns. But I want to know if it's possible to do it while retaining pairs and for any future queries where I might want to sort partitioned/windowed results by specific guidelines.

Thanks for any help. Please let me know if I didn't provide enough information or if I was unclear in my explanation.


texts: Is tsvector the right choice for searching exact match of two words with variations like plural and singular?

texts:
I'm developing a search application that takes a string and then break it down into a combination of two words and then search a table for matching patterns. An example:

'My cat ate a rat'

search patterns: 'my cat' 'cat ate' 'ate a' 'a rat'

At the same time, this should match with plurals and singulars of the combination. So 'my cat' should match 'my cats' and also "my cat's". Stop words should not be ignored and the two words must be in the same order with no words between them.

My question is, is tsvector the right tool for this or can this be done using the 'LIKE' operator alone? I'm asking this to decide whether I need to spend time diving deep into tsvector to build my queries.

Thanks!

note: this is my first time here, please let me know if this is not the right place to ask this type of question.


texts: Linked servers - Upgrading from 2005 to 2012

texts:
Best way to find other server are linked to 2005 SQL Instance as I am migrating all to 2012 SQL Server.

I will migrate all linked server that is linked to 2005 SQL Instance but what is the best way to find other server in my environment are linked to 2005 SQL instance so I can change after migration to 2012 ?


texts:
I want to know if I fire query like insert into emp ...
What are the steps involved in query ?

It will go through InnoDB buffer pool?

Thanks for your help.


texts: How to use mySQL to generate auto-increment ID for me

texts:
This may be a bad structure. Let me know if it is, it's been a long time since I did any database design.

I have a tblGame in a mySQL database, which has two fields:

ID_Table
ID_Player

In the actual game, multiple players will sit on the one table.  So I have both fields set as a combined primary key. And ID_Table is set to auto-increment. ID_Player is an auto-increment in another table (tblPlayers).  What I'd like to do is to get the ID_Table value before any players are added to the table.  The actual game analogy is to an open table (with ID_Table already assigned) sitting there waiting for players to join. As they join, they get added to tblGame.

Is it possible to do this?


texts: Select permission denied when using linked server in a function unless inlining is off

texts:
We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production environment running SQL 2014.

The statement is:

select function(1234567)

The error message is:

  Msg 229, Level 14, State 5, Line 1
  The SELECT permission was denied on the object 'function', database 'database', schema 'dbo'.

The function is:

CREATE FUNCTION [dbo].[function](@var1 INT)
RETURNS BIT
BEGIN
    DECLARE @var2 BIT, @var3 BIGINT, var4 INT
    SET @var2 = 0

    SELECT @var3= ISNULL(tbl2.field1,tbl3.field1) FROM tbl1
    LEFT OUTER JOIN tbl2 WITH (NOLOCK) ON tbl2.field2 = tbl1.field2
    LEFT OUTER JOIN tbl3 WITH (NOLOCK) ON tbl3.field2 = tbl1.field2
    WHERE tbl1.field2 = @var1

    SELECT @var4 = ISNULL(field4,1) FROM linked_server.database.dbo.tbl4 WHERE field5=Convert(VARCHAR(20),@var3)
    IF(@var4 <> 1)
    BEGIN
        SET @var1 = 1
    END

    RETURN @var1
END

GO

Successful workarounds:

Individual users can execute the statement inside the function without error
Modifying the function to include the clause "with inline = off"
Adding the user executing the function to the sysadmin server role

I'm not sure if we've found a bug or if it's just a case of missing permissions (or something else entirely). We would like to avoid changing the code if possible; elevating user permissions to sysadmin is a non-starter.

We are running CU2. We have not tested on CU3.


texts:
Our database project is about creating a web application where the user can add or edit information. My concern is How do I connect Oracle 11g with my PHP?

Note: Our teacher did not teach us about doing all of these. Our teacher wants us to learn by "self-discovery." Hope you can help.


texts: Initializing Transactional Replication From two Different Database Backup

texts:
I have two different large Database (200GB for each) which I want to replicate their data to a central database (3rd DB). I followed this link and it was very useful for a single database.

But my problem is I have to restore two different backups to a single database. Is that possible at all?

I am working for a company which it is located in CityA. it has two branches in two different cities CityB and CityC. I have developed a software for them to insert the the customers' information and scan their documents. and I have developed another software for their boss to monitor the data inserted in CityB and CityC for the sake of doing some reports and analysis. until now I could do it by replicating the data form CityB and CityC to CityA with central subscriber topology and it was fine. But now the problem is the Databases in CityB and CityC have become very big (about 200GB for each). Now something happened and I had to establish the replication again. and it is now very cumbersome and time consuming and sometimes it gives strange and wired errors. the last problem I had was the snapshot agent is very very slow and it stops at the following message The process is running and is waiting for a response from the server which I asked here

I can replicate CityB to CityA a with Initialize using Backup but I don't know how to do the same for CityC?

another question is that do I have any better choice instead of replication at all?

please give me the practical advice or links as I am not a professional DB Admin and I am actually a programmer.

Instances are all sql server 2008R2, windows Servers are 2008R2 the connection between the cities are VPN over Internet.


texts:
I have a SQL Server 2000 machine that has a pretty big database on it - almost a terabyte in size.

There's a particular table that has been a source of some production issues and any attempt to try to fix it seems to cause the same problem....self-blocking (I know it's not a big deal on SQL Server 2000 SP4 but then a blocking spid of -2).

Even something like SELECT count(1) FROM <MyProblemTable> causes this spid(-2) to appear.  I went into the table properties and there's about 1.5 million rows which isn't much.  I have no problems running the same count statement on much larger sized tables.  I did notice the fragmentation is quite high on this table (around 45%) but any attempt to either rebuild or reorganize wreaks havoc on the system.  I tried running the reorganize during the weekend for like 8 hours and the job was still running and people were complaining of time-out issues.

Then the other day, I tried using the import/export wizard to copy the table to my local instance and it ran fine for the first 250K rows and I was also monitoring sysprocesses and saw the spid for the ETL process.  It was fine for those first 250K rows...then the wizard row count wouldn't increment anymore.  Then I check the spid on 2K box and it was blocked by spid(-2) again.

What the hell is wrong with the table?  Is there an integrity issue?  The only way to run SELECT statements against it without this happening is to use NOLOCK.


texts: Only specific variations of LIMIT are working for me?

texts:
I spent most of yesterday coding a "TOP 10" page for my website to pull from my database.

It was a pretty simple SQL statement:

SELECT * FROM `cards` GROUP By `name` ORDER BY `viewsperweek` DESC LIMIT 10;

It was all working perfectly for me last night and the info was correct but when I woke up this morning it no longer works.

After some testing it seems only LIMITS of 1-5 will work. If I put it past 5 now the SQL result now longer shows on the website (it DOES continue to work on the database itself).

So basically now I can seemingly only do this:

SELECT * FROM `cards` GROUP By `name` ORDER BY `viewsperweek` DESC LIMIT 5;

Can't for the life of me figure out why it would work last night and not this morning.
The code itself is being called from a js file which in turn calls the php file for the SQL code.


texts: How do I set the option use_remote_estimate?

texts:
Where do I set the "Cost Estimation Options" for postgres_fdw. Specifically I want to add use_remote_estimate.

test=# SET use_remote_estimate=true;
ERROR:  unrecognized configuration parameter "use_remote_estimate"


texts:
I have a MySQL instance running on my Ubuntu server that is eating up most of the memory on the machine.  I currently have 2 GB of ram installed on the machine, and MySQL is using 1983 MB.

Most of my experience is with SQL Development so I'm a little unsure about how to go about freeing up memory.  So far I've tried running FLUSH TABLES; but that didn't really do anything.  I also ran some diagnostic queries, but am not sure what to make of them:

SHOW ENGINE INNODB STATUS;

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22114338; in additional pool allocated 1048576
Dictionary memory allocated 1061936
Buffer pool size   512
Free buffers       0
Database pages     510
Modified db pages  33
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12944231, created 26985, written 3625935
2.17 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 976 / 1000

select sum(data_length+index_length) from information_schema.tables where engine='memory';
+-------------------------------+
| sum(data_length+index_length) |
+-------------------------------+
|                             0 |
+-------------------------------+

Can anyone make any suggestions about how to free up memory and prevent MySQL from eating up all memory on the server with the information I have provided?  If I haven't provided enough info here, any suggestions on what I should look at next to diagnose the problem?

UPDATE:

SHOW STATUS LIKE 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 31    |
| Threads_created   | 38    |
| Threads_running   | 1     |
+-------------------+-------+

and

SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     |
+-------------------+-------+


texts: Structuring change history data in MongoDB / NoSQL

texts:
I have a Chart on which I need to track changes; the Chart has several properties. A Chart also has a long list of items.

So in C#, my Chart object looks like this:

public class Chart {
    public int Id { get; set; }
    public string Title { get; set; }
    ...
    public List<ChartItem> Items { get; set; }
}

Or in JSON, my Chart looks like this (I wrote this JSON by hand, so it could be wrong):

{
    "Id": 1,
    "Title": "Test Chart",
    "Items": [
        {
            "Id": 222,
            "Title": "Test Item",
            "Price": 112.34
        }
    ]
}

So my question is as follows:

Should I log an entire chart with all the items as a single document, every time there is a change? (Although I read there may be a limit on document size, so this could be a problem.)

OR

Should I log Chart changes as one type of document and Chart Item changes as a separate type of document? This would allow me to store changes to chart items as a single "line item" document without having to save the entire Chart every time there is a change to a single chart item.

OR

Is there a better way?


texts: Permission for sequence in another schema

texts:
Postgres 9.3
Debian 7.0

I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same user needs usage on the sequence of the primary key of this table, but it says "ERROR: permission denied for sequence"

The original table and its sequence belongs to schema A. This users's schema B has an insert-able view of this table T. I cannot grant usage on schema A for this user, otherwise he will be able to see the names and definition of all my tables.

The question is: Is there some way to create some kind of view for this sequence so he can call nextval() and currval()? The goal is making this sequence usable for this restricted user without giving him access to the main schema where the sequence actually belongs.


texts: Returning surrounding words when searching a postgres tsvector

texts:
I'm currently generating and storing a tsvector of all words found inside a jsonb object when saving to the database. This allows me to perform a quick text search and locate records that contain the search term. It only serves as a selector, though, and doesn't provide me with any additional context. What I would like to do is be able to provide an "excerpt" when returning my results. Something like the 3 words before and 3 words after the found search term, when I return my results.

Is this something that can be done with Postgres directly, or will I need to switch to something more comprehensive like ElasticSearch?


texts: Allow Null Columns vs separate 1-to-many tables

texts:
Perhaps similar questions have been asked, but this is re having null columns.

I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better to have 5 or 6 additional columns on the one table that could reference IDs of other tables, or have 5 or 6 extra tables that provide a link if there is a need for them.

My inclination is that fewer null entries are better, but then joining the tables when accessing the database could have a higher cost?


texts: DBeaver 5.3.2 reconnect after db change (CREATE TABLE call)

texts:
SQL scripts work on the active object in DBeaver. If you add a new table to your active database via an SQL statement in a script, DBeaver does not automatically update the navigator entries. Even when chosing to reconnnect to the database, the navigator stays not updated. To see the changes, the user has to disconnect and then connect to the database server again. Additionally, the active database changes in my case everytime I connect to it. I use postgresql with two databases: one custom database I change with scripts and the "postgres" database, which always becomes the active object by default.

Do you have the same obstacles, when creating new tables, or is there a way to work without the need to close and start the database connection?

Is there a way to attach a script to a special database without having to set one database to be the active one by hand?


texts: Assessing the normal form for a relation with a multi-valued attribute

texts:
Assume that you have designed the following schema of relations:

COURSES (CID, CNAME)
STUDENTS(SID, SNAME, FAV_CIDS)

However, you can not have a relation with set valued attributes (Did not really understand what was meant by this) and you
need to convert it to a normal form. What is the normal form and the resulting schema?

We are studying SQL at school. Could not really make out which normal form this was in and functional dependencies.


texts: Select TOP @N where @N is a variable from another Query

texts:
I am building a report on server hardware projections, and I'm getting stuck here.

I have a query that correctly returns the number of HDD's installed on the server, and am storing that number into a variable called @OtherHDDs, and it is an INT.

I need to now do this...

Select top @OtherHDDs....
from ...

How do I do this correctly?


texts: Analyzing a query plan for a query with TWO predicates, but index only has ONE

texts:
Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL.

One homework question I'm doing right now is to analyze some query plans for a query with two predicates, specifically:

SELECT *
FROM Student
WHERE wam > 75 AND faculty = 'Arts';

however the plans provided assume an index only on one of the predicates, such as an unclustered B+ tree index on (wam).

One example part of the question is as follows:

  Compute the estimated cost of the best plan assuming that an
  unclustered B+ tree index on (wam) is the only index available.
  Suppose there are 60 index pages. Discuss and calculate alternative
  plans

How would I go about calculating the cost of a plan like this? Would I have to calculate costs for unclustered/clustered B+ tree and hash indexes for the other predicate, faculty, as well? I am aware that one alternative plan is also simply a full table scan.

"Student" has 800 pages, 64000 tuples. "faculty" can take 10 unique values. "wam" can take 100 unique values.

Thank you, if I'm not being specific enough feel free to point out anything lacking in this post.


texts:
I have production database with a size of 1TB in SQL Server 2008. I can't close the database for backup/restore process, because it needs so long time for perform this operation.

How to migrate this database to another server which has SQL Server 2016?

I am thinking of creating a database on the new server and then bulk inserting the existing data. But what about logins, roles, permissions? How to figure it out this problem ?

Thanks,


texts: Reordering rows in a chapter table when creating, sorting, and deleting

texts:
I am creating an application where an Author can create a Book with say 10 Chapters, and let Readers read it. But I feel that the Author should be able to rearrange Chapters in order, sometimes DELETE a Chapter, or INSERT a new Chapter in between others.

My idea so far is to add a column to my chapter table called chapter_number and let the users add the numbers to it when creating or editing chapters, and based on that sort it when fetching it from the DB.

I am having trouble understanding how I should handle extra additions, say, an Author wants to INSERT a Chapter with chapter_number as 4, and expect the rest of the rows to automatically sort themselves. I understand that the way to handle this is to have chapter_number saved in multiples of 10 or 100 so that we have a 'room' between the rows.

But it feels a bit hacky to me. Is there a better way?


texts: How can I update query to use GROUP BY instead of DISTINCT while still preserving ORDER BY?

texts:
Trying to make this query more performant (basically I need to have distinct speech_ids from the speech_candidate table while at the same time counting the number of duplicate speech_ids for each of the unique rows from the speech_candidate table we're pulling):
SELECT
  *
FROM
  (
    SELECT DISTINCT ON (speech_candidate.speech_id)
       speech_candidate.speech_id
     ,(speechtype_id = 7) AS is_position_paper
     ,(speechdate IS NULL) AS is_null_date
     ,(
        SELECT
          COALESCE(shorttitle, '')
        FROM
          office oc
        JOIN
          office_candidate oc2 USING (office_id)
        WHERE
          oc2.candidate_id = candidate.candidate_id
            AND officecandidatestatus_id = 1
        ORDER BY
          rank
        LIMIT 1
      ) AS shorttitle
     ,speech_candidate.speech_candidate_id
     ,speech_candidate.speech_id
     ,speech_candidate.candidate_id
     ,speech.speech_id
     ,speech.title
     ,speech.speechdate
     ,speech.location
     ,speech.key
     ,speech.quote
     ,candidate.candidate_id
     ,candidate.firstname
     ,candidate.middlename
     ,candidate.lastname
     ,candidate.nickname
    FROM
      speech_candidate
    INNER JOIN
      candidate
        ON speech_candidate.candidate_id = candidate.candidate_id
    INNER JOIN
      office_candidate
        ON candidate.candidate_id = office_candidate.candidate_id
    INNER JOIN
      office
        ON office_candidate.office_id = office.office_id
    INNER JOIN
      speech
        ON speech_candidate.speech_id = speech.speech_id
    WHERE
      office_candidate.state_id IN ('NA', 'AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NC', 'ND', 'MP', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VI', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY')
        AND office_candidate.officecandidatestatus_id = 1
        AND office.officetype_id = 'C'
        AND speech.release_id >= 1
    ORDER BY
      speech_candidate.speech_id
     ,is_position_paper
     ,is_null_date
     ,speech.speechdate
  ) sc
ORDER BY
  sc.is_position_paper
 ,sc.is_null_date
 ,sc.speechdate DESC
LIMIT 50 OFFSET 0

Would it be better to do this via GROUP BY  or is DISTINCT really what I want to use here? Again, I need to both dedupe AND add a column with a counting of the duplicates for each row.


texts: In SQL Server 2005 how would I determine the minimal permissions a user requires?

texts:
I'm the accidental DBA of my group and I've inherited a database that follows a number of bad practices.  Currently every login is a member of the sysadmin group.

What is the easiest way to determine the minimal amount of privileges needed for each account?  I know I can use SQL Server Profiler to log all the SQL commands executed but it will return a bunch of information.  I can not think of an easy way to summarize all that data.

Any help would be appreciated.


texts: Mysql replication, master_log_pos sync but tables not update

texts:
First, my replication config on Master:

replicate-do-db         = mydb
binlog-do-db            = mydb
expire_logs_days        = 2
max_binlog_size         = 100M
binlog_format           = MIXED
log-bin                 = /home/mysql/mysqld-bin
server-id               = 1

On Slave:

replicate-do-db         = mydb
binlog-do-db            = mydb
expire_logs_days        = 2
max_binlog_size         = 100M
binlog_format           = MIXED
log-bin                 = /home/mysql/mysqld-bin
server-id               = 2
relay-log-info-repository=TABLE

All tables use InnoDB (XtraDB), the database is used for a Prestashop 1.5.6.2.

Both servers are on Gentoo 2016, Mariadb 10.1.14.
Master use 2 x SSD, slave 2 x HDD.

When I start the slave, it works good, IO and SQL are running, bin cursor increase correctly but some tables are not sync.
There is no error in mysql log.


texts: What is the maximum number of parameters I can pass using the SQL IN clause in SQL server 2008 and SQL server 2000?

texts:
My program lets people pick items from a list to help run a query. To return the minimum amount of data, I've had to use the IN clause.

I'm pretty sure there's a limit to how many items I can put in the IN clause of one SELECT statement, but I don't know exactly what the limit is. For my case, I need to know the limit for SQL server 2000 and SQL server 2008.

Thanks!


texts: Getting the "Column name or number of supplied values does not match table definition" when I use CTEs in an IF statement inside a stored procedure

texts:
I have a Stored Procedure that has 3 parameters. The stored procedure looks like this:
ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '')
AS

SET NOCOUNT ON
DECLARE @transmsg varchar (80)

IF @Load = 1
BEGIN

;with cte1 as (

 select top 100 percent
    field1,
    field2,
    field3
 from dbo.table1

 order by field1
), cte2 as (
  select top 100 percent
     field4,
     field5
  from dbo.table2

  order by field4
)

select distinct
 _cte1.*,
 _cte2.*
from cte1 _cte1
inner join cte2 _cte2 on _cte2.field4 = _cte1.field1

END

The stored procedure compiles just fine: however, when I try to execute it using the parameter @Load= 1, for example, (exec Trans_sp_test @Load = 1), I get an error:
"Column name or number of supplied values does not match table definition".
I have verified the columns multiple times and ensured that I have the correct number of columns, but I am still getting the error. At this point, I'm not sure what am I missing, or if there's an error in my code. I appreciate all your help.


texts: Postgres : Multiply length of array to count aggregate result

texts:
Following command works fine :

select count(*) * 2 from rooms;

But if I am trying to do something like :

select count(*) * array_length(students,1) from rooms;

where students is an array column in the table rooms. Following error is thrown :

ERROR: column "rooms.students" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 32

What is the right way to get the desired result?


texts: Can't we make a non-primary key or non-unique key in a table as a foreign key in another table?

texts:
I have got 4 tables named Blood_Bank,Consumer,Employee and Donor.Blood_Bank has SB as primary key which is a dummy attribute.I did not take bank_id as primary key since it can be repeated.To identify each repeated values,I have added one more column called SB.I took bank_id(neither primary nor unique)as the foreign key.These are my tables:

CREATE TABLE Blood_Bank(
    SB varchar(10) PRIMARY KEY,
    bank_id varchar(10) UNIQUE,
    bank_name char(25) NOT NULL,
    bank_add varchar(50) NOT NULL,
    bank_phone varchar(12) NOT NULL,
    b_id varchar(5) NOT NULL,
    b_grp varchar(5) NOT NULL,
    b_quantity varchar(4)NOT NULL,
    b_RHfactor varchar(5)NOT NULL);

CREATE TABLE Consumer(
    c_id varchar(20)PRIMARY KEY,
    c_name char(20) NOT NULL,
    c_add char(50) NOT NULL,
    c_blood varchar(10) NOT NULL,
    c_phone varchar(12) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

CREATE TABLE Employee(
    emp_id int PRIMARY KEY,
    emp_name varchar(30) NOT NULL,
    emp_dept varchar(30) NOT NULL,
    emp_salary varchar(30) NOT NULL,
    emp_desgn varchar(40) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

CREATE TABLE Donor(
    d_id varchar(20)PRIMARY KEY,
    d_name char(20)NOT NULL,
    d_blood varchar(15)NOT NULL,
    d_phone varchar(20) NOT NULL,
    d_gender char(20) NOT NULL,
    d_age varchar(15) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

But,it is giving me the following error for the tables Consumer,Employee and Donor.

  ERROR:  there is no unique constraint matching given keys for referenced table "blood_bank".Please help.


texts:
I have servers where I use proxy accounts to run ETL processes or even to backup SSAS databases using powershell through a job.

I can see what AD Accounts are related to my proxies:

use msdb
go

select
    s.job_id
    ,s.name as [job_name]
    ,js.step_id
    ,js.step_name
    ,js.subsystem
    ,js.proxy_id
    ,js.command
    , c.credential_identity
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
INNER JOIN sysproxies p ON js.proxy_id = p.proxy_id
INNER JOIN sys.credentials as c on c.credential_id = p.credential_id

I can script out roles and logins and even table types but now I need to script out my proxies.

QUESTION:
How to script out proxies?

I Want to script out the following:

I have got this so far:

IF OBJECT_ID('TEMPDB..#tmp_sp_help_proxy','U') IS NOT NULL
   DROP TABLE #tmp_sp_help_proxy

create table #tmp_sp_help_proxy(proxy_id int null,
                               name nvarchar(128) null,
                               credential_identity nvarchar(128) null,
                               enabled tinyint null,
                               description nvarchar(1024) null,
                               user_sid varbinary(40) null,
                               credential_id int null,
                               credential_identity_exists int null)

insert into #tmp_sp_help_proxy(proxy_id, name, credential_identity, enabled, description, user_sid, credential_id, credential_identity_exists)
exec msdb.dbo.sp_help_proxy

SELECT
tshp.name AS [Name],
tshp.proxy_id AS [ID],
CAST(tshp.enabled AS bit) AS [IsEnabled],
ISNULL(tshp.description,N'''') AS [Description],
tshp.credential_id AS [CredentialID],
ISNULL(sc.name,N'''') AS [CredentialName],
ISNULL(tshp.credential_identity,N'''') AS [CredentialIdentity]
FROM
#tmp_sp_help_proxy AS tshp
INNER JOIN sys.credentials AS sc ON sc.credential_id = tshp.credential_id


texts: Dealing with disk space full in postgresql

texts:
I have a Django web application with postgresql 9.3.10 backend (sitting in a Linux OS). I ran into disk full error, such that even if I try to truncate a table, I get errors of the sort:

ERROR:  could not extend file "base/30137/33186048": No space left on device
HINT:  Check free disk space.

I can't easily add more disk space to the server, nor can I delete stuff on this VM. However there are several tables that are candidates for truncation, but seems I can't truncate them now either.

Can anyone give me advice on what I can do here? This is hitting my production server hard, and I'm a bit of an accidental DBA here, so totally stumped.


texts: Executing stored procedures in oracle inside a package using dbms_job

texts:
I am trying to execute multiple stored procedures through package using DBMS_JOB. Even if the job is getting submitted and is showing in USER_JOBS its not getting started for some reason. I have tried with and without putting the 'START DATE' parameter with the same result i.e. no start.

I am using ORACLE 9i and i guess i cannot use dbms_scheduler.

Find below the code :

--PACKAGE BODY--

create or replace
PACKAGE BODY PKG_TEST IS

  PROCEDURE PASSPORT_DC_1 IS

  l_jobno binary_integer;
  l_jobno1 binary_integer;

    BEGIN

       dbms_job.submit(l_jobno1, 'BEGIN SP_ABC(); END;',SYSDATE );
       DBMS_OUTPUT.PUT_LINE('SP_UPDATE_TIDCUMTL :' || l_jobno1);
       COMMIT;

       dbms_job.submit(l_jobno, 'BEGIN SP_XYZ(); END;',SYSDATE);
       DBMS_OUTPUT.PUT_LINE('SP_UPDATE_TIDPTDTL :' || l_jobno);
       COMMIT;

  END PASSPORT_DC_1;

--PACKAGE SPEC--

create or replace PACKAGE PKG_TEST AS

  PROCEDURE PASSPORT_DC_1;

END PKG_TEST;


texts: Application Continuity on Single instance

texts:
I recently was going through Application Continuity in Oracle 12c. As I read through the Oracle link which I understand when used in RAC, will be able to replay the transaction (DDL/DML) in the event the node goes down.

In the link, it also mentions creating a service for non-RAC (standalone) instances as well. How does Application continuity work in case of single instance.

declare
params dbms_service.svc_parameter_array;
begin
params('FAILOVER_TYPE'):='TRANSACTION';
params('REPLAY_INITIATION_TIMEOUT'):=1800;
params('RETENTION_TIMEOUT'):=604800;
params('FAILOVER_DELAY'):=10;
params('FAILOVER_RETRIES'):=30;
params('commit_outcome'):='true';
params('aq_ha_notifications'):='true';
dbms_service.modify_service('[your service]',params);
end;
/

Say, if a recoverable error occurs on the single instance, where does it replay the transaction? Can someone help me understand this?


texts:
Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files.

What is the best way to implement this?

PostgreSQL version: 9.3.5.0

 BEGIN

 RETURN QUERY SELECT text
    FROM eightks
    WHERE other_events = true
    AND text ~* '(\y(chief executive officer)\y)'
    AND text ~*'(\y(cancer)\y)'
    \o /Users/XXXX/desktop/test.txt;

 RETURN QUERY SELECT text
    FROM eightks
    WHERE other_events = true
    AND text ~* '(\y(chief executive officer)\y)'
    AND text ~* '(\y(sudden)\y)'
    \o /Users/xxxx/desktop/sudden.txt;

  END

Here is the follow ERROR message

    edgar=# \i test3.sql;
    psql:test3.sql:21: ERROR:  syntax error at or near "RETURN"
    LINE 2: RETURN QUERY SELECT text
    ^

Here is also the structure of the table if it helps;

     edgar=# \d eightks
           Table "public.eightks"
       Column    |   Type   | Modifiers
   --------------+----------+-----------
    id           | integer  |
    doc_id       | bigint   |
    text         | text     |
    tsv          | tsvector |
    other_events | boolean  |

Thanks very much


texts: How to access row value from UPDATE sub query function?

texts:
I would need to use UPDATE like this:

update offer set (bank, bank_id) = (cost, gid)
from (
select cost, osm_points.gid from pgr_kdijkstraCost(
  'SELECT gid as id, source, target, walk_cost as cost FROM ways',
  offer.vertice_id,
  array(select vertice_id from osm_points where super_type = 'bank'),
  false,false)
join osm_points on id2 = vertice_id
where cost != -1
and osm_points.super_type = 'bank'
order by cost
limit 1)t ;

but I get following:

  ERROR:  invalid reference to FROM-clause entry for table "offer"

  HINT:  There is an entry for table "offer", but it cannot be referenced from this part of the query.

Thank you in advance


texts:
I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We do not have enough DB free space in order to store the full backed-up table.

Which is the best way? Is there a way to do this by blocks? I mean something like: backing up the first 100K rows from the original table, updating those 100K rows in the original table, deleting those 100K rows from the backed-up table, backing up the following 100K rows from the original table, and proceeding analogously. Is this feasible?


texts: What security threats does the SA account and other known account names pose to security?

texts: Does an known account name like sa, pose a security threat to database? When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout after 5 times)?


texts: Subscriber database to be in sync using Distributor

texts:
I have database D1 as a publisher, D2 as a subscriber and they are using Distributor 'DIST' which is reside on the subscriber side. Both D1 and D2 database having DR databases called DR1 and DR2. I have setup the Tran. Replication from D1 to D2, D1 to DR1 and D2 to DR2 as shown in below diagram:

When my production incoming traffic is on to database D1 and data is replicating to databases D2, DR1, and DR2. so obviously there might be some delay in data transferred to DR1, and DR2. so lets say a disaster happen and my D1, Dist, and D2 database server got crashed, in that case i have to divert my traffic to DR1 and i have to setup  Tran. replication between DR1 to DR2 (with Nosync option, as i don't want to reinitialize it) but because of delay in data transfer there might be data mismatch between DR1 and DR2.

is there any setting on distributor that can give me the surety that the data transfer to DR1 and DR2 be in same transaction. or is there any alternative ways to handle this problem ?
Note: i am using SQL SERVER 2008, Transactional Replication. I can not afford to re-initialize the replication between DR1 and DR2.


texts: How can I lock row for reading/updating in DB2?

texts:
I am trying to resolve a race condition in my application.

I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it reads the ID's of available jobs, then it reserves them and gets the info.

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL LIMIT 5; --Check available jobs
UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?); --Lock jobs
SELECT * FROM JOBS WHERE ID IN(?); --Get info about jobs to process

Issue arises when multiple threads go at this table at the same time. This if happens if 'Thread#2' reads the row before 'Thread#1' runs the UPDATE. Causing same jobs to run twice.

I have found a solution by wrapping the queries in:

LOCK TABLE JOBS;
--Queries from above
COMIT;

This works fine and prevents race conditions but it is a bit extreme, because threads have to wait for each other to finish.

How can I make sure that I can place a lock only on records from the following query?

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL LIMIT 5;

This is on AS400 DB2 database, now LUW


texts:
The condition staff.staffId = staffId in the following stored function apparently always evaluates to true because staffId is scoped to the (inner) column in table staff instead of the (outer) argument of the function endpoint_organizations.

CREATE OR REPLACE FUNCTION endpoint_organizations (staffId INTEGER)
RETURNS TABLE (organizationId INTEGER, name TEXT)
AS $$
  SELECT organizations.organizationId, name
  FROM organizations
  INNER JOIN staff
  ON staff.organizationId = organizations.organizationId
  WHERE staff.staffId = staffId
$$ LANGUAGE SQL;

Is there are way for affecting the scoping in a concise way that does not require changing the name of the argument e.g. into staffId1. I've tried changing the condition into (staff.staffId AS staffId1) = staffId, but that does not seem to work.


texts: slow UPDATE of rows by COUNTing from another table

texts:
I try to update a table by counting rows from another table as

UPDATE t1 SET c1 = (
    SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
    )

strangely, this query stuck at Sending data state for hours.

SELECT COUNT(*) FROM t2

returns ~16M in 2 seconds.

SELECT id FROM t2 GROUP BY id

returns 50K rows in 5 seconds.

The table structures are very simple

CREATE TABLE t2
(
item int(11) unsigned NOT NULL,
id mediumint(7) unsigned NOT NULL,
PRIMARY KEY (item,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci


texts: Stop SQL Server service(s) before defragmenting drive?

texts:
Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.

We scheduled a task to defragment this drive at 3:00 a.m. on a Saturday morning. The job completed after 40 minutes with no apparent errors. However, the drive remains heavily fragmented.

Should we have stopped SQL Server service(s) before defragmenting?

CONTEXT

Per requests for context: We have a Microsoft SQL Server 2005 instance (9.00.5324.00) running 32-bit Windows Server 2003 (SP2) on Dell PowerEdge 2950 hardware, circa 2007, with 4GB RAM. The PowerEdge 2950 has four 68GB drives configured as RAID-1 to create two 68GB virtual disks: (1) C (boot and OS) & D (pagefile, miscellaneous other data); and (2) E (SQL data). To my knowledge, IT staff have never defragmented any of these drives...Disk Defragmenter reports file fragmentation of 66% (C), 77% (D), and 99% (E). Performance Monitor reports the following average results: "Paging file: % usage" = ~6.8%; "SQL Server: Buffer Manager - Page life expectancy" = 20 seconds; and "PhysicalDisk: Avg. disk sec/write, drive E" = between 300 and 1,100 ms. We're due for a much-needed hardware and SQL Server upgrade in a few months time (viz., new hardware, 64-bit Windows Server 2012, 64-bit SQL Server 2012, 12GB RAM), but, due to end-user performance, want to alleviate the issue as much as possible. Thus the thinking a file defrag might help for drive E, the main SQL data drive.

As an aside, last week we pulled two failed drives and rebuilt the array...not sure that matters. We contract with another IT team to maintain the server, so we do not have direct access to the equipment...our organization just pays for services.

We can afford the downtime during regularly scheduled maintenance windows (weekly) as well as out-of-band downtime, as necessary, overnight.


texts: SSIS comparison load between new, updated and deleted records and populate a staging database

texts:
So far,

My control flow looks like:

Execute SQL Task
- This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be able to track the updates that have occurred. My code for this is:

IF OBJECT_ID('CDC_Staging', 'U') IS NOT NULL
   DROP TABLE  CDC_Staging;

CREATE TABLE CDC_Staging
(
    [Employee_ID] [int] PRIMARY KEY NOT NULL,
    [FirstName] [nvarchar](255) NULL,
    [LastName] [nvarchar](255) NULL,
    [Education] [nvarchar](255) NULL,
    [Occupation] [nvarchar](255) NULL,
    [YearlyIncome] [float] NULL,
    [Sales] [float] NULL
);

Data Flow Task
- The OLE DB Source is the Source Database. The Lookup uses the results of this query:

SELECT [Employee_ID] FROM [CDC_Target]

Then I use a conditional split that puts inserts in the Target Database and Updates to Staging

My Final control flow task is an execute SQL Task. I used this query to update the target database.

UPDATE [dbo].[CDC_Target]
   SET [FirstName] = Staging.[FirstName]
      ,[LastName] = Staging.[LastName]
      ,[Education] = Staging.[Education]
      ,[Occupation] = Staging.[Occupation]
      ,[YearlyIncome] = Staging.[YearlyIncome]
      ,[Sales] = Staging.[Sales]
FROM [CDC_Target]
   INNER JOIN
     [CDC_Staging] AS Staging
     ON [CDC_Target].Employee_ID = Staging.Employee_ID

I just inserted 4 more records into my source table. After I ran the package, the Source Table and Target table had the correct data but the staging table did not contain the 4 inserts that I committed.

Am I doing something wrong here? Any suggestions on how to better do this?

PS. I followed a tutorial explaining how to do this, I'm a Jr. DBA but my team lead is wanting me to learn SSIS.


texts: Making a recursive CTE like query, but for many records

texts:
I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code :

declare @Id integer

;WITH CTE AS
(
    SELECT  a.[Id],
            a.[ParentId]
    FROM    [Area] a WITH (NOLOCK)
    where a.[Id] = @Id

    UNION ALL

    SELECT  a.[Id],
            a.[ParentId]
    FROM [Area] a WITH (NOLOCK)
    INNER JOIN CTE cte ON cte.[ParentId] = a.[Id]
)

SELECT top 1 a.[Id]
FROM CTE a
WHERE a.ParentId is null

It works as long as I'm working with one id, here @Id.

But I can't find a way to do the same but for a list of Ids, without using cursors, as CTE seems to be OK only when y'oure working on one record.

Could someone point me at the right direction ?

Many thanks


texts:
I truncated the wrong table, but I have a backup made an hour before the incident.

I am trying to restore but at 30% I get the error:

  Restore of database 'msdb1740' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  System.Data.SqlClient.SqlError:
  Write on "C:\...\Backup\msdb1740_LogBackup_2016-03-19_09-50-09.bak"
  failed: 112 (There is not enough space on the disk.)
  (Microsoft.SqlServer.SmoExtended)

I have freed up some extra disk space but had no luck. I have transferred the .bak file to another computer and tried it there but that gives me a permissions error.

How can I restore this database, or even better, can I restore just one table in the database?


texts:
I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows.

CREATE TABLE `test` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM

If in on one connection, I execute something like INSERT INTO test VALUES (0, sleep(60)) and in a second process, INSERT INTO test VALUES (1, 0), the second insert will wait for the first to complete. Note that innodb_table_locks is off. Is there something I'm missing, or is this just not possible?

Update

As Randy points out below, I created a MyISAM table during testing and then assumed that concurrent inserts weren't possible for my other tables (which are InnoDB). I actually don't have a problem with InnoDB tables.


texts: Help with an entity-relationship diagram re Manager entity type and its relationships

texts:
In the below ER diagram:

A manager can have many Users but a User can only have 1 Manager
A Manager must be able to assign multiple users to multiple courses

Are the manager relationships correct?


texts: Is Oracle Wallet available for use with Oracle XE 11gR1

texts:
Oracle XE 11gR1
RHEL 6.3

Is Oracle Wallet available with Oracle XE for production environments? or any environment?
Google search turns up nothing specifically declaring it not being available.


texts: How in Oracle can I export package that will have package and body?

texts:
I'm using putty. How in Oracle can I export package and its body to a file in present directory using spool?

Thanks


texts: Include failed step's error details in database mail for failed agent jobs

texts:
I receive database notification mail whenever a job fails. Error message of the failed job (Step Id=0 in the Job History) is included in the mail;

  MESSAGES: The job failed.  The Job was invoked by User SomeUser.  The
  last step to run was step 1 (FailingStep).

but not the step's failure message (Step Id > 0 in the Job History).
I also want to see the step's message so that I can identify the root cause directly from looking at the mail. For example, step 1's message in the above example.
Is it possible to include a job step's failure message within the alert mail?


texts: PostgreSQL 9.2; Altering a table to use another sequence

texts:
I've created a table like so:

create table doki_data_item2 (like doki_data_item including defaults);

but this new table uses the same sequence as the old table.

So two questions:

How can I copy and table and the necessary sequences easily?
How can I replace a sequence on one table with another sequence?


texts:
In my database, I have columns that should contain text pieces.

What is the best data type used for texts in SQL?
How can I show that text as a numbered or bullet list?
Can I search for a certain word or sentence in that text?
Can I make some words in the text piece as a hyperlinks to refers to another columns content?

Example about the Augmentin medicine

Augmentin is a penicillin antibiotic.
Augmentin contains a combination of amoxicillin and clavulanate  potassium.
Amoxicillin is an antibiotic in a group of drugs called penicillins.
Clavulanate potassium is a form of clavulanic acid, which is similar  to penicillin.
Augmentin is used to treat many different infections caused by bacteria, such as sinusitis, pneumonia, ear infections, bronchitis, urinary tract infections, and infections of the skin.


texts: SQL Server 2012 Installation Error on Windows 2008 Server

texts:
I am facing an error while trying to install SQL Server 2012 EXPRESS ADVANCED EDITION(32bit) on Windows Server 2008 Enterprise(64bit).

After passing Rule Check, at install updates section, error occurs about update could not be downloaded. On clicking next, following error in dialog box occurs.

SQL Server Setup has encountered the following error:

Setup encountered a failure while running job UpdateResult.

Error code 0x876E0003.

How can I solve this?


texts:
I am trying to design a database for mark sheet data of a school having different types of exams of different levels or class.
Is it possible to have single table where I can store data in string (array serialize PHP) and perform a search based on subjects easily (Maximum/Minimum/Average).

The table look something like this with bit variations-

Class 5

XXXXX ------ Math ----- Hindi ----- English
Amit -------- 10 ------- 20 ----------31
Alok -------- 10 ------- 20 ----------31

Class 8

XXXXX ------ Math ----- Hindi ----- English ---- Science
Amit -------- 10 ------- 20 ----------31 ----------20
Alok -------- 10 ------- 20 ----------31 ----------23

Using PHP's array serialize I will have marks data in string as

a:4:{s:4:"math";i:20;s:5:"hindi";i:30;s:7:"english";i:23;s:7:"science";i:10;}

Should I have separate table or can I have the condensed table so that database does not become too heavy with too many rows?


texts: Can't restore SQL Server 2016 backup to SQL Server 2014

texts:
We are having an issue at trying to restore a database:

  System.Data.SqlClient.SqlError: The database was backed up on a server
  running version 13.00.4001. That version is incompatible with this
  server, which is running version 12.00.2269. Either restore the
  database on a server that supports the backup, or use a backup that is
  compatible with this server.


texts: Could Not Be Accessed Value Of The For Loop Counter In PL/SQL

texts:
I try to learn PL/SQL for loops in tutorials. I learn flow of control in a for loops. (And i saw that it is same with most of the other programming languages)

Here is the flow of control in a for loop:

 1. The initial step is executed first, and only once. This step allows
   you to declare and initialize any loop control variables.

 2. Next, the condition, i.e., initial_value .. final_value is evaluated.
   If it is TRUE, the body of the loop is executed. If it is FALSE, the
   body of the loop does not execute and flow of control jumps to the
   next statement just after the for loop.

 3. After the body of the for loop executes, the value of the counter
   variable is increased or decreased.

 4. The condition is now evaluated again. If it is TRUE, the loop
   executes and the process repeats itself (body of loop, then increment
   step, and then again condition). After the condition becomes FALSE,
   the FOR-LOOP terminates.

And then i  want to test this steps for my worry in my TOAD IDE. I use simple sample code to test as below.

DECLARE
   a number(2) ;
BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
END;
/

Now this loop start initial counter, initial_value and final_value. counter is 'a', initial_value is 10 and the final_value is 20. And the counter a  will be the first in 20 because of the REVERSE keyword. The loop body is executed for 20. After the body is executed a is decreased. And it will be 19. And then the condition is eveluated. The condition is true because of 19 is in [10, 20]. and then for loop body is executed. This operations is contiuned at the same way. When counter a's value is 9 and then condition evaluated to FALSE.And then the loop is terminated. Flow of control jumps to the next statement just after the for loop.The counter's last value is 9. And i want to see it 9 value. so that i run below code.

DECLARE
   a number(2) ;
BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
   dbms_output.put_line('THE FINAL VALUE OF a: ' || a);
END;
/

When the above code is executed, it produces the following result:

value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10
THE FINAL VALUE OF a:

But i hope see the result like as below:

value of a: 20
value of a: 19
value of a: 18
value of a: 17
value of a: 16
value of a: 15
value of a: 14
value of a: 13
value of a: 12
value of a: 11
value of a: 10
THE FINAL VALUE OF a: 9

Why the counter 'a' is not connected after the loop. Why I did not see it's value to 9. Does'nt the program compiler made a real way assignment to counter 'a' ??


texts: Choosing the right table layout for a scenario of Books Editions

texts:
Let's say for example that I have to organize this type of information: books, books editions, colors of every page, reactions. Books can have one or many editions.

I can't decide if it's best to do:

Books [bookid, author]
Editions [editionid, bookid, title, publisher, isbn, release_date]
PagesColors [editionid, page, color]
Reactions [userid, editionid, reaction]

or removing editions table and using one table with bookid_parent to create them:

Books [bookid, bookid_parent, author, title, publisher, isbn, release_date]
PageColors [bookid, page, color]
Reactions [userid, bookid, reaction]

or:

Books [bookid, bookid_parent, author, release_date]
BooksDetails [bookid, title, publisher, isbn, ..other details without index]
PagesColors [bookid, page, color]
Reactions [userid, bookid, reaction]

I usually select the first one, but the second and the third would save me a lot of PHP coding in the real project. (I'm the only developer). The only difference between the last two are the records:

1, 0, "Jane Austen", null, null, null, 2018-01-01
2, 1, null, "Emma", "Books Enterprise", 12231231213, 2018-02-02
3, 1, null, "Emma in Italian", "Jane Austen Italian Editions", 45345354334, 2018-03-03

(lots of null)
or:

[Books]
1, 0, "Jane Austen", 2018-01-01
2, 1, null, 2018-02-02
3, 1, null, 2018-03-03

[BooksDetails]
2, "Emma", "Books Enterprise", 12231231213
3, "Emma in Italian", "Jane Austen Italian Editions", 45345354334


texts: MySQL: UNION query to get a line for each date

texts:
Table1
DATE        SKU     QTY
2015-06-20      ABCD    3
2015-06-20      ABCD    1
2015-06-21      ABCD    5
2015-06-23      ABCD    2
2015-06-20      EFGH    1
2015-06-21      EFGH    3

Table2
DATE        SKU     QTY
2015-06-21      ABCD    -1
2015-06-22      ABCD    -4
2015-06-23      ABCD    1
2015-06-20      EFGH    -2
2015-06-20      EFGH    1
2015-06-21      EFGH    2
2015-06-23      EFGH    3

Table3
SKU         CATEGORY
ABCD        cat1
EFGH        cat2

I want to know how many pieces I have, basically a daily running total per SKU - like the following:

DATE        SKU     CATEGORY    QTY
2015-06-20      ABCD    cat1            4   (=[0]+3+1)
2015-06-21      ABCD    cat1            8   (=[4]+5-1)
2015-06-22      ABCD    cat1            4   (=[8]-4)
2015-06-23      ABCD    cat1            7   (=[4]+2+1)
2015-06-20      EFGH    cat2            0   (=[0]+1-2+1)
2015-06-21      EFGH    cat2            5   (=[0]+3+2)
2015-06-22      EFGH    cat2            5   (=[5])
2015-06-23      EFGH    cat2            8   (=[5]+3)

basically:
- QTY of item = QTY of item of day before + sum of qty in both tables
- I need a line for all days - even if there is no lines for that day/sku in table1 and table2

I have tried:

SET @lastItem := 0, @lastValue := 0;
SELECT t6.date, t6.SKU, t6.Category, t6.qty FROM
(
SELECT
    t1.date as date,
    t1.sku as sku,
    t3.category as Category,
    @lastValue :=  if( @lastItem = t1.sku, if(@lastValue + ifnull(t1.qty,0)<0,0,@lastValue + ifnull(t1.qty,0)), ifnull(t1.qty,0) ) as qty,
    @lastItem := t1.sku
    FROM
    (
        select date, sku, ifnull(sum(qty),0) as qty FROM(
            (SELECT date(date) as date, idItem, ifnull(Sum(qty),0) as qty
                FROM table1
                GROUP BY sku, date(date)
            )
            UNION
            (SELECT date(date) as date, idItem, ifnull(Sum(qty),0) as qty
                FROM table2
                GROUP BY sku, date(date)
            )
        ) t5
        group by date,sky
    ) t1
    LEFT JOIN table3 t3 on t1.sku = t3.sku
    order by t1.sku, t1.date
) t6

but that does not give me the a line if there is no entry in both table1 and table2

EDIT: Replaced Table1 on second table INSERT.

DDLs:

    CREATE TABLE `Table1` (
      `date` date NOT NULL,
      `sku` varchar(5) NOT NULL,
      `qty` int(5) NOT NULL,
      PRIMARY KEY (`sku`,`date`,`qty`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table1`(date, sku, qty)VALUES
    ('2015-06-20', 'ABCD', '1'),
    ('2015-06-20', 'ABCD', '3'),
    ('2015-06-21', 'ABCD', '5'),
    ('2015-06-23', 'ABCD', '2'),
    ('2015-06-20', 'EFGH', '1'),
    ('2015-06-21', 'EFGH', '3');

    CREATE TABLE `Table2` (
      `date` date NOT NULL,
      `sku` varchar(5) NOT NULL,
      `qty` int(5) NOT NULL,
      PRIMARY KEY (`qty`,`sku`,`date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table2`(date, sku, qty)VALUES
    ('2015-06-22', 'ABCD', '-4'),
    ('2015-06-20', 'EFGH', '-2'),
    ('2015-06-21', 'ABCD', '-1'),
    ('2015-06-23', 'ABCD', '1'),
    ('2015-06-20', 'EFGH', '1'),
    ('2015-06-21', 'EFGH', '2'),
    ('2015-06-23', 'EFGH', '3');

    CREATE TABLE `Table3` (
      `sku` varchar(5) NOT NULL,
      `CATEGORY` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`sku`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `Table3`(sku, CATEGORY)VALUES
    ('ABCD', 'cat1'),
    ('EFGH', 'cat2');


texts:
I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field.  I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)


texts: SQL Server Max Memory not limiting use of RAM

texts:
I would like your input on this. I have a sql server 2008r2 Ent. Ed. 64bit with 16 cores and 64GB RAM. There is one instance of SQL server patched fully as of 20111014.

The max ram is set to 60000MB. Amount of free ram is 0 according to task manager after a few days online.

If I change the max ram to below 53GB it will after a few days to stabilize and have some free ram.

It is the sql process that does allocate the ram according to task manager. How do i come to terms with what the problem really is? It goes without saying that i did alot of testing already but havn't solved this to my liking yet. and ohh we do not get the typical memory starvation lagging when the available ram is down to 0 free.

Update 1:

Inspired by another Q/A related to RAM on this page https://dba.stackexchange.com/a/7062/2744 . I used these two to see what the RAM is being used for.

SELECT TOP ( 10 )
        [type] AS [Memory Clerk Type] ,
        SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM    sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC
OPTION  ( RECOMPILE ) ;

SELECT  DB_NAME(database_id) AS [Database Name] ,
        COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM    sys.dm_os_buffer_descriptors
--WHERE   database_id > 4 -- system databases
--        AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC
OPTION  ( RECOMPILE ) ;

The amount used shown by these are
first select 7948432    Kb
second one 44030,57812  MB
that is a total of about 52GB used by sql server... so where did the rest of my RAM go? :-)
Task manager show right now cached 363, available 401, free 40 and sqlservr.exe has Memory private set 64 459 656. Max Ram set to 60000MB as before.


texts:
I can't seem to create a table in a particular tablespace.  For instance, when I do:

CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space;

and then I do:

select * from pg_tables where schemaname ='myschema' limit 10;

...the tablespace is blank.  The tablespace shows up in PgAdmin as pg_default.  What do I need to change to create the non-default tablespace?


texts: Double queue reader or is it still busy?

texts:
I came across this line in the verbose log on replication QueueReader Agent job startup.

  The Queue Reader Agent has encountered the error 'Another queuereader agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent.' when connecting to 'distribution' on 'Svr01\Inst01'. Ensure that the publication and subscription are defined properly and that both servers are running.

The first clue I had that something was wrong was the vague replication message:

  The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Replication IS working but Replication Monitor is showing red X's.
This second error has shown up from time to time and sometimes goes away.
For a while now I though this was a bug as it has been reported a few times on Connect.

Assuming the first quoted error is correct, how can I find the double QueueReader or previous request?

  tasklist /FI "imagename eq qreader.exe"

Returns nothing.

  SELECT job_id, name, enabled FROM msdb..sysjobs WHERE category_id = 19  ORDER BY date_created;

Returns 1 row on each distributor.

Setup:

Svr01 = Publisher, Distributor, SQL 2005
Svr02 = Publisher, Distributor, SQL 2005, Subscribor (Svr01)
Svr03 = SQL 2005, Subscribor (Svr01, Svr02)

Both Svr01 and Svr02 are reporting these errors.

Resources:

DBA.Stackexchange search results

MSDB forum


texts: Are database connections from an application expensive from a performance point of view?

texts:
A colleague tells me that connections to a database are expensive and could harm performance of the application. They ask me to create a procedure that calls say another 8 procedures, that way they call one procedure and get back 8 datasets. From my viewpoint SQL is having to wait for each dataset to be returned before it can start to retrieve the next one so that will have a negative effect too.

Can anybody quantify the cost of the connection from an application and let me know whether the best approach is to call multiple procedures or one master procedure?


texts: Database Size Increase After Index Rebuild.How to get back down?

texts:
I executed an Index Rebuild for a large table in database because it was 85% fragmented. After the Index Rebuild finished the database file size has grown from 19 GB to 27 GB.

Rebuild option:

(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

My Question :  Is there any solution to decrease database file size without increase index fragmentation?


texts: how to append or update JSON datatype value in postgreSQL

texts:
hey guys i'm using PHP's codeigniter to create my application and postgreSQL as database and i have to update some value in database.
So, let's say we have table structure like this:-

CREATE TABLE emailrecord (
   id serial NOT NULL,
   email json
);

insert first record

INSERT INTO emailrecord (email) VALUES('{"id":"abc@any.com"}');

Now, how to update the inserted record at 1 which is to be like this

'{{"id":"abc@any.com"},{"id","def@any.com"}}'

AND

is there any way to update it via CODEIGNITER  it would be helpful for me


texts: mysql... compare and match items in same column

texts:
I have a column full of server names. For example;

LouServer01
VgsServer01
LouServer02
VgsServer02

I need/want to be able to create a query that can match those two names based on everything after Lou/Vgs and give me something that looks like this:

Louisville  | Vegas
____________|____________
LouServer01 | VgsServer01
LouServer02 | VgsServer02


texts: SQL Server add a linked server to postgres database

texts:
I am trying to connect a PostgreSQL to SQL Server server as a linked server.

On the SQL Server, I have installed the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a specific database on the PostgreSQL. This DSN work correctly.

Using SSMS I created a linked server that created successfully.
When I test the connection it says successful and when I expand I can see all the tables from postgres.

However when I try selecting from one of those tables it goves me the error:
The OLE DB provider "MSDASQL" for linked server "ServerName" reported an error. The provider did not give any information about the error.

I googled around but did not find anything that was helpful for me.
Can someone please help me with this.

Thank you


texts: PAGEIOLATCH wait and Page Life Expectancy very low

texts:
Training for SQL Server, has following scenario:
SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:
- very high PAGELATCH_IO waits
- average Page Life Expectancy is 30
- nothing else is known

What should be done in order to decrease PAGELATCH_IO waits ?
Choose correct answer (need to pick just one):

Add more tempdb files
Enable large-page support
Enable lock pages in memory
Configure buffer pool extensions

Training says that the correct answer is 1) add more tempdb files
I think correct answer is 4) Configure buffer pool extensions, based on:
- PLE is a number of seconds a page will stay in the buffer pool without removing
- PLE = 30 is too low, means high turnover in the buffer pool
- DS-series support Premium SSD drives, can place buffer pool extension on Premium SSD

But could be also this 3) Enable lock pages in memory based on this
https://sqlperformance.com/2014/06/io-subsystem/knee-jerk-waits-pageiolatch-sh
Who is right - me, Training material, or you (your option) ?
By the way, not sure what they mean under PAGELATCH_IO, that could be PAGEIOLATCH or PAGELATCH, I guess answer depends on exactly which type we have


texts:
I have an installation with mariadb version 10.0.21.

I need to backup the binlog file every 5 minutes to TSM, but as the binlog is being written every second, i am afraid of the file getting corrupted when copied.
Is it possible that this happens?
If the binlog gets corrupted (the end of it), will it corrupt the entire file or i can discard the last lines?
Any other solution proposed? We already have replication with master/slave, but for security and audit reasons, we need to keep a copy of the binlog.

Regards


texts:
I have an expired version of SQL Server 2016 RC2 with SSRS installed as well.
The developer decided he didn’t get all the RDL from the SSRS before it expired and now needs to recover those.
I tried to run the installation to upgrade it, but it fails because the services are not started.
I tried to run the Edition Upgrade and it only upgrades a few components of which is not the engine.

How can I recover this?  When the developer installed and configured SSRS they did not save the encryption key, so I don’t believe I can do a new install and restore the mdf.  Is there anything else I can do.


texts: how can I insert a long string into a blob using an insert statement?

texts:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
  ORA-06512: at "SYS.UTL_RAW", line 224

the datatype is BLOB

here's my insert statement

insert into system_value (type, version, id, date_created, date_modified, value )
values ( 0, 0, 0, ${liquibase.db.datatype.timestamp.value}, ${liquibase.db.datatype.timestamp.value},
    utl_raw.cast_to_raw('...really long string'))