274
Add or edit the following line in your postgresql.conf :
listen_addresses = '*'
Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 0.0.0.0/0 md5
Restart Postgresql after adding this with service postgresql restart or ...
26
This solution works for IPv4 / IPv6
nano /var/lib/pgsql/data/pg_hba.conf
add at final
host all all ::1/128 md5
host all postgres 127.0.0.1/32 md5
and then restart postgresql service
/etc/init.d/postgresql restart
24
The option max_user_connections is a limit imposed, not on the total number of simultaneous connections in the server instance, but on the individual user account.
Let's say the user is called db_user@localhost. You can find out what this user's connection limit is by running this query:
SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND ...
15
Use an array to represent the series of values:
pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])");
$strpar = "{3,4,6,8,10}";
pg_execute($con, "prep", array($strpars));
The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.
13
The solution to your problem is a MySQL capability called "partitioning". The documentation is here.
What partitioning does is store a single table in separate "partitions". These are defined by a particular expression, usually a column value or range. In your case, this would probably be based on endTime -- assuming that it is known when a record is ...
13
The way I solved this was:
Added the line as below in pg_hba.conf:
hostnossl all all 0.0.0.0/0 trust
and this was modified in postgresql.conf, as shown:
listen_addresses = '*'
I had this instance running on a Centos 7.3 and Postgres 9.5 in a VM in Azure, given this was a POC (proof of concept) you won't want to connect ...
12
(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):
Lots more features.
CHECK constraints
True SERIALIZABLE isolation
Arrays (including index support for arrays)
Window functions (lead, lag, row_number, etc)
Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
...
11
The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.
CREATE TABLE dbo.EmpLeave
(
EmployeeID int,
leave_date date,
leave_type_color char(6)
);
INSERT ...
10
I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.
My table structure would be similar to the following:
create table myTable
(
id int,
name varchar(50),
IsDeleted bit,
DeletedDate datetime
);
The IsDeleted column has a default value of no and the DeletedDate is not ...
10
That is called a column alias.
An alias is used to give columns a new name for the result set. This can be used to name a column based on an expressions (because otherwise the expression wouldn't "have" a name) or to rename columns, e.g. because the select list contains columns from different tables with the same name:
select t1.foo as t1_foo,
t2....
answered Nov 24 '12 at 12:39
a_horse_with_no_name
60.5k12 gold badges115 silver badges150 bronze badges
7
This is a good "there's more than one way to do it" question.
Start by thinking about your data usage:
Think about which fields are going to be read and written more often.
For example, first name and last name are probably written once and are rarely changed, but depending on your app, they could be read very often.
Last login date could be written ...
7
To expand more on @Twinkles alternative, I'm concerned about why you're storing present and absent students in your attendance data. Is it because you want to be able to run a report that shows all kids and whether they were absent on a given day? There's a better way to do it.
Most systems I've seen store either the that the student is present (Positive ...
7
This looks like you might have hit a bug logged against Percona Server 5.5:
Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables.
There is no fix and no reproducible test case for this bug yet. It has only been observed in a production environment.
The pattern described is:
INSERT a value into a column with a unique constraint....
7
Fresh Postgres 9.5 install, Ubuntu.
The key was the local connection type, since psql uses domain socket connection.
pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all md5
host all all 0.0.0.0/0 md5
6
If you are doing a range of consecutive dates, there is no need to use the IN operator.
If you use this to compute midnight seven days ago
DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND
here is what you get back:
mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND;
+--------------------------------------------------+
| DATE(NOW()) - ...
6
You are looking for pt-archiver, part of the Percona Toolkit. The tool can purge rows from a table; write rows from a table to file; purge rows from a table while copying them to another table (possibly another server)...
It does so in chunks, so like 1,000 rows at a time, so that there is no high load on your server.
6
In addition to privileges on the TABLE, a role needs privileges for the SCHEMA and the DATABASE. The most likely candidate is the schema. If so, this additional GRANT would be the fix:
GRANT ALL ON SCHEMA my_schema TO my_user;
Or at least:
GRANT USAGE ON SCHEMA my_schema TO my_user;
6
I don't use phpPgAmin.
To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column".
A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence.
When you define a column as serial Postgres automatically ...
answered Feb 10 '14 at 7:27
a_horse_with_no_name
60.5k12 gold badges115 silver badges150 bronze badges
6
The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120.
I would perform all this testing on a test server and not in production.
https://...
6
The foundation of your system is your database. If you change your database schema then you also have to change your code. That means that keeping a stable data model helps to reduce the amount of rework you need to do in your code.
Having said that, in the real world requirements change. It's a good idea to invest some time up front to get your data ...
6
When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.
6
You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR.
Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, ...
6
Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks.
Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?".
Using parameterized queries ...
5
I had the same error, trying to debug locally some application that connects to remote DBs.
When using php < 5.3, everything worked.
When using php 5.3 or greater, error shows up.
After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user.
All databases i use have new password length (41).
I changed the ...
5
You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements.
Each element must be bound to a ...
5
Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated
Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute
Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ...
If you feel intimidated by SQL, an ORM can help ...
5
Add the following line in the bottom of pg_hba.conf:
hostnossl all all 0.0.0.0/0 md5
Add/modify the line in postgresql.conf:
listen_addresses = '*'
MAKE SURE THAT the user that is connecting has a password: (Example connect user named postgres)
a. Run the following psql command with the postgres user account:
sudo -u postgres ...
5
Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.
The following query may help you understand this better:
SELECT p.customer_id
, c.name
, sum(t.points)
FROM products p
INNER JOIN types t ON p.type_id = t.id
INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
, c....
5
The primary solution is to add parentheses:
( -- parentheses required!
SELECT dt1.name AS name1, dt1.time AS time1
, dt2.name AS name2, dt2.time AS time2
FROM departure_times dt1
JOIN departure_times dt2 USING (tram_id)
WHERE dt1.name = '$name'
AND dt2.name = 'CitySquare'
-- no ORDER BY?
LIMIT 5
)
UNION ALL -- see below
(
SELECT dt1.name, ...
5
MySQL 8
MySQL 8 provides window functions...
SELECT item_id
FROM (
SELECT
item_id,
date,
count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
FROM (
SELECT
item_id,
date,
date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
FROM foo
) AS t
) AS t2
GROUP BY item_id
HAVING ...
Only top voted, non community-wiki answers of a minimum length are eligible
